RedshiftQuery: negative offset on custom granularity produces invalid SQL (-- line)

Start in packages/cubejs-schema-compiler/src/adapter/RedshiftQuery.ts at subtractInterval, then reproduce the issue with the provided custom-granularity schema and the Data API SQL endpoint. Done means a negative offset emits valid Redshift DATEADD SQL without a leading -- and the example load request no longer fails; also inspect addInterval as suggested for the same case.

Describe the bug

When a custom granularity is defined with a negative offset (e.g., offset: -1 day as documented in Cube.dev's own sunday_week recipe), the Redshift dialect emits invalid SQL. The inner DATEADD renders --1; two consecutive minus signs, which Redshift parses as a SQL line-comment start. The rest of the line is treated as a comment, so the query fails with:

syntax error at or near "FROM" in context "(day, --1, ..."

Root cause subtractInterval in RedshiftQuery.ts uses literal string concatenation to negate the interval value, with no parenthesization:

result = `DATEADD(${datePart}, -${intervalValue}, ${result})`;

When intervalValue = -1, the template concatenates - + -1 producing --1.

Source: packages/cubejs-schema-compiler/src/adapter/RedshiftQuery.ts at v1.7.4, method subtractInterval.

To Reproduce

1. Define a cube with a time dimension and a custom granularity with a negative offset:

cubes:
  - name: orders
    sql: >
      SELECT 1 AS id, DATE '2026-01-07' AS event_ts, 100 AS amount
      UNION ALL SELECT 2, DATE '2026-01-14', 200
      UNION ALL SELECT 3, DATE '2026-01-21', 300
    dimensions:
      - name: event_ts
        type: time
        sql: "{CUBE}.event_ts"
        granularities:
          - name: sunday_week
            interval: 1 week
            offset: -1 day    # ← the buggy case
    measures:
      - name: total
        type: sum
        sql: "{CUBE}.amount"

2. Configure Cube against Redshift as the data source.
3. Query the cube via the Data API:

{
  "measures": ["orders.total"],
  "timeDimensions": [{
    "dimension": "orders.event_ts",
    "granularity": "sunday_week",
    "dateRange": ["2026-01-01", "2026-02-01"]
  }]
}

4. GET /cubejs-api/v1/sql shows the emitted SQL contains the bug:

SELECT DATEADD(day, -1, date_trunc('week', DATEADD(day, --1, ...)))
                                                     ^^^^^ SQL line comment starts here

5. GET /cubejs-api/v1/load fails: Error: syntax error at or near "FROM"

Expected behavior

The inner DATEADD should render as valid SQL, e.g. DATEADD(day, 1, ...) ; the negation computed as -(intervalValue) = -(-1) = 1. Full expected SQL:

SELECT DATEADD(day, -1, date_trunc('week', DATEADD(day, 1, ...)))

Minimally reproducible Cube Schema

See "To Reproduce" above. Same bug reproduces regardless of yaml vs JS schema format, the defect is in the SQL emitter, not the schema parser.

Version:
1.7.4 through 1.7.16 (latest as of 2026-07-31). Verified byte-identical at v1.7.4 and on master.

Additional context

Suggested fix two clean options in RedshiftQuery.ts:

// Option A: parenthesize
result = `DATEADD(${datePart}, -(${intervalValue}), ${result})`;

// Option B: compute negation in JS (cleaner and never emits leading `--`)
result = `DATEADD(${datePart}, ${-intervalValue}, ${result})`;

The same fix pattern likely applies to addInterval if any other code path can pass a negative value that becomes doubly-negated.

Related the DuckDB dialect had the same bug class, fixed in #11272 (v1.7.3, 2026-07-16). Redshift's subtractInterval was not touched by that PR. A wider audit of the other dialect overrides of subtractInterval / addInterval (Postgres uses +/- interval '...' and is safe; MSSQL, BigQuery, Snowflake, Athena, Databricks etc. should be checked) is likely worthwhile.

Workaround use a positive offset that's mathematically equivalent modulo the interval length. For a 1-week interval, offset: 6 days ≡ offset: -1 day (both anchor buckets to Sunday). This avoids the -- string entirely. Verified empirically on Cube Cloud 1.7.4: 10 Sunday-anchored buckets returned, no SQL syntax error.