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 0dd30b5cd7 [CALCITE-6950] Use ANY operator to check if an element
exists in an array throws exception
0dd30b5cd7 is described below
commit 0dd30b5cd72353d84825d194b3739766db8e921c
Author: Xiong Duan <[email protected]>
AuthorDate: Thu Jun 12 07:51:41 2025 +0800
[CALCITE-6950] Use ANY operator to check if an element exists in an array
throws exception
---
.../calcite/sql/fun/SqlQuantifyOperator.java | 43 ++++++++++---
.../sql/validate/implicit/TypeCoercion.java | 18 ++++++
.../sql/validate/implicit/TypeCoercionImpl.java | 70 ++++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 29 +++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 44 ++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 46 ++++++++++++++
6 files changed, 243 insertions(+), 7 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java
index dbd02f8e86..aeb770acba 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java
@@ -17,16 +17,21 @@
package org.apache.calcite.sql.fun;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
+import com.google.common.collect.ImmutableList;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
@@ -102,15 +107,33 @@ public class SqlQuantifyOperator extends SqlInOperator {
final SqlNode left = call.operand(0);
final SqlNode right = call.operand(1);
if (right instanceof SqlNodeList && ((SqlNodeList) right).size() == 1) {
- final RelDataType rightType =
+ RelDataType rightType =
validator.deriveType(scope, ((SqlNodeList) right).get(0));
if (SqlTypeUtil.isCollection(rightType)) {
- final RelDataType componentRightType =
- requireNonNull(rightType.getComponentType());
- final RelDataType leftType = validator.deriveType(scope, left);
- if (SqlTypeUtil.sameNamedType(componentRightType, leftType)
- || SqlTypeUtil.isNull(leftType)
- || SqlTypeUtil.isNull(componentRightType)) {
+ RelDataType componentRightType =
requireNonNull(rightType.getComponentType());
+ RelDataType leftType = validator.deriveType(scope, left);
+ boolean isCompatibleType = isCompatibleType(componentRightType,
leftType);
+ SqlCall sqlCall =
+ new SqlBasicCall(call.getOperator(),
+ ImmutableList.of(left, ((SqlNodeList) right).get(0)),
SqlParserPos.ZERO);
+ SqlCallBinding callBinding = new SqlCallBinding(validator, scope,
sqlCall);
+ if (!isCompatibleType && callBinding.isTypeCoercionEnabled()) {
+ boolean coerced = callBinding.getValidator().getTypeCoercion()
+ .quantifyOperationCoercion(callBinding);
+ if (coerced) {
+ // Update the node data type if we coerced any type.
+ call.setOperand(0,
+ callBinding.operand(0));
+ call.setOperand(1,
+ new SqlNodeList(ImmutableList.of(callBinding.operand(1)),
SqlParserPos.ZERO));
+ leftType = validator.deriveType(scope, call.operand(0));
+ rightType = validator.deriveType(scope, ((SqlNodeList)
right).get(0));
+ componentRightType = rightType.getComponentType();
+ requireNonNull(componentRightType, "componentRightType");
+ isCompatibleType = isCompatibleType(componentRightType, leftType);
+ }
+ }
+ if (isCompatibleType) {
return validator.getTypeFactory().createTypeWithNullability(
validator.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN),
rightType.isNullable()
@@ -124,6 +147,12 @@ public class SqlQuantifyOperator extends SqlInOperator {
return null;
}
+ private static boolean isCompatibleType(RelDataType leftType, RelDataType
rightType) {
+ return SqlTypeUtil.sameNamedType(rightType, leftType)
+ || SqlTypeUtil.isNull(leftType)
+ || SqlTypeUtil.isNull(rightType);
+ }
+
@Override public SqlOperator not() {
switch (kind) {
case SOME:
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 525dfee2b2..f3d1d528fd 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
@@ -120,6 +120,24 @@ boolean rowTypeCoercion(
*/
boolean inOperationCoercion(SqlCallBinding binding);
+ /** Handles type coercion for Quantify operations {@link
org.apache.calcite.sql.fun.SqlQuantifyOperator}.
+ *
+ * <p>For example:
+ * <ul>
+ * <li>{@code 1.0 = some (ARRAY[2,3,null])}
+ * <li>{@code 'timestamp 1970-01-01 01:23:47' =
+ * any (array['1970-01-01 01:23:45', '1970-01-01 01:23:46'])}
+ * <li>{@code WITH tb as
+ * (select
+ * array(SELECT * FROM (VALUES ('1970-01-01 01:23:45'), ('1970-01-01
01:23:46'))
+ * as x(a)) as a)
+ * SELECT timestamp '1970-01-01 01:23:45' >= some (a) FROM tb}
+ * </ul>
+ *
+ * <p>See {@link TypeCoercionImpl} for default strategies.
+ * */
+ boolean quantifyOperationCoercion(SqlCallBinding binding);
+
/** Coerces operand of binary arithmetic expressions to Numeric type.*/
boolean binaryArithmeticCoercion(SqlCallBinding binding);
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 10e1d36d0c..f144ec8399 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
@@ -598,6 +598,76 @@ private boolean coalesceCoercion(SqlCallBinding
callBinding) {
return false;
}
+ /**
+ * {@inheritDoc}
+ *
+ * <p>STRATEGIES
+ *
+ * <p>To determine the common type:
+ *
+ * <ul>
+ *
+ * <li>When the LHS has a Simple type and RHS has a Collection type
determined by {@link SqlTypeUtil#isCollection},
+ * to find the common type of LHS's type and RHS's component type.
+ * <li>If the common type differs from the LHS type, then coerced LHS type,
+ * and the nullability of the result type remains unchanged.
+ * <li>Create a new Collection type that matches the common type,
+ * the nullability of the new type keep same as RHS's component type and
RHS's collection type.
+ * <li>If this new type differs from the RHS type, adjust the RHS type as
needed.
+ *
+ *<pre>
+ * field1 ARRAY(field2, field3, field4)
+ * | | | |
+ * | +-------+-------+
+ * | |
+ * | component type
+ * | |
+ * +------common type-------+
+ *</pre>
+ *
+ * <li>If LHS type and the component type of RHS are different from the
common type,
+ * CAST needs to be added.
+ * </ul>
+ */
+ @Override public boolean quantifyOperationCoercion(SqlCallBinding binding) {
+ final RelDataType type1 = binding.getOperandType(0);
+ final RelDataType collectionType = binding.getOperandType(1);
+ final RelDataType type2 = collectionType.getComponentType();
+ requireNonNull(type2, "type2");
+ final SqlCall sqlCall = binding.getCall();
+ final SqlValidatorScope scope = binding.getScope();
+ final SqlNode node1 = binding.operand(0);
+ final SqlNode node2 = binding.operand(1);
+ RelDataType widenType = commonTypeForBinaryComparison(type1, type2);
+ if (widenType == null) {
+ widenType = getTightestCommonType(type1, type2);
+ }
+ if (widenType == null) {
+ return false;
+ }
+ final RelDataType leftWidenType =
+ binding.getTypeFactory().enforceTypeWithNullability(widenType,
type1.isNullable());
+ boolean coercedLeft =
+ coerceOperandType(scope, sqlCall, 0, leftWidenType);
+ if (coercedLeft) {
+ updateInferredType(node1, leftWidenType);
+ }
+ final RelDataType rightWidenType =
+ binding.getTypeFactory().enforceTypeWithNullability(widenType,
type2.isNullable());
+ RelDataType collectionWidenType =
+ binding.getTypeFactory().createArrayType(rightWidenType, -1);
+ collectionWidenType =
+ binding
+ .getTypeFactory()
+ .enforceTypeWithNullability(collectionWidenType,
collectionType.isNullable());
+ boolean coercedRight =
+ coerceOperandType(scope, sqlCall, 1, collectionWidenType);
+ if (coercedRight) {
+ updateInferredType(node2, collectionWidenType);
+ }
+ return coercedLeft || coercedRight;
+ }
+
@Override public boolean builtinFunctionCoercion(
SqlCallBinding binding,
List<RelDataType> operandTypes,
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index cc6a80488e..96d956a040 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3789,6 +3789,35 @@ void checkCorrelatedMapSubQuery(boolean expand) {
.ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6950";>[CALCITE-6950]
+ * Use ANY operator to check if an element exists in an array throws
exception</a>. */
+ @Test void testQuantifyOperatorsWithTypeCoercion() {
+ sql("SELECT 1.0 = some (ARRAY[2,null,3])")
+ .withExpand(false)
+ .ok();
+ }
+
+ @Test void testQuantifyOperatorsWithTypeCoercion2() {
+ sql("SELECT 3 = some (ARRAY[1.0, 2.0])")
+ .withExpand(false)
+ .ok();
+ }
+
+ @Test void testQuantifyOperatorsWithTypeCoercion3() {
+ sql("SELECT '1970-01-01 01:23:45' = any (array[timestamp '1970-01-01
01:23:45',"
+ + "timestamp '1970-01-01 01:23:46'])")
+ .withExpand(false)
+ .ok();
+ }
+
+ @Test void testQuantifyOperatorsWithTypeCoercion4() {
+ sql("SELECT timestamp '1970-01-01 01:23:45' = any (array['1970-01-01
01:23:45',"
+ + "'1970-01-01 01:23:46'])")
+ .withExpand(false)
+ .ok();
+ }
+
@Test void testQualifyInCorrelatedSubQuery() {
// The QUALIFY clause is inside a WHERE EXISTS and references columns from
// the enclosing query.
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 0c7463f7b9..760d4c5465 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -6727,6 +6727,50 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7],
QualifyExpression=[=(ROW_NUMBER() OVER (PARTITION BY $1 ORDER BY $7), 1)])
LogicalFilter(condition=[>($7, 5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testQuantifyOperatorsWithTypeCoercion">
+ <Resource name="sql">
+ <![CDATA[SELECT 1.0 = some (ARRAY[2,null,3])]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[= SOME(1.0:DECIMAL(11, 1), CAST(ARRAY(2, null:INTEGER,
3)):DECIMAL(11, 1) ARRAY NOT NULL)])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testQuantifyOperatorsWithTypeCoercion2">
+ <Resource name="sql">
+ <![CDATA[SELECT 3 = some (ARRAY[1.0, 2.0])]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[= SOME(3.0:DECIMAL(11, 1), CAST(ARRAY(1.0:DECIMAL(2,
1), 2.0:DECIMAL(2, 1))):DECIMAL(11, 1) NOT NULL ARRAY NOT NULL)])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testQuantifyOperatorsWithTypeCoercion3">
+ <Resource name="sql">
+ <![CDATA[SELECT '1970-01-01 01:23:45' = any (array[timestamp '1970-01-01
01:23:45',timestamp '1970-01-01 01:23:46'])]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[= SOME(1970-01-01 01:23:45, ARRAY(1970-01-01 01:23:45,
1970-01-01 01:23:46))])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testQuantifyOperatorsWithTypeCoercion4">
+ <Resource name="sql">
+ <![CDATA[SELECT timestamp '1970-01-01 01:23:45' = any (array['1970-01-01
01:23:45','1970-01-01 01:23:46'])]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[= SOME(1970-01-01 01:23:45, CAST(ARRAY('1970-01-01
01:23:45', '1970-01-01 01:23:46')):TIMESTAMP(0) NOT NULL ARRAY NOT NULL)])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
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 9313823fdc..a20ad2d326 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -16145,6 +16145,52 @@ void testTimestampDiff(boolean coercionEnabled) {
"Values passed to = SOME operator must have compatible types", false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6950";>[CALCITE-6950]
+ * Use ANY operator to check if an element exists in an array throws
exception</a>. */
+ @Test void testQuantifyOperatorsWithTypeCoercion() {
+ final SqlOperatorFixture f = fixture();
+ QUANTIFY_OPERATORS.forEach(operator -> f.setFor(operator,
SqlOperatorFixture.VmName.EXPAND));
+
+ f.checkNull("1.0 = some (ARRAY[2,3,null])");
+ f.checkNull("1.0 = some (ARRAY[2,null,3])");
+ f.enableTypeCoercion(false).checkFails(
+ "^1.0 = some (ARRAY[2,3,null])^",
+ "Values passed to = SOME operator must have compatible types",
+ false);
+
+ f.checkBoolean("1.0 = some (ARRAY[1,2,null])", true);
+ f.checkBoolean("3.0 = some (ARRAY[1,2])", false);
+ f.enableTypeCoercion(false).checkFails(
+ "^3.0 = some (ARRAY[1,2])^",
+ "Values passed to = SOME operator must have compatible types",
+ false);
+
+ f.checkBoolean(
+ "'1970-01-01 01:23:45' = any (array[timestamp '1970-01-01 01:23:45',"
+ + "timestamp '1970-01-01 01:23:46'])", true);
+ f.checkBoolean(
+ "'1970-01-01 01:23:47' = any (array[timestamp '1970-01-01 01:23:45',"
+ + "timestamp '1970-01-01 01:23:46'])", false);
+ f.enableTypeCoercion(false).checkFails(
+ "^'1970-01-01 01:23:47' = any (array[timestamp '1970-01-01 01:23:45',"
+ + "timestamp '1970-01-01 01:23:46'])^",
+ "Values passed to = SOME operator must have compatible types",
+ false);
+
+ f.checkBoolean(
+ "cast('1970-01-01 01:23:45' as timestamp) = any (array['1970-01-01
01:23:45',"
+ + "'1970-01-01 01:23:46'])", true);
+ f.checkBoolean(
+ "cast('1970-01-01 01:23:47' as timestamp) = any (array['1970-01-01
01:23:45',"
+ + "'1970-01-01 01:23:46'])", false);
+ f.enableTypeCoercion(false).checkFails(
+ "^cast('1970-01-01 01:23:47' as timestamp) = any (array['1970-01-01
01:23:45',"
+ + "'1970-01-01 01:23:46'])^",
+ "Values passed to = SOME operator must have compatible types",
+ false);
+ }
+
@Test void testAnyValueFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.ANY_VALUE, VM_EXPAND);
