You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ta...@apache.org on 2023/06/05 23:51:40 UTC
[calcite] branch main updated: [CALCITE-5738] Add SORT_ARRAY function (enabled in Spark library)
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 2cb766380a [CALCITE-5738] Add SORT_ARRAY function (enabled in Spark library)
2cb766380a is described below
commit 2cb766380a27a1f4fd3b33540c2742bfc2eaeb59
Author: yongen.ly <yo...@alibaba-inc.com>
AuthorDate: Thu Jun 1 10:45:19 2023 +0800
[CALCITE-5738] Add SORT_ARRAY function (enabled in Spark library)
---
.../calcite/adapter/enumerable/RexImpTable.java | 23 ++++++++
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 9 +++
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../calcite/sql/fun/SqlLibraryOperators.java | 7 +++
.../org/apache/calcite/sql/type/OperandTypes.java | 65 ++++++++++++++++++++++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/runtime/CalciteResource.properties | 1 +
site/_docs/reference.md | 1 +
.../apache/calcite/sql/test/AbstractSqlTester.java | 2 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 33 +++++++++++
11 files changed, 147 insertions(+), 1 deletion(-)
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 cad65ffa60..fb764466ab 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
@@ -203,6 +203,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA256;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA512;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SINH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SORT_ARRAY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPLIT;
@@ -702,6 +703,7 @@ public class RexImpTable {
defineMethod(MAP_KEYS, BuiltInMethod.MAP_KEYS.method, NullPolicy.STRICT);
defineMethod(MAP_VALUES, BuiltInMethod.MAP_VALUES.method, NullPolicy.STRICT);
map.put(ARRAY_CONCAT, new ArrayConcatImplementor());
+ map.put(SORT_ARRAY, new SortArrayImplementor());
final MethodImplementor isEmptyImplementor =
new MethodImplementor(BuiltInMethod.IS_EMPTY.method, NullPolicy.NONE,
false);
@@ -3069,6 +3071,27 @@ public class RexImpTable {
}
}
+ /** Implementor for sort_array. */
+ private static class SortArrayImplementor extends AbstractRexCallImplementor {
+ SortArrayImplementor() {
+ super("sort_array", NullPolicy.STRICT, false);
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator,
+ RexCall call, List<Expression> argValueList) {
+ if (argValueList.size() == 2) {
+ return Expressions.call(
+ BuiltInMethod.SORT_ARRAY.method,
+ argValueList);
+ } else {
+ return Expressions.call(
+ BuiltInMethod.SORT_ARRAY.method,
+ argValueList.get(0),
+ Expressions.constant(true));
+ }
+ }
+ }
+
/** Implementor for a array concat. */
private static class ArrayConcatImplementor extends AbstractRexCallImplementor {
ArrayConcatImplementor() {
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 e062ae1242..d515c8e024 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -576,6 +576,9 @@ public interface CalciteResource {
@BaseMessage("Argument to function ''{0}'' must be a literal")
ExInst<SqlValidatorException> argumentMustBeLiteral(String a0);
+ @BaseMessage("Argument to function ''{0}'' must be a boolean literal")
+ ExInst<SqlValidatorException> argumentMustBeBooleanLiteral(String a0);
+
@BaseMessage("Argument to function ''{0}'' must be a positive integer literal")
ExInst<SqlValidatorException> argumentMustBePositiveInteger(String a0);
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 0db3921cca..a3b92ee6fb 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -3926,6 +3926,15 @@ public class SqlFunctions {
return Collections.nCopies(numberOfElement, element);
}
+ /** Support the SORT_ARRAY function. */
+ public static List sortArray(List list, boolean ascending) {
+ Comparator comparator = ascending
+ ? Comparator.nullsFirst(Comparator.naturalOrder())
+ : Comparator.nullsLast(Comparator.reverseOrder());
+ list.sort(comparator);
+ return list;
+ }
+
/** Support the MAP_ENTRIES function. */
public static List mapEntries(Map<Object, Object> map) {
final List result = new ArrayList(map.size());
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 7c2c698e1d..86f85ba4ba 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -701,6 +701,9 @@ public enum SqlKind {
/** {@code ARRAY_SIZE} function (Spark semantics). */
ARRAY_SIZE,
+ /** {@code SORT_ARRAY} function (Spark semantics). */
+ SORT_ARRAY,
+
/** {@code MAP_ENTRIES} function (Spark semantics). */
MAP_ENTRIES,
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 ec0627a89b..05021ed3b9 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
@@ -937,6 +937,13 @@ public abstract class SqlLibraryOperators {
ReturnTypes.LEAST_RESTRICTIVE,
OperandTypes.AT_LEAST_ONE_SAME_VARIADIC);
+ /** The "SORT_ARRAY(array)" function (Spark). */
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction SORT_ARRAY =
+ SqlBasicFunction.create(SqlKind.SORT_ARRAY,
+ ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.ARRAY.or(OperandTypes.ARRAY_BOOLEAN_LITERAL));
+
/** The "MAP_ENTRIES(map)" function. */
@LibraryOperator(libraries = {SPARK})
public static final SqlFunction MAP_ENTRIES =
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 3b4de3ebcb..ccafc9eb7a 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -496,6 +496,71 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker LITERAL =
new LiteralOperandTypeChecker(false);
+ /**
+ * Operand type-checking strategy type must be a boolean non-NULL literal.
+ */
+ public static final SqlSingleOperandTypeChecker BOOLEAN_LITERAL =
+ new FamilyOperandTypeChecker(ImmutableList.of(SqlTypeFamily.BOOLEAN),
+ i -> false) {
+ @Override public boolean checkSingleOperandType(
+ SqlCallBinding callBinding,
+ SqlNode operand,
+ int iFormalOperand,
+ boolean throwOnFailure) {
+ if (!LITERAL.checkSingleOperandType(
+ callBinding,
+ operand,
+ iFormalOperand,
+ throwOnFailure)) {
+ return false;
+ }
+
+ if (!super.checkSingleOperandType(
+ callBinding,
+ operand,
+ iFormalOperand,
+ throwOnFailure)) {
+ return false;
+ }
+
+ final SqlLiteral arg = (SqlLiteral) operand;
+ final boolean isBooleanLiteral =
+ SqlLiteral.valueMatchesType(arg.getValue(), SqlTypeName.BOOLEAN);
+
+ if (!isBooleanLiteral) {
+ if (throwOnFailure) {
+ throw callBinding.newError(
+ RESOURCE.argumentMustBeBooleanLiteral(
+ callBinding.getOperator().getName()));
+ }
+ return false;
+ }
+ return true;
+ }
+ };
+
+ /**
+ * Operand type-checking strategy where the first operand must be array and
+ * the second operand must be a boolean non-NULL literal.
+ */
+ public static final SqlSingleOperandTypeChecker
+ ARRAY_BOOLEAN_LITERAL =
+ new FamilyOperandTypeChecker(
+ ImmutableList.of(SqlTypeFamily.ARRAY, SqlTypeFamily.BOOLEAN),
+ i -> false) {
+ @Override public boolean checkSingleOperandType(
+ SqlCallBinding callBinding, SqlNode operand,
+ int iFormalOperand, boolean throwOnFailure) {
+ if (iFormalOperand == 0) {
+ return super.checkSingleOperandType(callBinding, operand,
+ iFormalOperand, throwOnFailure);
+ }
+
+ return BOOLEAN_LITERAL.checkSingleOperandType(
+ callBinding, operand, 0, throwOnFailure);
+ }
+ };
+
/**
* Operand type-checking strategy type must be a positive integer non-NULL
* literal.
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 cfc51dfd38..ad1857bfc1 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -634,6 +634,7 @@ public enum BuiltInMethod {
ARRAY_MIN(SqlFunctions.class, "arrayMin", List.class),
ARRAY_REPEAT(SqlFunctions.class, "repeat", Object.class, Integer.class),
ARRAY_REVERSE(SqlFunctions.class, "reverse", List.class),
+ SORT_ARRAY(SqlFunctions.class, "sortArray", List.class, boolean.class),
MAP_ENTRIES(SqlFunctions.class, "mapEntries", Map.class),
MAP_KEYS(SqlFunctions.class, "mapKeys", Map.class),
MAP_VALUES(SqlFunctions.class, "mapValues", Map.class),
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 86c6fbac26..d4e9f29208 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -193,6 +193,7 @@ FromAliasDuplicate=Duplicate relation name ''{0}'' in FROM clause
DuplicateColumnName=Duplicate column name ''{0}'' in output
Internal=Internal error: {0}
ArgumentMustBeLiteral=Argument to function ''{0}'' must be a literal
+ArgumentMustBeBooleanLiteral=Argument to function ''{0}'' must be a boolean literal
ArgumentMustBePositiveInteger=Argument to function ''{0}'' must be a positive integer literal
ArgumentMustBeNumericLiteralInRange=Argument to function ''{0}'' must be a numeric literal between {1,number,#} and {2,number,#}
ValidationError=Validation Error: {0}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index a2a816cbfb..d9b486ba80 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2661,6 +2661,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| s | ARRAY_REPEAT(element, count) | Returns the array containing element count times.
| b | ARRAY_REVERSE(array) | Reverses elements of *array*
| s | ARRAY_SIZE(array) | Synonym for `CARDINALITY`
+| s | SORT_ARRAY(array [, ascendingOrder]) | Sorts the *array* in ascending or descending order according to the natural ordering of the array elements. The default order is ascending if *ascendingOrder* is not specified. Null elements will be placed at the beginning of the returned array in ascending order or at the end of the returned array in descending order
| * | ASINH(numeric) | Returns the inverse hyperbolic sine of *numeric*
| * | ATANH(numeric) | Returns the inverse hyperbolic tangent of *numeric*
| m s | CHAR(integer) | Returns the character whose ASCII code is *integer* % 256, or null if *integer* < 0
diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
index a0d1e19e23..3904f43870 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
@@ -325,7 +325,7 @@ public abstract class AbstractSqlTester implements SqlTester, AutoCloseable {
* @return Query that evaluates a scalar expression
*/
protected String buildQuery2(SqlTestFactory factory, String expression) {
- if (expression.matches("(?i).*(percentile_(cont|disc)|convert)\\(.*")) {
+ if (expression.matches("(?i).*(percentile_(cont|disc)|convert|sort_array)\\(.*")) {
// PERCENTILE_CONT requires its argument to be a literal,
// so converting its argument to a column will cause false errors.
// Similarly, MSSQL-style CONVERT.
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 ad7ae34667..b4181e0288 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5468,6 +5468,39 @@ public class SqlOperatorTest {
f.checkNull("array_length(null)");
}
+ /** Tests {@code SORT_ARRAY} function from Spark. */
+ @Test void testSortArrayFunc() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.SORT_ARRAY);
+ f0.checkFails("^sort_array(array[null, 1, null, 2])^",
+ "No match found for function signature SORT_ARRAY\\(<INTEGER ARRAY>\\)", false);
+ f0.checkFails("^sort_array(array[null, 1, null, 2], true)^",
+ "No match found for function signature SORT_ARRAY\\(<INTEGER ARRAY>, <BOOLEAN>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
+ f.checkScalar("sort_array(array[2, null, 1])", "[null, 1, 2]",
+ "INTEGER ARRAY NOT NULL");
+ f.checkScalar("sort_array(array(2, null, 1), false)", "[2, 1, null]",
+ "INTEGER ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[true, false, null])", "[null, false, true]",
+ "BOOLEAN ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[true, false, null], false)", "[true, false, null]",
+ "BOOLEAN ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[null])", "[null]",
+ "NULL ARRAY NOT NULL");
+ f.checkScalar("sort_array(array())", "[]",
+ "UNKNOWN NOT NULL ARRAY NOT NULL");
+ f.checkNull("sort_array(null)");
+
+ f.checkFails("^sort_array(array[2, null, 1], cast(1 as boolean))^",
+ "Argument to function 'SORT_ARRAY' must be a literal", false);
+ f.checkFails("^sort_array(array[2, null, 1], 1)^",
+ "Cannot apply 'SORT_ARRAY' to arguments of type "
+ + "'SORT_ARRAY\\(<INTEGER ARRAY>, <INTEGER>\\)'\\."
+ + " Supported form\\(s\\): 'SORT_ARRAY\\(<ARRAY>\\)'\n"
+ + "'SORT_ARRAY\\(<ARRAY>, <BOOLEAN>\\)'", false);
+ }
+
/** Tests {@code MAP_ENTRIES} function from Spark. */
@Test void testMapEntriesFunc() {
final SqlOperatorFixture f0 = fixture();