This is an automated email from the ASF dual-hosted git repository. jiajunxie 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 9c33d7aeef [CALCITE-5695] Add MAP_KEYS, MAP_VALUES function (enabled in Spark library) 9c33d7aeef is described below commit 9c33d7aeefe082bf5f7be617ef231e1285418a6c Author: yongen.ly <yongen...@alibaba-inc.com> AuthorDate: Wed May 17 10:35:11 2023 +0800 [CALCITE-5695] Add MAP_KEYS, MAP_VALUES function (enabled in Spark library) --- .../calcite/adapter/enumerable/RexImpTable.java | 4 ++++ .../org/apache/calcite/runtime/SqlFunctions.java | 10 ++++++++ .../main/java/org/apache/calcite/sql/SqlKind.java | 6 +++++ .../calcite/sql/fun/SqlLibraryOperators.java | 14 +++++++++++ .../org/apache/calcite/sql/type/OperandTypes.java | 3 +++ .../org/apache/calcite/sql/type/ReturnTypes.java | 24 +++++++++++++++++++ .../apache/calcite/sql/type/SqlTypeTransforms.java | 28 ++++++++++++++++++++++ .../org/apache/calcite/util/BuiltInMethod.java | 2 ++ site/_docs/reference.md | 2 ++ .../org/apache/calcite/test/SqlOperatorTest.java | 28 ++++++++++++++++++++++ 10 files changed, 121 insertions(+) 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 1f833ad0a4..0d5090a93a 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 @@ -165,6 +165,8 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.LOG; import static org.apache.calcite.sql.fun.SqlLibraryOperators.LOGICAL_AND; import static org.apache.calcite.sql.fun.SqlLibraryOperators.LOGICAL_OR; import static org.apache.calcite.sql.fun.SqlLibraryOperators.LPAD; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.MAP_KEYS; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.MAP_VALUES; import static org.apache.calcite.sql.fun.SqlLibraryOperators.MAX_BY; import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5; import static org.apache.calcite.sql.fun.SqlLibraryOperators.MIN_BY; @@ -680,6 +682,8 @@ public class RexImpTable { defineMethod(ARRAY_REPEAT, BuiltInMethod.ARRAY_REPEAT.method, NullPolicy.NONE); defineMethod(ARRAY_REVERSE, BuiltInMethod.ARRAY_REVERSE.method, NullPolicy.STRICT); defineMethod(ARRAY_SIZE, BuiltInMethod.COLLECTION_SIZE.method, NullPolicy.STRICT); + defineMethod(MAP_KEYS, BuiltInMethod.MAP_KEYS.method, NullPolicy.STRICT); + defineMethod(MAP_VALUES, BuiltInMethod.MAP_VALUES.method, NullPolicy.STRICT); map.put(ARRAY_CONCAT, new ArrayConcatImplementor()); final MethodImplementor isEmptyImplementor = new MethodImplementor(BuiltInMethod.IS_EMPTY.method, NullPolicy.NONE, 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 29ba098dda..1fc339fc07 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -3831,6 +3831,16 @@ public class SqlFunctions { return Collections.nCopies(numberOfElement, element); } + /** Support the MAP_KEYS function. */ + public static List mapKeys(Map map) { + return new ArrayList<>(map.keySet()); + } + + /** Support the MAP_VALUES function. */ + public static List mapValues(Map map) { + return new ArrayList<>(map.values()); + } + /** Support the SLICE function. */ public static List slice(List list) { List result = new ArrayList(list.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 9104af350c..d52322bd2f 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -692,6 +692,12 @@ public enum SqlKind { /** {@code ARRAY_SIZE} function (Spark semantics). */ ARRAY_SIZE, + /** {@code MAP_KEYS} function (Spark semantics). */ + MAP_KEYS, + + /** {@code MAP_VALUES} function (Spark semantics). */ + MAP_VALUES, + /** {@code REVERSE} function (SQL Server, MySQL). */ REVERSE, 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 fe3269d18a..a1952900eb 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 @@ -888,6 +888,20 @@ public abstract class SqlLibraryOperators { ReturnTypes.LEAST_RESTRICTIVE, OperandTypes.AT_LEAST_ONE_SAME_VARIADIC); + /** The "MAP_KEYS(map)" function. */ + @LibraryOperator(libraries = {SPARK}) + public static final SqlFunction MAP_KEYS = + SqlBasicFunction.create(SqlKind.MAP_KEYS, + ReturnTypes.TO_MAP_KEYS_NULLABLE, + OperandTypes.MAP); + + /** The "MAP_VALUES(map)" function. */ + @LibraryOperator(libraries = {SPARK}) + public static final SqlFunction MAP_VALUES = + SqlBasicFunction.create(SqlKind.MAP_VALUES, + ReturnTypes.TO_MAP_VALUES_NULLABLE, + OperandTypes.MAP); + @LibraryOperator(libraries = {BIG_QUERY, MYSQL}) public static final SqlFunction REVERSE = SqlBasicFunction.create(SqlKind.REVERSE, 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 ff14401bcf..855161da3e 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 @@ -481,6 +481,9 @@ public abstract class OperandTypes { .or(family(SqlTypeFamily.ARRAY)) .or(family(SqlTypeFamily.MAP)); + public static final SqlSingleOperandTypeChecker MAP = + family(SqlTypeFamily.MAP); + /** * Operand type-checking strategy where type must be a literal or NULL. */ 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 0b7385438f..e25e3b6c96 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 @@ -536,6 +536,30 @@ public abstract class ReturnTypes { public static final SqlReturnTypeInference TO_MAP = ARG0.andThen(SqlTypeTransforms.TO_MAP); + /** + * Returns a ARRAY type. + * + * <p>For example, given {@code (INTEGER, DATE) MAP}, returns + * {@code INTEGER ARRAY}. + */ + public static final SqlReturnTypeInference TO_MAP_KEYS = + ARG0.andThen(SqlTypeTransforms.TO_MAP_KEYS); + + public static final SqlReturnTypeInference TO_MAP_KEYS_NULLABLE = + TO_MAP_KEYS.andThen(SqlTypeTransforms.TO_NULLABLE); + + /** + * Returns a ARRAY type. + * + * <p>For example, given {@code (INTEGER, DATE) MAP}, returns + * {@code DATE ARRAY}. + */ + public static final SqlReturnTypeInference TO_MAP_VALUES = + ARG0.andThen(SqlTypeTransforms.TO_MAP_VALUES); + + public static final SqlReturnTypeInference TO_MAP_VALUES_NULLABLE = + TO_MAP_VALUES.andThen(SqlTypeTransforms.TO_NULLABLE); + /** * Type-inference strategy that always returns GEOMETRY. */ diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java index 92ce99f085..f87b6992f0 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java @@ -228,6 +228,34 @@ public abstract class SqlTypeTransforms { SqlTypeUtil.createMapTypeFromRecord(opBinding.getTypeFactory(), typeToTransform); + /** + * Parameter type-inference transform strategy that converts a MAP type + * to a ARRAY type. + * + * @see org.apache.calcite.rel.type.RelDataTypeFactory#createArrayType + */ + public static final SqlTypeTransform TO_MAP_KEYS = + (opBinding, typeToTransform) -> { + RelDataType keyType = + requireNonNull(typeToTransform.getKeyType(), + () -> "keyType for " + typeToTransform + " in opBinding " + opBinding); + return opBinding.getTypeFactory().createArrayType(keyType, -1); + }; + + /** + * Parameter type-inference transform strategy that converts a MAP type + * to a ARRAY type. + * + * @see org.apache.calcite.rel.type.RelDataTypeFactory#createArrayType + */ + public static final SqlTypeTransform TO_MAP_VALUES = + (opBinding, typeToTransform) -> { + RelDataType keyType = + requireNonNull(typeToTransform.getValueType(), + () -> "valueType for " + typeToTransform + " in opBinding " + opBinding); + return opBinding.getTypeFactory().createArrayType(keyType, -1); + }; + /** * Parameter type-inference transform strategy where a derived type must be * a struct type with precisely one field and the returned type is the type 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 13bc6ec051..4f4de9946b 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -631,6 +631,8 @@ public enum BuiltInMethod { ARRAY_DISTINCT(SqlFunctions.class, "distinct", List.class), ARRAY_REPEAT(SqlFunctions.class, "repeat", Object.class, Integer.class), ARRAY_REVERSE(SqlFunctions.class, "reverse", List.class), + MAP_KEYS(SqlFunctions.class, "mapKeys", Map.class), + MAP_VALUES(SqlFunctions.class, "mapValues", Map.class), SELECTIVITY(Selectivity.class, "getSelectivity", RexNode.class), UNIQUE_KEYS(UniqueKeys.class, "getUniqueKeys", boolean.class), AVERAGE_ROW_SIZE(Size.class, "averageRowSize"), diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 64adc009df..ea611d449e 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2727,6 +2727,8 @@ BigQuery's type system uses confusingly different names for types and functions: | m | TO_BASE64(string) | Converts the *string* to base-64 encoded form and returns a encoded string | b m | FROM_BASE64(string) | Returns the decoded result of a base-64 *string* as a string | b o | LTRIM(string) | Returns *string* with all blanks removed from the start +| s | MAP_KEYS(map) | Returns the keys of the *map* as an array, the order of the entries is not defined +| s | MAP_VALUES(map) | Returns the values of the *map* as an array, the order of the entries is not defined | b m p | 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, it returns '二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10' | o | 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 346bbbc74c..0f2b16af03 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -5397,6 +5397,34 @@ public class SqlOperatorTest { f.checkNull("array_length(null)"); } + /** Tests {@code MAP_KEYS} function from Spark. */ + @Test void testMapKeysFunc() { + final SqlOperatorFixture f0 = fixture(); + f0.setFor(SqlLibraryOperators.MAP_KEYS); + f0.checkFails("^map_keys(map['foo', 1, 'bar', 2])^", + "No match found for function signature MAP_KEYS\\(<\\(CHAR\\(3\\), INTEGER\\) " + + "MAP>\\)", false); + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK); + f.checkScalar("map_keys(map['foo', 1, 'bar', 2])", "[foo, bar]", + "CHAR(3) NOT NULL ARRAY NOT NULL"); + f.checkScalar("map_keys(map['foo', 1, null, 2])", "[foo, null]", + "CHAR(3) ARRAY NOT NULL"); + } + + /** Tests {@code MAP_VALUES} function from Spark. */ + @Test void testMapValuesFunc() { + final SqlOperatorFixture f0 = fixture(); + f0.setFor(SqlLibraryOperators.MAP_VALUES); + f0.checkFails("^map_values(map['foo', 1, 'bar', 2])^", + "No match found for function signature MAP_VALUES\\(<\\(CHAR\\(3\\), INTEGER\\) " + + "MAP>\\)", false); + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK); + f.checkScalar("map_values(map['foo', 1, 'bar', 2])", "[1, 2]", + "INTEGER NOT NULL ARRAY NOT NULL"); + f.checkScalar("map_values(map['foo', 1, 'bar', cast(null as integer)])", "[1, null]", + "INTEGER ARRAY NOT NULL"); + } + /** Tests {@code UNIX_SECONDS} and other datetime functions from BigQuery. */ @Test void testUnixSecondsFunc() { SqlOperatorFixture f = fixture()