Skip to content

Commit 83be77c

Browse files
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/binary.rs‎

Lines changed: 231 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,8 @@
1818
mod kernels;
1919

2020
use crate::PhysicalExpr;
21+
use crate::expressions::SqlSimilarToPattern;
22+
use crate::expressions::translate_scalar;
2123
use crate::intervals::cp_solver::{propagate_arithmetic, propagate_comparison};
2224
use std::cmp::Ordering;
2325
use std::hash::Hash;
@@ -1371,7 +1373,19 @@ pub fn similar_to(
13711373
(true, false) => Operator::RegexNotMatch,
13721374
(true, true) => Operator::RegexNotIMatch,
13731375
};
1374-
Ok(Arc::new(BinaryExpr::new(expr, binary_op, pattern)))
1376+
1377+
let translated_pattern = match pattern.downcast_ref::<crate::expressions::Literal>() {
1378+
Some(literal) => Arc::new(crate::expressions::Literal::new(translate_scalar(
1379+
literal.value(),
1380+
)?)) as Arc<dyn PhysicalExpr>,
1381+
None => Arc::new(SqlSimilarToPattern::new(pattern)) as Arc<dyn PhysicalExpr>,
1382+
};
1383+
1384+
Ok(Arc::new(BinaryExpr::new(
1385+
expr,
1386+
binary_op,
1387+
translated_pattern,
1388+
)))
13751389
}
13761390

13771391
#[cfg(test)]
@@ -5327,25 +5341,17 @@ mod tests {
53275341
Ok(())
53285342
}
53295343

5330-
/// Test helper for SIMILAR TO binary operation
53315344
fn apply_similar_to(
53325345
schema: &SchemaRef,
53335346
va: Vec<&str>,
5334-
vb: Vec<&str>,
5347+
pattern: &str,
53355348
negated: bool,
53365349
case_insensitive: bool,
53375350
expected: &BooleanArray,
53385351
) -> Result<()> {
53395352
let a = StringArray::from(va);
5340-
let b = StringArray::from(vb);
5341-
let op = similar_to(
5342-
negated,
5343-
case_insensitive,
5344-
col("a", schema)?,
5345-
col("b", schema)?,
5346-
)?;
5347-
let batch =
5348-
RecordBatch::try_new(Arc::clone(schema), vec![Arc::new(a), Arc::new(b)])?;
5353+
let op = similar_to(negated, case_insensitive, col("a", schema)?, lit(pattern))?;
5354+
let batch = RecordBatch::try_new(Arc::clone(schema), vec![Arc::new(a)])?;
53495355
let result = op
53505356
.evaluate(&batch)?
53515357
.into_array(batch.num_rows())
@@ -5357,32 +5363,237 @@ mod tests {
53575363

53585364
#[test]
53595365
fn test_similar_to() {
5360-
let schema = Arc::new(Schema::new(vec![
5361-
Field::new("a", DataType::Utf8, false),
5362-
Field::new("b", DataType::Utf8, false),
5363-
]));
5366+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
53645367

5368+
// `%` matches any sequence; case-sensitive
53655369
let expected = [Some(true), Some(false)].iter().collect();
5366-
// case-sensitive
53675370
apply_similar_to(
53685371
&schema,
53695372
vec!["hello world", "Hello World"],
5370-
vec!["hello.*", "hello.*"],
5373+
"hello%",
53715374
false,
53725375
false,
53735376
&expected,
53745377
)
53755378
.unwrap();
5376-
// case-insensitive
5379+
5380+
// `%` matches any sequence; case-insensitive
5381+
let expected = [Some(true), Some(false)].iter().collect();
53775382
apply_similar_to(
53785383
&schema,
53795384
vec!["hello world", "bye"],
5380-
vec!["hello.*", "hello.*"],
5385+
"hello%",
53815386
false,
53825387
true,
53835388
&expected,
53845389
)
53855390
.unwrap();
5391+
5392+
// `_` matches exactly one character
5393+
let expected = [Some(true), Some(false), Some(false)].iter().collect();
5394+
apply_similar_to(&schema, vec!["x", "xy", ""], "_", false, false, &expected)
5395+
.unwrap();
5396+
5397+
// Match must cover the entire string (no implicit substring match)
5398+
let expected = [Some(false), Some(true)].iter().collect();
5399+
apply_similar_to(&schema, vec!["abc", "a"], "a", false, false, &expected)
5400+
.unwrap();
5401+
5402+
// `%` matches zero or more, so the empty string matches.
5403+
let expected = [Some(true), Some(true)].iter().collect();
5404+
apply_similar_to(&schema, vec!["", "anything"], "%", false, false, &expected)
5405+
.unwrap();
5406+
5407+
// `_` requires exactly one character, so the empty string does not
5408+
// match.
5409+
let expected = [Some(false), Some(true)].iter().collect();
5410+
apply_similar_to(&schema, vec!["", "x"], "_", false, false, &expected).unwrap();
5411+
5412+
// `%` at the start of the pattern is still anchored: the string
5413+
// must end where the trailing literal begins.
5414+
let expected = [Some(true), Some(false)].iter().collect();
5415+
apply_similar_to(&schema, vec!["abc", "abd"], "%c", false, false, &expected)
5416+
.unwrap();
5417+
5418+
// `%` and `_` together: `%` matches zero or more (including the
5419+
// empty string), `_` matches exactly one character.
5420+
let expected = [Some(true), Some(true)].iter().collect();
5421+
apply_similar_to(&schema, vec!["a", "abc"], "a%", false, false, &expected)
5422+
.unwrap();
5423+
let expected = [Some(true), Some(false)].iter().collect();
5424+
apply_similar_to(&schema, vec!["axb", "abc"], "a_b", false, false, &expected)
5425+
.unwrap();
5426+
}
5427+
5428+
// Regression: regex metacharacters that are NOT SIMILAR TO metacharacters
5429+
// (`. ^ $ \`) must be treated as SQL literals. Without escaping, `a.`
5430+
// would match any `a` followed by any character (`ab`, `a1`, ...).
5431+
#[test]
5432+
fn test_similar_to_sql_literal_metachars() {
5433+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
5434+
5435+
// `.` is a literal, not the regex "any character" operator.
5436+
let expected = [Some(true), Some(false), Some(false)].iter().collect();
5437+
apply_similar_to(
5438+
&schema,
5439+
vec!["a.", "ab", "a"],
5440+
"a.",
5441+
false,
5442+
false,
5443+
&expected,
5444+
)
5445+
.unwrap();
5446+
5447+
// `^` and `$` are literals and only match the literal `^` and `$`.
5448+
let expected = [Some(true), Some(false)].iter().collect();
5449+
apply_similar_to(&schema, vec!["^x$", "x"], r"^x$", false, false, &expected)
5450+
.unwrap();
5451+
5452+
// `\` is a literal backslash (we don't support the ESCAPE clause).
5453+
let expected = [Some(true), Some(false)].iter().collect();
5454+
apply_similar_to(&schema, vec![r"a\b", "ab"], r"a\b", false, false, &expected)
5455+
.unwrap();
5456+
}
5457+
5458+
// SIMILAR TO borrows POSIX metacharacters from regular expressions:
5459+
// `| * + ? ( ) { } [ ]`. The translator passes them through to the
5460+
// underlying regex engine.
5461+
#[test]
5462+
fn test_similar_to_posix_metachars() {
5463+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
5464+
5465+
// `|` alternation.
5466+
let expected = [Some(true), Some(false), Some(true)].iter().collect();
5467+
apply_similar_to(&schema, vec!["a", "c", "b"], "a|b", false, false, &expected)
5468+
.unwrap();
5469+
5470+
// `*` zero or more.
5471+
let expected = [Some(true), Some(true), Some(false)].iter().collect();
5472+
apply_similar_to(&schema, vec!["", "aa", "ab"], "a*", false, false, &expected)
5473+
.unwrap();
5474+
5475+
// `+` one or more.
5476+
let expected = [Some(false), Some(true)].iter().collect();
5477+
apply_similar_to(&schema, vec!["", "aa"], "a+", false, false, &expected).unwrap();
5478+
5479+
// `?` zero or one.
5480+
let expected = [Some(true), Some(true), Some(false)].iter().collect();
5481+
apply_similar_to(&schema, vec!["", "a", "aa"], "a?", false, false, &expected)
5482+
.unwrap();
5483+
5484+
// `()` grouping.
5485+
let expected = [Some(true), Some(true), Some(false)].iter().collect();
5486+
apply_similar_to(
5487+
&schema,
5488+
vec!["ab", "abc", "ac"],
5489+
"(ab)c?",
5490+
false,
5491+
false,
5492+
&expected,
5493+
)
5494+
.unwrap();
5495+
5496+
// `{m}` exact count.
5497+
let expected = [Some(true), Some(false)].iter().collect();
5498+
apply_similar_to(&schema, vec!["aaa", "aa"], "a{3}", false, false, &expected)
5499+
.unwrap();
5500+
5501+
// `[...]` character class.
5502+
let expected = [Some(true), Some(false)].iter().collect();
5503+
apply_similar_to(&schema, vec!["a", "c"], "[ab]", false, false, &expected)
5504+
.unwrap();
5505+
5506+
// `[^...]` negated character class.
5507+
let expected = [Some(true), Some(false)].iter().collect();
5508+
apply_similar_to(&schema, vec!["c", "a"], "[^ab]", false, false, &expected)
5509+
.unwrap();
5510+
5511+
// `[a-z]` range inside a character class.
5512+
let expected = [Some(true), Some(false)].iter().collect();
5513+
apply_similar_to(&schema, vec!["m", "1"], "[a-z]", false, false, &expected)
5514+
.unwrap();
5515+
}
5516+
5517+
// Regression: `%` and `_` must match newlines, matching SQL semantics
5518+
// where these wildcards match "any character".
5519+
#[test]
5520+
fn test_similar_to_wildcards_match_newlines() {
5521+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
5522+
5523+
// `%` crosses a newline. (`%` also matches zero characters, so `ab`
5524+
// matches `a%b` as well.)
5525+
let expected = [Some(true), Some(true)].iter().collect();
5526+
apply_similar_to(&schema, vec!["a\nb", "ab"], "a%b", false, false, &expected)
5527+
.unwrap();
5528+
5529+
// `_` matches a single newline. (`_` requires exactly one character,
5530+
// so `ab` does not match `a_b`.)
5531+
let expected = [Some(true), Some(false)].iter().collect();
5532+
apply_similar_to(&schema, vec!["a\nb", "ab"], "a_b", false, false, &expected)
5533+
.unwrap();
5534+
}
5535+
5536+
#[test]
5537+
fn test_similar_to_non_literal_pattern_errors() {
5538+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
5539+
// Non-string literal patterns still error.
5540+
let err = similar_to(false, false, col("a", &schema).unwrap(), lit(1i32))
5541+
.expect_err("non-string literal pattern should error");
5542+
assert!(
5543+
err.to_string()
5544+
.contains("SIMILAR TO pattern must be a string type, got Int32(1)"),
5545+
"unexpected error message: {err}"
5546+
);
5547+
}
5548+
5549+
#[test]
5550+
fn test_similar_to_dynamic_pattern() {
5551+
let schema = Arc::new(Schema::new(vec![
5552+
Field::new("text", DataType::Utf8, false),
5553+
Field::new("pattern", DataType::Utf8, false),
5554+
]));
5555+
let text = StringArray::from(vec!["abc", "ab", "x"]);
5556+
let pattern = StringArray::from(vec!["a%", "a.", "_"]);
5557+
let batch = RecordBatch::try_new(
5558+
Arc::clone(&schema),
5559+
vec![Arc::new(text), Arc::new(pattern)],
5560+
)
5561+
.unwrap();
5562+
5563+
let op = similar_to(
5564+
false,
5565+
false,
5566+
col("text", &schema).unwrap(),
5567+
col("pattern", &schema).unwrap(),
5568+
)
5569+
.unwrap();
5570+
let result = op.evaluate(&batch).unwrap();
5571+
let result_array = result.into_array(batch.num_rows()).unwrap();
5572+
let result = as_boolean_array(&result_array).unwrap();
5573+
assert!(result.value(0)); // "abc" ~ ^(?:a(?s:.*))$
5574+
assert!(!result.value(1)); // "ab" ~ ^(?:a\.)$
5575+
assert!(result.value(2)); // "x" ~ ^(?:(?s:.))$
5576+
}
5577+
5578+
#[test]
5579+
fn test_similar_to_null_pattern() {
5580+
let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
5581+
let a = StringArray::from(vec!["hello"]);
5582+
let op = similar_to(
5583+
false,
5584+
false,
5585+
col("a", &schema).unwrap(),
5586+
lit(ScalarValue::Utf8(None)),
5587+
)
5588+
.unwrap();
5589+
let batch = RecordBatch::try_new(Arc::clone(&schema), vec![Arc::new(a)]).unwrap();
5590+
let result = op
5591+
.evaluate(&batch)
5592+
.unwrap()
5593+
.into_array(batch.num_rows())
5594+
.unwrap();
5595+
let expected: BooleanArray = [None].iter().collect();
5596+
assert_eq!(result.as_ref(), &expected);
53865597
}
53875598

53885599
pub fn binary_expr(

‎datafusion/physical-expr/src/expressions/mod.rs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ mod literal;
3333
mod negative;
3434
mod no_op;
3535
mod not;
36+
mod similar_to_pattern;
3637
mod try_cast;
3738
mod unknown_column;
3839

@@ -58,6 +59,8 @@ pub use literal::{Literal, lit};
5859
pub use negative::{NegativeExpr, negative};
5960
pub use no_op::NoOp;
6061
pub use not::{NotExpr, not};
62+
pub(crate) use similar_to_pattern::translate_scalar;
63+
pub use similar_to_pattern::{SqlSimilarToPattern, sql_similar_to_regex};
6164
pub use try_cast::{TryCastExpr, try_cast};
6265
pub use unknown_column::UnKnownColumn;
6366

0 commit comments

Comments
 (0)