[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 <[email protected]> Authored: Wed Jan 13 09:51:31 2016 -0800 Committer: Julian Hyde <[email protected]> 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
