Tesseract: multi_stage time_shift returns NULL when queried through a view (pre-agg partition range not widened)

[puneet-bdp]

Describe the bug

A multi_stage measure with a time_shift returns NULL when the query goes through a view, if the query's date range does not itself already contain the shifted period. The identical query against the cube returns correct values.

The cause is pre-aggregation partition selection: for a shifted read the matched date range must be widened backwards by the shift interval. That widening is skipped for view-qualified queries, so the shifted leaf scans a partition set that cannot contain its rows and yields NULL — silently, with no error.

This is distinct from #11030 (FILTER_PARAMS, fixed in #11191) and #11240 (joined-cube dimensions). It reproduces with literal date ranges, no FILTER_PARAMS, and no joined cubes.

To Reproduce

Model — one cube with a weekly-partitioned rollup, and a view over it:

cubes:
  - name: orders
    sql_table: public.orders
    dimensions:
      - name: created_at
        sql: created_at
        type: time
    measures:
      - name: amount
        sql: amount
        type: sum
      - name: amount_prior_week
        type: number
        sql: "{amount}"
        multi_stage: true
        time_shift:
          - time_dimension: created_at
            interval: 7 day
            type: prior
    pre_aggregations:
      - name: daily_rollup
        type: rollup
        measures: [amount]
        time_dimension: created_at
        granularity: day
        partition_granularity: week

views:
  - name: orders_view
    cubes:
      - join_path: orders
        includes: [created_at, amount, amount_prior_week]

Query amount + amount_prior_week, grouped by created_at (day), with a literal dateRange spanning less than one partition beyond the shift — e.g. exactly 7 days for a 7-day shift:

• via the view (orders_view.*) → amount_prior_week is NULL for every row
• via the cube (orders.*) → correct values
• widening the range to include the prior week (e.g. 14 days) makes the view query correct too, because the unshifted range then coincidentally covers the shifted data

Environment: self-hosted, Tesseract enabled (CUBEJS_TESSERACT_SQL_PLANNER=true, CUBEJS_TESSERACT_PRE_AGGREGATIONS=true). Reproduced on v1.6.70; code inspection indicates v1.7.18 is unchanged (see "Versions" below).

Expected behavior

The view query returns the same values as the cube query. Pre-aggregations are a transparent performance optimization, so a query's result must not depend on whether a rollup matched.

(Refusing to match the pre-aggregation and falling back would also be acceptable — slower but correct. Matching and returning NULL is the problematic outcome.)

Observable signal

The pre-aggregation description exposes the difference directly. Same query, only the namespace differs:

queried via	matchedTimeDimensionDateRange
view	2026-08-03 … 2026-08-09 — not widened
cube	2026-07-27 … 2026-08-09 — widened by exactly the 7-day shift

Both carry usageMapping with 2 usages, so mergeUsageDateRanges runs in both cases — the shifted usage simply carries an unshifted range in the view case, making the merge a no-op.

Root cause

extract_date_range() widens the range only when this lookup hits:

// rust/cube/cubesqlplanner/cubesqlplanner/src/logical_plan/optimizers/pre_aggregation/optimizer.rs
time_shifts.dimensions_shifts.get(&base_filter.member_name())

• The map keys are reference-chain-resolved, cube-qualified names. all_time_members() resolves each symbol before it becomes a key, and the Dimensions variant inserts the declaration's own cube symbol (planner/query_properties.rs, ts.dimension.full_name()).
• base_filter.member_name() is member_evaluator().full_name(), and BaseFilter::member_evaluator() calls only resolve_base_symbol — it peels the TimeDimension wrapper but never walks the reference chain (planner/filter/base_filter.rs).

A view member is a reference to the underlying cube member, so its unresolved name never equals the resolved key, the lookup misses, and no shift is applied.

Suggested fix

Resolve the chain and retry — an exact second lookup, using the same identity mechanism already used elsewhere (member_chain_eq):

let resolved = base_filter.member_evaluator().resolve_reference_chain().full_name();
time_shifts
    .dimensions_shifts
    .get(&base_filter.member_name())
    .or_else(|| time_shifts.dimensions_shifts.get(&resolved))

MemberSymbol::resolve_reference_chain is pub fn (self: Rc<Self>) -> Rc<MemberSymbol> (planner/symbols/member_symbol.rs), and member_evaluator() already returns an Rc<MemberSymbol>. A member resolving to a different cube's dimension still does not match, so this cannot apply a shift that would not otherwise apply.

Fixing at the insert site instead looks worse: dimensions_shifts is also consumed by extract_time_shifts for render-time SQL, so re-keying there would change rendering as well.

Versions

• Reproduced: v1.6.70
• extract_date_range and time_shift_state.rs are byte-identical at v1.7.18, member_evaluator() is unchanged, and the pre-aggregation optimizer contains no view-specific handling at either tag — so v1.7.18 appears affected as well (not executed there).
• Present since the usage-range merging landed (~v1.6.40).

Impact

Views are the recommended query interface for BI tools, so in practice most consumer queries take the broken path. The failure is silent: a "week over week" column renders blank and is indistinguishable from genuinely absent data. Shorter windows are affected worst — for an interval I and a range of N days, only the last N − I days get values, so a "last 7 days" dashboard with a 7-day shift is entirely blank.