This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 a83f2f81f9 [CALCITE-6663] Support SPLIT_PART function for PostgreSql
a83f2f81f9 is described below
commit a83f2f81f9275d670e7df86a525aef613af72366
Author: qzhu <[email protected]>
AuthorDate: Mon Nov 4 10:53:44 2024 +0800
[CALCITE-6663] Support SPLIT_PART function for PostgreSql
---
babel/src/test/resources/sql/postgresql.iq | 11 ++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 2 ++
.../org/apache/calcite/runtime/SqlFunctions.java | 23 +++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 8 ++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 ++
.../org/apache/calcite/test/SqlFunctionsTest.java | 16 +++++++++++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 33 ++++++++++++++++++++++
8 files changed, 96 insertions(+)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index af6b09744c..7cea0ab532 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -39,6 +39,17 @@ EXPR$0
false
!ok
+#Test string function split_part
+select split_part('abc~@~def~@~ghi', '~@~', 2);
+EXPR$0
+def
+!ok
+
+select split_part('abc,def,ghi,jkl', ',', -2);
+EXPR$0
+ghi
+!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 2b21dc3499..21bca972c4 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
@@ -294,6 +294,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX_SPARK;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPLIT;
+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.STR_TO_MAP;
@@ -746,6 +747,7 @@ public class RexImpTable {
BuiltInMethod.ARRAY_REVERSE.method);
defineMethod(LEVENSHTEIN, BuiltInMethod.LEVENSHTEIN.method,
NullPolicy.STRICT);
defineMethod(SPLIT, BuiltInMethod.SPLIT.method, NullPolicy.STRICT);
+ defineMethod(SPLIT_PART, BuiltInMethod.SPLIT_PART.method,
NullPolicy.STRICT);
defineReflective(PARSE_URL, BuiltInMethod.PARSE_URL2.method,
BuiltInMethod.PARSE_URL3.method);
defineReflective(REGEXP, BuiltInMethod.RLIKE.method);
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 09e3a41d65..c9e7eb7c76 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -914,6 +914,29 @@ public class SqlFunctions {
}
}
+
+ /** SQL {@code SPLIT_PART(string, string, int)} function. */
+ public static String splitPart(String s, String delimiter, int n) {
+ if (Strings.isNullOrEmpty(s) || Strings.isNullOrEmpty(delimiter)) {
+ return "";
+ }
+
+ String[] parts = s.split(delimiter, -1);
+ int partCount = parts.length;
+
+ if (n < 0) {
+ n = partCount + n + 1;
+ }
+
+ if (n <= 0 || n > partCount) {
+ return "";
+ }
+
+ return parts[n - 1];
+ }
+
+
+
/** SQL {@code SPLIT(string)} function. */
public static List<String> split(String s) {
return split(s, ",");
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 edde88f758..8b16906bd8 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
@@ -389,6 +389,14 @@ public abstract class SqlLibraryOperators {
return operatorBinding.getTypeFactory().createSqlType(typeName);
}
+ /** The "SPLIT_PART(string, delimiter, n)" function. */
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
+ public static final SqlFunction SPLIT_PART =
+ SqlBasicFunction.create("SPLIT_PART",
+ ReturnTypes.VARCHAR_NULLABLE,
+ OperandTypes.STRING_STRING_INTEGER,
+ SqlFunctionCategory.STRING);
+
/** The "STRPOS(string, substring)" function. */
@LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
public static final SqlFunction STRPOS = new SqlPositionFunction("STRPOS");
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 bedea92211..2bad29df0f 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -414,6 +414,8 @@ public enum BuiltInMethod {
REPEAT(SqlFunctions.class, "repeat", String.class, int.class),
SPACE(SqlFunctions.class, "space", int.class),
SPLIT(SqlFunctions.class, "split", String.class),
+ SPLIT_PART(SqlFunctions.class, "splitPart", String.class, String.class,
+ int.class),
SOUNDEX(SqlFunctions.class, "soundex", String.class),
SOUNDEX_SPARK(SqlFunctions.class, "soundexSpark", String.class),
STRCMP(SqlFunctions.class, "strcmp", String.class, String.class),
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 93da75dec5..253deb08dc 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -1055,6 +1055,22 @@ class SqlFunctionsTest {
is(list(s, racad, r, empty)));
}
+ @Test void testSplitPart() {
+ assertThat(SqlFunctions.splitPart("abc~@~def~@~ghi", "~@~", 2), is("def"));
+ assertThat(SqlFunctions.splitPart("abc,def,ghi,jkl", ",", -2), is("ghi"));
+
+ assertThat(SqlFunctions.splitPart("abc,,ghi", ",", 2), is(""));
+ assertThat(SqlFunctions.splitPart("", ",", 1), is(""));
+ assertThat(SqlFunctions.splitPart("abc", "", 1), is(""));
+
+ assertThat(SqlFunctions.splitPart(null, ",", 1), is(""));
+ assertThat(SqlFunctions.splitPart("abc,def", null, 1), is(""));
+ assertThat(SqlFunctions.splitPart("abc,def", ",", 0), is(""));
+
+ assertThat(SqlFunctions.splitPart("abc,def", ",", 3), is(""));
+ assertThat(SqlFunctions.splitPart("abc,def", ",", -3), is(""));
+ }
+
@Test void testByteString() {
final byte[] bytes = {(byte) 0xAB, (byte) 0xFF};
final ByteString byteString = new ByteString(bytes);
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 9924940c59..d26e4710dd 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2927,6 +2927,7 @@ In the following:
| s | SOUNDEX(string) | Returns the phonetic
representation of *string*; return original *string* if *string* is encoded
with multi-byte encoding such as UTF-8
| m s | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
| b | SPLIT(string [, delimiter ]) | Returns the string
array of *string* split at *delimiter* (if omitted, default is comma). If the
*string* is empty it returns an empty array, otherwise, if the *delimiter* is
empty, it returns an array containing the original *string*.
+| p | SPLIT_PART(string, delimiter, n) | Returns the *n*th field
in *string* using *delimiter*; returns empty string if *n* is less than 1 or
greater than the number of fields, and the n can be negative to count from the
end.
| f s | STARTSWITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
| b p | STARTS_WITH(string1, string2) | Equivalent to
`STARTSWITH(string1, string2)`
| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller than the first one
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 5ba92aa601..6d46affa08 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -10901,6 +10901,39 @@ public class SqlOperatorTest {
+ "requires extra delimiter argument", false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6663";>[CALCITE-6663]
+ * Support SPLIT_PART function for PostgreSql </a>.
+ */
+ @Test void testSplitPartFunction() {
+ final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.SPLIT_PART);
+ f0.checkFails("^split_part('hello', ',', 1)^",
+ "No match found for function signature SPLIT_PART\\(<CHARACTER>,
<CHARACTER>, <NUMERIC>\\)",
+ false);
+
+ final SqlOperatorFixture f1 = fixture().withLibrary(SqlLibrary.REDSHIFT);
+ f1.checkFails("^split_part('hello', ',', 1)^",
+ "No match found for function signature SPLIT_PART\\(<CHARACTER>,
<CHARACTER>, <NUMERIC>\\)",
+ false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.POSTGRESQL);
+
+ f.checkScalar("SPLIT_PART('abc~@~def~@~ghi', '~@~', 2)", "def", "VARCHAR
NOT NULL");
+ f.checkScalar("SPLIT_PART('abc,def,ghi,jkl', ',', 3)", "ghi", "VARCHAR NOT
NULL");
+
+ f.checkScalar("SPLIT_PART('abc~@~def~@~ghi', '~@~', -1)", "ghi", "VARCHAR
NOT NULL");
+ f.checkScalar("SPLIT_PART('abc,def,ghi,jkl', ',', -2)", "ghi", "VARCHAR
NOT NULL");
+
+ f.checkScalar("SPLIT_PART('h,e,l,l,o', ',', 7)", "", "VARCHAR NOT NULL");
+ f.checkScalar("SPLIT_PART('h,e,l,l,o', ',', -7)", "", "VARCHAR NOT NULL");
+
+ f.checkScalar("SPLIT_PART('abc,,ghi', ',', 2)", "", "VARCHAR NOT NULL");
+ f.checkScalar("SPLIT_PART('', ',', 1)", "", "VARCHAR NOT NULL");
+
+ f.checkNull("SPLIT_PART(null, ',', 1)");
+ f.checkNull("SPLIT_PART('abc', null, 1)");
+ f.checkNull("SPLIT_PART('a,b,c', ',', null)");
+ }
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5811";>[CALCITE-5811]
