This is an automated email from the ASF dual-hosted git repository.
xiong 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 988caace5c [CALCITE-6951] Add STRING_TO_ARRAY function(enabled in
PostgreSQL Library)
988caace5c is described below
commit 988caace5c423c2c98f60e3beeee6d0e660d3111
Author: Juntao Zhang <[email protected]>
AuthorDate: Fri May 16 09:17:05 2025 +0800
[CALCITE-6951] Add STRING_TO_ARRAY function(enabled in PostgreSQL Library)
---
babel/src/test/resources/sql/postgresql.iq | 51 ++++++++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 33 ++++++++++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 4 ++
.../calcite/sql/fun/SqlLibraryOperators.java | 7 +++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 22 ++++++++++
8 files changed, 122 insertions(+)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index 383dbfad1c..bc1116af2b 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -50,6 +50,57 @@ EXPR$0
ghi
!ok
+# Test string_to_array function
+select string_to_array('a,b,c', ',', 'd');
+EXPR$0
+[a, b, c]
+!ok
+
+select string_to_array('a,b,,d', ',', 'd');
+EXPR$0
+[a, b, , null]
+!ok
+
+select string_to_array('a,b,,d', ',', null);
+EXPR$0
+[a, b, , d]
+!ok
+
+select string_to_array('abc', '');
+EXPR$0
+[abc]
+!ok
+
+select string_to_array('abc', ',', 'abc');
+EXPR$0
+[null]
+!ok
+
+select string_to_array('calcite apache org', ' ', 'apache');
+EXPR$0
+[calcite, null, org]
+!ok
+
+select string_to_array('calcite apache org', ' ');
+EXPR$0
+[calcite, apache, org]
+!ok
+
+select string_to_array('a*c', NULL, '*');
+EXPR$0
+[a, null, c]
+!ok
+
+select string_to_array(NULL, ' ');
+EXPR$0
+null
+!ok
+
+select string_to_array('', '');
+EXPR$0
+[]
+!ok
+
# Test coercion string to array inside INSERT
create table sal_emp (name varchar, pay_by_quarter int array, schedule varchar
array array);
(0 rows modified)
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 d182eff6e3..d6940f7cc3 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
@@ -309,6 +309,7 @@
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPLIT_PART;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STARTS_WITH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STRCMP;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.STRING_TO_ARRAY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STR_TO_MAP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SUBSTRING_INDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SYSDATE;
@@ -1071,6 +1072,7 @@ void populate2() {
defineMethod(ARRAY_SLICE, BuiltInMethod.ARRAY_SLICE.method,
NullPolicy.STRICT);
defineMethod(ARRAY_TO_STRING, BuiltInMethod.ARRAY_TO_STRING.method,
NullPolicy.STRICT);
+ defineMethod(STRING_TO_ARRAY, BuiltInMethod.STRING_TO_ARRAY.method,
NullPolicy.ARG0);
defineMethod(ARRAY_UNION, BuiltInMethod.ARRAY_UNION.method,
NullPolicy.ANY);
defineMethod(ARRAYS_OVERLAP, BuiltInMethod.ARRAYS_OVERLAP.method,
NullPolicy.ANY);
defineMethod(ARRAYS_ZIP, BuiltInMethod.ARRAYS_ZIP.method,
NullPolicy.ANY);
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 768767e9d6..04baae8128 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -6615,6 +6615,39 @@ public static String arrayToString(List list, String
delimiter, @Nullable String
return sb.toString();
}
+ /** SQL {@code STRING_TO_ARRAY(string, delimiter)} function.
+ * Returns a one-dimensional string[] array by splitting the
+ * input string value into subvalues using the specified string value as the
"delimiter".
+ * Optionally, allows a specified string value to be interpreted as NULL. */
+ public static List<@Nullable String> stringToArray(
+ String string, @Nullable String delimiter) {
+ return stringToArray(string, delimiter, null);
+ }
+
+ /** SQL {@code STRING_TO_ARRAY(string, delimiter, nullString)} function. */
+ public static List<@Nullable String> stringToArray(
+ String string, @Nullable String delimiter, @Nullable String nullString) {
+ String[] parts;
+ if (delimiter == null) {
+ parts = string.chars()
+ .mapToObj(c -> Character.toString((char) c))
+ .toArray(String[]::new);
+ } else if (delimiter.isEmpty()) {
+ parts = new String[]{string};
+ } else {
+ parts = string.split(delimiter);
+ }
+ List<@Nullable String> result = new ArrayList<>(parts.length);
+ for (String part : parts) {
+ if (nullString != null && nullString.equals(part)) {
+ result.add(null);
+ } else {
+ result.add(part);
+ }
+ }
+ return result;
+ }
+
/**
* Function that, given a certain List containing single-item structs (i.e.
arrays / lists with
* a single item), builds an Enumerable that returns those single items
inside the structs.
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index a870757ef7..1063b9df90 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -905,6 +905,9 @@ public enum SqlKind {
/** {@code STARTS_WITH} function. */
STARTS_WITH,
+ /** {@code STRING_TO_ARRAY} function (PostgreSQL semantics). */
+ STRING_TO_ARRAY,
+
/** Call to a function using JDBC function syntax. */
JDBC_FN,
@@ -1844,6 +1847,7 @@ public SqlKind getFunctionKind() {
case REVERSE:
case REVERSE_SPARK:
case SOUNDEX_SPARK:
+ case STRING_TO_ARRAY:
case SUBSTR_BIG_QUERY:
case SUBSTR_MYSQL:
case SUBSTR_ORACLE:
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 cd1cd08ac6..e3f234a6f0 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
@@ -408,6 +408,13 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
+ /** The "STRING_TO_ARRAY(string, delimiter [, null_string ])" function
(PostgreSQL). */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlFunction STRING_TO_ARRAY =
+ SqlBasicFunction.create(SqlKind.STRING_TO_ARRAY,
+ ReturnTypes.TO_ARRAY_NULLABLE,
+ OperandTypes.STRING_STRING_OPTIONAL_STRING);
+
/** The "ENDS_WITH(value1, value2)" function (BigQuery). */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlBasicFunction ENDS_WITH =
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index ebfa61359b..3f472e382e 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -403,6 +403,8 @@ public enum BuiltInMethod {
LOWER(SqlFunctions.class, "lower", String.class),
ARRAY_TO_STRING(SqlFunctions.class, "arrayToString", List.class,
String.class),
+ STRING_TO_ARRAY(SqlFunctions.class, "stringToArray", String.class,
String.class,
+ String.class),
SROUND(SqlFunctions.class, "sround", long.class),
STRUNCATE(SqlFunctions.class, "struncate", long.class),
ASCII(SqlFunctions.class, "ascii", String.class),
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 3a994d2460..1c55f4dd2b 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2945,6 +2945,7 @@ ### Dialect-specific Operators
| s | MAP_FROM_ENTRIES(arrayOfRows) | Returns a map created
from an arrays of row with two fields. Note that the number of fields in a row
must be 2. Note that calcite is using the LAST_WIN strategy
| s | STR_TO_MAP(string [, stringDelimiter [, keyValueDelimiter]]) | Returns a
map after splitting the *string* into key/value pairs using delimiters. Default
delimiters are ',' for *stringDelimiter* and ':' for *keyValueDelimiter*. Note
that calcite is using the LAST_WIN strategy
| s | SUBSTRING_INDEX(string, delim, count) | Returns the substring
from *string* before *count* occurrences of the delimiter *delim*. If *count*
is positive, everything to the left of the final delimiter (counting from the
left) is returned. If *count* is negative, everything to the right of the final
delimiter (counting from the right) is returned. The function substring_index
performs a case-sensitive match when searching for *delim*.
+| p r | STRING_TO_ARRAY(string, delimiter [, nullString ]) | Returns a
one-dimensional string[] array by splitting the input string value into
subvalues using the specified string value as the "delimiter". Optionally,
allows a specified string value to be interpreted as NULL.
| b m p r s h | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in
the connection's locale, of the month in *datetime*; for example, for a locale
of en, it will return 'February' for both DATE '2020-02-10' and TIMESTAMP
'2020-02-10 10:10:10', and for a locale of zh, it will return '二月'
| o r s | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
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 28a9b633a1..9313823fdc 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -12487,6 +12487,28 @@ private static void
checkStringAggFuncFails(SqlOperatorFixture f) {
false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6951";>[CALCITE-6951]
+ * Add STRING_TO_ARRAY function(enabled in PostgreSQL Library)</a>. */
+ @Test void testStringToArrayFunc() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.STRING_TO_ARRAY);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.POSTGRESQL);
+ f.checkNull("string_to_array(NULL, ' ')");
+ f.checkScalar("string_to_array('', '')", "[]",
+ "CHAR(0) NOT NULL ARRAY NOT NULL");
+ f.checkScalar("string_to_array('ab', NULL)",
+ "[a, b]", "CHAR(2) NOT NULL ARRAY");
+ f.checkScalar("string_to_array('www apache org', ' ')",
+ "[www, apache, org]", "CHAR(14) NOT NULL ARRAY NOT NULL");
+ f.checkScalar("string_to_array('www apache org', ' ', 'apache')",
+ "[www, null, org]", "CHAR(14) NOT NULL ARRAY NOT NULL");
+ f.checkFails("^string_to_array(NULL)^",
+ "Invalid number of arguments to function 'STRING_TO_ARRAY'.*", false);
+ f.checkFails("^string_to_array(NULL, NULL, NULL, NULL)^",
+ "Invalid number of arguments to function 'STRING_TO_ARRAY'.*", false);
+ }
+
@Test void testGroupConcatFunc() {
final SqlOperatorFixture f = fixture();
checkGroupConcatFunc(f.withLibrary(SqlLibrary.MYSQL));
