SQL API: ISOYEAR extraction is not working
- 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**

**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
Assessment
This issue has not been assessed yet.