Tesseract: multi_stage time_shift measure returns empty results when time filter is pushed into FILTER_PARAMS (relative/dynamic date filters)

[puneet-bdp]

Describe the bug

A multi_stage measure with a time_shift (e.g. a prior-year YoY measure) returns empty results when the time-dimension filter is one that Tesseract pushes into a cube's FILTER_PARAMS template (which happens for dynamic/relative date filters from the SQL API, e.g. CURRENT_TIMESTAMP-based ranges). The logically identical query with literal DATE bounds returns correct data.

Root cause: when rendering FILTER_PARAMS, the active time-shift state is discarded, so inside the shifted (prior-year) CTE the FILTER_PARAMS predicate filters the raw, un-shifted time column against the current-period bounds, while the rest of the CTE correctly shifts the column. The two predicates are mutually exclusive ⇒ the prior-year CTE returns 0 rows ⇒ the INNER JOIN in the YoY stage collapses ⇒ empty result.

To Reproduce

Minimal model (cube/):

cubes:
  - name: orders
    # A hand-written FILTER_PARAMS pushdown on the time dimension is the trigger.
    sql: >
      SELECT * FROM public.orders AS base
        WHERE {FILTER_PARAMS.orders.created_at.filter('base.created_at')}
    dimensions:
      - name: created_at
        sql: created_at
        type: time
    measures:
      - name: count
        type: count
      - name: count_prior_year
        type: number
        sql: "{count}"
        multi_stage: true
        time_shift:
          - time_dimension: created_at
            interval: 1 year
            type: prior
      - name: count_yoy_growth
        type: number
        sql: "ROUND(CAST(({count} - {count_prior_year}) AS DOUBLE) / NULLIF(CAST({count_prior_year} AS DOUBLE), 0), 5)"
        multi_stage: true

views:
  - name: orders_view
    cubes:
      - join_path: orders
        includes: [created_at, count, count_yoy_growth]

Query the SQL API. (A) returns 0 rows, (B) returns data — they are logically identical:

-- (A) dynamic/relative date filter  -> EMPTY
SELECT DATE_TRUNC('day', created_at) AS day,
       MEASURE(count_yoy_growth) AS yoy
FROM orders_view
WHERE DATE_TRUNC('day', created_at) >= DATE_TRUNC('day', CURRENT_TIMESTAMP - INTERVAL '7 day')
  AND DATE_TRUNC('day', created_at) <  DATE_TRUNC('day', CURRENT_TIMESTAMP)
GROUP BY 1;

-- (B) literal date filter          -> CORRECT (non-empty)
SELECT DATE_TRUNC('day', created_at) AS day,
       MEASURE(count_yoy_growth) AS yoy
FROM orders_view
WHERE DATE_TRUNC('day', created_at) >= DATE '2026-06-01'
  AND DATE_TRUNC('day', created_at) <  DATE '2026-06-08'
GROUP BY 1;

Expected behavior

Both queries return the same (non-empty) YoY results. The time_shift prior-year CTE must select prior-year source rows regardless of whether the time filter is pushed into FILTER_PARAMS.

Actual behavior — generated SQL

For the dynamic filter (A), the resolved range is pushed into the cube's FILTER_PARAMS template and applied to the raw base.created_at inside every CTE, including the time-shifted prior-year CTE — without being shifted:

-- prior-year CTE (time_shift: 1 year prior), dynamic filter:
... FROM (
      SELECT * FROM public.orders AS base
      WHERE (base.created_at >= ? AND base.created_at <= ?)        -- FILTER_PARAMS: CURRENT-year bounds, raw column
    ) AS orders
WHERE ((CAST(orders.created_at AS TIMESTAMP) + interval '1' year) >= ?
   AND (CAST(orders.created_at AS TIMESTAMP) + interval '1' year) <= ?)  -- outer: shifts the column -> needs PRIOR-year rows
GROUP BY 1

base.created_at ∈ [current-year] (inner) and base.created_at ∈ [prior-year] (outer, since the column is shifted +1y vs. a current-year range) are mutually exclusive → 0 rows → INNER JOIN with the current-year leg yields no rows.

For the literal filter (B), FILTER_PARAMS resolves to WHERE (1 = 1) (the predicate is treated as a member-expression filter, not pushed into FILTER_PARAMS), and the correctly-shifted outer WHERE does the filtering — so it works. That difference is the only reason (B) succeeds.

Root cause (code)

rust/cubesqlplanner/cubesqlplanner/src/planner/sql_call.rs, eval_filter_group() builds the filter-params render context with a fresh SqlNodesFactory::new() that has no time-shift state:

let context = VisitorContext::new_for_filter_params(
    query_tools.clone(),
    &SqlNodesFactory::new(),   // time-shift state dropped here
    filter_params_columns,
);
render_filter_item(&context, &subtree, templates)

Everywhere else in a shifted leaf CTE the factory is built via PushDownBuilderContext::make_sql_nodes_factory() (physical_plan_builder/context.rs), which calls set_time_shifts(...) so TimeShiftSqlNode wraps the time-dimension column with + interval. FILTER_PARAMS rendering bypasses that, so the pushed-down predicate is shift-blind.

Possible fixes:

• Minimal: thread the active TimeShiftState into the filter-params render and, when a FILTER_PARAMS member has an active shift, emit always_true() (matches the working literal path). Loses partition pruning on the prior-year sub-scan only.
• Complete (keeps pruning): inverse-shift the bound values by the shift interval (SqlInterval::inverse() already exists) so the prior-year CTE pushes base.created_at BETWEEN <prior-year bounds>.
• Likely related to the existing TODO in planner/filter/compiler.rs about classifying time-dimension filters separately.

Note: there appears to be no planner test combining FILTER_PARAMS with time_shift, which is why this is uncaught.

Version

• Cube v1.6.36 (Tesseract SQL planner / multi_stage + time_shift via the Postgres SQL API). Code paths above are still present on master as of this writing.

Additional context

The bug only manifests when (a) the cube's sql contains a FILTER_PARAMS.<cube>.<timeDim>.filter(...) pushdown and (b) a multi_stage time_shift measure is selected and (c) the incoming time filter is one that gets pushed into FILTER_PARAMS (dynamic/relative date expressions). Any one of the three absent → correct results.

[igorlukanin]

Reproduced on v1.6.36 (reported) and current latest v1.6.57, with the Tesseract SQL planner.