Tesseract - Casting Parameters for Presto/Trino SQL #9875
Description
Describe the bug With tesseract on, Cube doesn’t cast boolean and number parameters in presto/trino.
To Reproduce Steps to reproduce the behavior:
- Define the following cube.
cubes:
- name: test_cube
sql: >
SELECT 1 AS id, true AS condition, 10 as num UNION ALL
SELECT 2 AS id, false AS condition, 1 as num UNION ALL
SELECT 3 AS id, false AS condition, 2 as num
dimensions:
- name: id
sql: id
type: number
- name: condtion
sql: condtion
type: boolean
measures:
- name: num_sum
sql: num
type: sum
- Write a query with
conditionas a filter.
SELECT
id,
measure(num_sum)
FROM test_cube
WHERE condition
GROUP BY 1
Actual Result
The resulting SQL has condition = ‘true’ in the where clause.
SELECT
"test_cube".id "test_cube__id",
sum("test_cube".num) "test_cube__num_sum"
FROM
(
SELECT
1 AS id,
true AS condition,
10 as num
UNION ALL
SELECT
2 AS id,
false AS condition,
1 as num
UNION ALL
SELECT
3 AS id,
false AS condition,
2 as num
) AS "test_cube"
WHERE
("test_cube".condition = 'true')
GROUP BY
1
LIMIT
50000
Expected behavior
Cube should cast 'true' to a boolean.
SELECT
"test_cube".id "test_cube__id",
sum("test_cube".num) "test_cube__num_sum"
FROM
(
SELECT
1 AS id,
true AS condition,
10 as num
UNION ALL
SELECT
2 AS id,
false AS condition,
1 as num
UNION ALL
SELECT
3 AS id,
false AS condition,
2 as num
) AS "test_cube"
WHERE
("test_cube".condition = CAST('true' AS BOOLEAN)
GROUP BY
1
LIMIT
50000
Additional Context
I believe that this can be solved by adding the following to packages/cubejs-schema-compiler/src/adapter/PrestodbQuery.ts:
templates.tesseract.bool_param_cast = 'CAST({{ expr }} AS BOOLEAN)';
templates.tesseract.number_param_cast = 'CAST({{ expr }} AS DOUBLE)';
If you would like, we could do this.
Version: v1.3.39