This is an automated email from the ASF dual-hosted git repository.
guohongyu 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 e877885ed9 [CALCITE-6116] Add EXISTS function (enabled in Spark
library)
e877885ed9 is described below
commit e877885ed90127a4cadb25f1b718f91375fe6164
Author: Hongyu Guo <[email protected]>
AuthorDate: Wed Dec 13 14:27:10 2023 +0800
[CALCITE-6116] Add EXISTS function (enabled in Spark library)
Following [CALCITE-3679] Allow lambda expressions in SQL queries
* Refactoring LambdaOperandTypeChecker into an abstract class
* Add LambdaRelOperandTypeChecker and LambdaFamilyOperandTypeChecker
* Incorrect validation when lambda expression is null
---
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 17 +++
.../calcite/sql/fun/SqlLibraryOperators.java | 8 +
.../org/apache/calcite/sql/type/OperandTypes.java | 168 +++++++++++++++++----
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 39 ++++-
.../apache/calcite/test/SqlToRelConverterTest.java | 12 ++
.../org/apache/calcite/test/SqlValidatorTest.java | 2 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 11 ++
core/src/test/resources/sql/lambda.iq | 104 +++++++++++++
site/_docs/reference.md | 8 +-
.../java/org/apache/calcite/test/QuidemTest.java | 6 +
.../org/apache/calcite/test/SqlOperatorTest.java | 72 +++++++++
13 files changed, 421 insertions(+), 29 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 77cd73d24f..c1288c730d 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
@@ -174,6 +174,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.DATE_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DAYNAME;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DIFFERENCE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ENDS_WITH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS_NODE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_VALUE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_XML;
@@ -839,6 +840,7 @@ public class RexImpTable {
defineMethod(ARRAY_UNION, BuiltInMethod.ARRAY_UNION.method,
NullPolicy.ANY);
defineMethod(ARRAYS_OVERLAP, BuiltInMethod.ARRAYS_OVERLAP.method,
NullPolicy.ANY);
defineMethod(ARRAYS_ZIP, BuiltInMethod.ARRAYS_ZIP.method,
NullPolicy.ANY);
+ defineMethod(EXISTS, BuiltInMethod.EXISTS.method, NullPolicy.ANY);
defineMethod(MAP_CONCAT, BuiltInMethod.MAP_CONCAT.method,
NullPolicy.ANY);
defineMethod(MAP_ENTRIES, BuiltInMethod.MAP_ENTRIES.method,
NullPolicy.STRICT);
defineMethod(MAP_KEYS, BuiltInMethod.MAP_KEYS.method, NullPolicy.STRICT);
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 adaf232add..abc2b7e3cf 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -32,6 +32,7 @@ import org.apache.calcite.linq4j.function.Deterministic;
import org.apache.calcite.linq4j.function.Experimental;
import org.apache.calcite.linq4j.function.Function1;
import org.apache.calcite.linq4j.function.NonDeterministic;
+import org.apache.calcite.linq4j.function.Predicate1;
import org.apache.calcite.linq4j.tree.Primitive;
import org.apache.calcite.rel.type.TimeFrame;
import org.apache.calcite.rel.type.TimeFrameSet;
@@ -5403,6 +5404,22 @@ public class SqlFunctions {
return list;
}
+ /** Support the EXISTS(list, function1) function. */
+ public static @Nullable Boolean exists(List list, Function1<Object, Boolean>
function1) {
+ return nullableExists(list, function1);
+ }
+
+ /** Support the EXISTS(list, predicate1) function. */
+ public static Boolean exists(List list, Predicate1 predicate1) {
+ for (Object element : list) {
+ boolean ret = predicate1.apply(element);
+ if (ret) {
+ return true;
+ }
+ }
+ return false;
+ }
+
/** Support the MAP_CONCAT function. */
public static Map mapConcat(Map... maps) {
final Map result = new LinkedHashMap();
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 7f113cc96d..878e7e0ab3 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
@@ -1193,6 +1193,14 @@ public abstract class SqlLibraryOperators {
SqlLibraryOperators::arrayAppendPrependReturnType,
OperandTypes.ARRAY_ELEMENT);
+ /** The "EXISTS(array, lambda)" function (Spark); returns whether a
predicate holds
+ * for one or more elements in the array. */
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction EXISTS =
+ SqlBasicFunction.create("EXISTS",
+ ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.EXISTS);
+
@SuppressWarnings("argument.type.incompatible")
private static RelDataType arrayCompactReturnType(SqlOperatorBinding
opBinding) {
final RelDataType arrayType = opBinding.collectOperandTypes().get(0);
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 0b56e1db7b..0c68f0eacd 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -115,7 +115,7 @@ public abstract class OperandTypes {
*/
public static SqlSingleOperandTypeChecker function(SqlTypeFamily
returnTypeFamily,
SqlTypeFamily... paramTypeFamilies) {
- return new LambdaOperandTypeChecker(
+ return new LambdaFamilyOperandTypeChecker(
returnTypeFamily, ImmutableList.copyOf(paramTypeFamilies));
}
@@ -126,7 +126,7 @@ public abstract class OperandTypes {
*/
public static SqlSingleOperandTypeChecker function(SqlTypeFamily
returnTypeFamily,
List<SqlTypeFamily> paramTypeFamilies) {
- return new LambdaOperandTypeChecker(returnTypeFamily, paramTypeFamilies);
+ return new LambdaFamilyOperandTypeChecker(returnTypeFamily,
paramTypeFamilies);
}
/**
@@ -1135,6 +1135,39 @@ public abstract class OperandTypes {
}
};
+ public static final SqlOperandTypeChecker EXISTS =
+ new SqlOperandTypeChecker() {
+ @Override public boolean checkOperandTypes(
+ SqlCallBinding callBinding,
+ boolean throwOnFailure) {
+ // The first operand must be an array type
+ ARRAY.checkSingleOperandType(callBinding, callBinding.operand(0), 0,
throwOnFailure);
+ final RelDataType arrayType =
+ SqlTypeUtil.deriveType(callBinding, callBinding.operand(0));
+ final RelDataType componentType =
+ requireNonNull(arrayType.getComponentType(), "componentType");
+
+ // The second operand is a function(array_element_type)->boolean type
+ LambdaRelOperandTypeChecker lambdaChecker =
+ new LambdaRelOperandTypeChecker(
+ SqlTypeFamily.BOOLEAN,
+ ImmutableList.of(componentType));
+ return lambdaChecker.checkSingleOperandType(
+ callBinding,
+ callBinding.operand(1),
+ 1,
+ throwOnFailure);
+ }
+
+ @Override public SqlOperandCountRange getOperandCountRange() {
+ return SqlOperandCountRanges.of(2);
+ }
+
+ @Override public String getAllowedSignatures(SqlOperator op, String
opName) {
+ return "EXISTS(<ARRAY>, <FUNCTION(ARRAY_ELEMENT_TYPE)->BOOLEAN>)";
+ }
+ };
+
/**
* Checker for record just has one field.
*/
@@ -1442,18 +1475,17 @@ public abstract class OperandTypes {
/**
* Operand type-checking strategy where the type of the operand is a lambda
- * expression with a given return type and argument types.
+ * expression with a given return type and argument {@link SqlTypeFamily}s.
*/
- private static class LambdaOperandTypeChecker
- implements SqlSingleOperandTypeChecker {
+ private static class LambdaFamilyOperandTypeChecker
+ extends LambdaOperandTypeChecker {
- private final SqlTypeFamily returnTypeFamily;
private final List<SqlTypeFamily> argFamilies;
- LambdaOperandTypeChecker(
+ LambdaFamilyOperandTypeChecker(
SqlTypeFamily returnTypeFamily,
List<SqlTypeFamily> argFamilies) {
- this.returnTypeFamily = requireNonNull(returnTypeFamily,
"returnTypeFamily");
+ super(returnTypeFamily);
this.argFamilies = ImmutableList.copyOf(argFamilies);
}
@@ -1481,40 +1513,125 @@ public abstract class OperandTypes {
}
final SqlLambda lambdaExpr = (SqlLambda) operand;
- if (lambdaExpr.getParameters().isEmpty()
- || argFamilies.stream().allMatch(f -> f == SqlTypeFamily.ANY)
- || returnTypeFamily == SqlTypeFamily.ANY) {
- return true;
+ if (SqlUtil.isNullLiteral(lambdaExpr.getExpression(), false)) {
+ checkNull(callBinding, lambdaExpr, throwOnFailure);
}
- if (SqlUtil.isNullLiteral(lambdaExpr.getExpression(), false)) {
- if (callBinding.isTypeCoercionEnabled()) {
- return true;
+ final SqlValidator validator = callBinding.getValidator();
+ if (!lambdaExpr.getParameters().isEmpty()
+ && !argFamilies.stream().allMatch(f -> f == SqlTypeFamily.ANY)) {
+ // Replace the parameter types in the lambda expression.
+ final SqlLambdaScope scope =
+ (SqlLambdaScope) validator.getLambdaScope(lambdaExpr);
+ for (int i = 0; i < argFamilies.size(); i++) {
+ final SqlNode param = lambdaExpr.getParameters().get(i);
+ final RelDataType type =
+
argFamilies.get(i).getDefaultConcreteType(callBinding.getTypeFactory());
+ if (type != null) {
+ scope.getParameterTypes().put(param.toString(), type);
+ }
}
+ lambdaExpr.accept(new TypeRemover(validator));
+ // Given the new relDataType, re-validate the lambda expression.
+ validator.validateLambda(lambdaExpr);
+ }
+ return checkReturnType(validator, callBinding, lambdaExpr,
throwOnFailure);
+ }
+ }
+
+ /**
+ * Operand type-checking strategy where the type of the operand is a lambda
+ * expression with a given return type and argument {@link RelDataType}s.
+ */
+ private static class LambdaRelOperandTypeChecker
+ extends LambdaOperandTypeChecker {
+ private final List<RelDataType> argTypes;
+
+ LambdaRelOperandTypeChecker(
+ SqlTypeFamily returnTypeFamily,
+ List<RelDataType> argTypes) {
+ super(returnTypeFamily);
+ this.argTypes = argTypes;
+ }
+
+ @Override public String getAllowedSignatures(SqlOperator op, String
opName) {
+ ImmutableList.Builder<SqlTypeFamily> builder = ImmutableList.builder();
+ argTypes.stream()
+ .map(t -> requireNonNull(t.getSqlTypeName().getFamily()))
+ .forEach(builder::add);
+ builder.add(returnTypeFamily);
+
+ return SqlUtil.getAliasedSignature(op, opName, builder.build());
+ }
+
+ @Override public boolean checkSingleOperandType(SqlCallBinding
callBinding, SqlNode operand,
+ int iFormalOperand,
+ boolean throwOnFailure) {
+ if (!(operand instanceof SqlLambda)
+ || ((SqlLambda) operand).getParameters().size() != argTypes.size()) {
if (throwOnFailure) {
- throw
callBinding.getValidator().newValidationError(lambdaExpr.getExpression(),
- RESOURCE.nullIllegal());
+ throw callBinding.newValidationSignatureError();
}
return false;
}
+ final SqlLambda lambdaExpr = (SqlLambda) operand;
+ if (SqlUtil.isNullLiteral(lambdaExpr.getExpression(), false)) {
+ checkNull(callBinding, lambdaExpr, throwOnFailure);
+ }
+
// Replace the parameter types in the lambda expression.
final SqlValidator validator = callBinding.getValidator();
final SqlLambdaScope scope =
(SqlLambdaScope) validator.getLambdaScope(lambdaExpr);
- for (int i = 0; i < argFamilies.size(); i++) {
+ for (int i = 0; i < argTypes.size(); i++) {
final SqlNode param = lambdaExpr.getParameters().get(i);
- final RelDataType type =
-
argFamilies.get(i).getDefaultConcreteType(callBinding.getTypeFactory());
+ final RelDataType type = argTypes.get(i);
if (type != null) {
scope.getParameterTypes().put(param.toString(), type);
}
}
lambdaExpr.accept(new TypeRemover(validator));
-
// Given the new relDataType, re-validate the lambda expression.
validator.validateLambda(lambdaExpr);
+
+ return checkReturnType(validator, callBinding, lambdaExpr,
throwOnFailure);
+ }
+ }
+
+ /**
+ * Abstract base class for type-checking strategies involving lambda
expressions.
+ * This class provides common functionality for checking the type of lambda
expression.
+ */
+ private abstract static class LambdaOperandTypeChecker
+ implements SqlSingleOperandTypeChecker {
+ protected final SqlTypeFamily returnTypeFamily;
+
+ LambdaOperandTypeChecker(SqlTypeFamily returnTypeFamily) {
+ this.returnTypeFamily = requireNonNull(returnTypeFamily,
"returnTypeFamily");
+ }
+
+ protected boolean checkNull(
+ SqlCallBinding callBinding,
+ SqlLambda lambdaExpr,
+ boolean throwOnFailure) {
+ if (callBinding.isTypeCoercionEnabled()) {
+ return true;
+ }
+
+ if (throwOnFailure) {
+ throw
callBinding.getValidator().newValidationError(lambdaExpr.getExpression(),
+ RESOURCE.nullIllegal());
+ }
+ return false;
+ }
+
+ protected boolean checkReturnType(
+ SqlValidator validator,
+ SqlCallBinding callBinding,
+ SqlLambda lambdaExpr,
+ boolean throwOnFailure) {
final RelDataType newType = validator.getValidatedNodeType(lambdaExpr);
assert newType instanceof FunctionSqlType;
final SqlTypeName returnTypeName =
@@ -1533,14 +1650,14 @@ public abstract class OperandTypes {
/**
* Visitor that removes the relDataType of a sqlNode and its children in
the
* validator. Now this visitor is only used for removing the relDataType
- * in {@code LambdaOperandTypeChecker}. Since lambda expressions
- * will be validated for the second time based on the given parameter type,
+ * when we check lambda operand. Since lambda expressions will be
+ * validated for the second time based on the given parameter type,
* the type cached during the first validation must be cleared.
*/
- private static class TypeRemover extends SqlBasicVisitor<Void> {
+ protected static class TypeRemover extends SqlBasicVisitor<Void> {
private final SqlValidator validator;
- TypeRemover(SqlValidator validator) {
+ protected TypeRemover(SqlValidator validator) {
this.validator = validator;
}
@@ -1553,7 +1670,6 @@ public abstract class OperandTypes {
validator.removeValidatedNodeType(call);
return super.visit(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 89f3318a28..142f1d30d2 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -773,6 +773,7 @@ public enum BuiltInMethod {
ARRAY_REVERSE(SqlFunctions.class, "reverse", List.class),
ARRAYS_OVERLAP(SqlFunctions.class, "arraysOverlap", List.class, List.class),
ARRAYS_ZIP(SqlFunctions.class, "arraysZip", List.class, List.class),
+ EXISTS(SqlFunctions.class, "exists", List.class, Function1.class),
SORT_ARRAY(SqlFunctions.class, "sortArray", List.class, boolean.class),
MAP(SqlFunctions.class, "map", Object[].class),
MAP_CONCAT(SqlFunctions.class, "mapConcat", Map[].class),
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 79c340e2da..aedc4bdc37 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -7430,7 +7430,7 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"employee\"";
sql(sql3).ok(expected3);
- final String sql4 = "select higher_order_function2(1, () -> null)";
+ final String sql4 = "select higher_order_function2(1, () -> cast(null as
integer))";
final String expected4 = "SELECT HIGHER_ORDER_FUNCTION2("
+ "1, () -> NULL)\nFROM (VALUES (0)) AS \"t\" (\"ZERO\")";
sql(sql4).ok(expected4);
@@ -7452,6 +7452,43 @@ class RelToSqlConverterTest {
sql(sql6).ok(expected6);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6116";>[CALCITE-6116]
+ * Add EXISTS function (enabled in Spark library)</a>. */
+ @Test void testExistsFunctionInSpark() {
+ final String sql = "select \"EXISTS\"(array[1,2,3], x -> x > 2)";
+ final String expected = "SELECT EXISTS(ARRAY[1, 2, 3], \"X\" -> \"X\" >
2)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(sql)
+ .withLibrary(SqlLibrary.SPARK)
+ .ok(expected);
+
+ final String sql2 = "select \"EXISTS\"(array[1,2,3], (x) -> false)";
+ final String expected2 = "SELECT EXISTS(ARRAY[1, 2, 3], \"X\" -> FALSE)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(sql2)
+ .withLibrary(SqlLibrary.SPARK)
+ .ok(expected2);
+
+ // empty array
+ final String sql3 = "select \"EXISTS\"(array(), (x) -> false)";
+ final String expected3 = "SELECT EXISTS(ARRAY(), \"X\" -> FALSE)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(sql3)
+ .withLibrary(SqlLibrary.SPARK)
+ .ok(expected3);
+
+ final String sql4 = "select \"EXISTS\"('string', (x) -> false)";
+ final String error4 = "org.apache.calcite.runtime.CalciteContextException:
"
+ + "From line 1, column 8 to line 1, column 39: "
+ + "Cannot apply 'EXISTS' to arguments of type "
+ + "'EXISTS(<CHAR(6)>, <FUNCTION(ANY) -> BOOLEAN>)'. "
+ + "Supported form(s): EXISTS(<ARRAY>,
<FUNCTION(ARRAY_ELEMENT_TYPE)->BOOLEAN>)";
+ sql(sql4)
+ .withLibrary(SqlLibrary.SPARK)
+ .throws_(error4);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5265";>[CALCITE-5265]
* JDBC adapter sometimes adds unnecessary parentheses around SELECT in
INSERT</a>. */
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 6a43a487f7..cd95b88aef 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -137,6 +137,18 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
.ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6116";>[CALCITE-6116]
+ * Add EXISTS function (enabled in Spark library)</a>. */
+ @Test void testExistsFunctionInSpark() {
+ final String sql = "select \"EXISTS\"(array(1,2,3), x -> false)";
+ fixture()
+ .withFactory(c ->
+ c.withOperatorTable(t ->
SqlValidatorTest.operatorTableFor(SqlLibrary.SPARK)))
+ .withSql(sql)
+ .ok();
+ }
+
@Test void testDotLiteralAfterRow() {
final String sql = "select row(1,2).\"EXPR$1\" from emp";
sql(sql).ok();
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 3e9269b783..b790a81c84 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6696,7 +6696,7 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> x + 1)").ok();
s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> y)").ok();
s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> char_length(x) +
1)").ok();
- s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> null)").ok();
+ s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> cast(null as
integer))").ok();
s.withSql("select HIGHER_ORDER_FUNCTION2(1, () -> 0.1)").ok();
s.withSql("select emp.deptno, HIGHER_ORDER_FUNCTION(1, (x, deptno) ->
deptno) from emp").ok();
s.withSql("select HIGHER_ORDER_FUNCTION(1, (x, y) -> char_length(x) + 1)")
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 bf246b3a0b..9db58746c3 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1976,6 +1976,17 @@ LogicalProject(EMPNO=[$0])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject($f9=[CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT
NULL, 'M':VARCHAR(20))], $f0=[true])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExistsFunctionInSpark">
+ <Resource name="sql">
+ <![CDATA[select "EXISTS"(array(1,2,3), x -> false)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[EXISTS(ARRAY(1, 2, 3), (X) -> false)])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/lambda.iq
b/core/src/test/resources/sql/lambda.iq
new file mode 100644
index 0000000000..207543ec77
--- /dev/null
+++ b/core/src/test/resources/sql/lambda.iq
@@ -0,0 +1,104 @@
+# lambda.iq - Queries involving functions with lambda as arguments
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use sparkfunc
+!set outputformat mysql
+
+# EXISTS
+select "EXISTS"(array(1,2,3), x -> x = 2);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array(1,2,3), x -> x = 4);
++--------+
+| EXPR$0 |
++--------+
+| false |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array(1,2,3), x -> x > 2 and x < 4);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array(1,2,3), x -> power(x, 2) = 9);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array(-1,-2,-3), x -> abs(x) = 0);
++--------+
+| EXPR$0 |
++--------+
+| false |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array(1,2,3), x -> cast(null as boolean));
++--------+
+| EXPR$0 |
++--------+
+| |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(cast(null as integer array), x -> cast(null as boolean));
++--------+
+| EXPR$0 |
++--------+
+| |
++--------+
+(1 row)
+
+!ok
+
+select "EXISTS"(array[1, 2, 3], 1);
+Cannot apply 'EXISTS' to arguments of type 'EXISTS(<INTEGER ARRAY>, <INTEGER>)'
+!error
+
+select "EXISTS"(array[array[1, 2], array[3, 4]], x -> x[1] = 1);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 1bcb076bf1..841c42650c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2651,6 +2651,9 @@ BigQuery's type system uses confusingly different names
for types and functions:
function, return a Calcite `TIMESTAMP WITH LOCAL TIME ZONE`;
* Similarly, `DATETIME(string)` returns a Calcite `TIMESTAMP`.
+In the following:
+* *func* is a lambda argument.
+
| C | Operator syntax | Description
|:- |:-----------------------------------------------|:-----------
| p | expr :: type | Casts *expr* to *type*
@@ -2731,8 +2734,9 @@ BigQuery's type system uses confusingly different names
for types and functions:
| p | DIFFERENCE(string, string) | Returns a measure of
the similarity of two strings, namely the number of character positions that
their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0
if the `SOUNDEX` values are totally different
| f | ENDSWITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
| b p | ENDS_WITH(string1, string2) | Equivalent to
`ENDSWITH(string1, string2)`
-| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the XML
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
+| s | EXISTS(array, func) | Returns whether a
predicate *func* holds for one or more elements in the *array*
| o | EXISTSNODE(xml, xpath, [, namespaces ]) | Determines whether
traversal of a XML document using a specified xpath results in any nodes.
Returns 0 if no nodes remain after applying the XPath traversal on the document
fragment of the element or elements matched by the XPath expression. Returns 1
if any nodes remain. The optional namespace value that specifies a default
mapping or namespace mapping for prefixes, which is used when evaluating the
XPath expression.
+| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the XML
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
| m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the
first text node which is a child of the element or elements matched by the
XPath expression.
| h s | FACTORIAL(integer) | Returns the factorial
of *integer*, the range of *integer* is [0, 20]. Otherwise, returns NULL
| h s | FIND_IN_SET(matchStr, textStr) | Returns the index
(1-based) of the given *matchStr* in the comma-delimited *textStr*. Returns 0,
if the given *matchStr* is not found or if the *matchStr* contains a comma. For
example, FIND_IN_SET('bc', 'a,bc,def') returns 2
@@ -3139,6 +3143,8 @@ Higher-order functions are not included in the SQL
standard, so all the function
[Dialect-specific OperatorsPermalink]({{ site.baseurl
}}/docs/reference.html#dialect-specific-operators)
as well.
+Examples of functions with a lambda argument are *EXISTS*.
+
## User-defined functions
Calcite is extensible. You can define each kind of function using user code.
diff --git a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
index aec668e469..9760536492 100644
--- a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
@@ -306,6 +306,12 @@ public abstract class QuidemTest {
.with(CalciteAssert.Config.REGULAR)
.with(CalciteAssert.SchemaSpec.POST)
.connect();
+ case "sparkfunc":
+ return CalciteAssert.that()
+ .with(CalciteConnectionProperty.FUN, "spark")
+ .with(CalciteAssert.Config.REGULAR)
+ .with(CalciteAssert.SchemaSpec.POST)
+ .connect();
case "oraclefunc":
return CalciteAssert.that()
.with(CalciteConnectionProperty.FUN, "oracle")
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 8853610ca4..583fd06882 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6906,6 +6906,78 @@ public class SqlOperatorTest {
+ "'SORT_ARRAY\\(<ARRAY>, <BOOLEAN>\\)'", false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6116";>[CALCITE-6116]
+ * Add EXISTS function (enabled in Spark library)</a>. */
+ @Test void testExistsFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.EXISTS)
+ .withLibrary(SqlLibrary.SPARK);
+ // wrong return type in function
+ f.withValidatorConfig(t -> t.withTypeCoercionEnabled(false))
+ .checkFails("^\"EXISTS\"(array[1, 2, 3], x -> x + 1)^",
+ "Cannot apply 'EXISTS' to arguments of type "
+ + "'EXISTS\\(<INTEGER ARRAY>, <FUNCTION\\(INTEGER\\) ->
INTEGER>\\)'. "
+ + "Supported form\\(s\\): "
+ + "EXISTS\\(<ARRAY>,
<FUNCTION\\(ARRAY_ELEMENT_TYPE\\)->BOOLEAN>\\)",
+ false);
+
+ // bad number of arguments
+ f.checkFails("^\"EXISTS\"(array[1, 2, 3])^",
+ "Invalid number of arguments to function 'EXISTS'\\. Was expecting 2
arguments",
+ false);
+ f.checkFails("^\"EXISTS\"(array[1, 2, 3], x -> x > 2, x -> x > 2)^",
+ "Invalid number of arguments to function 'EXISTS'\\. Was expecting 2
arguments",
+ false);
+
+ // function should not be null
+ f.checkFails("^\"EXISTS\"(array[1, 2, 3], null)^",
+ "Cannot apply 'EXISTS' to arguments of type 'EXISTS\\(<INTEGER ARRAY>,
<NULL>\\)'. "
+ + "Supported form\\(s\\): "
+ + "EXISTS\\(<ARRAY>,
<FUNCTION\\(ARRAY_ELEMENT_TYPE\\)->BOOLEAN>\\)",
+ false);
+
+ // bad type
+ f.checkFails("^\"EXISTS\"(1, x -> x > 2)^",
+ "Cannot apply 'EXISTS' to arguments of type 'EXISTS\\(<INTEGER>, "
+ + "<FUNCTION\\(ANY\\) -> BOOLEAN>\\)'. "
+ + "Supported form\\(s\\): "
+ + "EXISTS\\(<ARRAY>,
<FUNCTION\\(ARRAY_ELEMENT_TYPE\\)->BOOLEAN>\\)",
+ false);
+ f.checkFails("^\"EXISTS\"(array[1, 2, 3], 1)^",
+ "Cannot apply 'EXISTS' to arguments of type 'EXISTS\\(<INTEGER ARRAY>,
<INTEGER>\\)'. "
+ + "Supported form\\(s\\): "
+ + "EXISTS\\(<ARRAY>,
<FUNCTION\\(ARRAY_ELEMENT_TYPE\\)->BOOLEAN>\\)",
+ false);
+
+ // simple expression
+ f.checkScalar("\"EXISTS\"(array[1, 2, 3], x -> x > 2)", true, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[1, 2, 3], x -> x > 3)", false, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[1, 2, 3], x -> false)", false, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[1, 2, 3], x -> true)", true, "BOOLEAN");
+
+ // empty array
+ f.checkScalar("\"EXISTS\"(array(), x -> true)", false, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array(), x -> false)", false, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array(), x -> cast(x as int) = 1)", false,
"BOOLEAN");
+
+ // complex expression
+ f.checkScalar("\"EXISTS\"(array[-1, 2, 3], y -> abs(y) = 1)", true,
"BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[-1, 2, 3], y -> abs(y) = 4)", false,
"BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[1, 2, 3], x -> x > 2 and x < 4)", true,
"BOOLEAN");
+
+ // complex array
+ f.checkScalar("\"EXISTS\"(array[array[1, 2], array[3, 4]], x -> x[1] =
1)", true, "BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[array[1, 2], array[3, 4]], x -> x[1] =
5)", false, "BOOLEAN");
+
+ // test for null
+ f.checkScalar("\"EXISTS\"(array[null, 3], x -> x > 2 or x < 4)", true,
"BOOLEAN");
+ f.checkScalar("\"EXISTS\"(array[null, 3], x -> x is null)", true,
"BOOLEAN");
+ f.checkNull("\"EXISTS\"(array[null, 3], x -> cast(null as boolean))");
+ f.checkNull("\"EXISTS\"(array[null, 3], x -> x = null)");
+ f.checkNull("\"EXISTS\"(cast(null as integer array), x -> x > 2)");
+ }
+
/** Tests {@code MAP_CONCAT} function from Spark. */
@Test void testMapConcatFunc() {
// 1. check with std map constructor, map[k, v ...]
