Joining two saved questions on custom column results in unknown column error

Hi! I noticed a couple of similar threads on here already, and there have also been similar issues that have been closed, but I can't tell if my issue maps to one of these pre-existing ones exactly.

I have two saved questions. Each of them contains a custom column called "key". When I try to create a new question, in which both saved questions are joined on this custom column, I get an error message saying "ERROR: column Question 1571.key does not exist".

I am on Metabase version 39.5. Thanks for any help!

Hi @snabar
I cannot reproduce on latest 0.40.3.1, so please provide the full stacktrace and/or screenshots of the Notebook questions.
It would also be very helpful if you linked to the topics/issues.

Hi @flamber, thanks for following up. Here are screenshots of the notebook questions, and the generated sql. It looks like the generated sql does not create the custom column for the second saved question in the join:

First saved question:


First custom column:

Second saved question:


Second custom column:

Joined questions:

Translated SQL:

SELECT "source"."deployment_ended_at" AS "deployment_ended_at", "source"."application_id" AS "application_id", "source"."count" AS "count", "source"."key" AS "key", "Question 1571"."incident_created_at" AS "Question 1571__incident_created_at", "Question 1571"."application_id" AS "Question 1571__application_id", "Question 1571"."count" AS "count_2", "Question 1571"."key" AS "key_2"
FROM (
SELECT "source"."deployment_ended_at" AS "deployment_ended_at", "source"."application_id" AS "application_id", "source"."count" AS "count", concat((CAST(date_trunc('week', CAST((CAST("source"."deployment_ended_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')), "source"."application_id") AS "key"
FROM (
SELECT (CAST(date_trunc('week', CAST((CAST("public"."deployment_global"."deployment_ended_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')) AS "deployment_ended_at", "public"."deployment_global"."application_id" AS "application_id", count(distinct "public"."deployment_global"."deployment_uid") AS "count"
FROM "public"."deployment_global"
WHERE ("public"."deployment_global"."environment" = 'Prod' AND "public"."deployment_global"."deployment_status" = 'Success')
GROUP BY (CAST(date_trunc('week', CAST((CAST("public"."deployment_global"."deployment_ended_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')), "public"."deployment_global"."application_id"
ORDER BY (CAST(date_trunc('week', CAST((CAST("public"."deployment_global"."deployment_ended_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')) ASC, "public"."deployment_global"."application_id" ASC
) "source"
) "source"
LEFT JOIN (
SELECT "source".*
FROM (
SELECT "source"."incident_created_at" AS "incident_created_at", "source"."application_id" AS "application_id", "source"."application_id" AS "application_id_2"
FROM (
SELECT (CAST(date_trunc('week', CAST((CAST("public"."incident_global"."incident_created_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')) AS "incident_created_at", "public"."incident_global"."application_id" AS "application_id", count(distinct "public"."incident_global"."incident_id") AS "count"
FROM "public"."incident_global" WHERE ("public"."incident_global"."incident_severity" = 'Sev1' OR "public"."incident_global"."incident_severity" = 'Sev2' OR "public"."incident_global"."incident_severity" = 'Sev3')
GROUP BY (CAST(date_trunc('week', CAST((CAST("public"."incident_global"."incident_created_at" AS timestamp) + (INTERVAL '1 day')) AS timestamp)) AS timestamp) + (INTERVAL '-1 day')), "public"."incident_global"."application_id"
) "source"
) "source"
) "Question 1571" ON "source"."key" = "Question 1571"."key"
LIMIT 1048575

@snabar I have created an issue for this:
https://github.com/metabase/metabase/issues/17770 - upvote by clicking :+1: on the first post