Multiple joined Saved Questions, which are based on the same table, causes incorrect field references #18502
Assignees
Labels
.Backend
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
Milestone
Comments
flamber
added
Type:Bug
nemanjaglumac
added a commit
that referenced
this issue
Oct 17, 2021
nemanjaglumac
pushed a commit
that referenced
this issue
Oct 17, 2021
|
This is probably going to be part of the #18580 group of fixes |
|
Generated SQL looks like (Postgres) SELECT
"Q2"."created_at" AS "Q2__created_at",
"source"."count" AS "count",
"Q2"."birth_date" AS "Q2__birth_date",
"Q2"."count" AS "Q2__count"
FROM (
SELECT
date_trunc('month', CAST("public"."people"."created_at" AS timestamp)) AS "created_at",
count(*) AS "count"
FROM
"public"."people"
GROUP BY
date_trunc('month', CAST("public"."people"."created_at" AS timestamp))
ORDER BY
date_trunc('month', CAST("public"."people"."created_at" AS timestamp)) ASC
) "source"
LEFT JOIN (
SELECT
date_trunc('month', CAST("public"."people"."birth_date" AS timestamp)) AS "birth_date",
count(*) AS "count"
FROM
"public"."people"
GROUP BY
date_trunc('month', CAST("public"."people"."birth_date" AS timestamp))
ORDER BY
date_trunc('month', CAST("public"."people"."birth_date" AS timestamp)) ASC
) "Q2"
ON date_trunc('month', CAST("source"."created_at" AS timestamp))
= date_trunc('month', CAST("Q2"."birth_date" AS timestamp))
LIMIT 3 |
|
I noticed the above SQL looked really silly with all that unnecesary casting so I made a few adjustments and the improved SQL is now: SELECT
"Q2"."created_at" AS "Q2__created_at",
"source"."count" AS "count",
"Q2"."birth_date" AS "Q2__birth_date",
"Q2"."count" AS "Q2__count"
FROM (
SELECT
date_trunc('month', "public"."people"."created_at") AS "created_at",
count(*) AS "count"
FROM
"public"."people"
GROUP BY
date_trunc('month', "public"."people"."created_at")
ORDER BY
date_trunc('month', "public"."people"."created_at") ASC
) "source"
LEFT JOIN (
SELECT
date_trunc('month', "public"."people"."birth_date") AS "birth_date",
count(*) AS "count"
FROM
"public"."people"
GROUP BY
date_trunc('month', "public"."people"."birth_date")
ORDER BY
date_trunc('month', "public"."people"."birth_date") ASC
) "Q2"
ON date_trunc('month', "source"."created_at")
= date_trunc('month', "Q2"."birth_date")
LIMIT 3I might split that stuff on into a separate PR or just do it as part of the fix for this issue. |
|
The error these days is Clearly someone is on drugs. For some reason it thinks |
|
Split the PG cast stuff off into #19790 |
|
Ok, so this is a bug in the QP As you can see it's incorrectly rewriting the Before: (mt/mbql-query people
{:source-query {:source-table $$people
:breakout [!month.created_at]
:aggregation [[:count]]
:order-by [[:asc !month.created_at]]}
:joins [{:source-query {:source-table $$people
:breakout [!month.birth_date]
:aggregation [[:count]]
:order-by [[:asc !month.birth_date]]}
:alias "Q2"
:condition [:= !month.created_at !month.&Q2.birth_date]
:fields [&Q2.birth_date &Q2.*count/BigInteger]
:strategy :left-join}]
:fields [!default.created_at
*count/BigInteger
&Q2.birth_date
&Q2.*count/BigInteger]
:limit 3})After: (mt/mbql-query people
{:source-query {:source-table $$people
:breakout [!month.created_at]
:aggregation [[:count]]
:order-by [[:asc !month.created_at]]}
:joins [{:source-query {:source-table $$people
:breakout [!month.birth_date]
:aggregation [[:count]]
:order-by [[:asc !month.birth_date]]}
:alias "Q2"
:condition [:= !month.created_at !month.&Q2.birth_date]
:fields [&Q2.birth_date &Q2.*count/BigInteger]
:strategy :left-join}]
:fields [!default.&Q2.created_at
*count/BigInteger
&Q2.birth_date
&Q2.*count/BigInteger]
:limit 3}) |
This was referenced Jan 20, 2022
This was referenced Dec 1, 2022
This was referenced May 8, 2024
Merged
Merged
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the bug
When joining multiple Saved Questions, which are based on the same table, but with different group-by fields, then query fails because of incorrect table alias in the field reference.
Regression since 0.38.0
To Reproduce
Column "Q2.CREATED_AT" not found;(other databases might error withERROR: column Q2.CREATED_AT does not exist)SQL generated
SQL 0.37.8
Full stacktrace
Information about your Metabase Installation:
Tested 0.37.8 thru 0.41.0 and master - regression since 0.38.0
Additional context
Perhaps related to #14787 and #16389 and #17770, which are also regressions since 0.38.0, but this behaves differently.
The text was updated successfully, but these errors were encountered: