Cube is getting invalid query generated by Metabase when aggregating by custom field #8243
Description
Failed SQL
Initial planning error: Error during planning: No field named 'source.count_distinct_users'. Valid fields are 'source.vl_watch_time_seconds', 'source.vl_session_time_seconds', 'source.bl_watch_session'.
SELECT
"source"."bl_watch_session" AS "bl_watch_session",
count(distinct "source"."count_distinct_users") AS "count"
FROM
(
SELECT
"public"."dataset"."vl_watch_time_seconds" AS "vl_watch_time_seconds",
"public"."dataset"."vl_session_time_seconds" AS "vl_session_time_seconds",
CASE
WHEN "public"."dataset"."vl_session_time_seconds" = "public"."dataset"."vl_watch_time_seconds" THEN TRUE
ELSE FALSE
END AS "bl_watch_session"
FROM
"public"."dataset"
) AS "source"
GROUP BY
"source"."bl_watch_session"
ORDER BY
"source"."bl_watch_session" ASC
Logical Plan
It did not generate a Logical Plan since it raised an error.
Version:
Cube: 0.35.29
Additional context
We are testing Metabase as BI Tool to integrate with Cube. We tried to create a custom dimension at Metabase and use a measure to aggregate upon it. However, the result query (seen above) does not bring the measure (distinct users) that was created at Cube at the subquery, and therefore it generates an error when Cube tries to translate the SQL API query to the SQL to our database.
The expected query would be something like the query below:
SELECT
CASE
WHEN "public"."dataset"."vl_session_time_seconds" = "public"."dataset"."vl_watch_time_seconds" THEN TRUE
ELSE FALSE
END AS "bl_watch_session",
count(distinct "public"."dataset"."count_distinct_users") AS "count"
FROM
"public"."dataset"
GROUP BY
1