[calcite] branch master updated: [CALCITE-1726] Subquery in FILTER is left untransformed (Vineet Garg)

[jcamacho]

This is an automated email from the ASF dual-hosted git repository.

jcamacho pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/master by this push:
     new 2102f1f  [CALCITE-1726] Subquery in FILTER is left untransformed 
(Vineet Garg)
2102f1f is described below

commit 2102f1f5442fa271c258b7754da8df07d65847ec
Author: Vineet Garg <vg...@apache.org>
AuthorDate: Thu Feb 7 20:56:19 2019 -0800

    [CALCITE-1726] Subquery in FILTER is left untransformed (Vineet Garg)
    
    Close apache/calcite#1035
---
 .../java/org/apache/calcite/tools/Programs.java    |  9 ++--
 core/src/test/resources/sql/sub-query.iq           | 56 ++++++++++++++++++++++
 2 files changed, 61 insertions(+), 4 deletions(-)

diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java 
b/core/src/main/java/org/apache/calcite/tools/Programs.java
index b6337e2..38d6aa5 100644
--- a/core/src/main/java/org/apache/calcite/tools/Programs.java
+++ b/core/src/main/java/org/apache/calcite/tools/Programs.java
@@ -258,10 +258,11 @@ public class Programs {
   }
 
   public static Program subQuery(RelMetadataProvider metadataProvider) {
-    return hep(
-        ImmutableList.of((RelOptRule) SubQueryRemoveRule.FILTER,
-            SubQueryRemoveRule.PROJECT,
-            SubQueryRemoveRule.JOIN), true, metadataProvider);
+    final HepProgramBuilder builder = HepProgram.builder();
+    builder.addRuleCollection(ImmutableList.of((RelOptRule) 
SubQueryRemoveRule.FILTER,
+        SubQueryRemoveRule.PROJECT,
+        SubQueryRemoveRule.JOIN));
+    return of(builder.build(), true, metadataProvider);
   }
 
   public static Program getProgram() {
diff --git a/core/src/test/resources/sql/sub-query.iq 
b/core/src/test/resources/sql/sub-query.iq
index 4168eb0..9b8c179 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -1998,4 +1998,60 @@ from "scott".emp;
 
 !ok
 
+# Test nested subquery in PROJECT within FILTER
+select * from emp where deptno IN (select (select max(deptno) from "scott".emp 
t1) from "scott".emp t2);
+ EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO
+-------+--------+----------+------+------------+---------+---------+--------
+  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
+  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
+  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
+  7698 | BLAKE  | MANAGER  | 7839 | 1981-01-05 | 2850.00 |         |     30
+  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
+  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |         |     30
+(6 rows)
+
+!ok
+EnumerableSemiJoin(condition=[=($7, $9)], joinType=[inner])
+  EnumerableTableScan(table=[[scott, EMP]])
+  EnumerableJoin(condition=[true], joinType=[left])
+    EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+      EnumerableTableScan(table=[[scott, EMP]])
+    EnumerableAggregate(group=[{}], EXPR$0=[MAX($7)])
+      EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test nested subquery in FILTER within PROJECT
+select (select max(deptno) from "scott".emp where deptno IN (select deptno 
from "scott".emp)) from emp ;
+ EXPR$0
+--------
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+     30
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])
+  EnumerableJoin(condition=[true], joinType=[left])
+    EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+      EnumerableTableScan(table=[[scott, EMP]])
+    EnumerableAggregate(group=[{}], EXPR$0=[MAX($1)])
+      EnumerableSemiJoin(condition=[=($1, $9)], joinType=[inner])
+        EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
+          EnumerableTableScan(table=[[scott, EMP]])
+        EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+
+
 # End sub-query.iq