Cannot join with saved question "Bad: joined-field clause: join with alias 'Question 58' does not exist"

Problem
I get an error when I create a new question which is joined to a saved question.

My environment

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.3+7",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.3",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.3+7",
    "os.name": "Linux",
    "os.version": "4.14.152-98.182.amzn1.x86_64",
    "user.language": "en",
    "user.timezone": "US/Eastern"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "h2"
    ],
    "hosting-env": "elastic-beanstalk",
    "application-database": "postgres",
    "run-mode": "prod",
    "version": {
      "date": "2019-11-19",
      "tag": "v0.33.6",
      "branch": "release-0.33.x",
      "hash": "be1e0e1"
    },
    "settings": {
      "report-timezone": "US/Eastern"
    }
  }
}

The error
I get the following error in the Metabase UI:
Bad :joined-field clause: join with alias ‘Question 58’ does not exist. Found: #{“Product” “Product Bundle Product”}

Relevant Logs

[91a20eeb-df0e-480f-90d8-d29d20d7a181] 2019-12-18T16:26:51-07:00 WARN metabase.query-processor.middleware.process-userland-query Query failure {:status :failed,
:class java.lang.IllegalArgumentException,
:error "Bad :joined-field clause: join with alias 'Question 58' does not exist. Found: #{\"Product\" \"Product Bundle Product\"}",
:stacktrace
("--> query_processor.middleware.resolve_joins$check_join_aliases.invokeStatic(resolve_joins.clj:139)"
"query_processor.middleware.resolve_joins$check_join_aliases.invoke(resolve_joins.clj:133)"
"query_processor.middleware.resolve_joins$fn__41098$resolve_joins_in_mbql_query__41103$fn__41107.invoke(resolve_joins.clj:148)"
"query_processor.middleware.resolve_joins$fn__41098$resolve_joins_in_mbql_query__41103.invoke(resolve_joins.clj:142)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invokeStatic(resolve_joins.clj:165)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invoke(resolve_joins.clj:163)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invokeStatic(resolve_joins.clj:173)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invoke(resolve_joins.clj:163)"
"query_processor.middleware.resolve_joins$fn__40992$resolve_join_source_queries__40997$fn__40998$iter__40999__41003$fn__41004$fn__41005.invoke(resolve_joins.clj:97)"
"query_processor.middleware.resolve_joins$fn__40992$resolve_join_source_queries__40997$fn__40998$iter__40999__41003$fn__41004.invoke(resolve_joins.clj:96)"
"query_processor.middleware.resolve_joins$fn__40992$resolve_join_source_queries__40997.invoke(resolve_joins.clj:94)"
"query_processor.middleware.resolve_joins$fn__41098$resolve_joins_in_mbql_query__41103$fn__41107.invoke(resolve_joins.clj:146)"
"query_processor.middleware.resolve_joins$fn__41098$resolve_joins_in_mbql_query__41103.invoke(resolve_joins.clj:142)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invokeStatic(resolve_joins.clj:165)"
"query_processor.middleware.resolve_joins$resolve_joins_in_mbql_query_all_levels.invoke(resolve_joins.clj:163)"
"query_processor.middleware.resolve_joins$resolve_joins_STAR_.invokeStatic(resolve_joins.clj:177)"
"query_processor.middleware.resolve_joins$resolve_joins_STAR_.invoke(resolve_joins.clj:175)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__41131.invoke(resolve_joins.clj:184)"
"query_processor.middleware.limit$limit$fn__38092.invoke(limit.clj:19)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__43890.invoke(results_metadata.clj:87)"
"query_processor.middleware.format_rows$format_rows$fn__38080.invoke(format_rows.clj:26)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__35043.invoke(add_dimension_projections.clj:234)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__35684.invoke(add_source_metadata.clj:107)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41181.invoke(resolve_source_table.clj:46)"
"query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__35531.invoke(add_row_count_and_status.clj:16)"
"query_processor.middleware.driver_specific$process_query_in_context$fn__37567.invoke(driver_specific.clj:12)"
"query_processor.middleware.add_settings$add_settings$fn__35554.invoke(add_settings.clj:45)"
"query_processor.middleware.resolve_driver$resolve_driver$fn__40795.invoke(resolve_driver.clj:22)"
"query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881$fn__36882.invoke(bind_effective_timezone.clj:9)"
"util.date$call_with_effective_timezone.invokeStatic(date.clj:88)"
"util.date$call_with_effective_timezone.invoke(date.clj:77)"
"query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881.invoke(bind_effective_timezone.clj:8)"
"query_processor.middleware.store$initialize_store$fn__43915$fn__43916.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:46)"
"query_processor.store$do_with_store.invoke(store.clj:40)"
"query_processor.middleware.store$initialize_store$fn__43915.invoke(store.clj:10)"
"query_processor.middleware.async$async__GT_sync$fn__34195.invoke(async.clj:23)"
"query_processor.middleware.async_wait$runnable$fn__36607.invoke(async_wait.clj:89)"),
:query
{:query
{:source-table 142,
:joins [{:fields "all", :source-table "card__58", :condition ["=" ["field-id" 284] ["joined-field" "Question 58" ["field-literal" "id" "type/Integer"]]], :alias "Question 58"}]},
:type "query",
:parameters [],
:async? true,
:middleware {:add-default-userland-constraints? true, :userland-query? true},
:info
{:executed-by 1,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [75, -73, -5, -20, 14, -54, 27, -104, 70, -127, 16, 30, 126, -47, -101, -53, 124, 117, -118, 117, 68, 81, -25, -48, 68, 30, -97, 67, 60, 58, -61, 123]},
:constraints {:max-results 10000, :max-results-bare-rows 2000}},
:preprocessed nil,
:native nil}

[91a20eeb-df0e-480f-90d8-d29d20d7a181] 2019-12-18T16:26:51-07:00 DEBUG metabase.middleware.log POST /api/dataset 200 [ASYNC: completed] 385.1 ms (47 DB calls) Jetty threads: 2/50 (4 idle, 0 queued) (87 total active threads) Queries in flight: 0

How to duplicate
I am unable to duplicate this with the sample dataset, because I am unable to use saved queries on the sample dataset, for some unknown reason.

To duplicate, on a fresh dataset, I would attempt to do the following:

  • Given a table where a unique identifier appears in multiple rows (eg. orders for a users with user ids)
  • Create a question which summarizes a count of rows, grouped by the user id
  • Save this question as question #1, as a table
  • Create a new question, starting with some other table
  • Join that other table to question #1, using the user id on the join
  • Visualize the result of the question
  • You should now see the error

I am unable to duplicate this error using the sample data set, because I am unable to use saved questions in any manner with the sample data set.

Severity
I don’t think I can do any queries that require a subquery without this feature. For many parts of my database, I will need to have the ability to use subqueries in order to get the information I need.

Specifically, I would not be able to

  • Display a breakdown of revenue from purchases of bundles, broken down by piano keyboard model number and also broken down by subscription type.
  • Display a the large majority of the most meaningful statistics associated with different segments of our user base.

Metabase is great
You are putting together a great product. I look forward to seeing what the future holds. You are making data so much more accessible! I love what you are doing!

Feel free to let me know if I can answer any clarifying questions.

@ben-pg
Really nice with a lot of details :+1:
First step, I would recommend upgrading to 0.33.7.3.

By any chance, are you using “Use foreign key” for any FK columns used in any of the tables included in the query?
Admin > Data Model > (database) > (table) > (column) :gear: > “Display value”
I’m asking, since that’s the only time I’ve seen that type of error, when I was reproducing a different issue that was fixed on 0.33.4:
https://github.com/metabase/metabase/issues/10871#issuecomment-534806631

I’m not sure why you’re unable to use Saved Questions from the Sample Dataset. Are you getting any errors? I think you might be seeing issue #9027, where you have to do a browser refresh after you’ve just created the question for it to appear on Saved Question list.

I’ve not been able to reproduce with Sample Dataset (and a couple of other databases):

  1. Simple question > Orders: Summarize count-of-rows by UserID, save question Q1
  2. (refresh browser)
  3. Custom question > People: join Q1 on ID=UserID, visualize