calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [24/50] [abbrv] incubator-calcite git commit: [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING clause gives AssertionError
Date Fri, 31 Jul 2015 22:43:01 GMT
[CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING clause gives AssertionError

Based on work by Sean Hsuan-Yi Chu.


Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f98d567f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/f98d567f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/f98d567f

Branch: refs/heads/branch-release
Commit: f98d567fa6843762a830a272e06f2d74908ce440
Parents: 61ba314
Author: Julian Hyde <jhyde@apache.org>
Authored: Fri May 8 12:36:25 2015 -0700
Committer: Julian Hyde <jhyde@apache.org>
Committed: Fri May 8 12:36:25 2015 -0700

----------------------------------------------------------------------
 .../calcite/sql/validate/SqlValidator.java      |  1 +
 .../calcite/sql2rel/SqlToRelConverter.java      | 41 ++++++++++++------
 .../calcite/test/SqlToRelConverterTest.java     | 28 ++++++++++++
 .../calcite/test/SqlToRelConverterTest.xml      | 43 +++++++++++++++++++
 core/src/test/resources/sql/subquery.oq         | 45 ++++++++++++++++++++
 5 files changed, 145 insertions(+), 13 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f98d567f/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
index bd6d0b6..7e44b15 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
@@ -346,6 +346,7 @@ public interface SqlValidator {
    * @param selectNode Expression in SELECT clause
    * @return whether expression is an aggregate function
    */
+  @Deprecated // to be removed before 2.0
   boolean isAggregate(SqlNode selectNode);
 
   /**

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f98d567f/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 07ec53e..5dad7f4 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2545,23 +2545,16 @@ public class SqlToRelConverter {
       SqlNodeList groupList,
       SqlNode having,
       List<SqlNode> orderExprList) {
-    SqlNodeList aggList = new SqlNodeList(SqlParserPos.ZERO);
-
-    for (SqlNode selectNode : selectList) {
-      if (validator.isAggregate(selectNode)) {
-        aggList.add(selectNode);
-      }
-    }
-
-    // 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);
+    // Find aggregate functions in SELECT and HAVING clause
+    final AggregateFinder aggregateFinder = new AggregateFinder();
+    selectList.accept(aggregateFinder);
+    if (having != null) {
+      having.accept(aggregateFinder);
     }
 
     // first replace the subqueries inside the aggregates
     // because they will provide input rows to the aggregates.
-    replaceSubqueries(bb, aggList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
+    replaceSubqueries(bb, aggregateFinder.list, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
 
     // If group-by clause is missing, pretend that it has zero elements.
     if (groupList == null) {
@@ -4931,6 +4924,28 @@ public class SqlToRelConverter {
       this.logic = logic;
     }
   }
+
+  /**
+   * Visitor that collects all aggregate functions in a {@link SqlNode} tree.
+   */
+  private static class AggregateFinder extends SqlBasicVisitor<Void> {
+    final SqlNodeList list = new SqlNodeList(SqlParserPos.ZERO);
+
+    @Override public Void visit(SqlCall call) {
+      if (call.getOperator().isAggregator()) {
+        list.add(call);
+        return null;
+      }
+
+      // Don't traverse into sub-queries, even if they contain aggregate
+      // functions.
+      if (call instanceof SqlSelect) {
+        return null;
+      }
+
+      return call.getOperator().acceptCall(this, call);
+    }
+  }
 }
 
 // End SqlToRelConverter.java

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f98d567f/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 711895d..1853244 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1225,6 +1225,34 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
   }
 
   /**
+   * Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-716">[CALCITE-716]
+   * Scalar sub-query and aggregate function in SELECT or HAVING clause gives
+   * AssertionError</a>; variant involving HAVING clause.
+   */
+  @Test public void testAggregateAndScalarSubQueryInHaving() {
+    sql("select deptno\n"
+            + "from emp\n"
+            + "group by deptno\n"
+            + "having max(emp.empno) > (SELECT min(emp.empno) FROM emp)\n")
+        .convertsTo("${plan}");
+  }
+
+  /**
+   * Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-716">[CALCITE-716]
+   * Scalar sub-query and aggregate function in SELECT or HAVING clause gives
+   * AssertionError</a>; variant involving SELECT clause.
+   */
+  @Test public void testAggregateAndScalarSubQueryInSelect() {
+    sql("select deptno,\n"
+            + "  max(emp.empno) > (SELECT min(emp.empno) FROM emp) as b\n"
+            + "from emp\n"
+            + "group by deptno\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/f98d567f/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 9897fa9..ef17e98 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2535,4 +2535,47 @@ LogicalProject(SAL=[$0])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testAggregateAndScalarSubQueryInHaving">
+        <Resource name="sql">
+            <![CDATA[select deptno
+from emp
+group by deptno
+having max(emp.empno) > (SELECT min(emp.empno) FROM emp)
+]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0])
+  LogicalFilter(condition=[>($1, $2)])
+    LogicalJoin(condition=[true], joinType=[left])
+      LogicalAggregate(group=[{0}], agg#0=[MAX($1)])
+        LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+        LogicalProject(EMPNO=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testAggregateAndScalarSubQueryInSelect">
+        <Resource name="sql">
+            <![CDATA[select deptno,
+  max(emp.empno) > (SELECT min(emp.empno) FROM emp) as b
+from emp
+group by deptno
+]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], B=[>($1, $2)])
+  LogicalJoin(condition=[true], joinType=[left])
+    LogicalAggregate(group=[{0}], agg#0=[MAX($1)])
+      LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+      LogicalProject(EMPNO=[$0])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
 </Root>

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f98d567f/core/src/test/resources/sql/subquery.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/subquery.oq b/core/src/test/resources/sql/subquery.oq
index db345a5..72349a6 100644
--- a/core/src/test/resources/sql/subquery.oq
+++ b/core/src/test/resources/sql/subquery.oq
@@ -230,4 +230,49 @@ where (gender, deptno) in (select gender, 10 from emp where gender =
'F');
 
 !ok
 
+!use scott
+
+# [CALCITE-694] Scan HAVING clause for sub-queries and IN-lists
+SELECT count(*) AS c
+FROM "scott".emp
+GROUP BY emp.deptno
+HAVING sum(case when emp.empno in (7369, 7839, 7902) then emp.sal else 0 end)
+     BETWEEN 5000.0 AND 10000.0;
+ C
+---
+ 3
+(1 row)
+
+!ok
+
+# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING
+# clause gives AssertionError
+SELECT emp.deptno
+FROM "scott".emp
+GROUP BY emp.deptno
+HAVING max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp);
+ DEPTNO
+--------
+     10
+     20
+     30
+(3 rows)
+
+!ok
+
+# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING
+# clause gives AssertionError
+SELECT emp.deptno,
+  max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp) as bbbb
+FROM "scott".emp
+GROUP BY emp.deptno;
+ DEPTNO | BBBB
+--------+------
+     10 | true
+     20 | true
+     30 | true
+(3 rows)
+
+!ok
+
 # End subquery.oq


Mime
View raw message