Incorrect integer division implementation in the SQL API
- 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
Assessment
This issue has not been assessed yet.