Skip to content

Commit 587e74c

Browse files
authored
fix: preserve short-circuit evaluation in Boolean CASE simplification (apache#25143)
## Which issue does this PR close? Closes apache#25136. ## Rationale for this change `CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE false END` currently becomes an `AND` predicate. On input containing `'abc'`, predicate evaluation can reach the cast even though the `WHEN` excluded that row. ## What changes are included in this PR? Restrict the searched Boolean CASE-to-AND/OR rewrite to columns or literals in every `WHEN` condition and every `THEN`/`ELSE` output. Apply the same restriction to the companion inversion rule. Even a nonvolatile first condition can fail, so it must remain evaluated when both outputs are identical constants. This is deliberately conservative: it does not attempt general expression fallibility analysis or change predicate ordering. More complex conditional expressions retain their CASE structure. Existing column/literal rewrites remain available, and two optimizer tests record the intentionally reduced simplification. Four EXPLAIN snapshots in `null_aware_mark_join.slt` and `subquery.slt` also change. CASE expressions generated for ANY comparisons survive until EXISTS decorrelation, allowing the first mark column to be reused instead of duplicating its join. A correlated `max(...) IS NULL` projection retains CASE under the stricter first-WHEN guard. Existing query-result expectations are unchanged; an additional result test covers non-NULL maxima, all-NULL groups, empty groups, and NULL correlation keys. ## What is the testing strategy for this PR? The SQL regressions in `case.slt` cover guarded casts in filters and projections, true/false/NULL/implicit ELSE, a fallible later WHEN with literal outputs, guarded division, and a selected invalid branch that must still error. Five queries failed against current main before the optimizer change. The regressions also cover a volatile first WHEN and a fallible first WHEN with identical false outputs, which must still raise the cast error. Initial validation at `49c02ec7` with pinned Rust 1.97.0 (before the first-WHEN review correction): - `cargo fmt --all` and `git diff --check` passed. - `cargo clippy --all-targets --all-features -- -D warnings` passed. - The complete `./dev/rust_lint.sh` suite passed. - The contributor-guide extended workspace command passed: 11,341 Rust tests, 8 ignored, and all 513 SQL test files. No additional test exclusions were needed. Latest follow-up validation: all 787 optimizer tests and all 512 SQL files supported by the existing review-fix binary passed, including six focused CASE/subquery files. `cargo fmt --all`, `git diff --check`, and `cargo clippy --all-targets --all-features -- -D warnings` passed with pinned Rust 1.97.0. The full `./dev/rust_lint.sh` suite also passed. Hosted CI for the follow-up remains pending. The nearby `simplify_function_over_case` microbenchmark was run against current main with separately rebuilt binaries (Rust 1.98.0, dev profile, short Criterion samples). The quieter comparison reported no change or changes within the noise threshold. It exercises string-output CASE, not the retained Boolean CASE workloads, so this is not a general performance claim. ## Are there any user-facing changes? Guarded Boolean CASE expressions preserve branch-local evaluation. Some optimized plans retain CASE expressions where Boolean lowering was previously applied; there is no public API change. Generated with OpenAI Codex assistance, including implementation, tests, and this description.
1 parent 16ecdc9 commit 587e74c

4 files changed

Lines changed: 241 additions & 170 deletions

File tree

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

Lines changed: 120 additions & 109 deletions
Original file line numberDiff line numberDiff line change
@@ -1575,7 +1575,9 @@ impl TreeNodeRewriter for Simplifier<'_> {
15751575
// ---> (X AND A) OR (Y AND B AND NOT X) OR ... (NOT (X OR Y) AND Q)
15761576
//
15771577
// Note: the rationale for this rewrite is that the expr can then be further
1578-
// simplified using the existing rules for AND/OR
1578+
// simplified using the existing rules for AND/OR. Unlike CASE, AND/OR
1579+
// do not guarantee branch-local evaluation, so only expose columns
1580+
// and literals from conditional branches.
15791581
Expr::Case(Case {
15801582
expr: None,
15811583
when_then_expr,
@@ -1586,7 +1588,8 @@ impl TreeNodeRewriter for Simplifier<'_> {
15861588
// or all thens are literal bools and a small number of them are true
15871589
|| (when_then_expr.iter().all(|(_, then)| is_bool_lit(then))
15881590
&& when_then_expr.iter().filter(|(_, then)| is_true(then)).count() < 3))
1589-
&& info.is_boolean_type(&when_then_expr[0].1)? =>
1591+
&& info.is_boolean_type(&when_then_expr[0].1)?
1592+
&& can_lower_case_to_boolean(&when_then_expr, else_expr.as_deref()) =>
15901593
{
15911594
// String disjunction of all the when predicates encountered so far. Not nullable.
15921595
let mut filter_expr = lit(false);
@@ -1643,7 +1646,8 @@ impl TreeNodeRewriter for Simplifier<'_> {
16431646
.filter(|(_, then)| is_false(then))
16441647
.count()
16451648
< 3
1646-
&& else_expr.as_deref().is_none_or(is_bool_lit) =>
1649+
&& else_expr.as_deref().is_none_or(is_bool_lit)
1650+
&& can_lower_case_to_boolean(&when_then_expr, else_expr.as_deref()) =>
16471651
{
16481652
Transformed::yes(
16491653
Expr::Case(Case {
@@ -2463,6 +2467,20 @@ fn simplify_inlist_set_operation(
24632467
}))
24642468
}
24652469

2470+
/// Conservatively checks the inputs whose evaluation can change when lowering
2471+
/// CASE to AND/OR. [`Expr`] has no general fallibility analysis: only columns and
2472+
/// literals are admitted for all WHEN conditions and branch outputs.
2473+
fn can_lower_case_to_boolean(
2474+
when_then_expr: &[(Box<Expr>, Box<Expr>)],
2475+
else_expr: Option<&Expr>,
2476+
) -> bool {
2477+
let is_leaf = |expr: &Expr| matches!(expr, Expr::Column(_) | Expr::Literal(..));
2478+
when_then_expr
2479+
.iter()
2480+
.all(|(when, then)| is_leaf(when) && is_leaf(then))
2481+
&& else_expr.is_none_or(is_leaf)
2482+
}
2483+
24662484
/// Returns expression testing a boolean `expr` for being exactly `true` (not `false` or NULL).
24672485
fn is_exactly_true(expr: Expr, info: &SimplifyContext) -> Result<Expr> {
24682486
if !info.nullable(&expr)? {
@@ -2608,12 +2626,15 @@ mod tests {
26082626
// ELSE false
26092627
// END
26102628
//
2611-
// Can be simplified to `i < 5`
2629+
// Fold the constant conditions, but preserve CASE because the THEN
2630+
// expression is outside the conservative column/literal subset.
26122631
let expr = when(col("i").gt(lit(5)).and(lit(false)), col("i").gt(lit(5)))
26132632
.when(col("i").lt(lit(5)).and(lit(true)), col("i").lt(lit(5)))
26142633
.otherwise(lit(false))
26152634
.unwrap();
2616-
let expected = col("i").lt(lit(5));
2635+
let expected = when(col("i").lt(lit(5)), col("i").lt(lit(5)))
2636+
.otherwise(lit(false))
2637+
.unwrap();
26172638
assert_eq!(expected, simplifier.simplify(expr).unwrap());
26182639
}
26192640

@@ -4241,76 +4262,89 @@ mod tests {
42414262
Some(Box::new(lit("ready"))),
42424263
));
42434264

4244-
assert_eq!(
4245-
simplify(binary_expr(
4246-
complex_case.clone(),
4265+
// Comparisons still fold into literal outputs, but later WHEN conditions must
4266+
// remain conditional: they are not columns or literals.
4267+
for (op, value, outputs) in [
4268+
(
42474269
Operator::Eq,
4248-
lit("completed"),
4249-
)),
4250-
not_distinct_from(col("c1").eq(lit("completed")), lit(true)).and(
4251-
distinct_from(col("c1").eq(lit("inboxed")), lit(true))
4252-
.and(distinct_from(col("c1").eq(lit("scheduled")), lit(true)))
4253-
)
4254-
);
4255-
4256-
assert_eq!(
4257-
simplify(binary_expr(
4258-
complex_case.clone(),
4270+
"completed",
4271+
[false, false, true, false, false, false, false],
4272+
),
4273+
(
42594274
Operator::NotEq,
4260-
lit("completed"),
4261-
)),
4262-
distinct_from(col("c1").eq(lit("completed")), lit(true))
4263-
.or(not_distinct_from(col("c1").eq(lit("inboxed")), lit(true))
4264-
.or(not_distinct_from(col("c1").eq(lit("scheduled")), lit(true))))
4265-
);
4266-
4267-
assert_eq!(
4268-
simplify(binary_expr(
4269-
complex_case.clone(),
4275+
"completed",
4276+
[true, true, false, true, true, true, true],
4277+
),
4278+
(
42704279
Operator::Eq,
4271-
lit("running"),
4272-
)),
4273-
not_distinct_from(col("c2"), lit(true)).and(
4274-
distinct_from(col("c1").eq(lit("inboxed")), lit(true))
4275-
.and(distinct_from(col("c1").eq(lit("scheduled")), lit(true)))
4276-
.and(distinct_from(col("c1").eq(lit("completed")), lit(true)))
4277-
.and(distinct_from(col("c1").eq(lit("paused")), lit(true)))
4278-
)
4279-
);
4280-
4281-
assert_eq!(
4282-
simplify(binary_expr(
4283-
complex_case.clone(),
4280+
"running",
4281+
[false, false, false, false, true, false, false],
4282+
),
4283+
(
42844284
Operator::Eq,
4285-
lit("ready"),
4286-
)),
4287-
distinct_from(col("c1").eq(lit("inboxed")), lit(true))
4288-
.and(distinct_from(col("c1").eq(lit("scheduled")), lit(true)))
4289-
.and(distinct_from(col("c1").eq(lit("completed")), lit(true)))
4290-
.and(distinct_from(col("c1").eq(lit("paused")), lit(true)))
4291-
.and(distinct_from(col("c2"), lit(true)))
4292-
.and(distinct_from(
4293-
col("c1").eq(lit("invoked")).and(col("c3").gt(lit(0))),
4294-
lit(true)
4295-
))
4296-
);
4297-
4298-
assert_eq!(
4299-
simplify(binary_expr(
4300-
complex_case.clone(),
4285+
"ready",
4286+
[false, false, false, false, false, false, true],
4287+
),
4288+
(
43014289
Operator::NotEq,
4302-
lit("ready"),
4290+
"ready",
4291+
[true, true, true, true, true, true, false],
4292+
),
4293+
] {
4294+
let Expr::Case(mut expected) = complex_case.clone() else {
4295+
unreachable!()
4296+
};
4297+
for ((_, then), value) in expected.when_then_expr.iter_mut().zip(outputs) {
4298+
**then = lit(value);
4299+
}
4300+
expected.else_expr = Some(Box::new(lit(outputs[6])));
4301+
assert_eq!(
4302+
simplify(binary_expr(complex_case.clone(), op, lit(value))),
4303+
Expr::Case(expected)
4304+
);
4305+
}
4306+
}
4307+
4308+
#[test]
4309+
fn simplify_case_preserves_conditional_expressions() {
4310+
let fallible =
4311+
Expr::Cast(Cast::new(Box::new(col("c1")), DataType::Int32)).gt(lit(0_i32));
4312+
let volatile = Expr::ScalarFunction(ScalarFunction::new_udf(
4313+
Arc::new(ScalarUDF::new_from_impl(VolatileUdf::new())),
4314+
vec![],
4315+
));
4316+
for expr in [
4317+
Expr::Case(Case::new(
4318+
None,
4319+
vec![(Box::new(fallible.clone()), Box::new(lit(false)))],
4320+
Some(Box::new(lit(false))),
43034321
)),
4304-
not_distinct_from(col("c1").eq(lit("inboxed")), lit(true))
4305-
.or(not_distinct_from(col("c1").eq(lit("scheduled")), lit(true)))
4306-
.or(not_distinct_from(col("c1").eq(lit("completed")), lit(true)))
4307-
.or(not_distinct_from(col("c1").eq(lit("paused")), lit(true)))
4308-
.or(not_distinct_from(col("c2"), lit(true)))
4309-
.or(not_distinct_from(
4310-
col("c1").eq(lit("invoked")).and(col("c3").gt(lit(0))),
4311-
lit(true)
4312-
))
4313-
);
4322+
Expr::Case(Case::new(
4323+
None,
4324+
vec![(Box::new(col("c2")), Box::new(fallible.clone()))],
4325+
Some(Box::new(lit(false))),
4326+
)),
4327+
Expr::Case(Case::new(
4328+
None,
4329+
vec![(Box::new(col("c2")), Box::new(lit(true)))],
4330+
Some(Box::new(fallible.clone())),
4331+
)),
4332+
Expr::Case(Case::new(
4333+
None,
4334+
vec![
4335+
(Box::new(col("c2")), Box::new(lit(false))),
4336+
(Box::new(fallible), Box::new(lit(true))),
4337+
],
4338+
Some(Box::new(lit(false))),
4339+
)),
4340+
Expr::Case(Case::new(
4341+
None,
4342+
vec![(Box::new(volatile.gt(lit(0_i16))), Box::new(lit(true)))],
4343+
Some(Box::new(col("c2"))),
4344+
)),
4345+
] {
4346+
assert_eq!(simplify(expr.clone()), expr);
4347+
}
43144348
}
43154349

43164350
#[test]
@@ -4354,19 +4388,20 @@ mod tests {
43544388

43554389
// CASE WHEN ISNULL(c2) THEN true ELSE c2
43564390
// -->
4357-
// ISNULL(c2) OR c2
4358-
//
4359-
// Need to call simplify 2x due to
4360-
// https://github.com/apache/datafusion/issues/1160
4391+
// Preserve CASE because the WHEN expression is outside the conservative
4392+
// column/literal subset.
4393+
let expected = Expr::Case(Case::new(
4394+
None,
4395+
vec![(Box::new(col("c2").is_null()), Box::new(lit(true)))],
4396+
Some(Box::new(col("c2"))),
4397+
));
43614398
assert_eq!(
43624399
simplify(simplify(Expr::Case(Case::new(
43634400
None,
43644401
vec![(Box::new(col("c2").is_null()), Box::new(lit(true)),)],
43654402
Some(Box::new(col("c2"))),
43664403
)))),
4367-
col("c2")
4368-
.is_null()
4369-
.or(col("c2").is_not_null().and(col("c2")))
4404+
expected
43704405
);
43714406

43724407
// CASE WHEN c1 then true WHEN c2 then false ELSE true
@@ -4407,29 +4442,21 @@ mod tests {
44074442
col("c1_non_null").or(col("c1_non_null").not().and(col("c2_non_null").not()))
44084443
);
44094444

4410-
// CASE WHEN c > 0 THEN true END AS c1
4411-
assert_eq!(
4412-
simplify(simplify(Expr::Case(Case::new(
4445+
// Preserve CASE with a non-leaf WHEN condition, with or without ELSE.
4446+
for expr in [
4447+
Expr::Case(Case::new(
44134448
None,
44144449
vec![(Box::new(col("c3").gt(lit(0_i64))), Box::new(lit(true)))],
44154450
None,
4416-
)))),
4417-
not_distinct_from(col("c3").gt(lit(0_i64)), lit(true)).or(distinct_from(
4418-
col("c3").gt(lit(0_i64)),
4419-
lit(true)
4420-
)
4421-
.and(lit_bool_null()))
4422-
);
4423-
4424-
// CASE WHEN c > 0 THEN true ELSE false END AS c1
4425-
assert_eq!(
4426-
simplify(simplify(Expr::Case(Case::new(
4451+
)),
4452+
Expr::Case(Case::new(
44274453
None,
44284454
vec![(Box::new(col("c3").gt(lit(0_i64))), Box::new(lit(true)))],
44294455
Some(Box::new(lit(false))),
4430-
)))),
4431-
not_distinct_from(col("c3").gt(lit(0_i64)), lit(true))
4432-
);
4456+
)),
4457+
] {
4458+
assert_eq!(simplify(simplify(expr.clone())), expr);
4459+
}
44334460
}
44344461

44354462
#[test]
@@ -4626,22 +4653,6 @@ mod tests {
46264653
assert_eq!(simplify(expr.clone()), expr);
46274654
}
46284655

4629-
fn distinct_from(left: impl Into<Expr>, right: impl Into<Expr>) -> Expr {
4630-
Expr::BinaryExpr(BinaryExpr {
4631-
left: Box::new(left.into()),
4632-
op: Operator::IsDistinctFrom,
4633-
right: Box::new(right.into()),
4634-
})
4635-
}
4636-
4637-
fn not_distinct_from(left: impl Into<Expr>, right: impl Into<Expr>) -> Expr {
4638-
Expr::BinaryExpr(BinaryExpr {
4639-
left: Box::new(left.into()),
4640-
op: Operator::IsNotDistinctFrom,
4641-
right: Box::new(right.into()),
4642-
})
4643-
}
4644-
46454656
#[test]
46464657
fn simplify_expr_bool_or() {
46474658
// col || true is always true

‎datafusion/sqllogictest/test_files/case.slt‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -906,3 +906,66 @@ drop table floats;
906906
#####
907907
# End of lookup table CASE tests
908908
#####
909+
910+
statement ok
911+
CREATE VIEW guarded_cast AS SELECT * FROM (VALUES ('1'), ('abc'), ('2'), (NULL)) t(s);
912+
913+
# A Boolean CASE must not become an AND that exposes the cast to 'abc'.
914+
# Regression for https://github.com/apache/datafusion/issues/25136.
915+
query T rowsort
916+
SELECT s FROM guarded_cast
917+
WHERE CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE false END;
918+
----
919+
1
920+
2
921+
922+
# Preserve branch evaluation in projections, including both forms of ELSE NULL.
923+
query TBBB rowsort
924+
SELECT s,
925+
CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE true END,
926+
CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE NULL END,
927+
CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 END
928+
FROM guarded_cast;
929+
----
930+
1 true true true
931+
2 true true true
932+
NULL true NULL NULL
933+
abc true NULL NULL
934+
935+
# The ELSE expression also runs only on rows that reach it.
936+
query T rowsort
937+
SELECT s FROM guarded_cast
938+
WHERE CASE WHEN s ~ '^[a-z]+$' THEN false ELSE CAST(s AS INT) > 0 END;
939+
----
940+
1
941+
2
942+
943+
# Literal outputs alone are insufficient: later WHEN expressions are conditional.
944+
query T rowsort
945+
SELECT s FROM guarded_cast
946+
WHERE CASE WHEN s ~ '^[a-z]+$' THEN false
947+
WHEN CAST(s AS INT) > 0 THEN true ELSE false END;
948+
----
949+
1
950+
2
951+
952+
# Division is another fallible branch expression, independent of cast handling.
953+
query I rowsort
954+
SELECT n FROM (VALUES (0), (1), (2), (NULL)) t(n)
955+
WHERE CASE WHEN n <> 0 THEN 10 / n > 1 ELSE false END;
956+
----
957+
1
958+
2
959+
960+
# A selected invalid branch must still raise its original error.
961+
query error Cannot cast string 'abc' to value of Int32 type
962+
SELECT CASE WHEN s ~ '^[a-z]+$' THEN CAST(s AS INT) > 0 ELSE false END
963+
FROM guarded_cast;
964+
965+
# The first WHEN must be evaluated even when both branch outputs are identical.
966+
query error Cannot cast string 'abc' to value of Int32 type
967+
SELECT CASE WHEN CAST(s AS INT) > 0 THEN false ELSE false END
968+
FROM (VALUES ('abc')) t(s);
969+
970+
statement ok
971+
DROP VIEW guarded_cast;

0 commit comments

Comments
 (0)