No-code joins with error when the same field used

lvgiacomin · November 16, 2022, 6:46pm

I've saved 2 different questions using no-code (tables already modelled using primary and entity keys to join the Star Schema).
The first one is:

Metric 1 from fact_order
Dimension 1 from dim_customers

The second one is:

Metric 2 from fact_sales
Dimension 1 from dim_customers (same as the first question)

I'm trying to join both saved questions in the no-code interface with the field Dimension 1, but I'm receiving the following error: Cannot find matching FK Table ID for FK Field null.

Every time I try to join questions with the same field from the same table, this error appears.

Do you know if I can do it better or it is a bug from Metabase?

flamber · November 16, 2022, 6:51pm

Hi @lvgiacomin
Post "Diagnostic Info" from Admin > Troubleshooting.
And the full related log - Admin > Troubleshooting > Logs.

lvgiacomin · November 16, 2022, 7:00pm

Hello @flamber, sure!
In my example I gave a hypothetical scenario, but the problem is the same.

[1196cb4a-e924-4017-9638-22cbb53dbfd7] 2022-11-16T15:49:03-03:00 INFO metabase.api.dataset Source query for this query is Card 315
[1196cb4a-e924-4017-9638-22cbb53dbfd7] 2022-11-16T15:49:03-03:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Cannot find matching FK Table ID for FK Field null
{:database_id 2,
 :started_at #t "2022-11-16T18:49:03.274410Z[GMT]",
 :json_query
 {:type "query",
  :query
  {:source-table "card__315",
   :joins
   [{:fields "all",
     :source-table "card__314",
     :condition
     ["and"
      ["=" ["field" 2449 {:temporal-unit "month"}] ["field" 4143 {:join-alias "Question 314", :temporal-unit "month"}]]
      ["=" ["field" 822 nil] ["field" 822 {:join-alias "Question 314"}]]],
     :alias "Question 314"}]},
  :database 2,
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native nil,
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "mbql.util.match.impl$replace_in_collection$iter__20188__20192$fn__20193.invoke(impl.cljc:44)"
  "mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:43)"
  "mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:47)"
  "mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "mbql.util.match.impl$replace_in_collection$iter__20188__20192$fn__20193.invoke(impl.cljc:44)"
  "mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:43)"
  "mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:47)"
  "mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "mbql.util.match.impl$replace_in_collection$iter__20188__20192$fn__20193.invoke(impl.cljc:44)"
  "mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:43)"
  "mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field$replace_52875__52876.invoke(add_implicit_joins.clj:90)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field.invokeStatic(add_implicit_joins.clj:90)"
  "query_processor.middleware.add_implicit_joins$add_join_alias_to_fields_with_source_field.invoke(add_implicit_joins.clj:78)"
  "query_processor.middleware.add_implicit_joins$resolve_implicit_joins_this_level.invokeStatic(add_implicit_joins.clj:204)"
  "query_processor.middleware.add_implicit_joins$resolve_implicit_joins_this_level.invoke(add_implicit_joins.clj:196)"
  "query_processor.middleware.add_implicit_joins$resolve_implicit_joins$fn__52956.invoke(add_implicit_joins.clj:219)"
  "query_processor.middleware.add_implicit_joins$resolve_implicit_joins.invokeStatic(add_implicit_joins.clj:214)"
  "query_processor.middleware.add_implicit_joins$resolve_implicit_joins.invoke(add_implicit_joins.clj:213)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins.invokeStatic(add_implicit_joins.clj:241)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins.invoke(add_implicit_joins.clj:228)"
  "query_processor$preprocess_STAR_$fn__54075.invoke(query_processor.clj:127)"
  "query_processor$preprocess_STAR_.invokeStatic(query_processor.clj:125)"
  "query_processor$preprocess_STAR_.invoke(query_processor.clj:120)"
  "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__68858.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 315,
 :context :ad-hoc,
 :error "Cannot find matching FK Table ID for FK Field null",
 :row_count 0,
 :running_time 0,
 :preprocessed nil,
 :ex-data
 {:resolving [:field 822 {:source-field 4144}],
  :candidates
  {2452 "dim_investor_relations__via__fk_investor_relations_key",
   2448 "dim_calendar__via__fk_calendar_key",
   2445 "dim_investor_demographics__via__fk_investor_demographics_key"}},
 :data {:rows [], :cols []}}

lvgiacomin · November 17, 2022, 1:18pm

@flamber do you have any light for me?

lvgiacomin · November 17, 2022, 5:00pm

If it helps, you can replicate this error with the Sample Data from Metabase. I'm using the latest version here v0.44.6

Create a question from Orders, Sum of Tax Grouped by Product -> Category
Create a question from Reviews, Avg. Rating Grouped by Product -> Category
After saving both, try to join them with Product -> Category. You'll receive "Cannot find matching FK Table ID for FK Field null".

Actually, in the image you can see the join is being labeled only to Reviews, not with Orders.
Reviews Product -> Category = Reviews Product -> Category, and not Orders Product -> Category = Reviews Product -> Category

flamber · November 20, 2022, 1:18pm

@lvgiacomin Excellent! I've created an issue for this:
https://github.com/metabase/metabase/issues/26631 - upvote by clicking on the first post

The visual glitch in the label is unrelated https://github.com/metabase/metabase/issues/19893

lvgiacomin · November 22, 2022, 11:46pm

Thanks for that @flamber!!