kosiew commented on code in PR #26468:
URL: https://github.com/apache/datafusion/pull/26468#discussion_r4492201365
##########
datafusion/sqllogictest/test_files/strings.slt:
##########
@@ -361,6 +361,149 @@ p2
p2e1
p2m1e1
+# LIKE ANY
+query T rowsort
+SELECT s FROM test WHERE s LIKE ANY ('p1%', '%m1%');
+----
+P1m1e1
+p1
+p1e1
+p1m1e1
+p2m1e1
+
+query T rowsort
+SELECT s FROM test WHERE s LIKE ANY ('e1');
+----
+e1
+
+# NOT applies to each pattern: true when s fails to match at least one
+query T rowsort
+SELECT s FROM test WHERE s NOT LIKE ANY ('p%', '%e1');
+----
+P1
+P1e1
+P1m1e1
+e1
+p1
+p2
+
+# NOT before the subject negates the whole match
+query T rowsort
+SELECT s FROM test WHERE NOT s LIKE ANY ('p%', '%e1');
+----
+P1
+
+# ILIKE ANY
+query T rowsort
+SELECT s FROM test WHERE s ILIKE ANY ('p1', 'E%');
+----
+P1
+e1
+p1
+
+query T rowsort
+SELECT s FROM test WHERE s NOT ILIKE ANY ('p%', '%e1');
+----
+P1
+e1
+p1
+p2
+
+query B
+SELECT 'a_c' LIKE ANY ('a\_c', 'x%') ESCAPE '\';
+----
+true
+
+# ESCAPE applies to every pattern
+query TT
+EXPLAIN SELECT s FROM test WHERE s LIKE ANY ('p\_%', '%e1') ESCAPE '\';
+----
+logical_plan
+01)Filter: test.s LIKE Utf8View("p\_%") ESCAPE '\' OR test.s LIKE
Utf8View("%e1") ESCAPE '\'
+02)--TableScan: test projection=[s]
+physical_plan
+01)FilterExec: s@0 LIKE p\_% OR s@0 LIKE %e1
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
+# A NULL pattern gives NULL only when no other pattern matches
+query BBBB
+SELECT 'ab' LIKE ANY ('a%', NULL), 'ab' LIKE ANY ('x%', NULL), 'ab' LIKE ANY
('x%', 'y%'), NULL LIKE ANY ('a%', 'c%');
+----
+true NULL false NULL
+
+query BBB
+SELECT 'ab' NOT LIKE ANY ('a%', 'c%'), 'ab' NOT LIKE ANY ('a%', '%b'), NOT
'ab' LIKE ANY ('a%', 'c%');
Review Comment:
Could we optionally add NULL cases for negation? For example, `'ab' NOT LIKE
ANY ('a%', NULL)` should return NULL, `'ab' NOT LIKE ANY ('x%', NULL)` should
return true, and `NOT 'ab' LIKE ANY ('a%', NULL)` should return false. Existing
tests cover these behaviors separately, but combining them would strengthen
regression coverage.
##########
docs/source/user-guide/sql/operators.md:
##########
@@ -617,6 +619,89 @@ applies the opposite (POSIX) convention to offsets spelled
this way.
[`timezone(tz, expression)`]: scalar_functions.md#timezone
+(op_like_any)=
+
+### `LIKE ANY`
+
+Like Any: `expression LIKE ANY (pattern1, ..., patternN)` is true when
+`expression` matches at least one pattern. It is the same as
+`expression LIKE pattern1 OR ... OR expression LIKE patternN`, following Spark.
+
+```sql
+> SELECT 'datafusion' LIKE ANY ('dat%', '%sql') AS like_any;
++----------+
+| like_any |
++----------+
+| true |
++----------+
+```
+
+`NOT` before `LIKE` applies to each pattern, so `expression NOT LIKE ANY (...)`
+is true when `expression` fails to match at least one pattern. `NOT` before the
+expression negates the whole match:
+
+```sql
+> SELECT
+ 'datafusion' NOT LIKE ANY ('dat%', '%sql') AS per_pattern,
+ NOT 'datafusion' LIKE ANY ('dat%', '%sql') AS whole_match;
++-------------+-------------+
+| per_pattern | whole_match |
++-------------+-------------+
+| true | false |
++-------------+-------------+
+```
+
+An `ESCAPE` character applies to every pattern:
+
+```sql
+> SELECT 'data_fusion' LIKE ANY ('data\_%', '%sql') ESCAPE '\' AS escaped;
++---------+
+| escaped |
++---------+
+| true |
++---------+
+```
+
+A NULL pattern gives NULL only when no other pattern matches. A NULL
+expression gives NULL:
+
+```sql
+> SELECT
+ 'datafusion' LIKE ANY ('dat%', NULL) AS one_match,
+ 'datafusion' LIKE ANY ('%sql', NULL) AS no_match,
+ NULL LIKE ANY ('dat%', '%sql') AS null_subject;
++-----------+----------+--------------+
+| one_match | no_match | null_subject |
++-----------+----------+--------------+
+| true | NULL | NULL |
++-----------+----------+--------------+
+```
+
+Snowflake differs here: its `LIKE ANY` returns NULL when any argument is NULL,
+so `'datafusion' LIKE ANY ('dat%', NULL)` is NULL in Snowflake.
+
+The patterns must be a parenthesized list. An array of patterns, such as
+`ARRAY['dat%', '%sql']` or `make_array('dat%', '%sql')`, and a subquery are not
+supported. A volatile expression, such as `CAST(random() AS VARCHAR)`, is not
Review Comment:
Could we clarify that the volatility restriction applies specifically to the
left-hand expression, not the patterns, and that the unsupported subquery form
is a subquery supplying the pattern list? Nonvolatile scalar-subquery subjects
are supported, so the current wording could imply broader restrictions than the
planner enforces.
--
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]
