cube-js / cube-js/cube

SQL API: ISOYEAR extraction is not working

Open
#9,227 0 comments 0 reactions 1 assignee Claimed by @igorlukanin View on GitHub
api:sql
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
**Issue**: Extracting ISOYEAR is critical for us to calculate weeks correctly in SQL WHERE clause:

**Query**:
```
SELECT ...
FROM ...
WHERE week = EXTRACT(ISOYEAR FROM CURRENT_DATE - INTERVAL '1 week') * 100
```

**DB Engine**: This functionality is working in PostgreSQL but does not work when Cube is generating query, most likely because it is not supported in DataFusion which is used under the hood.

**EXTRACT function**: Other similar queries are working if we extract WEEK, YEAR, etc but in this case we need exactly ISOYEAR extraction.
**Workarounds**: Other possible workarounds are not working because of limited SQL functionality.
**Pushdown**: Enabling Pushdown does not help, there is still the same error.
**Dimensions in Cube**: This function can be applied to Cube dimensions in **sql** parameter but we have to be able to apply it in SQL API.

**Error**: "Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information

**To Reproduce**
Steps to reproduce the behavior:
1. Create Cube that has date dimensions
2. In **Superset** run query against Cube where ISOYEAR should be used in WHERE clause: `WHERE week = EXTRACT(**ISOYEAR** FROM CURRENT_DATE - INTERVAL '1 week') * 100`
3. **Error**: "Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information

**Expected behavior**
ISOYEAR extraction is working as expected, the correct result is selected and there is no error.

**Screenshots**

![Image](https://github.com/user-attachments/assets/61a8a14c-d516-4765-b9c1-c77f99ad57f1)

**Version:**
[1.2.4]

**Additional Context**:
**DataFusion Bug Report** (ISOYEAR/ISOWEEK): https://github.com/apache/datafusion/issues/14524
**Arrow-rs Bug report** (ISOYEAR/ISOWEEK): https://github.com/apache/arrow-rs/issues/7115

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.