This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 16b08db00d fix: preserve NULL semantics when simplifying col ~ '.*'
(#24380)
16b08db00d is described below
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]
