commit de39888754759d3e6b7ce35770fcff3d8d0e6a4c
author Clint Wylie <cwylie@apache.org> Wed Jan 17 00:30:19 2024 -0800
committer Mihai Budiu <mbudiu@feldera.com> Tue Jan 30 10:30:43 2024 -0800
tree e60b0b5f100cb8ae890ee6cc707608bf6a065b1f
parent 75511b82ab13ff95bc65f6180d40cf1e234b834e

[CALCITE-6208] Update JSON_VALUE return type inference to make explicit array return types be nullable with nullable elements

core/src/main/java/org/apache/calcite/sql/fun/SqlJsonValueFunction.java

diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonValueFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonValueFunction.java
index 5cc542b..84bc1ba 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonValueFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonValueFunction.java
@@ -30,11 +30,10 @@
 import org.apache.calcite.sql.SqlOperatorBinding;
 import org.apache.calcite.sql.SqlWriter;
 import org.apache.calcite.sql.type.OperandTypes;
-import org.apache.calcite.sql.type.ReturnTypes;
 import org.apache.calcite.sql.type.SqlOperandCountRanges;
 import org.apache.calcite.sql.type.SqlTypeFamily;
 import org.apache.calcite.sql.type.SqlTypeName;
-import org.apache.calcite.sql.type.SqlTypeTransforms;
+import org.apache.calcite.sql.type.SqlTypeUtil;
 
 import com.google.common.collect.ImmutableList;
 
@@ -54,9 +53,9 @@
 
   public SqlJsonValueFunction(String name) {
     super(name, SqlKind.OTHER_FUNCTION,
-        ReturnTypes.cascade(
-            opBinding -> explicitTypeSpec(opBinding).orElse(getDefaultType(opBinding)),
-            SqlTypeTransforms.FORCE_NULLABLE),
+        opBinding -> explicitTypeSpec(opBinding)
+            .map(relDataType -> deriveExplicitType(opBinding, relDataType))
+            .orElseGet(() -> getDefaultType(opBinding)),
         null,
         OperandTypes.family(
             ImmutableList.of(SqlTypeFamily.ANY, SqlTypeFamily.CHARACTER),
@@ -64,10 +63,23 @@
         SqlFunctionCategory.SYSTEM);
   }
 
+  private static RelDataType deriveExplicitType(SqlOperatorBinding opBinding, RelDataType type) {
+    if (SqlTypeName.ARRAY == type.getSqlTypeName()) {
+      RelDataType elementType = Objects.requireNonNull(type.getComponentType());
+      RelDataType nullableElementType = deriveExplicitType(opBinding, elementType);
+      return SqlTypeUtil.createArrayType(
+          opBinding.getTypeFactory(),
+          nullableElementType,
+          true);
+    }
+    return opBinding.getTypeFactory().createTypeWithNullability(type, true);
+  }
+
   /** Returns VARCHAR(2000) as default. */
   private static RelDataType getDefaultType(SqlOperatorBinding opBinding) {
     final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
-    return typeFactory.createSqlType(SqlTypeName.VARCHAR, 2000);
+    final RelDataType baseType = typeFactory.createSqlType(SqlTypeName.VARCHAR, 2000);
+    return typeFactory.createTypeWithNullability(baseType, true);
   }
 
   /**

core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java

diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index fff0fc2..8fead2c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -11417,6 +11417,14 @@
     expr("json_value('{\"foo\":100}', 'lax $.foo' returning boolean"
         + " default 100 on empty)")
         .columnType("BOOLEAN");
+
+    expr("json_value('{\"foo\":[100, null, 200]}', 'lax $.foo'"
+        + "returning integer array)")
+        .columnType("INTEGER ARRAY");
+
+    expr("json_value('{\"foo\":[[100, null, 200]]}', 'lax $.foo'"
+        + "returning integer array array)")
+        .columnType("INTEGER ARRAY ARRAY");
   }
 
   @Test void testJsonQuery() {