Tesseract: FILTER_PARAMS pushdown becomes 1 = 1 when a query includes a segment
- 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
Assessment
This issue has not been assessed yet.