viirya opened a new issue, #24379:
URL: https://github.com/apache/datafusion/issues/24379
### Describe the bug
Expression simplification rewrites `col ~ '.*'` to `col IS NOT NULL`, which
loses SQL NULL semantics. For a NULL input, `NULL ~ '.*'` is `NULL`
(three-valued logic), but `NULL IS NOT NULL` is `false`. So in a projection
context the rewrite returns `false` where the correct answer is `NULL`.
The `!~` (`RegexNotMatch`) branch of the same rule is already NULL-aware
(`col IS NULL AND NULL`); only the `~` (`RegexMatch`) branch drops the NULL.
Same class as #24246, but a different rule (regex `.*`).
### To Reproduce
```sql
SELECT s, s ~ '.*' AS m
FROM (VALUES (CAST(NULL AS VARCHAR)), ('x')) t(s);
```
Actual:
```
+---+-------+
| s | m |
+---+-------+
| | false | <- wrong, should be NULL
| x | true |
+---+-------+
```
Expected (what NULL semantics require):
```
+---+------+
| s | m |
+---+------+
| | NULL |
| x | true |
+---+------+
```
### Expected behavior
`col ~ '.*'` is `true` for a non-NULL string and `NULL` for a NULL input,
matching `NestedLoopJoin`-free evaluation.
### Additional context
Root cause: `simplify_regex_expr` in
`datafusion/optimizer/src/simplify_expressions/regex.rs` rewrites the `~ '.*'`
case to `left.is_not_null()`. In a WHERE filter this is fine (both FALSE and
NULL reject the row, which is why existing SLT filter tests didn't catch it),
but it is wrong in a general/projection context. Fix + regression test coming.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
