Tesseract generates unsupported named WINDOW clause for Amazon Redshift

[paschmaria]

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):

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

Reproduction

1. Use @cubejs-backend/redshift-driver with Cube v0.35
2. Define a measure with multi_stage: true:

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.