You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jc...@apache.org on 2019/03/25 17:52:31 UTC
[calcite] 04/04: [CALCITE-2951] Support decorrelate subquery that
has aggregate with grouping sets (Haisheng Yuan)
This is an automated email from the ASF dual-hosted git repository.
jcamacho pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 406129b97a47262f7307de64bf24c26b2eba70eb
Author: Haisheng Yuan <h....@alibaba-inc.com>
AuthorDate: Tue Mar 19 18:30:36 2019 -0700
[CALCITE-2951] Support decorrelate subquery that has aggregate with grouping sets (Haisheng Yuan)
Close apache/calcite#1118
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 16 +++++++++-----
.../apache/calcite/test/SqlToRelConverterTest.java | 13 +++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 25 ++++++++++++++++++++++
3 files changed, 49 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 10cbfa1..da674d9 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -76,7 +76,6 @@ import org.apache.calcite.sql.fun.SqlSingleValueAggFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilderFactory;
-import org.apache.calcite.util.Bug;
import org.apache.calcite.util.Holder;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Litmus;
@@ -447,9 +446,6 @@ public class RelDecorrelator implements ReflectiveVisitor {
* @param rel Aggregate to rewrite
*/
public Frame decorrelateRel(LogicalAggregate rel) {
- if (rel.getGroupType() != Aggregate.Group.SIMPLE) {
- throw new AssertionError(Bug.CALCITE_461_FIXED);
- }
//
// Rewrite logic:
//
@@ -560,6 +556,16 @@ public class RelDecorrelator implements ReflectiveVisitor {
List<AggregateCall> newAggCalls = new ArrayList<>();
List<AggregateCall> oldAggCalls = rel.getAggCallList();
+ ImmutableList<ImmutableBitSet> newGroupSets = null;
+ if (rel.getGroupType() != Aggregate.Group.SIMPLE) {
+ final ImmutableBitSet addedGroupSet =
+ ImmutableBitSet.range(oldGroupKeyCount, newGroupKeyCount);
+ final Iterable<ImmutableBitSet> tmpGroupSets =
+ Iterables.transform(rel.getGroupSets(),
+ bitSet -> bitSet.union(addedGroupSet));
+ newGroupSets = ImmutableBitSet.ORDERING.immutableSortedCopy(tmpGroupSets);
+ }
+
int oldInputOutputFieldCount = rel.getGroupSet().cardinality();
int newInputOutputFieldCount = newGroupSet.cardinality();
@@ -592,7 +598,7 @@ public class RelDecorrelator implements ReflectiveVisitor {
}
relBuilder.push(
- LogicalAggregate.create(newProject, newGroupSet, null, newAggCalls));
+ LogicalAggregate.create(newProject, newGroupSet, newGroupSets, newAggCalls));
if (!omittedConstants.isEmpty()) {
final List<RexNode> postProjects = new ArrayList<>(relBuilder.fields());
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 9976321..d416eb8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2457,6 +2457,19 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).decorrelate(true).ok();
}
+ /**
+ * Test case for decorrelating sub-query that has aggregate with
+ * grouping sets.
+ */
+ @Test public void testCorrelationAggregateGroupSets() {
+ final String sql = "select sum(e1.empno)\n"
+ + "from emp e1, dept d1\n"
+ + "where e1.deptno = d1.deptno\n"
+ + "and e1.sal > (select avg(e2.sal) from emp e2\n"
+ + " where e2.deptno = d1.deptno group by cube(comm, mgr))";
+ sql(sql).decorrelate(true).ok();
+ }
+
@Test public void testCustomColumnResolving() {
final String sql = "select k0 from struct.t";
sql(sql).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 c24e21b..24289d3 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3972,6 +3972,31 @@ LogicalProject(EMPNO=[$0])
]]>
</Resource>
</TestCase>
+ <TestCase name="testCorrelationAggregateGroupSets">
+ <Resource name="sql">
+ <![CDATA[select sum(e1.empno)
+from emp e1, dept d1
+where e1.deptno = d1.deptno
+and e1.sal > (select avg(e2.sal) from emp e2
+where e2.deptno = d1.deptno group by cube(comm, mgr))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], DEPTNO1=[CAST($11):INTEGER], $f1=[CAST($12):INTEGER])
+ LogicalJoin(condition=[AND(=($9, $11), >($5, $12))], joinType=[inner])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])
+ LogicalProject(DEPTNO=[$2], EXPR$0=[$3])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 2}, {1, 2}, {2}]], EXPR$0=[AVG($3)])
+ LogicalProject(COMM=[$6], MGR=[$3], DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testFakeStar">
<Resource name="sql">
<![CDATA[SELECT * FROM (VALUES (0, 0)) AS T(A, "*")]]>