Incorrect integer division implementation in the SQL API

[pauldheinrichs]

Describe the bug When utilizing the share expression in Metabase.

It generates a decimal value representing a percentage, the problem is cube converts assumes the as an int so the Share result expression is always either 0 or 1 rendering it pretty much useless. Changing the casting manually to enforce the usages of ::double resolves the issue.

Latest Cube release Driver: Redshift CUBE_SQL_PUSH_DOWN: enabled

Problem SQL

 DATE_TRUNC('week', "source"."created_date") AS "created_date",
  SUM(
    CASE
      WHEN "source"."count" = 1 THEN 1
      ELSE 0.0
    END
  ) / COUNT(*) AS "one video"

Manually casting either side of these expressions to ::double resolves the problem IE:

  SUM(
    CASE
      WHEN "source"."count" = 1 THEN 1
      ELSE 0.0
    END
  ) / COUNT(*)::double AS "one video"

Full SQL:

SELECT
  DATE_TRUNC('week', "source"."created_date") AS "created_date",
  SUM(
    CASE
      WHEN "source"."count" = 1 THEN 1
      ELSE 0.0
    END
  ) / COUNT(*) AS "one video"
FROM
  (
    SELECT
      DATE_TRUNC('week', "public"."table"."created_date") AS "created_date",
      "public"."table"."user_id" AS "user_id",
      count(distinct "public"."table"."video_id") AS "count"
    FROM
      "public"."table"
   
WHERE
      (
        "public"."table"."created_date" >= DATE_TRUNC('quarter', NOW())
      )
     
   AND (
        "public"."table"."created_date" < DATE_TRUNC('quarter', (NOW() + INTERVAL '3 month'))
      )
   
GROUP BY
      DATE_TRUNC('week', "public"."table"."created_date"),
      "public"."all_videos"."user_id"
   
ORDER BY
      DATE_TRUNC('week', "public"."table"."created_date") ASC,
      "public"."table"."user_id" ASC
  ) AS "source"
GROUP BY
  DATE_TRUNC('week', "source"."created_date")
ORDER BY
  DATE_TRUNC('week', "source"."created_date") ASC
=

[igorlukanin]

Hi @pauldheinrichs 👋

I assume this is not a bug but rather Cube conforming to the SQL standard (and, more specifically, it's implementation in Postgres). As you can see in the Postgres documentation, "(integer division truncates results." If you'd like to use floating-point division, at least one of the arguments should be floating-point. Casting them, either on the data model side or the querying side, is a way to go here.

[pauldheinrichs]

@igorlukanin Appreciate the response, but i believe the following part of the query 0.0 is what converts it to a floating

 select SUM(
    CASE
      WHEN 1 = 1 THEN 1
      ELSE 0.0
    END
  ) / 1 AS "one video"

To confirm this i've run the following query in postgres, redshift, and cube being the only engine which results in a non-floating point number

Cube Query:

Redshift Query:

Postgres Query:

[igorlukanin]

I see. That makes sense! The workaround here would be to multiple the whole CASE expression by 1.0. However, I believe we'll conform with the Postgres implementation eventually.