calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maryann...@apache.org
Subject calcite git commit: [CALCITE-1507] OFFSET cannot be pushed through a JOIN if the non-preserved side of outer join is not count-preserving
Date Wed, 07 Dec 2016 06:07:28 GMT
Repository: calcite
Updated Branches:
  refs/heads/master ffe1a68d7 -> 106dcc0fa


[CALCITE-1507] OFFSET cannot be pushed through a JOIN if the non-preserved side of outer join
is not count-preserving


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/106dcc0f
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/106dcc0f
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/106dcc0f

Branch: refs/heads/master
Commit: 106dcc0fa58d651c17a3a01993e23f4bf3fdbf89
Parents: ffe1a68
Author: maryannxue <maryann.xue@gmail.com>
Authored: Tue Dec 6 22:07:15 2016 -0800
Committer: maryannxue <maryann.xue@gmail.com>
Committed: Tue Dec 6 22:07:15 2016 -0800

----------------------------------------------------------------------
 .../rel/rules/SortJoinTransposeRule.java        |  21 ++-
 .../apache/calcite/test/MockCatalogReader.java  |  23 ++-
 .../apache/calcite/test/RelMetadataTest.java    |   4 +-
 .../apache/calcite/test/RelOptRulesTest.java    |  87 +++++++---
 .../org/apache/calcite/test/RelOptRulesTest.xml | 165 ++++++++++++-------
 5 files changed, 208 insertions(+), 92 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/106dcc0f/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
index 2da9a35..d6bf453 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
@@ -24,6 +24,7 @@ import org.apache.calcite.rel.RelCollations;
 import org.apache.calcite.rel.RelFieldCollation;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinInfo;
 import org.apache.calcite.rel.core.JoinRelType;
 import org.apache.calcite.rel.core.Sort;
 import org.apache.calcite.rel.logical.LogicalJoin;
@@ -61,11 +62,17 @@ public class SortJoinTransposeRule extends RelOptRule {
   @Override public boolean matches(RelOptRuleCall call) {
     final Sort sort = call.rel(0);
     final Join join = call.rel(1);
+    final RelMetadataQuery mq = RelMetadataQuery.instance();
+    final JoinInfo joinInfo = JoinInfo.of(
+        join.getLeft(), join.getRight(), join.getCondition());
 
     // 1) If join is not a left or right outer, we bail out
-    // 2) If sort does not consist only of a limit operation,
-    // or any sort column is not part of the input where the
+    // 2) If sort is not a trivial order-by, and if there is
+    // any sort column that is not part of the input where the
     // sort is pushed, we bail out
+    // 3) If sort has an offset, and if the non-preserved side
+    // of the join is not count-preserving against the join
+    // condition, we bail out
     if (join.getJoinType() == JoinRelType.LEFT) {
       if (sort.getCollation() != RelCollations.EMPTY) {
         for (RelFieldCollation relFieldCollation
@@ -76,6 +83,11 @@ public class SortJoinTransposeRule extends RelOptRule {
           }
         }
       }
+      if (sort.offset != null
+          && !RelMdUtil.areColumnsDefinitelyUnique(
+              mq, join.getRight(), joinInfo.rightSet())) {
+        return false;
+      }
     } else if (join.getJoinType() == JoinRelType.RIGHT) {
       if (sort.getCollation() != RelCollations.EMPTY) {
         for (RelFieldCollation relFieldCollation
@@ -86,6 +98,11 @@ public class SortJoinTransposeRule extends RelOptRule {
           }
         }
       }
+      if (sort.offset != null
+          && !RelMdUtil.areColumnsDefinitelyUnique(
+              mq, join.getLeft(), joinInfo.leftSet())) {
+        return false;
+      }
     } else {
       return false;
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/106dcc0f/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
index 9dc0692..a21218b 100644
--- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
+++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
@@ -206,7 +206,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     // Register "EMP" table.
     final MockTable empTable =
         MockTable.create(this, salesSchema, "EMP", false, 14);
-    empTable.addColumn("EMPNO", intType);
+    empTable.addColumn("EMPNO", intType, true);
     empTable.addColumn("ENAME", varchar20Type);
     empTable.addColumn("JOB", varchar10Type);
     empTable.addColumn("MGR", intTypeNull);
@@ -220,7 +220,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     // Register "EMP_B" table. As "EMP", birth with a "BIRTHDATE" column.
     final MockTable empBTable =
         MockTable.create(this, salesSchema, "EMP_B", false, 14);
-    empBTable.addColumn("EMPNO", intType);
+    empBTable.addColumn("EMPNO", intType, true);
     empBTable.addColumn("ENAME", varchar20Type);
     empBTable.addColumn("JOB", varchar10Type);
     empBTable.addColumn("MGR", intTypeNull);
@@ -234,14 +234,14 @@ public class MockCatalogReader implements Prepare.CatalogReader {
 
     // Register "DEPT" table.
     MockTable deptTable = MockTable.create(this, salesSchema, "DEPT", false, 4);
-    deptTable.addColumn("DEPTNO", intType);
+    deptTable.addColumn("DEPTNO", intType, true);
     deptTable.addColumn("NAME", varchar10Type);
     registerTable(deptTable);
 
     // Register "DEPT_NESTED" table.
     MockTable deptNestedTable =
         MockTable.create(this, salesSchema, "DEPT_NESTED", false, 4);
-    deptNestedTable.addColumn("DEPTNO", intType);
+    deptNestedTable.addColumn("DEPTNO", intType, true);
     deptNestedTable.addColumn("NAME", varchar10Type);
     deptNestedTable.addColumn("EMPLOYEES", empListType);
     registerTable(deptNestedTable);
@@ -258,7 +258,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     // Register "SALGRADE" table.
     MockTable salgradeTable =
         MockTable.create(this, salesSchema, "SALGRADE", false, 5);
-    salgradeTable.addColumn("GRADE", intType);
+    salgradeTable.addColumn("GRADE", intType, true);
     salgradeTable.addColumn("LOSAL", intType);
     salgradeTable.addColumn("HISAL", intType);
     registerTable(salgradeTable);
@@ -266,7 +266,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     // Register "EMP_ADDRESS" table
     MockTable contactAddressTable =
         MockTable.create(this, salesSchema, "EMP_ADDRESS", false, 26);
-    contactAddressTable.addColumn("EMPNO", intType);
+    contactAddressTable.addColumn("EMPNO", intType, true);
     contactAddressTable.addColumn("HOME_ADDRESS", addressType);
     contactAddressTable.addColumn("MAILING_ADDRESS", addressType);
     registerTable(contactAddressTable);
@@ -630,6 +630,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     private final double rowCount;
     protected final List<Map.Entry<String, RelDataType>> columnList =
         new ArrayList<>();
+    protected final List<Integer> keyList = new ArrayList<>();
     protected RelDataType rowType;
     private List<RelCollation> collationList;
     protected final List<String> names;
@@ -740,7 +741,8 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     }
 
     public boolean isKey(ImmutableBitSet columns) {
-      return false;
+      return !keyList.isEmpty()
+          && columns.contains(ImmutableBitSet.of(keyList));
     }
 
     public RelDataType getRowType() {
@@ -776,6 +778,13 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     }
 
     public void addColumn(String name, RelDataType type) {
+      addColumn(name, type, false);
+    }
+
+    public void addColumn(String name, RelDataType type, boolean isKey) {
+      if (isKey) {
+        keyList.add(columnList.size());
+      }
       columnList.add(Pair.of(name, type));
     }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/106dcc0f/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
index 4a35c52..df2bd9c 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -644,7 +644,7 @@ public class RelMetadataTest extends SqlToRelTestBase {
 
   @Test public void testRowCountAggregateGroupingSets() {
     final String sql = "select deptno from emp\n"
-        + "group by grouping sets ((deptno), (empno, deptno))";
+        + "group by grouping sets ((deptno), (ename, deptno))";
     checkRowCount(sql, 2.8D); // EMP_SIZE / 10 * 2
     checkMaxRowCount(sql, Double.POSITIVE_INFINITY);
   }
@@ -816,7 +816,7 @@ public class RelMetadataTest extends SqlToRelTestBase {
    * "RelMdColumnUniqueness uses ImmutableBitSet.Builder twice, gets
    * NullPointerException"</a>. */
   @Test public void testJoinUniqueKeys() {
-    RelNode rel = convertSql("select * from emp join dept using (deptno)");
+    RelNode rel = convertSql("select * from emp join bonus using (ename)");
     final RelMetadataQuery mq = RelMetadataQuery.instance();
     Set<ImmutableBitSet> result = mq.getUniqueKeys(rel);
     assertThat(result.isEmpty(), is(true));

http://git-wip-us.apache.org/repos/asf/calcite/blob/106dcc0f/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 ebc0e99..018a857 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2007,11 +2007,11 @@ public class RelOptRulesTest extends RelOptTestBase {
     HepProgram program = HepProgram.builder()
         .addRuleInstance(AggregateFilterTransposeRule.INSTANCE)
         .build();
-    final String sql = "select empno, sal, deptno from ("
-        + "  select empno, sal, deptno"
+    final String sql = "select ename, sal, deptno from ("
+        + "  select ename, sal, deptno"
         + "  from emp"
         + "  where sal > 5000)"
-        + "group by empno, sal, deptno";
+        + "group by ename, sal, deptno";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2024,11 +2024,11 @@ public class RelOptRulesTest extends RelOptTestBase {
     HepProgram program = HepProgram.builder()
         .addRuleInstance(AggregateFilterTransposeRule.INSTANCE)
         .build();
-    final String sql = "select empno, sal, deptno from ("
-        + "  select empno, sal, deptno"
+    final String sql = "select ename, sal, deptno from ("
+        + "  select ename, sal, deptno"
         + "  from emp"
         + "  where sal > 5000)"
-        + "group by rollup(empno, sal, deptno)";
+        + "group by rollup(ename, sal, deptno)";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2425,10 +2425,10 @@ public class RelOptRulesTest extends RelOptTestBase {
     final HepProgram program = new HepProgramBuilder()
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
-    final String sql = "select e.empno,d.deptno\n"
+    final String sql = "select e.job,d.name\n"
         + "from (select * from sales.emp where empno = 10) as e\n"
-        + "join sales.dept as d on e.empno = d.deptno\n"
-        + "group by e.empno,d.deptno";
+        + "join sales.dept as d on e.job = d.name\n"
+        + "group by e.job,d.name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2439,11 +2439,11 @@ public class RelOptRulesTest extends RelOptTestBase {
     final HepProgram program = new HepProgramBuilder()
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
-    final String sql = "select e.empno,d.deptno\n"
+    final String sql = "select e.job,d.name\n"
         + "from (select * from sales.emp where empno = 10) as e\n"
-        + "join sales.dept as d on e.empno = d.deptno\n"
+        + "join sales.dept as d on e.job = d.name\n"
         + "and e.deptno + e.empno = d.deptno + 5\n"
-        + "group by e.empno,d.deptno";
+        + "group by e.job,d.name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2469,10 +2469,10 @@ public class RelOptRulesTest extends RelOptTestBase {
     final HepProgram program = new HepProgramBuilder()
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
-    final String sql = "select e.empno,sum(sal)\n"
+    final String sql = "select e.job,sum(sal)\n"
         + "from (select * from sales.emp where empno = 10) as e\n"
-        + "join sales.dept as d on e.empno = d.deptno\n"
-        + "group by e.empno,d.deptno";
+        + "join sales.dept as d on e.job = d.name\n"
+        + "group by e.job,d.name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2484,14 +2484,14 @@ public class RelOptRulesTest extends RelOptTestBase {
     final HepProgram program = new HepProgramBuilder()
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
-    final String sql = "select e.empno,\n"
+    final String sql = "select e.job,\n"
         + "  min(sal) as min_sal, min(e.deptno) as min_deptno,\n"
         + "  sum(sal) + 1 as sum_sal_plus, max(sal) as max_sal,\n"
         + "  sum(sal) as sum_sal_2, count(sal) as count_sal,\n"
         + "  count(mgr) as count_mgr\n"
         + "from sales.emp as e\n"
-        + "join sales.dept as d on e.empno = d.deptno\n"
-        + "group by e.empno,d.deptno";
+        + "join sales.dept as d on e.job = d.name\n"
+        + "group by e.job,d.name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2504,12 +2504,12 @@ public class RelOptRulesTest extends RelOptTestBase {
     final HepProgram program = new HepProgramBuilder()
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
-    final String sql = "select d.deptno,\n"
+    final String sql = "select d.name,\n"
         + "  sum(sal) as sum_sal, count(*) as c\n"
         + "from sales.emp as e\n"
-        + "join (select distinct deptno from sales.dept) as d\n"
-        + "  on e.empno = d.deptno\n"
-        + "group by d.deptno";
+        + "join (select distinct name from sales.dept) as d\n"
+        + "  on e.job = d.name\n"
+        + "group by d.name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2522,7 +2522,7 @@ public class RelOptRulesTest extends RelOptTestBase {
         .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
         .build();
     final String sql =
-        "select count(*) from sales.emp join sales.dept using (deptno)";
+        "select count(*) from sales.emp join sales.dept on job = name";
     checkPlanning(tester, preProgram, new HepPlanner(program), sql);
   }
 
@@ -2639,6 +2639,47 @@ public class RelOptRulesTest extends RelOptTestBase {
   }
 
   /** Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-1507">[CALCITE-1507]
+   * OFFSET cannot be pushed through a JOIN if the non-preserved side of outer
+   * join is not count-preserving</a>. */
+  @Test public void testSortJoinTranspose6() {
+    final HepProgram preProgram = new HepProgramBuilder()
+        .addRuleInstance(SortProjectTransposeRule.INSTANCE)
+        .build();
+    final HepProgram program = new HepProgramBuilder()
+        .addRuleInstance(SortJoinTransposeRule.INSTANCE)
+        .build();
+    // This one can be pushed down even if it has an OFFSET, since the dept
+    // table is count-preserving against the join condition.
+    final String sql = "select d.deptno, empno from sales.dept d\n"
+        + "right join sales.emp e using (deptno) limit 10 offset 2";
+    sql(sql)
+        .withPre(preProgram)
+        .with(program)
+        .check();
+  }
+
+  /** Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-1507">[CALCITE-1507]
+   * OFFSET cannot be pushed through a JOIN if the non-preserved side of outer
+   * join is not count-preserving</a>. */
+  @Test public void testSortJoinTranspose7() {
+    final HepProgram preProgram = new HepProgramBuilder()
+        .addRuleInstance(SortProjectTransposeRule.INSTANCE)
+        .build();
+    final HepProgram program = new HepProgramBuilder()
+        .addRuleInstance(SortJoinTransposeRule.INSTANCE)
+        .build();
+    // This one cannot be pushed down
+    final String sql = "select d.deptno, empno from sales.dept d\n"
+        + "left join sales.emp e using (deptno) order by d.deptno offset 1";
+    sql(sql)
+        .withPre(preProgram)
+        .with(program)
+        .checkUnchanged();
+  }
+
+  /** Test case for
    * <a href="https://issues.apache.org/jira/browse/CALCITE-1023">[CALCITE-1023]
    * Planner rule that removes Aggregate keys that are constant</a>. */
   @Test public void testAggregateConstantKeyRule() {

http://git-wip-us.apache.org/repos/asf/calcite/blob/106dcc0f/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 5313698..1acd8b0 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2517,13 +2517,13 @@ LogicalAggregate(group=[{0}], EXPR$1=[$SUM0($1)])
     </TestCase>
     <TestCase name="testPullFilterThroughAggregate">
         <Resource name="sql">
-            <![CDATA[select empno, sal, deptno from (  select empno, sal, deptno  from
emp  where sal > 5000)group by empno, sal, deptno]]>
+            <![CDATA[select ename, sal, deptno from (  select ename, sal, deptno  from
emp  where sal > 5000)group by ename, sal, deptno]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
 LogicalAggregate(group=[{0, 1, 2}])
   LogicalFilter(condition=[>($1, 5000)])
-    LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
+    LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
@@ -2531,31 +2531,31 @@ LogicalAggregate(group=[{0, 1, 2}])
             <![CDATA[
 LogicalFilter(condition=[>($1, 5000)])
   LogicalAggregate(group=[{0, 1, 2}])
-    LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
+    LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
     <TestCase name="testPullFilterThroughAggregateGroupingSets">
         <Resource name="sql">
-            <![CDATA[select empno, sal, deptno from (  select empno, sal, deptno  from
emp  where sal > 5000)group by rollup(empno, sal, deptno)]]>
+            <![CDATA[select ename, sal, deptno from (  select ename, sal, deptno  from
emp  where sal > 5000)group by rollup(ename, sal, deptno)]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
-LogicalProject(EMPNO=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null,
$2)])
+LogicalProject(ENAME=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null,
$2)])
   LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]], indicator=[true])
     LogicalFilter(condition=[>($1, 5000)])
-      LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
+      LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
-LogicalProject(EMPNO=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null,
$2)])
+LogicalProject(ENAME=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null,
$2)])
   LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]], indicator=[true])
     LogicalFilter(condition=[>($1, 5000)])
       LogicalAggregate(group=[{0, 1, 2}])
-        LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
+        LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
@@ -4521,15 +4521,15 @@ LogicalProject(SUM1=[SUM($7) OVER (PARTITION BY $7 ORDER BY $5 RANGE
BETWEEN UNB
     </TestCase>
     <TestCase name="testPushAggregateThroughJoin1">
         <Resource name="sql">
-            <![CDATA[select e.empno,d.deptno
+            <![CDATA[select e.job,d.name
 from (select * from sales.emp where empno = 10) as e
-join sales.dept as d on e.empno = d.deptno
-group by e.empno,d.deptno]]>
+join sales.dept as d on e.job = d.name
+group by e.job,d.name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
-LogicalAggregate(group=[{0, 9}])
-  LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+LogicalAggregate(group=[{2, 10}])
+  LogicalJoin(condition=[=($2, $10)], joinType=[inner])
     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6],
DEPTNO=[$7], SLACKER=[$8])
       LogicalFilter(condition=[=($0, 10)])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -4539,27 +4539,27 @@ LogicalAggregate(group=[{0, 9}])
         <Resource name="planAfter">
             <![CDATA[
 LogicalJoin(condition=[=($0, $1)], joinType=[inner])
-  LogicalAggregate(group=[{0}])
+  LogicalAggregate(group=[{2}])
     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6],
DEPTNO=[$7], SLACKER=[$8])
       LogicalFilter(condition=[=($0, 10)])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-  LogicalAggregate(group=[{0}])
+  LogicalAggregate(group=[{1}])
     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
     <TestCase name="testPushAggregateThroughJoin2">
         <Resource name="sql">
-            <![CDATA[select e.empno,d.deptno
+            <![CDATA[select e.job,d.name
 from (select * from sales.emp where empno = 10) as e
-join sales.dept as d on e.empno = d.deptno
+join sales.dept as d on e.job = d.name
 and e.deptno + e.empno = d.deptno + 5
-group by e.empno,d.deptno]]>
+group by e.job,d.name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
-LogicalAggregate(group=[{0, 10}])
-  LogicalJoin(condition=[AND(=($0, $10), =($9, $12))], joinType=[inner])
+LogicalAggregate(group=[{2, 11}])
+  LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[inner])
     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6],
DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
       LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
         LogicalFilter(condition=[=($0, 10)])
@@ -4572,12 +4572,12 @@ LogicalAggregate(group=[{0, 10}])
             <![CDATA[
 LogicalAggregate(group=[{0, 2}])
   LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
-    LogicalAggregate(group=[{0, 9}])
+    LogicalAggregate(group=[{2, 9}])
       LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
         LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
           LogicalFilter(condition=[=($0, 10)])
             LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-    LogicalAggregate(group=[{0, 2}])
+    LogicalAggregate(group=[{1, 2}])
       LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
         LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
@@ -4613,16 +4613,16 @@ LogicalAggregate(group=[{0, 9}])
     </TestCase>
     <TestCase name="testPushAggregateSumThroughJoin">
         <Resource name="sql">
-            <![CDATA[select e.empno,sum(sal)
+            <![CDATA[select e.job,sum(sal)
 from (select * from sales.emp where empno = 10) as e
-join sales.dept as d on e.empno = d.deptno
-group by e.empno,d.deptno]]>
+join sales.dept as d on e.job = d.name
+group by e.job,d.name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
-LogicalProject(EMPNO=[$0], EXPR$1=[$2])
-  LogicalAggregate(group=[{0, 9}], EXPR$1=[SUM($5)])
-    LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+LogicalProject(JOB=[$0], EXPR$1=[$2])
+  LogicalAggregate(group=[{2, 10}], EXPR$1=[SUM($5)])
+    LogicalJoin(condition=[=($2, $10)], joinType=[inner])
       LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
         LogicalFilter(condition=[=($0, 10)])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -4631,15 +4631,15 @@ LogicalProject(EMPNO=[$0], EXPR$1=[$2])
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
-LogicalProject(EMPNO=[$0], EXPR$1=[$2])
-  LogicalProject(EMPNO=[$0], DEPTNO=[$2], $f4=[$4])
-    LogicalProject(EMPNO=[$0], EXPR$1=[$1], DEPTNO=[$2], $f1=[$3], $f4=[CAST(*($1, $3)):INTEGER
NOT NULL])
+LogicalProject(JOB=[$0], EXPR$1=[$2])
+  LogicalProject(JOB=[$0], NAME=[$2], $f4=[$4])
+    LogicalProject(JOB=[$0], EXPR$1=[$1], NAME=[$2], $f1=[$3], $f4=[CAST(*($1, $3)):INTEGER
NOT NULL])
       LogicalJoin(condition=[=($0, $2)], joinType=[inner])
-        LogicalAggregate(group=[{0}], EXPR$1=[SUM($5)])
+        LogicalAggregate(group=[{2}], EXPR$1=[SUM($5)])
           LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
             LogicalFilter(condition=[=($0, 10)])
               LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalAggregate(group=[{0}], agg#0=[COUNT()])
+        LogicalAggregate(group=[{1}], agg#0=[COUNT()])
           LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
@@ -5046,45 +5046,45 @@ LogicalProject(DEPTNO=[$0])
     </TestCase>
     <TestCase name="testPushAggregateFunctionsThroughJoin">
         <Resource name="sql">
-            <![CDATA[select e.empno,
+            <![CDATA[select e.job,
   min(sal) as min_sal, min(e.deptno) as min_deptno,
   sum(sal) + 1 as sum_sal_plus, max(sal) as max_sal,
   sum(sal) as sum_sal_2, count(sal) as count_sal,
   count(mgr) as count_mgr
 from sales.emp as e
-join sales.dept as d on e.empno = d.deptno
-group by e.empno,d.deptno]]>
+join sales.dept as d on e.job = d.name
+group by e.job,d.name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
-LogicalProject(EMPNO=[$0], MIN_SAL=[$2], MIN_DEPTNO=[$3], SUM_SAL_PLUS=[+($4, 1)], MAX_SAL=[$5],
SUM_SAL_2=[$4], COUNT_SAL=[$6], COUNT_MGR=[$7])
-  LogicalAggregate(group=[{0, 9}], MIN_SAL=[MIN($5)], MIN_DEPTNO=[MIN($7)], SUM_SAL_2=[SUM($5)],
MAX_SAL=[MAX($5)], COUNT_SAL=[COUNT()], COUNT_MGR=[COUNT($3)])
-    LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+LogicalProject(JOB=[$0], MIN_SAL=[$2], MIN_DEPTNO=[$3], SUM_SAL_PLUS=[+($4, 1)], MAX_SAL=[$5],
SUM_SAL_2=[$4], COUNT_SAL=[$6], COUNT_MGR=[$7])
+  LogicalAggregate(group=[{2, 10}], MIN_SAL=[MIN($5)], MIN_DEPTNO=[MIN($7)], SUM_SAL_2=[SUM($5)],
MAX_SAL=[MAX($5)], COUNT_SAL=[COUNT()], COUNT_MGR=[COUNT($3)])
+    LogicalJoin(condition=[=($2, $10)], joinType=[inner])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
       LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
-LogicalProject(EMPNO=[$0], MIN_SAL=[$2], MIN_DEPTNO=[$3], SUM_SAL_PLUS=[+($4, 1)], MAX_SAL=[$5],
SUM_SAL_2=[$4], COUNT_SAL=[$6], COUNT_MGR=[$7])
-  LogicalProject(EMPNO=[$0], DEPTNO=[$7], MIN_SAL=[$1], MIN_DEPTNO=[$2], $f9=[$9], MAX_SAL=[$4],
$f10=[$10], $f11=[$11])
-    LogicalProject(EMPNO=[$0], MIN_SAL=[$1], MIN_DEPTNO=[$2], SUM_SAL_2=[$3], MAX_SAL=[$4],
COUNT_SAL=[$5], COUNT_MGR=[$6], DEPTNO=[$7], $f1=[$8], $f9=[CAST(*($3, $8)):INTEGER NOT NULL],
$f10=[*($5, $8)], $f11=[*($6, $8)])
+LogicalProject(JOB=[$0], MIN_SAL=[$2], MIN_DEPTNO=[$3], SUM_SAL_PLUS=[+($4, 1)], MAX_SAL=[$5],
SUM_SAL_2=[$4], COUNT_SAL=[$6], COUNT_MGR=[$7])
+  LogicalProject(JOB=[$0], NAME=[$7], MIN_SAL=[$1], MIN_DEPTNO=[$2], $f9=[$9], MAX_SAL=[$4],
$f10=[$10], $f11=[$11])
+    LogicalProject(JOB=[$0], MIN_SAL=[$1], MIN_DEPTNO=[$2], SUM_SAL_2=[$3], MAX_SAL=[$4],
COUNT_SAL=[$5], COUNT_MGR=[$6], NAME=[$7], $f1=[$8], $f9=[CAST(*($3, $8)):INTEGER NOT NULL],
$f10=[*($5, $8)], $f11=[*($6, $8)])
       LogicalJoin(condition=[=($0, $7)], joinType=[inner])
-        LogicalAggregate(group=[{0}], MIN_SAL=[MIN($5)], MIN_DEPTNO=[MIN($7)], SUM_SAL_2=[SUM($5)],
MAX_SAL=[MAX($5)], COUNT_SAL=[COUNT()], COUNT_MGR=[COUNT($3)])
+        LogicalAggregate(group=[{2}], MIN_SAL=[MIN($5)], MIN_DEPTNO=[MIN($7)], SUM_SAL_2=[SUM($5)],
MAX_SAL=[MAX($5)], COUNT_SAL=[COUNT()], COUNT_MGR=[COUNT($3)])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalAggregate(group=[{0}], agg#0=[COUNT()])
+        LogicalAggregate(group=[{1}], agg#0=[COUNT()])
           LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
     <TestCase name="testPushAggregateSumNoGroup">
         <Resource name="sql">
-            <![CDATA[select count(*) from sales.emp join sales.dept using (deptno)]]>
+            <![CDATA[select count(*) from sales.emp join sales.dept on job = name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
 LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
-  LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+  LogicalJoin(condition=[=($2, $10)], joinType=[inner])
     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
@@ -5092,40 +5092,40 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
         <Resource name="planAfter">
             <![CDATA[
 LogicalAggregate(group=[{}], EXPR$0=[$SUM0($4)])
-  LogicalProject(DEPTNO=[$0], EXPR$0=[$1], DEPTNO0=[$2], EXPR$00=[$3], $f4=[*($1, $3)])
+  LogicalProject(JOB=[$0], EXPR$0=[$1], NAME=[$2], EXPR$00=[$3], $f4=[*($1, $3)])
     LogicalJoin(condition=[=($0, $2)], joinType=[inner])
-      LogicalAggregate(group=[{7}], EXPR$0=[COUNT()])
+      LogicalAggregate(group=[{2}], EXPR$0=[COUNT()])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-      LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+      LogicalAggregate(group=[{1}], EXPR$0=[COUNT()])
         LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
     <TestCase name="testPushAggregateThroughJoinDistinct">
         <Resource name="sql">
-            <![CDATA[select d.deptno,
+            <![CDATA[select d.name,
   sum(sal) as sum_sal, count(*) as c
 from sales.emp as e
-join (select distinct deptno from sales.dept) as d
-  on e.empno = d.deptno
-group by d.deptno]]>
+join (select distinct name from sales.dept) as d
+  on e.job = d.name
+group by d.name]]>
         </Resource>
         <Resource name="planBefore">
             <![CDATA[
 LogicalAggregate(group=[{9}], SUM_SAL=[SUM($5)], C=[COUNT()])
-  LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+  LogicalJoin(condition=[=($2, $9)], joinType=[inner])
     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-    LogicalAggregate(group=[{0}])
+    LogicalAggregate(group=[{1}])
       LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
-LogicalProject(DEPTNO=[$3], SUM_SAL=[$1], C=[$2])
+LogicalProject(NAME=[$3], SUM_SAL=[$1], C=[$2])
   LogicalJoin(condition=[=($0, $3)], joinType=[inner])
-    LogicalAggregate(group=[{0}], SUM_SAL=[SUM($5)], C=[COUNT()])
+    LogicalAggregate(group=[{2}], SUM_SAL=[SUM($5)], C=[COUNT()])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-    LogicalAggregate(group=[{0}])
+    LogicalAggregate(group=[{1}])
       LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
@@ -5385,6 +5385,55 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testSortJoinTranspose6">
+        <Resource name="sql">
+            <![CDATA[select d.deptno, empno from sales.dept d
+right join sales.emp e using (deptno) limit 10 offset 2]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], EMPNO=[$2])
+  LogicalSort(offset=[2], fetch=[10])
+    LogicalJoin(condition=[=($0, $9)], joinType=[right])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], EMPNO=[$2])
+  LogicalSort(offset=[2], fetch=[10])
+    LogicalJoin(condition=[=($0, $9)], joinType=[right])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalSort(offset=[2], fetch=[10])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testSortJoinTranspose7">
+        <Resource name="sql">
+            <![CDATA[select d.deptno, empno from sales.dept d
+left join sales.emp e using (deptno) order by d.deptno offset 1]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], EMPNO=[$2])
+  LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
+    LogicalJoin(condition=[=($0, $9)], joinType=[left])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], EMPNO=[$2])
+  LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
+    LogicalJoin(condition=[=($0, $9)], joinType=[left])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testSortUnionTranspose">
         <Resource name="sql">
             <![CDATA[select a.name from dept a


Mime
View raw message