Inconsistent conversion of time dimensions to DATETIME bigquery type
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
Dimensions of type `time` are automatically casted to `DATETIME(sql_expression, 'UTC')` when used as dimension, but not cased when it is used in the measure definition.
This causes a lot of type inconsistency in complicated schemas and is hard to maintain. The same time dimension based on one `TIMESTAMP` column is once interpreted as `TIMESTAMP` and in other situations as `DATETIME`.
**To Reproduce**
Test schema:
```
cube('bugcase01', {
public: true,
sql: `WITH test_data AS (
SELECT TIMESTAMP '2025-01-01' as timeCol, 45.67 as value UNION ALL
SELECT TIMESTAMP '2025-01-02', 78.90 UNION ALL
SELECT TIMESTAMP '2025-01-03', 23.45 UNION ALL
SELECT TIMESTAMP '2025-01-04', 89.12 UNION ALL
SELECT TIMESTAMP '2025-01-05', 34.56 UNION ALL
SELECT TIMESTAMP '2025-01-06', 67.89 UNION ALL
SELECT TIMESTAMP '2025-01-07', 23.55
) SELECT * FROM test_data
`,
dimensions: {
time_dim: {
sql: `${CUBE}.timeCol`,
type: 'time'
},
},
measures: {
test_measure: {
type: 'max',
sql: `(DATETIME_DIFF(CURRENT_DATETIME(), ${time_dim}, SECOND) / 60.0)`
}
}
})
```
Test query:
```
SELECT
DATE_TRUNC('day', bugcase01.time_dim),
MEASURE(bugcase01.test_measure)
FROM
bugcase01
GROUP BY
1
LIMIT
10000;
```
The above query is translated to the following BigQuery query ...
```
SELECT
DATETIME_TRUNC(DATETIME(`bugcase01`.timeCol, 'UTC'), DAY) `bugcase01__time_dim_day`,
max(
(
DATETIME_DIFF(CURRENT_DATETIME(), `bugcase01`.timeCol, SECOND) / 60.0
)
) `bugcase01__test_measure`
FROM
(
WITH test_data AS (
SELECT TIMESTAMP '2025-01-01' as timeCol, 45.67 as value UNION ALL
SELECT TIMESTAMP '2025-01-02', 78.90 UNION ALL
SELECT TIMESTAMP '2025-01-03', 23.45 UNION ALL
SELECT TIMESTAMP '2025-01-04', 89.12 UNION ALL
SELECT TIMESTAMP '2025-01-05', 34.56 UNION ALL
SELECT TIMESTAMP '2025-01-06', 67.89 UNION ALL
SELECT TIMESTAMP '2025-01-07', 23.55
)
SELECT * FROM test_data
) AS `bugcase01`
WHERE
(
`bugcase01`.timeCol >= TIMESTAMP(?)
AND `bugcase01`.timeCol <= TIMESTAMP(?)
)
GROUP BY
1
ORDER BY
1 ASC
LIMIT
10000
```
... which causes the following error, because `DATETIME_DIFF` expects either two `TIMESTAMP` or two `DATETIME` arguments and got one `DATETIME` and one `TIMESTAMP`. Please notice that `bugcase01.timeCol` is not casted to `DATETIME(..., 'UTC')` for `bugcase01__test_measure` but is casted for `bugcase01__time_dim_day`
Please also notice that there `WHERE ...` conditions built by cube are dealing with `TIMESTAMP(?)` query parameters and `timeCol` is not casted here too. I cannot cast it myself to anything other than `TIMESTAMP` in my schema definition because `WHERE` time-based conditions would stop working then.
BigQuery error:
```
No matching signature for function DATETIME_DIFF Argument types: DATETIME, TIMESTAMP, DATE_TIME_PART Signature: DATETIME_DIFF(DATETIME, DATETIME, DATE_TIME_PART) Argument 2: Unable to coerce type TIMESTAMP to expected type DATETIME Signature: DATETIME_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART) Argument 1: Unable to coerce type DATETIME to expected type TIMESTAMP at [2:97]
```
**Expected behavior**
The BigQuery compiled query has `timeCol` casted everywhere consitently to the same type (either `DATETIME` or `TIMESTAMP`).
**Version:**
1.2.4
**Additional context**
We upgraded recently from 1.1.2 and I believe it did not behave that way.
Contributor guide
Assessment
This issue has not been assessed yet.