Skip to content

Commit d1815c2

Browse files
fornwalllukekim
authored andcommitted
fix: account for empty scalar subqueries in nullability (apache#24516)
## Which issue does this PR close? - Closes apache#24513. ## Rationale for this change A scalar subquery that returns zero rows evaluates to `NULL`, even when its projected expression is non-nullable. DataFusion currently derives `Expr::ScalarSubquery` nullability from the subquery's output field. This can produce a non-nullable output schema containing `NULL`, and can cause `SimplifyExpressions` to incorrectly fold predicates such as: ```sql SELECT (SELECT 1 WHERE FALSE) IS NULL; ``` to `false`. This change deliberately marks all scalar subqueries nullable, including those guaranteed to return exactly one row (such as an ungrouped aggregate like `(SELECT count(*) FROM t)`). This is a conservative trade-off that gives up some nullability precision for correctness, and is consistent with how PostgreSQL treats scalar subqueries. A possible follow-up refinement is a `LogicalPlan::min_rows()` lower bound (mirroring the existing `max_rows()`), which would let uncorrelated scalar subqueries provably returning at least one row keep their projected field's nullability. ## What changes are included in this PR? Scalar subqueries are conservatively marked nullable in logical expression schema derivation and physical expression planning. The projected field's data type, name, and metadata are preserved. ## Are these changes tested? Yes. Unit tests cover logical schema derivation and expression simplification, and SQLLogicTests cover both zero-row execution and `IS NULL` correctness. The full workspace test suite and Clippy with warnings denied pass. ## Are there any user-facing changes? Yes. Zero-row scalar subqueries with non-nullable projections now return `NULL` without a schema validation error, and `IS NULL` predicates produce the correct result. In addition, output schemas containing scalar subqueries now always mark those fields as nullable, even for subqueries that can never produce `NULL`. Downstream consumers that inspect schema nullability can observe this change. No public APIs change. --- AI usage: Created with Claude Code and Opus 5. I have reviewed the code and made modifications where it made sense. --------- Signed-off-by: Fredrik Fornwall <[email protected]> (cherry picked from commit 4798476)
1 parent 624470f commit d1815c2

4 files changed

Lines changed: 80 additions & 9 deletions

File tree

‎datafusion/expr/src/expr_schema.rs‎

Lines changed: 30 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -374,9 +374,9 @@ impl ExprSchemable for Expr {
374374

375375
Ok(expr_nullable | subquery_nullable)
376376
}
377-
Expr::ScalarSubquery(subquery) => {
378-
Ok(subquery.subquery.schema().field(0).is_nullable())
379-
}
377+
// A scalar subquery may return no rows, in which case it evaluates to NULL
378+
// regardless of the nullability of its projected field.
379+
Expr::ScalarSubquery(_) => Ok(true),
380380
Expr::BinaryExpr(BinaryExpr { left, right, .. }) => {
381381
Ok(left.nullable(input_schema)? || right.nullable(input_schema)?)
382382
}
@@ -522,9 +522,15 @@ impl ExprSchemable for Expr {
522522
| Expr::Exists { .. } => {
523523
Ok(Arc::new(Field::new(&schema_name, DataType::Boolean, false)))
524524
}
525-
Expr::ScalarSubquery(subquery) => {
526-
Ok(Arc::clone(&subquery.subquery.schema().fields()[0]))
527-
}
525+
Expr::ScalarSubquery(subquery) => Ok(Arc::new(
526+
subquery
527+
.subquery
528+
.schema()
529+
.field(0)
530+
.as_ref()
531+
.clone()
532+
.with_nullable(true),
533+
)),
528534
Expr::BinaryExpr(BinaryExpr { left, right, op }) => {
529535
let (left_field, right_field) =
530536
(left.to_field(schema)?.1, right.to_field(schema)?.1);
@@ -806,7 +812,7 @@ mod tests {
806812
use crate::logical_plan::builder::LogicalTableSource;
807813
use crate::{
808814
LogicalPlanBuilder, and, col, in_subquery, lit, not, or,
809-
out_ref_col_with_metadata, when,
815+
out_ref_col_with_metadata, scalar_subquery, when,
810816
};
811817

812818
use arrow::datatypes::Schema;
@@ -1274,6 +1280,23 @@ mod tests {
12741280
);
12751281
}
12761282

1283+
#[test]
1284+
fn scalar_subquery_is_nullable_with_non_nullable_output() {
1285+
let subquery = LogicalPlanBuilder::empty(false)
1286+
.project(vec![lit(1)])
1287+
.unwrap()
1288+
.build()
1289+
.unwrap();
1290+
assert!(!subquery.schema().field(0).is_nullable());
1291+
1292+
let expr = scalar_subquery(Arc::new(subquery));
1293+
assert!(expr.nullable(&MockExprSchema::new()).unwrap());
1294+
1295+
let field = expr.to_field(&MockExprSchema::new()).unwrap().1;
1296+
assert_eq!(field.data_type(), &DataType::Int32);
1297+
assert!(field.is_nullable());
1298+
}
1299+
12771300
#[test]
12781301
fn test_scalar_variable() {
12791302
let mut meta = HashMap::new();

‎datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3777,6 +3777,21 @@ mod tests {
37773777
);
37783778
}
37793779

3780+
#[test]
3781+
fn simplify_scalar_subquery_is_null() {
3782+
let subquery = LogicalPlanBuilder::empty(false)
3783+
.project(vec![lit(1)])
3784+
.unwrap()
3785+
.build()
3786+
.unwrap();
3787+
let scalar_subquery = scalar_subquery(Arc::new(subquery));
3788+
3789+
assert_eq!(
3790+
simplify(scalar_subquery.clone().is_null()),
3791+
scalar_subquery.is_null()
3792+
);
3793+
}
3794+
37803795
#[test]
37813796
fn simplify_expr_is_unknown() {
37823797
assert_eq!(simplify(col("c2").is_unknown()), col("c2").is_unknown(),);

‎datafusion/physical-expr/src/planner.rs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -413,10 +413,10 @@ pub fn create_physical_expr(
413413
);
414414
}
415415
let dt = schema.field(0).data_type().clone();
416-
let nullable = schema.field(0).is_nullable();
417416
Ok(Arc::new(ScalarSubqueryExpr::new(
418417
dt,
419-
nullable,
418+
// A scalar subquery may return no rows and evaluate to NULL.
419+
true,
420420
index,
421421
execution_props.subquery_results.clone(),
422422
)))

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1805,6 +1805,24 @@ SELECT (SELECT v FROM sq_empty);
18051805
----
18061806
NULL
18071807

1808+
# A zero-row scalar subquery is nullable even when its projected expression is not.
1809+
query I
1810+
SELECT (SELECT 1 WHERE FALSE);
1811+
----
1812+
NULL
1813+
1814+
# Its nullability must prevent SimplifyExpressions from folding IS NULL to false.
1815+
query B
1816+
SELECT (SELECT 1 WHERE FALSE) IS NULL;
1817+
----
1818+
true
1819+
1820+
# The same holds for the opposite fold direction.
1821+
query B
1822+
SELECT (SELECT 1 WHERE FALSE) IS NOT NULL;
1823+
----
1824+
false
1825+
18081826
# Scalar subquery returning zero rows in arithmetic → NULL propagation
18091827
query I
18101828
SELECT x + (SELECT v FROM sq_empty) FROM sq_main;
@@ -2145,6 +2163,21 @@ SELECT (SELECT v FROM sq_empty);
21452163
----
21462164
NULL
21472165

2166+
# A zero-row scalar subquery is nullable even when its projected expression is
2167+
# not. The rewrite to a left join already produces a nullable column here, so
2168+
# this pins both paths to the same result.
2169+
query I
2170+
SELECT (SELECT 1 WHERE FALSE);
2171+
----
2172+
NULL
2173+
2174+
# SimplifyExpressions runs before ScalarSubqueryToJoin, so this fold is still
2175+
# governed by Expr::ScalarSubquery nullability on this path.
2176+
query B
2177+
SELECT (SELECT 1 WHERE FALSE) IS NULL;
2178+
----
2179+
true
2180+
21482181
# Scalar subquery returning zero rows in arithmetic → NULL propagation
21492182
query I
21502183
SELECT x + (SELECT v FROM sq_empty) FROM sq_main;

0 commit comments

Comments
 (0)