cube-js / cube-js/cube

Inconsistent conversion of time dimensions to DATETIME bigquery type

Open
#9,245 2 comments 2 reactions 1 assignee Claimed by @igorlukanin View on GitHub
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

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.