cube-js / cube-js/cube

Cube Store silently returns ~2x-inflated results for multi_stage measures whose stage references two measures, on longer date ranges

Open
#11,545 5 comments 0 reactions 0 assignees View on GitHub
cube store
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## Describe the bug

With Tesseract pre-aggregation serving enabled (`CUBEJS_TESSERACT_SQL_PLANNER=true`, `CUBEJS_TESSERACT_PRE_AGGREGATIONS=true`), a `multi_stage` measure whose stage SQL references **two** measures returns **silently inflated (~2x) values** for some months when the query's date range is long enough. No error — plausible-looking wrong numbers, served fast from Cube Store.

Minimal model shape (MS SQL source; a per-order "ticket count" allocated fractionally):

```yaml
# fact cube
- name: ticket_fraction # line's share of its order's positive sales; sums to <= 1 per order
sql: percent_of_total_positive_ticket_sales
type: sum

# order-dimension cube joined many_to_one from the fact
- name: order_slice_gated
multi_stage: true
type: number
sql: "CASE WHEN {fact.sale} > 0 THEN {fact.ticket_fraction} END" # <-- TWO measures
add_group_by: [id]

- name: tickets
multi_stage: true
type: sum
sql: "{order_slice_gated}"
```

One rollup on the fact covering `sale`, `ticket_fraction`, the order key (`orders.id`), the query dimensions, and `time_dimension` on a conformed calendar cube, granularity day.

## Symptom

Same tenant, same month cell, month granularity — the answer depends on the surrounding date range:

| query range | value for July |
|---|---|
| Jul alone | 671.99 ✅ |
| Jun–Jul | 671.99 ✅ |
| Mar–Jul | 671.99 ✅ |
| **Apr–Jul** | **1341.99** ❌ |
| **Feb–Jul** | **1341.99** ❌ |

- The tenant had **692 orders with positive sales in July** — `tickets` is a sum of per-order fractions each ≤ 1, so 692 is a hard ceiling. 1341.99 is arithmetically impossible under the model's semantics.
- WHICH months inflate depends on the exact range: April is wrong in a 6-month query but right in 4- and 5-month queries; May is the reverse. Stable across repeated queries and rebuilt rollup tables for a given shape.
- A sibling stage in the same query referencing **one** measure plus a constant (`CASE WHEN {order_slice_sale} > 0 THEN 1 END`, a distinct-order count) stays **correct** in the same corrupted response — a ratio of the two exceeded 100% (an email-collection *rate* of 1.558) even though its numerator counts a strict subset of its denominator's orders.
- A pure integer variant (gate + order-constant flag) doubles **exactly**: 198 vs 99 distinct orders in one month cell — so the duplication looks wholesale per month/branch, not per boundary-spanning order.

## Evidence the executed plan diverges from the reported plan

`/v1/sql` for the affected query prints CTEs where the gated stage LEFT JOINs two `GROUP BY (id, month)` subqueries on `(id, month)` over a `SELECT DISTINCT` key set — under that SQL the gated sum cannot exceed the distinct key count, yet the returned value does. The rollup appears twice in the plan (`__usage_0` / `__usage_1`, one scan per referenced measure), which is presumably the join that gets misassembled.

This looks like the silent sibling of the plan-serialization crash we also hit with the same model (5+ measures involving multi_stage members → `Error decoding expr as protobuf: ... recursion limit reached`): same pipeline, corrupting instead of rejecting.

## Versions

Reproduces identically (same wrong values, digit for digit) on:

- cube **v1.7.4** and **v1.7.19** (2026-08-12), stock Docker images
- cubestore **v1.7.4** and **v1.7.19**, single-node and router+2-workers
- Fresh rollup builds each time; MS SQL Server source

## Workaround

Removing the gate's second measure from the rollup definition (forcing those stages to the source database) returns correct values for every range shape — verified exactly against source SQL.

## Expected behavior

Either correct results from the rollup-served plan, or fallback to the source database when the plan cannot be served faithfully — silent wrong data is the worst of the three behaviors this pipeline currently exhibits (crash / wrong join-path error / silent inflation).

Contributor guide

Open the contributing guide

Research direction

Start with the /v1/sql output for the affected query and compare the multi_stage plan's __usage_0 and __usage_1 rollup scans and LEFT JOINs against the reported values. Reproduce with the stated Tesseract flags, date ranges, and two-measure stage, then verify that rollup-served results match source SQL or fall back to the source database instead of silently inflating values.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.