Skip to content

Commit 5f21582

Browse files
1fanwangkosiew
andauthored
fix: plan aggregates over CASE conditions with multiple columns (#25684)
## Which issue does this PR close? - Closes #25369. ## Rationale for this change A query that sums over a repeated CASE expression whose condition uses two columns fails during planning. The reported query now returns 1 instead of an internal schema error. ## What changes are included in this PR? CASE nullability inference now uses the existing three-valued bounds for AND, OR and NOT when a condition cannot be evaluated without input rows. The aggregate's schema check is unchanged. The deduction that a branch is unreachable relies on NULL AND x never being true, for any x, including NULL. OR and NOT keep the distinction between false and NULL, so a branch that can still be reached with a NULL value keeps the CASE nullable. ## What is the testing strategy for this PR? The reported query runs through a small program on upstream main and on this branch, and the same query is in cse.slt with SUM, COUNT, an expanded BETWEEN, NULL inputs, a nullable or missing ELSE and an empty input. The unit tests in case.rs cover independent columns in both operand orders, a nested AND over OR condition and its negation checked against evaluation on every combination of NULL inputs, and Boolean-cast failures in literal conditions. ## Testing Done Put the source below in src/main.rs of a new crate, add these dependencies to its Cargo.toml with the path pointing at a checkout, and run it with cargo run. ```toml [dependencies] datafusion = { path = "../datafusion/datafusion/core", default-features = false, features = ["sql"] } tokio = { version = "1", features = ["rt-multi-thread", "macros"] } ``` Before, on upstream main (0d79252): ```text SELECT sum(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END) BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS total FROM cse_nullable_case Error: Internal("Physical input schema should be the same as the one converted from logical input schema. Differences: \n\t- field nullability at index 0 [__common_expr_1]: (physical) true vs (logical) false") exit=1 ``` After, on this branch: ```text SELECT sum(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END) BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS total FROM cse_nullable_case +-------+ | total | +-------+ | 1 | +-------+ exit=0 ``` <details><summary>Reproducer source: main.rs</summary> ```rust use datafusion::error::Result; use datafusion::prelude::SessionContext; #[tokio::main] async fn main() -> Result<()> { let ctx = SessionContext::new(); ctx.sql("CREATE TABLE cse_nullable_case(a INT, b INT) AS VALUES (1, 1), (2, 2), (3, 3)") .await? .collect() .await?; let sql = "SELECT sum(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END) \ BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS total FROM cse_nullable_case"; println!("{sql}"); ctx.sql(sql).await?.show().await?; Ok(()) } ``` </details> ## Are there any user-facing changes? The affected aggregate queries plan and run. There is no public API change. --------- Signed-off-by: 1fanwang <[email protected]> Co-authored-by: kosiew <[email protected]>
1 parent 2ff1f76 commit 5f21582

2 files changed

Lines changed: 230 additions & 25 deletions

File tree

  • datafusion

‎datafusion/physical-expr/src/expressions/case.rs‎

Lines changed: 165 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ mod literal_lookup_table;
1919

2020
use super::{Column, Literal};
2121
use crate::expressions::{
22-
CastExpr, LambdaVariable, NegativeExpr, NotExpr, lit, try_cast,
22+
BinaryExpr, CastExpr, LambdaVariable, NegativeExpr, NotExpr, lit, try_cast,
2323
};
2424
use crate::{PhysicalExpr, ScalarFunctionExpr};
2525
use arrow::array::*;
@@ -35,6 +35,8 @@ use datafusion_common::{
3535
internal_datafusion_err, internal_err,
3636
};
3737
use datafusion_expr::ColumnarValue;
38+
use datafusion_expr_common::interval_arithmetic::NullableInterval;
39+
use datafusion_expr_common::operator::Operator;
3840
use indexmap::IndexMap;
3941
use std::borrow::Cow;
4042
use std::collections::BTreeSet;
@@ -1292,19 +1294,14 @@ impl PhysicalExpr for CaseExpr {
12921294
Ok(e) => e,
12931295
};
12941296

1295-
// Try to const evaluate the modified `when` expression.
1296-
let predicate_result = match evaluate_predicate(&with_null) {
1297+
let can_be_true = match evaluate_predicate(&with_null).and_then(|bounds| {
1298+
bounds.contains_value(ScalarValue::Boolean(Some(true)))
1299+
}) {
12971300
Err(e) => return Some(Err(e)),
12981301
Ok(b) => b,
12991302
};
13001303

1301-
match predicate_result {
1302-
// Evaluation was inconclusive or true, so the 'then' expression is reachable
1303-
None | Some(true) => Some(Ok(())),
1304-
// Evaluation proves the branch will never be taken.
1305-
// The most common pattern for this is `WHEN x IS NOT NULL THEN x`.
1306-
Some(false) => None,
1307-
}
1304+
if can_be_true { Some(Ok(())) } else { None }
13081305
});
13091306

13101307
if let Some(nullable_then) = nullable_then {
@@ -1516,12 +1513,8 @@ impl CaseExpr {
15161513
}
15171514
}
15181515

1519-
/// Attempts to const evaluate the given `predicate`.
1520-
/// Returns:
1521-
/// - `Some(true)` if the predicate evaluates to a truthy value.
1522-
/// - `Some(false)` if the predicate evaluates to a falsy value.
1523-
/// - `None` if the predicate could not be evaluated.
1524-
fn evaluate_predicate(predicate: &Arc<dyn PhysicalExpr>) -> Result<Option<bool>> {
1516+
/// Uses three-valued bounds when input columns prevent constant evaluation.
1517+
fn evaluate_predicate(predicate: &Arc<dyn PhysicalExpr>) -> Result<NullableInterval> {
15251518
// Create a dummy record with no columns and one row
15261519
let batch = RecordBatch::try_new_with_options(
15271520
Arc::new(Schema::empty()),
@@ -1531,15 +1524,24 @@ fn evaluate_predicate(predicate: &Arc<dyn PhysicalExpr>) -> Result<Option<bool>>
15311524

15321525
// Evaluate the predicate and interpret the result as a boolean
15331526
let result = match predicate.evaluate(&batch) {
1534-
// An error during evaluation means we couldn't const evaluate the predicate, so return `None`
1535-
Err(_) => None,
1536-
Ok(ColumnarValue::Array(array)) => Some(
1527+
Err(_) => {
1528+
if let Some(expr) = predicate.downcast_ref::<BinaryExpr>()
1529+
&& matches!(expr.op(), Operator::And | Operator::Or)
1530+
{
1531+
return evaluate_predicate(expr.left())?
1532+
.apply_operator(expr.op(), &evaluate_predicate(expr.right())?);
1533+
}
1534+
if let Some(expr) = predicate.downcast_ref::<NotExpr>() {
1535+
return evaluate_predicate(expr.arg())?.not();
1536+
}
1537+
return Ok(NullableInterval::ANY_TRUTH_VALUE);
1538+
}
1539+
Ok(ColumnarValue::Array(array)) => {
15371540
ScalarValue::try_from_array(array.as_ref(), 0)?
1538-
.cast_to(&DataType::Boolean)?,
1539-
),
1540-
Ok(ColumnarValue::Scalar(scalar)) => Some(scalar.cast_to(&DataType::Boolean)?),
1541+
}
1542+
Ok(ColumnarValue::Scalar(scalar)) => scalar,
15411543
};
1542-
Ok(result.map(|v| matches!(v, ScalarValue::Boolean(Some(true)))))
1544+
Ok(result.cast_to(&DataType::Boolean)?.into())
15431545
}
15441546

15451547
fn replace_with_null(
@@ -1594,7 +1596,7 @@ mod tests {
15941596
use super::*;
15951597

15961598
use crate::expressions;
1597-
use crate::expressions::{BinaryExpr, binary, cast, col, is_not_null};
1599+
use crate::expressions::{binary, cast, col, is_not_null};
15981600
use arrow::buffer::Buffer;
15991601
use arrow::datatypes::DataType::Float64;
16001602
use arrow::datatypes::Field;
@@ -1603,7 +1605,6 @@ mod tests {
16031605
use datafusion_common::plan_err;
16041606
use datafusion_common::tree_node::{Transformed, TransformedResult, TreeNode};
16051607
use datafusion_expr::type_coercion::binary::type_union_coercion;
1606-
use datafusion_expr_common::operator::Operator;
16071608
use datafusion_physical_expr_common::physical_expr::fmt_sql;
16081609
use half::f16;
16091610

@@ -2796,6 +2797,145 @@ mod tests {
27962797
case_expression_nullability(false)
27972798
}
27982799

2800+
#[rstest::rstest]
2801+
#[case::and(Operator::And, false, false)]
2802+
#[case::or(Operator::Or, false, true)]
2803+
#[case::not_and(Operator::And, true, true)]
2804+
#[case::not_or(Operator::Or, true, false)]
2805+
fn test_case_expression_nullability_with_independent_columns(
2806+
#[case] op: Operator,
2807+
#[case] negated: bool,
2808+
#[case] nullable: bool,
2809+
#[values(false, true)] reverse: bool,
2810+
) -> Result<()> {
2811+
let schema = Schema::new(vec![
2812+
Field::new("a", DataType::Int32, true),
2813+
Field::new("b", DataType::Int32, true),
2814+
]);
2815+
let a: Arc<dyn PhysicalExpr> = col("a", &schema)?;
2816+
let left: Arc<dyn PhysicalExpr> =
2817+
binary(Arc::clone(&a), Operator::Gt, lit(1), &schema)?;
2818+
let right: Arc<dyn PhysicalExpr> =
2819+
binary(col("b", &schema)?, Operator::Lt, lit(3), &schema)?;
2820+
let predicate: Arc<dyn PhysicalExpr> = if reverse {
2821+
binary(right, op, left, &schema)?
2822+
} else {
2823+
binary(left, op, right, &schema)?
2824+
};
2825+
let predicate: Arc<dyn PhysicalExpr> = if negated {
2826+
expressions::not(predicate)?
2827+
} else {
2828+
predicate
2829+
};
2830+
2831+
assert_nullability(when_then_else(&predicate, &a, &lit(0))?, &schema, nullable);
2832+
Ok(())
2833+
}
2834+
2835+
#[rstest::rstest]
2836+
#[case::and_over_or(false, false)]
2837+
#[case::not_and_over_or(true, true)]
2838+
fn test_case_expression_nullability_with_nested_predicates(
2839+
#[case] negated: bool,
2840+
#[case] nullable: bool,
2841+
#[values(false, true)] reverse: bool,
2842+
) -> Result<()> {
2843+
let schema = Arc::new(Schema::new(vec![
2844+
Field::new("a", DataType::Int32, true),
2845+
Field::new("b", DataType::Int32, true),
2846+
]));
2847+
let a: Arc<dyn PhysicalExpr> = col("a", &schema)?;
2848+
let b: Arc<dyn PhysicalExpr> = col("b", &schema)?;
2849+
2850+
// a > 1 AND (b < 3 OR b IS NULL)
2851+
let a_gt_1: Arc<dyn PhysicalExpr> =
2852+
binary(Arc::clone(&a), Operator::Gt, lit(1), &schema)?;
2853+
let b_lt_3_or_null: Arc<dyn PhysicalExpr> = binary(
2854+
binary(Arc::clone(&b), Operator::Lt, lit(3), &schema)?,
2855+
Operator::Or,
2856+
expressions::is_null(b)?,
2857+
&schema,
2858+
)?;
2859+
let predicate: Arc<dyn PhysicalExpr> = if reverse {
2860+
binary(b_lt_3_or_null, Operator::And, a_gt_1, &schema)?
2861+
} else {
2862+
binary(a_gt_1, Operator::And, b_lt_3_or_null, &schema)?
2863+
};
2864+
let predicate: Arc<dyn PhysicalExpr> = if negated {
2865+
expressions::not(predicate)?
2866+
} else {
2867+
predicate
2868+
};
2869+
let expr = when_then_else(&predicate, &a, &lit(0))?;
2870+
2871+
assert_nullability(Arc::clone(&expr), &schema, nullable);
2872+
2873+
// Cross-check the inferred nullability against evaluation on every combination
2874+
// of NULL and non-NULL inputs. With a = NULL and b = 3 the negated predicate is
2875+
// TRUE, so the THEN branch yields NULL.
2876+
let rows: Vec<(Option<i32>, Option<i32>)> = [None, Some(1), Some(2)]
2877+
.into_iter()
2878+
.flat_map(|a| [None, Some(2), Some(3)].into_iter().map(move |b| (a, b)))
2879+
.collect();
2880+
let batch = RecordBatch::try_new(
2881+
Arc::clone(&schema),
2882+
vec![
2883+
Arc::new(Int32Array::from_iter(rows.iter().map(|(a, _)| *a))),
2884+
Arc::new(Int32Array::from_iter(rows.iter().map(|(_, b)| *b))),
2885+
],
2886+
)?;
2887+
let result = expr.evaluate(&batch)?.into_array(batch.num_rows())?;
2888+
assert_eq!(result.null_count() > 0, nullable);
2889+
Ok(())
2890+
}
2891+
2892+
#[rstest::rstest]
2893+
#[case::literal(lit("invalid"))]
2894+
#[case::and_left(Arc::new(BinaryExpr::new(
2895+
lit("invalid"),
2896+
Operator::And,
2897+
make_col("flag", 0)
2898+
)))]
2899+
#[case::and_right(Arc::new(BinaryExpr::new(
2900+
make_col("flag", 0),
2901+
Operator::And,
2902+
lit("invalid")
2903+
)))]
2904+
#[case::or_left(Arc::new(BinaryExpr::new(
2905+
lit("invalid"),
2906+
Operator::Or,
2907+
make_col("flag", 0)
2908+
)))]
2909+
#[case::or_right(Arc::new(BinaryExpr::new(
2910+
make_col("flag", 0),
2911+
Operator::Or,
2912+
lit("invalid")
2913+
)))]
2914+
#[case::not(Arc::new(NotExpr::new(lit("invalid"))))]
2915+
fn test_case_expression_nullability_with_invalid_predicate(
2916+
#[case] predicate: Arc<dyn PhysicalExpr>,
2917+
) -> Result<()> {
2918+
let batch = RecordBatch::try_from_iter(vec![
2919+
(
2920+
"flag",
2921+
Arc::new(BooleanArray::from(vec![Some(true), Some(false), None]))
2922+
as ArrayRef,
2923+
),
2924+
(
2925+
"value",
2926+
Arc::new(Int32Array::from(vec![Some(1), None, Some(3)])) as ArrayRef,
2927+
),
2928+
])?;
2929+
let schema = batch.schema();
2930+
let expr = when_then_else(&predicate, &col("value", &schema)?, &lit(0))?;
2931+
let error = expr
2932+
.nullable(&schema)
2933+
.expect_err("invalid predicates must fail nullability inference");
2934+
assert!(error.to_string().contains("invalid"), "{error}");
2935+
assert!(expr.evaluate(&batch).is_err());
2936+
Ok(())
2937+
}
2938+
27992939
fn case_expression_nullability(col_is_nullable: bool) -> Result<()> {
28002940
let schema =
28012941
Schema::new(vec![Field::new("foo", DataType::Int32, col_is_nullable)]);

‎datafusion/sqllogictest/test_files/cse.slt‎

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -214,6 +214,71 @@ physical_plan
214214
02)--ProjectionExec: expr=[a@0 = 1 as __common_expr_1, a@0 = 2 as __common_expr_2, a@0 + 3 as __common_expr_3, a@0 + 4 as __common_expr_4]
215215
03)----DataSourceExec: partitions=1, partition_sizes=[0]
216216

217+
statement ok
218+
CREATE TABLE cse_nullable_case(a INT, b INT) AS VALUES (1, 1), (2, 2), (3, 3);
219+
220+
query I
221+
SELECT sum(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END)
222+
BETWEEN 1 AND 2 THEN 1 ELSE 0 END)
223+
FROM cse_nullable_case;
224+
----
225+
1
226+
227+
query I
228+
SELECT count(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END)
229+
BETWEEN 1 AND 2 THEN 1 ELSE 0 END)
230+
FROM cse_nullable_case;
231+
----
232+
3
233+
234+
query I
235+
SELECT sum(CASE WHEN (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END) >= 1
236+
AND (CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END) <= 2
237+
THEN 1 ELSE 0 END)
238+
FROM cse_nullable_case;
239+
----
240+
1
241+
242+
statement ok
243+
INSERT INTO cse_nullable_case VALUES (NULL, 2), (2, NULL), (NULL, NULL);
244+
245+
query II
246+
SELECT sum(v), count(v)
247+
FROM (SELECT CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END AS v FROM cse_nullable_case);
248+
----
249+
2 6
250+
251+
query II
252+
SELECT sum(v), count(v)
253+
FROM (SELECT CASE WHEN a > 1 OR b < 3 THEN a ELSE 0 END AS v FROM cse_nullable_case);
254+
----
255+
8 5
256+
257+
query II
258+
SELECT sum(v), count(v)
259+
FROM (SELECT CASE WHEN NOT (a > 1 OR b < 3) THEN a ELSE 0 END AS v FROM cse_nullable_case);
260+
----
261+
0 6
262+
263+
query II
264+
SELECT sum(v), count(v)
265+
FROM (SELECT CASE WHEN a > 1 AND b < 3 THEN a END AS v FROM cse_nullable_case);
266+
----
267+
2 1
268+
269+
query II
270+
SELECT sum(v), count(v)
271+
FROM (SELECT CASE WHEN a > 1 AND b < 3 THEN a ELSE b END AS v FROM cse_nullable_case);
272+
----
273+
8 4
274+
275+
query II
276+
SELECT sum(v), count(v)
277+
FROM (SELECT CASE WHEN a > 1 AND b < 3 THEN a ELSE 0 END AS v FROM cse_nullable_case)
278+
WHERE false;
279+
----
280+
NULL 0
281+
217282
# Only conditionally evaluated expressions
218283
query TT
219284
EXPLAIN SELECT

0 commit comments

Comments
 (0)