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 2016/01/26 21:35:41 UTC
[2/4] calcite git commit: [CALCITE-551] Sub-query inside aggregate
function
[CALCITE-551] Sub-query inside aggregate function
The bug seems to have been fixed by recent changes; this commit adds test cases.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/82b58604
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/82b58604
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/82b58604
Branch: refs/heads/master
Commit: 82b586040ffbb4a7e46544b39b2dcc165d642430
Parents: d80e26c
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Jan 13 09:51:31 2016 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Tue Jan 26 11:53:33 2016 -0800
----------------------------------------------------------------------
.../calcite/test/SqlToRelConverterTest.java | 102 +++++++++++--------
.../calcite/test/SqlToRelConverterTest.xml | 89 ++++++++++------
core/src/test/resources/sql/agg.iq | 48 +++++++++
3 files changed, 164 insertions(+), 75 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/82b58604/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 f3cdf68..a527722 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -685,7 +685,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testWithInsideScalarSubquery() {
+ @Test public void testWithInsideScalarSubQuery() {
final String sql = "select (\n"
+ " with dept2 as (select * from dept where deptno > 10)"
+ " select count(*) from dept2) as c\n"
@@ -694,7 +694,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testWithInsideScalarSubqueryRex() {
+ @Test public void testWithInsideScalarSubQueryRex() {
final String sql = "select (\n"
+ " with dept2 as (select * from dept where deptno > 10)"
+ " select count(*) from dept2) as c\n"
@@ -783,7 +783,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
"${plan}");
}
- @Test public void testUnnestSubquery() {
+ @Test public void testUnnestSubQuery() {
check("select*from unnest(multiset(select*from dept))", "${plan}");
}
@@ -797,7 +797,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testMultisetSubquery() {
+ @Test public void testMultisetSubQuery() {
check(
"select multiset(select deptno from dept) from (values(true))",
"${plan}");
@@ -911,21 +911,21 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
@Test public void testInValueListLong() {
- // Go over the default threshold of 20 to force a subquery.
+ // Go over the default threshold of 20 to force a subQuery.
check("select empno from emp where deptno in"
+ " (10, 20, 30, 40, 50, 60, 70, 80, 90, 100"
+ ", 110, 120, 130, 140, 150, 160, 170, 180, 190"
+ ", 200, 210, 220, 230)", "${plan}");
}
- @Test public void testInUncorrelatedSubquery() {
+ @Test public void testInUncorrelatedSubQuery() {
final String sql = "select empno from emp where deptno in"
+ " (select deptno from dept)";
sql(sql)
.convertsTo("${plan}");
}
- @Test public void testInUncorrelatedSubqueryRex() {
+ @Test public void testInUncorrelatedSubQueryRex() {
final String sql = "select empno from emp where deptno in"
+ " (select deptno from dept)";
sql(sql)
@@ -933,7 +933,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testCompositeInUncorrelatedSubqueryRex() {
+ @Test public void testCompositeInUncorrelatedSubQueryRex() {
final String sql = "select empno from emp where (empno, deptno) in"
+ " (select deptno - 10, deptno from dept)";
sql(sql)
@@ -941,14 +941,14 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testNotInUncorrelatedSubquery() {
+ @Test public void testNotInUncorrelatedSubQuery() {
final String sql = "select empno from emp where deptno not in"
+ " (select deptno from dept)";
sql(sql)
.convertsTo("${plan}");
}
- @Test public void testNotInUncorrelatedSubqueryRex() {
+ @Test public void testNotInUncorrelatedSubQueryRex() {
final String sql = "select empno from emp where deptno not in"
+ " (select deptno from dept)";
sql(sql)
@@ -966,7 +966,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testInUncorrelatedSubqueryInSelect() {
+ @Test public void testInUncorrelatedSubQueryInSelect() {
// In the SELECT clause, the value of IN remains in 3-valued logic
// -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
// WHERE clause -- so the translation is more complicated.
@@ -977,7 +977,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testInUncorrelatedSubqueryInSelectRex() {
+ @Test public void testInUncorrelatedSubQueryInSelectRex() {
// In the SELECT clause, the value of IN remains in 3-valued logic
// -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
// WHERE clause -- so the translation is more complicated.
@@ -989,7 +989,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testInUncorrelatedSubqueryInHavingRex() {
+ @Test public void testInUncorrelatedSubQueryInHavingRex() {
final String sql = "select sum(sal) as s\n"
+ "from emp\n"
+ "group by deptno\n"
@@ -999,7 +999,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).expand(false).convertsTo("${plan}");
}
- @Test public void testUncorrelatedScalarSubqueryInOrderRex() {
+ @Test public void testUncorrelatedScalarSubQueryInOrderRex() {
final String sql = "select ename\n"
+ "from emp\n"
+ "order by (select case when true then deptno else null end from emp) desc,\n"
@@ -1007,7 +1007,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).expand(false).convertsTo("${plan}");
}
- @Test public void testUncorrelatedScalarSubqueryInGroupOrderRex() {
+ @Test public void testUncorrelatedScalarSubQueryInGroupOrderRex() {
final String sql = "select sum(sal) as s\n"
+ "from emp\n"
+ "group by deptno\n"
@@ -1016,16 +1016,16 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).expand(false).convertsTo("${plan}");
}
- @Test public void testUncorrelatedScalarSubqueryInAggregateRex() {
+ @Test public void testUncorrelatedScalarSubQueryInAggregateRex() {
final String sql = "select sum((select min(deptno) from emp)) as s\n"
+ "from emp\n"
+ "group by deptno\n";
sql(sql).expand(false).convertsTo("${plan}");
}
- /** Plan should be as {@link #testInUncorrelatedSubqueryInSelect}, but with
+ /** Plan should be as {@link #testInUncorrelatedSubQueryInSelect}, but with
* an extra NOT. Both queries require 3-valued logic. */
- @Test public void testNotInUncorrelatedSubqueryInSelect() {
+ @Test public void testNotInUncorrelatedSubQueryInSelect() {
final String sql = "select empno, deptno not in (\n"
+ " select case when true then deptno else null end from dept)\n"
+ "from emp";
@@ -1033,7 +1033,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testNotInUncorrelatedSubqueryInSelectRex() {
+ @Test public void testNotInUncorrelatedSubQueryInSelectRex() {
final String sql = "select empno, deptno not in (\n"
+ " select case when true then deptno else null end from dept)\n"
+ "from emp";
@@ -1044,7 +1044,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
/** Since 'deptno NOT IN (SELECT deptno FROM dept)' can not be null, we
* generate a simpler plan. */
- @Test public void testNotInUncorrelatedSubqueryInSelectNotNull() {
+ @Test public void testNotInUncorrelatedSubQueryInSelectNotNull() {
final String sql = "select empno, deptno not in (\n"
+ " select deptno from dept)\n"
+ "from emp";
@@ -1052,7 +1052,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
.convertsTo("${plan}");
}
- @Test public void testNotInUncorrelatedSubqueryInSelectNotNullRex() {
+ @Test public void testNotInUncorrelatedSubQueryInSelectNotNullRex() {
final String sql = "select empno, deptno not in (\n"
+ " select deptno from dept)\n"
+ "from emp";
@@ -1160,8 +1160,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
"${plan}");
}
- @Test public void testUnionSubquery() {
- // union of subquery, inside from list, also values
+ @Test public void testUnionSubQuery() {
+ // union of subQuery, inside from list, also values
check(
"select deptno from emp as emp0 cross join\n"
+ " (select empno from emp union all\n"
@@ -1368,7 +1368,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
/**
* Test group-by CASE expression involving a non-query IN
*/
- @Test public void testGroupByCaseSubquery() {
+ @Test public void testGroupByCaseSubQuery() {
sql("SELECT CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END\n"
+ "FROM emp\n"
+ "GROUP BY (CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END)")
@@ -1378,7 +1378,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
/**
* Test aggregate function on a CASE expression involving a non-query IN
*/
- @Test public void testAggCaseSubquery() {
+ @Test public void testAggCaseSubQuery() {
sql("SELECT SUM(CASE WHEN empno IN (3) THEN 0 ELSE 1 END) FROM emp")
.convertsTo("${plan}");
}
@@ -1391,19 +1391,31 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
+ "FROM emp) GROUP BY empno, EXPR$2").convertsTo("${plan}");
}
- @Test public void testAggScalarSubquery() {
+ @Test public void testAggScalarSubQuery() {
sql("SELECT SUM(SELECT min(deptno) FROM dept) FROM emp")
.convertsTo("${plan}");
}
/** Test aggregate function on a CASE expression involving IN with a
- * sub-query */
- @Ignore("[CALCITE-551] Sub-query inside aggregate function")
- @Test public void testAggCaseInSubquery() {
- sql("SELECT SUM(\n"
+ * sub-query.
+ *
+ * <p>Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-551">[CALCITE-551]
+ * Sub-query inside aggregate function</a>.
+ */
+ @Test public void testAggCaseInSubQuery() {
+ final String sql = "SELECT SUM(\n"
+ " CASE WHEN deptno IN (SELECT deptno FROM dept) THEN 1 ELSE 0 END)\n"
- + "FROM emp")
- .convertsTo("${plan}");
+ + "FROM emp";
+ sql(sql).expand(false).convertsTo("${plan}");
+ }
+
+ @Test public void testCorrelatedSubQueryInAggregate() {
+ final String sql = "SELECT SUM(\n"
+ + " (select char_length(name) from dept\n"
+ + " where dept.deptno = emp.empno))\n"
+ + "FROM emp";
+ sql(sql).expand(false).convertsTo("${plan}");
}
/**
@@ -1480,7 +1492,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
* SqlSingleValueAggFunction is created when it may not be needed</a>.
*/
- @Test public void testSubqueryAggreFunctionFollowedBySimpleOperation() {
+ @Test public void testSubQueryAggregateFunctionFollowedBySimpleOperation() {
sql("select deptno\n"
+ "from EMP\n"
+ "where deptno > (select min(deptno) * 2 + 10 from EMP)")
@@ -1492,7 +1504,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
* SqlSingleValueAggFunction is created when it may not be needed</a>.
*/
- @Test public void testSubqueryValues() {
+ @Test public void testSubQueryValues() {
sql("select deptno\n"
+ "from EMP\n"
+ "where deptno > (values 10)")
@@ -1504,7 +1516,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
* SqlSingleValueAggFunction is created when it may not be needed</a>.
*/
- @Test public void testSubqueryLimitOne() {
+ @Test public void testSubQueryLimitOne() {
sql("select deptno\n"
+ "from EMP\n"
+ "where deptno > (select deptno\n"
@@ -1518,7 +1530,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* When look up subqueries, perform the same logic as the way when ones were
* registered</a>.
*/
- @Test public void testIdenticalExpressionInSubquery() {
+ @Test public void testIdenticalExpressionInSubQuery() {
sql("select deptno\n"
+ "from EMP\n"
+ "where deptno in (1, 2) or deptno in (1, 2)")
@@ -1545,7 +1557,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* Scan HAVING clause for sub-queries and IN-lists</a>, with a sub-query in
* the HAVING clause.
*/
- @Test public void testHavingInSubqueryWithAggrFunction() {
+ @Test public void testHavingInSubQueryWithAggrFunction() {
sql("select sal\n"
+ "from emp\n"
+ "group by sal\n"
@@ -1639,10 +1651,10 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
/**
* 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
+ * variant involving join with subQuery that contains window function and
* GROUP BY</a>.
*/
- @Test public void testWindowAggInSubqueryJoin() {
+ @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"
@@ -1653,9 +1665,9 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
- * Test case (correlated scalar aggregate subquery) for
+ * Test case (correlated scalar aggregate subQuery) for
* <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
- * When de-correlating, push join condition into subquery</a>.
+ * When de-correlating, push join condition into subQuery</a>.
*/
@Test public void testCorrelationScalarAggAndFilter() {
final String sql = "SELECT e1.empno\n"
@@ -1676,9 +1688,9 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
- * Test case (correlated EXISTS subquery) for
+ * Test case (correlated EXISTS subQuery) for
* <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
- * When de-correlating, push join condition into subquery</a>.
+ * When de-correlating, push join condition into subQuery</a>.
*/
@Test public void testCorrelationExistsAndFilter() {
final String sql = "SELECT e1.empno\n"
@@ -1699,9 +1711,9 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
- * Test case (correlated NOT EXISTS subquery) for
+ * Test case (correlated NOT EXISTS subQuery) for
* <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
- * When de-correlating, push join condition into subquery</a>.
+ * When de-correlating, push join condition into subQuery</a>.
*/
@Test public void testCorrelationNotExistsAndFilter() {
tester.withDecorrelation(true).assertConvertsTo(
http://git-wip-us.apache.org/repos/asf/calcite/blob/82b58604/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 84e1afe..0f9c715 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -117,6 +117,25 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)], EXPR$2=[SUM(DISTINCT $1)], EXPR$
<![CDATA[select deptno, sum(sal), sum(distinct sal), count(*) from emp group by deptno]]>
</Resource>
</TestCase>
+ <TestCase name="testCorrelatedSubQueryInAggregate">
+ <Resource name="sql">
+ <![CDATA[SELECT SUM(
+ (select char_length(name) from dept
+ where dept.deptno = emp.empno))
+FROM emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject($f0=[$SCALAR_QUERY({
+LogicalProject(EXPR$0=[CHAR_LENGTH($1)])
+ LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testCorrelatedSubQueryInJoin">
<Resource name="sql">
<![CDATA[select *
@@ -156,7 +175,7 @@ LogicalProject(EXPR$0=[$0])
<![CDATA[select*from unnest(multiset[1,2])]]>
</Resource>
</TestCase>
- <TestCase name="testUnnestSubquery">
+ <TestCase name="testUnnestSubQuery">
<Resource name="plan">
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
@@ -198,7 +217,7 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], ORDINALITY=[$2])
]]>
</Resource>
</TestCase>
- <TestCase name="testMultisetSubquery">
+ <TestCase name="testMultisetSubQuery">
<Resource name="plan">
<![CDATA[
LogicalProject(EXPR$0=[$1])
@@ -408,7 +427,7 @@ select 34 from emp
union all values (30), (45 + 10)]]>
</Resource>
</TestCase>
- <TestCase name="testUnionSubquery">
+ <TestCase name="testUnionSubQuery">
<Resource name="plan">
<![CDATA[
LogicalProject(DEPTNO=[$7])
@@ -631,7 +650,7 @@ LogicalProject(EMPNO=[$0])
]]>
</Resource>
</TestCase>
- <TestCase name="testInUncorrelatedSubquery">
+ <TestCase name="testInUncorrelatedSubQuery">
<Resource name="sql">
<![CDATA[select empno from emp where deptno in (select deptno from dept)]]>
</Resource>
@@ -1372,7 +1391,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
- <TestCase name="testWithInsideScalarSubquery">
+ <TestCase name="testWithInsideScalarSubQuery">
<Resource name="sql">
<![CDATA[select (
with dept2 as (select * from dept where deptno > 10) select count(*) from dept2) as c
@@ -1704,7 +1723,7 @@ LogicalProject(EMPNO=[$0], X=[$1])
]]>
</Resource>
</TestCase>
- <TestCase name="testInUncorrelatedSubqueryInSelect">
+ <TestCase name="testInUncorrelatedSubQueryInSelect">
<Resource name="sql">
<![CDATA[select name, deptno in (
select case when true then deptno else null end from emp)
@@ -1728,7 +1747,7 @@ LogicalProject(NAME=[$1], EXPR$1=[CASE(=($2, 0), false, IS NOT NULL($6), true, I
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubqueryInSelect">
+ <TestCase name="testNotInUncorrelatedSubQueryInSelect">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
select case when true then deptno else null end from dept)
@@ -1752,7 +1771,7 @@ LogicalProject(EMPNO=[$0], EXPR$1=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), t
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubquery">
+ <TestCase name="testNotInUncorrelatedSubQuery">
<Resource name="sql">
<![CDATA[select empno from emp where deptno not in (select deptno from dept)]]>
</Resource>
@@ -1775,7 +1794,7 @@ LogicalProject(EMPNO=[$0])
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubqueryInSelectNotNull">
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectNotNull">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
select deptno from dept)
@@ -2069,7 +2088,7 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], X=[$2])
]]>
</Resource>
</TestCase>
- <TestCase name="testGroupByCaseSubquery">
+ <TestCase name="testGroupByCaseSubQuery">
<Resource name="sql">
<![CDATA[SELECT CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END
FROM emp
@@ -2096,7 +2115,7 @@ LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
]]>
</Resource>
</TestCase>
- <TestCase name="testAggCaseSubquery">
+ <TestCase name="testAggCaseSubQuery">
<Resource name="sql">
<![CDATA[SELECT SUM(CASE WHEN empno IN (3) THEN 0 ELSE 1 END) FROM emp]]>
</Resource>
@@ -2108,14 +2127,24 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
]]>
</Resource>
</TestCase>
- <TestCase name="testAggCaseInSubquery">
+ <TestCase name="testAggCaseInSubQuery">
<Resource name="sql">
<![CDATA[SELECT SUM(
CASE WHEN deptno IN (SELECT deptno FROM dept) THEN 1 ELSE 0 END)
FROM emp]]>
</Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject($f0=[CASE(IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}), 1, 0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
</TestCase>
- <TestCase name="testAggScalarSubquery">
+ <TestCase name="testAggScalarSubQuery">
<Resource name="sql">
<![CDATA[SELECT SUM(SELECT min(deptno) FROM dept) FROM emp]]>
</Resource>
@@ -2201,7 +2230,7 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $2], EXPR$2=[COUNT()])
]]>
</Resource>
</TestCase>
- <TestCase name="testSubqueryAggreFunctionFollowedBySimpleOperation">
+ <TestCase name="testSubQueryAggregateFunctionFollowedBySimpleOperation">
<Resource name="sql">
<![CDATA[select deptno
from EMP
@@ -2220,7 +2249,7 @@ LogicalProject(DEPTNO=[$7])
]]>
</Resource>
</TestCase>
- <TestCase name="testSubqueryValues">
+ <TestCase name="testSubQueryValues">
<Resource name="sql">
<![CDATA[select deptno
from EMP
@@ -2236,7 +2265,7 @@ LogicalProject(DEPTNO=[$7])
]]>
</Resource>
</TestCase>
- <TestCase name="testSubqueryLimitOne">
+ <TestCase name="testSubQueryLimitOne">
<Resource name="sql">
<![CDATA[select deptno
from EMP
@@ -2255,7 +2284,7 @@ LogicalProject(DEPTNO=[$7])
]]>
</Resource>
</TestCase>
- <TestCase name="testIdenticalExpressionInSubquery">
+ <TestCase name="testIdenticalExpressionInSubQuery">
<Resource name="sql">
<![CDATA[select deptno
from EMP
@@ -2287,7 +2316,7 @@ LogicalProject(DEPTNO=[$0])
]]>
</Resource>
</TestCase>
- <TestCase name="testHavingInSubqueryWithAggrFunction">
+ <TestCase name="testHavingInSubQueryWithAggrFunction">
<Resource name="sql">
<![CDATA[select sal
from emp
@@ -2467,7 +2496,7 @@ LogicalProject(EXPR$0=[$2], EXPR$1=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOU
]]>
</Resource>
</TestCase>
- <TestCase name="testWindowAggInSubqueryJoin">
+ <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,
@@ -2703,7 +2732,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
- <TestCase name="testWithInsideScalarSubqueryRex">
+ <TestCase name="testWithInsideScalarSubQueryRex">
<Resource name="sql">
<![CDATA[select (
with dept2 as (select * from dept where deptno > 10) select count(*) from dept2) as c
@@ -2742,7 +2771,7 @@ LogicalFilter(condition=[<=($0, $cor1.DEPTNO)])
]]>
</Resource>
</TestCase>
- <TestCase name="testInUncorrelatedSubqueryInSelectRex">
+ <TestCase name="testInUncorrelatedSubQueryInSelectRex">
<Resource name="sql">
<![CDATA[select name, deptno in (
select case when true then deptno else null end from emp)
@@ -2758,7 +2787,7 @@ LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubqueryInSelectNotNullRex">
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectNotNullRex">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
select deptno from dept)
@@ -2774,7 +2803,7 @@ LogicalProject(DEPTNO=[$0])
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubqueryRex">
+ <TestCase name="testNotInUncorrelatedSubQueryRex">
<Resource name="sql">
<![CDATA[select empno from emp where deptno not in (select deptno from dept)]]>
</Resource>
@@ -2789,7 +2818,7 @@ LogicalProject(DEPTNO=[$0])
]]>
</Resource>
</TestCase>
- <TestCase name="testNotInUncorrelatedSubqueryInSelectRex">
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectRex">
<Resource name="sql">
<![CDATA[select empno, deptno not in (
select case when true then deptno else null end from dept)
@@ -2805,7 +2834,7 @@ LogicalProject(EXPR$0=[CASE(true, CAST($0):INTEGER, null)])
]]>
</Resource>
</TestCase>
- <TestCase name="testInUncorrelatedSubqueryRex">
+ <TestCase name="testInUncorrelatedSubQueryRex">
<Resource name="sql">
<![CDATA[select empno from emp where deptno in (select deptno from dept)]]>
</Resource>
@@ -2840,7 +2869,7 @@ LogicalFilter(condition=[<=($0, $cor1.DEPTNO)])
]]>
</Resource>
</TestCase>
- <TestCase name="testCompositeInUncorrelatedSubqueryRex">
+ <TestCase name="testCompositeInUncorrelatedSubQueryRex">
<Resource name="sql">
<![CDATA[select empno from emp where (empno, deptno) in (select deptno - 10, deptno from dept)]]>
</Resource>
@@ -2892,7 +2921,7 @@ LogicalFilter(condition=[>($0, +($cor0.DEPTNO0, 5))])
]]>
</Resource>
</TestCase>
- <TestCase name="testInUncorrelatedSubqueryInHavingRex">
+ <TestCase name="testInUncorrelatedSubQueryInHavingRex">
<Resource name="sql">
<![CDATA[select sum(sal) as s
from emp
@@ -2914,7 +2943,7 @@ LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
]]>
</Resource>
</TestCase>
- <TestCase name="testUncorrelatedScalarSubqueryInGroupOrderRex">
+ <TestCase name="testUncorrelatedScalarSubQueryInGroupOrderRex">
<Resource name="sql">
<![CDATA[select sum(sal) as s
from emp
@@ -2936,7 +2965,7 @@ LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
]]>
</Resource>
</TestCase>
- <TestCase name="testUncorrelatedScalarSubqueryInOrderRex">
+ <TestCase name="testUncorrelatedScalarSubQueryInOrderRex">
<Resource name="sql">
<![CDATA[select ename
from emp
@@ -2955,7 +2984,7 @@ LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
]]>
</Resource>
</TestCase>
- <TestCase name="testUncorrelatedScalarSubqueryInAggregateRex">
+ <TestCase name="testUncorrelatedScalarSubQueryInAggregateRex">
<Resource name="sql">
<![CDATA[select sum((select min(deptno) from emp)) as s
from emp
http://git-wip-us.apache.org/repos/asf/calcite/blob/82b58604/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 52a15cc..39bd683 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -591,6 +591,54 @@ from (values cast(-86.4 as float), cast(-100 as float)) as t(v);
!ok
+# [CALCITE-551] Sub-query inside aggregate function
+SELECT SUM(
+ CASE WHEN deptno IN (SELECT deptno FROM "scott".dept) THEN 1
+ ELSE 0 END) as s
+FROM "scott".emp;
++----+
+| S |
++----+
+| 14 |
++----+
+(1 row)
+
+!ok
+
+SELECT SUM((select min(cast(deptno as integer)) from "scott".dept)) as s
+FROM "scott".emp;
++-----+
+| S |
++-----+
+| 140 |
++-----+
+(1 row)
+
+!ok
+
+# As above, but with GROUP BY
+SELECT SUM((select min(cast(deptno as integer)) from "scott".dept)) as s, deptno
+FROM "scott".emp
+GROUP BY deptno;
++----+--------+
+| S | DEPTNO |
++----+--------+
+| 30 | 10 |
+| 50 | 20 |
+| 60 | 30 |
++----+--------+
+(3 rows)
+
+!ok
+
+# As above, but with correlation
+!if (fixed.calcite1045) {
+SELECT SUM(
+ (select char_length(dname) from "scott".dept where dept.deptno = emp.empno)) as s
+FROM "scott".emp;
+!ok
+!}
+
# COLLECT
select deptno, collect(empno) as empnos
from "scott".emp