"Break out" drillthrough fails on questions with group by implicitly joined column and filter after aggregation #67228
Description
Describe the bug
A lot of things need to happen to see this one, but bad news is that those things happen in our Examples dashboard, so we have broken drillthrough in Examples again:
If you have a question with:
- A summary by an implicitly joined column (like Product -> Category on the orders table)
- A filter after summary
then "break out by" drill-through will fail. This is a regression: it works in v56 but not in v57+
If the summary by a column in the same table, or there's no filter after summary, then everything's fine.
The error on h2 sample DB is:
Column "source.PRODUCTS__via__PRODUCT_ID__CATEGORY" not found; SQL statement:
-- Metabase:: userID: 3 queryType: MBQL queryHash: 934fb7fd8cb15f200c2eb9e94a1d21ba8052583408f80f43a40af03592fe123e
SELECT "source"."PRODUCTS__via__PRODUCT_ID__CATEGORY" AS "PRODUCTS__via__PRODUCT_ID__CATEGORY", "source"."PEOPLE__via__USER_ID__SOURCE" AS "PEOPLE__via__USER_ID__SOURCE", "source"."count" AS "count" FROM (SELECT "PRODUCTS__via__PRODUCT_ID"."CATEGORY" AS "PRODUCTS__via__PRODUCT_ID__CATEGORY", "PEOPLE__via__USER_ID"."SOURCE" AS "PEOPLE__via__USER_ID__SOURCE", COUNT(*) AS "count" FROM "PUBLIC"."ORDERS" LEFT JOIN "PUBLIC"."PRODUCTS" AS "PRODUCTS__via__PRODUCT_ID" ON "PUBLIC"."ORDERS"."PRODUCT_ID" = "PRODUCTS__via__PRODUCT_ID"."ID" LEFT JOIN "PUBLIC"."PEOPLE" AS "PEOPLE__via__USER_ID" ON "PUBLIC"."ORDERS"."USER_ID" = "PEOPLE__via__USER_ID"."ID" WHERE "source"."PRODUCTS__via__PRODUCT_ID__CATEGORY" = ? GROUP BY "PRODUCTS__via__PRODUCT_ID"."CATEGORY", "PEOPLE__via__USER_ID"."SOURCE" ORDER BY "PRODUCTS__via__PRODUCT_ID"."CATEGORY" ASC, "PEOPLE__via__USER_ID"."SOURCE" ASC) AS "source" WHERE "source"."count" >= 1 LIMIT 2000 [42122-214]
The error on postgres sample DB is:
ERROR: missing FROM-clause entry for table "source"
Position: 771
To Reproduce
You can observe it on the Examples dashboard on a new instance: go to "Portfolio performance" tab and use the "Product breakdown" sunburst chart.
To make your own:
- Create a GUI question on the sample database: Count of Orders by Product Category, filtered by Count > 1
- Visualize.
- Click on any count and select "Break out by" in the action menu
- Select Category > User: Source (it actually doesn't matter what you select here)
- Error
Note the new filter on "Products Via Product ID Category" added.
Optional: go back to the original question before drill-through, remove the filter Count > 1 filter, repeat the steps - see that drill-through works.
Expected behavior
Drill-though works: instead of filter on "Products Via Product ID Category", the filter on "Category " is added like in v56
Logs
No response
Information about your Metabase installation
v57+, masterSeverity
P2, it's a regression
Additional context
No response