SQL API: TRIM(view_dimension) expands to nearly the full view and times out
- 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
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