apache / apache/datafusion

Avoid inlining non deterministic CTE

Open
#10,337 5 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

Currently Datafusion will inline all CTE, a non-deterministic expression can be executed multiple times producing different results

### To Reproduce

Consider the following query which uses the `aggregate_test_100` data from datafusion-examples. Here, column c11 is a Float64
```
WITH cte as (
SELECT sum(c4 * c11) as total
FROM aggregate_test_100
GROUP BY c1)
SELECT total
FROM cte
WHERE total = (select max(total) from cte)
```

The optimized plan generated will inline the CTE and thus execute it twice
```
Projection: cte.total
Inner Join: cte.total = __scalar_sq_1.MAX(cte.total)
SubqueryAlias: cte
Projection: SUM(aggregate_test_100.c4 * aggregate_test_100.c11) AS total
Aggregate: groupBy=[[aggregate_test_100.c1]], aggr=[[SUM(CAST(aggregate_test_100.c4 AS Float64) * aggregate_test_100.c11)]]
TableScan: aggregate_test_100 projection=[c1, c4, c11]
SubqueryAlias: __scalar_sq_1
Aggregate: groupBy=[[]], aggr=[[MAX(cte.total)]]
SubqueryAlias: cte
Projection: SUM(aggregate_test_100.c4 * aggregate_test_100.c11) AS total
Aggregate: groupBy=[[aggregate_test_100.c1]], aggr=[[SUM(CAST(aggregate_test_100.c4 AS Float64) * aggregate_test_100.c11)]]
TableScan: aggregate_test_100 projection=[c1, c4, c11]
```

### Expected behavior

Since summation here is dependent on ordering, I believe it is incorrect to inline the CTE here and execute it more than once.

### Additional context

Related issue, which talks about possible advantages on not inlining CTE in some cases: https://github.com/apache/datafusion/issues/8777

Contributor guide

Open the contributing guide

Research direction

Start by running the query against the aggregate_test_100 data from datafusion-examples and inspect the optimized plan shown in the issue. Trace the CTE inlining and nondeterministic expression handling, then verify that the CTE is not evaluated multiple times and that the query produces stable results.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Needs clarification
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.