Dandandan opened a new issue, #22391:
URL: https://github.com/apache/datafusion/issues/22391
### Describe the bug
A query of the shape
```sql
WITH s0 AS (SELECT ... FROM t LEFT JOIN ...),
s1 AS (SELECT *, <depth-K nested CASE over s0.col> AS d1 FROM s0),
s2 AS (SELECT *, <depth-K nested CASE over s0.col> AS d2 FROM s1),
...
sN AS (SELECT *, <depth-K nested CASE over s0.col> AS dN FROM s_{N-1})
SELECT * FROM sN
```
causes planning to consume **gigabytes of RAM and tens of seconds of wall
time**, growing far faster than linear in `N` (chain length) and `K` (CASE
depth). At sufficiently large `N`/`K` planning OOMs the process.
### To Reproduce
A reproducible scaling sweep against `datafusion-cli`:
| N (chained CTEs) | K (CASE depth) | wall | peak RSS |
|---:|---:|---:|---:|
| 80 | 23 | ~4 s | ~630 MB |
| 120 | 45 | 52 s | 4.9 GB |
| 160 | 45 | 94 s | 8.6 GB |
| 200 | 45 | 147 s | 13.4 GB |
Above N=300 at K=45 the process exhausts a 32 GB machine.
A planning-only benchmark in `sql_planner_extended.rs`
(`physical_plan_chained_case_projection_hotspot`, N=80, K=23, wide-OR=30)
measures **623 ms** for `DataFrame::create_physical_plan` on the unfixed code.
### Expected behavior
Planning should be roughly linear in `N` × `K` and not allocate gigabytes of
throwaway expression trees.
### Additional context
Three independent sources of superlinear cost identified:
1. **`update_expr` rebuilds every parent on a Column-with-itself
substitution.** In the chain-collapse path each pass-through column is
substituted with a `Column` it already equals, but `update_expr` always returns
`Transformed::yes`, forcing `transform_up` to rebuild the enclosing
`CaseExpr`/`CaseBody`. For N levels × K-deep CASEs this is **O(N² · K²) fresh
`CaseExpr` allocations** — the dominant driver of memory growth (multi-GB).
2. **Pairwise projection unification in `ProjectionPushdown`.** The
recursive `try_swapping_with_projection` collapses one level per iteration,
building N-1 intermediate `ProjectionExec`s. Each runs `compute_properties`
(which projects equivalence properties through the projection mapping) — O(N ·
W) wasted work plus heap pressure from the intermediates.
3. **`merge_consecutive_projections` (logical) folds only one pair per rule
application.** The outer optimizer fixpoint loop has to re-run the rule N
times, each walking the full plan tree, to collapse an N-deep chain.
PR fixing all three: #22389
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
