This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 e9f7dfc318 [CALCITE-6991] Validator cannot infer type for COALESCE
when call is not expanded
e9f7dfc318 is described below
commit e9f7dfc318b15198df617a9cdb0f477d30997517
Author: Juntao Zhang <[email protected]>
AuthorDate: Wed Apr 30 18:33:34 2025 +0800
[CALCITE-6991] Validator cannot infer type for COALESCE when call is not
expanded
---
.../apache/calcite/sql/fun/SqlCaseOperator.java | 2 +-
.../calcite/sql/fun/SqlCoalesceFunction.java | 51 ++++++++++++++++++++++
.../sql/validate/implicit/TypeCoercion.java | 25 +++++++++--
.../sql/validate/implicit/TypeCoercionImpl.java | 39 ++++++++++++++++-
.../org/apache/calcite/test/SqlValidatorTest.java | 16 ++++++-
5 files changed, 126 insertions(+), 7 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCaseOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlCaseOperator.java
index 0c547fb876..4d5cbf9a6d 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCaseOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCaseOperator.java
@@ -272,7 +272,7 @@ private static RelDataType inferTypeFromValidator(
// (with the correct nullability) in SqlValidator
// instead of the commonType as the return type.
if (null != commonType) {
- coerced = typeCoercion.caseWhenCoercion(callBinding);
+ coerced = typeCoercion.caseOrEquivalentCoercion(callBinding);
if (coerced) {
ret = SqlTypeUtil.deriveType(callBinding);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
index 1dcfe477f9..cd3c46040c 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
@@ -16,21 +16,34 @@
*/
package org.apache.calcite.sql.fun;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
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.implicit.TypeCoercion;
import org.apache.calcite.util.Util;
+import org.checkerframework.checker.nullness.qual.NonNull;
+
+import java.util.ArrayList;
import java.util.List;
+import static org.apache.calcite.util.Static.RESOURCE;
+
+import static java.util.Objects.requireNonNull;
+
/**
* The <code>COALESCE</code> function.
*/
@@ -80,4 +93,42 @@ public SqlCoalesceFunction() {
assert call.getFunctionQuantifier() == null;
return SqlCase.createSwitched(pos, null, whenList, thenList, elseExpr);
}
+
+ @Override @NonNull public RelDataType inferReturnType(
+ SqlOperatorBinding opBinding) {
+ RelDataType returnType =
getReturnTypeInference().inferReturnType(opBinding);
+ if (returnType == null
+ && opBinding instanceof SqlCallBinding
+ && ((SqlCallBinding) opBinding).isTypeCoercionEnabled()) {
+ SqlCallBinding callBinding = (SqlCallBinding) opBinding;
+ List<RelDataType> argTypes = new ArrayList<>();
+ for (SqlNode operand : callBinding.operands()) {
+ RelDataType type = SqlTypeUtil.deriveType(callBinding, operand);
+ argTypes.add(type);
+ }
+ TypeCoercion typeCoercion = callBinding.getValidator().getTypeCoercion();
+ RelDataType commonType = typeCoercion.getWiderTypeFor(argTypes, true);
+ if (null != commonType) {
+ // COALESCE type coercion, find a common type across all branches and
casts
+ // operands to this common type if necessary.
+ boolean coerced = typeCoercion.caseOrEquivalentCoercion(callBinding);
+ if (coerced) {
+ return SqlTypeUtil.deriveType(callBinding);
+ }
+ }
+ throw callBinding.newValidationError(RESOURCE.illegalMixingOfTypes());
+ }
+
+ if (returnType == null) {
+ throw new IllegalArgumentException(
+ "Cannot infer return type for " + opBinding.getOperator() + ";
operand types: "
+ + opBinding.collectOperandTypes());
+ }
+
+ return returnType;
+ }
+
+ @Override public SqlReturnTypeInference getReturnTypeInference() {
+ return requireNonNull(super.getReturnTypeInference(),
"returnTypeInference");
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
index 2a343cca1b..525dfee2b2 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
@@ -129,10 +129,29 @@ boolean rowTypeCoercion(
/**
* Coerces CASE WHEN statement branches to one common type.
*
- * <p>Rules: Find common type for all the then operands and else operands,
- * then try to coerce the then/else operands to the type if needed.
+ * @deprecated Use {@link #caseOrEquivalentCoercion} instead.
*/
- boolean caseWhenCoercion(SqlCallBinding binding);
+ @Deprecated boolean caseWhenCoercion(SqlCallBinding binding);
+
+ /**
+ * Type coercion in CASE WHEN, COALESCE, and NULLIF.
+ *
+ * <p>Rules:
+ * <ol>
+ * <li>
+ * CASE WHEN collect all the branches types including then
+ * operands and else operands to find a common type, then cast the
operands to the common type
+ * when needed.</li>
+ * <li>
+ * COALESCE collect all the branches types to find a common type,
+ * then cast the operands to the common type when needed.</li>
+ * <li>
+ * NULLIF returns the first operand if the two operands are not equal,
+ * otherwise it returns a null value of the type of the first operand,
+ * without return type coercion.</li>
+ * </ol>
+ */
+ boolean caseOrEquivalentCoercion(SqlCallBinding binding);
/**
* Type coercion with inferred type from passed in arguments and the
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
index 711c8d22dc..10e1d36d0c 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
@@ -383,11 +383,12 @@ protected boolean booleanEquality(SqlCallBinding binding,
}
/**
- * CASE and COALESCE type coercion, collect all the branches types including
then
+ * CASE WHEN type coercion, collect all the branches types including then
* operands and else operands to find a common type, then cast the operands
to the common type
* when needed.
*/
- @Override public boolean caseWhenCoercion(SqlCallBinding callBinding) {
+ @SuppressWarnings("deprecation")
+ public boolean caseWhenCoercion(SqlCallBinding callBinding) {
// For sql statement like:
// `case when ... then (a, b, c) when ... then (d, e, f) else (g, h, i)`
// an exception throws when entering this method.
@@ -422,6 +423,40 @@ protected boolean booleanEquality(SqlCallBinding binding,
return false;
}
+ /**
+ * Coerces CASE WHEN and COALESCE statement branches to a unified type.
+ * NULLIF returns the same type as the first operand without return type
coercion.
+ */
+ @Override public boolean caseOrEquivalentCoercion(SqlCallBinding
callBinding) {
+ if (callBinding.getCall().getKind() == SqlKind.COALESCE) {
+ // For sql statement like: `coalesce(a, b, c)`
+ return coalesceCoercion(callBinding);
+ } else if (callBinding.getCall().getKind() == SqlKind.NULLIF) {
+ // For sql statement like: `nullif(a, b)`
+ return false;
+ } else {
+ assert callBinding.getCall() instanceof SqlCase;
+ return caseWhenCoercion(callBinding);
+ }
+ }
+
+ /**
+ * COALESCE type coercion, collect all the branches types to find a common
type,
+ * then cast the operands to the common type when needed.
+ */
+ private boolean coalesceCoercion(SqlCallBinding callBinding) {
+ List<RelDataType> argTypes = new ArrayList<>();
+ SqlValidatorScope scope = getScope(callBinding);
+ for (SqlNode node : callBinding.operands()) {
+ argTypes.add(validator.deriveType(scope, node));
+ }
+ RelDataType widerType = getWiderTypeFor(argTypes, true);
+ if (null != widerType) {
+ return coerceOperandsType(scope, callBinding.getCall(), widerType);
+ }
+ return false;
+ }
+
/**
* {@inheritDoc}
*
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 7be6bcf4b2..19ef2b0347 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -704,6 +704,16 @@ static SqlOperatorTable operatorTableFor(SqlLibrary
library) {
.columnType("DECIMAL(5, 2)");
expr("nullif(345.21, 2e0)")
.columnType("DECIMAL(5, 2)");
+ expr("nullif(DATE '2020-01-01', '2020-01-01')")
+ .columnType("DATE");
+ expr("nullif('2020-01-01', DATE '2020-01-01')")
+ .columnType("CHAR(10)");
+ expr("nullif(DATE '2020-01-01', '2020-01-01')")
+ .withValidatorConfig(c -> c.withCallRewrite(false))
+ .columnType("DATE");
+ expr("nullif('2020-01-01', DATE '2020-01-01')")
+ .withValidatorConfig(c -> c.withCallRewrite(false))
+ .columnType("CHAR(10)");
wholeExpr("nullif(1,2,3)")
.fails("Invalid number of arguments to function 'NULLIF'. Was "
+ "expecting 2 arguments");
@@ -713,7 +723,11 @@ static SqlOperatorTable operatorTableFor(SqlLibrary
library) {
expr("coalesce('a','b')").ok();
expr("coalesce('a','b','c')")
.columnType("CHAR(1) NOT NULL");
-
+ expr("COALESCE(DATE '2020-01-01', '2020-01-02')")
+ .columnType("VARCHAR NOT NULL");
+ expr("COALESCE(DATE '2020-01-01', '2020-01-02')")
+ .withValidatorConfig(c -> c.withCallRewrite(false))
+ .columnType("VARCHAR NOT NULL");
sql("select COALESCE(mgr, 12) as m from EMP")
.columnType("INTEGER NOT NULL");
}
