cube-js / cube-js/cube

FILTER_PARAMS lambda should receive the filter operator (or use sentinel bounds for one-sided filters)

Open
#10,655 0 comments 0 reactions 0 assignees View on GitHub
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

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.