This is an automated email from the ASF dual-hosted git repository.
fengzhu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new f11115a [CALCITE-3815] Add missing SQL standard aggregate functions:
EVERY, SOME, INTERSECTION
f11115a is described below
commit f11115a2fe9e360f38910f112288581040e0ced5
Author: Hanumath Maduri <[email protected]>
AuthorDate: Sat Mar 7 21:35:38 2020 -0800
[CALCITE-3815] Add missing SQL standard aggregate functions: EVERY, SOME,
INTERSECTION
---
core/src/main/codegen/templates/Parser.jj | 3 +
.../calcite/adapter/enumerable/RexImpTable.java | 41 +++++++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 6 +-
.../calcite/sql/fun/SqlMinMaxAggFunction.java | 11 ++-
.../calcite/sql/fun/SqlStdOperatorTable.java | 24 +++++++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../apache/calcite/sql/parser/SqlParserTest.java | 16 ++++-
.../apache/calcite/sql/test/SqlAdvisorTest.java | 6 ++
.../calcite/sql/test/SqlOperatorBaseTest.java | 79 ++++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 7 ++
.../org/apache/calcite/test/SqlValidatorTest.java | 10 +++
.../apache/calcite/test/SqlToRelConverterTest.xml | 13 ++++
core/src/test/resources/sql/agg.iq | 22 ++++++
site/_docs/reference.md | 3 +
14 files changed, 236 insertions(+), 6 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 1946c63..eebd80c 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6401,10 +6401,12 @@ SqlIdentifier ReservedFunctionName() :
| <CURRENT_TIMESTAMP>
| <DENSE_RANK>
| <ELEMENT>
+ | <EVERY>
| <EXP>
| <FIRST_VALUE>
| <FLOOR>
| <FUSION>
+ | <INTERSECTION>
| <GROUPING>
| <HOUR>
| <LAG>
@@ -6433,6 +6435,7 @@ SqlIdentifier ReservedFunctionName() :
| <RIGHT>
| <ROW_NUMBER>
| <SECOND>
+ | <SOME>
| <SQRT>
| <STDDEV_POP>
| <STDDEV_SAMP>
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 795048f..629e2bd 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
@@ -167,6 +167,7 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ELEMENT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.EQUALS;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.EVERY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.EXP;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.EXTRACT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.FIRST_VALUE;
@@ -177,6 +178,7 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.GREATER_THAN_OR_EQU
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.GROUPING;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.GROUPING_ID;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.INITCAP;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.INTERSECTION;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.IS_A_SET;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.IS_EMPTY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.IS_FALSE;
@@ -263,6 +265,7 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.SIMILAR_TO;
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.STRUCT_ACCESS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBMULTISET_OF;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBSTRING;
@@ -623,6 +626,8 @@ public class RexImpTable {
aggMap.put(MIN, minMax);
aggMap.put(MAX, minMax);
aggMap.put(ANY_VALUE, minMax);
+ aggMap.put(SOME, minMax);
+ aggMap.put(EVERY, minMax);
final Supplier<BitOpImplementor> bitop =
constructorSupplier(BitOpImplementor.class);
aggMap.put(BIT_AND, bitop);
aggMap.put(BIT_OR, bitop);
@@ -631,6 +636,7 @@ public class RexImpTable {
aggMap.put(COLLECT, constructorSupplier(CollectImplementor.class));
aggMap.put(LISTAGG, constructorSupplier(ListaggImplementor.class));
aggMap.put(FUSION, constructorSupplier(FusionImplementor.class));
+ aggMap.put(INTERSECTION,
constructorSupplier(IntersectionImplementor.class));
final Supplier<GroupingImplementor> grouping =
constructorSupplier(GroupingImplementor.class);
aggMap.put(GROUPING, grouping);
@@ -1542,6 +1548,41 @@ public class RexImpTable {
}
}
+ /** Implementor for the {@code INTERSECTION} aggregate function. */
+ static class IntersectionImplementor extends StrictAggImplementor {
+ @Override protected void implementNotNullReset(AggContext info,
AggResetContext reset) {
+ reset.currentBlock().add(
+ Expressions.statement(
+ Expressions.assign(reset.accumulator().get(0),
Expressions.constant(null))));
+ }
+
+ @Override public void implementNotNullAdd(AggContext info, AggAddContext
add) {
+ BlockBuilder accumulatorIsNull = new BlockBuilder();
+ accumulatorIsNull.add(
+ Expressions.statement(
+ Expressions.assign(add.accumulator().get(0),
Expressions.new_(ArrayList.class))));
+ accumulatorIsNull.add(
+ Expressions.statement(
+ Expressions.call(add.accumulator().get(0),
+ BuiltInMethod.COLLECTION_ADDALL.method,
add.arguments().get(0))));
+
+ BlockBuilder accumulatorNotNull = new BlockBuilder();
+ accumulatorNotNull.add(
+ Expressions.statement(
+ Expressions.call(add.accumulator().get(0),
+ BuiltInMethod.COLLECTION_RETAIN_ALL.method,
+ add.arguments().get(0))
+ )
+ );
+
+ add.currentBlock().add(
+ Expressions.ifThenElse(
+ Expressions.equal(add.accumulator().get(0),
Expressions.constant(null)),
+ accumulatorIsNull.toBlock(),
+ accumulatorNotNull.toBlock()));
+ }
+ }
+
/** Implementor for the {@code FUSION} aggregate function. */
static class FusionImplementor extends StrictAggImplementor {
@Override protected void implementNotNullReset(AggContext info,
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index d5ac533..b3e42a8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -966,6 +966,9 @@ public enum SqlKind {
/** The {@code FUSION} aggregate function. */
FUSION,
+ /** The {@code INTERSECTION} aggregate function. */
+ INTERSECTION,
+
/** The {@code SINGLE_VALUE} aggregate function. */
SINGLE_VALUE,
@@ -1161,7 +1164,8 @@ public enum SqlKind {
LAST_VALUE, COVAR_POP, COVAR_SAMP, REGR_COUNT, REGR_SXX, REGR_SYY,
AVG, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, NTILE, COLLECT,
FUSION, SINGLE_VALUE, ROW_NUMBER, RANK, PERCENT_RANK, DENSE_RANK,
- CUME_DIST, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_AND, BIT_OR, BIT_XOR,
LISTAGG);
+ CUME_DIST, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_AND, BIT_OR, BIT_XOR,
+ LISTAGG, INTERSECTION, ANY_VALUE);
/**
* Category consisting of all DML operators.
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java
index e71d990..f6c3b22 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java
@@ -24,6 +24,7 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlSplittableAggFunction;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlOperandTypeChecker;
import org.apache.calcite.util.Optionality;
import com.google.common.base.Preconditions;
@@ -68,12 +69,18 @@ public class SqlMinMaxAggFunction extends SqlAggFunction {
/** Creates a SqlMinMaxAggFunction. */
public SqlMinMaxAggFunction(SqlKind kind) {
- super(kind.name(),
+ this(kind.name(), kind, OperandTypes.COMPARABLE_ORDERED);
+ }
+
+ /** Creates a SqlMinMaxAggFunction. */
+ public SqlMinMaxAggFunction(String funcName, SqlKind kind,
+ SqlOperandTypeChecker inputTypeChecker) {
+ super(funcName,
null,
kind,
ReturnTypes.ARG0_NULLABLE_IF_EMPTY,
null,
- OperandTypes.COMPARABLE_ORDERED,
+ inputTypeChecker,
SqlFunctionCategory.SYSTEM,
false,
false,
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 91dc944..e0ec497 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
@@ -943,6 +943,18 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
new SqlMinMaxAggFunction(SqlKind.MAX);
/**
+ * <code>EVERY</code> aggregate function.
+ */
+ public static final SqlAggFunction EVERY =
+ new SqlMinMaxAggFunction("EVERY", SqlKind.MIN, OperandTypes.BOOLEAN);
+
+ /**
+ * <code>SOME</code> aggregate function.
+ */
+ public static final SqlAggFunction SOME =
+ new SqlMinMaxAggFunction("SOME", SqlKind.MAX, OperandTypes.BOOLEAN);
+
+ /**
* <code>LAST_VALUE</code> aggregate function.
*/
public static final SqlAggFunction LAST_VALUE =
@@ -2219,6 +2231,18 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
};
/**
+ * The INTERSECTION operator. Multiset aggregator function.
+ */
+ public static final SqlAggFunction INTERSECTION =
+ new SqlAggFunction("INTERSECTION", null,
+ SqlKind.INTERSECTION,
+ ReturnTypes.ARG0,
+ null,
+ OperandTypes.MULTISET,
+ SqlFunctionCategory.SYSTEM, false, false,
+ Optionality.FORBIDDEN) {
+ };
+ /**
* The sequence next value function: <code>NEXT VALUE FOR sequence</code>
*/
public static final SqlOperator NEXT_VALUE =
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 7d30ac5..bb226a0 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -287,6 +287,7 @@ public enum BuiltInMethod {
MAP_PUT(Map.class, "put", Object.class, Object.class),
COLLECTION_ADD(Collection.class, "add", Object.class),
COLLECTION_ADDALL(Collection.class, "addAll", Collection.class),
+ COLLECTION_RETAIN_ALL(Collection.class, "retainAll", Collection.class),
LIST_GET(List.class, "get", int.class),
ITERATOR_HAS_NEXT(Iterator.class, "hasNext"),
ITERATOR_NEXT(Iterator.class, "next"),
diff --git
a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 83ff169..e96bf6b 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2139,6 +2139,12 @@ public class SqlParserTest {
+ "FROM `DEPT`))");
}
+ @Test public void testSomeEveryAndIntersectionAggQuery() {
+ sql("select some(deptno = 10), every(deptno > 0),
intersection(multiset[1,2]) from dept")
+ .ok("SELECT SOME((`DEPTNO` = 10)), EVERY((`DEPTNO` > 0)),
INTERSECTION((MULTISET[1, 2]))\n"
+ + "FROM `DEPT`");
+ }
+
/**
* Tricky for the parser - looks like "IN (scalar, scalar)" but isn't.
*/
@@ -2184,11 +2190,15 @@ public class SqlParserTest {
final String sql3 = "select * from emp\n"
+ "where name like (select ^some^ name from emp)";
- sql(sql3).fails("(?s).*Encountered \"some\" at .*");
+ sql(sql3).fails("(?s).*Encountered \"some name\" at .*");
final String sql4 = "select * from emp\n"
- + "where name ^like^ some (select name from emp)";
- sql(sql4).fails("(?s).*Encountered \"like some\" at .*");
+ + "where name like some (select name from emp)";
+ final String expected4 = "SELECT *\n"
+ + "FROM `EMP`\n"
+ + "WHERE (`NAME` LIKE SOME((SELECT `NAME`\n"
+ + "FROM `EMP`)))";
+ sql(sql4).ok(expected4);
final String sql5 = "select * from emp where empno = any (10,20)";
final String expected5 = "SELECT *\n"
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 09fc285..b0e5761 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -152,6 +152,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(DATE)",
"KEYWORD(DENSE_RANK)",
"KEYWORD(ELEMENT)",
+ "KEYWORD(EVERY)",
"KEYWORD(EXISTS)",
"KEYWORD(EXP)",
"KEYWORD(EXTRACT)",
@@ -161,6 +162,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(FUSION)",
"KEYWORD(GROUPING)",
"KEYWORD(HOUR)",
+ "KEYWORD(INTERSECTION)",
"KEYWORD(INTERVAL)",
"KEYWORD(JSON_ARRAY)",
"KEYWORD(JSON_ARRAYAGG)",
@@ -208,6 +210,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(RUNNING)",
"KEYWORD(SECOND)",
"KEYWORD(SESSION_USER)",
+ "KEYWORD(SOME)",
"KEYWORD(SPECIFIC)",
"KEYWORD(SQRT)",
"KEYWORD(SUBSTRING)",
@@ -1295,6 +1298,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"COLUMN(EMPNO)\n"
+ "COLUMN(ENAME)\n"
+ "KEYWORD(ELEMENT)\n"
+ + "KEYWORD(EVERY)\n"
+ "KEYWORD(EXISTS)\n"
+ "KEYWORD(EXP)\n"
+ "KEYWORD(EXTRACT)\n"
@@ -1308,6 +1312,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"COLUMN(EMPNO)\n"
+ "COLUMN(ENAME)\n"
+ "KEYWORD(ELEMENT)\n"
+ + "KEYWORD(EVERY)\n"
+ "KEYWORD(EXISTS)\n"
+ "KEYWORD(EXP)\n"
+ "KEYWORD(EXTRACT)\n"
@@ -1321,6 +1326,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"COLUMN(EMPNO)\n"
+ "COLUMN(ENAME)\n"
+ "KEYWORD(ELEMENT)\n"
+ + "KEYWORD(EVERY)\n"
+ "KEYWORD(EXISTS)\n"
+ "KEYWORD(EXP)\n"
+ "KEYWORD(EXTRACT)\n"
diff --git
a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index ca67272..8ad9f30 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -6944,6 +6944,28 @@ public abstract class SqlOperatorBaseTest {
@Test public void testFusionFunc() {
tester.setFor(SqlStdOperatorTable.FUSION, VM_FENNEL, VM_JAVA);
+ tester.checkFails("fusion(^*^)", "Unknown identifier '\\*'", false);
+ checkAggType(tester, "fusion(MULTISET[1,2,3])", "INTEGER NOT NULL MULTISET
NOT NULL");
+ strictTester.checkFails("^fusion(12)^",
+ "Cannot apply 'FUSION' to arguments of type .*", false);
+ final String[] values1 = {"MULTISET[0]", "MULTISET[1]", "MULTISET[2]",
"MULTISET[3]"};
+ tester.checkAgg("fusion(x)", values1, "[0, 1, 2, 3]", 0);
+ final String[] values2 = {"MULTISET[0,1]", "MULTISET[1, 2]"};
+ tester.checkAgg("fusion(x)", values2, "[0, 1, 1, 2]", 0);
+ }
+
+ @Test public void testIntersectionFunc() {
+ tester.setFor(SqlStdOperatorTable.INTERSECTION, VM_FENNEL, VM_JAVA);
+ tester.checkFails("intersection(^*^)", "Unknown identifier '\\*'", false);
+ checkAggType(tester, "intersection(MULTISET[1,2,3])", "INTEGER NOT NULL
MULTISET NOT NULL");
+ strictTester.checkFails("^intersection(12)^",
+ "Cannot apply 'INTERSECTION' to arguments of type .*", false);
+ final String[] values1 = {"MULTISET[0]", "MULTISET[1]", "MULTISET[2]",
"MULTISET[3]"};
+ tester.checkAgg("intersection(x)", values1, "[]", 0);
+ final String[] values2 = {"MULTISET[0, 1]", "MULTISET[1, 2]"};
+ tester.checkAgg("intersection(x)", values2, "[1]", 0);
+ final String[] values3 = {"MULTISET[0, 1, 1]", "MULTISET[0, 1, 2]"};
+ tester.checkAgg("intersection(x)", values3, "[0, 1, 1]", 0);
}
@Test public void testYear() {
@@ -8868,6 +8890,63 @@ public abstract class SqlOperatorBaseTest {
0d);
}
+ @Test public void testEveryFunc() {
+ tester.setFor(SqlStdOperatorTable.EVERY, VM_EXPAND);
+ tester.checkFails(
+ "every(^*^)",
+ "Unknown identifier '\\*'",
+ false);
+ tester.checkType("every(1 = 1)", "BOOLEAN");
+ tester.checkType("every(1.2 = 1.2)", "BOOLEAN");
+ tester.checkType("every(1.5 = 1.4)", "BOOLEAN");
+ tester.checkFails(
+ "^every()^",
+ "Invalid number of arguments to function 'EVERY'. Was expecting 1
arguments",
+ false);
+ tester.checkFails(
+ "^every(1, 2)^",
+ "Invalid number of arguments to function 'EVERY'. Was expecting 1
arguments",
+ false);
+ final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"};
+ if (!enable) {
+ return;
+ }
+ tester.checkAgg(
+ "every(x = 2)",
+ values,
+ "false",
+ 0d);
+ }
+
+ @Test public void testSomeAggFunc() {
+ tester.setFor(SqlStdOperatorTable.SOME, VM_EXPAND);
+ tester.checkFails(
+ "some(^*^)",
+ "Unknown identifier '\\*'",
+ false);
+ tester.checkType("some(1 = 1)", "BOOLEAN");
+ tester.checkType("some(1.2 = 1.2)", "BOOLEAN");
+ tester.checkType("some(1.5 = 1.4)", "BOOLEAN");
+ tester.checkFails(
+ "^some()^",
+ "Invalid number of arguments to function 'SOME'. Was expecting 1
arguments",
+ false);
+ tester.checkFails(
+ "^some(1, 2)^",
+ "Invalid number of arguments to function 'SOME'. Was expecting 1
arguments",
+ false);
+ final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"};
+ if (!enable) {
+ return;
+ }
+ tester.checkAgg(
+ "some(x = 2)",
+ values,
+ "true",
+ 0d);
+ }
+
+
@Test public void testAnyValueFunc() {
tester.setFor(SqlStdOperatorTable.ANY_VALUE, VM_EXPAND);
tester.checkFails(
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 993a5e2..698c748 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3205,6 +3205,13 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
sql(sql).ok();
}
+ @Test public void testSomeAndEveryAggregateFunctions() throws Exception {
+ final String sql = "SELECT some(empno = 130) as someempnoexists,\n"
+ + " every(empno > 0) as everyempnogtzero\n"
+ + " FROM emp AS e group by e.sal";
+ sql(sql).ok();
+ }
+
private Tester getExtendedTester() {
return tester.withCatalogReaderFactory(MockCatalogReaderExtended::new);
}
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 dc9a50d..0be6a60 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7831,6 +7831,16 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
sql("SELECT MAX(5) FROM emp").ok();
}
+ @Test public void testSomeEveryAndIntersectionFunctions() {
+ sql("select some(sal = 100), every(sal > 0), intersection(multiset[1,2])
from emp").ok();
+ sql("select some(sal = 100), ^empno^ from emp")
+ .fails("Expression 'EMPNO' is not being grouped");
+ sql("select every(sal > 0), ^empno^ from emp")
+ .fails("Expression 'EMPNO' is not being grouped");
+ sql("select intersection(multiset[1]), ^empno^ from emp")
+ .fails("Expression 'EMPNO' is not being grouped");
+ }
+
@Test public void testAnyValueFunction() {
sql("SELECT any_value(ename) from emp").ok();
}
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 2f00acf..7e4ad68 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -6052,6 +6052,19 @@ LogicalProject(C1=[$1])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSomeAndEveryAggregateFunctions">
+ <Resource name="sql">
+ <![CDATA[]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(SOMEEMPNOEXISTS=[$1], EVERYEMPNOGTZERO=[$2])
+ LogicalAggregate(group=[{0}], SOMEEMPNOEXISTS=[SOME($1)],
EVERYEMPNOGTZERO=[EVERY($2)])
+ LogicalProject(SAL=[$5], $f1=[=($0, 130)], $f2=[>($0, 0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testAnyValueAggregateFunctionNoGroupBy">
<Resource name="sql">
<![CDATA[SELECT any_value(empno) as anyempno FROM emp AS e]]>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 8b3fd9c..398ebea 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -159,6 +159,28 @@ select stddev_samp(deptno) as s from emp;
!ok
+# [CALCITE-3815] Add missing SQL standard aggregate
+# functions: EVERY, SOME, INTERSECTION
+select some(deptno = 100), every(deptno > 0), intersection(multiset[1, 2])
from emp;
++--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 |
++--------+--------+--------+
+| false | true | [1, 2] |
++--------+--------+--------+
+(1 row)
+
+!ok
+
+select some(deptno > 100), every(deptno > 0) from emp where deptno > 1000;
++--------+--------+
+| EXPR$0 | EXPR$1 |
++--------+--------+
+| | |
++--------+--------+
+(1 row)
+
+!ok
+
# stddev_pop
select stddev_pop(deptno) as s from emp;
+----+
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 6e6402d..b0dbace 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1766,12 +1766,15 @@ and `LISTAGG`).
| COUNT( [ ALL | DISTINCT ] value [, value ]*) | Returns the number of
input rows for which *value* is not null (wholly not null if *value* is
composite)
| COUNT(*) | Returns the number of input rows
| FUSION(multiset) | Returns the multiset union of
*multiset* across all input values
+| INTERSECTION(multiset) | Returns the multiset intersection of
*multiset* across all input values
| APPROX_COUNT_DISTINCT(value [, value ]*) | Returns the approximate
number of distinct values of *value*; the database is allowed to use an
approximation but is not required to
| AVG( [ ALL | DISTINCT ] numeric) | Returns the average
(arithmetic mean) of *numeric* across all input values
| SUM( [ ALL | DISTINCT ] numeric) | Returns the sum of *numeric*
across all input values
| MAX( [ ALL | DISTINCT ] value) | Returns the maximum value of
*value* across all input values
| MIN( [ ALL | DISTINCT ] value) | Returns the minimum value of
*value* across all input values
| ANY_VALUE( [ ALL | DISTINCT ] value) | Returns one of the values of
*value* across all input values; this is NOT specified in the SQL standard
+| SOME(condition) | Returns true if any
condition is true.
+| EVERY(condition) | Returns true if all
conditions are true.
| BIT_AND( [ ALL | DISTINCT ] value) | Returns the bitwise AND of
all non-null input values, or null if none
| BIT_OR( [ ALL | DISTINCT ] value) | Returns the bitwise OR of
all non-null input values, or null if none
| BIT_XOR( [ ALL | DISTINCT ] value) | Returns the bitwise XOR of
all non-null input values, or null if none
