Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Summarizing with implicit join does not allow subsequent joins and nested query #20519

Closed
flamber opened this issue Feb 15, 2022 · 1 comment · Fixed by #20697
Closed

Summarizing with implicit join does not allow subsequent joins and nested query #20519

flamber opened this issue Feb 15, 2022 · 1 comment · Fixed by #20697
Assignees
@camsaul
Labels
.Backend Priority:P2 Average run of the mill bug Querying/Nested Queries Questions based on other saved questions .Regression Bugs that were previously fixed and/or bugs unintentionally shipped with new features. .Reproduced Issues reproduced in test (usually Cypress) Type:Bug Product defects
Milestone
0.42.2

Comments

@flamber
Copy link
Contributor

flamber commented Feb 15, 2022

Describe the bug
Almost a repeat of #17767, just with another nesting. It's possible to trigger something similar following #18512 and adding Custom Column to the third combining question.
Regression since 0.39.4

Workaround is to use explicit joins.

To Reproduce

  1. Question > Sample Dataset > Orders
  2. Summarize Count grouped by Products.Category
  3. Join Products on Category=Category
  4. Custom Column 1 + 1 as "CC" (just to trigger nested query) - errors with Column "source.PRODUCTS__via__PRODUCT_ID__ID" not found; (different database types might error slightly differently)
    image
Full stacktrace 
2022-02-15 12:56:48,350 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 4,
 :started_at #t "2022-02-15T12:56:47.514485+01:00[Europe/Copenhagen]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error
   "Error executing query: Column \"source.PRODUCTS__via__PRODUCT_ID__ID\" not found; SQL statement:\nCREATE FORCE VIEW PUBLIC._99 AS\nSELECT\n    \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\",\n    \"source\".\"count\" AS \"count\",\n    (1 + 1) AS CC,\n    \"Products\".ID AS \"Products__ID\",\n    \"Products\".TITLE AS \"Products__TITLE\",\n    \"Products\".CATEGORY AS \"Products__CATEGORY\",\n    \"Products\".VENDOR AS \"Products__VENDOR\",\n    \"Products\".PRICE AS \"Products__PRICE\",\n    \"Products\".RATING AS \"Products__RATING\",\n    \"Products\".CREATED_AT AS \"Products__CREATED_AT\",\n    \"source\".\"PRODUCTS__via__PRODUCT_ID__ID\" AS \"PRODUCTS__via__PRODUCT_ID__ID\"\nFROM (\n    SELECT\n        \"PRODUCTS__via__PRODUCT_ID\".CATEGORY AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\",\n        COUNT(*) AS \"count\"\n    FROM PUBLIC.ORDERS\n    LEFT OUTER JOIN PUBLIC.PRODUCTS \"PRODUCTS__via__PRODUCT_ID\"\n        ON PUBLIC.ORDERS.PRODUCT_ID = \"PRODUCTS__via__PRODUCT_ID\".ID\n    GROUP BY \"PRODUCTS__via__PRODUCT_ID\".CATEGORY\n    ORDER BY 1\n) \"source\"\nLEFT OUTER JOIN PUBLIC.PRODUCTS \"Products\"\n    ON \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" = \"Products\".CATEGORY [42122-197]",
   :stacktrace
   ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__53274.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__81915.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__81915.invoke(sql_jdbc.clj:52)"
    "driver.h2$fn__79065.invokeStatic(h2.clj:90)"
    "driver.h2$fn__79065.invoke(h2.clj:87)"
    "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__49659.invoke(mbql_to_native.clj:25)"
    "query_processor.middleware.check_features$check_features$fn__50405.invoke(check_features.clj:42)"
    "query_processor.middleware.limit$limit$fn__48032.invoke(limit.clj:37)"
    "query_processor.middleware.cache$maybe_return_cached_results$fn__50788.invoke(cache.clj:204)"
    "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__51821.invoke(optimize_temporal_filters.clj:204)"
    "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__51865.invoke(validate_temporal_bucketing.clj:50)"
    "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49716.invoke(auto_parse_filter_values.clj:43)"
    "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39821.invoke(wrap_value_literals.clj:161)"
    "query_processor.middleware.annotate$add_column_info$fn__44587.invoke(annotate.clj:659)"
    "query_processor.middleware.permissions$check_query_permissions$fn__46329.invoke(permissions.clj:108)"
    "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__51010.invoke(pre_alias_aggregations.clj:40)"
    "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46725.invoke(cumulative_aggregations.clj:60)"
    "query_processor.middleware.visualization_settings$update_viz_settings$fn__46663.invoke(visualization_settings.clj:63)"
    "query_processor.middleware.fix_bad_references$fix_bad_references_middleware$fn__50975.invoke(fix_bad_references.clj:91)"
    "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47610.invoke(resolve_joined_fields.clj:111)"
    "query_processor.middleware.resolve_joins$resolve_joins$fn__51591.invoke(resolve_joins.clj:176)"
    "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__51133.invoke(add_implicit_joins.clj:202)"
    "query_processor.middleware.large_int_id$convert_id_to_string$fn__47629.invoke(large_int_id.clj:59)"
    "query_processor.middleware.format_rows$format_rows$fn__51185.invoke(format_rows.clj:74)"
    "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46997.invoke(add_default_temporal_unit.clj:23)"
    "query_processor.middleware.desugar$desugar$fn__46636.invoke(desugar.clj:21)"
    "query_processor.middleware.binning$update_binning_strategy$fn__39550.invoke(binning.clj:229)"
    "query_processor.middleware.resolve_fields$resolve_fields$fn__45971.invoke(resolve_fields.clj:34)"
    "query_processor.middleware.add_dimension_projections$add_remapping$fn__50342.invoke(add_dimension_projections.clj:487)"
    "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__50636.invoke(add_implicit_clauses.clj:164)"
    "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48017.invoke(upgrade_field_literals.clj:117)"
    "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__47374.invoke(add_source_metadata.clj:125)"
    "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50887.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
    "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48974.invoke(auto_bucket_datetimes.clj:147)"
    "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45952.invoke(resolve_source_table.clj:45)"
    "query_processor.middleware.parameters$substitute_parameters$fn__48628.invoke(parameters.clj:111)"
    "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46025.invoke(resolve_referenced.clj:79)"
    "query_processor.middleware.expand_macros$expand_macros$fn__52249.invoke(expand_macros.clj:184)"
    "query_processor.middleware.add_timezone_info$add_timezone_info$fn__48407.invoke(add_timezone_info.clj:15)"
    "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__51200.invoke(splice_params_in_response.clj:32)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50647$fn__50652.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__50647.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46571.invoke(fetch_source_query.clj:286)"
    "query_processor.middleware.store$initialize_store$fn__46762$fn__46763.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__46762.invoke(store.clj:10)"
    "query_processor.middleware.validate$validate_query$fn__50982.invoke(validate.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__50989.invoke(normalize_query.clj:22)"
    "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48353.invoke(add_rows_truncated.clj:35)"
    "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49645.invoke(results_metadata.clj:82)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__48371.invoke(constraints.clj:42)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__50923.invoke(process_userland_query.clj:146)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51280.invoke(catch_exceptions.clj:169)"
    "query_processor.reducible$async_qp$qp_STAR___43323$thunk__43324.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___43323.invoke(reducible.clj:109)"
    "query_processor.reducible$sync_qp$qp_STAR___43332$fn__43335.invoke(reducible.clj:135)"
    "query_processor.reducible$sync_qp$qp_STAR___43332.invoke(reducible.clj:134)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:245)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:241)"
    "query_processor$fn__52297$process_query_and_save_execution_BANG___52306$fn__52309.invoke(query_processor.clj:256)"
    "query_processor$fn__52297$process_query_and_save_execution_BANG___52306.invoke(query_processor.clj:249)"
    "query_processor$fn__52341$process_query_and_save_with_max_results_constraints_BANG___52350$fn__52353.invoke(query_processor.clj:268)"
    "query_processor$fn__52341$process_query_and_save_with_max_results_constraints_BANG___52350.invoke(query_processor.clj:261)"
    "api.dataset$run_query_async$fn__65276.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__38459$fn__38460.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__38459.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__26889.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:sql
    "-- Metabase:: userID: 1 queryType: MBQL queryHash: 01657a67eb985d7d55c274faeaf9a409d22590c2524176b87099dbe9119f2bfb\nSELECT \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", \"source\".\"count\" AS \"count\", \"source\".\"CC\" AS \"CC\", \"source\".\"Products__ID\" AS \"Products__ID\", \"source\".\"Products__TITLE\" AS \"Products__TITLE\", \"source\".\"Products__CATEGORY\" AS \"Products__CATEGORY\", \"source\".\"Products__VENDOR\" AS \"Products__VENDOR\", \"source\".\"Products__PRICE\" AS \"Products__PRICE\", \"source\".\"Products__RATING\" AS \"Products__RATING\", \"source\".\"Products__CREATED_AT\" AS \"Products__CREATED_AT\" FROM (SELECT \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", \"source\".\"count\" AS \"count\", (1 + 1) AS \"CC\", \"Products\".\"ID\" AS \"Products__ID\", \"Products\".\"TITLE\" AS \"Products__TITLE\", \"Products\".\"CATEGORY\" AS \"Products__CATEGORY\", \"Products\".\"VENDOR\" AS \"Products__VENDOR\", \"Products\".\"PRICE\" AS \"Products__PRICE\", \"Products\".\"RATING\" AS \"Products__RATING\", \"Products\".\"CREATED_AT\" AS \"Products__CREATED_AT\", \"source\".\"PRODUCTS__via__PRODUCT_ID__ID\" AS \"PRODUCTS__via__PRODUCT_ID__ID\" FROM (SELECT \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" LEFT JOIN \"PUBLIC\".\"PRODUCTS\" \"PRODUCTS__via__PRODUCT_ID\" ON \"PUBLIC\".\"ORDERS\".\"PRODUCT_ID\" = \"PRODUCTS__via__PRODUCT_ID\".\"ID\" GROUP BY \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" ORDER BY \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" ASC) \"source\" LEFT JOIN \"PUBLIC\".\"PRODUCTS\" \"Products\" ON \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" = \"Products\".\"CATEGORY\") \"source\" LIMIT 2000",
    :params nil,
    :type :invalid-query}}],
 :state "42S22",
 :error_type :invalid-query,
 :json_query
 {:database 4,
  :query
  {:source-query {:source-table 11, :aggregation [["count"]], :breakout [["field" 107 {:source-field 82}]]},
   :joins
   [{:fields "all",
     :source-table 10,
     :condition ["=" ["field" "CATEGORY" {:base-type "type/Text"}] ["field" 107 {:join-alias "Products"}]],
     :alias "Products"}],
   :expressions {:CC ["+" 1 1]}},
  :type "query",
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", \"source\".\"count\" AS \"count\", \"source\".\"CC\" AS \"CC\", \"source\".\"Products__ID\" AS \"Products__ID\", \"source\".\"Products__TITLE\" AS \"Products__TITLE\", \"source\".\"Products__CATEGORY\" AS \"Products__CATEGORY\", \"source\".\"Products__VENDOR\" AS \"Products__VENDOR\", \"source\".\"Products__PRICE\" AS \"Products__PRICE\", \"source\".\"Products__RATING\" AS \"Products__RATING\", \"source\".\"Products__CREATED_AT\" AS \"Products__CREATED_AT\" FROM (SELECT \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", \"source\".\"count\" AS \"count\", (1 + 1) AS \"CC\", \"Products\".\"ID\" AS \"Products__ID\", \"Products\".\"TITLE\" AS \"Products__TITLE\", \"Products\".\"CATEGORY\" AS \"Products__CATEGORY\", \"Products\".\"VENDOR\" AS \"Products__VENDOR\", \"Products\".\"PRICE\" AS \"Products__PRICE\", \"Products\".\"RATING\" AS \"Products__RATING\", \"Products\".\"CREATED_AT\" AS \"Products__CREATED_AT\", \"source\".\"PRODUCTS__via__PRODUCT_ID__ID\" AS \"PRODUCTS__via__PRODUCT_ID__ID\" FROM (SELECT \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\", count(*) AS \"count\" FROM \"PUBLIC\".\"ORDERS\" LEFT JOIN \"PUBLIC\".\"PRODUCTS\" \"PRODUCTS__via__PRODUCT_ID\" ON \"PUBLIC\".\"ORDERS\".\"PRODUCT_ID\" = \"PRODUCTS__via__PRODUCT_ID\".\"ID\" GROUP BY \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" ORDER BY \"PRODUCTS__via__PRODUCT_ID\".\"CATEGORY\" ASC) \"source\" LEFT JOIN \"PUBLIC\".\"PRODUCTS\" \"Products\" ON \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" = \"Products\".\"CATEGORY\") \"source\" LIMIT 2000",
  :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.optimize(ExpressionColumn.java:150)"
  "org.h2.expression.Alias.optimize(Alias.java:51)"
  "org.h2.command.dml.Select.prepare(Select.java:858)"
  "org.h2.command.Parser.prepare(Parser.java:263)"
  "org.h2.engine.Session.prepare(Session.java:576)"
  "org.h2.table.TableView.compileViewQuery(TableView.java:120)"
  "org.h2.table.TableView.initColumnsAndTables(TableView.java:172)"
  "org.h2.table.TableView.init(TableView.java:113)"
  "org.h2.table.TableView.<init>(TableView.java:70)"
  "org.h2.table.TableView.createTempView(TableView.java:559)"
  "org.h2.command.Parser.readTableFilter(Parser.java:1367)"
  "org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:2138)"
  "org.h2.command.Parser.parseSelectSimple(Parser.java:2287)"
  "org.h2.command.Parser.parseSelectSub(Parser.java:2133)"
  "org.h2.command.Parser.parseSelectUnion(Parser.java:1946)"
  "org.h2.command.Parser.parseSelect(Parser.java:1919)"
  "org.h2.command.Parser.parsePrepared(Parser.java:463)"
  "org.h2.command.Parser.parse(Parser.java:335)"
  "org.h2.command.Parser.parse(Parser.java:307)"
  "org.h2.command.Parser.prepareCommand(Parser.java:278)"
  "org.h2.engine.Session.prepareLocal(Session.java:611)"
  "org.h2.engine.Session.prepareCommand(Session.java:549)"
  "org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)"
  "org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)"
  "org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)"
  "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
  "--> driver.sql_jdbc.execute$fn__53194.invokeStatic(execute.clj:368)"
  "driver.sql_jdbc.execute$fn__53194.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__53274.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__81915.invokeStatic(sql_jdbc.clj:54)"
  "driver.sql_jdbc$fn__81915.invoke(sql_jdbc.clj:52)"
  "driver.h2$fn__79065.invokeStatic(h2.clj:90)"
  "driver.h2$fn__79065.invoke(h2.clj:87)"
  "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__49659.invoke(mbql_to_native.clj:25)"
  "query_processor.middleware.check_features$check_features$fn__50405.invoke(check_features.clj:42)"
  "query_processor.middleware.limit$limit$fn__48032.invoke(limit.clj:37)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__50788.invoke(cache.clj:204)"
  "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__51821.invoke(optimize_temporal_filters.clj:204)"
  "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__51865.invoke(validate_temporal_bucketing.clj:50)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49716.invoke(auto_parse_filter_values.clj:43)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39821.invoke(wrap_value_literals.clj:161)"
  "query_processor.middleware.annotate$add_column_info$fn__44587.invoke(annotate.clj:659)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46329.invoke(permissions.clj:108)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__51010.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46725.invoke(cumulative_aggregations.clj:60)"
  "query_processor.middleware.visualization_settings$update_viz_settings$fn__46663.invoke(visualization_settings.clj:63)"
  "query_processor.middleware.fix_bad_references$fix_bad_references_middleware$fn__50975.invoke(fix_bad_references.clj:91)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47610.invoke(resolve_joined_fields.clj:111)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__51591.invoke(resolve_joins.clj:176)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__51133.invoke(add_implicit_joins.clj:202)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47629.invoke(large_int_id.clj:59)"
  "query_processor.middleware.format_rows$format_rows$fn__51185.invoke(format_rows.clj:74)"
  "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46997.invoke(add_default_temporal_unit.clj:23)"
  "query_processor.middleware.desugar$desugar$fn__46636.invoke(desugar.clj:21)"
  "query_processor.middleware.binning$update_binning_strategy$fn__39550.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__45971.invoke(resolve_fields.clj:34)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__50342.invoke(add_dimension_projections.clj:487)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__50636.invoke(add_implicit_clauses.clj:164)"
  "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48017.invoke(upgrade_field_literals.clj:117)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__47374.invoke(add_source_metadata.clj:125)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50887.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48974.invoke(auto_bucket_datetimes.clj:147)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45952.invoke(resolve_source_table.clj:45)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48628.invoke(parameters.clj:111)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46025.invoke(resolve_referenced.clj:79)"
  "query_processor.middleware.expand_macros$expand_macros$fn__52249.invoke(expand_macros.clj:184)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__48407.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__51200.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50647$fn__50652.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__50647.invoke(resolve_database_and_driver.clj:34)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46571.invoke(fetch_source_query.clj:286)"
  "query_processor.middleware.store$initialize_store$fn__46762$fn__46763.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__46762.invoke(store.clj:10)"
  "query_processor.middleware.validate$validate_query$fn__50982.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__50989.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48353.invoke(add_rows_truncated.clj:35)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49645.invoke(results_metadata.clj:82)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__48371.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__50923.invoke(process_userland_query.clj:146)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51280.invoke(catch_exceptions.clj:169)"
  "query_processor.reducible$async_qp$qp_STAR___43323$thunk__43324.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___43323.invoke(reducible.clj:109)"
  "query_processor.reducible$sync_qp$qp_STAR___43332$fn__43335.invoke(reducible.clj:135)"
  "query_processor.reducible$sync_qp$qp_STAR___43332.invoke(reducible.clj:134)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:245)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:241)"
  "query_processor$fn__52297$process_query_and_save_execution_BANG___52306$fn__52309.invoke(query_processor.clj:256)"
  "query_processor$fn__52297$process_query_and_save_execution_BANG___52306.invoke(query_processor.clj:249)"
  "query_processor$fn__52341$process_query_and_save_with_max_results_constraints_BANG___52350$fn__52353.invoke(query_processor.clj:268)"
  "query_processor$fn__52341$process_query_and_save_with_max_results_constraints_BANG___52350.invoke(query_processor.clj:261)"
  "api.dataset$run_query_async$fn__65276.invoke(dataset.clj:69)"
  "query_processor.streaming$streaming_response_STAR_$fn__38459$fn__38460.invoke(streaming.clj:162)"
  "query_processor.streaming$streaming_response_STAR_$fn__38459.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__26889.invoke(streaming_response.clj:84)"],
 :card_id nil,
 :context :ad-hoc,
 :error
 "Column \"source.PRODUCTS__via__PRODUCT_ID__ID\" not found; SQL statement:\nCREATE FORCE VIEW PUBLIC._99 AS\nSELECT\n    \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\",\n    \"source\".\"count\" AS \"count\",\n    (1 + 1) AS CC,\n    \"Products\".ID AS \"Products__ID\",\n    \"Products\".TITLE AS \"Products__TITLE\",\n    \"Products\".CATEGORY AS \"Products__CATEGORY\",\n    \"Products\".VENDOR AS \"Products__VENDOR\",\n    \"Products\".PRICE AS \"Products__PRICE\",\n    \"Products\".RATING AS \"Products__RATING\",\n    \"Products\".CREATED_AT AS \"Products__CREATED_AT\",\n    \"source\".\"PRODUCTS__via__PRODUCT_ID__ID\" AS \"PRODUCTS__via__PRODUCT_ID__ID\"\nFROM (\n    SELECT\n        \"PRODUCTS__via__PRODUCT_ID\".CATEGORY AS \"PRODUCTS__via__PRODUCT_ID__CATEGORY\",\n        COUNT(*) AS \"count\"\n    FROM PUBLIC.ORDERS\n    LEFT OUTER JOIN PUBLIC.PRODUCTS \"PRODUCTS__via__PRODUCT_ID\"\n        ON PUBLIC.ORDERS.PRODUCT_ID = \"PRODUCTS__via__PRODUCT_ID\".ID\n    GROUP BY \"PRODUCTS__via__PRODUCT_ID\".CATEGORY\n    ORDER BY 1\n) \"source\"\nLEFT OUTER JOIN PUBLIC.PRODUCTS \"Products\"\n    ON \"source\".\"PRODUCTS__via__PRODUCT_ID__CATEGORY\" = \"Products\".CATEGORY [42122-197]",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:database 4,
  :query
  {:expressions {:CC [:+ 1 1]},
   :source-metadata
   [{:semantic_type :type/Category,
     :table_id 10,
     :coercion_strategy nil,
     :name "CATEGORY",
     :settings nil,
     :field_ref [:field 107 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}],
     :effective_type :type/Text,
     :parent_id nil,
     :id 107,
     :display_name "Product → Category",
     :fingerprint
     {:global {:distinct-count 4, :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.375}}},
     :base_type :type/Text,
     :source_alias "PRODUCTS__via__PRODUCT_ID"}
    {:name "count",
     :display_name "Count",
     :base_type :type/BigInteger,
     :semantic_type :type/Quantity,
     :field_ref [:aggregation 0]}],
   :fields
   [[:field 107 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]
    [:field "count" {:base-type :type/BigInteger}]
    [:expression "CC"]
    [:field 105 {:join-alias "Products"}]
    [:field 106 {:join-alias "Products"}]
    [:field 107 {:join-alias "Products"}]
    [:field 109 {:join-alias "Products"}]
    [:field 104 {:join-alias "Products"}]
    [:field 103 {:join-alias "Products"}]
    [:field 108 {:temporal-unit :default, :join-alias "Products"}]],
   :joins
   [{:strategy :left-join,
     :fields
     [[:field 105 {:join-alias "Products"}]
      [:field 106 {:join-alias "Products"}]
      [:field 107 {:join-alias "Products"}]
      [:field 109 {:join-alias "Products"}]
      [:field 104 {:join-alias "Products"}]
      [:field 103 {:join-alias "Products"}]
      [:field 108 {:temporal-unit :default, :join-alias "Products"}]],
     :source-table 10,
     :condition
     [:=
      [:field 107 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]
      [:field 107 {:join-alias "Products"}]],
     :alias "Products",
     :joins
     [{:fields :none,
       :alias "PRODUCTS__via__PRODUCT_ID",
       :strategy :left-join,
       :condition [:= [:field 82 nil] [:field 105 {:join-alias "PRODUCTS__via__PRODUCT_ID"}]],
       :source-table 10,
       :fk-field-id 82}]}],
   :source-query
   {:source-table 11,
    :aggregation [[:aggregation-options [:count] {:name "count"}]],
    :breakout [[:field 107 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]],
    :order-by [[:asc [:field 107 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]]],
    :joins
    [{:strategy :left-join,
      :alias "PRODUCTS__via__PRODUCT_ID",
      :condition [:= [:field 82 nil] [:field 105 {:join-alias "PRODUCTS__via__PRODUCT_ID"}]],
      :source-table 10,
      :fk-field-id 82}]},
   :limit 2000},
  :type :query,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :nested? false,
   :query-hash
   [1, 101, 122, 103, -21, -104, 93, 125, 85, -62, 116, -6, -22, -7, -92, 9, -46, 37, -112, -62, 82, 65, 118, -72, 112,
    -103, -37, -23, 17, -97, 43, -5]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :data {:rows [], :cols []}}

2022-02-15 12:56:48,365 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 853.9 ms (20 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (104 total active threads) Queries in flight: 0 (0 queued); h2 DB 4 connections: 0/1 (0 threads blocked)

Information about your Metabase Installation:
Tested 0.39.3 thru 0.42.0
@flamber flamber added Type:Bug Product defects Priority:P2 Average run of the mill bug Querying/Nested Queries Questions based on other saved questions .Backend .Regression Bugs that were previously fixed and/or bugs unintentionally shipped with new features. labels Feb 15, 2022
nemanjaglumac added a commit that referenced this issue Feb 15, 2022
@nemanjaglumac
Add repro for #20519
fbbb2c5
@nemanjaglumac nemanjaglumac mentioned this issue Feb 15, 2022
Merged
nemanjaglumac added a commit that referenced this issue Feb 15, 2022
@nemanjaglumac @flamber
Repro #20519: Summarizing with implicit join does not allow subsequen…
54c4fa9 
…t joins and nested query (#20521)

* Add repro for #20519

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>
@nemanjaglumac nemanjaglumac added the .Reproduced Issues reproduced in test (usually Cypress) label Feb 15, 2022
github-actions bot pushed a commit that referenced this issue Feb 15, 2022
@nemanjaglumac @flamber
Repro #20519: Summarizing with implicit join does not allow subsequen…
0754c07 
…t joins and nested query (#20521)

* Add repro for #20519

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>
nemanjaglumac added a commit that referenced this issue Feb 15, 2022
@github-actions @nemanjaglumac @flamber
Repro #20519: Summarizing with implicit join does not allow subsequen…
51253fd 
…t joins and nested query (#20521) (#20530)

* Add repro for #20519

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

* Update frontend/test/metabase/scenarios/joins/reproductions/20519-cannot-join-on-aggregation-with-implicit-joins-and-nested-query.cy.spec.js

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>

Co-authored-by: Nemanja Glumac <31325167+nemanjaglumac@users.noreply.github.com>
Co-authored-by: flamber <1447303+flamber@users.noreply.github.com>
@camsaul camsaul self-assigned this Feb 23, 2022
@camsaul
Copy link
Member

camsaul commented Feb 23, 2022

From the error message, here's the SQL it's generating:

SELECT
    "source"."PRODUCTS__via__PRODUCT_ID__CATEGORY" AS "PRODUCTS__via__PRODUCT_ID__CATEGORY",
    "source"."count" AS "count",
    (1 + 1) AS CC,
    "Products".ID AS "Products__ID",
    "Products".TITLE AS "Products__TITLE",
    "Products".CATEGORY AS "Products__CATEGORY",
    "Products".VENDOR AS "Products__VENDOR",
    "Products".PRICE AS "Products__PRICE",
    "Products".RATING AS "Products__RATING",
    "Products".CREATED_AT AS "Products__CREATED_AT",
    "source"."PRODUCTS__via__PRODUCT_ID__ID" AS "PRODUCTS__via__PRODUCT_ID__ID"
FROM (
    SELECT
        "PRODUCTS__via__PRODUCT_ID".CATEGORY AS "PRODUCTS__via__PRODUCT_ID__CATEGORY",
        COUNT(*) AS "count"
    FROM PUBLIC.ORDERS
    LEFT OUTER JOIN PUBLIC.PRODUCTS "PRODUCTS__via__PRODUCT_ID"
        ON PUBLIC.ORDERS.PRODUCT_ID = "PRODUCTS__via__PRODUCT_ID".ID
    GROUP BY "PRODUCTS__via__PRODUCT_ID".CATEGORY
    ORDER BY 1
) "source"
LEFT OUTER JOIN PUBLIC.PRODUCTS "Products"
    ON "source"."PRODUCTS__via__PRODUCT_ID__CATEGORY" = "Products".CATEGORY

It's a little unclear why it's trying to include

    "source"."PRODUCTS__via__PRODUCT_ID__ID" AS "PRODUCTS__via__PRODUCT_ID__ID"

in the top level since it's neither available nor needed for anything there. I'll investigate.

camsaul added a commit that referenced this issue Feb 23, 2022
@camsaul
Fix #20519
dec0be7
@camsaul camsaul mentioned this issue Feb 23, 2022
Merged
camsaul added a commit that referenced this issue Feb 23, 2022
@camsaul
Fix resolution of implicit joins inside explicit join conditions (#20697
8b2877b 
)

* Fix #20519

* Fix busted indentation
@camsaul camsaul added this to the 0.42.2 milestone Feb 23, 2022
camsaul added a commit that referenced this issue Feb 23, 2022
@camsaul
Fix resolution of implicit joins inside explicit join conditions (#20697
1bdadb6 
)

* Fix #20519

* Fix busted indentation
@camsaul camsaul mentioned this issue Feb 23, 2022
Merged
alxnddr pushed a commit that referenced this issue Feb 24, 2022
@camsaul @alxnddr
Fix resolution of implicit joins inside explicit join conditions (#20697
9f188fb 
)

* Fix #20519

* Fix busted indentation
@flamber flamber mentioned this issue Mar 2, 2022
Closed
@flamber flamber mentioned this issue Nov 20, 2022
Closed
@deploysentinel deploysentinel bot mentioned this issue Feb 13, 2023
Merged
This was referenced Mar 20, 2023
Merged
Merged
Merged
@deploysentinel deploysentinel bot mentioned this issue Apr 1, 2023
Closed
@deploysentinel deploysentinel bot mentioned this issue Aug 2, 2023
Merged
@deploysentinel deploysentinel bot mentioned this issue Aug 18, 2023
Merged
This was referenced May 7, 2024
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Closed
Merged
Merged
Merged
Merged
Closed
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@camsaul camsaul

Labels
.Backend Priority:P2 Average run of the mill bug Querying/Nested Queries Questions based on other saved questions .Regression Bugs that were previously fixed and/or bugs unintentionally shipped with new features. .Reproduced Issues reproduced in test (usually Cypress) Type:Bug Product defects
Projects
None yet
Milestone
0.42.2
Development

Successfully merging a pull request may close this issue.

Fix resolution of implicit joins inside explicit join conditions metabase/metabase
3 participants
@flamber @camsaul @nemanjaglumac