Tesseract generates unsupported named WINDOW clause for Amazon Redshift
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Description
When `CUBEJS_TESSERACT_SQL_PLANNER=true` is enabled with the Redshift driver (`@cubejs-backend/redshift-driver`), measures using `multi_stage: true` (rolling windows, time shifts) fail at runtime with:
```
error: WINDOW definition is not supported
```
## Root Cause
Tesseract generates SQL using the **named WINDOW clause** (SQL:2003):
```sql
SELECT SUM(x) OVER w FROM table WINDOW w AS (PARTITION BY col ORDER BY date)
```
Amazon Redshift does not support named WINDOW definitions. Redshift is based on PostgreSQL 8.0.2, and named WINDOW clause support was added in PostgreSQL 8.4. Redshift has never backported this feature.
Reference: [AWS Redshift unsupported PostgreSQL features](https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html)
## Reproduction
1. Use `@cubejs-backend/redshift-driver` with Cube v0.35
2. Define a measure with `multi_stage: true`:
```yaml
measures:
- name: wau_count
sql: "CASE WHEN {active_day} = 1 THEN {provider_id} END"
type: count_distinct
rolling_window:
trailing: 7 day
multi_stage: true
```
3. Enable `CUBEJS_TESSERACT_SQL_PLANNER=true`
4. Query the measure
**Expected**: Query executes successfully
**Actual**: `error: WINDOW definition is not supported`
Without Tesseract enabled, the measures fail at compile time: `(measures.wau_count.multiStage = true) is not allowed`
## Precedent
This is the same pattern as #9567 (Tesseract generating PostgreSQL dialect for MSSQL), which was fixed in PRs #10342 and #10343 by adding database-specific SQL templates.
Redshift's query class likely needs a similar override to inline window specifications instead of using named WINDOW definitions.
## Environment
- Cube version: 0.35
- Driver: `@cubejs-backend/redshift-driver`
- Database: Amazon Redshift (ra3.xlplus)
- Platform: ECS Fargate
## Workaround
Keep `CUBEJS_TESSERACT_SQL_PLANNER` disabled (default) and avoid `multi_stage: true` on measures. Compute rolling windows in upstream dbt models instead.
Contributor guide
Assessment
This issue has not been assessed yet.