cube-js / cube-js/cube

SQL API fails when aggregating using EXTRACT on a timestamp

Open
#8,313 5 comments 2 reactions 0 assignees View on GitHub
question
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Failed SQL**
I'm seeing some issues when using timestamp EXTRACT. See below:

```sh
# It works if I write the query out like so:
> SELECT EXTRACT(YEAR FROM created_at), order_count FROM public.orders_cube;

datepart(Utf8("YEAR"),orders_cube.created_at) | order_count
-----------------------------------------------+-------------
2019 | 2052
2020 | 2043
2021 | 2031
2022 | 2122
2023 | 1752
(5 rows)

# But using a column alias for the EXTRACT column triggers an error:
> SELECT EXTRACT(YEAR FROM created_at) as created_at_year, order_count FROM public.orders_cube;
# ERROR: Error during rewrite: Unexpected panic. Reason: Unexpected expression node: AggregateAggrExpr([]). Please check logs for additional information.
# QUERY: SELECT EXTRACT(YEAR FROM created_at) AS created_at_year, order_count FROM public.orders_cube

# and GROUP BY doesn't work when using MEASURE as the aggregation function...:
> SELECT EXTRACT(YEAR FROM created_at), MEASURE(order_count) FROM public.orders_cube GROUP BY 1;
# ERROR: Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.
# QUERY: SELECT EXTRACT(YEAR FROM created_at), MEASURE(order_count) FROM public.orders_cube GROUP BY 1

# ...but it works when using another aggregation function
> SELECT EXTRACT(YEAR FROM created_at), COUNT(order_count) FROM public.orders_cube GROUP BY 1;

datepart(Utf8("YEAR"),orders_cube.created_at) | COUNT(orders_cube.order_count)
-----------------------------------------------+--------------------------------
2019 | 2052
2020 | 2043
2021 | 2031
2022 | 2122
2023 | 1752
(5 rows)

> SELECT EXTRACT(YEAR FROM created_at), SUM(order_count) FROM public.orders_cube GROUP BY 1;
datepart(Utf8("YEAR"),orders_cube.created_at) | SUM(orders_cube.order_count)
-----------------------------------------------+------------------------------
2019 | 2052
2020 | 2043
2021 | 2031
2022 | 2122
2023 | 1752
(5 rows)
```

**Version:**
0.35.41

**Additional context**
- This is what I spotted in the logs as these errors were happening
`thread 'tokio-runtime-worker' panicked at /__w/cube/cube/rust/cubesql/cubesql/src/compile/rewrite/converter.rs:1203:14:`
`Unexpected expression node: AggregateAggrExpr([])`
- Tried using `CUBESQL_SQL_PUSH_DOWN=true`, but that did not help.

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.