cube-js / cube-js/cube

Tesseract generates unsupported named WINDOW clause for Amazon Redshift

Open
#10,567 1 comment 0 reactions 1 assignee Claimed by @igorlukanin View on GitHub
data modeling:tesseract driver: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

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.