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 f68af8cd9f [CALCITE-6445] Add REVERSE function (enabled in Spark
library)
f68af8cd9f is described below
commit f68af8cd9fd2bcab302d9150f7f246b545b8a85f
Author: YiwenWu <[email protected]>
AuthorDate: Mon Jul 8 13:11:03 2024 +0800
[CALCITE-6445] Add REVERSE function (enabled in Spark library)
---
babel/src/test/resources/sql/spark.iq | 93 +++++++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 3 +
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../calcite/sql/fun/SqlLibraryOperators.java | 9 ++
.../org/apache/calcite/sql/type/ReturnTypes.java | 14 ++++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 95 +++++++++++++++-------
7 files changed, 188 insertions(+), 30 deletions(-)
diff --git a/babel/src/test/resources/sql/spark.iq
b/babel/src/test/resources/sql/spark.iq
index 97ba83664f..668d15409e 100644
--- a/babel/src/test/resources/sql/spark.iq
+++ b/babel/src/test/resources/sql/spark.iq
@@ -355,4 +355,97 @@ EXPR$0
!ok
+#####################################################################
+# REVERSE
+#
+# REVERSE(str | array)
+# Returns a reversed string or an array with reverse order of elements.
+#
+# Returns STRING or ARRAY
+
+SELECT REVERSE('Spark SQL');
+EXPR$0
+LQS krapS
+!ok
+
+SELECT REVERSE('#c@b!a');
+EXPR$0
+a!b@c#
+!ok
+
+SELECT REVERSE(5);
+EXPR$0
+5
+!ok
+
+SELECT REVERSE(array(2, 1, 4, 3));
+EXPR$0
+[3, 4, 1, 2]
+!ok
+
+SELECT REVERSE(NULL);
+EXPR$0
+null
+!ok
+
+SELECT REVERSE(cast(NULL as tinyint));
+EXPR$0
+null
+!ok
+
+SELECT REVERSE(array(NULL));
+EXPR$0
+[null]
+!ok
+
+SELECT REVERSE(cast(NULL as integer array));
+EXPR$0
+null
+!ok
+
+SELECT REVERSE(array(cast(NULL as tinyint)));
+EXPR$0
+[null]
+!ok
+
+SELECT REVERSE('');
+EXPR$0
+
+!ok
+
+SELECT REVERSE(array(cast(1 as tinyint), 2));
+EXPR$0
+[2, 1]
+!ok
+
+SELECT REVERSE(array(2.1, 2.2));
+EXPR$0
+[2.2, 2.1]
+!ok
+
+SELECT REVERSE(array(2.1, NULL));
+EXPR$0
+[null, 2.1]
+!ok
+
+SELECT REVERSE(array(2, CAST(2.2 AS double)));
+EXPR$0
+[2.2, 2.0]
+!ok
+
+SELECT REVERSE(array(2, CAST(NULL AS double)));
+EXPR$0
+[null, 2.0]
+!ok
+
+SELECT REVERSE(array(CAST(2.1 as decimal(17)), 2.1111111111111119));
+EXPR$0
+[2.1111111111111119, 2.1]
+!ok
+
+SELECT REVERSE(array(CAST(2.1 as double), 2.1111111111111119));
+EXPR$0
+[2.111111111111112, 2.1]
+!ok
+
# End spark.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 3e6088d626..da7ef8eaf6 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
@@ -249,6 +249,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_LIKE;
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;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE_SPARK;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RLIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD;
@@ -611,6 +612,8 @@ public class RexImpTable {
defineMethod(SOUNDEX_SPARK, BuiltInMethod.SOUNDEX_SPARK.method,
NullPolicy.STRICT);
defineMethod(DIFFERENCE, BuiltInMethod.DIFFERENCE.method,
NullPolicy.STRICT);
defineMethod(REVERSE, BuiltInMethod.REVERSE.method, NullPolicy.STRICT);
+ defineReflective(REVERSE_SPARK, BuiltInMethod.REVERSE.method,
+ BuiltInMethod.ARRAY_REVERSE.method);
defineMethod(LEVENSHTEIN, BuiltInMethod.LEVENSHTEIN.method,
NullPolicy.STRICT);
defineMethod(SPLIT, BuiltInMethod.SPLIT.method, NullPolicy.STRICT);
defineReflective(PARSE_URL, BuiltInMethod.PARSE_URL2.method,
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 9f64e13c5a..808c85c368 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -810,6 +810,9 @@ public enum SqlKind {
/** {@code REVERSE} function (SQL Server, MySQL). */
REVERSE,
+ /** {@code REVERSE} function (Spark semantics). */
+ REVERSE_SPARK,
+
/** {@code SOUNDEX} function (Spark semantics). */
SOUNDEX_SPARK,
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 5c876e0614..061a1025ae 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
@@ -1639,6 +1639,15 @@ public abstract class SqlLibraryOperators {
OperandTypes.CHARACTER)
.withFunctionType(SqlFunctionCategory.STRING);
+ /** The "REVERSE(string|array)" function. */
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction REVERSE_SPARK =
+ SqlBasicFunction.create(SqlKind.REVERSE,
+ ReturnTypes.ARG0_ARRAY_NULLABLE_VARYING,
+ OperandTypes.CHARACTER.or(OperandTypes.ARRAY))
+ .withFunctionType(SqlFunctionCategory.STRING)
+ .withKind(SqlKind.REVERSE_SPARK);
+
/** The "LEVENSHTEIN(string1, string2)" function. */
@LibraryOperator(libraries = {HIVE, SPARK})
public static final SqlFunction LEVENSHTEIN =
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index 40b5353514..f8aaff072c 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -178,6 +178,20 @@ public abstract class ReturnTypes {
public static final SqlReturnTypeInference ARG0_NULLABLE =
ARG0.andThen(SqlTypeTransforms.TO_NULLABLE);
+ /**
+ * Type-inference strategy that determines the return type based on the
first argument.
+ * If the first argument is an array, the return type is consistent with
{@link #ARG0_NULLABLE}.
+ * If the first argument is not an array,
+ * the return type is consistent with {@link #ARG0_NULLABLE_VARYING}.
+ */
+ public static final SqlReturnTypeInference ARG0_ARRAY_NULLABLE_VARYING =
opBinding -> {
+ SqlTypeName op = opBinding.getOperandType(0).getSqlTypeName();
+ if (op == SqlTypeName.ARRAY) {
+ return ARG0_NULLABLE.inferReturnType(opBinding);
+ }
+ return ARG0_NULLABLE_VARYING.inferReturnType(opBinding);
+ };
+
/**
* Type-inference strategy whereby the result type of a call is the type of
* the operand #0 (0-based). If the operand #0 (0-based) is nullable, the
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index dc80e67ed9..3e0a51eb45 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2851,6 +2851,7 @@ In the following:
| b | REGEXP_SUBSTR(string, regexp [, position [, occurrence]]) | Synonym for
REGEXP_EXTRACT
| b m p r s | 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
+| s | REVERSE(string | array) | Returns *string* with
the characters in reverse order or array with elements in reverse order
| b m p r s | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
| h m s | string1 RLIKE string2 | Whether *string1*
matches regex pattern *string2* (similar to `LIKE`, but uses Java regex)
| h m s | string1 NOT RLIKE string2 | Whether *string1* does
not match regex pattern *string2* (similar to `NOT LIKE`, but uses Java regex)
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 3bea58f537..65e1a1cb96 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5570,22 +5570,30 @@ public class SqlOperatorTest {
}
@Test void testReverseFunc() {
- final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.REVERSE);
+ final SqlFunction func = SqlLibraryOperators.REVERSE;
+ final SqlOperatorFixture f0 = fixture().setFor(func);
f0.checkFails("^reverse('abc')^",
"No match found for function signature REVERSE\\(<CHARACTER>\\)",
false);
+ checReverseFunc(f0, func, list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL));
+ }
+
+ void checReverseFunc(SqlOperatorFixture f0, SqlFunction function,
+ Iterable<? extends SqlLibrary> libraries) {
+ final String fn = function.getName();
final Consumer<SqlOperatorFixture> consumer = f -> {
- f.checkString("reverse('')", "", "VARCHAR(0) NOT NULL");
- f.checkString("reverse('123')", "321", "VARCHAR(3) NOT NULL");
- f.checkString("reverse('abc')", "cba", "VARCHAR(3) NOT NULL");
- f.checkString("reverse('ABC')", "CBA", "VARCHAR(3) NOT NULL");
- f.checkString("reverse('Hello World')", "dlroW olleH",
+ f.checkString(fn + "('')", "", "VARCHAR(0) NOT NULL");
+ f.checkString(fn + "('123')", "321", "VARCHAR(3) NOT NULL");
+ f.checkString(fn + "('abc')", "cba", "VARCHAR(3) NOT NULL");
+ f.checkString(fn + "('ABC')", "CBA", "VARCHAR(3) NOT NULL");
+ f.checkString(fn + "('Hello World')", "dlroW olleH",
"VARCHAR(11) NOT NULL");
- f.checkString("reverse(_UTF8'\u4F60\u597D')", "\u597D\u4F60",
+ f.checkString(fn + "(_UTF8'\u4F60\u597D')", "\u597D\u4F60",
"VARCHAR(2) NOT NULL");
- f.checkNull("reverse(cast(null as varchar(1)))");
+ f.checkString(fn + "('a!b@c#')", "#c@b!a", "VARCHAR(6) NOT NULL");
+ f.checkNull(fn + "(cast(null as varchar(1)))");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testLevenshtein() {
@@ -7642,30 +7650,57 @@ public class SqlOperatorTest {
/** Tests {@code ARRAY_REVERSE} function from BigQuery. */
@Test void testArrayReverseFunc() {
+ final SqlFunction func = SqlLibraryOperators.ARRAY_REVERSE;
final SqlOperatorFixture f0 = fixture();
- f0.setFor(SqlLibraryOperators.ARRAY_REVERSE);
+ f0.setFor(func);
f0.checkFails("^array_reverse(array[1])^",
"No match found for function signature ARRAY_REVERSE\\(<INTEGER
ARRAY>\\)", false);
- final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
- f.checkScalar("array_reverse(array[1])", "[1]",
- "INTEGER NOT NULL ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[1, 2])", "[2, 1]",
- "INTEGER NOT NULL ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[null, 1])", "[1, null]",
- "INTEGER ARRAY NOT NULL");
- // elements cast
- f.checkScalar("array_reverse(array[cast(1 as tinyint), 2])", "[2, 1]",
- "INTEGER NOT NULL ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[null, 1, cast(2 as tinyint)])", "[2, 1,
null]",
- "INTEGER ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[cast(1 as bigint), 2])", "[2, 1]",
- "BIGINT NOT NULL ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[null, 1, cast(2 as bigint)])", "[2, 1,
null]",
- "BIGINT ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[cast(1 as decimal), 2])", "[2, 1]",
- "DECIMAL(19, 0) NOT NULL ARRAY NOT NULL");
- f.checkScalar("array_reverse(array[null, 1, cast(2 as decimal)])", "[2, 1,
null]",
- "DECIMAL(19, 0) ARRAY NOT NULL");
+ checkArrayReverseFunc(f0, func, list(SqlLibrary.BIG_QUERY));
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6445";>[CALCITE-6445]
+ * Add REVERSE function (enabled in Spark library)</a>. */
+ @Test void testReverseSparkFunc() {
+ final SqlFunction func = SqlLibraryOperators.REVERSE_SPARK;
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(func);
+ Iterable<SqlLibrary> libraries = list(SqlLibrary.SPARK);
+ checkArrayReverseFunc(f0, func, libraries);
+ checReverseFunc(f0, func, libraries);
+ }
+
+ void checkArrayReverseFunc(SqlOperatorFixture f0, SqlFunction function,
+ Iterable<? extends SqlLibrary> libraries) {
+ final String fn = function.getName();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalar(fn + "(array[1])", "[1]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[1, 2])", "[2, 1]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[null, 1])", "[1, null]",
+ "INTEGER ARRAY NOT NULL");
+ // elements cast
+ f.checkScalar(fn + "(array[cast(1 as tinyint), 2])", "[2, 1]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[null, 1, cast(2 as tinyint)])", "[2, 1,
null]",
+ "INTEGER ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[cast(1 as bigint), 2])", "[2, 1]",
+ "BIGINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[null, 1, cast(2 as bigint)])", "[2, 1, null]",
+ "BIGINT ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[cast(1 as decimal), 2])", "[2, 1]",
+ "DECIMAL(19, 0) NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[null, 1, cast(2 as decimal)])", "[2, 1,
null]",
+ "DECIMAL(19, 0) ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[CAST(2.1 as decimal(17)),
2.1111111111111119])",
+ "[2.1111111111111119, 2.1]", "DECIMAL(19, 2) NOT NULL ARRAY NOT
NULL");
+ f.checkScalar(fn + "(array[CAST(2.1 as double), 2.1111111111111119])",
+ "[2.111111111111112, 2.1]", "DOUBLE NOT NULL ARRAY NOT NULL");
+ f.checkScalar(fn + "(array[null])", "[null]", "NULL ARRAY NOT NULL");
+ f.checkNull(fn + "(cast(null as integer array))");
+ };
+ f0.forEachLibrary(libraries, consumer);
}
/** Tests {@code ARRAY_SIZE} function from Spark. */
