This is an automated email from the ASF dual-hosted git repository.
tanner pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 9404c8c99a [CALCITE-5873] Add REGEXP_CONTAINS function (enabled in
BigQuery library)
9404c8c99a is described below
commit 9404c8c99ab6f847b8c788476f0047368aba7c49
Author: Jerin John <[email protected]>
AuthorDate: Tue Jul 25 15:29:31 2023 -0700
[CALCITE-5873] Add REGEXP_CONTAINS function (enabled in BigQuery library)
---
babel/src/test/resources/sql/big-query.iq | 65 +++++++++++++++++++++-
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 15 +++++
.../calcite/sql/fun/SqlLibraryOperators.java | 8 +++
.../calcite/runtime/CalciteResource.properties | 1 +
.../org/apache/calcite/test/SqlFunctionsTest.java | 30 ++++++++++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 21 +++++++
9 files changed, 145 insertions(+), 1 deletion(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index a0e7782a30..82a37386e9 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -717,6 +717,70 @@ SELECT (19 % 19) as result;
!ok
+#####################################################################
+# REGEXP_CONTAINS(value, regexp)
+#
+# Takes two STRING values. Returns TRUE if value is a partial match
+# for the regular expression, regexp.
+# If the regexp argument is invalid, the function returns an error.
+# Uses java.util.regex as a standard for regex processing
+# in Calcite instead of RE2 used by BigQuery/GoogleSQL.
+
+SELECT
+ email,
+ REGEXP_CONTAINS(email, '@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid
+FROM
+ (SELECT
+ ARRAY['[email protected]', '[email protected]', 'www.example.net']
+ AS addresses),
+ UNNEST(addresses) AS email;
++-----------------+----------+
+| email | is_valid |
++-----------------+----------+
+| [email protected] | true |
+| [email protected] | true |
+| www.example.net | false |
++-----------------+----------+
+(3 rows)
+
+!ok
+
+SELECT
+ email,
+ REGEXP_CONTAINS(email, '^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')
+ AS valid_email_address,
+ REGEXP_CONTAINS(email, '^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')
+ AS without_parentheses
+FROM
+ (SELECT
+ ARRAY['[email protected]', '[email protected]', '[email protected]', '[email protected]',
'[email protected]']
+ AS addresses),
+ UNNEST(addresses) AS email;
++----------------+---------------------+---------------------+
+| email | valid_email_address | without_parentheses |
++----------------+---------------------+---------------------+
+| [email protected] | true | true |
+| [email protected] | false | true |
+| [email protected] | true | true |
+| [email protected] | false | true |
+| [email protected] | false | false |
++----------------+---------------------+---------------------+
+(5 rows)
+
+!ok
+
+SELECT REGEXP_CONTAINS('abc def ghi', '(abc');
+Invalid regular expression for REGEXP_CONTAINS: 'Unclosed group near index 4
(abc'
+!error
+
+SELECT REGEXP_CONTAINS('abc def ghi', '[z-a]');
+Invalid regular expression for REGEXP_CONTAINS: 'Illegal character range near
index 3 [z-a] ^'
+!error
+
+SELECT REGEXP_CONTAINS('abc def ghi', '{2,1}');
+Invalid regular expression for REGEXP_CONTAINS: 'Illegal repetition range near
index 4 {2,1} ^'
+!error
+
#####################################################################
# SPLIT
#
@@ -3593,5 +3657,4 @@ FROM items;
!ok
-
# End big-query.iq
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 531a0ce013..7614eb784a 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -215,6 +215,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIME;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_URL;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.POW;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_CONTAINS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
@@ -552,6 +553,7 @@ public class RexImpTable {
defineMethod(LEVENSHTEIN, BuiltInMethod.LEVENSHTEIN.method,
NullPolicy.STRICT);
defineMethod(SPLIT, "split", NullPolicy.STRICT);
defineMethod(PARSE_URL, BuiltInMethod.PARSE_URL.method,
NullPolicy.STRICT);
+ defineMethod(REGEXP_CONTAINS, "regexpContains", NullPolicy.STRICT);
map.put(TRIM, new TrimImplementor());
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index c09c309ccc..bb33ae9cf0 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -1005,6 +1005,9 @@ public interface CalciteResource {
@BaseMessage("Invalid input for JSON_STORAGE_SIZE: ''{0}''")
ExInst<CalciteException> invalidInputForJsonStorageSize(String value);
+ @BaseMessage("Invalid regular expression for REGEXP_CONTAINS: ''{0}''")
+ ExInst<RuntimeException> invalidInputForRegexpContains(String value);
+
@BaseMessage("Invalid input for REGEXP_REPLACE: ''{0}''")
ExInst<CalciteException> invalidInputForRegexpReplace(String value);
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 79def765a2..15fbc472fa 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -110,6 +110,7 @@ import java.util.concurrent.atomic.AtomicLong;
import java.util.function.BinaryOperator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
+import java.util.regex.PatternSyntaxException;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -345,6 +346,20 @@ public class SqlFunctions {
return DigestUtils.sha512Hex(string.getBytes());
}
+ /** SQL {@code REGEXP_CONTAINS(value, regexp)} function.
+ * Throws a runtime exception for invalid regular expressions.*/
+ public static boolean regexpContains(String value, String regex) {
+ try {
+ // Uses java.util.regex as a standard for regex processing
+ // in Calcite instead of RE2 used by BigQuery/GoogleSQL
+ Pattern regexp = Pattern.compile(regex);
+ return regexp.matcher(value).find();
+ } catch (PatternSyntaxException ex) {
+ throw
RESOURCE.invalidInputForRegexpContains(ex.getMessage().replace("\r\n", " ")
+ .replace("\n", " ").replace("\r", " ")).ex();
+ }
+ }
+
/** SQL {@code REGEXP_REPLACE} function with 3 arguments. */
public static String regexpReplace(String s, String regex,
String replacement) {
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index c3bcb53607..6159d1162c 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -478,6 +478,14 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {MYSQL, ORACLE})
public static final SqlFunction REGEXP_REPLACE = new
SqlRegexpReplaceFunction();
+ /** The "REGEXP_CONTAINS(value, regexp)" function.
+ * Returns TRUE if value is a partial match for the regular expression,
regexp. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction REGEXP_CONTAINS =
+ SqlBasicFunction.create("REGEXP_CONTAINS", ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.STRING_STRING,
+ SqlFunctionCategory.STRING);
+
@LibraryOperator(libraries = {MYSQL})
public static final SqlFunction COMPRESS =
SqlBasicFunction.create("COMPRESS",
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 8dfd6fb9f3..8c67a7538b 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -328,6 +328,7 @@ InvalidInputForJsonLength=Invalid input for JSON_LENGTH:
''{0}''
InvalidInputForJsonKeys=Invalid input for JSON_KEYS: ''{0}''
InvalidInputForJsonRemove=Invalid input for JSON_REMOVE: document: ''{0}'',
jsonpath expressions: ''{1}''
InvalidInputForJsonStorageSize=Invalid input for JSON_STORAGE_SIZE: ''{0}''
+InvalidInputForRegexpContains=Invalid regular expression for REGEXP_CONTAINS:
''{0}''
InvalidInputForRegexpReplace=Invalid input for REGEXP_REPLACE: ''{0}''
InvalidInputForJsonInsert=Invalid input for JSON_INSERT: jsonDoc: ''{0}'',
kvs: ''{1}''
InvalidInputForJsonReplace=Invalid input for JSON_REPLACE: jsonDoc: ''{0}'',
kvs: ''{1}''
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index b973d8c2b8..e6eb29b938 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -59,6 +59,7 @@ import static org.apache.calcite.runtime.SqlFunctions.ltrim;
import static org.apache.calcite.runtime.SqlFunctions.md5;
import static org.apache.calcite.runtime.SqlFunctions.position;
import static org.apache.calcite.runtime.SqlFunctions.posixRegex;
+import static org.apache.calcite.runtime.SqlFunctions.regexpContains;
import static org.apache.calcite.runtime.SqlFunctions.regexpReplace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
@@ -234,6 +235,35 @@ class SqlFunctionsTest {
assertThat(posixRegex("abcq", "[[:xdigit:]]", false), is(true));
}
+ @Test void testRegexpContains() {
+ try {
+ regexpContains("abc def ghi", "(abc");
+ fail("'regexp_contains' on an invalid regex input '(abc' is not
possible");
+ } catch (RuntimeException e) {
+ assertThat(
+ e.getMessage(), is("Invalid regular expression for REGEXP_CONTAINS:
'Unclosed "
+ + "group near index 4 (abc'"));
+ }
+
+ try {
+ regexpContains("abc def ghi", "[z-a]");
+ fail("'regexp_contains' on an invalid regex input '[z-a]' is not
possible");
+ } catch (RuntimeException e) {
+ assertThat(
+ e.getMessage(), is("Invalid regular expression for REGEXP_CONTAINS:
'Illegal "
+ + "character range near index" + " 3 [z-a] ^'"));
+ }
+
+ try {
+ regexpContains("abc def ghi", "{2,1}");
+ fail("'regexp_contains' on an invalid regex input '{2,1}' is not
possible");
+ } catch (RuntimeException e) {
+ assertThat(
+ e.getMessage(), is("Invalid regular expression for REGEXP_CONTAINS:
'Illegal "
+ + "repetition range near " + "index 4 {2,1} ^'"));
+ }
+ }
+
@Test void testRegexpReplace() {
assertThat(regexpReplace("a b c", "b", "X"), is("a X c"));
assertThat(regexpReplace("abc def ghi", "[g-z]+", "X"), is("abc def X"));
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index abd151131f..ef80f5ea03 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2778,6 +2778,7 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b | PARSE_TIMESTAMP(format, string[, timeZone]) | Uses format specified
by *format* to convert *string* representation of timestamp to a TIMESTAMP WITH
LOCAL TIME ZONE value in *timeZone*
| h s | PARSE_URL(urlString, partToExtract [, keyToExtract] ) | Returns the
specified *partToExtract* from the *urlString*. Valid values for
*partToExtract* include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and
USERINFO. *keyToExtract* specifies which query to extract
| b | POW(numeric1, numeric2) | Returns *numeric1*
raised to the power *numeric2*
+| b | REGEXP_CONTAINS(string, regexp) | Returns whether
*string* is a partial match for the *regexp*
| m o | REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [,
matchType]]]) | Replaces all substrings of *string* that match *regexp* with
*rep* at the starting *pos* in expr (if omitted, the default is 1),
*occurrence* means which occurrence of a match to search for (if omitted, the
default is 1), *matchType* specifies how to perform matching
| b m p | REPEAT(string, integer) | Returns a string
consisting of *string* repeated of *integer* times; returns an empty string if
*integer* is less than 1
| b m | REVERSE(string) | Returns *string* with
the order of the characters reversed
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 35f23a8d3f..82a2aa56be 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4547,6 +4547,27 @@ public class SqlOperatorTest {
f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL), 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('[email protected]',
'@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+')", true);
+ f.checkBoolean("regexp_contains('[email protected]',
'@[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('[email protected]',
'@[a-zA-Z0-9-]+\\\\.[a-zA-Z0-9-.]+')");
+ f.checkQuery("select regexp_contains('55as56664as422', '\\d{10}')");
+
+ f.checkNull("regexp_contains('abc def ghi', cast(null as varchar))");
+ f.checkNull("regexp_contains(cast(null as varchar), 'abc')");
+ f.checkNull("regexp_contains(cast(null as varchar), cast(null as
varchar))");
+ }
+
@Test void testRegexpReplaceFunc() {
final SqlOperatorFixture f0 = fixture();
final Consumer<SqlOperatorFixture> consumer = f -> {
