This is an automated email from the ASF dual-hosted git repository.
xiong 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 e2e7ce177d [CALCITE-6711] Functions whose output value can be null
should return a nullable type
e2e7ce177d is described below
commit e2e7ce177d828445cd0b34ee51388264131d02d2
Author: Xiong Duan <[email protected]>
AuthorDate: Fri Dec 6 18:55:56 2024 +0800
[CALCITE-6711] Functions whose output value can be null should return a
nullable type
---
.../calcite/sql/fun/SqlLibraryOperators.java | 31 +++++----
.../apache/calcite/sql/type/SqlTypeTransforms.java | 24 +++++++
.../calcite/sql/test/SqlOperatorFixture.java | 12 ----
.../calcite/test/SqlOperatorFixtureImpl.java | 5 --
.../org/apache/calcite/test/SqlOperatorTest.java | 75 ++++++++++------------
5 files changed, 76 insertions(+), 71 deletions(-)
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 b4f0a17d31..8403354713 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
@@ -575,7 +575,7 @@ public abstract class SqlLibraryOperators {
* Returns the substring in value that matches the regexp. Returns NULL if
there is no match. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlBasicFunction REGEXP_EXTRACT =
- SqlBasicFunction.create("REGEXP_EXTRACT", ReturnTypes.VARCHAR_NULLABLE,
+ SqlBasicFunction.create("REGEXP_EXTRACT",
ReturnTypes.VARCHAR_FORCE_NULLABLE,
OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
@@ -583,8 +583,8 @@ public abstract class SqlLibraryOperators {
* Returns the substring in value that matches the regexp. Returns NULL if
there is no match. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlBasicFunction REGEXP_EXTRACT_ALL =
- SqlBasicFunction.create("REGEXP_EXTRACT_ALL", ReturnTypes.ARG0_NULLABLE
- .andThen(SqlTypeTransforms.TO_ARRAY),
+ SqlBasicFunction.create("REGEXP_EXTRACT_ALL", ReturnTypes.ARG0
+
.andThen(SqlTypeTransforms.TO_ARRAY).andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.STRING_STRING,
SqlFunctionCategory.STRING);
@@ -1522,9 +1522,11 @@ public abstract class SqlLibraryOperators {
@SuppressWarnings("argument.type.incompatible")
private static RelDataType arrayInsertReturnType(SqlOperatorBinding
opBinding) {
- final RelDataType arrayType = opBinding.collectOperandTypes().get(0);
+ final List<RelDataType> operandTypes = opBinding.collectOperandTypes();
+ final RelDataType arrayType = operandTypes.get(0);
final RelDataType componentType = arrayType.getComponentType();
- final RelDataType elementType = opBinding.collectOperandTypes().get(2);
+ final RelDataType elementType1 = operandTypes.get(1);
+ final RelDataType elementType2 = operandTypes.get(2);
requireNonNull(componentType, () -> "componentType of " + arrayType);
// we don't need to do leastRestrictive on componentType and elementType,
@@ -1532,15 +1534,15 @@ public abstract class SqlLibraryOperators {
// So we use componentType directly.
RelDataType type =
opBinding.getTypeFactory().leastRestrictive(
- ImmutableList.of(componentType, elementType));
+ ImmutableList.of(componentType, elementType2));
requireNonNull(type, "inferred array element type");
- if (elementType.isNullable()) {
+ if (elementType2.isNullable()) {
type = opBinding.getTypeFactory().createTypeWithNullability(type, true);
}
// make explicit CAST for array elements and inserted element to the
biggest type
// if array component type not equals to inserted element type
- if (!componentType.equalsSansFieldNames(elementType)) {
+ if (!componentType.equalsSansFieldNames(elementType2)) {
// 0, 2 is the operand index to be CAST
// For array_insert, 0 is the array arg and 2 is the inserted element
if (componentType.equalsSansFieldNames(type)) {
@@ -1551,7 +1553,8 @@ public abstract class SqlLibraryOperators {
adjustTypeForArrayFunctions(type, opBinding, 0);
}
}
- return SqlTypeUtil.createArrayType(opBinding.getTypeFactory(), type,
arrayType.isNullable());
+ boolean nullable = arrayType.isNullable() || elementType1.isNullable();
+ return SqlTypeUtil.createArrayType(opBinding.getTypeFactory(), type,
nullable);
}
/** The "ARRAY_INSERT(array, pos, val)" function (Spark). */
@@ -2292,7 +2295,9 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction CODE_POINTS_TO_BYTES =
SqlBasicFunction.create("CODE_POINTS_TO_BYTES",
- ReturnTypes.VARBINARY_NULLABLE,
+ ReturnTypes.VARBINARY
+ .andThen(SqlTypeTransforms.TO_NULLABLE)
+
.andThen(SqlTypeTransforms.TO_NULLABLE_IF_ARRAY_CONTAINS_NULLABLE),
OperandTypes.ARRAY_OF_INTEGER,
SqlFunctionCategory.STRING);
@@ -2301,7 +2306,9 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction CODE_POINTS_TO_STRING =
SqlBasicFunction.create("CODE_POINTS_TO_STRING",
- ReturnTypes.VARCHAR_NULLABLE,
+ ReturnTypes.VARCHAR
+ .andThen(SqlTypeTransforms.TO_NULLABLE)
+
.andThen(SqlTypeTransforms.TO_NULLABLE_IF_ARRAY_CONTAINS_NULLABLE),
OperandTypes.ARRAY_OF_INTEGER,
SqlFunctionCategory.STRING);
@@ -2311,7 +2318,7 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction TO_CODE_POINTS =
SqlBasicFunction.create("TO_CODE_POINTS",
- ReturnTypes.INTEGER.andThen(SqlTypeTransforms.TO_ARRAY_NULLABLE),
+
ReturnTypes.INTEGER.andThen(SqlTypeTransforms.TO_ARRAY_FORCE_NULLABLE),
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
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 d6677266ae..f1a699e9f3 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
@@ -53,6 +53,22 @@ public abstract class SqlTypeTransforms {
opBinding.collectOperandTypes(),
requireNonNull(typeToTransform, "typeToTransform"));
+ /**
+ * Parameter type-inference transform strategy where a derived type is
+ * transformed into the same type, but nullable if and only if the type
+ * is a ARRAY type and contains nullable elements.
+ */
+ public static final SqlTypeTransform TO_NULLABLE_IF_ARRAY_CONTAINS_NULLABLE =
+ (opBinding, typeToTransform) -> {
+ RelDataType relDataType = opBinding.getOperandType(0);
+ if (SqlTypeUtil.isArray(relDataType)) {
+ RelDataType componentRelDataType = relDataType.getComponentType();
+ assert componentRelDataType != null;
+ return
opBinding.getTypeFactory().createTypeWithNullability(typeToTransform,
+ componentRelDataType.isNullable());
+ }
+ return typeToTransform;
+ };
/**
* Parameter type-inference transform strategy where a derived type is
* transformed into the same type, but nullable if and only if all of a
call's
@@ -244,6 +260,14 @@ public abstract class SqlTypeTransforms {
(opBinding, typeToTransform) ->
TO_NULLABLE.transformType(opBinding,
TO_ARRAY.transformType(opBinding, typeToTransform));
+ /**
+ * Parameter type-inference transform strategy that wraps a given type in a
nullabe array.
+ */
+ public static final SqlTypeTransform TO_ARRAY_FORCE_NULLABLE =
+ (opBinding, typeToTransform) ->
+ FORCE_NULLABLE.transformType(opBinding,
+ TO_ARRAY.transformType(opBinding, typeToTransform));
+
/** Parameter type-inference transform that transforms {@code T} to
* {@code MEASURE<T>} for some type T. */
public static final SqlTypeTransform TO_MEASURE =
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
index 3c3a825af6..94436699b2 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
@@ -344,18 +344,6 @@ public interface SqlOperatorFixture extends AutoCloseable {
*/
void checkNull(String expression);
- /**
- * Tests that a SQL expression returns the SQL NULL value and
- * the data type of the returned result is nullable. For example,
- *
- * <blockquote>
- * <pre>checkNullValueWithNullableType("CHAR_LENGTH(CAST(NULL AS
VARCHAR(3))");</pre>
- * </blockquote>
- *
- * @param expression Scalar expression
- */
- void checkNullValueWithNullableType(String expression);
-
/**
* Tests that a SQL expression has a given type. For example,
*
diff --git
a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java
index 8663ebc0aa..58cf5c2d10 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java
@@ -285,11 +285,6 @@ class SqlOperatorFixtureImpl implements SqlOperatorFixture
{
}
@Override public void checkNull(String expression) {
- tester.forEachQuery(factory, expression, sql ->
- tester.check(factory, sql, SqlTests.ANY_TYPE_CHECKER, isNullValue()));
- }
-
- @Override public void checkNullValueWithNullableType(String expression) {
tester.forEachQuery(factory, expression, sql ->
tester.check(factory, sql, SqlTests.ANY_NULLABLE_TYPE_CHECKER,
isNullValue()));
}
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 9c0cd589c1..8f1b0d3c0b 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -2305,15 +2305,15 @@ public class SqlOperatorTest {
+ "'TO_CODE_POINTS\\(<BINARY>\\)'", false);
f.checkScalar("to_code_points(_UTF8'ÿþЀ\uD804\uDD70A')", "[255, 254, 1024,
70000, 65]",
- "INTEGER NOT NULL ARRAY NOT NULL");
+ "INTEGER NOT NULL ARRAY");
f.checkScalar("to_code_points('ABCD')", "[65, 66, 67, 68]",
- "INTEGER NOT NULL ARRAY NOT NULL");
+ "INTEGER NOT NULL ARRAY");
f.checkScalar("to_code_points(x'11223344')", "[17, 34, 51, 68]",
- "INTEGER NOT NULL ARRAY NOT NULL");
+ "INTEGER NOT NULL ARRAY");
f.checkScalar("to_code_points(code_points_to_string(array[255, 254, 1024,
70000, 65]))",
- "[255, 254, 1024, 70000, 65]", "INTEGER NOT NULL ARRAY NOT NULL");
+ "[255, 254, 1024, 70000, 65]", "INTEGER NOT NULL ARRAY");
f.checkScalar("to_code_points(code_points_to_bytes(array[64, 65, 66,
67]))",
- "[64, 65, 66, 67]", "INTEGER NOT NULL ARRAY NOT NULL");
+ "[64, 65, 66, 67]", "INTEGER NOT NULL ARRAY");
f.checkNull("to_code_points(null)");
f.checkNull("to_code_points('')");
@@ -4821,8 +4821,8 @@ public class SqlOperatorTest {
+ " 'QUERY', 'k1')",
"v1",
"VARCHAR");
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'QUERY', 'k3')");
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ + " 'QUERY', 'k3')");
f.checkString("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ " 'FILE')",
"/path1/p.php?k1=v1&k2=v2",
@@ -4843,39 +4843,31 @@ public class SqlOperatorTest {
+ " 'USERINFO')",
"bob",
"VARCHAR");
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'USERINFO')");
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ + " 'USERINFO')");
f.checkString("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ " 'AUTHORITY')",
"calcite.apache.org",
"VARCHAR");
// test with invalid partToExtract
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
- + " 'INVALID_PART_TO_EXTRACT')");
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'HOST', 'k1')");
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ + " 'INVALID_PART_TO_EXTRACT')");
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ + " 'HOST', 'k1')");
// test with invalid urlString
- f.checkNullValueWithNullableType(
- "parse_url('http:calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'HOST')");
- f.checkNullValueWithNullableType(
- "parse_url('calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'HOST')");
- f.checkNullValueWithNullableType(
- "parse_url('/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')");
+
f.checkNull("parse_url('http:calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'HOST')");
+
f.checkNull("parse_url('calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',
'HOST')");
+ f.checkNull("parse_url('/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')");
// test with operands with null values
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ " cast(null as varchar))");
- f.checkNullValueWithNullableType(
- "parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+
f.checkNull("parse_url('http://calcite.apache.org/path1/p.php?k1=v1&k2=v2#Ref1',"
+ " cast(null as varchar), cast(null as varchar))");
- f.checkNullValueWithNullableType(
- "parse_url(cast(null as varchar), cast(null as varchar))");
- f.checkNullValueWithNullableType(
- "parse_url(cast(null as varchar), cast(null as varchar), cast(null
as varchar))");
+ f.checkNull("parse_url(cast(null as varchar), cast(null as varchar))");
+ f.checkNull("parse_url(cast(null as varchar), cast(null as varchar),
cast(null as varchar))");
};
f0.forEachLibrary(list(SqlLibrary.HIVE, SqlLibrary.SPARK), consumer);
}
@@ -5437,8 +5429,8 @@ public class SqlOperatorTest {
f.checkString("from_base64('VGhpcyB pcyBhIHRlc3Qg\tU3Ry\naW5nLg==')",
"546869732069732061207465737420537472696e672e",
"VARBINARY");
- f.checkNullValueWithNullableType("from_base64('-1')");
- f.checkNullValueWithNullableType("from_base64('-100')");
+ f.checkNull("from_base64('-1')");
+ f.checkNull("from_base64('-100')");
};
f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer);
}
@@ -6164,15 +6156,14 @@ public class SqlOperatorTest {
final SqlFunction function = functionAlias.function;
final String fn = function.getName();
final Consumer<SqlOperatorFixture> consumer = f -> {
- f.checkString(fn + "('abc def ghi', 'def')", "def", "VARCHAR NOT NULL");
- f.checkString(fn + "('abcadcaecghi', 'a.c', 1, 3)", "aec", "VARCHAR NOT
NULL");
- f.checkString(fn + "('abcadcaecghi', 'abc(a.c)')", "adc", "VARCHAR NOT
NULL");
- f.checkString(fn + "('55as56664as422', '\\d{3}')", "566", "VARCHAR NOT
NULL");
- f.checkString(fn + "('abcadcabcaecghi', 'c(a.c)', 4)", "abc", "VARCHAR
NOT NULL");
- f.checkString(fn + "('abcadcabcaecghi', 'a.c(a.c)', 1, 2)", "aec",
"VARCHAR NOT NULL");
- f.checkString(fn + "('a9cadca5c4aecghi', 'a[0-9]c', 6)", "a5c", "VARCHAR
NOT NULL");
- f.checkString(fn + "('a9cadca5ca4cecghi', 'a[0-9]c', 1, 3)", "a4c",
"VARCHAR NOT "
- + "NULL");
+ f.checkString(fn + "('abc def ghi', 'def')", "def", "VARCHAR");
+ f.checkString(fn + "('abcadcaecghi', 'a.c', 1, 3)", "aec", "VARCHAR");
+ f.checkString(fn + "('abcadcaecghi', 'abc(a.c)')", "adc", "VARCHAR");
+ f.checkString(fn + "('55as56664as422', '\\d{3}')", "566", "VARCHAR");
+ f.checkString(fn + "('abcadcabcaecghi', 'c(a.c)', 4)", "abc", "VARCHAR");
+ f.checkString(fn + "('abcadcabcaecghi', 'a.c(a.c)', 1, 2)", "aec",
"VARCHAR");
+ f.checkString(fn + "('a9cadca5c4aecghi', 'a[0-9]c', 6)", "a5c",
"VARCHAR");
+ f.checkString(fn + "('a9cadca5ca4cecghi', 'a[0-9]c', 1, 3)", "a4c",
"VARCHAR");
f.checkNull(fn + "('abc def ghi', 'asd')");
f.checkNull(fn + "('abc def ghi', 'abc', 25)");
@@ -11692,9 +11683,9 @@ public class SqlOperatorTest {
f12.checkScalar("NVL2(NULL, 3.0, 2.111)", "2.111", "DECIMAL(4, 3) NOT
NULL");
f12.checkScalar("NVL2(3.111, 3.1415926, 2.111)", "3.1415926",
"DECIMAL(8, 7) NOT NULL");
- f12.checkNullValueWithNullableType("nvl2('ab', CAST(NULL AS VARCHAR(6)),
'def')");
- f12.checkNullValueWithNullableType("nvl2(NULL, 'abc', NULL)");
- f12.checkNullValueWithNullableType("nvl2(NULL, NULL, NULL)");
+ f12.checkNull("nvl2('ab', CAST(NULL AS VARCHAR(6)), 'def')");
+ f12.checkNull("nvl2(NULL, 'abc', NULL)");
+ f12.checkNull("nvl2(NULL, NULL, NULL)");
f12.checkFails("^NVL2(2.0, 2.0, true)^", "Parameters must be of the same
type", false);
f12.checkFails("^NVL2(NULL, 2.0, true)^", "Parameters must be of the
same type", false);
