SQL API fails when aggregating using EXTRACT on a timestamp
- 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
Assessment
This issue has not been assessed yet.