Skip to content

Commit b90cb07

Browse files
viiryalukekim
authored andcommitted
fix: preserve NULL semantics when simplifying col ~ '.*' (apache#24380)
## Which issue does this PR close? - Closes apache#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. (cherry picked from commit 16b08db)
1 parent 7d9f743 commit b90cb07

3 files changed

Lines changed: 28 additions & 7 deletions

File tree

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

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ const ANY_CHAR_REGEX_PATTERN: &str = ".*";
3838
/// - full anchored regex patterns (e.g. `^foo$`) to `= 'foo'`
3939
/// - partial anchored regex patterns (e.g. `^foo`) to `LIKE 'foo%'`
4040
/// - combinations (alternatives) of the above, will be concatenated with `OR` or `AND`
41-
/// - `EQ .*` to NotNull
41+
/// - `EQ .*` to `col IS NOT NULL OR NULL` (true for any string, NULL if col is NULL)
4242
/// - `NE .*` to col IS NULL AND Boolean(NULL) (false for any string, or NULL if col is NULL)
4343
///
4444
/// 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(
7575
right: Box::new(null_bool),
7676
})
7777
} else {
78-
// not null
79-
left.is_not_null()
78+
// `col ~ '.*'` matches every non-NULL string and yields NULL for a
79+
// NULL input (three-valued logic). Mirror the `not` branch so the
80+
// NULL row is preserved (`NULL`) instead of collapsing to `false`.
81+
let null_bool = lit(ScalarValue::Boolean(None));
82+
left.is_not_null().or(null_bool)
8083
};
8184
return Ok(Transformed::yes(new_expr));
8285
}

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1013,15 +1013,15 @@ mod tests {
10131013
]);
10141014
let table_scan = table_scan(Some("test"), &schema, None)?.build()?;
10151015

1016-
// Test `~ ".*"` transforms to true for any non-NULL string
1016+
// Test `~ ".*"` is TRUE for any non-NULL string and NULL for a NULL input
10171017
let plan = LogicalPlanBuilder::from(table_scan.clone())
10181018
.filter(binary_expr(col("a"), Operator::RegexMatch, lit(".*")))?
10191019
.build()?;
10201020

10211021
assert_optimized_plan_equal!(
10221022
plan,
10231023
@ r"
1024-
Filter: test.a IS NOT NULL
1024+
Filter: test.a IS NOT NULL OR Boolean(NULL)
10251025
TableScan: test
10261026
"
10271027
)?;

‎datafusion/sqllogictest/test_files/simplify_expr.slt‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -34,10 +34,10 @@ query TT
3434
explain select b from t where b ~ '.*'
3535
----
3636
logical_plan
37-
01)Filter: t.b IS NOT NULL
37+
01)Filter: t.b IS NOT NULL OR Boolean(NULL)
3838
02)--TableScan: t projection=[b]
3939
physical_plan
40-
01)FilterExec: b@0 IS NOT NULL
40+
01)FilterExec: b@0 IS NOT NULL OR NULL
4141
02)--DataSourceExec: partitions=1, partition_sizes=[1]
4242

4343
query TT
@@ -50,6 +50,24 @@ physical_plan
5050
01)FilterExec: b@0 IS NULL AND NULL
5151
02)--DataSourceExec: partitions=1, partition_sizes=[1]
5252

53+
# `col ~ '.*'` is TRUE for any non-NULL string and NULL for a NULL input.
54+
# The `.*` -> IS NOT NULL simplification must preserve that NULL (returning
55+
# `false` for the NULL row would be wrong in a projection context).
56+
query TB
57+
WITH vals(id, col) AS (
58+
VALUES
59+
(1, 'foo'::text),
60+
(2, ''::text),
61+
(3, NULL::text)
62+
)
63+
SELECT col, col ~ '.*'
64+
FROM vals
65+
ORDER BY id
66+
----
67+
foo true
68+
(empty) true
69+
NULL NULL
70+
5371
query TB
5472
WITH vals(id, col) AS (
5573
VALUES

0 commit comments

Comments
 (0)