Hello,
I made an indicator this morning, then noticed there was an update.
I did update metabase, and indicator is now broken. Recreating the same indicator fails:
How can I fix it back?
Hi @sbs
Which version did you upgrade from?
Post "Diagnostic Info" from Admin > Troubleshooting.
And post the full stacktrace error from Admin > Troubleshooting > Logs.
Diag Info :
{
"browser-info": {
"language": "fr-FR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.109 Safari/537.36 OPR/84.0.4316.31",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.14.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.14.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.14.1+1",
"os.name": "Linux",
"os.version": "5.4.0-104-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mysql",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2022-02-28",
"tag": "v0.42.2",
"branch": "release-x.42.x",
"hash": "d6ff494"
},
"settings": {
"report-timezone": null
}
}
}
@sbs I'm guessing that you upgraded from 0.42.0 and that you are using Saved Questions:
https://github.com/metabase/metabase/issues/20809 - upvote by clicking 
on the first post
good guess 
Just to make sure the stack trace (hopefully a useful part, since the log is quite long)
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 35 :
{:source-table "card__34",
:expressions {:Satisfaction [:- [:field 1617 nil] [:field 1621 nil]]},
:filter [:>= [:expression "Satisfaction"] 0],
:breakout [[:field 1618 {:temporal-unit :year}]],
:aggregation [[:count]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 34 :
{:source-table 189,
:joins
[{:fields
[[:field 1618 {:join-alias "TblTrainingGradeLog"}]
[:field 1617 {:join-alias "TblTrainingGradeLog"}]
[:field 1616 {:join-alias "TblTrainingGradeLog"}]],
:source-table 188,
:condition
[:= [:field 1622 {:temporal-unit :year}] [:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :year}]],
:alias "TblTrainingGradeLog",
:strategy :inner-join}],
:fields [[:field 1621 nil]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 32 :
{:source-table 188, :breakout [[:field 1618 {:temporal-unit :year}]], :aggregation [[:count]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 35 :
{:source-table "card__34",
:expressions {:Satisfaction [:- [:field 1617 nil] [:field 1621 nil]]},
:filter [:>= [:expression "Satisfaction"] 0],
:breakout [[:field 1618 {:temporal-unit :year}]],
:aggregation [[:count]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 32 :
{:source-table 188, :breakout [[:field 1618 {:temporal-unit :year}]], :aggregation [[:count]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:51+01:00 INFO metabase.query-processor.middleware.fetch-source-query Requête source extraite de la carte 34 :
{:source-table 189,
:joins
[{:fields
[[:field 1618 {:join-alias "TblTrainingGradeLog"}]
[:field 1617 {:join-alias "TblTrainingGradeLog"}]
[:field 1616 {:join-alias "TblTrainingGradeLog"}]],
:source-table 188,
:condition
[:= [:field 1622 {:temporal-unit :year}] [:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :year}]],
:alias "TblTrainingGradeLog",
:strategy :inner-join}],
:fields [[:field 1621 nil]]}
[67e3b154-bb0b-41cd-a3e2-7bd4a6883bea] 2022-03-14T12:22:52+01:00 ERROR metabase.query-processor.middleware.catch-exceptions Erreur lors du traitement de la requête : null
{:database_id 3,
:started_at #t "2022-03-14T11:22:51.538751Z[GMT]",
:via
[{:status :failed,
:class clojure.lang.ExceptionInfo,
:error "Erreur lors de lexécution de la requête: {0}",
:stacktrace
["--> driver.sql_jdbc.execute$execute_reducible_query$fn__53301.invoke(execute.clj:504)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:501)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:496)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
"driver.sql_jdbc$fn__81999.invokeStatic(sql_jdbc.clj:54)"
"driver.sql_jdbc$fn__81999.invoke(sql_jdbc.clj:52)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
"query_processor.reducible$pivot.invoke(reducible.clj:31)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__49655.invoke(mbql_to_native.clj:25)"
"query_processor.middleware.check_features$check_features$fn__50401.invoke(check_features.clj:42)"
"query_processor.middleware.limit$limit$fn__47991.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__50784.invoke(cache.clj:204)"
"query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__51848.invoke(optimize_temporal_filters.clj:204)"
"query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__51892.invoke(validate_temporal_bucketing.clj:50)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49712.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39780.invoke(wrap_value_literals.clj:161)"
"query_processor.middleware.annotate$add_column_info$fn__44546.invoke(annotate.clj:659)"
"query_processor.middleware.permissions$check_query_permissions$fn__46288.invoke(permissions.clj:108)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__51006.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46684.invoke(cumulative_aggregations.clj:60)"
"query_processor.middleware.visualization_settings$update_viz_settings$fn__46622.invoke(visualization_settings.clj:63)"
"query_processor.middleware.escape_join_aliases$escape_join_aliases_middleware$fn__48032.invoke(escape_join_aliases.clj:64)"
"query_processor.middleware.fix_bad_references$fix_bad_references_middleware$fn__50971.invoke(fix_bad_references.clj:91)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47569.invoke(resolve_joined_fields.clj:111)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__51618.invoke(resolve_joins.clj:178)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__51160.invoke(add_implicit_joins.clj:246)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47588.invoke(large_int_id.clj:59)"
"query_processor.middleware.format_rows$format_rows$fn__51212.invoke(format_rows.clj:74)"
"query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46956.invoke(add_default_temporal_unit.clj:23)"
"query_processor.middleware.desugar$desugar$fn__46595.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__39509.invoke(binning.clj:229)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__45930.invoke(resolve_fields.clj:34)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__50338.invoke(add_dimension_projections.clj:487)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__50632.invoke(add_implicit_clauses.clj:164)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__47976.invoke(upgrade_field_literals.clj:117)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__47333.invoke(add_source_metadata.clj:125)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50883.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48970.invoke(auto_bucket_datetimes.clj:147)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45911.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__48624.invoke(parameters.clj:109)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45984.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__52276.invoke(expand_macros.clj:184)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__48404.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__51227.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643$fn__50648.invoke(resolve_database_and_driver.clj:35)"
"driver$do_with_driver.invokeStatic(driver.clj:60)"
"driver$do_with_driver.invoke(driver.clj:56)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643.invoke(resolve_database_and_driver.clj:34)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46530.invoke(fetch_source_query.clj:282)"
"query_processor.middleware.store$initialize_store$fn__46721$fn__46722.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__46721.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__50978.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__50985.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48350.invoke(add_rows_truncated.clj:35)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49641.invoke(results_metadata.clj:82)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__48368.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__50919.invoke(process_userland_query.clj:146)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__51307.invoke(catch_exceptions.clj:169)"
"query_processor.reducible$async_qp$qp_STAR___43282$thunk__43283.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___43282$fn__43285.invoke(reducible.clj:108)"],
:error_type :invalid-query,
:ex-data
{:sql
"-- Metabase:: userID: 2 queryType: MBQL queryHash: 97c54cedadb0552fcd48112f8a7ac4cf2341b583d248a2d10153e5f632d243c4\nSELECT DateFromParts(year(\"source\".\"Question 32__TrainingDate\"), 1, 1) AS \"Question 32__TrainingDate\", avg(\"source\".\"Satisfaction\") AS \"avg\" FROM (SELECT \"Question 32\".\"TrainingDate\" AS \"Question 32__TrainingDate\", \"source\".\"count\" AS \"count\", (CAST(\"source\".\"count\" AS float) / CASE WHEN \"Question 32\".\"count\" = 0 THEN NULL ELSE \"Question 32\".\"count\" END) AS \"Satisfaction\", \"Question 32\".\"count\" AS \"Question 32__count\", DateFromParts(year(\"Question 32\".\"TrainingDate\"), 1, 1) AS \"Question 32__TrainingDate_2\", \"Question 32\".\"TrainingDate\" AS \"Question 32__TrainingDate_3\" FROM (SELECT TOP 1048575 DateFromParts(year(\"source\".\"TblTrainingGradeLog__TrainingDate\"), 1, 1) AS \"TblTrainingGradeLog__TrainingDate\", count(*) AS \"count\" FROM (SELECT \"dbo\".\"tblTrainingParams\".\"SatisfactionMinScore\" AS \"SatisfactionMinScore\", \"TblTrainingGradeLog\".\"TrainingDate\" AS \"TblTrainingGradeLog__TrainingDate\", \"TblTrainingGradeLog\".\"Grade\" AS \"TblTrainingGradeLog__Grade\", \"TblTrainingGradeLog\".\"TrainerID\" AS \"TblTrainingGradeLog__TrainerID\" FROM \"dbo\".\"tblTrainingParams\" INNER JOIN \"dbo\".\"tblTrainingGradeLog\" \"TblTrainingGradeLog\" ON DateFromParts(year(\"dbo\".\"tblTrainingParams\".\"ApplicableYear\"), 1, 1) = DateFromParts(year(\"TblTrainingGradeLog\".\"TrainingDate\"), 1, 1)) \"source\" WHERE (\"source\".\"TblTrainingGradeLog__Grade\" - \"source\".\"SatisfactionMinScore\") >= 0 GROUP BY year(\"source\".\"TblTrainingGradeLog__TrainingDate\") ORDER BY year(\"source\".\"TblTrainingGradeLog__TrainingDate\") ASC ) \"source\" INNER JOIN (SELECT DateFromParts(year(\"dbo\".\"tblTrainingGradeLog\".\"TrainingDate\"), 1, 1) AS \"TrainingDate\", count(*) AS \"count\" FROM \"dbo\".\"tblTrainingGradeLog\" GROUP BY year(\"dbo\".\"tblTrainingGradeLog\".\"TrainingDate\")) \"Question 32\" ON DateFromParts(year(\"source\".\"TrainingDate\"), 1, 1) = DateFromParts(year(\"Question 32\".\"TrainingDate\"), 1, 1)) \"source\" GROUP BY year(\"source\".\"Question 32__TrainingDate\") ORDER BY year(\"source\".\"Question 32__TrainingDate\") ASC",
:params nil,
:type :invalid-query}}],
:state "S0001",
:error_type :invalid-query,
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:js-int-to-string? true, :ignore-cached-results? true, :process-viz-settings? false},
:database 3,
:query
{:source-table "card__35",
:joins
[{:fields :all,
:source-table "card__32",
:condition
[:= [:field 1618 {:temporal-unit :year}] [:field 1618 {:join-alias "Question 32", :temporal-unit :year}]],
:alias "Question 32",
:strategy :inner-join}],
:expressions
{:Satisfaction
[:/
[:field "count" {:base-type :type/Integer}]
[:field "count" {:base-type :type/Integer, :join-alias "Question 32"}]]},
:breakout [[:field 1618 {:temporal-unit :year}]],
:aggregation [[:avg [:expression "Satisfaction"]]]},
:parameters [],
:async? true,
:cache-ttl nil},
:native
{:query
"SELECT DateFromParts(year(\"source\".\"Question 32__TrainingDate\"), 1, 1) AS \"Question 32__TrainingDate\", avg(\"source\".\"Satisfaction\") AS \"avg\" FROM (SELECT \"Question 32\".\"TrainingDate\" AS \"Question 32__TrainingDate\", \"source\".\"count\" AS \"count\", (CAST(\"source\".\"count\" AS float) / CASE WHEN \"Question 32\".\"count\" = 0 THEN NULL ELSE \"Question 32\".\"count\" END) AS \"Satisfaction\", \"Question 32\".\"count\" AS \"Question 32__count\", DateFromParts(year(\"Question 32\".\"TrainingDate\"), 1, 1) AS \"Question 32__TrainingDate_2\", \"Question 32\".\"TrainingDate\" AS \"Question 32__TrainingDate_3\" FROM (SELECT TOP 1048575 DateFromParts(year(\"source\".\"TblTrainingGradeLog__TrainingDate\"), 1, 1) AS \"TblTrainingGradeLog__TrainingDate\", count(*) AS \"count\" FROM (SELECT \"dbo\".\"tblTrainingParams\".\"SatisfactionMinScore\" AS \"SatisfactionMinScore\", \"TblTrainingGradeLog\".\"TrainingDate\" AS \"TblTrainingGradeLog__TrainingDate\", \"TblTrainingGradeLog\".\"Grade\" AS \"TblTrainingGradeLog__Grade\", \"TblTrainingGradeLog\".\"TrainerID\" AS \"TblTrainingGradeLog__TrainerID\" FROM \"dbo\".\"tblTrainingParams\" INNER JOIN \"dbo\".\"tblTrainingGradeLog\" \"TblTrainingGradeLog\" ON DateFromParts(year(\"dbo\".\"tblTrainingParams\".\"ApplicableYear\"), 1, 1) = DateFromParts(year(\"TblTrainingGradeLog\".\"TrainingDate\"), 1, 1)) \"source\" WHERE (\"source\".\"TblTrainingGradeLog__Grade\" - \"source\".\"SatisfactionMinScore\") >= 0 GROUP BY year(\"source\".\"TblTrainingGradeLog__TrainingDate\") ORDER BY year(\"source\".\"TblTrainingGradeLog__TrainingDate\") ASC ) \"source\" INNER JOIN (SELECT DateFromParts(year(\"dbo\".\"tblTrainingGradeLog\".\"TrainingDate\"), 1, 1) AS \"TrainingDate\", count(*) AS \"count\" FROM \"dbo\".\"tblTrainingGradeLog\" GROUP BY year(\"dbo\".\"tblTrainingGradeLog\".\"TrainingDate\")) \"Question 32\" ON DateFromParts(year(\"source\".\"TrainingDate\"), 1, 1) = DateFromParts(year(\"Question 32\".\"TrainingDate\"), 1, 1)) \"source\" GROUP BY year(\"source\".\"Question 32__TrainingDate\") ORDER BY year(\"source\".\"Question 32__TrainingDate\") ASC",
:params nil},
:status :failed,
:class com.microsoft.sqlserver.jdbc.SQLServerException,
:stacktrace
["com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)"
"com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)"
"com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743)"
"com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
"--> driver.sql_jdbc.execute$fn__53221.invokeStatic(execute.clj:368)"
"driver.sql_jdbc.execute$fn__53221.invoke(execute.clj:366)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:376)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:373)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__53301.invoke(execute.clj:502)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:501)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:496)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
"driver.sql_jdbc$fn__81999.invokeStatic(sql_jdbc.clj:54)"
"driver.sql_jdbc$fn__81999.invoke(sql_jdbc.clj:52)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
"query_processor.reducible$pivot.invoke(reducible.clj:31)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__49655.invoke(mbql_to_native.clj:25)"
"query_processor.middleware.check_features$check_features$fn__50401.invoke(check_features.clj:42)"
"query_processor.middleware.limit$limit$fn__47991.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__50784.invoke(cache.clj:204)"
"query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__51848.invoke(optimize_temporal_filters.clj:204)"
"query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__51892.invoke(validate_temporal_bucketing.clj:50)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49712.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39780.invoke(wrap_value_literals.clj:161)"
"query_processor.middleware.annotate$add_column_info$fn__44546.invoke(annotate.clj:659)"
"query_processor.middleware.permissions$check_query_permissions$fn__46288.invoke(permissions.clj:108)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__51006.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46684.invoke(cumulative_aggregations.clj:60)"
"query_processor.middleware.visualization_settings$update_viz_settings$fn__46622.invoke(visualization_settings.clj:63)"
"query_processor.middleware.escape_join_aliases$escape_join_aliases_middleware$fn__48032.invoke(escape_join_aliases.clj:64)"
"query_processor.middleware.fix_bad_references$fix_bad_references_middleware$fn__50971.invoke(fix_bad_references.clj:91)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47569.invoke(resolve_joined_fields.clj:111)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__51618.invoke(resolve_joins.clj:178)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__51160.invoke(add_implicit_joins.clj:246)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47588.invoke(large_int_id.clj:59)"
"query_processor.middleware.format_rows$format_rows$fn__51212.invoke(format_rows.clj:74)"
"query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46956.invoke(add_default_temporal_unit.clj:23)"
"query_processor.middleware.desugar$desugar$fn__46595.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__39509.invoke(binning.clj:229)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__45930.invoke(resolve_fields.clj:34)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__50338.invoke(add_dimension_projections.clj:487)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__50632.invoke(add_implicit_clauses.clj:164)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__47976.invoke(upgrade_field_literals.clj:117)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__47333.invoke(add_source_metadata.clj:125)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50883.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48970.invoke(auto_bucket_datetimes.clj:147)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45911.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__48624.invoke(parameters.clj:109)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45984.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__52276.invoke(expand_macros.clj:184)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__48404.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__51227.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643$fn__50648.invoke(resolve_database_and_driver.clj:35)"
"driver$do_with_driver.invokeStatic(driver.clj:60)"
"driver$do_with_driver.invoke(driver.clj:56)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643.invoke(resolve_database_and_driver.clj:34)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46530.invoke(fetch_source_query.clj:282)"
"query_processor.middleware.store$initialize_store$fn__46721$fn__46722.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__46721.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__50978.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__50985.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48350.invoke(add_rows_truncated.clj:35)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49641.invoke(results_metadata.clj:82)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__48368.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__50919.invoke(process_userland_query.clj:146)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__51307.invoke(catch_exceptions.clj:169)"
"query_processor.reducible$async_qp$qp_STAR___43282$thunk__43283.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___43282$fn__43285.invoke(reducible.clj:108)"],
:card_id 36,
:context :question,
:error "Invalid column name 'TrainingDate'.",
:row_count 0,
:running_time 0,
:preprocessed
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:js-int-to-string? true, :ignore-cached-results? true, :process-viz-settings? false},
:info
{:executed-by 2,
:context :question,
:card-id 36,
:card-name "SatisfactionRatePerYear",
:query-hash
[-105, -59, 76, -19, -83, -80, 85, 47, -51, 72, 17, 47, -118, 122, -60, -49, 35, 65, -75, -125, -46, 72, -94, -47,
1, 83, -27, -10, 50, -46, 67, -60]},
:database 3,
:query
{:expressions
{:Satisfaction
[:/
[:field "count" {:base-type :type/Integer}]
[:field "count" {:base-type :type/Integer, :join-alias "Question 32"}]]},
:breakout [[:field 1618 {:temporal-unit :year, :join-alias "Question 32"}]],
:aggregation [[:aggregation-options [:avg [:expression "Satisfaction"]] {:name "avg"}]],
:source-card-id 35,
:source-metadata
[{:description nil,
:semantic_type nil,
:coercion_strategy nil,
:unit :year,
:name "TrainingDate",
:settings nil,
:field_ref [:field 1618 {:temporal-unit :year, :join-alias "Question 32"}],
:effective_type :type/Date,
:id 1618,
:display_name "TblTrainingGradeLog → TrainingDate",
:fingerprint
{:global {:distinct-count 6, :nil% 0.0},
:type {:type/DateTime {:earliest "2021-02-28T00:00:00Z", :latest "2021-12-03T00:00:00Z"}}},
:base_type :type/Date}
{:display_name "Nombre de lignes",
:semantic_type :type/Quantity,
:field_ref [:aggregation 0],
:name "count",
:base_type :type/Integer,
:effective_type :type/Integer,
:fingerprint
{:global {:distinct-count 1, :nil% 0.0},
:type {:type/Number {:min 19.0, :q1 19.0, :q3 19.0, :max 19.0, :sd nil, :avg 19.0}}}}],
:order-by [[:asc [:field 1618 {:temporal-unit :year, :join-alias "Question 32"}]]],
:joins
[{:alias "Question 32",
:strategy :inner-join,
:fields
[[:field 1618 {:join-alias "Question 32"}]
[:field "count" {:base-type :type/Integer, :join-alias "Question 32"}]],
:condition
[:= [:field 1618 {:temporal-unit :year}] [:field 1618 {:join-alias "Question 32", :temporal-unit :year}]],
:source-card-id 32,
:source-query
{:source-table 188,
:breakout [[:field 1618 {:temporal-unit :year}]],
:aggregation [[:aggregation-options [:count] {:name "count"}]],
:order-by [[:asc [:field 1618 {:temporal-unit :year}]]]},
:source-metadata
[{:description nil,
:semantic_type nil,
:coercion_strategy nil,
:unit :year,
:name "TrainingDate",
:settings nil,
:field_ref [:field 1618 {:temporal-unit :year}],
:effective_type :type/Date,
:id 1618,
:display_name "TrainingDate",
:fingerprint
{:global {:distinct-count 6, :nil% 0.0},
:type {:type/DateTime {:earliest "2021-02-28T00:00:00Z", :latest "2021-12-03T00:00:00Z"}}},
:base_type :type/Date}
{:display_name "Nombre de lignes",
:semantic_type :type/Quantity,
:field_ref [:aggregation 0],
:name "count",
:base_type :type/Integer,
:effective_type :type/Integer,
:fingerprint
{:global {:distinct-count 1, :nil% 0.0},
:type {:type/Number {:min 25.0, :q1 25.0, :q3 25.0, :max 25.0, :sd nil, :avg 25.0}}}}]}],
:source-query
{:expressions {:Satisfaction [:- [:field 1617 {:join-alias "TblTrainingGradeLog"}] [:field 1621 nil]]},
:filter [:>= [:expression "Satisfaction"] [:value 0 nil]],
:breakout [[:field 1618 {:temporal-unit :year, :join-alias "TblTrainingGradeLog"}]],
:aggregation [[:aggregation-options [:count] {:name "count"}]],
:source-card-id 34,
:source-metadata
[{:semantic_type :type/Score,
:coercion_strategy nil,
:name "SatisfactionMinScore",
:field_ref [:field 1621 nil],
:effective_type :type/Float,
:id 1621,
:display_name "SatisfactionMinScore",
:fingerprint
{:global {:distinct-count 1, :nil% 0.0},
:type {:type/Number {:min 17.0, :q1 17.0, :q3 17.0, :max 17.0, :sd 0.0, :avg 17.0}}},
:base_type :type/Float}
{:semantic_type nil,
:coercion_strategy nil,
:unit :default,
:name "TrainingDate",
:field_ref [:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :default}],
:effective_type :type/DateTime,
:id 1618,
:display_name "TblTrainingGradeLog → TrainingDate",
:fingerprint
{:global {:distinct-count 6, :nil% 0.0},
:type {:type/DateTime {:earliest "2021-02-28T00:00:00Z", :latest "2021-12-03T00:00:00Z"}}},
:base_type :type/DateTime}
{:semantic_type nil,
:coercion_strategy nil,
:name "Grade",
:field_ref [:field 1617 {:join-alias "TblTrainingGradeLog"}],
:effective_type :type/Float,
:id 1617,
:display_name "TblTrainingGradeLog → Grade",
:fingerprint
{:global {:distinct-count 6, :nil% 0.0},
:type
{:type/Number
{:min 15.0, :q1 16.584119566836076, :q3 18.79516317700457, :max 20.0, :sd 1.369914839202301, :avg 17.72}}},
:base_type :type/Float}
{:semantic_type :type/Category,
:coercion_strategy nil,
:name "TrainerID",
:field_ref [:field 1616 {:join-alias "TblTrainingGradeLog"}],
:effective_type :type/Text,
:id 1616,
:display_name "TblTrainingGradeLog → TrainerID",
:fingerprint
{:global {:distinct-count 2, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 2.0}}},
:base_type :type/Text}],
:order-by [[:asc [:field 1618 {:temporal-unit :year, :join-alias "TblTrainingGradeLog"}]]],
:source-query
{:source-table 189,
:fields
[[:field 1621 nil]
[:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :default}]
[:field 1617 {:join-alias "TblTrainingGradeLog"}]
[:field 1616 {:join-alias "TblTrainingGradeLog"}]],
:joins
[{:alias "TblTrainingGradeLog",
:strategy :inner-join,
:fields
[[:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :default}]
[:field 1617 {:join-alias "TblTrainingGradeLog"}]
[:field 1616 {:join-alias "TblTrainingGradeLog"}]],
:source-table 188,
:condition
[:=
[:field 1622 {:temporal-unit :year}]
[:field 1618 {:join-alias "TblTrainingGradeLog", :temporal-unit :year}]]}]}}},
:async? true},
:data {:rows [], :cols []}}
@sbs Yep, you're filtering by TrainingDate inside the saved question, which comes from a joined table.
Is this broken until next release ?
Can I downgrade my version to a previous one where this issue does not exist, until a fix is available?
@sbs Metabase does not support downgrading officially. You can normally downgrade between minor versions, but major versions can cause a lot of problems. Recommended approach is to revert to your backup.
Hummpf, how do I put it?
I guess, I did not read all the appropriate documentations. And have installed metabase in a docker. I did a pull on my docker to upgrade it. Is there a way back by specifying a version in the docker file ? (or a manual I should read regarding upgrading, backups and how to revert ?)
@sbs If you are new Docker, then I would recommend reading through their documentation. Or use the JAR instead of Docker.
https://www.metabase.com/docs/latest/operations-guide/upgrading-metabase.html
https://www.metabase.com/docs/latest/operations-guide/backing-up-metabase-application-data.html
You would specify the image tag, example:
docker run ...your parameters... metabase/metabase:v0.41.6
Thank for providing the version. I rollbacked to 0.41.6, and it works again. I'll update again some time later.
Hello,
I tried upgrading to v0.44.3 today, and subqueries are still broken. Are there plan to fix htis someday, or shoudl I remain in 0.41.6?
@sbs Try reproducing the problem with the Sample Database, and provide updated stacktraces, since they are likely different.
Without detailed steps on how to reproduce a bug, then it's very difficult to fix problems.
Ok,
I will try that. however, where do I find the sample database, it was not added to my database list?
Regards,
@sbs Admin > Databases > there's a link in the bottom to add the Sample Database.
Hello,
Ok, I did it, and here is the log. Run on the sample database
[56bfbccb-8a12-4af6-8ee8-09106f8f78c5] 2022-09-17T08:31:06+02:00 INFO metabase.api.dataset Source query for this query is Card 74
[56bfbccb-8a12-4af6-8ee8-09106f8f78c5] 2022-09-17T08:31:06+02:00 WARN metabase.query-processor.middleware.upgrade-field-literals Warning: clause [:field "count" {:base-type :type/BigInteger, :join-alias "Question 75"}] refers to a Field that may not be present in the source query. Query may not work as expected. Found: #{"CREATED_AT" "SOURCE" "city" "NAME" "CITY" "longitude" "BIRTH_DATE" "id" "email" "LATITUDE" "ZIP" "latitude" "name" "PASSWORD" "address" "birth_date" "ID" "LONGITUDE" "ADDRESS" "source" "STATE" "state" "password" "created_at" "EMAIL" "zip"}
[56bfbccb-8a12-4af6-8ee8-09106f8f78c5] 2022-09-17T08:31:06+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Column """source"".ID" must be in the GROUP BY list; SQL statement:
-- Metabase:: userID: 2 queryType: MBQL queryHash: 037c6a6512b79354f77625fcfd8934c11ed5562f4b957a3920ef0ca5b78f5560
SELECT "source"."ID" AS "ID", "source"."ADDRESS" AS "ADDRESS", "source"."EMAIL" AS "EMAIL", "source"."PASSWORD" AS "PASSWORD", "source"."NAME" AS "NAME", "source"."CITY" AS "CITY", "source"."LONGITUDE" AS "LONGITUDE", "source"."STATE" AS "STATE", "source"."SOURCE" AS "SOURCE", "source"."BIRTH_DATE" AS "BIRTH_DATE", "source"."ZIP" AS "ZIP", "source"."LATITUDE" AS "LATITUDE", "source"."CREATED_AT" AS "CREATED_AT", "source"."Ratio" AS "Ratio", "source"."Question 75__CREATED_AT_2" AS "Question 75__CREATED_AT", "source"."Question 75__count" AS "Question 75__count" FROM (SELECT "source"."ID" AS "ID", "source"."ADDRESS" AS "ADDRESS", "source"."EMAIL" AS "EMAIL", "source"."PASSWORD" AS "PASSWORD", "source"."NAME" AS "NAME", "source"."CITY" AS "CITY", "source"."LONGITUDE" AS "LONGITUDE", "source"."STATE" AS "STATE", "source"."SOURCE" AS "SOURCE", "source"."BIRTH_DATE" AS "BIRTH_DATE", "source"."ZIP" AS "ZIP", "source"."LATITUDE" AS "LATITUDE", "source"."CREATED_AT" AS "CREATED_AT", (CAST(count(*) AS float) / CASE WHEN "Question 75"."count" = 0 THEN NULL ELSE "Question 75"."count" END) AS "Ratio", "Question 75"."CREATED_AT" AS "Question 75__CREATED_AT", "Question 75"."count" AS "Question 75__count", "Question 75"."CREATED_AT" AS "Question 75__CREATED_AT_2", parsedatetime(year("Question 75"."CREATED_AT"), 'yyyy') AS "Question 75__CREATED_AT_3" FROM (SELECT "PUBLIC"."PEOPLE"."ID" AS "ID", "PUBLIC"."PEOPLE"."ADDRESS" AS "ADDRESS", "PUBLIC"."PEOPLE"."EMAIL" AS "EMAIL", "PUBLIC"."PEOPLE"."PASSWORD" AS "PASSWORD", "PUBLIC"."PEOPLE"."NAME" AS "NAME", "PUBLIC"."PEOPLE"."CITY" AS "CITY", "PUBLIC"."PEOPLE"."LONGITUDE" AS "LONGITUDE", "PUBLIC"."PEOPLE"."STATE" AS "STATE", "PUBLIC"."PEOPLE"."SOURCE" AS "SOURCE", "PUBLIC"."PEOPLE"."BIRTH_DATE" AS "BIRTH_DATE", "PUBLIC"."PEOPLE"."ZIP" AS "ZIP", "PUBLIC"."PEOPLE"."LATITUDE" AS "LATITUDE", "PUBLIC"."PEOPLE"."CREATED_AT" AS "CREATED_AT" FROM "PUBLIC"."PEOPLE" WHERE ("PUBLIC"."PEOPLE"."BIRTH_DATE" >= parsedatetime(year(dateadd('year', CAST(-30 AS long), now())), 'yyyy') AND "PUBLIC"."PEOPLE"."BIRTH_DATE" < parsedatetime(year(now()), 'yyyy'))) "source" LEFT JOIN (SELECT parsedatetime(year("PUBLIC"."ORDERS"."CREATED_AT"), 'yyyy') AS "CREATED_AT", count(*) AS "count" FROM "PUBLIC"."ORDERS" GROUP BY parsedatetime(year("PUBLIC"."ORDERS"."CREATED_AT"), 'yyyy') ORDER BY "count" DESC, parsedatetime(year("PUBLIC"."ORDERS"."CREATED_AT"), 'yyyy') ASC) "Question 75" ON parsedatetime(year("source"."BIRTH_DATE"), 'yyyy') = parsedatetime(year("Question 75"."CREATED_AT"), 'yyyy')) "source" LIMIT 10 [90016-197]
{:database_id 5,
:started_at #t "2022-09-17T06:31:06.285711Z[GMT]",
:via
[{:status :failed,
:class clojure.lang.ExceptionInfo,
:error
"Error executing query: Column \"\"\"source\"\".ID\" must be in the GROUP BY list; SQL statement:\n-- Metabase:: userID: 2 queryType: MBQL queryHash: 037c6a6512b79354f77625fcfd8934c11ed5562f4b957a3920ef0ca5b78f5560\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Ratio\" AS \"Ratio\", \"source\".\"Question 75__CREATED_AT_2\" AS \"Question 75__CREATED_AT\", \"source\".\"Question 75__count\" AS \"Question 75__count\" FROM (SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", (CAST(count(*) AS float) / CASE WHEN \"Question 75\".\"count\" = 0 THEN NULL ELSE \"Question 75\".\"count\" END) AS \"Ratio\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT\", \"Question 75\".\"count\" AS \"Question 75__count\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT_2\", parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy') AS \"Question 75__CREATED_AT_3\" FROM (SELECT \"PUBLIC\".\"PEOPLE\".\"ID\" AS \"ID\", \"PUBLIC\".\"PEOPLE\".\"ADDRESS\" AS \"ADDRESS\", \"PUBLIC\".\"PEOPLE\".\"EMAIL\" AS \"EMAIL\", \"PUBLIC\".\"PEOPLE\".\"PASSWORD\" AS \"PASSWORD\", \"PUBLIC\".\"PEOPLE\".\"NAME\" AS \"NAME\", \"PUBLIC\".\"PEOPLE\".\"CITY\" AS \"CITY\", \"PUBLIC\".\"PEOPLE\".\"LONGITUDE\" AS \"LONGITUDE\", \"PUBLIC\".\"PEOPLE\".\"STATE\" AS \"STATE\", \"PUBLIC\".\"PEOPLE\".\"SOURCE\" AS \"SOURCE\", \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"PUBLIC\".\"PEOPLE\".\"ZIP\" AS \"ZIP\", \"PUBLIC\".\"PEOPLE\".\"LATITUDE\" AS \"LATITUDE\", \"PUBLIC\".\"PEOPLE\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"PEOPLE\" WHERE (\"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" >= parsedatetime(year(dateadd('year', CAST(-30 AS long), now())), 'yyyy') AND \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" < parsedatetime(year(now()), 'yyyy'))) \"source\" LEFT JOIN (SELECT parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') AS \"CREATED_AT\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" GROUP BY parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ORDER BY \"count\" DESC, parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ASC) \"Question 75\" ON parsedatetime(year(\"source\".\"BIRTH_DATE\"), 'yyyy') = parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy')) \"source\" LIMIT 10 [90016-197]",
:stacktrace
["--> driver.sql_jdbc.execute$execute_reducible_query$fn__55230.invoke(execute.clj:502)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
"driver.sql_jdbc$fn__85234.invokeStatic(sql_jdbc.clj:54)"
"driver.sql_jdbc$fn__85234.invoke(sql_jdbc.clj:52)"
"driver.h2$fn__82125.invokeStatic(h2.clj:149)"
"driver.h2$fn__82125.invoke(h2.clj:145)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
"query_processor.context.default$default_runf.invoke(default.clj:65)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___52607.invoke(cache.clj:220)"
"query_processor.middleware.permissions$check_query_permissions$fn__48131.invoke(permissions.clj:109)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51552.invoke(mbql_to_native.clj:23)"
"query_processor$fn__54083$combined_post_process__54088$combined_post_process_STAR___54089.invoke(query_processor.clj:212)"
"query_processor$fn__54083$combined_pre_process__54084$combined_pre_process_STAR___54085.invoke(query_processor.clj:209)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52528$fn__52533.invoke(resolve_database_and_driver.clj:35)"
"driver$do_with_driver.invokeStatic(driver.clj:75)"
"driver$do_with_driver.invoke(driver.clj:71)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52528.invoke(resolve_database_and_driver.clj:34)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48563.invoke(fetch_source_query.clj:342)"
"query_processor.middleware.store$initialize_store$fn__48751$fn__48752.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__48751.invoke(store.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__52800.invoke(normalize_query.clj:22)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__49833.invoke(constraints.clj:53)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__52739.invoke(process_userland_query.clj:145)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__53111.invoke(catch_exceptions.clj:167)"
"query_processor.reducible$async_qp$qp_STAR___44972$thunk__44974.invoke(reducible.clj:100)"
"query_processor.reducible$async_qp$qp_STAR___44972.invoke(reducible.clj:106)"
"query_processor.reducible$async_qp$qp_STAR___44972.invoke(reducible.clj:91)"
"query_processor.reducible$sync_qp$qp_STAR___44983.doInvoke(reducible.clj:126)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:331)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:327)"
"query_processor$fn__54130$process_query_and_save_execution_BANG___54139$fn__54142.invoke(query_processor.clj:342)"
"query_processor$fn__54130$process_query_and_save_execution_BANG___54139.invoke(query_processor.clj:335)"
"query_processor$fn__54174$process_query_and_save_with_max_results_constraints_BANG___54183$fn__54186.invoke(query_processor.clj:354)"
"query_processor$fn__54174$process_query_and_save_with_max_results_constraints_BANG___54183.invoke(query_processor.clj:347)"
"api.dataset$run_query_async$fn__68877.invoke(dataset.clj:69)"
"query_processor.streaming$streaming_response_STAR_$fn__40090$fn__40091.invoke(streaming.clj:162)"
"query_processor.streaming$streaming_response_STAR_$fn__40090.invoke(streaming.clj:161)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
"async.streaming_response$do_f_async$task__19006.invoke(streaming_response.clj:84)"],
:error_type :invalid-query,
:ex-data
{:sql
"-- Metabase:: userID: 2 queryType: MBQL queryHash: 037c6a6512b79354f77625fcfd8934c11ed5562f4b957a3920ef0ca5b78f5560\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Ratio\" AS \"Ratio\", \"source\".\"Question 75__CREATED_AT_2\" AS \"Question 75__CREATED_AT\", \"source\".\"Question 75__count\" AS \"Question 75__count\" FROM (SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", (CAST(count(*) AS float) / CASE WHEN \"Question 75\".\"count\" = 0 THEN NULL ELSE \"Question 75\".\"count\" END) AS \"Ratio\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT\", \"Question 75\".\"count\" AS \"Question 75__count\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT_2\", parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy') AS \"Question 75__CREATED_AT_3\" FROM (SELECT \"PUBLIC\".\"PEOPLE\".\"ID\" AS \"ID\", \"PUBLIC\".\"PEOPLE\".\"ADDRESS\" AS \"ADDRESS\", \"PUBLIC\".\"PEOPLE\".\"EMAIL\" AS \"EMAIL\", \"PUBLIC\".\"PEOPLE\".\"PASSWORD\" AS \"PASSWORD\", \"PUBLIC\".\"PEOPLE\".\"NAME\" AS \"NAME\", \"PUBLIC\".\"PEOPLE\".\"CITY\" AS \"CITY\", \"PUBLIC\".\"PEOPLE\".\"LONGITUDE\" AS \"LONGITUDE\", \"PUBLIC\".\"PEOPLE\".\"STATE\" AS \"STATE\", \"PUBLIC\".\"PEOPLE\".\"SOURCE\" AS \"SOURCE\", \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"PUBLIC\".\"PEOPLE\".\"ZIP\" AS \"ZIP\", \"PUBLIC\".\"PEOPLE\".\"LATITUDE\" AS \"LATITUDE\", \"PUBLIC\".\"PEOPLE\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"PEOPLE\" WHERE (\"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" >= parsedatetime(year(dateadd('year', CAST(-30 AS long), now())), 'yyyy') AND \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" < parsedatetime(year(now()), 'yyyy'))) \"source\" LEFT JOIN (SELECT parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') AS \"CREATED_AT\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" GROUP BY parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ORDER BY \"count\" DESC, parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ASC) \"Question 75\" ON parsedatetime(year(\"source\".\"BIRTH_DATE\"), 'yyyy') = parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy')) \"source\" LIMIT 10",
:params nil,
:type :invalid-query}}],
:state "90016",
:error_type :invalid-query,
:json_query
{:database 5,
:query
{:source-table "card__74",
:joins
[{:fields "all",
:source-table "card__75",
:condition
["=" ["field" 1691 {:temporal-unit "year"}] ["field" 1679 {:join-alias "Question 75", :temporal-unit "year"}]],
:alias "Question 75"}],
:expressions {:Ratio ["/" ["count"] ["field" "count" {:base-type "type/BigInteger", :join-alias "Question 75"}]]},
:limit 10},
:type "query",
:parameters [],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:native
{:query
"SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Ratio\" AS \"Ratio\", \"source\".\"Question 75__CREATED_AT_2\" AS \"Question 75__CREATED_AT\", \"source\".\"Question 75__count\" AS \"Question 75__count\" FROM (SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", (CAST(count(*) AS float) / CASE WHEN \"Question 75\".\"count\" = 0 THEN NULL ELSE \"Question 75\".\"count\" END) AS \"Ratio\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT\", \"Question 75\".\"count\" AS \"Question 75__count\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT_2\", parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy') AS \"Question 75__CREATED_AT_3\" FROM (SELECT \"PUBLIC\".\"PEOPLE\".\"ID\" AS \"ID\", \"PUBLIC\".\"PEOPLE\".\"ADDRESS\" AS \"ADDRESS\", \"PUBLIC\".\"PEOPLE\".\"EMAIL\" AS \"EMAIL\", \"PUBLIC\".\"PEOPLE\".\"PASSWORD\" AS \"PASSWORD\", \"PUBLIC\".\"PEOPLE\".\"NAME\" AS \"NAME\", \"PUBLIC\".\"PEOPLE\".\"CITY\" AS \"CITY\", \"PUBLIC\".\"PEOPLE\".\"LONGITUDE\" AS \"LONGITUDE\", \"PUBLIC\".\"PEOPLE\".\"STATE\" AS \"STATE\", \"PUBLIC\".\"PEOPLE\".\"SOURCE\" AS \"SOURCE\", \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"PUBLIC\".\"PEOPLE\".\"ZIP\" AS \"ZIP\", \"PUBLIC\".\"PEOPLE\".\"LATITUDE\" AS \"LATITUDE\", \"PUBLIC\".\"PEOPLE\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"PEOPLE\" WHERE (\"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" >= parsedatetime(year(dateadd('year', CAST(-30 AS long), now())), 'yyyy') AND \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" < parsedatetime(year(now()), 'yyyy'))) \"source\" LEFT JOIN (SELECT parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') AS \"CREATED_AT\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" GROUP BY parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ORDER BY \"count\" DESC, parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ASC) \"Question 75\" ON parsedatetime(year(\"source\".\"BIRTH_DATE\"), 'yyyy') = parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy')) \"source\" LIMIT 10",
:params nil},
:status :failed,
:class org.h2.jdbc.JdbcSQLException,
:stacktrace
["org.h2.message.DbException.getJdbcSQLException(DbException.java:357)"
"org.h2.message.DbException.get(DbException.java:179)"
"org.h2.message.DbException.get(DbException.java:155)"
"org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:172)"
"org.h2.expression.Alias.updateAggregate(Alias.java:87)"
"org.h2.command.dml.Select.queryGroup(Select.java:350)"
"org.h2.command.dml.Select.queryWithoutCache(Select.java:628)"
"org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)"
"org.h2.command.dml.Query.query(Query.java:371)"
"org.h2.command.dml.Query.query(Query.java:333)"
"org.h2.index.ViewIndex.find(ViewIndex.java:288)"
"org.h2.index.ViewIndex.find(ViewIndex.java:160)"
"org.h2.index.BaseIndex.find(BaseIndex.java:130)"
"org.h2.index.IndexCursor.find(IndexCursor.java:176)"
"org.h2.table.TableFilter.next(TableFilter.java:471)"
"org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1452)"
"org.h2.result.LazyResult.hasNext(LazyResult.java:79)"
"org.h2.result.LazyResult.next(LazyResult.java:59)"
"org.h2.command.dml.Select.queryFlat(Select.java:527)"
"org.h2.command.dml.Select.queryWithoutCache(Select.java:633)"
"org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)"
"org.h2.command.dml.Query.query(Query.java:371)"
"org.h2.command.dml.Query.query(Query.java:333)"
"org.h2.command.CommandContainer.query(CommandContainer.java:114)"
"org.h2.command.Command.executeQuery(Command.java:202)"
"org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:227)"
"org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)"
"com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
"--> driver.sql_jdbc.execute$fn__55150.invokeStatic(execute.clj:367)"
"driver.sql_jdbc.execute$fn__55150.invoke(execute.clj:365)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:375)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:372)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__55230.invoke(execute.clj:500)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
"driver.sql_jdbc$fn__85234.invokeStatic(sql_jdbc.clj:54)"
"driver.sql_jdbc$fn__85234.invoke(sql_jdbc.clj:52)"
"driver.h2$fn__82125.invokeStatic(h2.clj:149)"
"driver.h2$fn__82125.invoke(h2.clj:145)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
"query_processor.context.default$default_runf.invoke(default.clj:65)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___52607.invoke(cache.clj:220)"
"query_processor.middleware.permissions$check_query_permissions$fn__48131.invoke(permissions.clj:109)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51552.invoke(mbql_to_native.clj:23)"
"query_processor$fn__54083$combined_post_process__54088$combined_post_process_STAR___54089.invoke(query_processor.clj:212)"
"query_processor$fn__54083$combined_pre_process__54084$combined_pre_process_STAR___54085.invoke(query_processor.clj:209)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52528$fn__52533.invoke(resolve_database_and_driver.clj:35)"
"driver$do_with_driver.invokeStatic(driver.clj:75)"
"driver$do_with_driver.invoke(driver.clj:71)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52528.invoke(resolve_database_and_driver.clj:34)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48563.invoke(fetch_source_query.clj:342)"
"query_processor.middleware.store$initialize_store$fn__48751$fn__48752.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__48751.invoke(store.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__52800.invoke(normalize_query.clj:22)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__49833.invoke(constraints.clj:53)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__52739.invoke(process_userland_query.clj:145)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__53111.invoke(catch_exceptions.clj:167)"
"query_processor.reducible$async_qp$qp_STAR___44972$thunk__44974.invoke(reducible.clj:100)"
"query_processor.reducible$async_qp$qp_STAR___44972.invoke(reducible.clj:106)"
"query_processor.reducible$async_qp$qp_STAR___44972.invoke(reducible.clj:91)"
"query_processor.reducible$sync_qp$qp_STAR___44983.doInvoke(reducible.clj:126)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:331)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:327)"
"query_processor$fn__54130$process_query_and_save_execution_BANG___54139$fn__54142.invoke(query_processor.clj:342)"
"query_processor$fn__54130$process_query_and_save_execution_BANG___54139.invoke(query_processor.clj:335)"
"query_processor$fn__54174$process_query_and_save_with_max_results_constraints_BANG___54183$fn__54186.invoke(query_processor.clj:354)"
"query_processor$fn__54174$process_query_and_save_with_max_results_constraints_BANG___54183.invoke(query_processor.clj:347)"
"api.dataset$run_query_async$fn__68877.invoke(dataset.clj:69)"
"query_processor.streaming$streaming_response_STAR_$fn__40090$fn__40091.invoke(streaming.clj:162)"
"query_processor.streaming$streaming_response_STAR_$fn__40090.invoke(streaming.clj:161)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
"async.streaming_response$do_f_async$task__19006.invoke(streaming_response.clj:84)"],
:card_id 74,
:context :ad-hoc,
:error
"Column \"\"\"source\"\".ID\" must be in the GROUP BY list; SQL statement:\n-- Metabase:: userID: 2 queryType: MBQL queryHash: 037c6a6512b79354f77625fcfd8934c11ed5562f4b957a3920ef0ca5b78f5560\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Ratio\" AS \"Ratio\", \"source\".\"Question 75__CREATED_AT_2\" AS \"Question 75__CREATED_AT\", \"source\".\"Question 75__count\" AS \"Question 75__count\" FROM (SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"ADDRESS\" AS \"ADDRESS\", \"source\".\"EMAIL\" AS \"EMAIL\", \"source\".\"PASSWORD\" AS \"PASSWORD\", \"source\".\"NAME\" AS \"NAME\", \"source\".\"CITY\" AS \"CITY\", \"source\".\"LONGITUDE\" AS \"LONGITUDE\", \"source\".\"STATE\" AS \"STATE\", \"source\".\"SOURCE\" AS \"SOURCE\", \"source\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"source\".\"ZIP\" AS \"ZIP\", \"source\".\"LATITUDE\" AS \"LATITUDE\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", (CAST(count(*) AS float) / CASE WHEN \"Question 75\".\"count\" = 0 THEN NULL ELSE \"Question 75\".\"count\" END) AS \"Ratio\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT\", \"Question 75\".\"count\" AS \"Question 75__count\", \"Question 75\".\"CREATED_AT\" AS \"Question 75__CREATED_AT_2\", parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy') AS \"Question 75__CREATED_AT_3\" FROM (SELECT \"PUBLIC\".\"PEOPLE\".\"ID\" AS \"ID\", \"PUBLIC\".\"PEOPLE\".\"ADDRESS\" AS \"ADDRESS\", \"PUBLIC\".\"PEOPLE\".\"EMAIL\" AS \"EMAIL\", \"PUBLIC\".\"PEOPLE\".\"PASSWORD\" AS \"PASSWORD\", \"PUBLIC\".\"PEOPLE\".\"NAME\" AS \"NAME\", \"PUBLIC\".\"PEOPLE\".\"CITY\" AS \"CITY\", \"PUBLIC\".\"PEOPLE\".\"LONGITUDE\" AS \"LONGITUDE\", \"PUBLIC\".\"PEOPLE\".\"STATE\" AS \"STATE\", \"PUBLIC\".\"PEOPLE\".\"SOURCE\" AS \"SOURCE\", \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" AS \"BIRTH_DATE\", \"PUBLIC\".\"PEOPLE\".\"ZIP\" AS \"ZIP\", \"PUBLIC\".\"PEOPLE\".\"LATITUDE\" AS \"LATITUDE\", \"PUBLIC\".\"PEOPLE\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"PEOPLE\" WHERE (\"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" >= parsedatetime(year(dateadd('year', CAST(-30 AS long), now())), 'yyyy') AND \"PUBLIC\".\"PEOPLE\".\"BIRTH_DATE\" < parsedatetime(year(now()), 'yyyy'))) \"source\" LEFT JOIN (SELECT parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') AS \"CREATED_AT\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" GROUP BY parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ORDER BY \"count\" DESC, parsedatetime(year(\"PUBLIC\".\"ORDERS\".\"CREATED_AT\"), 'yyyy') ASC) \"Question 75\" ON parsedatetime(year(\"source\".\"BIRTH_DATE\"), 'yyyy') = parsedatetime(year(\"Question 75\".\"CREATED_AT\"), 'yyyy')) \"source\" LIMIT 10 [90016-197]",
:row_count 0,
:running_time 0,
:preprocessed
{:database 5,
:query
{:expressions {"Ratio" [:/ [:count] [:field "count" {:base-type :type/BigInteger, :join-alias "Question 75"}]]},
:limit 10,
:source-card-id 74,
:source-metadata
[{:description "A unique identifier given to each user.",
:semantic_type :type/PK,
:coercion_strategy nil,
:name "ID",
:settings nil,
:field_ref [:field 1697 nil],
:effective_type :type/BigInteger,
:id 1697,
:visibility_type :normal,
:display_name "ID",
:fingerprint nil,
:base_type :type/BigInteger}
{:description "The street address of the account’s billing address",
:semantic_type nil,
:coercion_strategy nil,
:name "ADDRESS",
:settings nil,
:field_ref [:field 1688 nil],
:effective_type :type/Text,
:id 1688,
:visibility_type :normal,
:display_name "Address",
:fingerprint
{:global {:distinct-count 2490, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 20.85}}},
:base_type :type/Text}
{:description "The contact email for the account.",
:semantic_type :type/Email,
:coercion_strategy nil,
:name "EMAIL",
:settings nil,
:field_ref [:field 1690 nil],
:effective_type :type/Text,
:id 1690,
:visibility_type :normal,
:display_name "Email",
:fingerprint
{:global {:distinct-count 2500, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 1.0, :percent-state 0.0, :average-length 24.1824}}},
:base_type :type/Text}
{:description "This is the salted password of the user. It should not be visible",
:semantic_type nil,
:coercion_strategy nil,
:name "PASSWORD",
:settings nil,
:field_ref [:field 1686 nil],
:effective_type :type/Text,
:id 1686,
:visibility_type :normal,
:display_name "Password",
:fingerprint
{:global {:distinct-count 2500, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 36.0}}},
:base_type :type/Text}
{:description "The name of the user who owns an account",
:semantic_type :type/Name,
:coercion_strategy nil,
:name "NAME",
:settings nil,
:field_ref [:field 1689 nil],
:effective_type :type/Text,
:id 1689,
:visibility_type :normal,
:display_name "Name",
:fingerprint
{:global {:distinct-count 2499, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 13.532}}},
:base_type :type/Text}
{:description "The city of the account’s billing address",
:semantic_type :type/City,
:coercion_strategy nil,
:name "CITY",
:settings nil,
:field_ref [:field 1696 nil],
:effective_type :type/Text,
:id 1696,
:visibility_type :normal,
:display_name "City",
:fingerprint
{:global {:distinct-count 1966, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.002, :average-length 8.284}}},
:base_type :type/Text}
{:description
"This is the longitude of the user on sign-up. It might be updated in the future to the last seen location.",
:semantic_type :type/Longitude,
:coercion_strategy nil,
:name "LONGITUDE",
:settings nil,
:field_ref [:field 1687 nil],
:effective_type :type/Float,
:id 1687,
:visibility_type :normal,
:display_name "Longitude",
:fingerprint
{:global {:distinct-count 2491, :nil% 0.0},
:type
{:type/Number
{:min -166.5425726,
:q1 -101.58350792373135,
:q3 -84.65289348288829,
:max -67.96735199999999,
:sd 15.399698968175663,
:avg -95.18741780363999}}},
:base_type :type/Float}
{:description "The state or province of the account’s billing address",
:semantic_type :type/State,
:coercion_strategy nil,
:name "STATE",
:settings nil,
:field_ref [:field 1692 nil],
:effective_type :type/Text,
:id 1692,
:visibility_type :normal,
:display_name "State",
:fingerprint
{:global {:distinct-count 49, :nil% 0.0},
:type
{:type/Text {:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 1.0, :average-length 2.0}}},
:base_type :type/Text}
{:description
"The channel through which we acquired this user. Valid values include: Affiliate, Facebook, Google, Organic and Twitter",
:semantic_type :type/Source,
:coercion_strategy nil,
:name "SOURCE",
:settings nil,
:field_ref [:field 1693 nil],
:effective_type :type/Text,
:id 1693,
:visibility_type :normal,
:display_name "Source",
:fingerprint
{:global {:distinct-count 5, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 7.4084}}},
:base_type :type/Text}
{:description "The date of birth of the user",
:semantic_type nil,
:coercion_strategy nil,
:unit :default,
:name "BIRTH_DATE",
:settings nil,
:field_ref [:field 1691 {:temporal-unit :default}],
:effective_type :type/Date,
:id 1691,
:visibility_type :normal,
:display_name "Birth Date",
:fingerprint
{:global {:distinct-count 2308, :nil% 0.0},
:type {:type/DateTime {:earliest "1958-04-26", :latest "2000-04-03"}}},
:base_type :type/Date}
{:description "The postal code of the account’s billing address",
:semantic_type :type/ZipCode,
:coercion_strategy nil,
:name "ZIP",
:settings nil,
:field_ref [:field 1695 nil],
:effective_type :type/Text,
:id 1695,
:visibility_type :normal,
:display_name "Zip",
:fingerprint
{:global {:distinct-count 2234, :nil% 0.0},
:type
{:type/Text {:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 5.0}}},
:base_type :type/Text}
{:description
"This is the latitude of the user on sign-up. It might be updated in the future to the last seen location.",
:semantic_type :type/Latitude,
:coercion_strategy nil,
:name "LATITUDE",
:settings nil,
:field_ref [:field 1694 nil],
:effective_type :type/Float,
:id 1694,
:visibility_type :normal,
:display_name "Latitude",
:fingerprint
{:global {:distinct-count 2491, :nil% 0.0},
:type
{:type/Number
{:min 25.775827,
:q1 35.302705923023126,
:q3 43.773802584662,
:max 70.6355001,
:sd 6.390832341883712,
:avg 39.87934670484002}}},
:base_type :type/Float}
{:description "The date the user record was created. Also referred to as the user’s \"join date\"",
:semantic_type :type/CreationTimestamp,
:coercion_strategy nil,
:unit :default,
:name "CREATED_AT",
:settings nil,
:field_ref [:field 1685 {:temporal-unit :default}],
:effective_type :type/DateTime,
:id 1685,
:visibility_type :normal,
:display_name "Created At",
:fingerprint
{:global {:distinct-count 2500, :nil% 0.0},
:type {:type/DateTime {:earliest "2016-04-19T21:35:18.752Z", :latest "2019-04-19T14:06:27.3Z"}}},
:base_type :type/DateTime}],
:fields
[[:field 1697 nil]
[:field 1688 nil]
[:field 1690 nil]
[:field 1686 nil]
[:field 1689 nil]
[:field 1696 nil]
[:field 1687 nil]
[:field 1692 nil]
[:field 1693 nil]
[:field 1691 {:temporal-unit :default}]
[:field 1695 nil]
[:field 1694 nil]
[:field 1685 {:temporal-unit :default}]
[:expression "Ratio"]
[:field 1679 {:join-alias "Question 75"}]
[:field "count" {:base-type :type/BigInteger, :join-alias "Question 75"}]],
:joins
[{:alias "Question 75",
:strategy :left-join,
:fields
[[:field 1679 {:join-alias "Question 75"}]
[:field "count" {:base-type :type/BigInteger, :join-alias "Question 75"}]],
:condition
[:= [:field 1691 {:temporal-unit :year}] [:field 1679 {:join-alias "Question 75", :temporal-unit :year}]],
:source-card-id 75,
:source-query
{:source-table 227,
:aggregation [[:aggregation-options [:count] {:name "count"}]],
:breakout [[:field 1679 {:temporal-unit :year}]],
:order-by [[:desc [:aggregation 0 nil]] [:asc [:field 1679 {:temporal-unit :year}]]]},
:source-metadata
[{:description "The date and time an order was submitted.",
:semantic_type :type/CreationTimestamp,
:table_id 227,
:coercion_strategy nil,
:unit :year,
:name "CREATED_AT",
:settings nil,
:source :breakout,
:field_ref [:field 1679 {:temporal-unit :year}],
:effective_type :type/DateTime,
:nfc_path nil,
:parent_id nil,
:id 1679,
:visibility_type :normal,
:display_name "Created At",
:fingerprint
{:global {:distinct-count 9998, :nil% 0.0},
:type {:type/DateTime {:earliest "2016-04-30T18:56:13.352Z", :latest "2020-04-19T14:07:15.657Z"}}},
:base_type :type/DateTime}
{:base_type :type/BigInteger,
:semantic_type :type/Quantity,
:name "count",
:display_name "Count",
:source :aggregation,
:field_ref [:aggregation 0]}]}],
:source-query
{:source-table 228,
:filter
[:and
[:>= [:field 1691 {:temporal-unit :default}] [:relative-datetime -30 :year]]
[:< [:field 1691 {:temporal-unit :default}] [:relative-datetime 0 :year]]],
:fields
[[:field 1697 nil]
[:field 1688 nil]
[:field 1690 nil]
[:field 1686 nil]
[:field 1689 nil]
[:field 1696 nil]
[:field 1687 nil]
[:field 1692 nil]
[:field 1693 nil]
[:field 1691 {:temporal-unit :default}]
[:field 1695 nil]
[:field 1694 nil]
[:field 1685 {:temporal-unit :default}]]}},
:type :query,
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
:info {:executed-by 2, :context :ad-hoc, :card-id 74}},
:data {:rows [], :cols []}}
[56bfbccb-8a12-4af6-8ee8-09106f8f78c5] 2022-09-17T08:31:06+02:00 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 599.5 ms (43 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (117 total active threads) Queries in flight: 0 (0 queued); h2 DB 5 connections: 0/1 (0 threads blocked)
Version info is :
{
"browser-info": {
"language": "fr-FR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.102 Safari/537.36 OPR/90.0.4480.84",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16.1+1",
"os.name": "Linux",
"os.version": "5.4.0-125-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mysql",
"sqlserver",
"h2"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-02",
"tag": "v0.44.3",
"branch": "release-x.44.x",
"hash": "7d50282"
},
"settings": {
"report-timezone": null
}
}
}
To give a little background of what I try to achieve, I have a table with user, date, grade
I have a question to get the count of user per year (let say Q1)
I have a question to get the count of user per year with passing grades (ie grade > value)
And I try to make a new question joining both table the yea and gen a new column [Count] / [Q1->Count].
This is the failure which stack trace is shown above.
On my own database which is running on SQLServer the error is Invalid column name 'TrainingDate'.
Regards,
@sbs Okay, so your Sample fails because you are using Count (the function) in a Custom Column, which you cannot do. If you change to the column [Count] then it works.
Try having a look at https://github.com/metabase/metabase/issues/20809 or https://github.com/metabase/metabase/issues/18512 or one of the many issues I've created, then open an issue with clear steps-to-reproduce.
Also, since you are clearly using Metabase in production, then you should migrate away from H2:
https://www.metabase.com/docs/latest/installation-and-operation/migrating-from-h2
Hello,
You are correct, I had a typo and did not put square brackets around count.
However doing so, metabase does not seem to cooperate and breaks the reference to the joined query count. (see screengrab).
https://imgur.com/a/xdNpAEX
Am I missing something?
(I will look into migrating away from H2)
PS : Sorry for the link, I do not know how I can upload a small videoscreengrab in discourse
@sbs Try looking at the issues I've created and then just provide screenshots or a video showing every step.
Looking at your video it changes from [count] to Count (the function). I don't think that table has a summary.