You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mm...@apache.org on 2018/07/20 17:41:52 UTC
[39/53] [abbrv] calcite git commit: [CALCITE-2366] Add support for
ANY_VALUE aggregate function (Gautam Parai)
[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 <gp...@maprtech.com>
Authored: Wed Jun 6 16:24:12 2018 -0700
Committer: Volodymyr Vysotskyi <vv...@gmail.com>
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