[GitHub] [calcite] tanclary commented on a diff in pull request #3338: [CALCITE-5873/CALCITE-5858] Add REGEXP_CONTAINS function for BigQuery Library

[via GitHub]

tanclary commented on code in PR #3338:
URL: https://github.com/apache/calcite/pull/3338#discussion_r1275535907


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -4438,6 +4438,31 @@ private static void checkIf(SqlOperatorFixture f) {
     f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.ORACLE), consumer);
   }
 
+  @Test
+  void testRegexpContainsFunc() {
+    final SqlOperatorFixture f = 
fixture().setFor(SqlLibraryOperators.REGEXP_CONTAINS)
+        .withLibrary(SqlLibrary.BIG_QUERY);
+    f.checkBoolean("regexp_contains('abc def ghi', 'abc')", true);
+    f.checkBoolean("regexp_contains('abc def ghi', '[a-z]+')", true);
+    f.checkBoolean("regexp_contains('f...@bar.com', 
'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+')", true);
+    f.checkBoolean("regexp_contains('foo@.com', 
'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+')", false);
+    f.checkBoolean("regexp_contains('5556664422', '^\\d{10}$')", true);
+    f.checkBoolean("regexp_contains('11555666442233', '^\\d{10}$')", false);
+    f.checkBoolean("regexp_contains('55566644221133', '\\d{10}')", true);
+    f.checkBoolean("regexp_contains('55as56664as422', '\\d{10}')", false);
+
+    f.checkQuery("select regexp_contains('abc def ghi', 'abc')");
+    f.checkQuery("select regexp_contains('f...@bar.com', 
'@[a-zA-Z0-9-]+\\\\.[a-zA-Z0-9-.]+')");
+    f.checkQuery("select regexp_contains('55as56664as422', '\\d{10}')");
+
+    f.checkFails("regexp_contains('abc def ghi', '(abc')",
+        "Invalid regex input for REGEXP_CONTAINS: '(abc'", true);
+    f.checkFails("regexp_contains('abc def ghi', '{3}')",
+        "Invalid regex input for REGEXP_CONTAINS: '{3}'", true);
+    f.checkFails("regexp_contains('abc def ghi', '[z-a]')",

Review Comment:
   That's right. It's still helpful to add .checkNull() tests to ensure that if 
either (or both) arguments is null that, that null is returned. This is useful 
because not all functions follow that behavior. In this case however, based on 
a couple of quick queries I ran against BigQuery, if either or both arguments 
is null the function should return null. Hope this helps.



##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -4438,6 +4438,31 @@ private static void checkIf(SqlOperatorFixture f) {
     f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.ORACLE), consumer);
   }
 
+  @Test
+  void testRegexpContainsFunc() {
+    final SqlOperatorFixture f = 
fixture().setFor(SqlLibraryOperators.REGEXP_CONTAINS)
+        .withLibrary(SqlLibrary.BIG_QUERY);
+    f.checkBoolean("regexp_contains('abc def ghi', 'abc')", true);
+    f.checkBoolean("regexp_contains('abc def ghi', '[a-z]+')", true);
+    f.checkBoolean("regexp_contains('f...@bar.com', 
'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+')", true);
+    f.checkBoolean("regexp_contains('foo@.com', 
'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+')", false);
+    f.checkBoolean("regexp_contains('5556664422', '^\\d{10}$')", true);
+    f.checkBoolean("regexp_contains('11555666442233', '^\\d{10}$')", false);
+    f.checkBoolean("regexp_contains('55566644221133', '\\d{10}')", true);
+    f.checkBoolean("regexp_contains('55as56664as422', '\\d{10}')", false);
+
+    f.checkQuery("select regexp_contains('abc def ghi', 'abc')");
+    f.checkQuery("select regexp_contains('f...@bar.com', 
'@[a-zA-Z0-9-]+\\\\.[a-zA-Z0-9-.]+')");
+    f.checkQuery("select regexp_contains('55as56664as422', '\\d{10}')");
+
+    f.checkFails("regexp_contains('abc def ghi', '(abc')",
+        "Invalid regex input for REGEXP_CONTAINS: '(abc'", true);
+    f.checkFails("regexp_contains('abc def ghi', '{3}')",
+        "Invalid regex input for REGEXP_CONTAINS: '{3}'", true);
+    f.checkFails("regexp_contains('abc def ghi', '[z-a]')",

Review Comment:
   That's right. It's still helpful to add .checkNull() tests to ensure that if 
either (or both) arguments is null that null is returned. This is useful 
because not all functions follow that behavior. In this case however, based on 
a couple of quick queries I ran against BigQuery, if either or both arguments 
is null the function should return null. Hope this helps.



-- 
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: commits-unsubscr...@calcite.apache.org

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