FILTER_PARAMS lambda should receive the filter operator (or use sentinel bounds for one-sided filters)
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Problem
When using `FILTER_PARAMS` with a lambda function to transform filter bounds before pushing them into raw SQL, the lambda always receives exactly two positional arguments `(start, end)` regardless of the original filter operator. This makes it impossible to implement correct asymmetric transformations for one-sided or negated filters.
### Use case
We have a Databricks table clustered on a UTC timestamp column (`reporting.internalReportingTimestamp`). Our Cube dimensions expose a **local-timezone** version of this column:
```yaml
dimensions:
- name: local_reporting_timestamp
sql: "from_utc_timestamp({CUBE}.reporting.internalReportingTimestamp, {Location.timezone})"
type: time
```
Since `from_utc_timestamp()` is a computed expression, Databricks cannot push filters on it down into the table scan for file pruning. To work around this, we want to use a `FILTER_PARAMS` lambda to push a **±24-hour buffered filter** on the raw UTC column:
```yaml
sql: |
SELECT * FROM orders o
JOIN locations loc ON ...
WHERE
{FILTER_PARAMS.Orders.local_reporting_timestamp.filter(
lambda start, end: f"o.reporting.internalReportingTimestamp >= ({start} - INTERVAL 24 HOURS) AND o.reporting.internalReportingTimestamp <= ({end} + INTERVAL 24 HOURS)"
)}
```
This works correctly for `inDateRange` — both bounds are meaningful and buffering them is safe.
### Where it breaks
For **one-sided filters**, the lambda still receives two arguments, but one of them is a synthetic bound that the lambda cannot distinguish from a real value:
| Filter | Operator | Lambda receives | Problem |
|--------|----------|----------------|---------|
| `inDateRange [A, B]` | `inDateRange` | `(A, B)` | ✅ Works correctly |
| `afterDate A` | `afterDate` | `(A, )` | ❌ Lambda generates `col <= ( + 24h)`, adding an unwanted upper bound that excludes future data |
| `beforeDate B` | `beforeDate` | `(, B)` | ❌ Lambda generates `col >= ( - 24h)`, adding an unwanted lower bound |
| `notInDateRange [A, B]` | `notInDateRange` | `(A, B)` | ❌ Lambda generates a range restriction `col >= (A-24h) AND col <= (B+24h)`, which is the **opposite** of the intended behavior |
The core issue is that **the lambda has no access to the filter operator**, so it cannot adapt its logic for different filter types.
## Proposed solutions (either would work)
### Option A: Pass the operator to the lambda (preferred)
Add the operator as a third argument to the lambda:
```yaml
{FILTER_PARAMS.Orders.local_reporting_timestamp.filter(
lambda start, end, operator: ...
)}
```
This would let users write correct logic for all cases:
```python
lambda start, end, operator:
f"col >= ({start} - INTERVAL 24 HOURS) AND col <= ({end} + INTERVAL 24 HOURS)"
if operator in ('inDateRange', 'dateRange')
else f"col >= ({start} - INTERVAL 24 HOURS)"
if operator in ('afterDate', 'afterOrOnDate')
else f"col <= ({end} + INTERVAL 24 HOURS)"
if operator in ('beforeDate', 'beforeOrOnDate')
else "1=1"
```
### Option B: Use sentinel bounds for one-sided filters
When the filter is one-sided, pass extreme sentinel values instead of synthetic dates:
| Operator | `start` | `end` |
|----------|---------|-------|
| `afterDate A` | `A` | `'9999-12-31T23:59:59Z'` |
| `beforeDate B` | `'0001-01-01T00:00:00Z'` | `B` |
This way, buffering the sentinel bound (`9999-12-31 + 24h` or `0001-01-01 - 24h`) is harmless — it still evaluates to an effectively unbounded range on that side.
This approach requires no API change and works with existing lambda signatures, though it's less explicit than Option A.
## Current workaround
We implemented a `queryRewrite` hook in `cube.js` that:
1. Detects `local_reporting_timestamp` filters
2. Inspects the filter operator (available in the query object)
3. Injects a buffered filter on the raw UTC dimension with operator-appropriate logic
This works but requires maintaining a mapping of every cube/view's local timestamp → UTC dimension, and duplicating logic that would be cleaner as a `FILTER_PARAMS` lambda.
## Environment
- Cube.js version: 1.3.11
- Database: Databricks (SQL)
Contributor guide
Assessment
This issue has not been assessed yet.