Skip to content

Commit d1ff850

Browse files
committed
fix: error instead of dropping a pulled up HAVING in scalar subqueries whose result is NULL on empty input
Closes apache#26505.
1 parent bc86321 commit d1ff850

2 files changed

Lines changed: 54 additions & 0 deletions

File tree

‎datafusion/optimizer/src/scalar_subquery_to_join.rs‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -345,6 +345,24 @@ fn build_join(
345345
.collected_count_expr_map
346346
.get(&decorrelated_subquery)
347347
.cloned();
348+
349+
// A pulled up `HAVING` is only applied through the count bug compensation
350+
// below, which skips the expressions that are NULL on empty input. Keep
351+
// the subquery correlated rather than drop the `HAVING` for its output.
352+
// <https://github.com/apache/datafusion/issues/26505>
353+
if pull_up.pull_up_having_expr.is_some() {
354+
let output_name = subquery_plan.schema().field(0).name();
355+
let Some(result) = collected_count_expr_map
356+
.as_ref()
357+
.and_then(|expr_map| expr_map.get(output_name))
358+
else {
359+
return Ok(None);
360+
};
361+
if evaluates_to_null(result.clone(), result.column_refs())? {
362+
return Ok(None);
363+
}
364+
}
365+
348366
let aliased_subquery = LogicalPlanBuilder::from(decorrelated_subquery)
349367
.alias(subquery_alias.to_string())?
350368
.build()?;

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1363,6 +1363,42 @@ SELECT t1_id, (
13631363
33 NULL
13641364
44 NULL
13651365

1366+
# A HAVING that is true on empty input over an aggregate that is NULL on empty
1367+
# input. https://github.com/apache/datafusion/issues/26505
1368+
# Errors rather than returning wrong rows. Expected `(1, NULL), (2, NULL)`.
1369+
query error Physical plan does not support logical expression ScalarSubquery
1370+
SELECT x.a, (SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING sum(y.b) IS NULL) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1371+
1372+
# Errors rather than returning wrong rows. Expected `(1, NULL), (2, NULL)`.
1373+
query error Physical plan does not support logical expression ScalarSubquery
1374+
SELECT x.a, (SELECT max(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING max(y.b) IS NULL) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1375+
1376+
# Errors rather than returning wrong rows. Expected `(1, NULL), (2, NULL)`.
1377+
query error Physical plan does not support logical expression ScalarSubquery
1378+
SELECT x.a, (SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING count(*) = 0) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1379+
1380+
# Still decorrelated: the HAVING is applied to `count(*)`.
1381+
query II
1382+
SELECT x.a, (SELECT count(*) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING count(*) = 0) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1383+
----
1384+
1 NULL
1385+
2 0
1386+
1387+
# Still decorrelated: the HAVING reads an aggregate that is NULL on empty input,
1388+
# but the subquery returns `count(*)`, which the HAVING is applied to.
1389+
query II
1390+
SELECT x.a, (SELECT count(*) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING sum(y.b) IS NULL) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1391+
----
1392+
1 NULL
1393+
2 0
1394+
1395+
# Still decorrelated: a HAVING that is false on empty input is not pulled up.
1396+
query II
1397+
SELECT x.a, (SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING sum(y.b) > 5) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a;
1398+
----
1399+
1 10
1400+
2 NULL
1401+
13661402
#correlated_scalar_subquery_count_agg_in_case_when
13671403
query TT
13681404
explain

0 commit comments

Comments
 (0)