Repository navigation
Commit 83be77c
authored
fix: translate SQL wildcards in SIMILAR TO patterns (apache#22263) (apache#23188)
`SIMILAR TO` previously passed the pattern straight to Arrow's regex
engine, so SQL wildcards were never translated and matches were
unanchored:
SELECT 'abc' SIMILAR TO 'a%'; -- returned false
SELECT 'x' SIMILAR TO '_'; -- returned false
Translate `%` to `(?s:.*)` and `_` to `(?s:.)` (dot-all so they match
newlines), then wrap the pattern in `^(?:...)$` so the regex matches the
entire string. `.`, `^`, `$`, and `\` are escaped as SQL literals. The
POSIX metacharacters that `SIMILAR TO` defines (`| * + ? ( ) { } [ ]`)
pass through to the regex unchanged.
## Which issue does this PR close?
- Closes apache#22263.
## Rationale for this change
`SIMILAR TO` is a SQL standard operator with well-defined wildcard
semantics (`%` = any sequence, `_` = single character, full-string
match). DataFusion's previous behavior silently produced wrong results
for the most basic patterns, which is a correctness bug for anyone
porting queries from Postgres or other SQL engines.
Supporting non-literal patterns requires a new physical expression, so
that expression also needs protobuf support — otherwise serialized
physical plans containing a dynamic `SIMILAR TO` would regress for
distributed engines built on `datafusion-proto`.
Accepting `LargeUtf8` and `Utf8View` patterns in turn requires type
coercion. `Expr::SimilarTo` was previously in the analyzer's no-op list,
so nothing reconciled the value type with the pattern type. Since the
regex kernel dispatches on the left-hand type and then downcasts the
pattern to that same array type, any mismatch aborted the query with a
`failed to downcast array` panic.
## What changes are included in this PR?
**Pattern translation**
- Added a `sql_similar_to_regex` helper that translates `%`/`_` and
anchors the pattern with `^(?:...)$`. It tracks bracket state so `^`
inside `[...]` is treated as bracket negation, not as a literal, and so
SQL wildcards lose their special meaning inside a bracket expression.
- Added a new `SqlSimilarToPattern` physical expression in
`datafusion/physical-expr/src/expressions/similar_to_pattern.rs` that
applies the translation at runtime for non-literal patterns.
- `similar_to()` now translates literal `Utf8` / `LargeUtf8` /
`Utf8View` patterns at planning time and wraps non-literal patterns in
`SqlSimilarToPattern` for runtime translation.
- NULL patterns pass through and return `NULL` instead of crashing with
an internal error. The translation preserves the pattern's string
variant, including for NULL, so the pattern type always matches the
value type.
- Restored a plan-time type check in `datafusion/sql/src/expr/mod.rs`
that rejects non-string patterns with a clean `plan_err!`.
- Runtime type errors in `SqlSimilarToPattern` are reported as
`exec_err!` rather than `internal_err!`.
**Type coercion**
- Added an `Expr::SimilarTo` arm to
`datafusion/optimizer/src/analyzer/type_coercion.rs` and removed
`Expr::SimilarTo` from the no-op list. It uses `regex_coercion`, the
same coercion `Operator::RegexMatch` already uses, since that is what
`SIMILAR TO` lowers to. Mismatched string types are now cast to a common
type instead of reaching the kernel and panicking.
**Protobuf support**
- Added a `PhysicalSqlSimilarToPatternNode` message and wired it into
`PhysicalExprNode.expr_type` as field 24.
- Implemented `try_to_proto` / `try_from_proto` for
`SqlSimilarToPattern` and added the decode arm in
`datafusion/proto/src/physical_plan/from_proto.rs`.
- Regenerated `prost.rs` / `pbjson.rs` via
`datafusion/proto-models/regen.sh`.
## Are these changes tested?
Yes.
Pattern semantics
(`datafusion/physical-expr/src/expressions/binary.rs`):
- `test_similar_to_sql_literal_metachars` confirms that `.`, `^`, `$`,
and `\` are treated as SQL literals, not as regex operators.
- `test_similar_to_posix_metachars` confirms that `|, *, +, ?, (, ), {,
}, [, ], [^...]`, and `[a-z]` behave as `SIMILAR TO` metacharacters.
- `test_similar_to_wildcards_match_newlines` confirms that `%` and `_`
match newlines.
- `test_similar_to` covers basic `%`/`_` semantics, full-string
anchoring, and case sensitivity.
- `test_similar_to_dynamic_pattern` covers column-based patterns.
- `test_similar_to_null_pattern` and
`test_similar_to_non_literal_pattern_errors` cover the NULL pattern and
non-string literal pattern paths.
Translation and coercion:
- `test_translate_scalar` / `test_translate_array` cover the translation
itself, including that each string variant (and its NULL) is preserved.
- `similar_to_for_type_coercion` in `type_coercion.rs` covers matching
types, mismatched string types, a NULL pattern cast to the value's type,
and the no-common-type error.
Protobuf (`similar_to_pattern.rs` `proto_tests`, mirroring the existing
`LikeExpr` tests):
- Encoding, decoding, rejection of a non-`SqlSimilarToPattern` node,
rejection of a missing `expr` field, and error propagation on both the
encode and decode paths.
- `roundtrip_sql_similar_to_pattern` in
`datafusion/proto/tests/cases/roundtrip_physical_plan.rs` covers the
full plan roundtrip.
End-to-end (`datafusion/sqllogictest/test_files/strings.slt`):
- The pre-existing `SIMILAR TO 'p[12].*'` / `NOT SIMILAR TO 'p[12].*'`
cases (which only worked because of the bug) were replaced with
equivalent cases using standard wildcard syntax.
- New cases cover literal metachars, POSIX metachars, newline matching,
dynamic column patterns, dynamic function patterns, `SELECT 'a' SIMILAR
TO NULL`, rejection of `SELECT 'a' SIMILAR TO 1`, mixed `Utf8` /
`LargeUtf8` / `Utf8View` operands, and dictionary-encoded values with
both literal and dynamic patterns.
## Are there any user-facing changes?
Yes:
- `SIMILAR TO` now produces correct results for queries that were
previously returning wrong answers.
- Queries that relied on the buggy behavior (e.g., treating `.`, `^`, or
`$` as regex metacharacters) now follow standard SQL `SIMILAR TO`
semantics.
- Queries using valid `SIMILAR TO` POSIX metacharacters (`| * + ? ( ) {
} [ ]`) now work as expected.
- `%` and `_` wildcards now work.
- Non-literal patterns (e.g., column patterns) now work instead of
returning a `not_impl_err!`, and physical plans containing them can be
serialized with `datafusion-proto`.
- `LargeUtf8` and `Utf8View` patterns are accepted, and mixing string
types between the value and the pattern is coerced rather than failing.
- Dictionary-encoded values are coerced to their value type and work
with both literal and dynamic patterns.
- `SELECT ... SIMILAR TO NULL` now returns `NULL` instead of crashing.
- `SELECT ... SIMILAR TO <non-string>` now returns a clean plan error
instead of an internal error.
`\` is still treated as a literal backslash rather than as the SQL
default escape character, and an explicit `ESCAPE` clause is still
rejected. Escape support is left as a follow-up.1 parent 710e74e commit 83be77c
12 files changed
Lines changed: 1081 additions & 32 deletions
File tree
- datafusion
- physical-expr/src/expressions
- proto-models
- proto
- src/generated
- proto
- src/physical_plan
- tests/cases/plans
- sqllogictest/test_files
- docs/source/library-user-guide/upgrading
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
18 | 18 | | |
19 | 19 | | |
20 | 20 | | |
| 21 | + | |
| 22 | + | |
21 | 23 | | |
22 | 24 | | |
23 | 25 | | |
| |||
1371 | 1373 | | |
1372 | 1374 | | |
1373 | 1375 | | |
1374 | | - | |
| 1376 | + | |
| 1377 | + | |
| 1378 | + | |
| 1379 | + | |
| 1380 | + | |
| 1381 | + | |
| 1382 | + | |
| 1383 | + | |
| 1384 | + | |
| 1385 | + | |
| 1386 | + | |
| 1387 | + | |
| 1388 | + | |
1375 | 1389 | | |
1376 | 1390 | | |
1377 | 1391 | | |
| |||
5327 | 5341 | | |
5328 | 5342 | | |
5329 | 5343 | | |
5330 | | - | |
5331 | 5344 | | |
5332 | 5345 | | |
5333 | 5346 | | |
5334 | | - | |
| 5347 | + | |
5335 | 5348 | | |
5336 | 5349 | | |
5337 | 5350 | | |
5338 | 5351 | | |
5339 | 5352 | | |
5340 | | - | |
5341 | | - | |
5342 | | - | |
5343 | | - | |
5344 | | - | |
5345 | | - | |
5346 | | - | |
5347 | | - | |
5348 | | - | |
| 5353 | + | |
| 5354 | + | |
5349 | 5355 | | |
5350 | 5356 | | |
5351 | 5357 | | |
| |||
5357 | 5363 | | |
5358 | 5364 | | |
5359 | 5365 | | |
5360 | | - | |
5361 | | - | |
5362 | | - | |
5363 | | - | |
| 5366 | + | |
5364 | 5367 | | |
| 5368 | + | |
5365 | 5369 | | |
5366 | | - | |
5367 | 5370 | | |
5368 | 5371 | | |
5369 | 5372 | | |
5370 | | - | |
| 5373 | + | |
5371 | 5374 | | |
5372 | 5375 | | |
5373 | 5376 | | |
5374 | 5377 | | |
5375 | 5378 | | |
5376 | | - | |
| 5379 | + | |
| 5380 | + | |
| 5381 | + | |
5377 | 5382 | | |
5378 | 5383 | | |
5379 | 5384 | | |
5380 | | - | |
| 5385 | + | |
5381 | 5386 | | |
5382 | 5387 | | |
5383 | 5388 | | |
5384 | 5389 | | |
5385 | 5390 | | |
| 5391 | + | |
| 5392 | + | |
| 5393 | + | |
| 5394 | + | |
| 5395 | + | |
| 5396 | + | |
| 5397 | + | |
| 5398 | + | |
| 5399 | + | |
| 5400 | + | |
| 5401 | + | |
| 5402 | + | |
| 5403 | + | |
| 5404 | + | |
| 5405 | + | |
| 5406 | + | |
| 5407 | + | |
| 5408 | + | |
| 5409 | + | |
| 5410 | + | |
| 5411 | + | |
| 5412 | + | |
| 5413 | + | |
| 5414 | + | |
| 5415 | + | |
| 5416 | + | |
| 5417 | + | |
| 5418 | + | |
| 5419 | + | |
| 5420 | + | |
| 5421 | + | |
| 5422 | + | |
| 5423 | + | |
| 5424 | + | |
| 5425 | + | |
| 5426 | + | |
| 5427 | + | |
| 5428 | + | |
| 5429 | + | |
| 5430 | + | |
| 5431 | + | |
| 5432 | + | |
| 5433 | + | |
| 5434 | + | |
| 5435 | + | |
| 5436 | + | |
| 5437 | + | |
| 5438 | + | |
| 5439 | + | |
| 5440 | + | |
| 5441 | + | |
| 5442 | + | |
| 5443 | + | |
| 5444 | + | |
| 5445 | + | |
| 5446 | + | |
| 5447 | + | |
| 5448 | + | |
| 5449 | + | |
| 5450 | + | |
| 5451 | + | |
| 5452 | + | |
| 5453 | + | |
| 5454 | + | |
| 5455 | + | |
| 5456 | + | |
| 5457 | + | |
| 5458 | + | |
| 5459 | + | |
| 5460 | + | |
| 5461 | + | |
| 5462 | + | |
| 5463 | + | |
| 5464 | + | |
| 5465 | + | |
| 5466 | + | |
| 5467 | + | |
| 5468 | + | |
| 5469 | + | |
| 5470 | + | |
| 5471 | + | |
| 5472 | + | |
| 5473 | + | |
| 5474 | + | |
| 5475 | + | |
| 5476 | + | |
| 5477 | + | |
| 5478 | + | |
| 5479 | + | |
| 5480 | + | |
| 5481 | + | |
| 5482 | + | |
| 5483 | + | |
| 5484 | + | |
| 5485 | + | |
| 5486 | + | |
| 5487 | + | |
| 5488 | + | |
| 5489 | + | |
| 5490 | + | |
| 5491 | + | |
| 5492 | + | |
| 5493 | + | |
| 5494 | + | |
| 5495 | + | |
| 5496 | + | |
| 5497 | + | |
| 5498 | + | |
| 5499 | + | |
| 5500 | + | |
| 5501 | + | |
| 5502 | + | |
| 5503 | + | |
| 5504 | + | |
| 5505 | + | |
| 5506 | + | |
| 5507 | + | |
| 5508 | + | |
| 5509 | + | |
| 5510 | + | |
| 5511 | + | |
| 5512 | + | |
| 5513 | + | |
| 5514 | + | |
| 5515 | + | |
| 5516 | + | |
| 5517 | + | |
| 5518 | + | |
| 5519 | + | |
| 5520 | + | |
| 5521 | + | |
| 5522 | + | |
| 5523 | + | |
| 5524 | + | |
| 5525 | + | |
| 5526 | + | |
| 5527 | + | |
| 5528 | + | |
| 5529 | + | |
| 5530 | + | |
| 5531 | + | |
| 5532 | + | |
| 5533 | + | |
| 5534 | + | |
| 5535 | + | |
| 5536 | + | |
| 5537 | + | |
| 5538 | + | |
| 5539 | + | |
| 5540 | + | |
| 5541 | + | |
| 5542 | + | |
| 5543 | + | |
| 5544 | + | |
| 5545 | + | |
| 5546 | + | |
| 5547 | + | |
| 5548 | + | |
| 5549 | + | |
| 5550 | + | |
| 5551 | + | |
| 5552 | + | |
| 5553 | + | |
| 5554 | + | |
| 5555 | + | |
| 5556 | + | |
| 5557 | + | |
| 5558 | + | |
| 5559 | + | |
| 5560 | + | |
| 5561 | + | |
| 5562 | + | |
| 5563 | + | |
| 5564 | + | |
| 5565 | + | |
| 5566 | + | |
| 5567 | + | |
| 5568 | + | |
| 5569 | + | |
| 5570 | + | |
| 5571 | + | |
| 5572 | + | |
| 5573 | + | |
| 5574 | + | |
| 5575 | + | |
| 5576 | + | |
| 5577 | + | |
| 5578 | + | |
| 5579 | + | |
| 5580 | + | |
| 5581 | + | |
| 5582 | + | |
| 5583 | + | |
| 5584 | + | |
| 5585 | + | |
| 5586 | + | |
| 5587 | + | |
| 5588 | + | |
| 5589 | + | |
| 5590 | + | |
| 5591 | + | |
| 5592 | + | |
| 5593 | + | |
| 5594 | + | |
| 5595 | + | |
| 5596 | + | |
5386 | 5597 | | |
5387 | 5598 | | |
5388 | 5599 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
33 | 33 | | |
34 | 34 | | |
35 | 35 | | |
| 36 | + | |
36 | 37 | | |
37 | 38 | | |
38 | 39 | | |
| |||
58 | 59 | | |
59 | 60 | | |
60 | 61 | | |
| 62 | + | |
| 63 | + | |
61 | 64 | | |
62 | 65 | | |
63 | 66 | | |
| |||
0 commit comments