Error in Query: Input to aggregation-name does not match schema

[etoulas]

Describe the bug
We upgraded from 0.36.3 to 0.37.3 and suddenly a lot of "custom queries" show the following error message where usually a result/chart is shown.

Input to aggregation-name does not match schema:

	   [(named [nil (named (named [nil (named (named [nil (named (not (instance? java.lang.String :source)) "alias") nil] "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "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") "aggregation") nil] ag-clause)]

What actually fixes the issue is removing the custom expression "Bestellwert" which multiplies 3 fields from the first table in the join.

I don't quite understand what the problem is based on the error message. Metabase created some aliases for the joined tables, one which is "List". There is also a field with the name "source" in one of the tables.

Logs
Stacktrace of "Input to aggregation-name does not match schema:"

[(named [nil (named (named [nil (named [c6f8ed97-4297-4320-8702-f15f68d60213] 2020-12-15T11:51:58+01:00 ERROR metabase.query-processor.middleware.catch-exceptions Fehlerverarbeitungsanfrage: null
{:database_id 2,
 :started_at #t "2020-12-15T11:51:58.132032+01:00[Europe/Zurich]",
 :error_type :invalid-query,
 :json_query
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :type :query,
  :middleware {:js-int-to-string? true},
  :database 2,
  :query
  {:source-table 79,
   :joins
   [{:fields :all, :source-table 76, :condition [:= [:field-id 576] [:joined-field "List" [:field-id 550]]], :alias "List"}
    {:fields :all, :source-table 35, :condition [:= [:joined-field "List" [:field-id 534]] [:joined-field "Department" [:field-id 73]]], :alias "Department"}
    {:fields :all, :source-table 112, :condition [:= [:joined-field "Department" [:field-id 74]] [:joined-field "Customer" [:field-id 833]]], :alias "Customer"}],
   :aggregation [[:distinct [:joined-field "List" [:field-id 550]]] [:sum [:expression "Bestellwert"]]],
   :breakout [[:datetime-field [:joined-field "List" [:field-id 555]] :year]],
   :expressions {:Bestellwert [:* [:field-id 581] [:field-id 577] [:field-id 574]]},
   :filter [:and [:= [:joined-field "Customer" [:field-id 832]] 135] [:time-interval [:joined-field "List" [:field-id 555]] -12 :year {:include-current true}]]},
  :parameters [],
  :async? true,
  :cache-ttl 942},
 :native nil,
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> query_processor.middleware.annotate$fn__44763$aggregation_name__44768.doInvoke(annotate.clj:263)"
  "driver.sql.query_processor$fn__68940$iter__68942__68946$fn__68947$fn__68948.invoke(query_processor.clj:567)"
  "driver.sql.query_processor$fn__68940$iter__68942__68946$fn__68947.invoke(query_processor.clj:563)"
  "driver.sql.query_processor$fn__68940.invokeStatic(query_processor.clj:563)"
  "driver.sql.query_processor$fn__68940.invoke(query_processor.clj:561)"
  "driver.sql.query_processor$apply_top_level_clauses$fn__69225.invoke(query_processor.clj:845)"
  "driver.sql.query_processor$apply_top_level_clauses.invokeStatic(query_processor.clj:843)"
  "driver.sql.query_processor$apply_top_level_clauses.invoke(query_processor.clj:839)"
  "driver.sql.query_processor$apply_clauses_with_aliased_source_query_table.invokeStatic(query_processor.clj:880)"
  "driver.sql.query_processor$apply_clauses_with_aliased_source_query_table.invoke(query_processor.clj:875)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:918)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:909)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:915)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:909)"
  "driver.sql.query_processor$fn__69306$build_honeysql_form__69311$fn__69315.invoke(query_processor.clj:929)"
  "driver.sql.query_processor$fn__69306$build_honeysql_form__69311.invoke(query_processor.clj:926)"
  "driver.sql.query_processor$mbql__GT_honeysql.invokeStatic(query_processor.clj:940)"
  "driver.sql.query_processor$mbql__GT_honeysql.invoke(query_processor.clj:938)"
  "driver.sql.query_processor$mbql__GT_native.invokeStatic(query_processor.clj:946)"
  "driver.sql.query_processor$mbql__GT_native.invoke(query_processor.clj:942)"
  "driver.sql$fn__78116.invokeStatic(sql.clj:40)"
  "driver.sql$fn__78116.invoke(sql.clj:38)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:15)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:10)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:23)"
  "query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)"
  "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)"
  "query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)"
  "query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)"
  "query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)"
  "query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)"
  "query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)"
  "driver$do_with_driver.invokeStatic(driver.clj:61)"
  "driver$do_with_driver.invoke(driver.clj:57)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)"
  "query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
  "query_processor.store$do_with_store.invoke(store.clj:40)"
  "query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)"
  "query_processor.middleware.cache$run_query_with_cache.invokeStatic(cache.clj:187)"
  "query_processor.middleware.cache$run_query_with_cache.invoke(cache.clj:178)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:213)"
  "query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)"
  "query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___39827$fn__39830.invoke(reducible.clj:108)"],
 :context :question,
 :error
 "Input to aggregation-name does not match schema: \n\n\t   [(named [nil (named (named [nil (named (named [nil (named (not (instance? java.lang.String :source)) \"alias\") nil] \"Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy\") \"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\") \"aggregation\") nil] ag-clause)]  \n\n",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :type :query,
  :middleware {:js-int-to-string? true},
  :info
  {:executed-by 6,
   :context :question,
   :card-id 997,
   :query-hash [84, -40, 66, -46, 91, -69, -77, 109, -69, -75, -96, -23, 97, -15, -45, -88, 96, -106, -16, 84, 13, -78, -84, 119, 86, -35, -119, -93, -66, 28, -110, 39]},
  :database 2,
  :query
  {:source-table 79,
   :joins
   [{:strategy :left-join, :source-table 76, :condition [:= [:field-id 576] [:joined-field "List" [:field-id 550]]], :alias "List"}
    {:strategy :left-join, :source-table 35, :condition [:= [:joined-field "List" [:field-id 534]] [:joined-field "Department" [:field-id 73]]], :alias "Department"}
    {:strategy :left-join, :source-table 112, :condition [:= [:joined-field "Department" [:field-id 74]] [:joined-field "Customer" [:field-id 833]]], :alias "Customer"}],
   :aggregation [[:aggregation-options [:distinct [:joined-field "List" [:field-id 550]]] {:name "count"}] [:aggregation-options [:sum [:expression "Bestellwert"]] {:name "sum"}]],
   :breakout [[:datetime-field [:joined-field "List" [:field-id 555]] :year]],
   :expressions {:Bestellwert [:* [:field-id 581] [:field-id 577] [:field-id 574]]},
   :filter
   [:and
    [:= [:joined-field "Customer" [:field-id 832]] [:value 135 {:base_type :type/Integer, :special_type :type/FK, :database_type "int4", :name "tag_id"}]]
    [:between [:datetime-field [:joined-field "List" [:field-id 555]] :year] [:relative-datetime -12 :year] [:relative-datetime 0 :year]]],
   :order-by [[:asc [:datetime-field [:joined-field "List" [:field-id 555]] :year]]]},
  :async? true,
  :cache-ttl 942},
 :ex-data
 {:type :schema.core/error,
  :value ([:aggregation-options [:distinct [:joined-field :source [:field-id 550]]] {:name "count"}]),
  :error
  [(named [nil (named (named [nil (named (named [nil (named (not (instance? java.lang.String :source)) "alias") nil] "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "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") "aggregation") nil] ag-clause)]},
 :data {:rows [], :cols []}}named [nil (named (not (instance? java.lang.String :source)) "alias") nil] "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "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") "aggregation") nil] ag-clause)]

To Reproduce
Steps to reproduce the behavior:

1. I didn't manage to reproduce it with a new query.
2. When I remove all the joins of the existing (failing) query the custom field will work again.
3. Something broke since upgrading to 0.37.3

Expected behavior
Existing custom expressions on joined tables should continue to work with the new Metabase version.

Information about your Metabase Installation:

You can get this information by going to Admin -> Troubleshooting.

• Your databases: Postgres
• Metabase version: 0.37.3
• Metabase hosting environment: Debian 10.4 (buster)
• Metabase internal database: Postgres

Severity
This is blocking some users, especially as the broken queries are spread all over the place.

Obviously, I don't want to go through 2000+ queries to check for that error and redo them manually...

[flamber]

Hi @etoulas
I'm not quite sure what is going wrong here. Haven't seen this problem before, besides problems with Custom Columns in 0.36.5.
If you make similar question to the failing one, then it works, right? Can you check your browser developer Network-tab, when you run the working question and copy the request data - I'm trying to see how that could be different from the failing one.

Can you post the output of the following /api/field/574 and /api/field/577 and /api/field/581 - I'm curious if the fingerprinting has somehow changed the field type, which might be causing a problem.

[sbelak]

This is probably the same bug as #14076 fixes

[etoulas]

@sbelak this issue sounds really very similar.

I will collect the queries as @flamber requested. My logs accumulated a lot of schema sync errors as well since the upgrade. So, probably this could be a side-effect of that.

[flamber]

Reproduced: QtyPrice = [Quantity] * [Products → Price]

Full stacktrace

2020-12-15 15:28:21,452 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 4,
 :started_at #t "2020-12-15T15:28:21.065381+01:00[Europe/Copenhagen]",
 :error_type :invalid-query,
 :json_query
 {:database 4,
  :query
  {:source-query
   {:source-table 11,
    :joins [{:fields "all", :source-table 10, :condition ["=" ["field-id" 82] ["joined-field" "Products" ["field-id" 105]]], :alias "Products"}],
    :expressions {:QtyPrice ["*" ["field-id" 85] ["joined-field" "Products" ["field-id" 104]]]},
    :aggregation [["distinct" ["joined-field" "Products" ["field-id" 105]]] ["sum" ["expression" "QtyPrice"]]],
    :breakout [["datetime-field" ["joined-field" "Products" ["field-id" 108]] "year"]]},
   :filter ["time-interval" ["field-literal" "CREATED_AT" "type/DateTime"] -30 "month" {:include-current true}],
   :order-by [["asc" ["field-literal" "CREATED_AT" "type/DateTime"]]]},
  :type "query",
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native nil,
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> query_processor.middleware.annotate$fn__44763$aggregation_name__44768.doInvoke(annotate.clj:263)"
  "driver.sql.query_processor$fn__68940$iter__68942__68946$fn__68947$fn__68948.invoke(query_processor.clj:567)"
  "driver.sql.query_processor$fn__68940$iter__68942__68946$fn__68947.invoke(query_processor.clj:563)"
  "driver.sql.query_processor$fn__68940.invokeStatic(query_processor.clj:563)"
  "driver.sql.query_processor$fn__68940.invoke(query_processor.clj:561)"
  "driver.sql.query_processor$apply_top_level_clauses$fn__69225.invoke(query_processor.clj:845)"
  "driver.sql.query_processor$apply_top_level_clauses.invokeStatic(query_processor.clj:843)"
  "driver.sql.query_processor$apply_top_level_clauses.invoke(query_processor.clj:839)"
  "driver.sql.query_processor$apply_clauses_with_aliased_source_query_table.invokeStatic(query_processor.clj:880)"
  "driver.sql.query_processor$apply_clauses_with_aliased_source_query_table.invoke(query_processor.clj:875)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:918)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:909)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:915)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:909)"
  "driver.sql.query_processor$apply_source_query$fn__69231.invoke(query_processor.clj:872)"
  "driver.sql.query_processor$apply_source_query.invokeStatic(query_processor.clj:871)"
  "driver.sql.query_processor$apply_source_query.invoke(query_processor.clj:864)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:920)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:909)"
  "driver.sql.query_processor$fn__69306$build_honeysql_form__69311$fn__69315.invoke(query_processor.clj:929)"
  "driver.sql.query_processor$fn__69306$build_honeysql_form__69311.invoke(query_processor.clj:926)"
  "driver.sql.query_processor$mbql__GT_honeysql.invokeStatic(query_processor.clj:940)"
  "driver.sql.query_processor$mbql__GT_honeysql.invoke(query_processor.clj:938)"
  "driver.sql.query_processor$mbql__GT_native.invokeStatic(query_processor.clj:946)"
  "driver.sql.query_processor$mbql__GT_native.invoke(query_processor.clj:942)"
  "driver.sql$fn__78116.invokeStatic(sql.clj:40)"
  "driver.sql$fn__78116.invoke(sql.clj:38)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:15)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:10)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:23)"
  "query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)"
  "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)"
  "query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)"
  "query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)"
  "query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)"
  "query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)"
  "query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)"
  "driver$do_with_driver.invokeStatic(driver.clj:61)"
  "driver$do_with_driver.invoke(driver.clj:57)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)"
  "query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
  "query_processor.store$do_with_store.invoke(store.clj:40)"
  "query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)"
  "query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)"
  "query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___39827.invoke(reducible.clj:109)"
  "query_processor.reducible$sync_qp$qp_STAR___39836$fn__39839.invoke(reducible.clj:135)"
  "query_processor.reducible$sync_qp$qp_STAR___39836.invoke(reducible.clj:134)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:237)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:233)"
  "query_processor$fn__49234$process_query_and_save_execution_BANG___49243$fn__49246.invoke(query_processor.clj:249)"
  "query_processor$fn__49234$process_query_and_save_execution_BANG___49243.invoke(query_processor.clj:241)"
  "query_processor$fn__49278$process_query_and_save_with_max_results_constraints_BANG___49287$fn__49290.invoke(query_processor.clj:261)"
  "query_processor$fn__49278$process_query_and_save_with_max_results_constraints_BANG___49287.invoke(query_processor.clj:254)"
  "api.dataset$fn__52603$fn__52606.invoke(dataset.clj:59)"
  "query_processor.streaming$streaming_response_STAR_$fn__37131$fn__37132.invoke(streaming.clj:73)"
  "query_processor.streaming$streaming_response_STAR_$fn__37131.invoke(streaming.clj:72)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)"
  "async.streaming_response$do_f_async$fn__24639.invoke(streaming_response.clj:85)"],
 :context :ad-hoc,
 :error
 "Input to aggregation-name does not match schema: \n\n\t   [(named [nil (named (named [nil (named (named [nil (named (not (instance? java.lang.String :source)) \"alias\") nil] \"Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy\") \"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\") \"aggregation\") nil] ag-clause)]  \n\n",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:database 4,
  :query
  {:filter [:between [:datetime-field [:field-literal "CREATED_AT" :type/DateTime] :month] [:relative-datetime -30 :month] [:relative-datetime 0 :month]],
   :order-by [[:asc [:field-literal "CREATED_AT" :type/DateTime]]],
   :source-metadata
   [{:table_id 10,
     :special_type :type/CreationTimestamp,
     :unit :year,
     :name "CREATED_AT",
     :settings nil,
     :id 108,
     :display_name "Products → Created At",
     :fingerprint {:global {:distinct-count 200, :nil% 0.0}, :type {:type/DateTime {:earliest "2016-04-27T01:29:55.147Z", :latest "2019-04-15T19:34:19.931Z"}}},
     :base_type :type/DateTime}
    {:name "count", :display_name "Distinct values of Products → ID", :base_type :type/BigInteger, :special_type :type/Number, :settings nil}
    {:name "sum", :display_name "Sum of QtyPrice", :base_type :type/Float, :special_type :type/Number}],
   :fields [[:field-literal "CREATED_AT" :type/DateTime] [:field-literal "count" :type/BigInteger] [:field-literal "sum" :type/Float]],
   :limit 2000,
   :source-query
   {:source-table 11,
    :joins [{:strategy :left-join, :source-table 10, :condition [:= [:field-id 82] [:joined-field "Products" [:field-id 105]]], :alias "Products"}],
    :expressions {:QtyPrice [:* [:field-id 85] [:joined-field "Products" [:field-id 104]]]},
    :aggregation [[:aggregation-options [:distinct [:joined-field "Products" [:field-id 105]]] {:name "count"}] [:aggregation-options [:sum [:expression "QtyPrice"]] {:name "sum"}]],
    :breakout [[:datetime-field [:joined-field "Products" [:field-id 108]] :year]],
    :order-by [[:asc [:datetime-field [:joined-field "Products" [:field-id 108]] :year]]]}},
  :type :query,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :nested? false,
   :query-hash [122, -107, -31, -108, -128, -73, -38, -91, -57, 49, -36, -57, 87, -80, -123, 106, 3, 5, 102, 40, 101, -115, 89, -24, 49, -66, 105, 86, -54, 115, 63, 124]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :ex-data
 {:type :schema.core/error,
  :value ([:aggregation-options [:distinct [:joined-field :source [:field-id 105]]] {:name "count"}]),
  :error
  [(named [nil (named (named [nil (named (named [nil (named (not (instance? java.lang.String :source)) "alias") nil] "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "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") "aggregation") nil] ag-clause)]},
 :data {:rows [], :cols []}}

2020-12-15 15:28:21,456 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 394.3 ms (13 DB calls) App DB connections: 0/10 Jetty threads: 3/50 (3 idle, 0 queued) (126 total active threads) Queries in flight: 1 (0 queued)

Regression since 0.37.1 - it works on 0.37.0:

[etoulas]

Good job!

I just found other queries that are structured the exact same way (for different customer IDs) and they work. Last modification of a particular query was 2 months ago...

[flamber]

Now, here's the fun part. It depends on the other aggregation that you might be using besides the Custom Column.

Here's a more simplified example: QtyTotal = [Quantity] * [Total] (so no joined fields), and the Distinct is using the explicit join Products.ID. This fails:

If I change Distinct to Orders.Product_ID, then it works, so that's probably why some of your questions works and others don't.

---

An even more simplified version that fails with implicit joins, where Custom Column is 1 * 1:

[etoulas]

Yes, you are right, this is spot on! The difference is that the implicit join was missing. I tried to add it to the example that was still working and boom.

Is this indeed the same issue that is solved in #14076 ? The description is very brief there.

[flamber]

Yes, that PR solves this issue. It's currently set to go into the upcoming 0.38.0 release, but I don't know if it can easily be changed to the next 0.37.x patch release. Until then, I would recommend that you downgrade to 0.37.0.2, since that's the last working version.

[etoulas]

Thank a lot @flamber! Downgrading is safe in that case?

[flamber]

@etoulas There's no such thing as "safe", but I would say safe-ish, since there's fairly few changes between 0.37.0 and 0.37.3. Always make backups before upgrading, downgrading or any other big operations, so you have something to revert back to.

[etoulas]

Ok, that makes sense. We do daily backups, so I will go for the downgrade and keep an eye on the upcoming versions.