Odd query failure when joining a table with a Model

Bug reports
1

Hi all! I'm seeing an odd, reproducible, consistently inconsistent error when I visualize the results of a Model that's a table joined with another Model: Unknown column 'source.Question 16__adv_notice_first_name' in 'field list'. Repro steps below.

Here's the error:

image
image2070×910 67.8 KB

If I edit the model query definition, the query runs ok and shows results in the preview at the bottom.

image
image2652×1414 214 KB

If I then cancel out, the query also runs ok.

image
image2694×956 268 KB

However, if I then click the refresh button, I get the error again.

image
image2704×1058 210 KB

If I remove the join, save, and then add the join back, I get results in preview again:

image
image2460×1356 185 KB

...and then if I save the query definition, then click the refresh button, I get the same error.

Thanks in advance! I'm on Cloud, v1.42.2, instance https://ncx.metabaseapp.com/ . I found the failing query in the logs, here it is, heavily elided to just the relevant column and nearby joins. Let me know if you need any more info.

    {:status :failed,
    :class clojure.lang.ExceptionInfo,
    :error
    "Error executing query: (conn=62797) Unknown column 'source.Question 16__adv_notice_first_name' in 'field list'",
    :stacktrace
    ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__42772.invoke(execute.clj:504)"
     ...],
    :error_type :invalid-query,
    :ex-data
    {:sql
     "-- Metabase:: userID: 1 queryType: MBQL queryHash: 2adeb3e94d2c1ab9b622980b83a58257ae244e8be9fc6c7328de8a399ff41d97
 SELECT `source`.`id` AS `id`, ... `source`.`Question 16__adv_notice_first_name` AS `Question 16__adv_notice_first_name`, ... FROM (
 SELECT ... `Question 16`.`ncapx_platform_accessinfo__adv_notice_first_name` AS `Question 16__ncapx_platform_accessinfo__adv_notice__62178281`, ... FROM `ncapx_platform_bid`
 LEFT JOIN (SELECT ..., `source`.`ncapx_platform_accessinfo__adv_notice_first_name` AS `ncapx_platform_accessinfo__adv_notice_first_name`,  FROM (SELECT ... `source`.`ncapx_platform_accessinfo__adv_notice_first_name` AS `ncapx_platform_accessinfo__adv_notice_first_name`, ... FROM (
 SELECT ..., `ncapx_platform_accessinfo`.`adv_notice_first_name` AS ... FROM `ncapx_platform_account`
 ...
 LEFT JOIN `ncapx_platform_account` `ncapx_platform_account__via__account_id` ON `source`.`ncapx_platform_accessinfo__account_id` = `ncapx_platform_account__via__account_id`.`id`) `source`) `Question 16` ON `ncapx_platform_bid`.`enrollment_id` = `Question 16`.`ncapx_platform_enrollment__id`) `source` LIMIT 2000",
     :params
     ([REDACTED]),
     :type :invalid-query}}],
2

Hi @snarfed

Let's ignore the "can see results in Metadata, but not when running the Model", since that's because another nested query happens, so the problem is not with Models, but multiple nested queries, Models just makes it worse.

It's a little difficult to decipher the redacted stacktrace, but my initial guess is that you're seeing this issue:
https://github.com/metabase/metabase/issues/20809
Can you try to re-create the problem with the Sample Database?

By the way, you are on 1.42.4 - Admin > Troubleshooting > see "Diagnostic Info"

3

Thanks for triaging! I'll see if I can repro on the sample db.

4

Hi again! I think I've reproduced this on the sample db. The error message is a bit different, but I'm guessing that's just H2 vs MySQL.

I first joined ORDERS and PRODUCTS, saved that query, and converted it to a model named Orders:

image
image1854×586 31.4 KB

I then joined PEOPLE with that Orders model, saved that query, and converted it to another model named Orderers:

image
image1774×514 13.2 KB

This second query fetched and visualized results fine while I was initially authoring it and converting it to a model. I then navigated away for a minute, did something else, re-opened it, and got this error:

Column "source.Question 51__EAN" not found; SQL statement: -- Metabase:: userID: 1 queryType: MBQL queryHash: 284811c489212cb0e552c18665d0ded856b3ea47b78f59dcfed3703affdb5e12 SELECT "source"."ID" AS "ID", "source"."ADDRESS" AS "ADDRESS", "source"."EMAIL" AS "EMAIL", "source"."PASSWORD" AS "PASSWORD", "source"."NAME" AS "NAME", "source"."CITY" AS "CITY", "source"."LONGITUDE" AS "LONGITUDE", "source"."STATE" AS "STATE", "source"."SOURCE" AS "SOURCE", "source"."BIRTH_DATE" AS "BIRTH_DATE", "source"."ZIP" AS "ZIP", "source"."LATITUDE" AS "LATITUDE", "source"."CREATED_AT" AS "CREATED_AT", "source"."Question 51__ID" AS "Question 51__ID", "source"."Question 51__USER_ID" AS "Question 51__USER_ID", "source"."Question 51__PRODUCT_ID" AS "Question 51__PRODUCT_ID", "source"."Question 51__SUBTOTAL" AS "Question 51__SUBTOTAL", "source"."Question 51__TAX" AS "Question 51__TAX", "source"."Question 51__TOTAL" AS "Question 51__TOTAL", "source"."Question 51__DISCOUNT" AS "Question 51__DISCOUNT", "source"."Question 51__CREATED_AT" AS "Question 51__CREATED_AT", "source"."Question 51__QUANTITY" AS "Question 51__QUANTITY", "source"."Question 51__ID" AS "Question 51__ID_2", "source"."Question 51__EAN" AS "Question 51__EAN", "source"."Question 51__TITLE" AS "Question 51__TITLE", "source"."Question 51__CATEGORY" AS "Question 51__CATEGORY", "source"."Question 51__VENDOR" AS "Question 51__VENDOR", "source"."Question 51__PRICE" AS "Question 51__PRICE", "source"."Question 51__RATING" AS "Question 51__RATING", "source"."Question 51__CREATED_AT" AS "Question 51__CREATED_AT_2" FROM (SELECT "PUBLIC"."PEOPLE"."ID" AS "ID", "PUBLIC"."PEOPLE"."ADDRESS" AS "ADDRESS", "PUBLIC"."PEOPLE"."EMAIL" AS "EMAIL", "PUBLIC"."PEOPLE"."PASSWORD" AS "PASSWORD", "PUBLIC"."PEOPLE"."NAME" AS "NAME", "PUBLIC"."PEOPLE"."CITY" AS "CITY", "PUBLIC"."PEOPLE"."LONGITUDE" AS "LONGITUDE", "PUBLIC"."PEOPLE"."STATE" AS "STATE", "PUBLIC"."PEOPLE"."SOURCE" AS "SOURCE", "PUBLIC"."PEOPLE"."BIRTH_DATE" AS "BIRTH_DATE", "PUBLIC"."PEOPLE"."ZIP" AS "ZIP", "PUBLIC"."PEOPLE"."LATITUDE" AS "LATITUDE", "PUBLIC"."PEOPLE"."CREATED_AT" AS "CREATED_AT", "Question 51"."ID" AS "Question 51__ID", "Question 51"."USER_ID" AS "Question 51__USER_ID", "Question 51"."PRODUCT_ID" AS "Question 51__PRODUCT_ID", "Question 51"."SUBTOTAL" AS "Question 51__SUBTOTAL", "Question 51"."TAX" AS "Question 51__TAX", "Question 51"."TOTAL" AS "Question 51__TOTAL", "Question 51"."DISCOUNT" AS "Question 51__DISCOUNT", "Question 51"."CREATED_AT" AS "Question 51__CREATED_AT", "Question 51"."QUANTITY" AS "Question 51__QUANTITY", "Question 51"."PRODUCTS - PRODUCT_ID__ID" AS "Question 51__PRODUCTS - PRODUCT_ID__ID", "Question 51"."PRODUCTS - PRODUCT_ID__EAN" AS "Question 51__PRODUCTS - PRODUCT_ID__EAN", "Question 51"."PRODUCTS - PRODUCT_ID__TITLE" AS "Question 51__PRODUCTS - PRODUCT_ID__TITLE", "Question 51"."PRODUCTS - PRODUCT_ID__CATEGORY" AS "Question 51__PRODUCTS - PRODUCT_ID__CATEGORY", "Question 51"."PRODUCTS - PRODUCT_ID__VENDOR" AS "Question 51__PRODUCTS - PRODUCT_ID__VENDOR", "Question 51"."PRODUCTS - PRODUCT_ID__PRICE" AS "Question 51__PRODUCTS - PRODUCT_ID__PRICE", "Question 51"."PRODUCTS - PRODUCT_ID__RATING" AS "Question 51__PRODUCTS - PRODUCT_ID__RATING", "Question 51"."PRODUCTS - PRODUCT_ID__CREATED_AT" AS "Question 51__PRODUCTS - PRODUCT_ID__CREATED_AT" FROM "PUBLIC"."PEOPLE" LEFT JOIN (SELECT "PUBLIC"."ORDERS"."ID" AS "ID", "PUBLIC"."ORDERS"."USER_ID" AS "USER_ID", "PUBLIC"."ORDERS"."PRODUCT_ID" AS "PRODUCT_ID", "PUBLIC"."ORDERS"."SUBTOTAL" AS "SUBTOTAL", "PUBLIC"."ORDERS"."TAX" AS "TAX", "PUBLIC"."ORDERS"."TOTAL" AS "TOTAL", "PUBLIC"."ORDERS"."DISCOUNT" AS "DISCOUNT", "PUBLIC"."ORDERS"."CREATED_AT" AS "CREATED_AT", "PUBLIC"."ORDERS"."QUANTITY" AS "QUANTITY", "PRODUCTS - PRODUCT_ID"."ID" AS "PRODUCTS - PRODUCT_ID__ID", "PRODUCTS - PRODUCT_ID"."EAN" AS "PRODUCTS - PRODUCT_ID__EAN", "PRODUCTS - PRODUCT_ID"."TITLE" AS "PRODUCTS - PRODUCT_ID__TITLE", "PRODUCTS - PRODUCT_ID"."CATEGORY" AS "PRODUCTS - PRODUCT_ID__CATEGORY", "PRODUCTS - PRODUCT_ID"."VENDOR" AS "PRODUCTS - PRODUCT_ID__VENDOR", "PRODUCTS - PRODUCT_ID"."PRICE" AS "PRODUCTS - PRODUCT_ID__PRICE", "PRODUCTS - PRODUCT_ID"."RATING" AS "PRODUCTS - PRODUCT_ID__RATING", "PRODUCTS - PRODUCT_ID"."CREATED_AT" AS "PRODUCTS - PRODUCT_ID__CREATED_AT" FROM "PUBLIC"."ORDERS" LEFT JOIN "PUBLIC"."PRODUCTS" "PRODUCTS - PRODUCT_ID" ON "PUBLIC"."ORDERS"."PRODUCT_ID" = "PRODUCTS - PRODUCT_ID"."ID") "Question 51" ON "PUBLIC"."PEOPLE"."ID" = "Question 51"."USER_ID") "source" LIMIT 2000 [42122-197]

image
image1280×1144 124 KB

One possible clue is the Question 51 part of the error column. It didn't originally show up while I was authoring the Orderers query/model, but it did later appear in the join UI and in column names and it's also here in the error column name. Seems like it should keep using the Orderers model name instead, at least in the query UI and columns, but not a big deal unless it's related to the bug.

5

@snarfed Awesome - now that makes a lot more sense (in a nested query perspective).
I wrote a little Inception story:
https://github.com/metabase/metabase/issues/22859 - upvote by clicking :+1: on the first post

1 Like