cube-js / cube-js/cube

Tesseract: FILTER_PARAMS pushdown becomes 1 = 1 when a query includes a segment

Open
#10,606 2 comments 2 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

## Summary
With `CUBEJS_TESSERACT_SQL_PLANNER=true`, `FILTER_PARAMS` pushdown works until the query includes a segment. As soon as a segment is present, the inner `FILTER_PARAMS` calls are rendered as `1 = 1`, so merchant/time filters stop pushing down into the base SQL and only remain on the outer query.

This looks distinct from older `FILTER_PARAMS` issues about duplicate outer filters: here the pushdown itself disappears specifically because a segment is present in the filter tree.

## Versions
Observed on:
- `v1.6.29`
- `v1.6.30`

## Minimal schema
```js
cube(`OrdersFp`, {
sql: `
SELECT *
FROM orders
WHERE ${FILTER_PARAMS.OrdersFp.createdAt.filter('created_at')}
AND ${FILTER_PARAMS.OrdersFp.merchantId.filter('merchant_id')}
`,

measures: {
count: {
type: `count`,
},
},

dimensions: {
id: {
sql: `id`,
type: `number`,
primaryKey: true,
},

merchantId: {
sql: `merchant_id`,
type: `string`,
},

status: {
sql: `status`,
type: `string`,
},

createdAt: {
sql: `created_at`,
type: `time`,
},
},

segments: {
completed: {
sql: `${CUBE}.status = 'completed'`,
},
},
});
```

Test table:
```sql
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
merchant_id TEXT NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMP NOT NULL
);
```

## Query without segment
```json
{
"measures": ["OrdersFp.count"],
"timeDimensions": [
{
"dimension": "OrdersFp.createdAt",
"dateRange": ["2024-01-01", "2024-01-31"]
}
],
"filters": [
{
"member": "OrdersFp.merchantId",
"operator": "equals",
"values": ["merchant-1"]
}
]
}
```

Generated SQL without segment:
```sql
SELECT count("orders_fp".id) "orders_fp__count"
FROM (
SELECT *
FROM orders
WHERE (created_at >= $1::timestamptz AND created_at <= $2::timestamptz)
AND (merchant_id = $3)
) AS "orders_fp"
WHERE ("orders_fp".created_at >= $4::timestamptz AND "orders_fp".created_at <= $5::timestamptz)
AND ("orders_fp".merchant_id = $6)
LIMIT 10000
```

This is the behavior I expect: the time and merchant filters are pushed into the base SQL.

## Query with segment
Same query, but adding this segment:
```json
{
"measures": ["OrdersFp.count"],
"timeDimensions": [
{
"dimension": "OrdersFp.createdAt",
"dateRange": ["2024-01-01", "2024-01-31"]
}
],
"segments": ["OrdersFp.completed"],
"filters": [
{
"member": "OrdersFp.merchantId",
"operator": "equals",
"values": ["merchant-1"]
}
]
}
```

Generated SQL with segment:
```sql
SELECT count("orders_fp".id) "orders_fp__count"
FROM (
SELECT *
FROM orders
WHERE 1 = 1
AND 1 = 1
) AS "orders_fp"
WHERE ("orders_fp".created_at >= $1::timestamptz AND "orders_fp".created_at <= $2::timestamptz)
AND ("orders_fp".merchant_id = $3)
AND ("orders_fp".status = 'completed')
LIMIT 10000
```

## Expected behavior
Adding a segment should not disable `FILTER_PARAMS` pushdown for unrelated filters. I would expect the inner SQL to still contain the pushed-down time and merchant filters, with the segment applied according to normal segment semantics.

## Impact
This causes large performance regressions for cubes/views that rely on `FILTER_PARAMS` to push tenant/time filters into CTEs or base SQL. In our case it caused merchant/time filters to stop pushing into inner CTEs whenever a segment was present, which dramatically increased scanned data and query latency.

## Suspected root cause
I dug through the Rust planner and found what looks like the failing path:

- `rust/cubesqlplanner/cubesqlplanner/src/planner/query_properties.rs`
- `all_filters()` chains time dimension filters, dimension filters, and segments into one top-level filter object.
- `rust/cubesqlplanner/cubesqlplanner/src/plan/filter.rs`
- `extract_filter_members()` returns `None` for `FilterItem::Segment`.
- `find_subtree_for_members()` calls `extract_filter_members()` on the subtree; if any child is a segment, the whole subtree lookup fails.
- `rust/cubesqlplanner/cubesqlplanner/src/planner/sql_evaluator/sql_call.rs`
- `eval_filter_group()` falls back to `templates.always_true()` when subtree extraction fails.

That combination seems to turn `FILTER_PARAMS` into `1 = 1` whenever the query's filter tree contains a segment.

## Related issue
This may be adjacent to, but seems different from, `#640`, which is about duplicate/outer filtering around `FILTER_PARAMS`. Here the key problem is that segment presence causes pushdown to disappear entirely.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.