Cube Store silently returns ~2x-inflated results for multi_stage measures whose stage references two measures, on longer date ranges
- 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
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