mbutrovich commented on code in PR #21240:
URL: https://github.com/apache/datafusion/pull/21240#discussion_r3138074451
##########
datafusion/sqllogictest/test_files/subquery.slt:
##########
@@ -1669,6 +1725,197 @@ drop table employees;
statement count 0
drop table project_assignments;
+#############
+## Uncorrelated scalar subquery row-count semantics
+## A scalar subquery must return at most one row; returning more is an error.
+#############
+
+statement ok
+CREATE TABLE sq_values(v INT) AS VALUES (1), (2), (3);
+
+statement ok
+CREATE TABLE sq_main(x INT) AS VALUES (10), (20);
+
+statement ok
+CREATE TABLE sq_empty(v INT) AS VALUES (1) LIMIT 0;
+
+# Scalar subquery returning multiple rows in SELECT position → error
+query error DataFusion error: Execution error: Scalar subquery returned more
than one row
+SELECT (SELECT v FROM sq_values);
+
+# Scalar subquery returning multiple rows in WHERE position → error
+query error DataFusion error: Execution error: Scalar subquery returned more
than one row
+SELECT x FROM sq_main WHERE x > (SELECT v FROM sq_values);
+
+# Scalar subquery returning multiple rows as a function argument → error
+query error DataFusion error: Execution error: Scalar subquery returned more
than one row
+SELECT x + (SELECT v FROM sq_values) FROM sq_main;
+
+# Scalar subquery returning exactly one row → success
+query I
+SELECT (SELECT v FROM sq_values LIMIT 1);
+----
+1
+
+# Scalar subquery returning exactly one row in WHERE → success
+query I rowsort
+SELECT x FROM sq_main WHERE x > (SELECT v FROM sq_values LIMIT 1);
+----
+10
+20
+
+# Scalar subquery returning zero rows → NULL
+query I
+SELECT (SELECT v FROM sq_empty);
+----
+NULL
+
+# Scalar subquery returning zero rows in arithmetic → NULL propagation
+query I
+SELECT x + (SELECT v FROM sq_empty) FROM sq_main;
+----
+NULL
+NULL
+
+# Scalar subquery returning zero rows in WHERE comparison → no matching rows
+query I
+SELECT x FROM sq_main WHERE x > (SELECT v FROM sq_empty);
+----
+
+# Aggregated subquery always returns one row, even on empty input → success
+query I
+SELECT (SELECT count(*) FROM sq_empty);
+----
+0
+
+# Aggregated subquery on multi-row table → success (aggregation reduces to 1
row)
+query I
+SELECT (SELECT max(v) FROM sq_values);
+----
+3
+
+# Multiple scalar subqueries, one returns multiple rows → error
+query error DataFusion error: Execution error: Scalar subquery returned more
than one row
+SELECT (SELECT count(*) FROM sq_empty), (SELECT v FROM sq_values);
+
+#############
+## Uncorrelated scalar subqueries in various expression contexts
+#############
+
+# HAVING clause with uncorrelated scalar subquery
+query II rowsort
+SELECT x, count(*) AS cnt FROM sq_main GROUP BY x
+HAVING count(*) > (SELECT min(v) FROM sq_values);
+----
+
+# CASE WHEN with uncorrelated scalar subquery as condition
+query T rowsort
+SELECT CASE WHEN x > (SELECT min(v) FROM sq_values)
+ THEN 'big' ELSE 'small' END AS label
+FROM sq_main;
+----
+big
+big
+
+# ORDER BY with uncorrelated scalar subquery
+query I
+SELECT x FROM sq_main ORDER BY x + (SELECT max(v) FROM sq_values);
+----
+10
+20
+
+# Aggregate function argument containing uncorrelated scalar subquery
+query I
+SELECT sum(x + (SELECT max(v) FROM sq_values)) AS s FROM sq_main;
+----
+36
+
+# JOIN ON condition with uncorrelated scalar subquery
+query II rowsort
+SELECT l.x, r.x AS rx
+FROM sq_main AS l JOIN sq_main AS r
+ON l.x = r.x + (SELECT min(v) FROM sq_values);
+----
+
+# Nested uncorrelated-in-uncorrelated scalar subquery.
+query I
+SELECT (SELECT max(v) + (SELECT min(v) FROM sq_values) FROM sq_values);
+----
+4
+
+# Verify nested subqueries are not hoisted: the root ScalarSubqueryExec
+# should manage only the outer subquery (subqueries=1), not both.
+query TT
+EXPLAIN SELECT (SELECT max(v) + (SELECT min(v) FROM sq_values) FROM sq_values);
+----
+logical_plan
+01)Projection: (<subquery>)
+02)--Subquery:
+03)----Projection: max(sq_values.v) + (<subquery>)
+04)------Subquery:
+05)--------Aggregate: groupBy=[[]], aggr=[[min(sq_values.v)]]
+06)----------TableScan: sq_values projection=[v]
+07)------Aggregate: groupBy=[[]], aggr=[[max(sq_values.v)]]
+08)--------TableScan: sq_values projection=[v]
+09)--EmptyRelation: rows=1
+physical_plan
+01)ScalarSubqueryExec: subqueries=1
+02)--ProjectionExec: expr=[scalar_subquery(<pending>) as max(sq_values.v) +
min(sq_values.v)]
+03)----PlaceholderRowExec
+04)--ScalarSubqueryExec: subqueries=1
+05)----ProjectionExec: expr=[max(sq_values.v)@0 + scalar_subquery(<pending>)
as max(sq_values.v) + min(sq_values.v)]
+06)------AggregateExec: mode=Single, gby=[], aggr=[max(sq_values.v)]
+07)--------DataSourceExec: partitions=1, partition_sizes=[1]
+08)----AggregateExec: mode=Single, gby=[], aggr=[min(sq_values.v)]
+09)------DataSourceExec: partitions=1, partition_sizes=[1]
+
+# CTE as source inside uncorrelated scalar subquery
+query I
+SELECT (SELECT s FROM (WITH cte AS (SELECT max(v) AS s FROM sq_values) SELECT
s FROM cte));
+----
+3
+
+# Window function with uncorrelated scalar subquery
+query II rowsort
+SELECT x, sum(x + (SELECT max(v) FROM sq_values)) OVER () AS win_sum FROM
sq_main;
+----
+10 36
+20 36
+
+# Duplicate uncorrelated scalar subqueries only appear in the query plan once
+statement ok
+set datafusion.explain.logical_plan_only = false;
+
+query TT
+explain SELECT (SELECT max(v) FROM sq_values) + (SELECT max(v) FROM sq_values)
AS doubled;
+----
+logical_plan
+01)Projection: __common_expr_1 + __common_expr_1 AS doubled
+02)--Projection: (<subquery>) AS __common_expr_1
+03)----Subquery:
+04)------Aggregate: groupBy=[[]], aggr=[[max(sq_values.v)]]
+05)--------TableScan: sq_values projection=[v]
+06)----EmptyRelation: rows=1
+physical_plan
+01)ScalarSubqueryExec: subqueries=1
+02)--ProjectionExec: expr=[__common_expr_1@0 + __common_expr_1@0 as doubled]
+03)----ProjectionExec: expr=[scalar_subquery(<pending>) as __common_expr_1]
+04)------PlaceholderRowExec
+05)--AggregateExec: mode=Single, gby=[], aggr=[max(sq_values.v)]
+06)----DataSourceExec: partitions=1, partition_sizes=[1]
+
+statement ok
+RESET datafusion.explain.logical_plan_only;
+
Review Comment:
```suggestion
#############
## Additional edge cases inspired by DuckDB scalar subquery bugs and tests.
## References:
## DuckDB issue #13639: volatile functions in uncorrelated subqueries
## DuckDB issue #13469 / PR #13514: multi-row error semantics
## DuckDB issue #4113: uncorrelated scalar subquery wrong results
## DuckDB test:
test/sql/subquery/scalar/test_uncorrelated_scalar_subquery.test
## DuckDB test: test/sql/subquery/scalar/test_scalar_subquery.test
## DuckDB test: test/sql/subquery/scalar/test_scalar_subquery_cte.test
## DuckDB test: test/sql/order/test_limit.test
## DuckDB PR #8519: TopN optimization with scalar subquery in LIMIT/OFFSET
#############
# Volatile function in uncorrelated subquery: random() is evaluated once and
# the same value is used for every outer row. Both Postgres and DuckDB
(#13639)
# exhibit this behavior; it is correct per the SQL standard.
# We cannot assert the exact value, but we can verify all rows are identical.
query B
SELECT count(DISTINCT r) = 1 FROM (
SELECT (SELECT random()) AS r
FROM (VALUES (1), (2), (3)) AS t(x)
);
----
true
# Subquery as GROUP BY key.
# Ref: DuckDB test_uncorrelated_scalar_subquery.test, test #6
query II
SELECT (SELECT 42) AS k, MAX(x) FROM (VALUES (1), (2), (3)) AS t(x) GROUP BY
k;
----
42 3
# Subquery inside an aggregate function argument.
# Ref: DuckDB test_uncorrelated_scalar_subquery.test, test #7
query II
SELECT x, MAX((SELECT 42)) FROM (VALUES (1), (2), (3)) AS t(x) GROUP BY x
ORDER BY x;
----
1 42
2 42
3 42
# Doubly-nested constant subquery.
# Ref: DuckDB test_scalar_subquery.test
query I
SELECT (SELECT (SELECT 42));
----
42
# Triple-nested constant subquery.
query I
SELECT (SELECT (SELECT (SELECT 99)));
----
99
# Star expansion: single column is OK.
# Ref: DuckDB test_uncorrelated_scalar_subquery.test, tests #16-17
query I
SELECT (SELECT * FROM (VALUES (1)) AS t(x));
----
1
# Star expansion: two columns must error.
query error Too many columns
SELECT (SELECT * FROM (VALUES (1, 2)) AS t(x, y));
# Subquery in BETWEEN bounds.
query I
SELECT x FROM (VALUES (1), (2), (3), (4), (5)) AS t(x)
WHERE x BETWEEN (SELECT 2) AND (SELECT 4)
ORDER BY x;
----
2
3
4
# DISTINCT subquery returning exactly one distinct value (multi-row input).
query I
SELECT (SELECT DISTINCT 42 FROM (VALUES (1), (2), (3)) AS t(x));
----
42
# DISTINCT subquery returning multiple distinct values must error.
query error DataFusion error: Execution error: Scalar subquery returned more
than one row
SELECT (SELECT DISTINCT x FROM (VALUES (1), (2), (3)) AS t(x));
# NULL comparison semantics through subquery boundary.
# Ref: DuckDB test_scalar_subquery.test, NULL edge cases
query B
SELECT 1 = (SELECT NULL::INT);
----
NULL
query B
SELECT NULL::INT = (SELECT 1);
----
NULL
# CTE inside uncorrelated scalar subquery.
# Ref: DuckDB test_scalar_subquery_cte.test
query I
SELECT (WITH cte AS (SELECT 42 AS val) SELECT val FROM cte);
----
42
# Nested CTE inside scalar subquery.
# Ref: DuckDB test_scalar_subquery_cte.test, nested CTE test
query I
SELECT (WITH cte1 AS (WITH cte2 AS (SELECT 42) SELECT * FROM cte2) SELECT *
FROM cte1);
----
42
# Subquery in LIMIT: not yet supported, verify clear error message.
# Ref: DuckDB PR #8519 — TopN optimization bug with subquery in LIMIT/OFFSET;
# DuckDB test/sql/order/test_limit.test
query error This feature is not implemented: Unsupported LIMIT expression
SELECT * FROM (VALUES (1), (2), (3)) AS t(x) ORDER BY x LIMIT (SELECT 2);
# Subquery in OFFSET: not yet supported, verify clear error message.
query error This feature is not implemented: Unsupported OFFSET expression
SELECT * FROM (VALUES (1), (2), (3)) AS t(x) ORDER BY x OFFSET (SELECT 1);
# Multiple subqueries where only one returns multiple rows still errors.
# Ref: DuckDB issue #13469 — silently dropping rows was a bug
query error DataFusion error: Execution error: Scalar subquery returned more
than one row
SELECT (SELECT 1), (SELECT v FROM sq_values);
# Subquery in a window function ORDER BY clause.
query II
SELECT x, SUM(x) OVER (ORDER BY x + (SELECT 0)) AS running
FROM (VALUES (1), (2), (3)) AS t(x);
----
1 1
2 3
3 6
# UNION ALL subquery with LIMIT 1 to avoid multi-row error.
query I
SELECT (SELECT v FROM (SELECT 1 AS v UNION ALL SELECT 2) AS t LIMIT 1);
----
1
# Aggregated subquery on empty table still returns one row (count = 0).
# Ref: DuckDB test_uncorrelated_scalar_subquery.test, test #8
query I
SELECT (SELECT count(*) FROM sq_empty);
----
0
# Zero-row subquery produces typed NULL, not an error.
# Ref: DuckDB test_uncorrelated_scalar_subquery.test, test #8
query II
SELECT x, (SELECT v FROM sq_empty) FROM sq_main ORDER BY x;
----
10 NULL
20 NULL
```
--
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]
