fmcmac opened a new pull request, #26468:
URL: https://github.com/apache/datafusion/pull/26468
## Which issue does this PR close?
No existing issue. This PR states the problem and a reproduction below.
## Rationale for this change
sqlparser parses `expr [NOT] [I]LIKE ANY (p1, ..., pn)` for every dialect,
but DataFusion refuses it:
```sql
SELECT 'ab' LIKE ANY ('a%', 'c%');
-- Error: This feature is not implemented: ANY in LIKE expression
```
Expected: `true`. Spark, Databricks and Snowflake users write this form.
## What changes are included in this PR?
- `x [I]LIKE ANY (p1, ..., pn)` plans as `x [I]LIKE p1 OR ... OR x [I]LIKE
pn`, sharing the escape character.
- `NOT` applies per pattern. `x NOT LIKE ANY (...)` is true when `x` fails
to match at least 1 pattern. `NOT x LIKE ANY (...)` negates the whole match.
This is Spark's contract. Postgres `ANY` agrees on NULLs: a NULL pattern gives
NULL only when no pattern matches.
- Each `LIKE` gets its own copy of the subject, so the copies must agree. A
volatile subject such as `random()` is refused, because each copy could see a
different value. A subject whose subquery plan holds a volatile expression is
refused too, such as `(SELECT CASE WHEN random() < 0.5 THEN 'a' ELSE 'b' END)`.
`Expr::is_volatile` treats subqueries as leaves, and the optimizer can plan
each copy of a subquery as its own join. A non-volatile subject gives every
copy the same value, and common subexpression elimination computes a
non-trivial expression subject once.
- An array (`ARRAY[...]`, or a list-typed expression) or a subquery as the
pattern is still refused, with a message naming the shape.
- No new `Expr` variant: the optimizer and unparser handle the `OR` chain
unchanged.
Open question for reviewers: Snowflake returns NULL when any argument is
NULL, so `'ab' LIKE ANY ('a%', NULL)` is NULL there and `true` here. An `OR`
chain cannot express Snowflake's rule. Is a per-dialect rule wanted?
## What is the testing strategy for this PR?
- `strings.slt`: `LIKE ANY`, `NOT LIKE ANY`, `NOT x LIKE ANY`, `ILIKE ANY`,
`NOT ILIKE ANY`, `ESCAPE`, the NULL cases, an `EXPLAIN` of the `OR` chain, an
`EXPLAIN` showing `upper(s)` computed once as a common subexpression, the
refused volatile subject, 2 refused subquery subjects with `random()` in their
plan, an accepted non-volatile subquery subject, and the 3 refused pattern
shapes.
- `sql_integration.rs`: `select_like_any_plans_as_or_chain`.
- The new queries fail on `main`.
## Are there any user-facing changes?
Yes: `LIKE ANY` and `ILIKE ANY` with a pattern list now plan. `LIKE` has no
section in the operators guide, so no docs were added. No public API change.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
