cube-js / cube-js/cube

Tesseract planner: measure-side fact copy of a multiplied-measure join-back renders FILTER_PARAMS as 1 = 1 (unbounded scan)

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

Description

**Describe the bug**

When a calculated measure (`type: number`) is queried together with a dimension from a `one_to_many`-joined cube, the Tesseract SQL planner generates the expected full-key plan: a keys subquery joined back to a second copy of the fact source by primary key.

The keys-side copy of the fact source is rendered **with** the query's filters. The measure-side copy is rendered **with no filter context at all**: every `FILTER_PARAMS` template inside the cube's `sql:` falls back to `ALWAYS_TRUE` and is emitted as `1 = 1`.

Query results are still correct (the keys side constrains the output), but the database has to build the join against the **entire unfiltered fact table** — every tenant, all time. The legacy planner (`CUBEJS_TESSERACT_SQL_PLANNER=false`) renders the same predicates into **both** copies for the identical schema and query.

**To Reproduce**

`model/cubes/orders.yml`:

```yaml
cubes:
- name: orders
# Multi-tenant fact table. FILTER_PARAMS pushes the query's tenant and
# date predicates into the source scan.
sql: >-
SELECT * FROM orders
WHERE {FILTER_PARAMS.orders.tenant_id.filter('tenant_id')}
AND {FILTER_PARAMS.orders.created_at.filter('created_at')}

joins:
- name: order_tags
sql: "{CUBE}.id = {order_tags}.order_id"
relationship: one_to_many

dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: tenant_id
sql: tenant_id
type: string
- name: created_at
sql: created_at
type: time

measures:
- name: count
type: count
- name: buyers
sql: user_id
type: count_distinct
- name: orders_per_buyer
sql: "{count} / nullif({buyers}, 0)"
type: number

- name: order_tags
sql: SELECT * FROM order_tags

dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: tag
sql: tag
type: string
```

Query:

```json
{
"measures": ["orders.orders_per_buyer"],
"dimensions": ["order_tags.tag"],
"timeDimensions": [
{ "dimension": "orders.created_at", "dateRange": ["2026-07-29", "2026-08-27"] }
],
"filters": [
{ "member": "orders.tenant_id", "operator": "equals", "values": ["t1"] }
]
}
```

Generated SQL with the Tesseract planner (ClickHouse dialect, abridged only for whitespace):

```sql
WITH cte_0 AS (
SELECT `keys`.`order_tags__tag`,
count(`orders_key_orders`.id) / nullif(COUNT(DISTINCT `orders_key_orders`.user_id), 0)
FROM (
SELECT DISTINCT `orders_key_order_tags`.tag `order_tags__tag`, `orders_key_orders`.id `orders__id`
FROM (SELECT * FROM orders
WHERE (tenant_id = ?)
AND (created_at >= parseDateTimeBestEffort(?) AND created_at <= parseDateTimeBestEffort(?))
) AS `orders_key_orders` -- keys side: bounded
LEFT JOIN order_tags AS `orders_key_order_tags` ON ...
WHERE (`orders_key_orders`.created_at >= ... ) AND (`orders_key_orders`.tenant_id = ?)
) AS `keys`
LEFT JOIN (SELECT * FROM orders
WHERE 1 = 1
AND 1 = 1 -- measure side: both FILTER_PARAMS gone
) AS `orders_key_orders`
ON `keys`.`orders__id` = `orders_key_orders`.id
GROUP BY 1
)
SELECT ... FROM cte_0
```

Same schema and query with `CUBEJS_TESSERACT_SQL_PLANNER=false` — the measure-side copy keeps the predicates:

```sql
LEFT JOIN (SELECT * FROM orders
WHERE (tenant_id = ?)
AND (created_at >= parseDateTimeBestEffort(?) AND created_at <= parseDateTimeBestEffort(?))
) AS `orders_key__orders`
ON `keys`.`orders__id` = `orders_key__orders`.id
```

**Expected behavior**

The measure-side copy of the fact source should carry the same pushed-down predicates as the keys-side copy. This is result-identical: the keys subquery already applies those predicates over the same fact columns, and the join-back is by primary key — every joinable row already satisfies them. Filtering the measure side can only shrink the hash build, never change the result.

**Impact**

On a large fact table the unfiltered hash build exceeds the database's memory
limit and the query fails.

**Where it appears to happen**

In `rust/cube/cubesqlplanner/cubesqlplanner/src/planner/planners/multiplied_measures_query_planner.rs`, `aggregate_subquery_plan` builds the measure-side source as a bare `pk_cube.into()` — no filter context — while `keys_subquery` is planned with the query's filters. The physical builder then renders the cube's SQL with an empty used-filter set, and `FILTER_PARAMS` evaluation falls back to `ALWAYS_TRUE`. Same fallback as #10606, reached via a different path (there segments break subtree extraction; here the source is planned filterless by construction).

A possible fix: when planning the bare-cube source of the aggregate subquery, thread through the subset of the query's filters whose members belong to the key cube (member filters and time-dimension ranges). Cumulative/rolling measures are planned outside this branch, so window-extension semantics should be unaffected. Happy to contribute a patch with regression tests if the approach is acceptable.

**Version**

- Repro compiled with `@cubejs-backend/schema-compiler` 1.7.24 (Tesseract planning via `@cubejs-backend/native` 1.6.65 — both versions exhibit it), ClickHouse dialect; the planning defect is dialect-independent.
- Workaround: `CUBEJS_TESSERACT_SQL_PLANNER=false` produces bounded copies (aware the legacy planner is scheduled for removal, hence this report).

Contributor guide

Open the contributing guide

Research direction

Start in rust/cube/cubesqlplanner/cubesqlplanner/src/planner/planners/multiplied_measures_query_planner.rs, focusing on aggregate_subquery_plan and comparing its bare pk_cube source with keys_subquery. Reproduce the ClickHouse query from the issue and inspect how the physical builder handles the measure-side filter context. Done means the measure-side source carries the applicable tenant and time predicates and regression coverage demonstrates bounded copies without changing results.

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
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.