This is an automated email from the ASF dual-hosted git repository. chunwei pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push: new f61541d [CALCITE-4822] Add functions ARRAY_CONCAT, ARRAY_REVERSE, ARRAY_LENGTH for BigQuery dialect f61541d is described below commit f61541d633cfde53a4b0de0c23a010250c93274e Author: snuyanzin <snuyan...@gmail.com> AuthorDate: Tue May 11 21:21:29 2021 +0200 [CALCITE-4822] Add functions ARRAY_CONCAT, ARRAY_REVERSE, ARRAY_LENGTH for BigQuery dialect --- .../calcite/adapter/enumerable/RexImpTable.java | 39 ++++++++++++++++++++++ .../org/apache/calcite/runtime/SqlFunctions.java | 7 ++++ .../main/java/org/apache/calcite/sql/SqlKind.java | 6 ++++ .../calcite/sql/fun/SqlLibraryOperators.java | 30 +++++++++++++++++ .../org/apache/calcite/sql/type/OperandTypes.java | 12 +++++++ .../org/apache/calcite/util/BuiltInMethod.java | 5 ++- .../calcite/sql/test/SqlOperatorBaseTest.java | 38 +++++++++++++++++++++ site/_docs/reference.md | 3 ++ 8 files changed, 139 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 4aa6f4f..4921339 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 @@ -109,7 +109,10 @@ import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract; import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus; import static org.apache.calcite.sql.fun.SqlInternalOperators.THROW_UNLESS; import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_AGG; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_CONCAT; import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_CONCAT_AGG; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_LENGTH; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_REVERSE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_AND; import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_OR; import static org.apache.calcite.sql.fun.SqlLibraryOperators.CHR; @@ -510,10 +513,14 @@ public class RexImpTable { // Multisets & arrays defineMethod(CARDINALITY, BuiltInMethod.COLLECTION_SIZE.method, NullPolicy.STRICT); + defineMethod(ARRAY_LENGTH, BuiltInMethod.COLLECTION_SIZE.method, + NullPolicy.STRICT); defineMethod(SLICE, BuiltInMethod.SLICE.method, NullPolicy.NONE); defineMethod(ELEMENT, BuiltInMethod.ELEMENT.method, NullPolicy.STRICT); defineMethod(STRUCT_ACCESS, BuiltInMethod.STRUCT_ACCESS.method, NullPolicy.ANY); defineMethod(MEMBER_OF, BuiltInMethod.MEMBER_OF.method, NullPolicy.NONE); + defineMethod(ARRAY_REVERSE, BuiltInMethod.ARRAY_REVERSE.method, NullPolicy.STRICT); + map.put(ARRAY_CONCAT, new ArrayConcatImplementor()); final MethodImplementor isEmptyImplementor = new MethodImplementor(BuiltInMethod.IS_EMPTY.method, NullPolicy.NONE, false); @@ -2657,6 +2664,38 @@ public class RexImpTable { } } + /** Implementor for a array concat. */ + private static class ArrayConcatImplementor extends AbstractRexCallImplementor { + + ArrayConcatImplementor() { + super(NullPolicy.STRICT, false); + } + + @Override String getVariableName() { + return "array_concat"; + } + + @Override Expression implementSafe(RexToLixTranslator translator, RexCall call, + List<Expression> argValueList) { + final BlockBuilder blockBuilder = translator.getBlockBuilder(); + final Expression list = + blockBuilder.append("list", Expressions.new_(ArrayList.class), false); + final Expression nullValue = Expressions.constant(null); + for (Expression expression : argValueList) { + blockBuilder.add( + Expressions.ifThenElse( + Expressions.or( + Expressions.equal(nullValue, list), + Expressions.equal(nullValue, expression)), + Expressions.assign(list, nullValue), + Expressions.statement( + Expressions.call(list, BuiltInMethod.COLLECTION_ADDALL.method, expression))) + ); + } + return list; + } + } + /** Implementor for a value-constructor. */ private static class ValueConstructorImplementor extends AbstractRexCallImplementor { 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 382e091..17eeb00 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -63,6 +63,7 @@ import java.util.ArrayList; import java.util.Arrays; import java.util.Base64; import java.util.Collection; +import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.HashMap; @@ -2788,6 +2789,12 @@ public class SqlFunctions { return resultCollection; } + /** Support the ARRAY_REVERSE function. */ + public static List reverse(List list) { + Collections.reverse(list); + return list; + } + /** * 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 ae68852..7dcfd07 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -635,6 +635,12 @@ public enum SqlKind { /** {@code EXTRACT} function. */ EXTRACT, + /** {@code ARRAY_CONCAT} function (BigQuery semantics). */ + ARRAY_CONCAT, + + /** {@code ARRAY_REVERSE} function (BigQuery semantics). */ + ARRAY_REVERSE, + /** {@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 7879564..0df5f8c 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 @@ -525,6 +525,36 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING_SAME_SAME, SqlFunctionCategory.STRING); + /** The "ARRAY_LENGTH(array)" function. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction ARRAY_LENGTH = + new SqlFunction("ARRAY_LENGTH", + SqlKind.OTHER_FUNCTION, + ReturnTypes.INTEGER_NULLABLE, + null, + OperandTypes.ARRAY, + SqlFunctionCategory.SYSTEM); + + /** The "ARRAY_REVERSE(array)" function. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction ARRAY_REVERSE = + new SqlFunction("ARRAY_REVERSE", + SqlKind.ARRAY_REVERSE, + ReturnTypes.ARG0_NULLABLE, + null, + OperandTypes.ARRAY, + SqlFunctionCategory.SYSTEM); + + /** The "ARRAY_CONCAT(array [, array]*)" function. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction ARRAY_CONCAT = + new SqlFunction("ARRAY_CONCAT", + SqlKind.ARRAY_CONCAT, + ReturnTypes.LEAST_RESTRICTIVE, + null, + OperandTypes.AT_LEAST_ONE_SAME_VARIADIC, + SqlFunctionCategory.SYSTEM); + @LibraryOperator(libraries = {MYSQL}) public static final SqlFunction REVERSE = new SqlFunction("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 1658cb8..534e298 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 @@ -437,6 +437,18 @@ public abstract class OperandTypes { new SameOperandTypeChecker(-1); /** + * Operand type-checking strategy where any positive number of operands must all be + * in the same type family. + */ + public static final SqlOperandTypeChecker AT_LEAST_ONE_SAME_VARIADIC = + new SameOperandTypeChecker(-1) { + @Override public SqlOperandCountRange + getOperandCountRange() { + return SqlOperandCountRanges.from(1); + } + }; + + /** * Operand type-checking strategy where operand types must allow ordered * comparisons. */ 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 2b4fa7a..510baf1 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -569,6 +569,7 @@ public enum BuiltInMethod { IS_EMPTY(Collection.class, "isEmpty"), SUBMULTISET_OF(SqlFunctions.class, "submultisetOf", Collection.class, Collection.class), + ARRAY_REVERSE(SqlFunctions.class, "reverse", List.class), SELECTIVITY(Selectivity.class, "getSelectivity", RexNode.class), UNIQUE_KEYS(UniqueKeys.class, "getUniqueKeys", boolean.class), AVERAGE_ROW_SIZE(Size.class, "averageRowSize"), @@ -633,7 +634,9 @@ public enum BuiltInMethod { long.class, long.class), SESSIONIZATION(EnumUtils.class, "sessionize", Enumerator.class, int.class, int.class, long.class), - BIG_DECIMAL_NEGATE(BigDecimal.class, "negate"); + BIG_DECIMAL_ADD(BigDecimal.class, "add", BigDecimal.class), + BIG_DECIMAL_NEGATE(BigDecimal.class, "negate"), + COMPARE_TO(Comparable.class, "compareTo", Object.class); @SuppressWarnings("ImmutableEnumChecker") public final Method method; diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 556c2c7..f3097ec 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -5760,6 +5760,44 @@ public abstract class SqlOperatorBaseTest { tester.checkScalar("rand_integer(2, 11)", 1, "INTEGER NOT NULL"); } + /** Tests {@code ARRAY_CONCAT} function from BigQuery. */ + @Test void testArrayConcat() { + SqlTester tester = libraryTester(SqlLibrary.BIG_QUERY); + tester.setFor(SqlLibraryOperators.ARRAY_CONCAT); + tester.checkFails("^array_concat()^", INVALID_ARGUMENTS_NUMBER, false); + tester.checkScalar("array_concat(array[1, 2], array[2, 3])", "[1, 2, 2, 3]", + "INTEGER NOT NULL ARRAY NOT NULL"); + tester.checkScalar("array_concat(array[1, 2], array[2, null])", "[1, 2, 2, null]", + "INTEGER ARRAY NOT NULL"); + tester.checkScalar( + "array_concat(array['hello', 'world'], array['!'], array[cast(null as char)])", + "[hello, world, !, null]", "CHAR(5) ARRAY NOT NULL"); + tester.checkNull("array_concat(cast(null as integer array), array[1])"); + } + + /** Tests {@code ARRAY_REVERSE} function from BigQuery. */ + @Test void testArrayReverseFunc() { + SqlTester tester = libraryTester(SqlLibrary.BIG_QUERY); + tester.setFor(SqlLibraryOperators.ARRAY_REVERSE); + tester.checkScalar("array_reverse(array[1])", "[1]", + "INTEGER NOT NULL ARRAY NOT NULL"); + tester.checkScalar("array_reverse(array[1, 2])", "[2, 1]", + "INTEGER NOT NULL ARRAY NOT NULL"); + tester.checkScalar("array_reverse(array[null, 1])", "[1, null]", + "INTEGER ARRAY NOT NULL"); + } + + /** Tests {@code ARRAY_LENGTH} function from BigQuery. */ + @Test void testArrayLengthFunc() { + SqlTester tester = libraryTester(SqlLibrary.BIG_QUERY); + tester.setFor(SqlLibraryOperators.ARRAY_LENGTH); + tester.checkScalar("array_length(array[1])", "1", + "INTEGER NOT NULL"); + tester.checkScalar("array_length(array[1, 2, null])", "3", + "INTEGER NOT NULL"); + tester.checkNull("array_length(null)"); + } + /** Tests {@code UNIX_SECONDS} and other datetime functions from BigQuery. */ @Test void testUnixSecondsFunc() { SqlTester tester = libraryTester(SqlLibrary.BIG_QUERY); diff --git a/site/_docs/reference.md b/site/_docs/reference.md index f186cca..2afd767 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2513,6 +2513,9 @@ semantics. | C | Operator syntax | Description |:- |:-----------------------------------------------|:----------- | p | expr :: type | Casts *expr* to *type* +| b | ARRAY_CONCAT(array [, array ]*) | Concatenates one or more arrays. If any input argument is `NULL` the function returns `NULL` +| b | ARRAY_LENGTH(array) | Synonym for `CARDINALITY` +| b | ARRAY_REVERSE(array) | Reverses elements of *array* | o | CHR(integer) | Returns the character having the binary equivalent to *integer* as a CHAR value | o | COSH(numeric) | Returns the hyperbolic cosine of *numeric* | o | CONCAT(string, string) | Concatenates two strings