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/12/27 07:49:52 UTC
[2/2] calcite git commit: [CALCITE-1511] Decorrelation fails if query
has more than one EXISTS in WHERE clause
[CALCITE-1511] Decorrelation fails if query has more than one EXISTS in WHERE clause
[CALCITE-1537] Unnecessary project expression in multi-sub-query plan
Add "planMid" resource to RelOptRulesTest, checked if lateDecorrelate
is enabled.
Add a test case for multiple scalar sub-queries, but it won't be fixed
until [CALCITE-1045].
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/1d2067bd
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/1d2067bd
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/1d2067bd
Branch: refs/heads/master
Commit: 1d2067bd0497540b3e416ac5c7440ea9578cd196
Parents: 3f26107
Author: Julian Hyde <jh...@apache.org>
Authored: Tue Dec 13 17:40:26 2016 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Dec 26 17:40:52 2016 -0800
----------------------------------------------------------------------
.../calcite/rel/rules/SubQueryRemoveRule.java | 32 ++-
.../apache/calcite/test/RelOptRulesTest.java | 47 ++++
.../org/apache/calcite/test/RelOptTestBase.java | 14 +-
.../org/apache/calcite/test/RelOptRulesTest.xml | 271 ++++++++++++++++++-
core/src/test/resources/sql/sub-query.iq | 34 +++
5 files changed, 367 insertions(+), 31 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/1d2067bd/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
index f0779d9..f9315e2 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
@@ -43,6 +43,7 @@ import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilderFactory;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Pair;
+import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
@@ -95,18 +96,25 @@ public abstract class SubQueryRemoveRule extends RelOptRule {
public void onMatch(RelOptRuleCall call) {
final Filter filter = call.rel(0);
final RelBuilder builder = call.builder();
- final RexSubQuery e =
- RexUtil.SubQueryFinder.find(filter.getCondition());
- assert e != null;
- final RelOptUtil.Logic logic =
- LogicVisitor.find(RelOptUtil.Logic.TRUE,
- ImmutableList.of(filter.getCondition()), e);
builder.push(filter.getInput());
- final int fieldCount = builder.peek().getRowType().getFieldCount();
- final RexNode target = apply(e, filter.getVariablesSet(), logic,
- builder, 1, fieldCount);
- final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
- builder.filter(shuttle.apply(filter.getCondition()));
+ int count = 0;
+ RexNode c = filter.getCondition();
+ for (;;) {
+ final RexSubQuery e = RexUtil.SubQueryFinder.find(c);
+ if (e == null) {
+ assert count > 0;
+ break;
+ }
+ ++count;
+ final RelOptUtil.Logic logic =
+ LogicVisitor.find(RelOptUtil.Logic.TRUE, ImmutableList.of(c),
+ e);
+ final RexNode target = apply(e, filter.getVariablesSet(), logic,
+ builder, 1, builder.peek().getRowType().getFieldCount());
+ final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
+ c = c.accept(shuttle);
+ }
+ builder.filter(c);
builder.project(fields(builder, filter.getRowType().getFieldCount()));
call.transformTo(builder.build());
}
@@ -291,7 +299,7 @@ public abstract class SubQueryRemoveRule extends RelOptRule {
builder.literal(false));
break;
}
- operands.add(builder.isNotNull(builder.field("dt", "i")),
+ operands.add(builder.isNotNull(Util.last(builder.fields())),
builder.literal(true));
if (!keyIsNulls.isEmpty()) {
operands.add(builder.or(keyIsNulls), builder.literal(null));
http://git-wip-us.apache.org/repos/asf/calcite/blob/1d2067bd/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 7aa6b33..72cddd4 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2895,6 +2895,53 @@ public class RelOptRulesTest extends RelOptTestBase {
checkSubQuery(sql).check();
}
+ @Test public void testDecorrelateExists() throws Exception {
+ final String sql = "select * from sales.emp\n"
+ + "where EXISTS (\n"
+ + " select * from emp e where emp.deptno = e.deptno)";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1511">[CALCITE-1511]
+ * AssertionError while decorrelating query with two EXISTS
+ * sub-queries</a>. */
+ @Test public void testDecorrelateTwoExists() throws Exception {
+ final String sql = "select * from sales.emp\n"
+ + "where EXISTS (\n"
+ + " select * from emp e where emp.deptno = e.deptno)\n"
+ + "AND NOT EXISTS (\n"
+ + " select * from emp ee where ee.job = emp.job AND ee.sal=34)";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1537">[CALCITE-1537]
+ * Unnecessary project expression in multi-sub-query plan</a>. */
+ @Test public void testDecorrelateTwoIn() throws Exception {
+ final String sql = "select sal\n"
+ + "from sales.emp\n"
+ + "where empno IN (\n"
+ + " select deptno from dept where emp.job = dept.name)\n"
+ + "AND empno IN (\n"
+ + " select empno from emp e where emp.ename = e.ename)";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1045">[CALCITE-1045]
+ * Decorrelate sub-queries in Project and Join</a>, with the added
+ * complication that there are two sub-queries. */
+ @Ignore("[CALCITE-1045]")
+ @Test public void testDecorrelateTwoScalar() throws Exception {
+ final String sql = "select deptno,\n"
+ + " (select min(1) from emp where empno > d.deptno) as i0,\n"
+ + " (select min(0) from emp\n"
+ + " where deptno = d.deptno and ename = 'SMITH') as i1\n"
+ + "from dept as d";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
@Test public void testWhereInCorrelated() {
final String sql = "select empno from emp as e\n"
+ "join dept as d using (deptno)\n"
http://git-wip-us.apache.org/repos/asf/calcite/blob/1d2067bd/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
index 277d368..28f0b9c 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
@@ -157,17 +157,19 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
assertThat(relBefore, notNullValue());
- String planBefore = NL + RelOptUtil.toString(relBefore);
+ final String planBefore = NL + RelOptUtil.toString(relBefore);
diffRepos.assertEquals("planBefore", "${planBefore}", planBefore);
SqlToRelTestBase.assertValid(relBefore);
planner.setRoot(relBefore);
- RelNode relAfter = planner.findBestExp();
- String planAfter = NL + RelOptUtil.toString(relAfter);
+ RelNode r = planner.findBestExp();
if (tester.isLateDecorrelate()) {
- relAfter = RelDecorrelator.decorrelateQuery(relAfter);
- planAfter = NL + RelOptUtil.toString(relAfter);
+ final String planMid = NL + RelOptUtil.toString(r);
+ diffRepos.assertEquals("planMid", "${planMid}", planMid);
+ SqlToRelTestBase.assertValid(r);
+ r = RelDecorrelator.decorrelateQuery(r);
}
+ final String planAfter = NL + RelOptUtil.toString(r);
if (unchanged) {
assertThat(planAfter, is(planBefore));
} else {
@@ -177,7 +179,7 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
+ "You must use unchanged=true or call checkPlanUnchanged");
}
}
- SqlToRelTestBase.assertValid(relAfter);
+ SqlToRelTestBase.assertValid(r);
}
/** Sets the SQL statement for a test. */
http://git-wip-us.apache.org/repos/asf/calcite/blob/1d2067bd/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index e2fd58e..24f60c6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -63,7 +63,7 @@ LogicalProject(SAL=[$5])
</TestCase>
<TestCase name="testReduceNullableCase">
<Resource name="sql">
- <![CDATA[SELECT CASE WHEN 1=2 THEN cast((values(1)) as integer) ELSE 2 end as state_nm from (values(1)) as a]]>
+ <![CDATA[SELECT CASE WHEN 1=2 THEN cast((values(1)) as integer) ELSE 2 end from (values(1))]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -195,6 +195,231 @@ LogicalProject(EXPR$0=[1])
]]>
</Resource>
</TestCase>
+ <TestCase name="testDecorrelateExists">
+ <Resource name="sql">
+ <![CDATA[select * from sales.emp
+where EXISTS (
+ select * from emp e where emp.deptno = e.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO0=[$0], $f1=[true])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO0=[$1], i=[$0])
+ LogicalProject(i=[true], DEPTNO0=[$9])
+ LogicalJoin(condition=[=($9, $7)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{7}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateTwoExists">
+ <Resource name="sql">
+ <![CDATA[select * from sales.emp
+where EXISTS (
+ select * from emp e where emp.deptno = e.deptno)
+AND NOT EXISTS (
+ select * from emp ee where ee.job = emp.job AND ee.sal=34)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[AND(EXISTS({
+LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}), NOT(EXISTS({
+LogicalFilter(condition=[AND(=($2, $cor0.JOB), =($5, 34))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[IS NULL($10)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{2}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{7}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[AND(=($2, $cor0.JOB), =($5, 34))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[IS NULL($12)])
+ LogicalJoin(condition=[=($2, $11)], joinType=[left])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO0=[$0], $f1=[true])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO0=[$1], i=[$0])
+ LogicalProject(i=[true], DEPTNO0=[$9])
+ LogicalJoin(condition=[=($9, $7)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(JOB0=[$0], $f1=[true])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(JOB0=[$1], i=[$0])
+ LogicalProject(i=[true], JOB0=[$9])
+ LogicalJoin(condition=[=($2, $9)], joinType=[inner])
+ LogicalFilter(condition=[=($5, 34)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateTwoIn">
+ <Resource name="sql">
+ <![CDATA[select sal
+from sales.emp
+where empno IN (
+ select deptno from dept where emp.job = dept.name)
+AND empno IN (
+ select empno from emp e where emp.ename = e.ename)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[AND(IN($0, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($cor0.JOB, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}), IN($0, {
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[=($cor0.ENAME, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($0, $10)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{1}])
+ LogicalFilter(condition=[=($0, $9)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($cor0.JOB, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[=($cor0.ENAME, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[AND(=($1, $12), =($0, $11))], joinType=[inner])
+ LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(DEPTNO=[$0], JOB=[$1])
+ LogicalProject(DEPTNO=[$0], JOB=[$2])
+ LogicalJoin(condition=[=($2, $1)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(EMPNO=[$0], ENAME0=[$1])
+ LogicalProject(EMPNO=[$0], ENAME0=[$9])
+ LogicalJoin(condition=[=($9, $1)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateTwoScalar">
+ <Resource name="sql">
+ <![CDATA[select deptno,
+ (select min(1) from emp where empno > d.deptno) as i0,
+ (select min(0) from emp
+ where deptno = d.deptno and ename = 'SMITH') as i1
+from dept as d]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], I0=[$SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+ LogicalProject($f0=[1])
+ LogicalFilter(condition=[>($0, $cor0.DEPTNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], I1=[$SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+ LogicalProject($f0=[0])
+ LogicalFilter(condition=[AND(=($7, $cor1.DEPTNO), =($1, 'SMITH'))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], I0=[$2], I1=[$3])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+ LogicalProject($f0=[1])
+ LogicalFilter(condition=[>($0, $cor0.DEPTNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+ LogicalProject($f0=[0])
+ LogicalFilter(condition=[AND(=($7, $cor1.DEPTNO), =($1, 'SMITH'))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testEmptyAggregate">
<Resource name="sql">
<![CDATA[select sum(empno) from emp where false group by deptno]]>
@@ -452,9 +677,10 @@ LogicalProject(EXPR$0=[1])
</TestCase>
<TestCase name="testPushFilterThroughSemiJoin">
<Resource name="sql">
- <![CDATA[select * from (select * from dept where dept.deptno in (
- select emp.deptno from emp
- ))R where R.deptno <=10 ]]>
+ <![CDATA[select * from (
+ select * from dept where dept.deptno in (
+ select emp.deptno from emp))R
+where R.deptno <=10]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -5820,19 +6046,18 @@ LogicalProject(DEPTNO=[$7])
<![CDATA[
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9])
- LogicalFilter(condition=[OR(<($9, $10), <($5, 100))])
+ LogicalFilter(condition=[OR(<($9, $10), <($5, 100))])
+ LogicalJoin(condition=[true], joinType=[left])
LogicalJoin(condition=[true], joinType=[left])
- LogicalJoin(condition=[true], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
- LogicalProject(DEPTNO=[$7])
- LogicalFilter(condition=[<($0, 20)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalProject(DEPTNO=[$7])
- LogicalFilter(condition=[>($0, 100)])
+ LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[>($0, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -6376,5 +6601,25 @@ LogicalProject(SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), true, false))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{2}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($cor0.JOB, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[=($cor0.EMPNO, $0)])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($cor0.JOB, $1)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
</TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/calcite/blob/1d2067bd/core/src/test/resources/sql/sub-query.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index ec42c0c..c509ce2 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -510,4 +510,38 @@ where d.dname = (
!ok
+# Two EXISTS
+# [CALCITE-1511] AssertionError while decorrelating query with two EXISTS sub-queries
+select *
+from "scott".dept as d
+where exists (select 1 from "scott".emp where empno > d.deptno)
+and exists (select 0 from "scott".emp where deptno = d.deptno and ename = 'SMITH');
++--------+----------+--------+
+| DEPTNO | DNAME | LOC |
++--------+----------+--------+
+| 20 | RESEARCH | DALLAS |
++--------+----------+--------+
+(1 row)
+
+!ok
+
+# Two scalar sub-queries
+!if (fixed.calcite1045) {
+select deptno,
+ (select min(1) from "scott".emp where empno > d.deptno) as i0,
+ (select min(0) from "scott".emp where deptno = d.deptno and ename = 'SMITH') as i1
+from "scott".dept as d;
++--------+----+---+
+| DEPTNO | I0 | I1|
++--------+----+---+
+| 10 | 1 | |
+| 20 | 1 | 0 |
+| 30 | 1 | |
+| 40 | 1 | |
++--------+----+---+
+(4 rows)
+
+!ok
+!}
+
# End sub-query.iq