[GitHub] [spark] MaxGekk commented on a change in pull request #27477: [SPARK-30724][SQL] Support 'LIKE ANY' and 'LIKE ALL' operators

[GitBox <gi...@apache.org>]

MaxGekk commented on a change in pull request #27477:
URL: https://github.com/apache/spark/pull/27477#discussion_r707112470



##########
File path: sql/core/src/test/resources/sql-tests/inputs/like-all.sql
##########
@@ -0,0 +1,39 @@
+CREATE OR REPLACE TEMPORARY VIEW like_all_table AS SELECT * FROM (VALUES
+  ('google', '%oo%'),
+  ('facebook', '%oo%'),
+  ('linkedin', '%in'))
+  as t1(company, pat);
+
+SELECT company FROM like_all_table WHERE company LIKE ALL ('%oo%', '%go%');
+
+SELECT company FROM like_all_table WHERE company LIKE ALL ('microsoft', '%yoo%');
+
+SELECT 
+    company,
+    CASE
+        WHEN company LIKE ALL ('%oo%', '%go%') THEN 'Y'
+        ELSE 'N'
+    END AS is_available,
+    CASE
+        WHEN company LIKE ALL ('%oo%', 'go%') OR company LIKE ALL ('%in', 'ms%') THEN 'Y'
+        ELSE 'N'
+    END AS mix
+FROM like_all_table ;
+
+-- Mix test with constant pattern and column value
+SELECT company FROM like_all_table WHERE company LIKE ALL ('%oo%', pat);
+
+-- not like all test
+SELECT company FROM like_all_table WHERE company NOT LIKE ALL ('%oo%', '%in', 'fa%');
+SELECT company FROM like_all_table WHERE company NOT LIKE ALL ('microsoft', '%yoo%');
+SELECT company FROM like_all_table WHERE company NOT LIKE ALL ('%oo%', 'fa%');
+SELECT company FROM like_all_table WHERE NOT company LIKE ALL ('%oo%', 'fa%');
+
+-- null test
+SELECT company FROM like_all_table WHERE company LIKE ALL ('%oo%', NULL);
+SELECT company FROM like_all_table WHERE company NOT LIKE ALL ('%oo%', NULL);
+SELECT company FROM like_all_table WHERE company LIKE ALL (NULL, NULL);
+SELECT company FROM like_all_table WHERE company NOT LIKE ALL (NULL, NULL);
+
+-- negative case
+SELECT company FROM like_any_table WHERE company LIKE ALL ();

Review comment:
       Is using of non-existing table intentional? I guess the purpose was to check `LIKE ALL ()`.




-- 
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: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org