This is an automated email from the ASF dual-hosted git repository.
dmsysolyatin 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 ce3d1333b9 [CALCITE-5160] ANY/SOME, ALL operators should support
collection expressions
ce3d1333b9 is described below
commit ce3d1333b909099565f18a0a22bbe64cd5e33c67
Author: dssysolyatin <[email protected]>
AuthorDate: Mon Jun 13 18:25:46 2022 +0300
[CALCITE-5160] ANY/SOME, ALL operators should support collection expressions
---
.../calcite/adapter/enumerable/RexImpTable.java | 124 ++++++++++++++++++++-
.../org/apache/calcite/runtime/SqlFunctions.java | 40 +++++++
.../calcite/sql/fun/SqlQuantifyOperator.java | 53 +++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 15 +++
.../apache/calcite/sql2rel/SqlToRelConverter.java | 16 ++-
.../calcite/sql2rel/StandardConvertletTable.java | 18 +++
.../org/apache/calcite/util/BuiltInMethod.java | 5 +
site/_docs/reference.md | 6 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 96 ++++++++++++++++
9 files changed, 368 insertions(+), 5 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 6f74859d38..c2cb550cca 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -29,6 +29,7 @@ import org.apache.calcite.linq4j.tree.ConstantExpression;
import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.linq4j.tree.ExpressionType;
import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.linq4j.tree.FunctionExpression;
import org.apache.calcite.linq4j.tree.MemberExpression;
import org.apache.calcite.linq4j.tree.MethodCallExpression;
import org.apache.calcite.linq4j.tree.NewExpression;
@@ -60,6 +61,7 @@ import org.apache.calcite.sql.SqlTypeConstructorFunction;
import org.apache.calcite.sql.SqlWindowTableFunction;
import org.apache.calcite.sql.fun.SqlJsonArrayAggAggFunction;
import org.apache.calcite.sql.fun.SqlJsonObjectAggAggFunction;
+import org.apache.calcite.sql.fun.SqlQuantifyOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -191,6 +193,12 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_SECONDS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.XML_TRANSFORM;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ABS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ACOS;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_EQ;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_GE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_GT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_LE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_LT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ALL_NE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.AND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ANY_VALUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ARG_MAX;
@@ -333,6 +341,12 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.SIN;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SINGLE_VALUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SLICE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_EQ;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_GE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_GT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_LE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_LT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SOME_NE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.STRUCT_ACCESS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBMULTISET_OF;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBSTRING;
@@ -749,6 +763,19 @@ public class RexImpTable {
map.put(CURRENT_ROLE, systemFunctionImplementor);
map.put(CURRENT_CATALOG, systemFunctionImplementor);
+ defineQuantify(SOME_EQ, EQUALS);
+ defineQuantify(SOME_GT, GREATER_THAN);
+ defineQuantify(SOME_GE, GREATER_THAN_OR_EQUAL);
+ defineQuantify(SOME_LE, LESS_THAN_OR_EQUAL);
+ defineQuantify(SOME_LT, LESS_THAN);
+ defineQuantify(SOME_NE, NOT_EQUALS);
+ defineQuantify(ALL_EQ, EQUALS);
+ defineQuantify(ALL_GT, GREATER_THAN);
+ defineQuantify(ALL_GE, GREATER_THAN_OR_EQUAL);
+ defineQuantify(ALL_LE, LESS_THAN_OR_EQUAL);
+ defineQuantify(ALL_LT, LESS_THAN);
+ defineQuantify(ALL_NE, NOT_EQUALS);
+
// Current time functions
map.put(CURRENT_TIME, systemFunctionImplementor);
map.put(CURRENT_TIMESTAMP, systemFunctionImplementor);
@@ -856,6 +883,11 @@ public class RexImpTable {
new BinaryImplementor(nullPolicy, true, expressionType,
backupMethodName));
}
+
+ private void defineQuantify(SqlQuantifyOperator operator,
SqlBinaryOperator binaryOperator) {
+ final RexCallImplementor binaryImplementor =
requireNonNull(map.get(binaryOperator));
+ map.put(operator, new QuantifyCollectionImplementor(binaryOperator,
binaryImplementor));
+ }
}
public static CallImplementor createImplementor(
@@ -3436,7 +3468,7 @@ public class RexImpTable {
}
// E.g., "final boolean xxx_isNull = xxx_value == null"
- private ParameterExpression genIsNullStatement(
+ protected final ParameterExpression genIsNullStatement(
final RexToLixTranslator translator, final ParameterExpression value) {
final ParameterExpression isNullVariable =
Expressions.parameter(Boolean.TYPE,
@@ -3921,6 +3953,96 @@ public class RexImpTable {
}
}
+ /**
+ * Implements
+ * <a
href="https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.16";>
+ * ANY/SOME</a> and
+ * <a
href="https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.17";>ALL</a>
+ * operators when the argument is an array or multiset expression.
+ */
+ private static class QuantifyCollectionImplementor extends
AbstractRexCallImplementor {
+ private final SqlBinaryOperator binaryOperator;
+ private final RexCallImplementor binaryImplementor;
+
+ QuantifyCollectionImplementor(SqlBinaryOperator binaryOperator,
+ RexCallImplementor binaryImplementor) {
+ super("quantify", NullPolicy.ANY, false);
+ this.binaryOperator = binaryOperator;
+ this.binaryImplementor = binaryImplementor;
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator, RexCall
call,
+ List<Expression> argValueList) {
+ Expression left = argValueList.get(0);
+ Expression right = argValueList.get(1);
+ final RelDataType rightComponentType =
+
requireNonNull(call.getOperands().get(1).getType().getComponentType());
+ // If the array expression yields a null array, the result of SOME|ALL
will be null
+ if (rightComponentType.getSqlTypeName() == SqlTypeName.NULL) {
+ return NULL_EXPR;
+ }
+
+ // The expression generated by this method will look as follows:
+ // final T _quantify_left_value = <left_value>
+ // <Function1|Predicate1> lambda =
+ // new org.apache.calcite.linq4j.function.<Function1|Predicate1>() {
+ // public Boolean apply(T el) {
+ // return <binaryImplementor code>(_quantify_left_value, el);
+ // }
+ // }
+ // If the lambda returns java.lang.Boolean then the lambda can return
null.
+ // In this case nullableExists or nullableSome should be used:
+ // return
org.apache.calcite.runtime.SqlFunctions.<nullableExists|nullableSome>(_list,
lambda)
+ // otherwise:
+ // return
org.apache.calcite.linq4j.function.Functions.<exists|all>(_list, lambda)
+ BlockBuilder lambdaBuilder = new BlockBuilder();
+ final ParameterExpression leftExpr =
+ Expressions.parameter(left.getType(),
+ translator.getBlockBuilder().newName("_" + getVariableName() +
"_left_value"));
+ // left should have final modifier otherwise it can not be passed to
lambda
+ translator.getBlockBuilder().add(Expressions.declare(Modifier.FINAL,
leftExpr, left));
+ RexNode leftRex = call.getOperands().get(0);
+ final ParameterExpression lambdaArg =
+
Expressions.parameter(translator.typeFactory.getJavaClass(rightComponentType),
"el");
+ final RexCall binaryImplementorRexCall =
+ (RexCall) translator.builder.makeCall(binaryOperator, leftRex,
+ translator.builder.makeDynamicParam(rightComponentType, 0));
+ final List<RexToLixTranslator.Result> binaryImplementorArgs =
+ ImmutableList.of(
+ new RexToLixTranslator.Result(
+ genIsNullStatement(translator, leftExpr), leftExpr),
+ new RexToLixTranslator.Result(
+ genIsNullStatement(translator, lambdaArg), lambdaArg));
+ final RexToLixTranslator.Result condition =
+ binaryImplementor.implement(translator, binaryImplementorRexCall,
binaryImplementorArgs);
+ lambdaBuilder.add(Expressions.return_(null, condition.valueVariable));
+ final FunctionExpression<?> predicate =
+ Expressions.lambda(lambdaBuilder.toBlock(), lambdaArg);
+ return Expressions.call(getMethod(condition.valueVariable.getType(),
call.getKind()), right,
+ predicate);
+ }
+
+ private static Method getMethod(Type comparisonReturnType, SqlKind kind) {
+ switch (kind) {
+ case SOME:
+ return Primitive.is(comparisonReturnType)
+ ? BuiltInMethod.COLLECTION_EXISTS.method
+ // if the array contains any null elements and no true comparison
result is obtained,
+ // the result of SOME will be null, not false.
+ : BuiltInMethod.COLLECTION_NULLABLE_EXISTS.method;
+ case ALL:
+ return Primitive.is(comparisonReturnType)
+ ? BuiltInMethod.COLLECTION_ALL.method
+ // if the array contains any null elements and no false comparison
result is obtained,
+ // the result of ALL will be null, not true.
+ : BuiltInMethod.COLLECTION_NULLABLE_ALL.method;
+ default:
+ throw new IllegalArgumentException("Unknown quantify operator " + kind
+ + ". Only support SOME, ALL.");
+ }
+ }
+ }
+
/** Implements the {@code SESSION} table function. */
private static class SessionImplementor implements
TableFunctionCallImplementor {
@Override public Expression implement(RexToLixTranslator translator,
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 50e4cd043c..fcd88bac1e 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -3682,6 +3682,46 @@ public class SqlFunctions {
return args;
}
+ /**
+ * Returns whether there is an element in {@code list} for which {@code
predicate} is true.
+ * Also, if {@code predicate} returns null for any element of {@code list}
+ * and does not return {@code true} for any element of {@code list},
+ * the result will be {@code null}, not {@code false}.
+ */
+ public static @Nullable <E> Boolean nullableExists(List<? extends E> list,
+ Function1<E, Boolean> predicate) {
+ boolean nullExists = false;
+ for (E e : list) {
+ Boolean res = predicate.apply(e);
+ if (res == null) {
+ nullExists = true;
+ } else if (res) {
+ return true;
+ }
+ }
+ return nullExists ? null : false;
+ }
+
+ /**
+ * Returns whether {@code predicate} is true for all elements of {@code
list}.
+ * Also, if {@code predicate} returns null for any element of {@code list}
+ * and does not return {@code false} for any element,
+ * the result will be {@code null}, not {@code true}.
+ */
+ public static @Nullable <E> Boolean nullableAll(List<? extends E> list,
+ Function1<E, Boolean> predicate) {
+ boolean nullExists = false;
+ for (E e : list) {
+ Boolean res = predicate.apply(e);
+ if (res == null) {
+ nullExists = true;
+ } else if (!res) {
+ return false;
+ }
+ }
+ return nullExists ? null : true;
+ }
+
/** Similar to {@link Linq4j#product(Iterable)} but each resulting list
* implements {@link FlatLists.ComparableList}. */
public static <E extends Comparable> Enumerable<FlatLists.ComparableList<E>>
product(
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 37250cd7cf..f259627fe6 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
@@ -16,10 +16,21 @@
*/
package org.apache.calcite.sql.fun;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+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.base.Preconditions;
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
import java.util.Objects;
/**
@@ -59,4 +70,46 @@ public class SqlQuantifyOperator extends SqlInOperator {
Preconditions.checkArgument(kind == SqlKind.SOME
|| kind == SqlKind.ALL);
}
+
+
+ @Override public RelDataType deriveType(SqlValidator validator,
+ SqlValidatorScope scope, SqlCall call) {
+ final List<SqlNode> operands = call.getOperandList();
+ assert operands.size() == 2;
+
+ RelDataType typeForCollectionArgument =
tryDeriveTypeForCollection(validator, scope, call);
+ if (typeForCollectionArgument != null) {
+ return typeForCollectionArgument;
+ }
+ return super.deriveType(validator, scope, call);
+ }
+
+ /**
+ * Derive type for SOME(collection expression), ANY (collection expression).
+ *
+ * @param validator Validator
+ * @param scope Scope of validation
+ * @param call Call to this operator
+ * @return If SOME or ALL is applied to a collection, then the function
+ * returns type of call, otherwise it returns null.
+ */
+ public @Nullable RelDataType tryDeriveTypeForCollection(SqlValidator
validator,
+ SqlValidatorScope scope, SqlCall call) {
+ final SqlNode left = call.operand(0);
+ final SqlNode right = call.operand(1);
+ if (right instanceof SqlNodeList && ((SqlNodeList) right).size() == 1) {
+ final RelDataType rightType = validator.deriveType(scope, ((SqlNodeList)
right).get(0));
+ if (SqlTypeUtil.isCollection(rightType)) {
+ final RelDataType componentRightType =
Objects.requireNonNull(rightType.getComponentType());
+ final RelDataType leftType = validator.deriveType(scope, left);
+ if (SqlTypeUtil.sameNamedType(componentRightType, leftType)
+ || SqlTypeUtil.isNull(leftType) ||
SqlTypeUtil.isNull(componentRightType)) {
+ return validator.getTypeFactory().createTypeWithNullability(
+ validator.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN),
+ rightType.isNullable() || componentRightType.isNullable() ||
leftType.isNullable());
+ }
+ }
+ }
+ return null;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 5351b4b914..e1427ab35b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -478,6 +478,21 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
public static final SqlQuantifyOperator ALL_NE =
new SqlQuantifyOperator(SqlKind.ALL, SqlKind.NOT_EQUALS);
+ public static final List<SqlQuantifyOperator> QUANTIFY_OPERATORS =
+ ImmutableList.of(SqlStdOperatorTable.SOME_EQ,
+ SqlStdOperatorTable.SOME_GT,
+ SqlStdOperatorTable.SOME_GE,
+ SqlStdOperatorTable.SOME_LE,
+ SqlStdOperatorTable.SOME_LT,
+ SqlStdOperatorTable.SOME_NE,
+
+ SqlStdOperatorTable.ALL_EQ,
+ SqlStdOperatorTable.ALL_GT,
+ SqlStdOperatorTable.ALL_GE,
+ SqlStdOperatorTable.ALL_LE,
+ SqlStdOperatorTable.ALL_LT,
+ SqlStdOperatorTable.ALL_NE);
+
/**
* Logical less-than operator, '<code><</code>'.
*/
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index e298500c3c..a0659bfc90 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2124,6 +2124,16 @@ public class SqlToRelConverter {
default:
break;
}
+ if (node instanceof SqlBasicCall
+ && ((SqlCall) node).getOperator() instanceof SqlQuantifyOperator
+ && ((SqlQuantifyOperator) ((SqlCall) node).getOperator())
+ .tryDeriveTypeForCollection(bb.getValidator(), bb.scope(),
(SqlCall) node) != null) {
+ findSubQueries(bb, ((SqlCall) node).operand(0), logic,
registerOnlyScalarSubQueries,
+ clause);
+ findSubQueries(bb, ((SqlCall) node).operand(1), logic,
registerOnlyScalarSubQueries,
+ clause);
+ break;
+ }
bb.registerSubQuery(node, logic, clause);
break;
default:
@@ -5451,7 +5461,11 @@ public class SqlToRelConverter {
case CURSOR:
case IN:
case NOT_IN:
- subQuery = requireNonNull(getSubQuery(expr, null));
+ subQuery = getSubQuery(expr, null);
+ if (subQuery == null && (kind == SqlKind.SOME || kind == SqlKind.ALL))
{
+ break;
+ }
+ assert subQuery != null;
rex = requireNonNull(subQuery.expr);
return StandardConvertletTable.castToValidatedType(expr, rex,
validator(), rexBuilder, false);
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index f230426925..e9b5e7d7b0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -98,6 +98,7 @@ import java.util.stream.Collectors;
import static com.google.common.base.Preconditions.checkArgument;
+import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.QUANTIFY_OPERATORS;
import static
org.apache.calcite.sql.type.NonNullableAccessors.getComponentTypeOrThrow;
import static org.apache.calcite.util.Util.first;
@@ -212,6 +213,9 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.TIMESTAMP_SUB,
new TimestampSubConvertlet());
+ QUANTIFY_OPERATORS.forEach(operator ->
+ registerOp(operator,
StandardConvertletTable::convertQuantifyOperator));
+
registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl);
registerOp(SqlLibraryOperators.DECODE,
StandardConvertletTable::convertDecode);
@@ -357,6 +361,20 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
}
+ /** Converts ALL or SOME operators. */
+ private static RexNode convertQuantifyOperator(SqlRexContext cx, SqlCall
call) {
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final RexNode left = cx.convertExpression(call.getOperandList().get(0));
+ assert call.getOperandList().get(1) instanceof SqlNodeList;
+ final RexNode right = cx.convertExpression(((SqlNodeList)
call.getOperandList().get(1)).get(0));
+ final RelDataType rightComponentType =
requireNonNull(right.getType().getComponentType());
+ final RelDataType returnType =
+ cx.getTypeFactory().createTypeWithNullability(
+ cx.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN),
right.getType().isNullable()
+ || left.getType().isNullable() ||
rightComponentType.isNullable());
+ return rexBuilder.makeCall(returnType, call.getOperator(),
ImmutableList.of(left, right));
+ }
+
/** Converts a call to the {@code NVL} function (and also its synonym,
* {@code IFNULL}). */
private static RexNode convertNvl(SqlRexContext cx, SqlCall call) {
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index f45d3f065a..9edc77b636 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -309,6 +309,11 @@ public enum BuiltInMethod {
COLLECTIONS_EMPTY_LIST(Collections.class, "emptyList"),
COLLECTIONS_SINGLETON_LIST(Collections.class, "singletonList", Object.class),
COLLECTION_SIZE(Collection.class, "size"),
+ COLLECTION_EXISTS(Functions.class, "exists", List.class, Predicate1.class),
+ COLLECTION_ALL(Functions.class, "all", List.class, Predicate1.class),
+ COLLECTION_NULLABLE_EXISTS(SqlFunctions.class, "nullableExists",
+ List.class, Function1.class),
+ COLLECTION_NULLABLE_ALL(SqlFunctions.class, "nullableAll", List.class,
Function1.class),
MAP_CLEAR(Map.class, "clear"),
MAP_GET(Map.class, "get", Object.class),
MAP_GET_OR_DEFAULT(Map.class, "getOrDefault", Object.class, Object.class),
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 0b4f67af3e..b784755401 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1281,9 +1281,9 @@ completeness.
| value NOT IN (value [, value]*) | Whether *value* is not
equal to every value in a list
| value IN (sub-query) | Whether *value* is equal
to a row returned by *sub-query*
| value NOT IN (sub-query) | Whether *value* is not
equal to every row returned by *sub-query*
-| value comparison SOME (sub-query) | Whether *value*
*comparison* at least one row returned by *sub-query*
-| value comparison ANY (sub-query) | Synonym for `SOME`
-| value comparison ALL (sub-query) | Whether *value*
*comparison* every row returned by *sub-query*
+| value comparison SOME (sub-query or collection) | Whether *value*
*comparison* at least one row returned by *sub-query* or *collection*
+| value comparison ANY (sub-query or collection) | Synonym for `SOME`
+| value comparison ALL (sub-query or collection) | Whether *value*
*comparison* every row returned by *sub-query* or *collection*
| EXISTS (sub-query) | Whether *sub-query*
returns at least one row
| UNIQUE (sub-query) | Whether the rows
returned by *sub-query* are unique (ignoring null values)
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 9bf5ccf5bd..50524b8de1 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -19,6 +19,8 @@ package org.apache.calcite.test;
import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.linq4j.Linq4j;
+import org.apache.calcite.linq4j.function.Function1;
+import org.apache.calcite.linq4j.function.Function2;
import org.apache.calcite.linq4j.tree.Types;
import org.apache.calcite.plan.Strong;
import org.apache.calcite.rel.type.DelegatingTypeSystem;
@@ -109,6 +111,7 @@ import java.util.stream.Stream;
import static org.apache.calcite.linq4j.tree.Expressions.list;
import static org.apache.calcite.rel.type.RelDataTypeImpl.NON_NULLABLE_SUFFIX;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PI;
+import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.QUANTIFY_OPERATORS;
import static org.apache.calcite.sql.test.ResultCheckers.isExactly;
import static org.apache.calcite.sql.test.ResultCheckers.isNullValue;
import static org.apache.calcite.sql.test.ResultCheckers.isSet;
@@ -9601,6 +9604,99 @@ public class SqlOperatorTest {
f.checkAgg("some(x = 2)", values, isSingle("true"));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5160";>[CALCITE-5160]
+ * ANY/SOME, ALL operators should support collection expressions</a>. */
+ @Test void testQuantifyCollectionOperators() {
+ final SqlOperatorFixture f = fixture();
+ QUANTIFY_OPERATORS.forEach(operator -> f.setFor(operator,
SqlOperatorFixture.VmName.EXPAND));
+
+ Function2<String, Boolean, Void> checkBoolean = (sql, result) -> {
+ f.checkBoolean(sql.replace("COLLECTION", "ARRAY"), result);
+ f.checkBoolean(sql.replace("COLLECTION", "MULTISET"), result);
+ return null;
+ };
+
+ Function1<String, Void> checkNull = sql -> {
+ f.checkNull(sql.replace("COLLECTION", "ARRAY"));
+ f.checkNull(sql.replace("COLLECTION", "MULTISET"));
+ return null;
+ };
+
+ checkNull.apply("1 = some (COLLECTION[2,3,null])");
+ checkNull.apply("null = some (COLLECTION[1,2,3])");
+ checkNull.apply("null = some (COLLECTION[1,2,null])");
+ checkNull.apply("1 = some (COLLECTION[null,null,null])");
+ checkNull.apply("null = some (COLLECTION[null,null,null])");
+
+ checkBoolean.apply("1 = some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("3 = some (COLLECTION[1,2])", false);
+
+ checkBoolean.apply("1 <> some (COLLECTION[1])", false);
+ checkBoolean.apply("2 <> some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("3 <> some (COLLECTION[1,2,null])", true);
+
+ checkBoolean.apply("1 < some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("0 < some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("2 < some (COLLECTION[1,2])", false);
+
+ checkBoolean.apply("2 <= some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("0 <= some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("3 <= some (COLLECTION[1,2])", false);
+
+ checkBoolean.apply("2 > some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("3 > some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("1 > some (COLLECTION[1,2])", false);
+
+ checkBoolean.apply("2 >= some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("3 >= some (COLLECTION[1,2,null])", true);
+ checkBoolean.apply("0 >= some (COLLECTION[1,2])", false);
+
+ f.check("SELECT 3 = some(x.t) FROM (SELECT ARRAY[1,2,3,null] as t) as x",
+ "BOOLEAN", true);
+ f.check("SELECT 4 = some(x.t) FROM (SELECT ARRAY[1,2,3] as t) as x",
+ "BOOLEAN NOT NULL", false);
+ f.check("SELECT 4 = some(x.t) FROM (SELECT ARRAY[1,2,3,null] as t) as x",
+ "BOOLEAN", isNullValue());
+ f.check("SELECT (SELECT * FROM UNNEST(ARRAY[3]) LIMIT 1) = "
+ + "some(x.t) FROM (SELECT ARRAY[1,2,3,null] as t) as x",
+ "BOOLEAN", true);
+
+
+ checkNull.apply("1 = all (COLLECTION[1,1,null])");
+ checkNull.apply("null = all (COLLECTION[1,2,3])");
+ checkNull.apply("null = all (COLLECTION[1,2,null])");
+ checkNull.apply("1 = all (COLLECTION[null,null,null])");
+ checkNull.apply("null = all (COLLECTION[null,null,null])");
+
+ checkBoolean.apply("1 = all (COLLECTION[1,1])", true);
+ checkBoolean.apply("3 = all (COLLECTION[1,3,null])", false);
+
+ checkBoolean.apply("1 <> all (COLLECTION[2,3,4])", true);
+ checkBoolean.apply("2 <> all (COLLECTION[2,null])", false);
+
+ checkBoolean.apply("1 < all (COLLECTION[2,3,4])", true);
+ checkBoolean.apply("2 < all (COLLECTION[1,2,null])", false);
+
+ checkBoolean.apply("2 <= all (COLLECTION[2,3,4])", true);
+ checkBoolean.apply("1 <= all (COLLECTION[0,1,null])", false);
+
+ checkBoolean.apply("2 > all (COLLECTION[0,1])", true);
+ checkBoolean.apply("3 > all (COLLECTION[1,3,null])", false);
+
+ checkBoolean.apply("2 >= all (COLLECTION[0,1,2])", true);
+ checkBoolean.apply("3 >= all (COLLECTION[3,4,null])", false);
+
+ f.check("SELECT 3 >= all(x.t) FROM (SELECT ARRAY[1,2,3] as t) as x",
+ "BOOLEAN NOT NULL", true);
+ f.check("SELECT 4 = all(x.t) FROM (SELECT ARRAY[1,2,3,null] as t) as x",
+ "BOOLEAN", false);
+ f.check("SELECT 4 = all(x.t) FROM (SELECT ARRAY[4,4,null] as t) as x",
+ "BOOLEAN", isNullValue());
+ f.check("SELECT (SELECT * FROM UNNEST(ARRAY[3]) LIMIT 1) = "
+ + "all(x.t) FROM (SELECT ARRAY[3,3] as t) as x",
+ "BOOLEAN", true);
+ }
@Test void testAnyValueFunc() {
final SqlOperatorFixture f = fixture();
