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 #17767

Closed
flamber opened this issue Sep 4, 2021 · 3 comments
Closed

Summarizing with implicit join does not allow subsequent joins #17767

flamber opened this issue Sep 4, 2021 · 3 comments
Assignees
@camsaul
Labels
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

Comments

@flamber
Copy link
Contributor

flamber commented Sep 4, 2021

Describe the bug
When aggregating by implicit join, then subsequent joins fails.
Regression since 0.39.4

Workaround: Use explicit joins.

To Reproduce

  1. Custom question > Sample Dataset > Orders - summarize Count by implicit join Products.ID - join Reviews on ID=Product_ID
    image
Full stacktrace 
2021-09-04 12:15:38,591 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 4,
 :started_at #t "2021-09-04T12:15:38.038886+02:00[Europe/Copenhagen]",
 :error_type :invalid-query,
 :json_query
 {:database 4,
  :query
  {:source-query {:source-table 11, :aggregation [["count"]], :breakout [["field" 105 {:source-field 82}]]},
   :joins
   [{:fields "all",
     :source-table 13,
     :condition ["=" ["field" "ID" {:base-type "type/BigInteger"}] ["field" 110 {:join-alias "Reviews"}]],
     :alias "Reviews"}],
   :limit 10},
  :type "query",
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native nil,
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> driver.sql.query_processor$fn__42721$source_aliases__42726.invoke(query_processor.clj:1011)"
  "driver.sql.query_processor$fn__42667$join_source_aliases__42672$fn__42673.invoke(query_processor.clj:1001)"
  "driver.sql.query_processor$fn__42667$join_source_aliases__42672.invoke(query_processor.clj:999)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727$iter__42728__42732$fn__42733$fn__42734.invoke(query_processor.clj:1019)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727$iter__42728__42732$fn__42733.invoke(query_processor.clj:1018)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727.invoke(query_processor.clj:1018)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726.invoke(query_processor.clj:1011)"
  "driver.sql.query_processor$fn__42667$join_source_aliases__42672$fn__42673.invoke(query_processor.clj:1001)"
  "driver.sql.query_processor$fn__42667$join_source_aliases__42672.invoke(query_processor.clj:999)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727$iter__42728__42732$fn__42733$fn__42734.invoke(query_processor.clj:1019)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727$iter__42728__42732$fn__42733.invoke(query_processor.clj:1018)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726$fn__42727.invoke(query_processor.clj:1018)"
  "driver.sql.query_processor$fn__42721$source_aliases__42726.invoke(query_processor.clj:1011)"
  "driver.sql.query_processor$apply_clauses.invokeStatic(query_processor.clj:1039)"
  "driver.sql.query_processor$apply_clauses.invoke(query_processor.clj:1034)"
  "driver.sql.query_processor$mbql__GT_honeysql.invokeStatic(query_processor.clj:1076)"
  "driver.sql.query_processor$mbql__GT_honeysql.invoke(query_processor.clj:1073)"
  "driver.sql.query_processor$mbql__GT_native.invokeStatic(query_processor.clj:1087)"
  "driver.sql.query_processor$mbql__GT_native.invoke(query_processor.clj:1084)"
  "driver.sql$fn__82236.invokeStatic(sql.clj:40)"
  "driver.sql$fn__82236.invoke(sql.clj:38)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:14)"
  "query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:9)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47902.invoke(mbql_to_native.clj:22)"
  "query_processor.middleware.check_features$check_features$fn__47016.invoke(check_features.clj:39)"
  "query_processor.middleware.limit$limit$fn__47888.invoke(limit.clj:37)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__46468.invoke(cache.clj:204)"
  "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__48148.invoke(optimize_temporal_filters.clj:204)"
  "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__50080.invoke(validate_temporal_bucketing.clj:50)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45587.invoke(auto_parse_filter_values.clj:43)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41716.invoke(wrap_value_literals.clj:161)"
  "query_processor.middleware.annotate$add_column_info$fn__41591.invoke(annotate.clj:608)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46888.invoke(permissions.clj:81)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__49009.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__47089.invoke(cumulative_aggregations.clj:60)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__49306.invoke(resolve_joined_fields.clj:102)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__49619.invoke(resolve_joins.clj:171)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__45163.invoke(add_implicit_joins.clj:190)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47852.invoke(large_int_id.clj:59)"
  "query_processor.middleware.format_rows$format_rows$fn__47833.invoke(format_rows.clj:74)"
  "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__44457.invoke(add_default_temporal_unit.clj:23)"
  "query_processor.middleware.desugar$desugar$fn__47155.invoke(desugar.clj:21)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45974.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46691.invoke(resolve_fields.clj:34)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__44812.invoke(add_dimension_projections.clj:314)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__45041.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__50029.invoke(upgrade_field_literals.clj:40)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__45326.invoke(add_source_metadata.clj:123)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__49181.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45534.invoke(auto_bucket_datetimes.clj:147)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46738.invoke(resolve_source_table.clj:45)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48991.invoke(parameters.clj:111)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46790.invoke(resolve_referenced.clj:79)"
  "query_processor.middleware.expand_macros$expand_macros$fn__47539.invoke(expand_macros.clj:184)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__45335.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49982.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49192$fn__49196.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__49192.invoke(resolve_database_and_driver.clj:25)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47779.invoke(fetch_source_query.clj:274)"
  "query_processor.middleware.store$initialize_store$fn__49991$fn__49992.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__49991.invoke(store.clj:10)"
  "query_processor.middleware.validate$validate_query$fn__50036.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47915.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__45181.invoke(add_rows_truncated.clj:35)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49967.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__47032.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__49078.invoke(process_userland_query.clj:134)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46972.invoke(catch_exceptions.clj:173)"
  "query_processor.reducible$async_qp$qp_STAR___38255$thunk__38256.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___38255.invoke(reducible.clj:109)"
  "query_processor.reducible$sync_qp$qp_STAR___38264$fn__38267.invoke(reducible.clj:135)"
  "query_processor.reducible$sync_qp$qp_STAR___38264.invoke(reducible.clj:134)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:241)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:237)"
  "query_processor$fn__50126$process_query_and_save_execution_BANG___50135$fn__50138.invoke(query_processor.clj:253)"
  "query_processor$fn__50126$process_query_and_save_execution_BANG___50135.invoke(query_processor.clj:245)"
  "query_processor$fn__50170$process_query_and_save_with_max_results_constraints_BANG___50179$fn__50182.invoke(query_processor.clj:265)"
  "query_processor$fn__50170$process_query_and_save_with_max_results_constraints_BANG___50179.invoke(query_processor.clj:258)"
  "api.dataset$run_query_async$fn__56417.invoke(dataset.clj:56)"
  "query_processor.streaming$streaming_response_STAR_$fn__56396$fn__56397.invoke(streaming.clj:72)"
  "query_processor.streaming$streaming_response_STAR_$fn__56396.invoke(streaming.clj:71)"
  "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$fn__16088.invoke(streaming_response.clj:84)"],
 :context :ad-hoc,
 :error
 "Input to source-aliases does not match schema: \n\n\t   [nil (named {:fields (named (not (sequential? :none)) \"Distinct, non-empty sequence of Field clauses\")} inner-query)]  \n\n",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:database 4,
  :query
  {:limit 10,
   :source-metadata
   [{:semantic_type :type/PK,
     :table_id 10,
     :coercion_strategy nil,
     :name "ID",
     :settings nil,
     :field_ref [:field 105 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}],
     :effective_type :type/BigInteger,
     :parent_id nil,
     :id 105,
     :display_name "Product → ID",
     :fingerprint nil,
     :base_type :type/BigInteger,
     :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 105 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]
    [:field "count" {:base-type :type/BigInteger}]
    [:field 111 {:join-alias "Reviews"}]
    [:field 110 {:join-alias "Reviews"}]
    [:field 113 {:join-alias "Reviews"}]
    [:field 115 {:join-alias "Reviews"}]
    [:field 112 {:join-alias "Reviews"}]
    [:field 114 {:temporal-unit :default, :join-alias "Reviews"}]],
   :joins
   [{:strategy :left-join,
     :source-table 13,
     :condition
     [:=
      [:field 105 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]
      [:field 110 {:join-alias "Reviews"}]],
     :alias "Reviews",
     :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 105 {:source-field 82, :join-alias "PRODUCTS__via__PRODUCT_ID"}]],
    :order-by [[:asc [:field 105 {: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}]}},
  :type :query,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :nested? false,
   :query-hash
   [46, 20, -34, -31, -122, 107, 22, -88, 117, 24, -1, 52, 116, -25, -80, -25, 117, 103, -60, 15, 93, -63, -118, -10,
    -45, -95, 3, 67, -66, -57, -3, 67]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :ex-data
 {:type :schema.core/error,
  :value
  [:h2
   {: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}],
  :error
  [nil
   (named {:fields (named (not (sequential? :none)) "Distinct, non-empty sequence of Field clauses")} inner-query)]},
 :data {:rows [], :cols []}}

2021-09-04 12:15:38,611 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 581.7 ms (23 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (3 idle, 0 queued) (65 total active threads) Queries in flight: 1 (0 queued)

Expected behavior
Using explicit join works:
image

Information about your Metabase Installation:
Tested 0.37.8 thru 0.40.3.1 and master ceb0af3 - regression since 0.39.4
@flamber flamber added Type:Bug Product defects 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. labels Sep 4, 2021
@flamber flamber mentioned this issue Oct 17, 2021
Closed
nemanjaglumac added a commit that referenced this issue Oct 17, 2021
@nemanjaglumac
Add repro for #17767
5c77f62
@nemanjaglumac nemanjaglumac mentioned this issue Oct 17, 2021
Merged
nemanjaglumac added a commit that referenced this issue Oct 17, 2021
@nemanjaglumac
#17767 Repro: Summarizing with implicit join does not allow subsequen…
6e3e521 
…t joins (#18515)
@nemanjaglumac nemanjaglumac added the .Reproduced Issues reproduced in test (usually Cypress) label Oct 17, 2021
github-actions bot pushed a commit that referenced this issue Oct 17, 2021
@nemanjaglumac
#17767 Repro: Summarizing with implicit join does not allow subsequen…
ec2b292 
…t joins (#18515)
nemanjaglumac added a commit that referenced this issue Oct 17, 2021
@github-actions @nemanjaglumac
#17767 Repro: Summarizing with implicit join does not allow subsequen…
1d1e7c6 
…t joins (#18515) (#18519)

Co-authored-by: Nemanja Glumac <31325167+nemanjaglumac@users.noreply.github.com>
@camsaul
Copy link
Member

camsaul commented Oct 20, 2021

Working on getting #16656 merged in to make this sort of thing easy to fix.

@camsaul camsaul mentioned this issue Oct 20, 2021
Closed
13 tasks
camsaul added a commit that referenced this issue Jan 11, 2022
@camsaul
Issue #17767 is fixed now
ccc586f
@camsaul camsaul mentioned this issue Jan 11, 2022
Merged
9 tasks
@camsaul
Copy link
Member

camsaul commented Jan 11, 2022

This will be fixed by #19384

@camsaul
Copy link
Member

camsaul commented Jan 18, 2022

Fixed by #19384

@camsaul camsaul closed this as completed Jan 18, 2022
@camsaul camsaul added this to the 0.42 milestone Jan 18, 2022
@flamber flamber mentioned this issue Feb 15, 2022
Closed
@flamber flamber mentioned this issue Nov 20, 2022
Closed
This was referenced Mar 15, 2023
Merged
Merged
@deploysentinel deploysentinel bot mentioned this issue Apr 18, 2023
Merged
@deploysentinel deploysentinel bot mentioned this issue May 12, 2023
Merged
nemanjaglumac added a commit that referenced this issue Jun 9, 2023
@nemanjaglumac
Tentatively fix flake in repro #17767
49da5a6 
This flake has a different cause than the rest of the flakes in this PR.
The request `POST /api/dataset` is timing out in CI. It takes more than
30 seconds to complete, and Cypress times out in meantime.

This is suspiciously slow, even for CI.
I'm trying to limit the number of results by limiting rows in the original
query. This reduces the number of total results (after the join) from
1,136 rows to just 9.
@nemanjaglumac nemanjaglumac mentioned this issue Jun 9, 2023
Merged
nemanjaglumac added a commit that referenced this issue Jun 12, 2023
@nemanjaglumac
[E2E] Fix joins flakes (#31486)
2f1ae54 
* Fix join flake for field literals

Example of a failed run from the past:
https://www.deploysentinel.com/ci/runs/647ff0426bd09da7e252a052

* Fix flake in repro #12928

An example of the failed run from the past:
https://www.deploysentinel.com/ci/runs/647f17d60baaca08b249b4aa

This commit extracts the joins logic to select two saved questions to a
seperate helper, because it seems we'll use it in many places.

* Fix flake for repro #18512

An example of a previously failed CI run:
https://www.deploysentinel.com/ci/runs/647f605e4d5097b4de537e07

* Fix flake for repro #18502

An example of a failed run from the past:
https://www.deploysentinel.com/ci/runs/64807a6e45294b33d5240830

* Tentatively fix flake in repro #17767

This flake has a different cause than the rest of the flakes in this PR.
The request `POST /api/dataset` is timing out in CI. It takes more than
30 seconds to complete, and Cypress times out in meantime.

This is suspiciously slow, even for CI.
I'm trying to limit the number of results by limiting rows in the original
query. This reduces the number of total results (after the join) from
1,136 rows to just 9.
This was referenced May 8, 2024
Merged
Merged
Merged
Closed
Merged
Merged
Merged
Merged
Merged
Merged
Closed
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Merged
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@camsaul camsaul

Labels
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
Development

No branches or pull requests
3 participants
@flamber @camsaul @nemanjaglumac