Rolling-window measures generate an unrunnable plan: time-series join has no equality predicate

[tlangton3]

Describe the bug

A query with several rolling_window measures and a high-cardinality dimension generates a plan whose
intermediate row count is the product of (entities × window length × anchors), because the time-series
join that builds each rolling window has no equality predicate — only a date range. Entities are
separated afterwards by the GROUP BY, so the database has to materialise the full cross product first.

Each rolling measure also gets its own scan of the base table, even when two of them have byte-identical
filters.

With 3 calculated measures over 5 rolling sums, 10 group-by dimensions and a 33-day day-granularity
range, this produces ~50KB of SQL (13 CTEs, 5 base scans, 14 LEFT JOINs) and BigQuery terminates the
query on the guardrail limiting CPU relative to bytes scanned.

Query exceeded resource limits. This query used 133612 CPU seconds but would charge only 170M
Analysis bytes. This exceeds the ratio supported by the on-demand pricing model. Please consider
moving this workload to a capacity-based pricing model, which does not have this limit. 133612 CPU
seconds were used, and this query must use less than 43500 CPU seconds.

133,612 CPU seconds against 170MB of input, for a result capped at 5,000 rows — roughly 786 CPU
seconds per megabyte read. The input is small; the work done on it is not.

To Reproduce

1. Define the schema below, with CUBEJS_TESSERACT_SQL_PLANNER=true.
2. Query events_per_hour_7d, events_per_hour_30d and error_rate_7d, grouped by entity_id and
   activity_date at day granularity, with a 33-day dateRange.
3. Read the generated SQL from /cubejs-api/v1/sql.
4. Observe one base scan per rolling sum, and that each rolling CTE joins time_series to the base CTE
   on a date range only:

FROM time_series
LEFT JOIN cte_0 AS rolling_source
  ON rolling_source.activity_date_day >= TIMESTAMP(TIMESTAMP_SUB(time_series.date_from, INTERVAL 7 DAY))
 AND rolling_source.activity_date_day <  TIMESTAMP(time_series.date_from)
GROUP BY 1,2,…

5. At real cardinality the query becomes unrunnable. Ours is ~4,700 entities over 33 anchors: the five
   rolling CTEs together materialise ~12.6M intermediate rows to return at most 5,000.

Expected behavior

A query reading 170MB and returning at most 5,000 rows should not require 133,612 CPU seconds. The plan
for a rolling-window measure should stay proportional to the rows in the window, not to
(entities × window × anchors). The entity key is present in the GROUP BY of every one of these CTEs,
so it is known at plan time and could restrict the join rather than being applied after it.

Minimally reproducible Cube Schema

cubes:
  - name: daily_activity
    sql: >
      select 1 as entity_id, DATE '2026-08-01' as activity_date, 10 as events, 60 as duration_minutes, 1 as errors
      UNION ALL select 1, DATE '2026-08-02', 12, 70, 0
      UNION ALL select 1, DATE '2026-08-03', 8,  50, 2
      UNION ALL select 2, DATE '2026-08-01', 20, 90, 1
      UNION ALL select 2, DATE '2026-08-02', 15, 80, 0
      UNION ALL select 2, DATE '2026-08-03', 18, 85, 3
    dimensions:
      - name: entity_id
        sql: entity_id
        type: string
        primary_key: true
      - name: activity_date
        sql: "timestamp(activity_date)"
        type: time

    measures:
      - name: events_7d
        sql: events
        type: sum
        rolling_window: { trailing: 7 day, offset: start }
      - name: minutes_7d
        sql: duration_minutes
        type: sum
        rolling_window: { trailing: 7 day, offset: start }
      - name: events_30d
        sql: events
        type: sum
        rolling_window: { trailing: 30 day, offset: start }
      - name: minutes_30d
        sql: duration_minutes
        type: sum
        rolling_window: { trailing: 30 day, offset: start }
      - name: errors_7d
        sql: errors
        type: sum
        rolling_window: { trailing: 7 day, offset: start }

      - name: events_per_hour_7d
        sql: "{events_7d} / nullif({minutes_7d} / 60.0, 0)"
        type: number
      - name: events_per_hour_30d
        sql: "{events_30d} / nullif({minutes_30d} / 60.0, 0)"
        type: number
      - name: error_rate_7d
        sql: "{errors_7d} / nullif({events_7d}, 0)"
        type: number

This compiles and runs on the sample rows — it demonstrates the generated plan shape. The failure only
appears at production cardinality, and scales with the number of distinct entities.

Version:

1.7.33 (also reproduced identically on 1.7.30). Tesseract SQL planner enabled. BigQuery driver.

Additional context

Secondary observation, possibly a separate issue: the date filter on each base scan is written as

WHERE TIMESTAMP(DATETIME(timestamp(activity_date), 'UTC'))
        >= TIMESTAMP_SUB((SELECT TIMESTAMP(min(date_from)) FROM time_series), INTERVAL 7 DAY)

The bound is a scalar sub-select and the column is function-wrapped, so BigQuery cannot use it to
eliminate partitions — every partition is read on all five scans. This is not what causes the failure
above; the billed bytes are small either way. The legacy planner emitted a literal
here (BaseQuery.js dateFromStartToEndConditionSql, with a BigQuery-specific override in
BigqueryQuery.ts), so this changed with Tesseract. Happy to split it out if that's easier to track
separately.

I have the full generated SQL for both versions and a more detailed trace through the Tesseract planner
if either would be useful.