Cube is getting invalid query generated by Metabase when aggregating by custom field

[LarissaSiqueirabp]

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

[igorlukanin]

Hi @LarissaSiqueirabp 👋

Could you please set CUBESQL_SQL_PUSH_DOWN=true on your Cube instance and try again to see if anything changes?