Metabase Query with ABS function throw error with Pivot table

[itestyoy]

Hi!

Based on docs, Cube supports ABS in projections (e.g., SELECT).
But when we use a pivot table in Metabase, it generates a query like this, and then Cube throws an error.

Error:

Error executing query: ERROR: Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

Query:

select
    "source"."calendar_retention_by_day" as "calendar_retention_by_day",
    "source"."pivot-grouping" as "pivot-grouping",
    sum("source"."measure__af_ad_revenue") as "sum_2"
from
    (
        select
            "public"."cohort_analysis"."measure__af_ad_revenue"
            as "measure__af_ad_revenue",
            "public"."cohort_analysis"."cohort_date" as "cohort_date",
            "public"."cohort_analysis"."calendar_retention_by_day"
            as "calendar_retention_by_day",
            abs(0) as "pivot-grouping"
        from "public"."cohort_analysis"
        where
            (
                "public"."cohort_analysis"."cohort_date"
                >= cast(cast((now() + interval '-7 day') as date) as timestamp)
            )
            and (
                "public"."cohort_analysis"."cohort_date"
                < cast(cast(now() as date) as timestamp)
            )
    ) as "source"
group by "source"."calendar_retention_by_day", "source"."pivot-grouping"
order by "source"."calendar_retention_by_day" asc, "source"."pivot-grouping" asc

Can you add abs function support?

cube: v1.2.9 (push_dowh = false)
metabase: v0.53.5

[igorlukanin]

If you turn the query pushdown on, this query should work. Could you please try that?

[itestyoy]

Hi @igorlukanin!

With pushdown on, it works, but we can't use this mode because of this:

#9330
#8993