This is an automated email from the ASF dual-hosted git repository. github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-24380-d3c47b4a80baa3ca941ca0fc2cc80f96687c5a27 in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 16b08db00ddf25f478f62292f149f378dba47c1f Author: Liang-Chi Hsieh <[email protected]> AuthorDate: Sat Aug 15 16:10:37 2026 +0000 fix: preserve NULL semantics when simplifying col ~ '.*' (#24380) ## Which issue does this PR close? - Closes #24379. ## Rationale for this change `simplify_regex_expr` rewrites `col ~ '.*'` to `col IS NOT NULL`. For a NULL input that returns `false`, but `NULL ~ '.*'` is `NULL` under three-valued logic — so the rewrite produces wrong results in a projection context: ```sql SELECT s, s ~ '.*' FROM (VALUES (CAST(NULL AS VARCHAR)), ('x')) t(s); -- NULL row currently returns `false`; it should be NULL ``` The `!~` (`RegexNotMatch`) branch of the same rule is already NULL-aware (`col IS NULL AND NULL`); only the `~` branch dropped the NULL. ## What changes are included in this PR? - Rewrite `col ~ '.*'` to `col IS NOT NULL OR NULL` — `true` for a non-NULL string, `NULL` for a NULL input. - In a WHERE filter both FALSE and NULL reject the row, so filter *results* are unchanged; only the plan text and projection-context values differ. Existing filter-plan expectations in `simplify_expr.slt` and the `test_simplify_regex_special_cases` unit test are updated accordingly, and a projection regression test is added. ## Are these changes tested? Yes. - New projection regression test in `simplify_expr.slt` asserting `col ~ '.*'` returns `true`/`true`/`NULL` for `'foo'`/`''`/`NULL`. - Updated the two filter-context plan expectations (logical + physical) that previously encoded the `IS NOT NULL` rewrite. - `simplify_expr.slt`, the `regexp/*` SLTs, and the optimizer simplify unit tests all pass. ## Are there any user-facing changes? `col ~ '.*'` in a projection now returns `NULL` for a NULL input instead of `false`, matching SQL semantics. No API changes. --- .../optimizer/src/simplify_expressions/regex.rs | 9 ++++++--- .../src/simplify_expressions/simplify_exprs.rs | 4 ++-- .../sqllogictest/test_files/simplify_expr.slt | 22 ++++++++++++++++++++-- 3 files changed, 28 insertions(+), 7 deletions(-) diff --git a/datafusion/optimizer/src/simplify_expressions/regex.rs b/datafusion/optimizer/src/simplify_expressions/regex.rs index f04d9476c4..7dccb5b1b9 100644 --- a/datafusion/optimizer/src/simplify_expressions/regex.rs +++ b/datafusion/optimizer/src/simplify_expressions/regex.rs @@ -38,7 +38,7 @@ const ANY_CHAR_REGEX_PATTERN: &str = ".*"; /// - full anchored regex patterns (e.g. `^foo$`) to `= 'foo'` /// - partial anchored regex patterns (e.g. `^foo`) to `LIKE 'foo%'` /// - combinations (alternatives) of the above, will be concatenated with `OR` or `AND` -/// - `EQ .*` to NotNull +/// - `EQ .*` to `col IS NOT NULL OR NULL` (true for any string, NULL if col is NULL) /// - `NE .*` to col IS NULL AND Boolean(NULL) (false for any string, or NULL if col is NULL) /// /// Dev note: unit tests of this function are in `expr_simplifier.rs`, case `test_simplify_regex`. @@ -75,8 +75,11 @@ pub fn simplify_regex_expr( right: Box::new(null_bool), }) } else { - // not null - left.is_not_null() + // `col ~ '.*'` matches every non-NULL string and yields NULL for a + // NULL input (three-valued logic). Mirror the `not` branch so the + // NULL row is preserved (`NULL`) instead of collapsing to `false`. + let null_bool = lit(ScalarValue::Boolean(None)); + left.is_not_null().or(null_bool) }; return Ok(Transformed::yes(new_expr)); } diff --git a/datafusion/optimizer/src/simplify_expressions/simplify_exprs.rs b/datafusion/optimizer/src/simplify_expressions/simplify_exprs.rs index 0e72a17abc..1c5a4a1869 100644 --- a/datafusion/optimizer/src/simplify_expressions/simplify_exprs.rs +++ b/datafusion/optimizer/src/simplify_expressions/simplify_exprs.rs @@ -1026,7 +1026,7 @@ mod tests { ]); let table_scan = table_scan(Some("test"), &schema, None)?.build()?; - // Test `~ ".*"` transforms to true for any non-NULL string + // Test `~ ".*"` is TRUE for any non-NULL string and NULL for a NULL input let plan = LogicalPlanBuilder::from(table_scan.clone()) .filter(binary_expr(col("a"), Operator::RegexMatch, lit(".*")))? .build()?; @@ -1034,7 +1034,7 @@ mod tests { assert_optimized_plan_equal!( plan, @ r" - Filter: test.a IS NOT NULL + Filter: test.a IS NOT NULL OR Boolean(NULL) TableScan: test " )?; diff --git a/datafusion/sqllogictest/test_files/simplify_expr.slt b/datafusion/sqllogictest/test_files/simplify_expr.slt index 57dc440407..158096328e 100644 --- a/datafusion/sqllogictest/test_files/simplify_expr.slt +++ b/datafusion/sqllogictest/test_files/simplify_expr.slt @@ -34,10 +34,10 @@ query TT explain select b from t where b ~ '.*' ---- logical_plan -01)Filter: t.b IS NOT NULL +01)Filter: t.b IS NOT NULL OR Boolean(NULL) 02)--TableScan: t projection=[b] physical_plan -01)FilterExec: b@0 IS NOT NULL +01)FilterExec: b@0 IS NOT NULL OR NULL 02)--DataSourceExec: partitions=1, partition_sizes=[1] query TT @@ -50,6 +50,24 @@ physical_plan 01)FilterExec: b@0 IS NULL AND NULL 02)--DataSourceExec: partitions=1, partition_sizes=[1] +# `col ~ '.*'` is TRUE for any non-NULL string and NULL for a NULL input. +# The `.*` -> IS NOT NULL simplification must preserve that NULL (returning +# `false` for the NULL row would be wrong in a projection context). +query TB +WITH vals(id, col) AS ( + VALUES + (1, 'foo'::text), + (2, ''::text), + (3, NULL::text) +) +SELECT col, col ~ '.*' +FROM vals +ORDER BY id +---- +foo true +(empty) true +NULL NULL + query TB WITH vals(id, col) AS ( VALUES --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
