jayzhan211 opened a new issue, #26704:
URL: https://github.com/apache/datafusion/issues/26704
### Describe the bug
A subquery (`EXISTS`, `IN` or a scalar subquery) inside an aggregate, that
is in a `GROUP BY` key, an aggregate argument or an aggregate `FILTER`, fails
at physical planning with `This feature is not implemented: Physical plan does
not support logical expression ...`. The same subquery in the `SELECT` list or
in `WHERE` works.
### To Reproduce
```sql
CREATE TABLE t1 (a INT, b VARCHAR, x INT);
INSERT INTO t1 VALUES (1, 'p', 10), (2, 'p', 20), (3, 'q', 30), (NULL, 'q',
40), (2, 'q', 50);
CREATE TABLE t2 (a INT, y INT);
INSERT INTO t2 VALUES (2, 7), (2, 9), (3, 5), (NULL, 1);
-- Query 1
SELECT count(*) FILTER (WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a))
AS n FROM t1;
-- Query 2
SELECT CASE WHEN EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a) THEN 'yes' ELSE
'no' END AS has_t2, count(*) AS n
FROM t1 GROUP BY 1 ORDER BY 1;
-- Query 3
SELECT sum(CASE WHEN t1.a IN (SELECT a FROM t2) THEN t1.x ELSE 0 END) AS s
FROM t1;
-- Query 4
SELECT t1.b, sum((SELECT max(t2.y) FROM t2 WHERE t2.a = t1.a)) AS s FROM t1
GROUP BY t1.b ORDER BY t1.b;
-- Query 5
SELECT count(*) FILTER (WHERE t1.a NOT IN (SELECT a FROM t2)) AS n FROM t1;
```
### Expected behavior
| Query | Subquery is in | DataFusion returns | Expected |
|---|---|---|---|
| 1 | aggregate `FILTER` | `Physical plan does not support logical
expression Exists(...)` | `3` |
| 2 | `GROUP BY` key | `Physical plan does not support logical expression
Exists(...)` | `no, 2` and `yes, 3` |
| 3 | aggregate argument | `Physical plan does not support logical
expression InSubquery(...)` | `100` |
| 4 | aggregate argument | `Physical plan does not support logical
expression ScalarSubquery(<subquery>)` | `p, 9` and `q, 14` |
| 5 | aggregate `FILTER` | `Physical plan does not support logical
expression InSubquery(...)` | `0` (`t2.a` has a `NULL`, so `NOT IN` is never
true) |
DuckDB returns the expected results.
### Additional context
- The cause: `DecorrelatePredicateSubquery` and `ScalarSubqueryToJoin`
rewrite subqueries only in `Filter` and `Projection` nodes
(`decorrelate_predicate_subquery.rs` matches `LogicalPlan::Projection` and
`LogicalPlan::Filter`, `scalar_subquery_to_join.rs` the same two). The
expressions of an `Aggregate` are never visited, so their subqueries reach the
physical planner unchanged.
- This shows up in the dbolympics benchmark (#26661): 13 of its 409 queries
put a subquery in an aggregate (q057, q084, q085, q091, q135, q136, q156, q178,
q182, q312, q315, q370, q409).
- A fix direction that removes the cause: compute each aggregate expression
that contains a subquery in a `Projection` below the `Aggregate`, keeping the
output names with aliases. These expressions are evaluated once per input row
anyway, and the existing rules then decorrelate the subquery in that
`Projection`. I tried this in a prototype (one new optimizer rule placed before
`DecorrelatePredicateSubquery`):
- Queries 1-5 return the expected results, as do harder cases (several
subqueries in one aggregate, `count(DISTINCT ...)`, an aggregate `ORDER BY`, an
aggregate inside a correlated subquery, `NOT EXISTS`, empty input).
- 10 of the 13 dbolympics queries plan. The other 3 then fail on a scalar
subquery with `ORDER BY ... LIMIT 1`, which is a separate gap.
- No other dbolympics query starts or stops failing, and all sqllogictest
files and optimizer tests pass (the only expected-output change is the new
rule's line in `EXPLAIN VERBOSE`).
- Like `ScalarSubqueryToJoin`, it leaves an uncorrelated scalar subquery
where it is, since `ScalarSubqueryExec` already runs it.
- Not covered by that approach: a subquery in a `ROLLUP` / `GROUPING SETS`
key. It fails differently on main today (`Schema error: No field named t1.a`).
- Related: #5492 (general decorrelation framework).
### Willingness to contribute
I'm not planning to fix this myself, so anyone is welcome to pick it up.
Feel free to ping me with questions about the cause or when you'd like a review.
--
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]
