Multi-stage time_shift measures produce empty results when combined with dimensions from joined cubes

[ultraglorious]

Summary

When querying a time_shift multi-stage measure alongside a regular measure, and the query also includes dimensions from a joined cube (e.g. a date dimension table), the Tesseract planner incorrectly includes those joined dimensions in the INNER JOIN predicate between the main query and the shifted CTE. This causes zero rows when the joined dimension values differ between the current and shifted dates.

Environment

• Cube Cloud, version 1.7.1 (Tesseract planner is default)
• Data source: Redshift

Reproduction

Schema:

cubes:
  - name: dim_date
    sql_table: analytics.dim_date
    public: false
    dimensions:
      - name: date
        sql: date
        type: time
        primary_key: true
      - name: day_name
        sql: day_name
        type: string

  - name: daily_facts
    sql_table: analytics.daily_facts
    joins:
      - name: dim_date
        relationship: many_to_one
        sql: "{CUBE}.date = {dim_date}.date"
    dimensions:
      - name: date
        sql: date
        type: time
    measures:
      - name: total
        sql: count
        type: sum
      - name: total_prior_day
        multi_stage: true
        sql: "{total}"
        type: number
        time_shift:
          - interval: 1 day
            type: prior

Query: Select total, total_prior_day, and dim_date.day_name, grouped by date (day granularity), with a date filter.

Expected: Rows with total for the current day, total_prior_day for the previous day, and day_name reflecting the current day.

Actual: Zero rows returned.

Root cause

The generated SQL shows the problem clearly. The final join between the main query (q_0) and the shifted CTE (q_1) includes ALL dimensions — including day_name from dim_date — as join predicates:

INNER JOIN (...) AS q_1 ON
  q_0."day_name" = q_1."day_name"         -- THIS BREAKS IT
  AND q_0."date_day" = q_1."date_day"

In the shifted CTE (cte_0), the dim_date join uses the unshifted fact row date:

LEFT JOIN analytics.dim_date AS "dim_date"
  ON "daily_facts".date = "dim_date".date

So for a query on July 10:

• Main query joins dim_date on July 10 → day_name = "Thursday"
• Shifted CTE reads July 9's fact row, joins dim_date on the raw date (July 9) → day_name = "Wednesday"
• Final INNER JOIN: "Thursday" = "Wednesday" → no match → empty result

The date_day column matches correctly (both align to July 10 after the shift), but the dim_date dimensions reflect the source row's raw date, not the shifted date.

Observations

• Querying total + total_prior_day without dim_date dimensions works correctly
• Querying total + total_prior_day with dim_date dimensions that happen to be the same across both dates works (e.g. month_name when both dates are in July) — confirming the join predicate is the issue
• The same behavior applies to time_shift: [{interval: 1 week, type: prior}]

Expected fix

Dimensions from joined cubes that are deterministically derived from the time dimension should either:

1. Not be included in the inter-CTE join predicate (they're functionally dependent on date_day which is already in the join), or
2. The shifted CTE should join dim_date on the shifted date rather than the raw fact date

[igorlukanin]

Reproduced on latest v1.7.4. The symptom shows differently now — instead of zero rows, each date splits into two rows with total and total_prior_day on separate rows and an inconsistent day_name — but it's the same underlying issue: the joined-cube dimension is used to align the shifted and current rows, and it's derived from the unshifted date.