You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/06/27 23:20:13 UTC
[1/2] incubator-calcite git commit: [CALCITE-770] Ignore window
aggregates and ranking functions when finding aggregate functions
Repository: incubator-calcite
Updated Branches:
refs/heads/master d559bacf2 -> 8774a671f
[CALCITE-770] Ignore window aggregates and ranking functions when finding aggregate functions
Close apache/incubator-calcite#99
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/e1378123
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/e1378123
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/e1378123
Branch: refs/heads/master
Commit: e1378123eb236721414d9ac3e77a08fd10bd4aa2
Parents: d559bac
Author: Aman Sinha <as...@maprtech.com>
Authored: Fri Jun 26 08:24:47 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jun 26 14:22:56 2015 -0700
----------------------------------------------------------------------
.../calcite/sql2rel/SqlToRelConverter.java | 8 +++
.../calcite/test/SqlToRelConverterTest.java | 55 +++++++++++++++
.../calcite/test/SqlToRelConverterTest.xml | 74 ++++++++++++++++++++
core/src/test/resources/sql/winagg.oq | 26 +++++++
4 files changed, 163 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e1378123/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 2b19020..5ca783f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -4366,6 +4366,10 @@ public class SqlToRelConverter {
// for now do not detect aggregates in subqueries.
return null;
}
+ // ignore window aggregates and ranking functions (associated with OVER operator)
+ if (call.getOperator().getKind() == SqlKind.OVER) {
+ return null;
+ }
if (call.getOperator().isAggregator()) {
translateAgg(call, null, call);
return null;
@@ -4805,6 +4809,10 @@ public class SqlToRelConverter {
final SqlNodeList list = new SqlNodeList(SqlParserPos.ZERO);
@Override public Void visit(SqlCall call) {
+ // ignore window aggregates and ranking functions (associated with OVER operator)
+ if (call.getOperator().getKind() == SqlKind.OVER) {
+ return null;
+ }
if (call.getOperator().isAggregator()) {
list.add(call);
return null;
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e1378123/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 3d2b48f..083367e 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1277,6 +1277,61 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
+ * window aggregate and ranking functions with grouped aggregates</a>.
+ */
+ @Test public void testWindowAggWithGroupBy() {
+ sql("select min(deptno), rank() over (order by empno),\n"
+ + "max(empno) over (partition by deptno)\n"
+ + "from emp group by deptno, empno\n")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
+ * variant involving joins</a>.
+ */
+ @Test public void testWindowAggWithGroupByAndJoin() {
+ sql("select min(d.deptno), rank() over (order by e.empno),\n"
+ + " max(e.empno) over (partition by e.deptno)\n"
+ + "from emp e, dept d\n"
+ + "where e.deptno = d.deptno\n"
+ + "group by d.deptno, e.empno, e.deptno\n")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
+ * variant involving HAVING clause</a>.
+ */
+ @Test public void testWindowAggWithGroupByAndHaving() {
+ sql("select min(deptno), rank() over (order by empno),\n"
+ + "max(empno) over (partition by deptno)\n"
+ + "from emp group by deptno, empno\n"
+ + "having empno < 10 and min(deptno) < 20\n")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
+ * variant involving join with subquery that contains window function and
+ * GROUP BY</a>.
+ */
+ @Test public void testWindowAggInSubqueryJoin() {
+ sql("select T.x, T.y, T.z, emp.empno from (select min(deptno) as x,\n"
+ + " rank() over (order by empno) as y,\n"
+ + " max(empno) over (partition by deptno) as z\n"
+ + " from emp group by deptno, empno) as T\n"
+ + " inner join emp on T.x = emp.deptno\n"
+ + " and T.y = emp.empno\n")
+ .convertsTo("${plan}");
+ }
+
+ /**
* Visitor that checks that every {@link RelNode} in a tree is valid.
*
* @see RelNode#isValid(boolean)
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e1378123/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 58777ab..075ccd2 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2631,4 +2631,78 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[INSERT], updateColu
]]>
</Resource>
</TestCase>
+ <TestCase name="testWindowAggWithGroupBy">
+ <Resource name="sql">
+ <![CDATA[select min(deptno), rank() over (order by empno)
+ max(empno) over (partition by deptno)
+ from emp group by deptno, empno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$2], EXPR$1=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$2=[MAX($1) OVER (PARTITION BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+ LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($0)])
+ LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testWindowAggWithGroupByAndJoin">
+ <Resource name="sql">
+ <![CDATA[select min(d.deptno), rank() over (order by e.empno),
+ max(e.empno) over (partition by e.deptno)
+ from emp e, dept d
+ where e.deptno = d.deptno
+ group by d.deptno, e.empno, e.deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$3], EXPR$1=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$2=[MAX($1) OVER (PARTITION BY $2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+ LogicalAggregate(group=[{0, 1, 2}], EXPR$0=[MIN($0)])
+ LogicalProject(DEPTNO0=[$9], EMPNO=[$0], DEPTNO=[$7])
+ LogicalFilter(condition=[=($7, $9)])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testWindowAggWithGroupByAndHaving">
+ <Resource name="sql">
+ <![CDATA[select min(deptno), rank() over (order by empno),
+ max(empno) over (partition by deptno)
+ from emp group by deptno, empno
+ having empno < 10 and min(deptno) < 20]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$2], EXPR$1=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$2=[MAX($1) OVER (PARTITION BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+ LogicalFilter(condition=[AND(<($1, 10), <($2, 20))])
+ LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($0)])
+ LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testWindowAggInSubqueryJoin">
+ <Resource name="sql">
+ <![CDATA[select T.x, T.y, T.z,
+ emp.empno from (select min(deptno) as x,
+ rank() over (order by empno) as y,
+ max(empno) over (partition by deptno) as z
+ from emp group by deptno, empno) as T
+ inner join emp on T.x = emp.deptno
+ and T.y = emp.empno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(X=[$0], Y=[$1], Z=[$2], EMPNO=[$3])
+ LogicalJoin(condition=[AND(=($0, $10), =($1, $3))], joinType=[inner])
+ LogicalProject(X=[$2], Y=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], Z=[MAX($1) OVER (PARTITION BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+ LogicalAggregate(group=[{0, 1}], X=[MIN($0)])
+ LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e1378123/core/src/test/resources/sql/winagg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/winagg.oq b/core/src/test/resources/sql/winagg.oq
index 16f2b08..d3c4b69 100644
--- a/core/src/test/resources/sql/winagg.oq
+++ b/core/src/test/resources/sql/winagg.oq
@@ -223,4 +223,30 @@ window w1 as ();
!ok
+# Window Aggregate and group-by.
+# (ORDER BY is necessary to work around [QUIDEM-7].)
+!set outputformat mysql
+select min(deptno) as x, rank() over (order by ename) as y,
+ max(ename) over (partition by deptno) as z
+from emp
+group by deptno, ename
+order by ename;
+
++----+---+-------+
+| X | Y | Z |
++----+---+-------+
+| | 9 | Wilma |
+| 50 | 1 | Eve |
+| 50 | 5 | Eve |
+| 20 | 4 | Eric |
+| 10 | 3 | Jane |
+| 10 | 7 | Jane |
+| 60 | 6 | Grace |
+| 30 | 2 | Susan |
+| 30 | 8 | Susan |
++----+---+-------+
+(9 rows)
+
+!ok
+
# End winagg.oq
[2/2] incubator-calcite git commit: [CALCITE-763] Missing translation
from Sort to MutableSort (Maryann Xue)
Posted by jh...@apache.org.
[CALCITE-763] Missing translation from Sort to MutableSort (Maryann Xue)
Close apache/incubator-calcite#100
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/8774a671
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/8774a671
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/8774a671
Branch: refs/heads/master
Commit: 8774a671f035d7bbc8fe8fa420ba237941431c08
Parents: e137812
Author: maryannxue <we...@intel.com>
Authored: Fri Jun 26 15:00:48 2015 -0400
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jun 26 15:28:25 2015 -0700
----------------------------------------------------------------------
.../org/apache/calcite/plan/SubstitutionVisitor.java | 6 ++++++
.../org/apache/calcite/test/MaterializationTest.java | 14 ++++++++++++++
2 files changed, 20 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/8774a671/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 7322d4b..b1c89e6 100644
--- a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
+++ b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
@@ -28,6 +28,7 @@ import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.logical.LogicalAggregate;
@@ -241,6 +242,11 @@ public class SubstitutionVisitor {
return MutableJoin.of(join.getCluster(), left, right,
join.getCondition(), join.getJoinType(), join.getVariablesStopped());
}
+ if (rel instanceof Sort) {
+ final Sort sort = (Sort) rel;
+ final MutableRel input = toMutable(sort.getInput());
+ return MutableSort.of(input, sort.getCollation(), sort.offset, sort.fetch);
+ }
throw new RuntimeException("cannot translate " + rel + " to MutableRel");
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/8774a671/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
index a8402d6..f0fb54b 100644
--- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
+++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java
@@ -272,6 +272,20 @@ public class MaterializationTest {
}
@Ignore
+ @Test public void testOrderByQueryOnProjectView() {
+ checkMaterialize(
+ "select \"deptno\", \"empid\" from \"emps\"",
+ "select \"empid\" from \"emps\" order by \"deptno\"");
+ }
+
+ @Ignore
+ @Test public void testOrderByQueryOnOrderByView() {
+ checkMaterialize(
+ "select \"deptno\", \"empid\" from \"emps\" order by \"deptno\"",
+ "select \"empid\" from \"emps\" order by \"deptno\"");
+ }
+
+ @Ignore
@Test public void testDifferentColumnNames() {}
@Ignore