Aggregation queries breaking for a join query question when used as a source in notebook editor

Get Help
1

I have a table which stores questions from customers for a particular product. I want to make a pivot table for this, which shows the category of the product of the question, where it (the question) came from, etc. along with the customer's email. And I only want to show the latest question for each customer.

For achieving this, I self joined the questions table with itself to get the latest question per customer id, then joined it with the customers table to get their email. Then I used this SQL question as a source to a notebook editor question, summarized by count of rows, and grouped by the category of product for the question. This works well to give me the count of unique customers questioning per product category.

Now, I want to show the sum of the product's price in this same pivot table, for which, I added product amount to the summarize by column of the notebook editor. Doing so, and clicking on visualize, the product amount column kept getting removed from the notebook editor's summarize by field, and when I clicked on View SQL for the question, I saw the following error message =>

Value does not match schema: {:query {:aggregation [nil (named [nil (named (named (not (":field clauses using a string field name must specify :base-type." a-clojure.lang.PersistentVector)) "Must be a valid instance of one of these clauses: :expression, :field") "field-or-expression")] "Must be a valid instance of one of these clauses: :count, :avg, :cum-count, :cum-sum, :distinct, :stddev, :sum, :min, :max, :metric, :share, :count-where, :sum-where, :case, :median, :percentile, :var")]}}

I've tried manually casting the product amount field in the query to integer, but that doesn't work. This is the only issue similar to my situation I've come across in metabase issues => https://github.com/metabase/metabase/issues/14080 .

Here is my diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.10+9",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.10",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.10+9",
    "os.name": "Linux",
    "os.version": "5.4.0-1042-gcp",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "postgres",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.2"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-04-20",
      "tag": "v0.39.0.1",
      "branch": "release-x.39.x",
      "hash": "47bb5f2"
    },
    "settings": {
      "report-timezone": "Asia/Kolkata"
    }
  }
}

Thanks!

2

Hi @anamay
You cannot use Pivot Table in Native query, since Metabase does not parse the SQL, so it has no understanding on the query. So that error seems unrelated to the problem.
Something else is going on in the Notebook editor - check the logs for errors Admin > Troubleshooting > Logs, and since it seems like it might be a frontend issue, then check the browser developer console for errors.

3

This is the error when metabase removes the sum aggregation from the question in the frontend =>

Removing invalid MBQL clause 
[
    "sum",
    [
        "field",
        "revenue",
        null
    ]
]

and this is the api response when metabase is able to make a query with sum aggregation

{
    "via": [
        {
            "type": "clojure.lang.ExceptionInfo",
            "message": "Value does not match schema: {:query {:aggregation [nil (named [nil (named (named (not (\":field clauses using a string field name must specify :base-type.\" a-clojure.lang.PersistentVector)) \"Must be a valid instance of one of these clauses: :expression, :field\") \"field-or-expression\")] \"Must be a valid instance of one of these clauses: :count, :avg, :cum-count, :cum-sum, :distinct, :stddev, :sum, :min, :max, :metric, :share, :count-where, :sum-where, :case, :median, :percentile, :var\")]}}",
            "data": {
                "type": "schema.core/error",
                "value": {
                    "database": 2,
                    "query": {
                        "source-table": "card__154",
                        "aggregation": [
                            [
                                "count"
                            ],
                            [
                                "sum",
                                [
                                    "field",
                                    "revenue",
                                    null
                                ]
                            ]
                        ],
                        "breakout": [
                            [
                                "field",
                                "event_type",
                                {
                                    "base-type": "type/Text"
                                }
                            ],
                            [
                                "field",
                                "category",
                                {
                                    "base-type": "type/Text"
                                }
                            ],
                            [
                                "field",
                                "utm_campaign",
                                {
                                    "base-type": "type/Text"
                                }
                            ],
                            [
                                "expression",
                                "pivot-grouping"
                            ]
                        ],
                        "expressions": {
                            "pivot-grouping": [
                                "abs",
                                0
                            ]
                        }
                    },
                    "type": "query",
                    "pivot-rows": [
                        1,
                        2
                    ],
                    "pivot-cols": [
                        0
                    ],
                    "async?": true
                },
                "error": {
                    "query": {
                        "aggregation": [
                            null,
                            "schema.utils.NamedError@4a06fe94"
                        ]
                    }
                }
            },
            "at": [
                "metabase.util.schema$schema_core_validator$fn__16373",
                "invoke",
                "schema.clj",
                29
            ]
        }
    ],
    "trace": [
        [
            "metabase.util.schema$schema_core_validator$fn__16373",
            "invoke",
            "schema.clj",
            29
        ],
        [
            "metabase.query_processor.middleware.validate$validate_query$fn__49352",
            "invoke",
            "validate.clj",
            9
        ],
        [
            "metabase.query_processor.middleware.normalize_query$normalize$fn__47233",
            "invoke",
            "normalize_query.clj",
            22
        ],
        [
            "metabase.query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44627",
            "invoke",
            "add_rows_truncated.clj",
            35
        ],
        [
            "metabase.query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49283",
            "invoke",
            "results_metadata.clj",
            147
        ],
        [
            "metabase.query_processor.reducible$async_qp$qp_STAR___37956$thunk__37957",
            "invoke",
            "reducible.clj",
            103
        ],
        [
            "metabase.query_processor.reducible$async_qp$qp_STAR___37956",
            "invoke",
            "reducible.clj",
            109
        ],
        [
            "metabase.query_processor.reducible$sync_qp$qp_STAR___37965$fn__37968",
            "invoke",
            "reducible.clj",
            135
        ],
        [
            "metabase.query_processor.reducible$sync_qp$qp_STAR___37965",
            "invoke",
            "reducible.clj",
            134
        ],
        [
            "metabase.query_processor$preprocess_query",
            "invokeStatic",
            "query_processor.clj",
            166
        ],
        [
            "metabase.query_processor$preprocess_query",
            "invoke",
            "query_processor.clj",
            158
        ],
        [
            "metabase.query_processor$query__GT_preprocessed",
            "invokeStatic",
            "query_processor.clj",
            172
        ],
        [
            "metabase.query_processor$query__GT_preprocessed",
            "invoke",
            "query_processor.clj",
            168
        ],
        [
            "metabase.query_processor$query__GT_expected_cols$fn__49418",
            "invoke",
            "query_processor.clj",
            187
        ],
        [
            "metabase.query_processor.store$do_with_store",
            "invokeStatic",
            "store.clj",
            42
        ],
        [
            "metabase.query_processor.store$do_with_store",
            "invoke",
            "store.clj",
            38
        ],
        [
            "metabase.query_processor$query__GT_expected_cols",
            "invokeStatic",
            "query_processor.clj",
            186
        ],
        [
            "metabase.query_processor$query__GT_expected_cols",
            "invoke",
            "query_processor.clj",
            176
        ],
        [
            "metabase.query_processor.pivot$run_pivot_query$fn__55041",
            "invoke",
            "pivot.clj",
            184
        ],
        [
            "metabase.query_processor.store$do_with_store",
            "invokeStatic",
            "store.clj",
            44
        ],
        [
            "metabase.query_processor.store$do_with_store",
            "invoke",
            "store.clj",
            38
        ],
        [
            "metabase.query_processor.pivot$run_pivot_query",
            "invokeStatic",
            "pivot.clj",
            179
        ],
        [
            "metabase.query_processor.pivot$run_pivot_query",
            "invoke",
            "pivot.clj",
            168
        ],
        [
            "metabase.api.dataset$fn__55750$fn__55753",
            "invoke",
            "dataset.clj",
            140
        ],
        [
            "metabase.query_processor.streaming$streaming_response_STAR_$fn__55678$fn__55679",
            "invoke",
            "streaming.clj",
            72
        ],
        [
            "metabase.query_processor.streaming$streaming_response_STAR_$fn__55678",
            "invoke",
            "streaming.clj",
            71
        ],
        [
            "metabase.async.streaming_response$do_f_STAR_",
            "invokeStatic",
            "streaming_response.clj",
            65
        ],
        [
            "metabase.async.streaming_response$do_f_STAR_",
            "invoke",
            "streaming_response.clj",
            63
        ],
        [
            "metabase.async.streaming_response$do_f_async$fn__16071",
            "invoke",
            "streaming_response.clj",
            84
        ],
        [
            "clojure.lang.AFn",
            "applyToHelper",
            "AFn.java",
            152
        ],
        [
            "clojure.lang.AFn",
            "applyTo",
            "AFn.java",
            144
        ],
        [
            "clojure.core$apply",
            "invokeStatic",
            "core.clj",
            665
        ],
        [
            "clojure.core$with_bindings_STAR_",
            "invokeStatic",
            "core.clj",
            1973
        ],
        [
            "clojure.core$with_bindings_STAR_",
            "doInvoke",
            "core.clj",
            1973
        ],
        [
            "clojure.lang.RestFn",
            "invoke",
            "RestFn.java",
            425
        ],
        [
            "clojure.lang.AFn",
            "applyToHelper",
            "AFn.java",
            156
        ],
        [
            "clojure.lang.RestFn",
            "applyTo",
            "RestFn.java",
            132
        ],
        [
            "clojure.core$apply",
            "invokeStatic",
            "core.clj",
            669
        ],
        [
            "clojure.core$bound_fn_STAR_$fn__5749",
            "doInvoke",
            "core.clj",
            2003
        ],
        [
            "clojure.lang.RestFn",
            "invoke",
            "RestFn.java",
            397
        ],
        [
            "clojure.lang.AFn",
            "run",
            "AFn.java",
            22
        ],
        [
            "java.util.concurrent.Executors$RunnableAdapter",
            "call",
            null,
            -1
        ],
        [
            "java.util.concurrent.FutureTask",
            "run",
            null,
            -1
        ],
        [
            "java.util.concurrent.ThreadPoolExecutor",
            "runWorker",
            null,
            -1
        ],
        [
            "java.util.concurrent.ThreadPoolExecutor$Worker",
            "run",
            null,
            -1
        ],
        [
            "java.lang.Thread",
            "run",
            null,
            -1
        ]
    ],
    "cause": "Value does not match schema: {:query {:aggregation [nil (named [nil (named (named (not (\":field clauses using a string field name must specify :base-type.\" a-clojure.lang.PersistentVector)) \"Must be a valid instance of one of these clauses: :expression, :field\") \"field-or-expression\")] \"Must be a valid instance of one of these clauses: :count, :avg, :cum-count, :cum-sum, :distinct, :stddev, :sum, :min, :max, :metric, :share, :count-where, :sum-where, :case, :median, :percentile, :var\")]}}",
    "data": {
        "type": "schema.core/error",
        "value": {
            "database": 2,
            "query": {
                "source-table": "card__154",
                "aggregation": [
                    [
                        "count"
                    ],
                    [
                        "sum",
                        [
                            "field",
                            "revenue",
                            null
                        ]
                    ]
                ],
                "breakout": [
                    [
                        "field",
                        "event_type",
                        {
                            "base-type": "type/Text"
                        }
                    ],
                    [
                        "field",
                        "category",
                        {
                            "base-type": "type/Text"
                        }
                    ],
                    [
                        "field",
                        "utm_campaign",
                        {
                            "base-type": "type/Text"
                        }
                    ],
                    [
                        "expression",
                        "pivot-grouping"
                    ]
                ],
                "expressions": {
                    "pivot-grouping": [
                        "abs",
                        0
                    ]
                }
            },
            "type": "query",
            "pivot-rows": [
                1,
                2
            ],
            "pivot-cols": [
                0
            ],
            "async?": true
        },
        "error": {
            "query": {
                "aggregation": [
                    null,
                    "schema.utils.NamedError@4a06fe94"
                ]
            }
        }
    },
    "_status": 500
}
4

@anamay Can you show a screenshot of the Notebook. From the looks of it, you're creating a question based on a Saved Question (154) with two metrics (Count and Sum of Revenue), but is Revenue actually a column that exists in the Saved Question?
That looks like the cause. Would love to figure out how to reproduce this.

5

@flamber Sure, here's the screenshot of the notebook editor:

Screenshot%20from%202021-04-22%2015-26-50
Screenshot%20from%202021-04-22%2015-26-501282×801 41.8 KB

The source is indeed a saved question, and it does have a field named as revenue, this is the line from the select statement of the query of saved question which names it so =>
coalesce(business_events.event_revenue, 0)::integer as revenue

edit: I've also recorded the process for you: metabase issue - Album on Imgur . Sorry about the terrible quality, I guess imgur must have compressed it.

6

@anamay Can you give the metadata for the Saved Question (please check it before posting, in case you find it too sensitive, then use <redacted> where absolutely needed)
Go to this URL /api/table/card__154/query_metadata

7

@flamber here you go:

{"id":"card__154","db_id":2,"display_name":"Business Events with deduplicated leads","schema":"Business Insights","description":null,"fields":[{"semantic_type":"type/Category","table_id":"card__154","name":"event_type","dimension_options":[],"field_ref":["field","event_type",{"base-type":"type/Text"}],"id":["field","event_type",{"base-type":"type/Text"}],"default_dimension_option":null,"display_name":"event_type","fingerprint":{"global":{"distinct-count":3,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":4.1605}}},"base_type":"type/Text"},{"semantic_type":null,"table_id":"card__154","name":"utm_campaign","dimension_options":[],"field_ref":["field","utm_campaign",{"base-type":"type/Text"}],"id":["field","utm_campaign",{"base-type":"type/Text"}],"default_dimension_option":null,"display_name":"utm_campaign","fingerprint":{"global":{"distinct-count":81,"nil%":0.554},"type":{"type/Text":{"percent-json":5.0E-4,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":6.212}}},"base_type":"type/Text"},{"semantic_type":null,"table_id":"card__154","name":"category","dimension_options":[],"field_ref":["field","category",{"base-type":"type/Text"}],"id":["field","category",{"base-type":"type/Text"}],"default_dimension_option":null,"display_name":"category","fingerprint":{"global":{"distinct-count":104,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":14.9605}}},"base_type":"type/Text"},{"semantic_type":null,"table_id":"card__154","name":"revenue","dimension_options":["15","16","17","18","19"],"field_ref":["field","revenue",{"base-type":"type/Integer"}],"id":["field","revenue",{"base-type":"type/Integer"}],"default_dimension_option":"15","display_name":"revenue","fingerprint":{"global":{"distinct-count":44,"nil%":0.0},"type":{"type/Number":{"min":0.0,"q1":0.0,"q3":0.31713172122303196,"max":184380.0,"sd":7163.662708084294,"avg":654.05}}},"base_type":"type/Integer"},{"semantic_type":null,"table_id":"card__154","name":"profit","dimension_options":["15","16","17","18","19"],"field_ref":["field","profit",{"base-type":"type/Integer"}],"id":["field","profit",{"base-type":"type/Integer"}],"default_dimension_option":"15","display_name":"profit","fingerprint":{"global":{"distinct-count":33,"nil%":0.0},"type":{"type/Number":{"min":0.0,"q1":0.0,"q3":2.1722390569928205,"max":134100.0,"sd":3331.1497567787374,"avg":179.708}}},"base_type":"type/Integer"},{"semantic_type":null,"table_id":"card__154","name":"email","dimension_options":[],"field_ref":["field","email",{"base-type":"type/Text"}],"id":["field","email",{"base-type":"type/Text"}],"default_dimension_option":null,"display_name":"email","fingerprint":{"global":{"distinct-count":1957,"nil%":0.022},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.978,"percent-state":0.0,"average-length":22.6955}}},"base_type":"type/Text"}],"dimension_options":{"9":{"name":"Day of Week","mbql":["field",null,{"temporal-unit":"day-of-week"}],"type":"type/DateTime"},"3":{"name":"Week","mbql":["field",null,{"temporal-unit":"week"}],"type":"type/DateTime"},"22":{"name":"Bin every 1 degree","mbql":["field",null,{"binning":{"strategy":"bin-width","bin-width":1.0}}],"type":"type/Coordinate"},"4":{"name":"Month","mbql":["field",null,{"temporal-unit":"month"}],"type":"type/DateTime"},"8":{"name":"Hour of Day","mbql":["field",null,{"temporal-unit":"hour-of-day"}],"type":"type/DateTime"},"14":{"name":"Quarter of Year","mbql":["field",null,{"temporal-unit":"quarter-of-year"}],"type":"type/DateTime"},"21":{"name":"Bin every 0.1 degrees","mbql":["field",null,{"binning":{"strategy":"bin-width","bin-width":0.1}}],"type":"type/Coordinate"},"20":{"name":"Auto bin","mbql":["field",null,{"binning":{"strategy":"default"}}],"type":"type/Coordinate"},"19":{"name":"Don't bin","mbql":null,"type":"type/Number"},"17":{"name":"50 bins","mbql":["field",null,{"binning":{"strategy":"num-bins","num-bins":50}}],"type":"type/Number"},"25":{"name":"Don't bin","mbql":null,"type":"type/Coordinate"},"15":{"name":"Auto bin","mbql":["field",null,{"binning":{"strategy":"default"}}],"type":"type/Number"},"7":{"name":"Minute of Hour","mbql":["field",null,{"temporal-unit":"minute-of-hour"}],"type":"type/DateTime"},"5":{"name":"Quarter","mbql":["field",null,{"temporal-unit":"quarter"}],"type":"type/DateTime"},"18":{"name":"100 bins","mbql":["field",null,{"binning":{"strategy":"num-bins","num-bins":100}}],"type":"type/Number"},"12":{"name":"Week of Year","mbql":["field",null,{"temporal-unit":"week-of-year"}],"type":"type/DateTime"},"13":{"name":"Month of Year","mbql":["field",null,{"temporal-unit":"month-of-year"}],"type":"type/DateTime"},"24":{"name":"Bin every 20 degrees","mbql":["field",null,{"binning":{"strategy":"bin-width","bin-width":20.0}}],"type":"type/Coordinate"},"6":{"name":"Year","mbql":["field",null,{"temporal-unit":"year"}],"type":"type/DateTime"},"1":{"name":"Hour","mbql":["field",null,{"temporal-unit":"hour"}],"type":"type/DateTime"},"0":{"name":"Minute","mbql":["field",null,{"temporal-unit":"minute"}],"type":"type/DateTime"},"11":{"name":"Day of Year","mbql":["field",null,{"temporal-unit":"day-of-year"}],"type":"type/DateTime"},"2":{"name":"Day","mbql":["field",null,{"temporal-unit":"day"}],"type":"type/DateTime"},"16":{"name":"10 bins","mbql":["field",null,{"binning":{"strategy":"num-bins","num-bins":10}}],"type":"type/Number"},"10":{"name":"Day of Month","mbql":["field",null,{"temporal-unit":"day-of-month"}],"type":"type/DateTime"},"23":{"name":"Bin every 10 degrees","mbql":["field",null,{"binning":{"strategy":"bin-width","bin-width":10.0}}],"type":"type/Coordinate"}}}
8

@anamay That did the trick - I was spending a long time trying to reproduce an issue open yesterday, which I couldn't get to fail, but with your data, then I nailed it - thank you!
Follow the issue here: https://github.com/metabase/metabase/issues/15725

9

@flamber Awesome, glad I could be of help :slight_smile: