cube-js / cube-js/cube

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

Open
#11,240 1 comment 0 reactions 0 assignees View on GitHub
data modeling:multi-stage data modeling:tesseract
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## 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:**

```yaml
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:

```sql
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:

```sql
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

Contributor guide

Open the contributing guide

Research direction

Start with the Tesseract planner and reproduce the query using the provided dim_date and daily_facts schema, then inspect the generated SQL for the final join between q_0 and q_1. The fix is complete when time-shift queries with joined-cube dimensions return the expected current and prior-period rows instead of an empty result.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
backend-api-design, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.