You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ma...@apache.org on 2016/12/07 06:07:28 UTC
calcite git commit: [CALCITE-1507] OFFSET cannot be pushed through a
JOIN if the non-preserved side of outer join is not count-preserving
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 <ma...@gmail.com>
Authored: Tue Dec 6 22:07:15 2016 -0800
Committer: maryannxue <ma...@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