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 2017/01/15 15:50:09 UTC
calcite git commit: [CALCITE-1546] Wrong plan for NOT IN sub-queries
with disjunction
Repository: calcite
Updated Branches:
refs/heads/master d06e5000e -> 0bd4f86b6
[CALCITE-1546] Wrong plan for NOT IN sub-queries with disjunction
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/0bd4f86b
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/0bd4f86b
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/0bd4f86b
Branch: refs/heads/master
Commit: 0bd4f86b6e5f1e1f6b8fa6ce4d9939235ef50bd1
Parents: d06e500
Author: Julian Hyde <jh...@apache.org>
Authored: Thu Jan 12 20:27:56 2017 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Sat Jan 14 23:35:05 2017 -0800
----------------------------------------------------------------------
.../org/apache/calcite/rex/LogicVisitor.java | 2 +-
.../apache/calcite/test/RelOptRulesTest.java | 10 +++++
.../apache/calcite/test/RexTransformerTest.java | 5 ++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 43 ++++++++++++++++++++
core/src/test/resources/sql/blank.iq | 40 ++++++++++++++++++
5 files changed, 97 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/0bd4f86b/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java b/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
index 14220df..721603e 100644
--- a/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
+++ b/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
@@ -105,7 +105,7 @@ public class LogicVisitor implements RexBiVisitor<Logic, Logic> {
case AND:
break;
default:
- logic = Logic.TRUE_FALSE;
+ logic = Logic.TRUE_FALSE_UNKNOWN;
}
}
for (RexNode operand : call.operands) {
http://git-wip-us.apache.org/repos/asf/calcite/blob/0bd4f86b/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 ac55f8a..112ceb9 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2823,6 +2823,16 @@ public class RelOptRulesTest extends RelOptTestBase {
checkSubQuery(sql).withLateDecorrelation(true).check();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1546">[CALCITE-1546]
+ * Sub-queries connected by OR</a>. */
+ @Test public void testWhereOrSubQuery() {
+ final String sql = "select * from emp\n"
+ + "where sal = 4\n"
+ + "or empno NOT IN (select deptno from dept)";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
@Test public void testExpandProjectIn() throws Exception {
final String sql = "select empno,\n"
+ " deptno in (select deptno from sales.emp where empno < 20) as d\n"
http://git-wip-us.apache.org/repos/asf/calcite/blob/0bd4f86b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java
index 9837f91..fcaac89 100644
--- a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java
@@ -399,10 +399,11 @@ public class RexTransformerTest {
assertThat(deduceLogic(and(x, not(y)), y, Logic.TRUE),
is(Logic.UNKNOWN_AS_TRUE));
assertThat(deduceLogic(and(x, not(not(y))), y, Logic.TRUE),
- is(Logic.TRUE_FALSE));
+ is(Logic.TRUE_FALSE_UNKNOWN)); // TRUE_FALSE would be better
assertThat(deduceLogic(and(x, not(and(y, z))), z, Logic.TRUE),
is(Logic.UNKNOWN_AS_TRUE));
- assertThat(deduceLogic(or(x, y), x, Logic.TRUE), is(Logic.TRUE_FALSE));
+ assertThat(deduceLogic(or(x, y), x, Logic.TRUE),
+ is(Logic.TRUE_FALSE_UNKNOWN));
}
private Logic deduceLogic(RexNode root, RexNode seek, Logic logic) {
http://git-wip-us.apache.org/repos/asf/calcite/blob/0bd4f86b/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 f76ef23..866da2e 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6715,4 +6715,47 @@ LogicalProject(SAL=[$5])
]]>
</Resource>
</TestCase>
+ <TestCase name="testWhereOrSubQuery">
+ <Resource name="sql">
+ <![CDATA[select * from emp
+where sal = 4
+or empno NOT IN (select deptno from dept)]]>
+ </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=[OR(=($5, 4), NOT(IN($0, {
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})))])
+ 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=[OR(=($5, 4), NOT(CASE(IS NOT NULL($10), true, false)))])
+ LogicalJoin(condition=[=($0, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </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=[OR(=($5, 4), NOT(CASE(IS NOT NULL($10), true, false)))])
+ LogicalJoin(condition=[=($0, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/calcite/blob/0bd4f86b/core/src/test/resources/sql/blank.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/blank.iq b/core/src/test/resources/sql/blank.iq
index c157006..c305b9d 100644
--- a/core/src/test/resources/sql/blank.iq
+++ b/core/src/test/resources/sql/blank.iq
@@ -100,4 +100,44 @@ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t3, $t9)], expr#11=
!ok
+select * from table1 where j not in (select i from table2);
++---+---+
+| I | J |
++---+---+
++---+---+
+(0 rows)
+
+!ok
+
+select * from table1 where j not in (select i from table2) or i = 1;
++---+---+
+| I | J |
++---+---+
+| 1 | 2 |
+| 1 | 3 |
++---+---+
+(2 rows)
+
+!ok
+
+select * from table1 where j not in (select i from table2) or j = 2;
++---+---+
+| I | J |
++---+---+
+| 1 | 2 |
++---+---+
+(1 row)
+
+!ok
+
+select * from table1 where j not in (select i from table2) or j = 3;
++---+---+
+| I | J |
++---+---+
+| 1 | 3 |
++---+---+
+(1 row)
+
+!ok
+
# End blank.iq