fix: translate SQL wildcards in SIMILAR TO patterns (#22263)

[oc7o]

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 PostgreSQL compatibility: SIMILAR TO should treat % as a wildcard #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.

[oc7o]

@huaxingao @viirya @wesm Could one of you trigger CI for me please? Thanks!

[viirya]

@oc7o Triggered. CI is running now.

[kosiew]

@oc7o
Thanks for working on this. The direction looks good, but I think there are still a couple of correctness issues in the SIMILAR TO translation that should be addressed before this can be merged. I also have one small test coverage suggestion.

[oc7o]

@kosiew I gotta thank you for the effort you put into this. It was really with the eye for detail andI really learned a lot! 😊

I think with how it now is we're a bit closer to the standard SQL implementation. Maybe (when this PR is ready 🤞) escaping could be a good follow up topic for me. Since we currently still treat \ as a literal. So that 'a%' SIMILAR TO 'a\%' then returns true. Currently it would return false.

I'm curios for any responses!

[kosiew]

@oc7o
Thanks for the updates here. The literal-pattern handling looks much better now: the regex-only literal characters are escaped, % and _ handle newlines, and the added Rust and SLT coverage is helpful.

I do still see one regression around dynamic pattern expressions. SIMILAR TO previously allowed the right-hand side to be a normal expression, but this PR now rejects non-literal patterns during planning. Could you please address that before this lands?

[oc7o]

@kosiew

Thx again for the review. I learn so much new stuff when working on this!

I now added support for dynamic values on the RHS alongside some bug fixes like for the SIMILAR TO NULL expression.

One issue still is that datafusion-proto can't serialize SqlSimilarToPattern but I would argue that this is out of scope for this PR.

Also a decision I consciously made was disregarding strings inside Dictionary and RunEndEncoded for now.

🐙

[kosiew]

One issue still is that datafusion-proto can't serialize SqlSimilarToPattern but I would argue that this is out of scope for this PR.

Since this PR introduces SqlSimilarToPattern as a new built-in physical expression used for SQL-visible plans, either that expression needs proto support, or the PR should avoid introducing it into plans that need to serialize. Otherwise distributed/serialized physical plans that include dynamic SIMILAR TO regress.

Recommendation: if the goal is to keep this PR small, limit this PR's SQL-correct translation to literal patterns and preserve the previous dynamic-pattern representation for now. In practice, that means translating only Literal RHS patterns and using the original pattern expression unchanged for non-literal RHS patterns:

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 => pattern,
};

Then in a follow up PR, add the SqlSimilarToPattern AND proto support.

[oc7o]

@kosiew

Looks like the PR blew up a bit 🥲 but I wanna do this right, and I think the relevant things are now well covered. About 750 of the changed lines are tests and 300 hand-written source (plus the generated proto code).

Proto support: I went with your first option rather than narrowing the PR. SqlSimilarToPattern now has a PhysicalSqlSimilarToPatternNode message (field 24 on PhysicalExprNode), try_to_proto / try_from_proto, and a decode arm in from_proto.rs, following the same shape as LikeExpr. Some proto code is generated from regen.sh. The tests are pretty similar to the ones from the existing LikeExpr which I took as a reference.

Type coercion: while testing the above I found an issue with the plan time type check. The plan-time check now accepts LargeUtf8 / Utf8View patterns. Previously mismatched string types could reach the regex kernel, which dispatches on the left-hand type and then downcasts the pattern to that same array type. So this panicked with failed to downcast array:

SELECT 'abc' SIMILAR TO arrow_cast('a%', 'LargeUtf8');

Expr::SimilarTo was sitting in the analyzer's no-op list, so nothing ever reconciled the two sides. I added a coercion arm using regex_coercion (from Operator::RegexMatch), which is what SIMILAR TO lowers to. As a side effect dictionary-encoded strings now work too, since dictionary_coercion unwraps them to their value type.

\ is still treated as a literal, so 'a%' SIMILAR TO 'a\%' is still false and escaping doesn't work. Happy to take that as the follow-up I mentioned.

[kosiew]

@oc7o
Looks like there are conflicts now.