[CALCITE-2366] Add support for ANY_VALUE aggregate function (Gautam Parai) Close apache/calcite#735
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/6a2af65c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/6a2af65c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/6a2af65c Branch: refs/heads/site Commit: 6a2af65c945be671123ae35f3c2b9e6f4d9163d1 Parents: d68f486 Author: Gautam Parai <[email protected]> Authored: Wed Jun 6 16:24:12 2018 -0700 Committer: Volodymyr Vysotskyi <[email protected]> Committed: Fri Jul 13 15:10:51 2018 +0300 ---------------------------------------------------------------------- .../calcite/adapter/enumerable/RexImpTable.java | 2 + .../calcite/plan/SubstitutionVisitor.java | 3 +- .../java/org/apache/calcite/rel/core/Match.java | 3 ++ .../rel/rules/AbstractMaterializedViewRule.java | 3 +- .../rel/rules/AggregateUnionTransposeRule.java | 2 + .../java/org/apache/calcite/sql/SqlKind.java | 3 ++ .../calcite/sql/fun/SqlAnyValueAggFunction.java | 54 ++++++++++++++++++++ .../calcite/sql/fun/SqlStdOperatorTable.java | 6 +++ .../calcite/sql/test/SqlOperatorBaseTest.java | 42 +++++++++++++++ .../calcite/test/SqlToRelConverterTest.java | 13 +++++ .../apache/calcite/test/SqlValidatorTest.java | 4 ++ .../calcite/test/SqlToRelConverterTest.xml | 25 +++++++++ core/src/test/resources/sql/agg.iq | 44 ++++++++++++++++ site/_docs/reference.md | 1 + 14 files changed, 203 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java ---------------------------------------------------------------------- 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 f806a11..5ba5959 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 @@ -93,6 +93,7 @@ import static org.apache.calcite.sql.fun.OracleSqlOperatorTable.TRANSLATE3; 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.AND; +import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ANY_VALUE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ASIN; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ATAN; @@ -443,6 +444,7 @@ public class RexImpTable { constructorSupplier(MinMaxImplementor.class); aggMap.put(MIN, minMax); aggMap.put(MAX, minMax); + aggMap.put(ANY_VALUE, minMax); aggMap.put(SINGLE_VALUE, constructorSupplier(SingleValueImplementor.class)); aggMap.put(COLLECT, constructorSupplier(CollectImplementor.class)); aggMap.put(FUSION, constructorSupplier(FusionImplementor.class)); http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java index c8f92c7..35b3399 100644 --- a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java +++ b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java @@ -1355,7 +1355,8 @@ public class SubstitutionVisitor { if (aggregation == SqlStdOperatorTable.SUM || aggregation == SqlStdOperatorTable.MIN || aggregation == SqlStdOperatorTable.MAX - || aggregation == SqlStdOperatorTable.SUM0) { + || aggregation == SqlStdOperatorTable.SUM0 + || aggregation == SqlStdOperatorTable.ANY_VALUE) { return aggregation; } else if (aggregation == SqlStdOperatorTable.COUNT) { return SqlStdOperatorTable.SUM0; http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/rel/core/Match.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/Match.java b/core/src/main/java/org/apache/calcite/rel/core/Match.java index 7d47bec..d2c170e 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Match.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Match.java @@ -251,6 +251,9 @@ public abstract class Match extends SingleRel { case COUNT: aggFunction = SqlStdOperatorTable.COUNT; break; + case ANY_VALUE: + aggFunction = SqlStdOperatorTable.ANY_VALUE; + break; default: for (RexNode rex : call.getOperands()) { rex.accept(this); http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java index 2753c44..f5ebafa 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java @@ -1714,7 +1714,8 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { if (aggregation == SqlStdOperatorTable.SUM || aggregation == SqlStdOperatorTable.MIN || aggregation == SqlStdOperatorTable.MAX - || aggregation == SqlStdOperatorTable.SUM0) { + || aggregation == SqlStdOperatorTable.SUM0 + || aggregation == SqlStdOperatorTable.ANY_VALUE) { return aggregation; } else if (aggregation == SqlStdOperatorTable.COUNT) { return SqlStdOperatorTable.SUM0; http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java index 2372205..1025753 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java @@ -30,6 +30,7 @@ import org.apache.calcite.rel.metadata.RelMdUtil; import org.apache.calcite.rel.metadata.RelMetadataQuery; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.fun.SqlAnyValueAggFunction; import org.apache.calcite.sql.fun.SqlCountAggFunction; import org.apache.calcite.sql.fun.SqlMinMaxAggFunction; import org.apache.calcite.sql.fun.SqlStdOperatorTable; @@ -63,6 +64,7 @@ public class AggregateUnionTransposeRule extends RelOptRule { SUPPORTED_AGGREGATES.put(SqlCountAggFunction.class, true); SUPPORTED_AGGREGATES.put(SqlSumAggFunction.class, true); SUPPORTED_AGGREGATES.put(SqlSumEmptyIsZeroAggFunction.class, true); + SUPPORTED_AGGREGATES.put(SqlAnyValueAggFunction.class, true); } /** Creates an AggregateUnionTransposeRule. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- 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 08cfbec..54b390b 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -849,6 +849,9 @@ public enum SqlKind { /** The {@code LAST_VALUE} aggregate function. */ LAST_VALUE, + /** The {@code ANY_VALUE} aggregate function. */ + ANY_VALUE, + /** The {@code COVAR_POP} aggregate function. */ COVAR_POP, http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java new file mode 100644 index 0000000..49208b9 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java @@ -0,0 +1,54 @@ +/* + * 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. + */ +package org.apache.calcite.sql.fun; + +import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlFunctionCategory; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.ReturnTypes; + +import com.google.common.base.Preconditions; + +/** + * Definition of the <code>ANY_VALUE</code> aggregate functions, + * returning any one of the values which go into it. + */ +public class SqlAnyValueAggFunction extends SqlAggFunction { + + //~ Instance fields -------------------------------------------------------- + + //~ Constructors ----------------------------------------------------------- + + /** Creates a SqlAnyValueAggFunction. */ + public SqlAnyValueAggFunction(SqlKind kind) { + super(kind.name(), + null, + kind, + ReturnTypes.ARG0_NULLABLE_IF_EMPTY, + null, + OperandTypes.ANY, + SqlFunctionCategory.SYSTEM, + false, + false); + Preconditions.checkArgument(kind == SqlKind.ANY_VALUE); + } + + //~ Methods ---------------------------------------------------------------- +} + +// End SqlAnyValueAggFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- 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 6ac1eb4..0064cba 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 @@ -861,6 +861,12 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlFirstLastValueAggFunction(SqlKind.LAST_VALUE); /** + * <code>ANY_VALUE</code> aggregate function. + */ + public static final SqlAggFunction ANY_VALUE = + new SqlAnyValueAggFunction(SqlKind.ANY_VALUE); + + /** * <code>FIRST_VALUE</code> aggregate function. */ public static final SqlAggFunction FIRST_VALUE = http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- 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 f2681f3..c518768 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 @@ -7265,6 +7265,48 @@ public abstract class SqlOperatorBaseTest { 0d); } + @Test public void testAnyValueFunc() { + tester.setFor(SqlStdOperatorTable.ANY_VALUE, VM_EXPAND); + tester.checkFails( + "any_value(^*^)", + "Unknown identifier '\\*'", + false); + tester.checkType("any_value(1)", "INTEGER"); + tester.checkType("any_value(1.2)", "DECIMAL(2, 1)"); + tester.checkType("any_value(DISTINCT 1.5)", "DECIMAL(2, 1)"); + tester.checkFails( + "^any_value()^", + "Invalid number of arguments to function 'ANY_VALUE'. Was expecting 1 arguments", + false); + tester.checkFails( + "^any_value(1, 2)^", + "Invalid number of arguments to function 'ANY_VALUE'. Was expecting 1 arguments", + false); + final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; + if (!enable) { + return; + } + tester.checkAgg( + "any_value(x)", + values, + "0", + 0d); + tester.checkAgg( + "any_value(CASE x WHEN 0 THEN NULL ELSE -1 END)", + values, + "-1", + 0d); + tester.checkAgg( + "any_value(DISTINCT CASE x WHEN 0 THEN NULL ELSE -1 END)", + values, + "-1", + 0d); + tester.checkAgg( + "any_value(DISTINCT x)", + values, + "0", + 0d); + } /** * Tests that CAST fails when given a value just outside the valid range for * that type. For example, http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- 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 0f9dad2..2fd4dbe 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -2614,6 +2614,19 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).with(getTesterWithDynamicTable()).ok(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2366";>[CALCITE-2366] + * Add support for ANY_VALUE aggregate function</a>. */ + @Test public void testAnyValueAggregateFunctionNoGroupBy() throws Exception { + final String sql = "SELECT any_value(empno) as anyempno FROM emp AS e"; + sql(sql).ok(); + } + + @Test public void testAnyValueAggregateFunctionGroupBy() throws Exception { + final String sql = "SELECT any_value(empno) as anyempno FROM emp AS e group by e.sal"; + sql(sql).ok(); + } + private Tester getExtendedTester() { return tester.withCatalogReaderFactory(typeFactory -> new MockCatalogReader(typeFactory, true).init().init2()); http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- 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 b659009..6f422bd 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -7697,6 +7697,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { check("SELECT MAX(5) FROM emp"); } + @Test public void testAnyValueFunction() { + check("SELECT any_value(ename) from emp"); + } + @Test public void testFunctionalDistinct() { check("select count(distinct sal) from emp"); checkFails("select COALESCE(^distinct^ sal) from emp", http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- 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 af280e1..51d6855 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -5292,4 +5292,29 @@ LogicalProject(**=[$1], $unnest=[$2]) ]]> </Resource> </TestCase> + <TestCase name="testAnyValueAggregateFunctionNoGroupBy"> + <Resource name="sql"> + <![CDATA[SELECT any_value(empno) as anyempno FROM emp AS e]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{}], ANYEMPNO=[ANY_VALUE($0)]) + LogicalProject(EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAnyValueAggregateFunctionGroupBy"> + <Resource name="sql"> + <![CDATA[SELECT any_value(empno) as anyempno FROM emp AS e group by e.sal]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(ANYEMPNO=[$1]) + LogicalAggregate(group=[{0}], ANYEMPNO=[ANY_VALUE($1)]) + LogicalProject(SAL=[$5], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> </Root> http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/core/src/test/resources/sql/agg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq index a3d1d70..6c26a89 100755 --- a/core/src/test/resources/sql/agg.iq +++ b/core/src/test/resources/sql/agg.iq @@ -2240,4 +2240,48 @@ EnumerableAggregate(group=[{1}], EXPR$1=[COUNT($2, $0) FILTER $5], EXPR$2=[MIN($ EnumerableTableScan(table=[[scott, EMP]]) !plan +# [CALCITE-2366] Add support for ANY_VALUE function +# Without GROUP BY clause +SELECT any_value(empno) as anyempno from "scott".emp; ++----------+ +| ANYEMPNO | ++----------+ +| 7934 | ++----------+ +(1 row) + +!ok + +EnumerableAggregate(group=[{}], ANYEMPNO=[ANY_VALUE($0)]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# [CALCITE-2366] Add support for ANY_VALUE function +# With GROUP BY clause +SELECT any_value(empno) as anyempno from "scott".emp group by sal; ++----------+ +| ANYEMPNO | ++----------+ +| 7369 | +| 7499 | +| 7566 | +| 7654 | +| 7698 | +| 7782 | +| 7839 | +| 7844 | +| 7876 | +| 7900 | +| 7902 | +| 7934 | ++----------+ +(12 rows) + +!ok + +EnumerableCalc(expr#0..1=[{inputs}], ANYEMPNO=[$t1]) + EnumerableAggregate(group=[{5}], ANYEMPNO=[ANY_VALUE($0)]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + # End agg.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/6a2af65c/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index b8bde09..acf8f29 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1503,6 +1503,7 @@ passed to the aggregate function. | 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 | STDDEV_POP( [ ALL | DISTINCT ] numeric) | Returns the population standard deviation of *numeric* across all input values | STDDEV_SAMP( [ ALL | DISTINCT ] numeric) | Returns the sample standard deviation of *numeric* across all input values | VAR_POP( [ ALL | DISTINCT ] value) | Returns the population variance (square of the population standard deviation) of *numeric* across all input values
