cube-js / cube-js/cube

Pre-aggregation with Time Dimension Generates Invalid DATETIME Cast

Open
#9,958 1 comment 2 reactions 0 assignees View on GitHub
driver:bigquery pre-aggregations
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 5h
Merged PRs (30d)
193

Description

Hello,

When we use a model with pre-aggregation by a time dimension, Cube stores the created_at column in the pre-aggregated table as a timestamp. However, when we query this pre-aggregated table, Cube generates a native SQL query that casts the placeholder to DATETIME, which causes the database to throw an error.

```
No matching signature for operator >= for argument types: TIMESTAMP, DATETIME Signature: T1 >= T1 Unable to find common supertype for templated argument Input types for : {TIMESTAMP, DATETIME} at [1:143]
```

```js
{
"timeDimensions": [
{
"dimension": "users.created_at",
"granularity": "day",
"dateRange": "this week"
}
]
}
```

```sql
SELECT
`users__created_at_day` `users__created_at_day`
FROM
bi_dev_cube.users_main AS `users__main`
WHERE
(
`users__created_at_day` >= DATETIME(TIMESTAMP(?))
AND `users__created_at_day` <= DATETIME(TIMESTAMP(?))
)
GROUP BY
1
ORDER BY
1 ASC
LIMIT
1000
```

```yml
cubes:
- name: users
sql: (select 1 as id, current_timestamp as created_at, current_timestamp as
last_activity_at, 'BY' as country)

dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: created_at
sql: created_at
type: time
- name: last_activity_at
sql: last_activity_at
type: time
- name: country
sql: country
type: string

measures:
- name: active_users
type: count_distinct
sql: id

pre_aggregations:
- name: main
external: false
measures:
- users.active_users
dimensions:
- users.country
refreshKey:
every: 1 hour
time_dimension: created_at
granularity: day
partition_granularity: month

```

Cube: 1.3.66
Database: BigQuery

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.