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 342cf606ac [CALCITE-5948] Use explicit casting if element type in
ARRAY/MAP does not equal derived component type
342cf606ac is described below
commit 342cf606acc954228820f69f83d43298fd874184
Author: Ran Tao <[email protected]>
AuthorDate: Sun Aug 27 14:33:36 2023 +0800
[CALCITE-5948] Use explicit casting if element type in ARRAY/MAP does not
equal derived component type
---
babel/src/test/resources/sql/big-query.iq | 4 +-
.../calcite/sql/fun/SqlArrayValueConstructor.java | 5 +
.../calcite/sql/fun/SqlLibraryOperators.java | 5 +
.../calcite/sql/fun/SqlMapValueConstructor.java | 6 +
.../calcite/sql/validate/SqlValidatorUtil.java | 89 ++++++++++
.../java/org/apache/calcite/test/JdbcTest.java | 3 +-
core/src/test/resources/sql/misc.iq | 12 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 193 +++++++++++++++++++--
8 files changed, 293 insertions(+), 24 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 5455de9f57..9dce6abdd0 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -1012,9 +1012,9 @@ FROM
SELECT
email,
- REGEXP_CONTAINS(email, '^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')
+ REGEXP_CONTAINS(email, '^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)\s+$')
AS valid_email_address,
- REGEXP_CONTAINS(email, '^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')
+ REGEXP_CONTAINS(email, '^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org\s+$')
AS without_parentheses
FROM
(SELECT
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlArrayValueConstructor.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlArrayValueConstructor.java
index e291c41211..5f6eb5dd82 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlArrayValueConstructor.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlArrayValueConstructor.java
@@ -20,6 +20,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
import static java.util.Objects.requireNonNull;
@@ -38,6 +39,10 @@ public class SqlArrayValueConstructor extends
SqlMultisetValueConstructor {
opBinding.getTypeFactory(),
opBinding.collectOperandTypes());
requireNonNull(type, "inferred array element type");
+
+ // explicit cast elements to component type if they are not same
+ SqlValidatorUtil.adjustTypeForArrayConstructor(type, opBinding);
+
return SqlTypeUtil.createArrayType(
opBinding.getTypeFactory(), type, false);
}
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 d58ae302d2..cdf4f76808 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
@@ -43,6 +43,7 @@ import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeTransforms;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.Litmus;
import org.apache.calcite.util.Optionality;
import org.apache.calcite.util.Static;
@@ -1066,6 +1067,10 @@ public abstract class SqlLibraryOperators {
: opBinding.getTypeFactory().createUnknownType();
}
requireNonNull(type, "inferred array element type");
+
+ // explicit cast elements to component type if they are not same
+ SqlValidatorUtil.adjustTypeForArrayConstructor(type, opBinding);
+
return SqlTypeUtil.createArrayType(opBinding.getTypeFactory(), type,
false);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlMapValueConstructor.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlMapValueConstructor.java
index d3c4e5840e..7f3da3bdb2 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlMapValueConstructor.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlMapValueConstructor.java
@@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
@@ -44,10 +45,15 @@ public class SqlMapValueConstructor extends
SqlMultisetValueConstructor {
super("MAP", SqlKind.MAP_VALUE_CONSTRUCTOR);
}
+ @SuppressWarnings("argument.type.incompatible")
@Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
Pair<@Nullable RelDataType, @Nullable RelDataType> type =
getComponentTypes(
opBinding.getTypeFactory(), opBinding.collectOperandTypes());
+
+ // explicit cast elements to component type if they are not same
+ SqlValidatorUtil.adjustTypeForMapConstructor(type, opBinding);
+
return SqlTypeUtil.createMapType(
opBinding.getTypeFactory(),
requireNonNull(type.left, "inferred key type"),
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
index 0825ec2b21..6d1119c9a4 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
@@ -38,6 +38,7 @@ import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDynamicParam;
import org.apache.calcite.sql.SqlFunctionCategory;
@@ -48,6 +49,7 @@ import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlSyntax;
@@ -1309,6 +1311,93 @@ public class SqlValidatorUtil {
return null;
}
+ /**
+ * When the array element does not equal the array component type, make
explicit casting.
+ *
+ * @param componentType derived array component type
+ * @param opBinding description of call
+ */
+ public static void adjustTypeForArrayConstructor(
+ RelDataType componentType, SqlOperatorBinding opBinding) {
+ if (opBinding instanceof SqlCallBinding) {
+ requireNonNull(componentType, "array component type");
+ adjustTypeForMultisetConstructor(
+ componentType, componentType, (SqlCallBinding) opBinding);
+ }
+ }
+
+ /**
+ * When the map key or value does not equal the map component key type or
value type,
+ * make explicit casting.
+ *
+ * @param componentType derived map pair component type
+ * @param opBinding description of call
+ */
+ public static void adjustTypeForMapConstructor(
+ Pair<RelDataType, RelDataType> componentType, SqlOperatorBinding
opBinding) {
+ if (opBinding instanceof SqlCallBinding) {
+ requireNonNull(componentType.getKey(), "map key type");
+ requireNonNull(componentType.getValue(), "map value type");
+ adjustTypeForMultisetConstructor(
+ componentType.getKey(), componentType.getValue(), (SqlCallBinding)
opBinding);
+ }
+ }
+
+ /**
+ * Adjusts the types for operands in a SqlCallBinding during the
construction of a sql collection
+ * type such as Array or Map. This method iterates from the operands of a
{@link SqlCall}
+ * obtained from the provided {@link SqlCallBinding}.
+ * It modifies each operand to match the specified 'evenType' or 'oddType'
depending on whether
+ * the operand's position is even or odd, respectively. The type adjustment
is performed by
+ * explicitly casting each operand to the desired type if the existing
operand type does not
+ * match the desired type without considering field names.
+ * If we adjust array, we should set 'evenType' and 'oddType' to same
desired type,
+ * if we adjust map, we should set 'evenType' as map key type and 'oddType'
as map value type.
+ *
+ * <p>For example, if the operand types are {@code [INT, STRING, INT,
STRING]}
+ * with {@code evenType} as {@code BOOLEAN} and {@code oddType} as {@code
DOUBLE},
+ * after executing this method, the types should be {@code [BOOLEAN, DOUBLE,
BOOLEAN, DOUBLE]},
+ * then the corresponding operands are cast to these types.
+ *
+ * @param evenType the {@link RelDataType} to which the operands at even
positions should be cast
+ * @param oddType the {@link RelDataType} to which the operands at odd
positions should be cast
+ * @param sqlCallBinding the {@link SqlCallBinding} containing the operands
to be adjusted
+ */
+ private static void adjustTypeForMultisetConstructor(
+ RelDataType evenType, RelDataType oddType, SqlCallBinding
sqlCallBinding) {
+ SqlCall call = sqlCallBinding.getCall();
+ List<RelDataType> operandTypes = sqlCallBinding.collectOperandTypes();
+ List<SqlNode> operands = call.getOperandList();
+ RelDataType elementType;
+ for (int i = 0; i < operands.size(); i++) {
+ if (i % 2 == 0) {
+ elementType = evenType;
+ } else {
+ elementType = oddType;
+ }
+ if (!operandTypes.get(i).equalsSansFieldNames(elementType)) {
+ call.setOperand(i, castTo(operands.get(i), elementType));
+ }
+ }
+ }
+
+ /**
+ * Creates a CAST operation to cast a given {@link SqlNode} to a specified
{@link RelDataType}.
+ * This method uses the {@link SqlStdOperatorTable#CAST} operator to create
a new {@link SqlCall}
+ * node representing a CAST operation. The original 'node' is cast to the
desired 'type',
+ * preserving the nullability of the 'type'.
+ *
+ * @param node the {@link SqlNode} which is to be cast
+ * @param type the target {@link RelDataType} to which 'node' should be cast
+ * @return a new {@link SqlNode} representing the CAST operation
+ */
+ private static SqlNode castTo(SqlNode node, RelDataType type) {
+ return SqlStdOperatorTable.CAST.createCall(
+ SqlParserPos.ZERO,
+ node,
+ SqlTypeUtil.convertTypeToSpec(type).withNullable(type.isNullable()));
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 8abb94f7dc..c02bff87bc 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -8144,10 +8144,9 @@ public class JdbcTest {
* ClassCastException retrieving from ARRAY that has mixed INTEGER and
DECIMAL
* elements</a>. */
@Test void testIntAndBigDecimalInArray() {
- // Result should be "EXPR$0=[1, 1.1]\n"; [CALCITE-4850] logged.
CalciteAssert.that()
.query("select array[1, 1.1]")
- .returns("EXPR$0=[0E+1, 1.1]\n");
+ .returns("EXPR$0=[1, 1.1]\n");
}
/** Test case for
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 7e10550455..8bdb345b22 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2207,12 +2207,12 @@ select array[1,null,2] as a from (values (1));
values array['a',null,'bcd'],
array['efgh'];
-+----------------+
-| EXPR$0 |
-+----------------+
-| [a, null, bcd] |
-| [efgh] |
-+----------------+
++------------------+
+| EXPR$0 |
++------------------+
+| [a , null, bcd] |
+| [efgh] |
++------------------+
(2 rows)
!ok
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 60bd95846d..03939830cc 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6001,6 +6001,13 @@ public class SqlOperatorTest {
f.checkScalar("array_compact(array())", "[]",
"UNKNOWN NOT NULL ARRAY NOT NULL");
f.checkNull("array_compact(null)");
+ // elements cast
+ f.checkScalar("array_compact(array[null, 1, null, cast(2 as tinyint)])",
"[1, 2]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array_compact(array[null, 1, null, cast(2 as bigint)])",
"[1, 2]",
+ "BIGINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array_compact(array[null, 1, null, cast(2 as decimal)])",
"[1, 2]",
+ "DECIMAL(19, 0) NOT NULL ARRAY NOT NULL");
}
/** Tests {@code ARRAY_CONCAT} function from BigQuery. */
@@ -6063,6 +6070,13 @@ public class SqlOperatorTest {
f.checkScalar("array_distinct(array[null, 1, null])", "[null, 1]",
"INTEGER ARRAY NOT NULL");
f.checkNull("array_distinct(null)");
+ // elements cast
+ f.checkScalar("array_distinct(array[null, cast(1 as tinyint), 1, cast(2 as
smallint)])",
+ "[null, 1, 2]", "INTEGER ARRAY NOT NULL");
+ f.checkScalar("array_distinct(array[null, cast(1 as tinyint), 1, cast(2 as
bigint)])",
+ "[null, 1, 2]", "BIGINT ARRAY NOT NULL");
+ f.checkScalar("array_distinct(array[null, cast(1 as tinyint), 1, cast(2 as
decimal)])",
+ "[null, 1, 2]", "DECIMAL(19, 0) ARRAY NOT NULL");
}
@Test void testArrayJoinFunc() {
@@ -6072,16 +6086,30 @@ public class SqlOperatorTest {
+ " signature ARRAY_JOIN\\(<CHAR\\(2\\) ARRAY>, <CHARACTER>\\)",
false);
final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
- f.checkScalar("array_join(array['aa', 'b', 'c'], '-')", "aa-b-c",
+ f.checkScalar("array_join(array['aa', 'b', 'c'], '-')", "aa-b -c ",
"VARCHAR NOT NULL");
f.checkScalar("array_join(array[null, 'aa', null, 'b', null], '-',
'empty')",
- "empty-aa-empty-b-empty", "VARCHAR NOT NULL");
- f.checkScalar("array_join(array[null, 'aa', null, 'b', null], '-')",
"aa-b",
+ "empty-aa-empty-b -empty", "VARCHAR NOT NULL");
+ f.checkScalar("array_join(array[null, 'aa', null, 'b', null], '-')", "aa-b
",
"VARCHAR NOT NULL");
f.checkScalar("array_join(array[null, x'aa', null, x'bb', null], '-')",
"aa-bb",
"VARCHAR NOT NULL");
- f.checkScalar("array_join(array['', 'b'], '-')", "-b", "VARCHAR NOT NULL");
+ f.checkScalar("array_join(array['', 'b'], '-')", " -b", "VARCHAR NOT
NULL");
f.checkScalar("array_join(array['', ''], '-')", "-", "VARCHAR NOT NULL");
+
+ final SqlOperatorFixture f1 =
+ f.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
+ f1.checkScalar("array_join(array['aa', 'b', 'c'], '-')", "aa-b-c",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_join(array[null, 'aa', null, 'b', null], '-',
'empty')",
+ "empty-aa-empty-b-empty", "VARCHAR NOT NULL");
+ f1.checkScalar("array_join(array[null, 'aa', null, 'b', null], '-')",
"aa-b",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_join(array[null, x'aa', null, x'bb', null], '-')",
"aa-bb",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_join(array['', 'b'], '-')", "-b", "VARCHAR NOT
NULL");
+ f1.checkScalar("array_join(array['', ''], '-')", "-", "VARCHAR NOT NULL");
+
f.checkNull("array_join(null, '-')");
f.checkNull("array_join(array['a', 'b', null], null)");
f.checkFails("^array_join(array[1, 2, 3], '-', ' ')^",
@@ -6104,6 +6132,13 @@ public class SqlOperatorTest {
f.checkType("array_max(array())", "UNKNOWN");
f.checkNull("array_max(array())");
f.checkNull("array_max(cast(null as integer array))");
+ // elements cast
+ f.checkScalar("array_max(array[null, 1, cast(2 as tinyint)])", "2",
+ "INTEGER");
+ f.checkScalar("array_max(array[null, 1, cast(2 as bigint)])", "2",
+ "BIGINT");
+ f.checkScalar("array_max(array[null, 1, cast(2 as decimal)])", "2",
+ "DECIMAL(19, 0)");
}
/** Tests {@code ARRAY_MIN} function from Spark. */
@@ -6119,6 +6154,13 @@ public class SqlOperatorTest {
f.checkType("array_min(array())", "UNKNOWN");
f.checkNull("array_min(array())");
f.checkNull("array_min(cast(null as integer array))");
+ // elements cast
+ f.checkScalar("array_min(array[null, 1, cast(2 as tinyint)])", "1",
+ "INTEGER");
+ f.checkScalar("array_min(array[null, 1, cast(2 as bigint)])", "1",
+ "BIGINT");
+ f.checkScalar("array_min(array[null, 1, cast(2 as decimal)])", "1",
+ "DECIMAL(19, 0)");
}
/** Tests {@code ARRAY_POSITION} function from Spark. */
@@ -6236,6 +6278,13 @@ public class SqlOperatorTest {
"(INTEGER NOT NULL, CHAR(1) NOT NULL) MAP NOT NULL ARRAY NOT NULL");
f.checkScalar("array_repeat(cast(null as integer), 2)", "[null, null]",
"INTEGER ARRAY NOT NULL");
+ // elements cast
+ f.checkScalar("array_repeat(cast(1 as tinyint), 2)", "[1, 1]",
+ "TINYINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array_repeat(cast(1 as bigint), 2)", "[1, 1]",
+ "BIGINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array_repeat(cast(1 as decimal), 2)", "[1, 1]",
+ "DECIMAL(19, 0) NOT NULL ARRAY NOT NULL");
f.checkNull("array_repeat(1, null)");
}
@@ -6252,6 +6301,19 @@ public class SqlOperatorTest {
"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");
}
/** Tests {@code ARRAY_SIZE} function from Spark. */
@@ -6267,6 +6329,13 @@ public class SqlOperatorTest {
f.checkScalar("array_size(array[1, 2, null])", "3",
"INTEGER NOT NULL");
f.checkNull("array_size(null)");
+ // elements cast
+ f.checkScalar("array_size(array[cast(1 as tinyint), 2])", "2",
+ "INTEGER NOT NULL");
+ f.checkScalar("array_size(array[null, 1, cast(2 as tinyint)])", "3",
+ "INTEGER NOT NULL");
+ f.checkScalar("array_size(array[cast(1 as bigint), 2])", "2",
+ "INTEGER NOT NULL");
}
/** Tests {@code ARRAY_LENGTH} function from BigQuery. */
@@ -6281,6 +6350,13 @@ public class SqlOperatorTest {
f.checkScalar("array_length(array[1, 2, null])", "3",
"INTEGER NOT NULL");
f.checkNull("array_length(null)");
+ // elements cast
+ f.checkScalar("array_length(array[cast(1 as tinyint), 2])", "2",
+ "INTEGER NOT NULL");
+ f.checkScalar("array_length(array[null, 1, cast(2 as tinyint)])", "3",
+ "INTEGER NOT NULL");
+ f.checkScalar("array_length(array[cast(1 as bigint), 2])", "2",
+ "INTEGER NOT NULL");
}
@Test void testArrayToStringFunc() {
@@ -6290,15 +6366,15 @@ public class SqlOperatorTest {
+ " signature ARRAY_TO_STRING\\(<CHAR\\(2\\) ARRAY>, <CHARACTER>\\)",
false);
final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
- f.checkScalar("array_to_string(array['aa', 'b', 'c'], '-')", "aa-b-c",
+ f.checkScalar("array_to_string(array['aa', 'b', 'c'], '-')", "aa-b -c ",
"VARCHAR NOT NULL");
f.checkScalar("array_to_string(array[null, 'aa', null, 'b', null], '-',
'empty')",
- "empty-aa-empty-b-empty", "VARCHAR NOT NULL");
- f.checkScalar("array_to_string(array[null, 'aa', null, 'b', null], '-')",
"aa-b",
+ "empty-aa-empty-b -empty", "VARCHAR NOT NULL");
+ f.checkScalar("array_to_string(array[null, 'aa', null, 'b', null], '-')",
"aa-b ",
"VARCHAR NOT NULL");
f.checkScalar("array_to_string(array[null, x'aa', null, x'bb', null],
'-')", "aa-bb",
"VARCHAR NOT NULL");
- f.checkScalar("array_to_string(array['', 'b'], '-')", "-b", "VARCHAR NOT
NULL");
+ f.checkScalar("array_to_string(array['', 'b'], '-')", " -b", "VARCHAR NOT
NULL");
f.checkScalar("array_to_string(array['', ''], '-')", "-", "VARCHAR NOT
NULL");
f.checkNull("array_to_string(null, '-')");
f.checkNull("array_to_string(array['a', 'b', null], null)");
@@ -6306,6 +6382,19 @@ public class SqlOperatorTest {
"Cannot apply 'ARRAY_TO_STRING' to arguments of type 'ARRAY_TO_STRING"
+ "\\(<INTEGER ARRAY>, <CHAR\\(1\\)>, <CHAR\\(1\\)>\\)'\\.
Supported form\\(s\\):"
+ " ARRAY_TO_STRING\\(<STRING ARRAY>, <CHARACTER>\\[,
<CHARACTER>\\]\\)", false);
+
+ final SqlOperatorFixture f1 =
+ f.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
+ f1.checkScalar("array_to_string(array['aa', 'b', 'c'], '-')", "aa-b-c",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_to_string(array[null, 'aa', null, 'b', null], '-',
'empty')",
+ "empty-aa-empty-b-empty", "VARCHAR NOT NULL");
+ f1.checkScalar("array_to_string(array[null, 'aa', null, 'b', null], '-')",
"aa-b",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_to_string(array[null, x'aa', null, x'bb', null],
'-')", "aa-bb",
+ "VARCHAR NOT NULL");
+ f1.checkScalar("array_to_string(array['', 'b'], '-')", "-b", "VARCHAR NOT
NULL");
+ f1.checkScalar("array_to_string(array['', ''], '-')", "-", "VARCHAR NOT
NULL");
}
/** Tests {@code ARRAY_EXCEPT} function from Spark. */
@@ -6549,6 +6638,16 @@ public class SqlOperatorTest {
"UNKNOWN NOT NULL ARRAY NOT NULL");
f.checkNull("sort_array(null)");
+ // elements cast
+ f.checkScalar("sort_array(array[cast(1 as tinyint), 2])", "[1, 2]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[null, 1, cast(2 as tinyint)])", "[null, 1,
2]",
+ "INTEGER ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[cast(1 as bigint), 2])", "[1, 2]",
+ "BIGINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar("sort_array(array[cast(1 as decimal), 2])", "[1, 2]",
+ "DECIMAL(19, 0) NOT NULL ARRAY NOT 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)^",
@@ -6619,6 +6718,21 @@ public class SqlOperatorTest {
"RecordType(CHAR(3) NOT NULL f0, INTEGER NOT NULL f1) NOT NULL ARRAY
NOT NULL");
f.checkScalar("map_entries(map['foo', 1, null, 2])", "[{foo, 1}, {null,
2}]",
"RecordType(CHAR(3) f0, INTEGER NOT NULL f1) NOT NULL ARRAY NOT NULL");
+ // elements cast
+ // key cast
+ f.checkScalar("map_entries(map[cast(1 as tinyint), 1, 2, 2])", "[{1, 1},
{2, 2}]",
+ "RecordType(INTEGER NOT NULL f0, INTEGER NOT NULL f1) NOT NULL ARRAY
NOT NULL");
+ f.checkScalar("map_entries(map[cast(1 as bigint), 1, null, 2])", "[{1, 1},
{null, 2}]",
+ "RecordType(BIGINT f0, INTEGER NOT NULL f1) NOT NULL ARRAY NOT NULL");
+ f.checkScalar("map_entries(map[cast(1 as decimal), 1, null, 2])", "[{1,
1}, {null, 2}]",
+ "RecordType(DECIMAL(19, 0) f0, INTEGER NOT NULL f1) NOT NULL ARRAY NOT
NULL");
+ // value cast
+ f.checkScalar("map_entries(map[1, cast(1 as tinyint), 2, 2])", "[{1, 1},
{2, 2}]",
+ "RecordType(INTEGER NOT NULL f0, INTEGER NOT NULL f1) NOT NULL ARRAY
NOT NULL");
+ f.checkScalar("map_entries(map[1, cast(1 as bigint), null, 2])", "[{1, 1},
{null, 2}]",
+ "RecordType(INTEGER f0, BIGINT NOT NULL f1) NOT NULL ARRAY NOT NULL");
+ f.checkScalar("map_entries(map[1, cast(1 as decimal), null, 2])", "[{1,
1}, {null, 2}]",
+ "RecordType(INTEGER f0, DECIMAL(19, 0) NOT NULL f1) NOT NULL ARRAY NOT
NULL");
}
/** Tests {@code MAP_KEYS} function from Spark. */
@@ -6633,6 +6747,21 @@ public class SqlOperatorTest {
"CHAR(3) NOT NULL ARRAY NOT NULL");
f.checkScalar("map_keys(map['foo', 1, null, 2])", "[foo, null]",
"CHAR(3) ARRAY NOT NULL");
+ // elements cast
+ // key cast
+ f.checkScalar("map_keys(map[cast(1 as tinyint), 1, 2, 2])", "[1, 2]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("map_keys(map[cast(1 as bigint), 1, null, 2])", "[1, null]",
+ "BIGINT ARRAY NOT NULL");
+ f.checkScalar("map_keys(map[cast(1 as decimal), 1, null, 2])", "[1, null]",
+ "DECIMAL(19, 0) ARRAY NOT NULL");
+ // value cast
+ f.checkScalar("map_keys(map[1, cast(1 as tinyint), 2, 2])", "[1, 2]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("map_keys(map[1, cast(1 as bigint), null, 2])", "[1, null]",
+ "INTEGER ARRAY NOT NULL");
+ f.checkScalar("map_keys(map[1, cast(1 as decimal), null, 2])", "[1, null]",
+ "INTEGER ARRAY NOT NULL");
}
/** Tests {@code MAP_VALUES} function from Spark. */
@@ -6661,7 +6790,13 @@ public class SqlOperatorTest {
f.checkScalar("map_from_arrays(array[1, 2], array['foo', 'bar'])",
"{1=foo, 2=bar}",
"(INTEGER NOT NULL, CHAR(3) NOT NULL) MAP NOT NULL");
f.checkScalar("map_from_arrays(array[1, 1, null], array['foo', 'bar',
'name'])",
- "{1=bar, null=name}", "(INTEGER, CHAR(4) NOT NULL) MAP NOT NULL");
+ "{1=bar , null=name}", "(INTEGER, CHAR(4) NOT NULL) MAP NOT NULL");
+
+ final SqlOperatorFixture f1 =
+ f.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
+ f1.checkScalar("map_from_arrays(array[1, 1, null], array['foo', 'bar',
'name'])",
+ "{1=bar, null=name}", "(INTEGER, VARCHAR(4) NOT NULL) MAP NOT NULL");
+
f.checkScalar("map_from_arrays(array(), array())",
"{}", "(UNKNOWN NOT NULL, UNKNOWN NOT NULL) MAP NOT NULL");
f.checkType("map_from_arrays(cast(null as integer array), array['foo',
'bar'])",
@@ -9968,6 +10103,13 @@ public class SqlOperatorTest {
"[foo, null]", "CHAR(3) ARRAY NOT NULL");
f.checkScalar("Array[null]",
"[null]", "NULL ARRAY NOT NULL");
+ // element cast
+ f.checkScalar("Array[cast(1 as tinyint), cast(2 as smallint)]",
+ "[1, 2]", "SMALLINT NOT NULL ARRAY NOT NULL");
+ f.checkScalar("Array[1, cast(2 as tinyint), cast(3 as smallint)]",
+ "[1, 2, 3]", "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("Array[1, cast(2 as tinyint), cast(3 as smallint), cast(4 as
bigint)]",
+ "[1, 2, 3, 4]", "BIGINT NOT NULL ARRAY NOT NULL");
// empty array is illegal per SQL spec. presumably because one can't
// infer type
f.checkFails("^Array[]^", "Require at least 1 argument", false);
@@ -9995,14 +10137,15 @@ public class SqlOperatorTest {
"[null, foo]", "CHAR(3) ARRAY NOT NULL");
f2.checkScalar("array(null)",
"[null]", "NULL ARRAY NOT NULL");
+ // calcite default cast char type will fill extra spaces
f2.checkScalar("array(1, 2, 'Hi')",
- "[1, 2, Hi]", "CHAR(2) NOT NULL ARRAY NOT NULL");
+ "[1 , 2 , Hi]", "CHAR(2) NOT NULL ARRAY NOT NULL");
f2.checkScalar("array(1, 2, 'Hi', 'Hello')",
- "[1, 2, Hi, Hello]", "CHAR(5) NOT NULL ARRAY NOT NULL");
+ "[1 , 2 , Hi , Hello]", "CHAR(5) NOT NULL ARRAY NOT NULL");
f2.checkScalar("array(1, 2, 'Hi', null)",
- "[1, 2, Hi, null]", "CHAR(2) ARRAY NOT NULL");
+ "[1 , 2 , Hi, null]", "CHAR(2) ARRAY NOT NULL");
f2.checkScalar("array(1, 2, 'Hi', cast(null as char(10)))",
- "[1, 2, Hi, null]", "CHAR(10) ARRAY NOT NULL");
+ "[1 , 2 , Hi , null]", "CHAR(10) ARRAY NOT
NULL");
}
/**
@@ -10169,14 +10312,36 @@ public class SqlOperatorTest {
f.checkFails("^map[1, 1, 2, 'x']^",
"Parameters must be of the same type", false);
f.checkScalar("map['washington', 1, 'obama', 44]",
- "{washington=1, obama=44}",
+ "{washington=1, obama =44}",
"(CHAR(10) NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ // elements cast
+ f.checkScalar("map['A', 1, 'ABC', 2]", "{A =1, ABC=2}",
+ "(CHAR(3) NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ f.checkScalar("Map[cast(1 as tinyint), 1, cast(2 as smallint), 2]",
+ "{1=1, 2=2}", "(SMALLINT NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ f.checkScalar("Map[1, cast(1 as tinyint), 2, cast(2 as smallint)]",
+ "{1=1, 2=2}", "(INTEGER NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
+ f.checkScalar("Map[1, cast(1 as tinyint), cast(2 as bigint), cast(2 as
smallint)]",
+ "{1=1, 2=2}", "(BIGINT NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
+ f.checkScalar("Map[cast(1 as bigint), cast(1 as tinyint), 2, cast(2 as
smallint)]",
+ "{1=1, 2=2}", "(BIGINT NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
final SqlOperatorFixture f1 =
f.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
f1.checkScalar("map['washington', 1, 'obama', 44]",
"{washington=1, obama=44}",
"(VARCHAR(10) NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ // elements cast
+ f1.checkScalar("map['A', 1, 'ABC', 2]", "{A=1, ABC=2}",
+ "(VARCHAR(3) NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ f1.checkScalar("Map[cast(1 as tinyint), 1, cast(2 as smallint), 2]",
+ "{1=1, 2=2}", "(SMALLINT NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ f1.checkScalar("Map[1, cast(1 as tinyint), 2, cast(2 as smallint)]",
+ "{1=1, 2=2}", "(INTEGER NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
+ f1.checkScalar("Map[1, cast(1 as tinyint), cast(2 as bigint), cast(2 as
smallint)]",
+ "{1=1, 2=2}", "(BIGINT NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
+ f1.checkScalar("Map[cast(1 as bigint), cast(1 as tinyint), 2, cast(2 as
smallint)]",
+ "{1=1, 2=2}", "(BIGINT NOT NULL, SMALLINT NOT NULL) MAP NOT NULL");
}
@Test void testCeilFunc() {
