calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject calcite git commit: [CALCITE-1546] Wrong plan for NOT IN sub-queries with disjunction
Date Sun, 15 Jan 2017 15:50:09 GMT
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 <jhyde@apache.org>
Authored: Thu Jan 12 20:27:56 2017 -0800
Committer: Julian Hyde <jhyde@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


Mime
View raw message