Tesseract planner: measure-side fact copy of a multiplied-measure join-back renders FILTER_PARAMS as 1 = 1 (unbounded scan)
- 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
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