You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/08/17 03:52:12 UTC
(datafusion) 01/01: fix: translate SQL wildcards in SIMILAR TO patterns (#22263) (#23188)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-23188-710e74e6764275675b117823d26d4944115f6ba2
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 83be77c523fa114e8b9f3756721b25177894d209
Author: 0x70 <10...@users.noreply.github.com>
AuthorDate: Mon Aug 17 03:51:49 2026 +0000
fix: translate SQL wildcards in SIMILAR TO patterns (#22263) (#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 #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.
---
datafusion-testing | 2 +-
datafusion/physical-expr/src/expressions/binary.rs | 251 ++++++++++++-
datafusion/physical-expr/src/expressions/mod.rs | 3 +
.../src/expressions/similar_to_pattern.rs | 417 +++++++++++++++++++++
datafusion/proto-models/proto/datafusion.proto | 5 +
datafusion/proto-models/src/generated/pbjson.rs | 105 ++++++
datafusion/proto-models/src/generated/prost.rs | 11 +-
datafusion/proto/src/physical_plan/from_proto.rs | 6 +-
datafusion/proto/tests/cases/plans/exprs.rs | 22 +-
datafusion/sqllogictest/test_files/strings.slt | 256 ++++++++++++-
.../sqllogictest/test_files/type_coercion.slt | 4 +-
docs/source/library-user-guide/upgrading/55.0.0.md | 31 ++
12 files changed, 1081 insertions(+), 32 deletions(-)
diff --git a/datafusion-testing b/datafusion-testing
index 7833a65d5b..13bbae3877 160000
--- a/datafusion-testing
+++ b/datafusion-testing
@@ -1 +1 @@
-Subproject commit 7833a65d5b08be2ca484ea938f471cf01df54e18
+Subproject commit 13bbae38776c2bfbc1fab1be7e7220222d4284bf
diff --git a/datafusion/physical-expr/src/expressions/binary.rs b/datafusion/physical-expr/src/expressions/binary.rs
index 1bd49696bb..e86748ccc9 100644
--- a/datafusion/physical-expr/src/expressions/binary.rs
+++ b/datafusion/physical-expr/src/expressions/binary.rs
@@ -18,6 +18,8 @@
mod kernels;
use crate::PhysicalExpr;
+use crate::expressions::SqlSimilarToPattern;
+use crate::expressions::translate_scalar;
use crate::intervals::cp_solver::{propagate_arithmetic, propagate_comparison};
use std::cmp::Ordering;
use std::hash::Hash;
@@ -1371,7 +1373,19 @@ pub fn similar_to(
(true, false) => Operator::RegexNotMatch,
(true, true) => Operator::RegexNotIMatch,
};
- Ok(Arc::new(BinaryExpr::new(expr, binary_op, pattern)))
+
+ let translated_pattern = match pattern.downcast_ref::<crate::expressions::Literal>() {
+ Some(literal) => Arc::new(crate::expressions::Literal::new(translate_scalar(
+ literal.value(),
+ )?)) as Arc<dyn PhysicalExpr>,
+ None => Arc::new(SqlSimilarToPattern::new(pattern)) as Arc<dyn PhysicalExpr>,
+ };
+
+ Ok(Arc::new(BinaryExpr::new(
+ expr,
+ binary_op,
+ translated_pattern,
+ )))
}
#[cfg(test)]
@@ -5327,25 +5341,17 @@ mod tests {
Ok(())
}
- /// Test helper for SIMILAR TO binary operation
fn apply_similar_to(
schema: &SchemaRef,
va: Vec<&str>,
- vb: Vec<&str>,
+ pattern: &str,
negated: bool,
case_insensitive: bool,
expected: &BooleanArray,
) -> Result<()> {
let a = StringArray::from(va);
- let b = StringArray::from(vb);
- let op = similar_to(
- negated,
- case_insensitive,
- col("a", schema)?,
- col("b", schema)?,
- )?;
- let batch =
- RecordBatch::try_new(Arc::clone(schema), vec![Arc::new(a), Arc::new(b)])?;
+ let op = similar_to(negated, case_insensitive, col("a", schema)?, lit(pattern))?;
+ let batch = RecordBatch::try_new(Arc::clone(schema), vec![Arc::new(a)])?;
let result = op
.evaluate(&batch)?
.into_array(batch.num_rows())
@@ -5357,32 +5363,237 @@ mod tests {
#[test]
fn test_similar_to() {
- let schema = Arc::new(Schema::new(vec![
- Field::new("a", DataType::Utf8, false),
- Field::new("b", DataType::Utf8, false),
- ]));
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+ // `%` matches any sequence; case-sensitive
let expected = [Some(true), Some(false)].iter().collect();
- // case-sensitive
apply_similar_to(
&schema,
vec!["hello world", "Hello World"],
- vec!["hello.*", "hello.*"],
+ "hello%",
false,
false,
&expected,
)
.unwrap();
- // case-insensitive
+
+ // `%` matches any sequence; case-insensitive
+ let expected = [Some(true), Some(false)].iter().collect();
apply_similar_to(
&schema,
vec!["hello world", "bye"],
- vec!["hello.*", "hello.*"],
+ "hello%",
false,
true,
&expected,
)
.unwrap();
+
+ // `_` matches exactly one character
+ let expected = [Some(true), Some(false), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["x", "xy", ""], "_", false, false, &expected)
+ .unwrap();
+
+ // Match must cover the entire string (no implicit substring match)
+ let expected = [Some(false), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["abc", "a"], "a", false, false, &expected)
+ .unwrap();
+
+ // `%` matches zero or more, so the empty string matches.
+ let expected = [Some(true), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["", "anything"], "%", false, false, &expected)
+ .unwrap();
+
+ // `_` requires exactly one character, so the empty string does not
+ // match.
+ let expected = [Some(false), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["", "x"], "_", false, false, &expected).unwrap();
+
+ // `%` at the start of the pattern is still anchored: the string
+ // must end where the trailing literal begins.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["abc", "abd"], "%c", false, false, &expected)
+ .unwrap();
+
+ // `%` and `_` together: `%` matches zero or more (including the
+ // empty string), `_` matches exactly one character.
+ let expected = [Some(true), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["a", "abc"], "a%", false, false, &expected)
+ .unwrap();
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["axb", "abc"], "a_b", false, false, &expected)
+ .unwrap();
+ }
+
+ // Regression: regex metacharacters that are NOT SIMILAR TO metacharacters
+ // (`. ^ $ \`) must be treated as SQL literals. Without escaping, `a.`
+ // would match any `a` followed by any character (`ab`, `a1`, ...).
+ #[test]
+ fn test_similar_to_sql_literal_metachars() {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+
+ // `.` is a literal, not the regex "any character" operator.
+ let expected = [Some(true), Some(false), Some(false)].iter().collect();
+ apply_similar_to(
+ &schema,
+ vec!["a.", "ab", "a"],
+ "a.",
+ false,
+ false,
+ &expected,
+ )
+ .unwrap();
+
+ // `^` and `$` are literals and only match the literal `^` and `$`.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["^x$", "x"], r"^x$", false, false, &expected)
+ .unwrap();
+
+ // `\` is a literal backslash (we don't support the ESCAPE clause).
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec![r"a\b", "ab"], r"a\b", false, false, &expected)
+ .unwrap();
+ }
+
+ // SIMILAR TO borrows POSIX metacharacters from regular expressions:
+ // `| * + ? ( ) { } [ ]`. The translator passes them through to the
+ // underlying regex engine.
+ #[test]
+ fn test_similar_to_posix_metachars() {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+
+ // `|` alternation.
+ let expected = [Some(true), Some(false), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["a", "c", "b"], "a|b", false, false, &expected)
+ .unwrap();
+
+ // `*` zero or more.
+ let expected = [Some(true), Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["", "aa", "ab"], "a*", false, false, &expected)
+ .unwrap();
+
+ // `+` one or more.
+ let expected = [Some(false), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["", "aa"], "a+", false, false, &expected).unwrap();
+
+ // `?` zero or one.
+ let expected = [Some(true), Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["", "a", "aa"], "a?", false, false, &expected)
+ .unwrap();
+
+ // `()` grouping.
+ let expected = [Some(true), Some(true), Some(false)].iter().collect();
+ apply_similar_to(
+ &schema,
+ vec!["ab", "abc", "ac"],
+ "(ab)c?",
+ false,
+ false,
+ &expected,
+ )
+ .unwrap();
+
+ // `{m}` exact count.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["aaa", "aa"], "a{3}", false, false, &expected)
+ .unwrap();
+
+ // `[...]` character class.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["a", "c"], "[ab]", false, false, &expected)
+ .unwrap();
+
+ // `[^...]` negated character class.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["c", "a"], "[^ab]", false, false, &expected)
+ .unwrap();
+
+ // `[a-z]` range inside a character class.
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["m", "1"], "[a-z]", false, false, &expected)
+ .unwrap();
+ }
+
+ // Regression: `%` and `_` must match newlines, matching SQL semantics
+ // where these wildcards match "any character".
+ #[test]
+ fn test_similar_to_wildcards_match_newlines() {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+
+ // `%` crosses a newline. (`%` also matches zero characters, so `ab`
+ // matches `a%b` as well.)
+ let expected = [Some(true), Some(true)].iter().collect();
+ apply_similar_to(&schema, vec!["a\nb", "ab"], "a%b", false, false, &expected)
+ .unwrap();
+
+ // `_` matches a single newline. (`_` requires exactly one character,
+ // so `ab` does not match `a_b`.)
+ let expected = [Some(true), Some(false)].iter().collect();
+ apply_similar_to(&schema, vec!["a\nb", "ab"], "a_b", false, false, &expected)
+ .unwrap();
+ }
+
+ #[test]
+ fn test_similar_to_non_literal_pattern_errors() {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+ // Non-string literal patterns still error.
+ let err = similar_to(false, false, col("a", &schema).unwrap(), lit(1i32))
+ .expect_err("non-string literal pattern should error");
+ assert!(
+ err.to_string()
+ .contains("SIMILAR TO pattern must be a string type, got Int32(1)"),
+ "unexpected error message: {err}"
+ );
+ }
+
+ #[test]
+ fn test_similar_to_dynamic_pattern() {
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("text", DataType::Utf8, false),
+ Field::new("pattern", DataType::Utf8, false),
+ ]));
+ let text = StringArray::from(vec!["abc", "ab", "x"]);
+ let pattern = StringArray::from(vec!["a%", "a.", "_"]);
+ let batch = RecordBatch::try_new(
+ Arc::clone(&schema),
+ vec![Arc::new(text), Arc::new(pattern)],
+ )
+ .unwrap();
+
+ let op = similar_to(
+ false,
+ false,
+ col("text", &schema).unwrap(),
+ col("pattern", &schema).unwrap(),
+ )
+ .unwrap();
+ let result = op.evaluate(&batch).unwrap();
+ let result_array = result.into_array(batch.num_rows()).unwrap();
+ let result = as_boolean_array(&result_array).unwrap();
+ assert!(result.value(0)); // "abc" ~ ^(?:a(?s:.*))$
+ assert!(!result.value(1)); // "ab" ~ ^(?:a\.)$
+ assert!(result.value(2)); // "x" ~ ^(?:(?s:.))$
+ }
+
+ #[test]
+ fn test_similar_to_null_pattern() {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, false)]));
+ let a = StringArray::from(vec!["hello"]);
+ let op = similar_to(
+ false,
+ false,
+ col("a", &schema).unwrap(),
+ lit(ScalarValue::Utf8(None)),
+ )
+ .unwrap();
+ let batch = RecordBatch::try_new(Arc::clone(&schema), vec![Arc::new(a)]).unwrap();
+ let result = op
+ .evaluate(&batch)
+ .unwrap()
+ .into_array(batch.num_rows())
+ .unwrap();
+ let expected: BooleanArray = [None].iter().collect();
+ assert_eq!(result.as_ref(), &expected);
}
pub fn binary_expr(
diff --git a/datafusion/physical-expr/src/expressions/mod.rs b/datafusion/physical-expr/src/expressions/mod.rs
index 035dd5d507..521b8b87e3 100644
--- a/datafusion/physical-expr/src/expressions/mod.rs
+++ b/datafusion/physical-expr/src/expressions/mod.rs
@@ -33,6 +33,7 @@ mod literal;
mod negative;
mod no_op;
mod not;
+mod similar_to_pattern;
mod try_cast;
mod unknown_column;
@@ -58,6 +59,8 @@ pub use literal::{Literal, lit};
pub use negative::{NegativeExpr, negative};
pub use no_op::NoOp;
pub use not::{NotExpr, not};
+pub(crate) use similar_to_pattern::translate_scalar;
+pub use similar_to_pattern::{SqlSimilarToPattern, sql_similar_to_regex};
pub use try_cast::{TryCastExpr, try_cast};
pub use unknown_column::UnKnownColumn;
diff --git a/datafusion/physical-expr/src/expressions/similar_to_pattern.rs b/datafusion/physical-expr/src/expressions/similar_to_pattern.rs
new file mode 100644
index 0000000000..4078cb5256
--- /dev/null
+++ b/datafusion/physical-expr/src/expressions/similar_to_pattern.rs
@@ -0,0 +1,417 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use std::fmt;
+use std::hash::Hash;
+use std::sync::Arc;
+
+use crate::PhysicalExpr;
+
+use arrow::array::{ArrayRef, LargeStringArray, StringArray, StringViewArray};
+use arrow::datatypes::{DataType, FieldRef};
+use arrow::record_batch::RecordBatch;
+use datafusion_common::{Result, ScalarValue, exec_err};
+use datafusion_expr::ColumnarValue;
+use datafusion_physical_expr_common::physical_expr::fmt_sql;
+
+pub fn sql_similar_to_regex(pattern: &str) -> String {
+ let mut result = String::with_capacity(pattern.len() + 10);
+ result.push_str("^(?:");
+ let mut in_bracket = false;
+ for ch in pattern.chars() {
+ match (ch, in_bracket) {
+ ('%', false) => result.push_str("(?s:.*)"),
+ ('_', false) => result.push_str("(?s:.)"),
+ ('[', false) => {
+ result.push('[');
+ in_bracket = true;
+ }
+ (']', true) => {
+ result.push(']');
+ in_bracket = false;
+ }
+ // `. ^ $` are SQL literals but regex metachars when not inside a
+ // bracket expression; `\` is a regex escape character in all
+ // positions, so it always needs escaping.
+ ('.' | '^' | '$', false) | ('\\', _) => {
+ result.push('\\');
+ result.push(ch);
+ }
+ (c, _) => result.push(c),
+ }
+ }
+ result.push_str(")$");
+ result
+}
+
+#[derive(Debug, Eq)]
+pub struct SqlSimilarToPattern {
+ expr: Arc<dyn PhysicalExpr>,
+}
+
+// Manually derive PartialEq and Hash to work around https://github.com/rust-lang/rust/issues/78808
+impl PartialEq for SqlSimilarToPattern {
+ fn eq(&self, other: &Self) -> bool {
+ self.expr.eq(&other.expr)
+ }
+}
+
+impl Hash for SqlSimilarToPattern {
+ fn hash<H: std::hash::Hasher>(&self, state: &mut H) {
+ self.expr.hash(state);
+ }
+}
+
+impl SqlSimilarToPattern {
+ pub fn new(expr: Arc<dyn PhysicalExpr>) -> Self {
+ Self { expr }
+ }
+
+ pub fn expr(&self) -> &Arc<dyn PhysicalExpr> {
+ &self.expr
+ }
+}
+
+impl fmt::Display for SqlSimilarToPattern {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ write!(f, "sql_similar_to_regex({})", self.expr)
+ }
+}
+
+impl PhysicalExpr for SqlSimilarToPattern {
+ fn data_type(&self, input_schema: &arrow::datatypes::Schema) -> Result<DataType> {
+ self.expr.data_type(input_schema)
+ }
+
+ fn nullable(&self, input_schema: &arrow::datatypes::Schema) -> Result<bool> {
+ self.expr.nullable(input_schema)
+ }
+
+ fn evaluate(&self, batch: &RecordBatch) -> Result<ColumnarValue> {
+ match self.expr.evaluate(batch)? {
+ ColumnarValue::Array(array) => translate_array(&array),
+ ColumnarValue::Scalar(scalar) => {
+ Ok(ColumnarValue::Scalar(translate_scalar(&scalar)?))
+ }
+ }
+ }
+
+ fn return_field(&self, input_schema: &arrow::datatypes::Schema) -> Result<FieldRef> {
+ self.expr.return_field(input_schema)
+ }
+
+ fn children(&self) -> Vec<&Arc<dyn PhysicalExpr>> {
+ vec![&self.expr]
+ }
+
+ fn with_new_children(
+ self: Arc<Self>,
+ children: Vec<Arc<dyn PhysicalExpr>>,
+ ) -> Result<Arc<dyn PhysicalExpr>> {
+ Ok(Arc::new(SqlSimilarToPattern::new(Arc::clone(&children[0]))))
+ }
+
+ fn fmt_sql(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ write!(f, "sql_similar_to_regex({})", fmt_sql(self.expr.as_ref()))
+ }
+
+ #[cfg(feature = "proto")]
+ fn try_to_proto(
+ &self,
+ ctx: &datafusion_physical_expr_common::physical_expr::proto_encode::PhysicalExprEncodeCtx<'_>,
+ ) -> Result<Option<datafusion_proto_models::protobuf::PhysicalExprNode>> {
+ use datafusion_proto_models::protobuf;
+
+ Ok(Some(protobuf::PhysicalExprNode {
+ expr_id: None,
+ expr_type: Some(protobuf::physical_expr_node::ExprType::SqlSimilarToPattern(
+ Box::new(protobuf::PhysicalSqlSimilarToPatternNode {
+ expr: Some(Box::new(ctx.encode_child(&self.expr)?)),
+ }),
+ )),
+ }))
+ }
+}
+
+#[cfg(feature = "proto")]
+impl SqlSimilarToPattern {
+ /// Reconstruct a [`SqlSimilarToPattern`] from its protobuf representation.
+ ///
+ /// Takes the whole [`PhysicalExprNode`] so the decode signature matches
+ /// other migrated expressions and can inspect outer-node metadata if
+ /// needed in the future.
+ ///
+ /// [`PhysicalExprNode`]: datafusion_proto_models::protobuf::PhysicalExprNode
+ pub fn try_from_proto(
+ node: &datafusion_proto_models::protobuf::PhysicalExprNode,
+ ctx: &datafusion_physical_expr_common::physical_expr::proto_decode::PhysicalExprDecodeCtx<'_>,
+ ) -> Result<Arc<dyn PhysicalExpr>> {
+ use datafusion_physical_expr_common::expect_expr_variant;
+ use datafusion_proto_models::protobuf;
+
+ let pattern = expect_expr_variant!(
+ node,
+ protobuf::physical_expr_node::ExprType::SqlSimilarToPattern,
+ "SqlSimilarToPattern",
+ );
+
+ Ok(Arc::new(SqlSimilarToPattern::new(
+ ctx.decode_required_expression(
+ pattern.expr.as_deref(),
+ "SqlSimilarToPattern",
+ "expr",
+ )?,
+ )))
+ }
+}
+
+pub(crate) fn translate_scalar(scalar: &ScalarValue) -> Result<ScalarValue> {
+ match scalar {
+ ScalarValue::Utf8(Some(s)) => {
+ Ok(ScalarValue::Utf8(Some(sql_similar_to_regex(s))))
+ }
+ ScalarValue::LargeUtf8(Some(s)) => {
+ Ok(ScalarValue::LargeUtf8(Some(sql_similar_to_regex(s))))
+ }
+ ScalarValue::Utf8View(Some(s)) => {
+ Ok(ScalarValue::Utf8View(Some(sql_similar_to_regex(s))))
+ }
+ // A NULL pattern makes the whole predicate NULL, but the string variant
+ // still has to survive the translation: the regex kernel dispatches on
+ // the left-hand type and downcasts the pattern to the same array type.
+ ScalarValue::Utf8(None) => Ok(ScalarValue::Utf8(None)),
+ ScalarValue::LargeUtf8(None) => Ok(ScalarValue::LargeUtf8(None)),
+ ScalarValue::Utf8View(None) => Ok(ScalarValue::Utf8View(None)),
+ ScalarValue::Null => Ok(ScalarValue::Utf8(None)),
+ other => exec_err!("SIMILAR TO pattern must be a string type, got {other:?}"),
+ }
+}
+
+fn translate_array(array: &ArrayRef) -> Result<ColumnarValue> {
+ match array.data_type() {
+ DataType::Utf8 => {
+ let arr = array.as_any().downcast_ref::<StringArray>().unwrap();
+ let translated: StringArray = arr
+ .iter()
+ .map(|opt| opt.map(sql_similar_to_regex))
+ .collect();
+ Ok(ColumnarValue::Array(Arc::new(translated)))
+ }
+ DataType::LargeUtf8 => {
+ let arr = array.as_any().downcast_ref::<LargeStringArray>().unwrap();
+ let translated: LargeStringArray = arr
+ .iter()
+ .map(|opt| opt.map(sql_similar_to_regex))
+ .collect();
+ Ok(ColumnarValue::Array(Arc::new(translated)))
+ }
+ DataType::Utf8View => {
+ let arr = array.as_any().downcast_ref::<StringViewArray>().unwrap();
+ let translated: StringViewArray = arr
+ .iter()
+ .map(|opt| opt.map(sql_similar_to_regex))
+ .collect();
+ Ok(ColumnarValue::Array(Arc::new(translated)))
+ }
+ other => exec_err!("SIMILAR TO pattern must be a string type, got {other:?}"),
+ }
+}
+
+#[cfg(test)]
+mod tests {
+ use super::*;
+ use crate::expressions::{col, lit};
+ use arrow::array::StringArray;
+ use arrow::datatypes::{DataType, Field, Schema};
+ use datafusion_common::cast::as_string_array;
+
+ #[test]
+ fn test_translate_scalar() -> Result<()> {
+ assert_eq!(
+ translate_scalar(&ScalarValue::Utf8(Some("a%".to_string())))?,
+ ScalarValue::Utf8(Some(r"^(?:a(?s:.*))$".to_string()))
+ );
+ assert_eq!(
+ translate_scalar(&ScalarValue::Utf8(Some("a.b".to_string())))?,
+ ScalarValue::Utf8(Some(r"^(?:a\.b)$".to_string()))
+ );
+ assert!(
+ translate_scalar(&ScalarValue::Null).is_ok(),
+ "NULL pattern should pass through"
+ );
+ assert_eq!(
+ translate_scalar(&ScalarValue::Null).unwrap(),
+ ScalarValue::Utf8(None)
+ );
+ // A NULL pattern keeps its string variant: the regex kernel dispatches
+ // on the left-hand type and downcasts the pattern to the same type.
+ assert_eq!(
+ translate_scalar(&ScalarValue::LargeUtf8(None))?,
+ ScalarValue::LargeUtf8(None)
+ );
+ assert_eq!(
+ translate_scalar(&ScalarValue::Utf8View(None))?,
+ ScalarValue::Utf8View(None)
+ );
+ // Non-`Utf8` patterns keep their variant when translated too.
+ assert_eq!(
+ translate_scalar(&ScalarValue::LargeUtf8(Some("a%".to_string())))?,
+ ScalarValue::LargeUtf8(Some(r"^(?:a(?s:.*))$".to_string()))
+ );
+ assert_eq!(
+ translate_scalar(&ScalarValue::Utf8View(Some("a%".to_string())))?,
+ ScalarValue::Utf8View(Some(r"^(?:a(?s:.*))$".to_string()))
+ );
+ assert!(translate_scalar(&ScalarValue::Int32(Some(1))).is_err());
+ Ok(())
+ }
+
+ #[test]
+ fn test_translate_array() -> Result<()> {
+ let schema = Schema::new(vec![Field::new("a", DataType::Utf8, false)]);
+ let input = StringArray::from(vec!["a%", "a.b", "a_b"]);
+ let expr = SqlSimilarToPattern::new(col("a", &schema)?);
+ let batch = RecordBatch::try_new(Arc::new(schema), vec![Arc::new(input)])?;
+ let result = expr.evaluate(&batch)?.into_array(batch.num_rows())?;
+ let result = as_string_array(&result)?;
+ assert_eq!(result.value(0), r"^(?:a(?s:.*))$");
+ assert_eq!(result.value(1), r"^(?:a\.b)$");
+ assert_eq!(result.value(2), r"^(?:a(?s:.)b)$");
+ Ok(())
+ }
+
+ #[test]
+ fn test_display_and_sql() -> Result<()> {
+ let expr = SqlSimilarToPattern::new(lit("a%"));
+ assert_eq!("sql_similar_to_regex(a%)", format!("{expr}"));
+ Ok(())
+ }
+}
+
+/// Tests for the `try_to_proto` / `try_from_proto` hooks.
+#[cfg(all(test, feature = "proto"))]
+mod proto_tests {
+ use super::*;
+ use crate::expressions::{Column, col};
+ use crate::proto_test_util::{
+ StubDecoder, StubEncoder, UnreachableDecoder, column_node,
+ };
+ use arrow::datatypes::{DataType, Field, Schema};
+ use datafusion_common::DataFusionError;
+ use datafusion_physical_expr_common::physical_expr::proto_decode::PhysicalExprDecodeCtx;
+ use datafusion_physical_expr_common::physical_expr::proto_encode::PhysicalExprEncodeCtx;
+ use datafusion_proto_models::protobuf::{
+ PhysicalExprNode, PhysicalSqlSimilarToPatternNode, physical_expr_node,
+ };
+
+ /// Build a `SqlSimilarToPattern` proto node with the given child.
+ fn pattern_node(expr: Option<Box<PhysicalExprNode>>) -> PhysicalExprNode {
+ PhysicalExprNode {
+ expr_id: None,
+ expr_type: Some(physical_expr_node::ExprType::SqlSimilarToPattern(Box::new(
+ PhysicalSqlSimilarToPatternNode { expr },
+ ))),
+ }
+ }
+
+ /// A `SqlSimilarToPattern` over a `Utf8` column.
+ fn pattern_fixture() -> SqlSimilarToPattern {
+ let schema = Schema::new(vec![Field::new("a", DataType::Utf8, false)]);
+ SqlSimilarToPattern::new(col("a", &schema).unwrap())
+ }
+
+ #[test]
+ fn try_to_proto_encodes_sql_similar_to_pattern() {
+ let pattern = pattern_fixture();
+ let encoder = StubEncoder::ok();
+ let ctx = PhysicalExprEncodeCtx::new(&encoder);
+
+ let node = pattern
+ .try_to_proto(&ctx)
+ .unwrap()
+ .expect("SqlSimilarToPattern should encode to Some(node)");
+
+ // Built-in exprs never set expr_id; only dynamic filters do.
+ assert!(node.expr_id.is_none());
+ let pattern_node = match node.expr_type {
+ Some(physical_expr_node::ExprType::SqlSimilarToPattern(boxed)) => *boxed,
+ other => panic!("expected a SqlSimilarToPattern node, got {other:?}"),
+ };
+ assert!(pattern_node.expr.is_some());
+ }
+
+ #[test]
+ fn try_to_proto_propagates_expr_encode_error() {
+ let pattern = pattern_fixture();
+ let encoder = StubEncoder::failing_on(1);
+ let ctx = PhysicalExprEncodeCtx::new(&encoder);
+ let err = pattern.try_to_proto(&ctx).unwrap_err();
+ assert!(matches!(err, DataFusionError::Internal(msg) if msg.contains("call 1")));
+ }
+
+ #[test]
+ fn try_from_proto_decodes_sql_similar_to_pattern() {
+ let node = pattern_node(Some(Box::new(column_node("a"))));
+ let schema = Schema::empty();
+ let decoder = StubDecoder::ok();
+ let ctx = PhysicalExprDecodeCtx::new(&schema, &decoder);
+
+ let decoded = SqlSimilarToPattern::try_from_proto(&node, &ctx).unwrap();
+ let pattern = decoded
+ .downcast_ref::<SqlSimilarToPattern>()
+ .expect("decoded expr should be a SqlSimilarToPattern");
+ assert!(pattern.expr().downcast_ref::<Column>().is_some());
+ }
+
+ #[test]
+ fn try_from_proto_rejects_non_sql_similar_to_pattern_node() {
+ let node = column_node("a");
+ let schema = Schema::empty();
+ let decoder = UnreachableDecoder;
+ let ctx = PhysicalExprDecodeCtx::new(&schema, &decoder);
+ let err = SqlSimilarToPattern::try_from_proto(&node, &ctx).unwrap_err();
+ assert!(matches!(
+ err,
+ DataFusionError::Internal(msg)
+ if msg.contains("PhysicalExprNode is not a SqlSimilarToPattern")
+ ));
+ }
+
+ #[test]
+ fn try_from_proto_rejects_missing_expr() {
+ let node = pattern_node(None);
+ let schema = Schema::empty();
+ let decoder = UnreachableDecoder;
+ let ctx = PhysicalExprDecodeCtx::new(&schema, &decoder);
+ let err = SqlSimilarToPattern::try_from_proto(&node, &ctx).unwrap_err();
+ assert!(matches!(
+ err,
+ DataFusionError::Internal(msg)
+ if msg.contains("SqlSimilarToPattern is missing required field 'expr'")
+ ));
+ }
+
+ #[test]
+ fn try_from_proto_propagates_expr_decode_error() {
+ let node = pattern_node(Some(Box::new(column_node("a"))));
+ let schema = Schema::empty();
+ let decoder = StubDecoder::failing_on(1);
+ let ctx = PhysicalExprDecodeCtx::new(&schema, &decoder);
+ let err = SqlSimilarToPattern::try_from_proto(&node, &ctx).unwrap_err();
+ assert!(matches!(err, DataFusionError::Internal(msg) if msg.contains("call 1")));
+ }
+}
diff --git a/datafusion/proto-models/proto/datafusion.proto b/datafusion/proto-models/proto/datafusion.proto
index f29d63c476..d98b67a66e 100644
--- a/datafusion/proto-models/proto/datafusion.proto
+++ b/datafusion/proto-models/proto/datafusion.proto
@@ -1051,6 +1051,7 @@ message PhysicalExprNode {
PhysicalLambdaExprNode lambda = 25;
PhysicalLambdaVariableExprNode lambda_variable = 26;
PhysicalRangeExprNode range_expr = 27;
+ PhysicalSqlSimilarToPatternNode sql_similar_to_pattern = 28;
}
}
@@ -1062,6 +1063,10 @@ message PhysicalDynamicFilterNode {
bool is_complete = 5;
}
+message PhysicalSqlSimilarToPatternNode {
+ PhysicalExprNode expr = 1;
+}
+
message PhysicalScalarUdfNode {
string name = 1;
repeated PhysicalExprNode args = 2;
diff --git a/datafusion/proto-models/src/generated/pbjson.rs b/datafusion/proto-models/src/generated/pbjson.rs
index 19121b2dbe..21309bb2d0 100644
--- a/datafusion/proto-models/src/generated/pbjson.rs
+++ b/datafusion/proto-models/src/generated/pbjson.rs
@@ -18582,6 +18582,9 @@ impl serde::Serialize for PhysicalExprNode {
physical_expr_node::ExprType::RangeExpr(v) => {
struct_ser.serialize_field("rangeExpr", v)?;
}
+ physical_expr_node::ExprType::SqlSimilarToPattern(v) => {
+ struct_ser.serialize_field("sqlSimilarToPattern", v)?;
+ }
}
}
struct_ser.end()
@@ -18639,6 +18642,8 @@ impl<'de> serde::Deserialize<'de> for PhysicalExprNode {
"lambdaVariable",
"range_expr",
"rangeExpr",
+ "sql_similar_to_pattern",
+ "sqlSimilarToPattern",
];
#[allow(clippy::enum_variant_names)]
@@ -18669,6 +18674,7 @@ impl<'de> serde::Deserialize<'de> for PhysicalExprNode {
Lambda,
LambdaVariable,
RangeExpr,
+ SqlSimilarToPattern,
}
impl<'de> serde::Deserialize<'de> for GeneratedField {
fn deserialize<D>(deserializer: D) -> std::result::Result<GeneratedField, D::Error>
@@ -18716,6 +18722,7 @@ impl<'de> serde::Deserialize<'de> for PhysicalExprNode {
"lambda" => Ok(GeneratedField::Lambda),
"lambdaVariable" | "lambda_variable" => Ok(GeneratedField::LambdaVariable),
"rangeExpr" | "range_expr" => Ok(GeneratedField::RangeExpr),
+ "sqlSimilarToPattern" | "sql_similar_to_pattern" => Ok(GeneratedField::SqlSimilarToPattern),
_ => Err(serde::de::Error::unknown_field(value, FIELDS)),
}
}
@@ -18920,6 +18927,13 @@ impl<'de> serde::Deserialize<'de> for PhysicalExprNode {
return Err(serde::de::Error::duplicate_field("rangeExpr"));
}
expr_type__ = map_.next_value::<::std::option::Option<_>>()?.map(physical_expr_node::ExprType::RangeExpr)
+;
+ }
+ GeneratedField::SqlSimilarToPattern => {
+ if expr_type__.is_some() {
+ return Err(serde::de::Error::duplicate_field("sqlSimilarToPattern"));
+ }
+ expr_type__ = map_.next_value::<::std::option::Option<_>>()?.map(physical_expr_node::ExprType::SqlSimilarToPattern)
;
}
}
@@ -21820,6 +21834,97 @@ impl<'de> serde::Deserialize<'de> for PhysicalSortExprNodeCollection {
deserializer.deserialize_struct("datafusion.PhysicalSortExprNodeCollection", FIELDS, GeneratedVisitor)
}
}
+impl serde::Serialize for PhysicalSqlSimilarToPatternNode {
+ #[allow(deprecated)]
+ fn serialize<S>(&self, serializer: S) -> std::result::Result<S::Ok, S::Error>
+ where
+ S: serde::Serializer,
+ {
+ use serde::ser::SerializeStruct;
+ let mut len = 0;
+ if self.expr.is_some() {
+ len += 1;
+ }
+ let mut struct_ser = serializer.serialize_struct("datafusion.PhysicalSqlSimilarToPatternNode", len)?;
+ if let Some(v) = self.expr.as_ref() {
+ struct_ser.serialize_field("expr", v)?;
+ }
+ struct_ser.end()
+ }
+}
+impl<'de> serde::Deserialize<'de> for PhysicalSqlSimilarToPatternNode {
+ #[allow(deprecated)]
+ fn deserialize<D>(deserializer: D) -> std::result::Result<Self, D::Error>
+ where
+ D: serde::Deserializer<'de>,
+ {
+ const FIELDS: &[&str] = &[
+ "expr",
+ ];
+
+ #[allow(clippy::enum_variant_names)]
+ enum GeneratedField {
+ Expr,
+ }
+ impl<'de> serde::Deserialize<'de> for GeneratedField {
+ fn deserialize<D>(deserializer: D) -> std::result::Result<GeneratedField, D::Error>
+ where
+ D: serde::Deserializer<'de>,
+ {
+ struct GeneratedVisitor;
+
+ impl serde::de::Visitor<'_> for GeneratedVisitor {
+ type Value = GeneratedField;
+
+ fn expecting(&self, formatter: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+ write!(formatter, "expected one of: {:?}", &FIELDS)
+ }
+
+ #[allow(unused_variables)]
+ fn visit_str<E>(self, value: &str) -> std::result::Result<GeneratedField, E>
+ where
+ E: serde::de::Error,
+ {
+ match value {
+ "expr" => Ok(GeneratedField::Expr),
+ _ => Err(serde::de::Error::unknown_field(value, FIELDS)),
+ }
+ }
+ }
+ deserializer.deserialize_identifier(GeneratedVisitor)
+ }
+ }
+ struct GeneratedVisitor;
+ impl<'de> serde::de::Visitor<'de> for GeneratedVisitor {
+ type Value = PhysicalSqlSimilarToPatternNode;
+
+ fn expecting(&self, formatter: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+ formatter.write_str("struct datafusion.PhysicalSqlSimilarToPatternNode")
+ }
+
+ fn visit_map<V>(self, mut map_: V) -> std::result::Result<PhysicalSqlSimilarToPatternNode, V::Error>
+ where
+ V: serde::de::MapAccess<'de>,
+ {
+ let mut expr__ = None;
+ while let Some(k) = map_.next_key()? {
+ match k {
+ GeneratedField::Expr => {
+ if expr__.is_some() {
+ return Err(serde::de::Error::duplicate_field("expr"));
+ }
+ expr__ = map_.next_value()?;
+ }
+ }
+ }
+ Ok(PhysicalSqlSimilarToPatternNode {
+ expr: expr__,
+ })
+ }
+ }
+ deserializer.deserialize_struct("datafusion.PhysicalSqlSimilarToPatternNode", FIELDS, GeneratedVisitor)
+ }
+}
impl serde::Serialize for PhysicalTryCastNode {
#[allow(deprecated)]
fn serialize<S>(&self, serializer: S) -> std::result::Result<S::Ok, S::Error>
diff --git a/datafusion/proto-models/src/generated/prost.rs b/datafusion/proto-models/src/generated/prost.rs
index a6fe825ff6..d830624322 100644
--- a/datafusion/proto-models/src/generated/prost.rs
+++ b/datafusion/proto-models/src/generated/prost.rs
@@ -1557,7 +1557,7 @@ pub struct PhysicalExprNode {
pub expr_id: ::core::option::Option<u64>,
#[prost(
oneof = "physical_expr_node::ExprType",
- tags = "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27"
+ tags = "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28"
)]
pub expr_type: ::core::option::Option<physical_expr_node::ExprType>,
}
@@ -1622,6 +1622,10 @@ pub mod physical_expr_node {
LambdaVariable(super::PhysicalLambdaVariableExprNode),
#[prost(message, tag = "27")]
RangeExpr(super::PhysicalRangeExprNode),
+ #[prost(message, tag = "28")]
+ SqlSimilarToPattern(
+ ::prost::alloc::boxed::Box<super::PhysicalSqlSimilarToPatternNode>,
+ ),
}
}
#[derive(Clone, PartialEq, ::prost::Message)]
@@ -1638,6 +1642,11 @@ pub struct PhysicalDynamicFilterNode {
pub is_complete: bool,
}
#[derive(Clone, PartialEq, ::prost::Message)]
+pub struct PhysicalSqlSimilarToPatternNode {
+ #[prost(message, optional, boxed, tag = "1")]
+ pub expr: ::core::option::Option<::prost::alloc::boxed::Box<PhysicalExprNode>>,
+}
+#[derive(Clone, PartialEq, ::prost::Message)]
pub struct PhysicalScalarUdfNode {
#[prost(string, tag = "1")]
pub name: ::prost::alloc::string::String,
diff --git a/datafusion/proto/src/physical_plan/from_proto.rs b/datafusion/proto/src/physical_plan/from_proto.rs
index 06105be806..bc443149df 100644
--- a/datafusion/proto/src/physical_plan/from_proto.rs
+++ b/datafusion/proto/src/physical_plan/from_proto.rs
@@ -36,7 +36,8 @@ use datafusion_physical_expr::{
};
use datafusion_physical_plan::expressions::{
BinaryExpr, CaseExpr, CastExpr, Column, InListExpr, IsNotNullExpr, IsNullExpr,
- LikeExpr, Literal, NegativeExpr, NotExpr, TryCastExpr, UnKnownColumn,
+ LikeExpr, Literal, NegativeExpr, NotExpr, SqlSimilarToPattern, TryCastExpr,
+ UnKnownColumn,
};
use datafusion_physical_plan::joins::HashExpr;
use datafusion_physical_plan::proto::ExecutionPlanDecodeCtx;
@@ -361,6 +362,9 @@ pub fn parse_physical_expr_with_converter(
ExprType::DynamicFilter(_) => {
DynamicFilterPhysicalExpr::try_from_proto(proto, &decode_ctx)?
}
+ ExprType::SqlSimilarToPattern(_) => {
+ SqlSimilarToPattern::try_from_proto(proto, &decode_ctx)?
+ }
ExprType::Extension(extension) => {
let inputs: Vec<Arc<dyn PhysicalExpr>> = extension
.inputs
diff --git a/datafusion/proto/tests/cases/plans/exprs.rs b/datafusion/proto/tests/cases/plans/exprs.rs
index 518b4a62ce..9142e44bee 100644
--- a/datafusion/proto/tests/cases/plans/exprs.rs
+++ b/datafusion/proto/tests/cases/plans/exprs.rs
@@ -26,7 +26,7 @@ use datafusion::logical_expr::Operator;
use datafusion::physical_expr::expressions::Literal;
use datafusion::physical_plan::empty::EmptyExec;
use datafusion::physical_plan::expressions::{
- BinaryExpr, Column, PhysicalSortExpr, binary, col, like, lit,
+ BinaryExpr, Column, PhysicalSortExpr, SqlSimilarToPattern, binary, col, like, lit,
};
use datafusion::physical_plan::filter::FilterExec;
use datafusion::physical_plan::projection::{ProjectionExec, ProjectionExpr};
@@ -39,7 +39,7 @@ use datafusion::scalar::ScalarValue;
use datafusion_common::Result;
use datafusion_proto::physical_plan::{
AsExecutionPlan, DefaultPhysicalExtensionCodec, DefaultPhysicalProtoConverter,
- PhysicalProtoConverterExtension,
+ PhysicalPlanDecodeContext, PhysicalProtoConverterExtension,
};
use datafusion_proto::protobuf;
use datafusion_proto::protobuf::PhysicalPlanNode;
@@ -230,6 +230,24 @@ fn roundtrip_range_expr() -> Result<()> {
Ok(())
}
+#[test]
+fn roundtrip_sql_similar_to_pattern() -> Result<()> {
+ let schema = Arc::new(Schema::new(vec![Field::new("a", DataType::Utf8, true)]));
+ let expr: Arc<dyn PhysicalExpr> =
+ Arc::new(SqlSimilarToPattern::new(col("a", &schema)?));
+
+ let codec = DefaultPhysicalExtensionCodec {};
+ let converter = DefaultPhysicalProtoConverter {};
+ let proto = converter.physical_expr_to_proto(&expr, &codec)?;
+ let ctx = SessionContext::new();
+ let task_ctx = ctx.task_ctx();
+ let decode_ctx = PhysicalPlanDecodeContext::new(task_ctx.as_ref(), &codec);
+ let decoded = converter.proto_to_physical_expr(&proto, &schema, &decode_ctx)?;
+
+ assert_eq!(format!("{expr:?}"), format!("{decoded:?}"));
+ Ok(())
+}
+
#[test]
fn roundtrip_call_null_scalar_struct_dict() -> Result<()> {
let data_type = DataType::Struct(Fields::from(vec![Field::new(
diff --git a/datafusion/sqllogictest/test_files/strings.slt b/datafusion/sqllogictest/test_files/strings.slt
index 9fa453fa02..5d35a65f92 100644
--- a/datafusion/sqllogictest/test_files/strings.slt
+++ b/datafusion/sqllogictest/test_files/strings.slt
@@ -71,25 +71,269 @@ p2
p2e1
p2m1e1
-# SIMILAR TO
+# SIMILAR TO with `%` wildcard (zero or more characters)
query T rowsort
-SELECT s FROM test WHERE s SIMILAR TO 'p[12].*';
+SELECT s FROM test WHERE s SIMILAR TO 'p1%';
----
p1
p1e1
p1m1e1
+
+# SIMILAR TO with `_` wildcard (exactly one character)
+query T rowsort
+SELECT s FROM test WHERE s SIMILAR TO 'p_';
+----
+p1
p2
-p2e1
-p2m1e1
-# NOT SIMILAR TO
+# SIMILAR TO requires full-string match (no implicit substring)
query T rowsort
-SELECT s FROM test WHERE s NOT SIMILAR TO 'p[12].*';
+SELECT s FROM test WHERE s SIMILAR TO 'p1';
+----
+p1
+
+# SIMILAR TO treats `. ^ $` as SQL literals (not as regex metachars).
+query B
+SELECT 'a.' SIMILAR TO 'a.';
+----
+true
+
+query B
+SELECT 'ab' SIMILAR TO 'a.';
+----
+false
+
+query B
+SELECT '^x$' SIMILAR TO '^x$';
+----
+true
+
+query B
+SELECT 'x' SIMILAR TO '^x$';
+----
+false
+
+# SIMILAR TO supports the POSIX metacharacters `| * + ? ( ) { } [ ]`.
+query B
+SELECT 'a' SIMILAR TO 'a|b';
+----
+true
+
+query B
+SELECT 'c' SIMILAR TO 'a|b';
+----
+false
+
+query B
+SELECT '' SIMILAR TO 'a*';
+----
+true
+
+query B
+SELECT 'aa' SIMILAR TO 'a*';
+----
+true
+
+query B
+SELECT 'ab' SIMILAR TO 'a*';
+----
+false
+
+query B
+SELECT 'a' SIMILAR TO 'a+';
+----
+true
+
+query B
+SELECT '' SIMILAR TO 'a+';
+----
+false
+
+query B
+SELECT '' SIMILAR TO 'a?';
+----
+true
+
+query B
+SELECT 'a' SIMILAR TO 'a?';
+----
+true
+
+query B
+SELECT 'aa' SIMILAR TO 'a?';
+----
+false
+
+query B
+SELECT 'ab' SIMILAR TO '(ab)';
+----
+true
+
+query B
+SELECT 'a' SIMILAR TO 'a{2}';
+----
+false
+
+query B
+SELECT 'aa' SIMILAR TO 'a{2}';
+----
+true
+
+query B
+SELECT 'a' SIMILAR TO '[ab]';
+----
+true
+
+query B
+SELECT 'c' SIMILAR TO '[ab]';
+----
+false
+
+query B
+SELECT 'c' SIMILAR TO '[^ab]';
+----
+true
+
+query B
+SELECT 'a' SIMILAR TO '[^ab]';
+----
+false
+
+# SIMILAR TO wildcards match newlines.
+query B
+SELECT 'a' || chr(10) || 'b' SIMILAR TO 'a%b';
+----
+true
+
+query B
+SELECT 'a' || chr(10) || 'b' SIMILAR TO 'a_b';
+----
+true
+
+query B
+SELECT 'ab' SIMILAR TO 'a_b';
+----
+false
+
+# SIMILAR TO with a dynamic (column) pattern: wildcards and regex-only
+# metacharacters are translated the same way as for literal patterns.
+statement ok
+CREATE TABLE dynamic_patterns(text TEXT, pattern TEXT) AS VALUES
+ ('abc', 'a%'),
+ ('ab', 'a.'),
+ ('x', '_'),
+ ('hello world', 'hello%');
+
+query T rowsort
+SELECT text FROM dynamic_patterns WHERE text SIMILAR TO pattern;
+----
+abc
+hello world
+x
+
+query T rowsort
+SELECT text FROM dynamic_patterns WHERE text NOT SIMILAR TO pattern;
+----
+ab
+
+statement ok
+DROP TABLE dynamic_patterns;
+
+# SIMILAR TO with a dynamic pattern produced by a function.
+query B
+SELECT 'abc' SIMILAR TO concat('a', '%');
+----
+true
+
+query B
+SELECT 'ab' SIMILAR TO concat('a', '.');
+----
+false
+
+query B
+SELECT 'x' SIMILAR TO lower('X');
+----
+true
+
+query B
+SELECT 'X' SIMILAR TO lower('X');
+----
+false
+
+# NOT SIMILAR TO with a literal pattern.
+query T rowsort
+SELECT s FROM test WHERE s NOT SIMILAR TO 'p1%';
----
P1
P1e1
P1m1e1
e1
+p2
+p2e1
+p2m1e1
+
+# SIMILAR TO NULL pattern returns NULL.
+query B
+SELECT 'a' SIMILAR TO NULL;
+----
+NULL
+
+# SIMILAR TO with a non-string pattern is rejected by the analyzer.
+statement error There isn't a common type to coerce Utf8 and Int64 in SIMILAR TO expression
+SELECT 'a' SIMILAR TO 1;
+
+# The value and the pattern are coerced to a common string type, so mixing
+# `Utf8`, `LargeUtf8` and `Utf8View` works instead of failing in the regex
+# kernel.
+query B
+SELECT 'abc' SIMILAR TO arrow_cast('a%', 'LargeUtf8');
+----
+true
+
+query B
+SELECT arrow_cast('ab', 'Utf8View') SIMILAR TO arrow_cast('a.', 'LargeUtf8');
+----
+false
+
+query B
+SELECT arrow_cast('a', 'Utf8View') SIMILAR TO NULL;
+----
+NULL
+
+query B
+SELECT arrow_cast('a', 'LargeUtf8') SIMILAR TO arrow_cast(NULL, 'Utf8View');
+----
+NULL
+
+statement ok
+CREATE TABLE mixed_patterns(text TEXT, pattern TEXT) AS VALUES ('abc', 'a%');
+
+query B
+SELECT text SIMILAR TO arrow_cast(pattern, 'LargeUtf8') FROM mixed_patterns;
+----
+true
+
+query B
+SELECT arrow_cast(text, 'Utf8View') SIMILAR TO arrow_cast(pattern, 'LargeUtf8')
+FROM mixed_patterns;
+----
+true
+
+# Dictionary-encoded values are coerced to their value type.
+query B
+SELECT arrow_cast(text, 'Dictionary(Int32, Utf8)') SIMILAR TO 'a%'
+FROM mixed_patterns;
+----
+true
+
+query B
+SELECT arrow_cast(text, 'Dictionary(Int32, Utf8)') SIMILAR TO pattern
+FROM mixed_patterns;
+----
+true
+
+statement ok
+DROP TABLE mixed_patterns;
# NOT LIKE
query T rowsort
diff --git a/datafusion/sqllogictest/test_files/type_coercion.slt b/datafusion/sqllogictest/test_files/type_coercion.slt
index 6a56fc2407..f02b526fd2 100644
--- a/datafusion/sqllogictest/test_files/type_coercion.slt
+++ b/datafusion/sqllogictest/test_files/type_coercion.slt
@@ -329,8 +329,10 @@ NULL
statement ok
CREATE TABLE t AS SELECT * FROM (VALUES ('user auth failed')) v(s);
+# `SIMILAR TO` matches against the whole string, so the alternation is wrapped
+# in `%` wildcards to still match a substring of the value.
statement ok
-CREATE TABLE p AS SELECT * FROM (VALUES ('(auth|login)')) v(pat);
+CREATE TABLE p AS SELECT * FROM (VALUES ('%(auth|login)%')) v(pat);
# Utf8View value with a non-scalar Utf8 pattern (issue repro)
query B
diff --git a/docs/source/library-user-guide/upgrading/55.0.0.md b/docs/source/library-user-guide/upgrading/55.0.0.md
index b0f2602d31..ef2785edb7 100644
--- a/docs/source/library-user-guide/upgrading/55.0.0.md
+++ b/docs/source/library-user-guide/upgrading/55.0.0.md
@@ -1301,6 +1301,37 @@ Previously, this query returned `2.0`, the distance between `[1, 2]` and
`[1, 4]`. It now returns a planning error stating that `array_distance` only
supports one-dimensional arrays.
+### `ExprType` gained a `SqlSimilarToPattern` variant
+
+`SIMILAR TO` with a non-literal (column- or expression-based) pattern is now
+represented by a new `SqlSimilarToPattern` physical expression, so it can be
+serialized. The generated `ExprType` enum on `PhysicalExprNode` gained a
+matching `SqlSimilarToPattern` variant (tag 28).
+
+**Who is affected:**
+
+- Users matching exhaustively on `ExprType` (for example in a custom
+ physical-plan encoder/decoder).
+
+**Migration guide:**
+
+Add a `SqlSimilarToPattern` arm, or fall back to a wildcard arm:
+
+```rust,ignore
+match expr_type {
+ // ...
+ ExprType::SqlSimilarToPattern(node) => { /* ... */ }
+ _ => { /* ... */ }
+}
+```
+
+Plans encoded before this variant existed are unaffected: they never produced
+this variant, so decoding is unchanged. Plans containing a dynamic
+`SIMILAR TO` pattern encoded after this change fail to decode on older
+readers that don't recognize the new tag.
+
+See [PR #23188](https://github.com/apache/datafusion/pull/23188) for details.
+
### `ParquetObjectReader` / `ParquetObjectWriter` deprecated upstream
The [`parquet` crate] deprecated [`ParquetObjectReader`]
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org