cube-js / cube-js/cube

SQL API: TRIM(view_dimension) expands to nearly the full view and times out

Open
#10,712 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 10h
Merged PRs (30d)
203

Description

## Summary
Using `TRIM(...)` on a view dimension in the SQL API appears to trigger pathological member-expression expansion.

Semantically similar expressions like `LOWER(...)`, `LTRIM(...)`, `RTRIM(...)`, `COALESCE(...)`, and `SUBSTRING(...)` stay narrow and complete normally, but `TRIM(...)` causes the normalized query to expand to nearly the full view and eventually time out.

## Version
Observed on Cube `v1.6.29`.

## Repro matrix

### Works
- `view.dimension`
- `LOWER(view.dimension)`
- `UPPER(view.dimension)`
- `COALESCE(view.dimension, '')`
- `SUBSTRING(view.dimension, 1, 12)`
- `LTRIM(view.dimension)`
- `RTRIM(view.dimension)`
- `LTRIM(LOWER(view.dimension))`
- `LTRIM(RTRIM(view.dimension))`

### Fails pathologically
- `TRIM(view.dimension)`
- `TRIM(LOWER(view.dimension))`

For the working shapes, the rewritten query stayed narrow (`2 measures + 1 dimension` in my case).

For the failing shapes, the rewritten query expanded to almost the full view (`46 measures + 50 dimensions` in my case) and later timed out.

## Representative query shape

```sql
SELECT
TRIM(sales_view.item_name) AS item_name,
MEASURE(sales_view.sales_gross_amount) AS gross_sales,
MEASURE(sales_view.items_sold_count) AS quantity_sold
FROM sales_view
GROUP BY 1
ORDER BY gross_sales DESC
LIMIT 10
```

The equivalent workaround completes normally:

```sql
SELECT
LTRIM(RTRIM(sales_view.item_name)) AS item_name,
MEASURE(sales_view.sales_gross_amount) AS gross_sales,
MEASURE(sales_view.items_sold_count) AS quantity_sold
FROM sales_view
GROUP BY 1
ORDER BY gross_sales DESC
LIMIT 10
```

## Expected behavior
`TRIM(view.dimension)` should behave like other simple scalar wrappers on a view dimension and only reference the actually needed members.

## Actual behavior
`TRIM(view.dimension)` causes the SQL API rewrite/planning pipeline to widen the query dramatically and eventually time out.

## Why this looks like a Cube bug
This does not look like a generic "function on a view dimension is expensive" issue. Only `TRIM(...)` reproduces the expansion. Other wrappers on the same dimension do not.

Looking through the source, `TRIM` appears to be represented specially in the Rust SQL layer, while recent SQL API fixes have focused on generic member-expression and view/dimension-only-expression handling. My guess is that `TRIM(...)` is falling onto a special expression path where dependency capture for view member expressions is incomplete, and the schema compiler later widens the dependency set during `fullKeyQueryAggregate` handling.

## Related work
- #9083 Handle member expressions in keyDimensions
- #9335 Handle measures with dimension-only member expressions
- #9583 Push down DATE_TRUNC expressions as member expressions with granularity
- #9603 Fix rolling window queries with expressions from SQL API
- #9673 member expressions fixes
- #10571 Support PatchMeasure for view measures

If helpful, I can try to turn this into a more minimal public repro.

Contributor guide

Open the contributing guide

Research direction

Start in the Rust SQL layer and trace how TRIM(view.dimension) is represented through the SQL API rewrite/planning pipeline, especially dependency capture and fullKeyQueryAggregate handling. Compare it with LTRIM/RTRIM and the related work in issues #9083, #9335, #9583, #9603, and #9673. Done means TRIM stays limited to the requested members and no longer expands or times out.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
api, backend
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.