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/08/01 00:43:00 UTC
[23/50] [abbrv] incubator-calcite git commit: [CALCITE-694] Scan
HAVING clause for sub-queries and IN-lists (Hsuan-Yi Chu)
[CALCITE-694] Scan HAVING clause for sub-queries and IN-lists (Hsuan-Yi Chu)
The aggregate functions in HAVING clause are also needed to be added to aggList to replace subqueries.
Close apache/incubator-calcite#85
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/61ba314f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/61ba314f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/61ba314f
Branch: refs/heads/branch-release
Commit: 61ba314f1328dd5392d3d440763139ea81b86645
Parents: 6cdb2e7
Author: Hsuan-Yi Chu <hs...@usc.edu>
Authored: Tue May 5 14:48:26 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed May 6 16:18:59 2015 -0700
----------------------------------------------------------------------
.../calcite/sql2rel/SqlToRelConverter.java | 6 +++
.../calcite/test/SqlToRelConverterTest.java | 35 ++++++++++++++-
.../calcite/test/SqlToRelConverterTest.xml | 46 ++++++++++++++++++++
3 files changed, 86 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/61ba314f/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 88213e6..07ec53e 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2553,6 +2553,12 @@ public class SqlToRelConverter {
}
}
+ // The aggregate functions in having clause are also needed
+ // to be added to aggList to replace subqueries
+ if (having != null && validator.isAggregate(having)) {
+ aggList.add(having);
+ }
+
// first replace the subqueries inside the aggregates
// because they will provide input rows to the aggregates.
replaceSubqueries(bb, aggList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/61ba314f/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 5adba1d..711895d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1182,7 +1182,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
/**
* Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-710">[CALCITE-710]
- * When look up subqueries, perform the same logic as the way when ones were registered</a>.
+ * When look up subqueries, perform the same logic as the way when ones were
+ * registered</a>.
*/
@Test public void testIdenticalExpressionInSubquery() {
sql("select deptno\n"
@@ -1192,6 +1193,38 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-694">[CALCITE-694]
+ * Scan HAVING clause for sub-queries and IN-lists</a> relating to IN.
+ */
+ @Test public void testHavingAggrFunctionIn() {
+ sql("select deptno \n"
+ + "from emp \n"
+ + "group by deptno \n"
+ + "having sum(case when deptno in (1, 2) then 0 else 1 end) + \n"
+ + "sum(case when deptno in (3, 4) then 0 else 1 end) > 10")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-694">[CALCITE-694]
+ * Scan HAVING clause for sub-queries and IN-lists</a>, with a sub-query in
+ * the HAVING clause.
+ */
+ @Test public void testHavingInSubqueryWithAggrFunction() {
+ sql("select sal \n"
+ + "from emp \n"
+ + "group by sal \n"
+ + "having sal in \n"
+ + "(select deptno \n"
+ + "from dept \n"
+ + "group by deptno \n"
+ + "having sum(deptno) > 0)")
+ .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/61ba314f/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 15accd9..9897fa9 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2489,4 +2489,50 @@ LogicalProject(DEPTNO=[$7])
]]>
</Resource>
</TestCase>
+ <TestCase name="testHavingAggrFunctionIn">
+ <Resource name="sql">
+ <![CDATA[select deptno
+from emp
+group by deptno
+having sum(case when deptno in (1, 2) then 0 else 1 end) +
+sum(case when deptno in (3, 4) then 0 else 1 end) > 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>(+($1, $2), 10)])
+ LogicalAggregate(group=[{0}], agg#0=[SUM($1)], agg#1=[SUM($2)])
+ LogicalProject(DEPTNO=[$7], $f1=[CASE(OR(=($7, 1), =($7, 2)), 0, 1)], $f2=[CASE(OR(=($7, 3), =($7, 4)), 0, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHavingInSubqueryWithAggrFunction">
+ <Resource name="sql">
+ <![CDATA[select sal
+from emp
+group by sal
+having sal in
+(select deptno
+from dept
+group by deptno
+having sum(deptno) > 0)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(SAL=[$0])
+ LogicalJoin(condition=[=($1, $2)], joinType=[inner])
+ LogicalProject($f0=[$0], $f1=[$0])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($1, 0)])
+ LogicalAggregate(group=[{0}], agg#0=[SUM($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
</Root>