Hi,
Since leaving 1.37.9 (we are on 1.38.1 now) some of our questions are not working anymore. To be precise questions with "multilevel aggregations and a filter in the laster section" are not working anymore. The issue seems to be mentioned (and resolved it seems ?) here. However our upgrade to 1.38.1 hasn't fixed the questions. Instead the following error message is appearing:
Here is an example of question that is failing:
Thank you for your help
Hi @anton1
But the Filter is not the last section - Summarize with "Average of Count" is the last section.
Please post "Diagnostic Info" from Admin > Troubleshooting.
Without the stacktrace from Admin > Troubleshooting > Logs, it's going to be difficult to figure out why it's failing - and when the database type is unknown, then it's even harder to reproduce.
True but the filter still seems to be the cause of the problem. When I remove the filter the question works.
Here is the diagnostic info:
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "4.14.186-146.268.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"bigquery"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.7"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-03-03",
"tag": "v1.38.1",
"branch": "release-x.38.x",
"hash": "79ef63a"
},
"settings": {
"report-timezone": null
}
}
}
@anton1 You are redacting too much, so it's not possible to see what is failing. I cannot reproduce on BigQuery and I've tried a few different table and filter combinations.
The stacktrace would be very useful.
Hi @flamber thank you for your response and sorry for coming back to you only now. Here is the stacktrace just after refreshing one of the questions that isn't working. Hope this helps. Thanks !
{:query
"SELECT avg(`source`.`count`) AS `avg` FROM (SELECT timestamp_trunc(`timestamps`.`to_assigned_lead`, month) AS `timestamps__to_assigned_lead`, `experts`.`expert_first_name` AS `experts__expert_first_name`, count(*) AS `count` FROM `business_production.sales` LEFT JOIN `business_production.timestamps` `timestamps` ON `business_production.sales`.`id` = `timestamps`.`sale_id` LEFT JOIN `business_production.experts` `experts` ON `business_production.sales`.`expert_id` = `experts`.`id` WHERE `timestamps`.`to_assigned_lead` IS NOT NULL GROUP BY `timestamps__to_assigned_lead`, `experts__expert_first_name` ORDER BY `timestamps__to_assigned_lead` ASC, `experts__expert_first_name` ASC) `source` WHERE datetime_trunc(CAST(`business_production.source`.`timestamps__to_assigned_lead` AS datetime), month) BETWEEN CAST(datetime_trunc(datetime_add(current_datetime(), INTERVAL -6 month), month) AS timestamp) AND CAST(datetime_trunc(current_datetime(), month) AS timestamp)",
:params nil,
:table-name "source",
:mbql? true},
:status :failed,
:class clojure.lang.ExceptionInfo,
:stacktrace
["--> driver.google$execute_no_auto_retry.invokeStatic(google.clj:41)"
"driver.google$execute_no_auto_retry.invoke(google.clj:33)"
"driver.google$execute.invokeStatic(google.clj:55)"
"driver.google$execute.invoke(google.clj:45)"
"driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:216)"
"driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
"driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:205)"
"driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
"driver.bigquery$process_native_STAR_$thunk__1596.invoke(bigquery.clj:277)"
"driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:279)"
"driver.bigquery$process_native_STAR_.invoke(bigquery.clj:272)"
"driver.bigquery$eval1600$fn__1602.invoke(bigquery.clj:299)"
"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__47232.invoke(mbql_to_native.clj:25)"
"query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:41)"
"query_processor.middleware.limit$limit$fn__47218.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__46142.invoke(cache.clj:211)"
"query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47397.invoke(optimize_datetime_filters.clj:133)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45288.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__40740.invoke(wrap_value_literals.clj:147)"
"query_processor.middleware.annotate$add_column_info$fn__40625.invoke(annotate.clj:582)"
"query_processor.middleware.permissions$check_query_permissions$fn__46359.invoke(permissions.clj:75)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__47920.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
"metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__49492.invoke(row_level_restrictions.clj:331)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48233.invoke(resolve_joined_fields.clj:94)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__48538.invoke(resolve_joins.clj:178)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44882.invoke(add_implicit_joins.clj:181)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47193.invoke(large_int_id.clj:44)"
"query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
"query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__45648.invoke(binning.clj:228)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__46166.invoke(resolve_fields.clj:24)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__44512.invoke(add_dimension_projections.clj:316)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44743.invoke(add_implicit_clauses.clj:146)"
"metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__49492.invoke(row_level_restrictions.clj:331)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48968.invoke(upgrade_field_literals.clj:45)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__45035.invoke(add_source_metadata.clj:122)"
"metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__49009.invoke(column_level_perms_check.clj:25)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48117.invoke(reconcile_breakout_and_order_by_bucketing.clj:97)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45235.invoke(auto_bucket_datetimes.clj:139)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46213.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__47902.invoke(parameters.clj:111)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46265.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__45044.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__48904.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48128$fn__48132.invoke(resolve_database_and_driver.clj:31)"
"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__48128.invoke(resolve_database_and_driver.clj:25)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
"query_processor.middleware.store$initialize_store$fn__48913$fn__48914.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__48913.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__48975.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__47245.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44900.invoke(add_rows_truncated.clj:35)"
"metabase_enterprise.audit.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__31310.invoke(handle_audit_queries.clj:162)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__48889.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__47991.invoke(process_userland_query.clj:135)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__46443.invoke(catch_exceptions.clj:173)"
"query_processor.reducible$async_qp$qp_STAR___33118$thunk__33119.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___33118$fn__33121.invoke(reducible.clj:108)"],
:context :question,
:error "Unrecognized name: `business_production.source` at [2:720]",
:row_count 0,
:running_time 0,
:preprocessed
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:js-int-to-string? true},
:user-parameters
[{:type :date/all-options,
:target [:dimension [:field-literal "to_assigned_lead" :type/DateTimeWithLocalTZ]],
:value "past6months~"}],
:info
{:executed-by 68,
:context :question,
:card-id 519,
:query-hash
[-62, -73, 87, 121, -57, 89, 57, 40, 49, -127, -23, 26, -112, -56, 37, -14, 81, -48, -7, -70, -87, -42, 97, -98, 30,
75, 35, 117, -121, 81, 24, 112]},
:database 35,
:query
{:aggregation [[:aggregation-options [:avg [:field-literal "count" :type/Integer]] {:name "avg"}]],
:filter
[:between
[:datetime-field [:joined-field "timestamps" [:field-id 4404]] :month]
[:relative-datetime -6 :month]
[:relative-datetime 0 :month]],
:source-metadata
[{:table_id 315,
:special_type nil,
:unit :month,
:name "to_assigned_lead",
:settings nil,
:field_ref [:datetime-field [:joined-field "timestamps" [:field-id 4404]] :month],
:parent_id nil,
:id 4404,
:display_name "timestamps → to_assigned_lead",
:fingerprint
{:global {:distinct-count 9481, :nil% 0.052},
:type {:type/DateTime {:earliest "2019-07-19T09:29:26.451Z", :latest "2020-11-05T14:11:15.916Z"}}},
:base_type :type/DateTimeWithLocalTZ,
:source_alias "timestamps"}
{:table_id 314,
:special_type :type/Category,
:name "expert_first_name",
:settings nil,
:field_ref [:joined-field "experts" [:field-id 4386]],
:parent_id nil,
:id 4386,
:display_name "experts → expert_first_name",
:fingerprint
{:global {:distinct-count 18, :nil% 0.0},
:type
{:type/Text
{:percent-json 0.0,
:percent-url 0.0,
:percent-email 0.0,
:percent-state 0.0,
:average-length 6.777777777777778}}},
:base_type :type/Text,
:source_alias "experts"}
{:name "count",
:display_name "Count",
:base_type :type/BigInteger,
:special_type :type/Number,
:field_ref [:aggregation 0]}],
:source-query
{:source-table 318,
:filter
[:!=
[:joined-field "timestamps" [:field-id 4404]]
[:value
nil
{:base_type :type/DateTimeWithLocalTZ,
:special_type nil,
:database_type "TIMESTAMP",
:name "to_assigned_lead",
:unit :default}]],
:aggregation [[:aggregation-options [:count] {:name "count"}]],
:breakout
[[:datetime-field [:joined-field "timestamps" [:field-id 4404]] :month]
[:joined-field "experts" [:field-id 4386]]],
:order-by
[[:asc [:datetime-field [:joined-field "timestamps" [:field-id 4404]] :month]]
[:asc [:joined-field "experts" [:field-id 4386]]]],
:joins
[{:strategy :left-join,
:source-table 315,
:condition [:= [:field-id 4476] [:joined-field "timestamps" [:field-id 4400]]],
:alias "timestamps"}
{:strategy :left-join,
:source-table 314,
:condition [:= [:field-id 4446] [:joined-field "experts" [:field-id 4391]]],
:alias "experts"}]}},
:async? true},
:ex-data
{"code" 400,
"errors"
[{"domain" "global", "location" "q", "locationType" "parameter", "message" "Unrecognized name: `business_production.source` at [2:720]", "reason" "invalidQuery"}],
"message" "Unrecognized name: `business_production.source` at [2:720]",
"status" "INVALID_ARGUMENT"},
:data {:rows [], :cols []}}
[a42e17d7-a9be-4c6a-a832-30480e71c6a0] 2021-03-26T10:41:44+01:00 DEBUG metabase.server.middleware.log POST /api/card/519/query 202 [ASYNC: completed] 11.2 s (19 appels de DB) Connexions App DB : 0/15 Threads Jetty : 2/50 (47 inactif, 0 en file dattente) (220 total de threads actifs) Requêtes en cours : 10 (0 en file dattente)
[a42e17d7-a9be-4c6a-a832-30480e71c6a0] 2021-03-26T10:41:44+01:00 ERROR metabase.query-processor.middleware.catch-exceptions Erreur lors du traitement de la requête : null
{:database_id 35,
:started_at #t "2021-03-26T09:41:33.668502Z[GMT]",
:via
[{:status :failed,
:class clojure.lang.ExceptionInfo,
:error "Error executing query",
:stacktrace
["--> driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:224)"
"driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
"driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:205)"
"driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
"driver.bigquery$process_native_STAR_$thunk__1596.invoke(bigquery.clj:277)"
"driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:279)"
"driver.bigquery$process_native_STAR_.invoke(bigquery.clj:272)"
"driver.bigquery$eval1600$fn__1602.invoke(bigquery.clj:299)"
"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__47232.invoke(mbql_to_native.clj:25)"
"query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:41)"
"query_processor.middleware.limit$limit$fn__47218.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__46142.invoke(cache.clj:211)"
"query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47397.invoke(optimize_datetime_filters.clj:133)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45288.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__40740.invoke(wrap_value_literals.clj:147)"
"query_processor.middleware.annotate$add_column_info$fn__40625.invoke(annotate.clj:582)"
"query_processor.middleware.permissions$check_query_permissions$fn__46359.invoke(permissions.clj:75)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__47920.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
"metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__49492.invoke(row_level_restrictions.clj:331)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48233.invoke(resolve_joined_fields.clj:94)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__48538.invoke(resolve_joins.clj:178)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44882.invoke(add_implicit_joins.clj:181)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47193.invoke(large_int_id.clj:44)"
"query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
"query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__45648.invoke(binning.clj:228)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__46166.invoke(resolve_fields.clj:24)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__44512.invoke(add_dimension_projections.clj:316)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44743.invoke(add_implicit_clauses.clj:146)"
"metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__49492.invoke(row_level_restrictions.clj:331)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48968.invoke(upgrade_field_literals.clj:45)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__45035.invoke(add_source_metadata.clj:122)"
"metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__49009.invoke(column_level_perms_check.clj:25)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48117.invoke(reconcile_breakout_and_order_by_bucketing.clj:97)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45235.invoke(auto_bucket_datetimes.clj:139)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46213.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__47902.invoke(parameters.clj:111)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46265.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__45044.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__48904.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48128$fn__48132.invoke(resolve_database_and_driver.clj:31)"
"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__48128.invoke(resolve_database_and_driver.clj:25)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
"query_processor.middleware.store$initialize_store$fn__48913$fn__48914.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__48913.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__48975.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__47245.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44900.invoke(add_rows_truncated.clj:35)"
"metabase_enterprise.audit.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__31310.invoke(handle_audit_queries.clj:162)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__48889.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__47991.invoke(process_userland_query.clj:135)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__46443.invoke(catch_exceptions.clj:173)"
"query_processor.reducible$async_qp$qp_STAR___33118$thunk__33119.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___33118$fn__33121.invoke(reducible.clj:108)"],
:error_type :invalid-query,
:ex-data
{:type :invalid-query,
:sql
"-- Metabase:: userID: 68 queryType: MBQL queryHash: 1a425277c71e2dfb8667bc6791407ae3e6eee221a817ba734d409b5fde42ae7f\nSELECT `source`.`experts__expert_first_name` AS `experts__expert_first_name`, avg(`source`.`count`) AS `avg` FROM (SELECT `experts`.`expert_first_name` AS `experts__expert_first_name`, timestamp_trunc(`timestamps`.`to_awaiting_sale_agreement`, month) AS `timestamps__to_awaiting_sale_agreement`, count(*) AS `count` FROM `business_production.sales` LEFT JOIN `business_production.timestamps` `timestamps` ON `business_production.sales`.`id` = `timestamps`.`sale_id` LEFT JOIN `business_production.experts` `experts` ON `business_production.sales`.`expert_id` = `experts`.`id` WHERE (`timestamps`.`to_awaiting_sale_agreement` IS NOT NULL AND `experts`.`expert_first_name` IS NOT NULL AND (`experts`.`expert_first_name` <> ? OR `experts`.`expert_first_name` IS NULL)) GROUP BY `experts__expert_first_name`, `timestamps__to_awaiting_sale_agreement` ORDER BY `experts__expert_first_name` ASC, `timestamps__to_awaiting_sale_agreement` ASC) `source` WHERE datetime_trunc(CAST(`business_production.source`.`timestamps__to_awaiting_sale_agreement` AS datetime), month) BETWEEN CAST(datetime_trunc(datetime_add(current_datetime(), INTERVAL -6 month), month) AS timestamp) AND CAST(datetime_trunc(current_datetime(), month) AS timestamp) GROUP BY `experts__expert_first_name` ORDER BY `avg` DESC, `experts__expert_first_name` ASC LIMIT 1",
:parameters ("")}}],
:error_type :invalid-query,
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:js-int-to-string? true},
:database 35,
:query
{:source-query
{:source-table 318,
:joins
[{:fields :all,
:source-table 315,
:condition [:= [:field-id 4476] [:joined-field "timestamps" [:field-id 4400]]],
:alias "timestamps"}
{:fields :all,
:source-table 314,
:condition [:= [:field-id 4446] [:joined-field "experts" [:field-id 4391]]],
:alias "experts"}],
:aggregation [[:count]],
:filter
[:and
[:not-null [:joined-field "timestamps" [:field-id 4394]]]
[:not-empty [:joined-field "experts" [:field-id 4386]]]],
:breakout
[[:joined-field "experts" [:field-id 4386]]
[:datetime-field [:joined-field "timestamps" [:field-id 4394]] :month]]},
:aggregation [[:avg [:field-literal "count" :type/Integer]]],
:breakout [[:field-literal "expert_first_name" :type/Text]],
:order-by [[:desc [:aggregation 0]]],
:limit 1},
:parameters
[{:type "date/all-options",
:target ["dimension" ["field-literal" "to_awaiting_sale_agreement" "type/DateTimeWithLocalTZ"]],
:value "past6months~"}],
:async? true,
:cache-ttl nil},
:native
@anton1 Perfect - it only happens, when the date column in the "group by" is from a joined table.
The good news is that it is fixed on the upcoming x.39.0, but I have still created an issue for this, so we can prevent a regression like that.
I'm unsure how difficult it would be to get the fix into x.38.3, but I will try to push for that - no guarantees.
https://github.com/metabase/metabase/issues/15370
@flamber, thank you for your response and the explanation. Do you have an idea of when x.38.3 and x.39.0 will be released ?
@anton1 I would expect 38.3 this week and 39.0 in a couple of weeks - and then there's usually a grace-period for a few of days before it is deployed for everyone on Cloud. Looking at the PR that I think might have fixed this, I don't think it can easily be applied to 0.38.3
Whenever there's a version that is available on Cloud that includes the fix, then your instance can be upgraded manually - just send a mail to support.
Hi @flamber, I hope you're doing well. I am confronted with the same problem for new questions I am currently trying to do. Do you know if one of the versions you mentioned is available for us to upgrade to ?
Thanks !
@anton1 We just released 39.0-rc1, but I cannot upgrade you to that - there are several problems that needs to be fixed before final release. Currently we're expecting to release 39.0 on Wednesday.
@anton1 I would probably advise against that. There are some regressions in 39.0.
But you should send an email to support via https://store.metabase.com/support so it is tied to your instance. I'm writing this again, not for you, but since this is a public forum and a lot of people are reading.
Thanks ! I just sent an email requesting an upgrade.