cube-js / cube-js/cube

Incorrect integer division implementation in the SQL API

Open
#8,359 3 comments 0 reactions 2 assignees Claimed by @paveltiunov View on GitHub
api:sql
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**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
=

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.