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 2019/10/23 22:32:38 UTC
[calcite] 03/04: [CALCITE-3428] Refine RelMdColumnUniqueness for
Filter by considering constant columns (Jin Xing)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit f9103e6079f75e3d6b3419d01690447ff0a14265
Author: jinxing <ji...@gmail.com>
AuthorDate: Fri Oct 18 19:10:43 2019 +0800
[CALCITE-3428] Refine RelMdColumnUniqueness for Filter by considering constant columns (Jin Xing)
With this rule, we can simplify
SELECT DISTINCT x
FROM (SELECT DISTINCT x, y FROM t)
WHERE y = 10
to
SELECT x
FROM (SELECT DISTINCT x, y FROM t)
WHERE y = 10
Close apache/calcite#1520
---
.../rel/metadata/RelMdColumnUniqueness.java | 37 ++++++
.../org/apache/calcite/test/RelMetadataTest.java | 141 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 58 ++++++---
.../org/apache/calcite/test/RelOptRulesTest.xml | 136 ++++++++++++--------
core/src/test/resources/sql/sub-query.iq | 9 +-
5 files changed, 307 insertions(+), 74 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdColumnUniqueness.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdColumnUniqueness.java
index dce0564..182732c 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdColumnUniqueness.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdColumnUniqueness.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.rel.metadata;
+import org.apache.calcite.plan.RelOptPredicateList;
import org.apache.calcite.plan.hep.HepRelVertex;
import org.apache.calcite.plan.volcano.RelSubset;
import org.apache.calcite.rel.RelNode;
@@ -79,6 +80,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Filter rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
return mq.areColumnsUnique(rel.getInput(), columns, ignoreNulls);
}
@@ -106,6 +108,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(SetOp rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
// If not ALL then the rows are distinct.
// Therefore the set of all columns is a key.
return !rel.all
@@ -114,6 +117,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Intersect rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
if (areColumnsUnique((SetOp) rel, mq, columns, ignoreNulls)) {
return true;
}
@@ -128,6 +132,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Minus rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
if (areColumnsUnique((SetOp) rel, mq, columns, ignoreNulls)) {
return true;
}
@@ -136,16 +141,19 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Sort rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
return mq.areColumnsUnique(rel.getInput(), columns, ignoreNulls);
}
public Boolean areColumnsUnique(Exchange rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
return mq.areColumnsUnique(rel.getInput(), columns, ignoreNulls);
}
public Boolean areColumnsUnique(Correlate rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
switch (rel.getJoinType()) {
case ANTI:
case SEMI:
@@ -182,6 +190,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Project rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
// LogicalProject maps a set of rows to a different set;
// Without knowledge of the mapping function(whether it
// preserves uniqueness), it is only safe to derive uniqueness
@@ -239,6 +248,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Join rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
if (columns.cardinality() == 0) {
return false;
}
@@ -314,6 +324,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Aggregate rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
// group by keys form a unique key
ImmutableBitSet groupKey = ImmutableBitSet.range(rel.getGroupCount());
return columns.contains(groupKey);
@@ -321,6 +332,7 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Values rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
if (rel.tuples.size() < 2) {
return true;
}
@@ -343,16 +355,19 @@ public class RelMdColumnUniqueness
public Boolean areColumnsUnique(Converter rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
return mq.areColumnsUnique(rel.getInput(), columns, ignoreNulls);
}
public Boolean areColumnsUnique(HepRelVertex rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
return mq.areColumnsUnique(rel.getCurrentRel(), columns, ignoreNulls);
}
public Boolean areColumnsUnique(RelSubset rel, RelMetadataQuery mq,
ImmutableBitSet columns, boolean ignoreNulls) {
+ columns = decorateWithConstantColumnsFromPredicates(columns, rel, mq);
int nullCount = 0;
for (RelNode rel2 : rel.getRels()) {
if (rel2 instanceof Aggregate
@@ -413,6 +428,28 @@ public class RelMdColumnUniqueness
}
return Pair.of(leftBuilder.build(), rightBuilder.build());
}
+
+ /**
+ * Deduce constant columns from predicates of rel and return the union
+ * bitsets of checkingColumns and the constant columns.
+ */
+ private static ImmutableBitSet decorateWithConstantColumnsFromPredicates(
+ ImmutableBitSet checkingColumns, RelNode rel, RelMetadataQuery mq) {
+ final RelOptPredicateList predicates = mq.getPulledUpPredicates(rel);
+ if (predicates != null) {
+ final Set<Integer> constantIndexes = new HashSet();
+ predicates.constantMap.keySet().forEach(rex -> {
+ if (rex instanceof RexInputRef) {
+ constantIndexes.add(((RexInputRef) rex).getIndex());
+ }
+ });
+ if (!constantIndexes.isEmpty()) {
+ return checkingColumns.union(ImmutableBitSet.of(constantIndexes));
+ }
+ }
+ // If no constant columns deduced, return the original "checkingColumns".
+ return checkingColumns;
+ }
}
// End RelMdColumnUniqueness.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 a5546e7..785ae17 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -72,6 +72,7 @@ import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexCorrelVariable;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
@@ -87,6 +88,7 @@ import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.BuiltInMethod;
+import org.apache.calcite.util.Holder;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
@@ -944,6 +946,145 @@ public class RelMetadataTest extends SqlToRelTestBase {
assertThat(areGroupByKeysUnique, is(false));
}
+ @Test public void testColumnUniquenessForFilterWithConstantColumns() {
+ checkColumnUniquenessForFilterWithConstantColumns(""
+ + "select *\n"
+ + "from (select distinct deptno, sal from emp)\n"
+ + "where sal=1000");
+ checkColumnUniquenessForFilterWithConstantColumns(""
+ + "select *\n"
+ + "from (select distinct deptno, sal from emp)\n"
+ + "where 1000=sal");
+ }
+
+ private void checkColumnUniquenessForFilterWithConstantColumns(String sql) {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(1)), is(false));
+ }
+
+ @Test public void testColumnUniquenessForUnionWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select deptno, sal from emp where sal=1000\n"
+ + "union\n"
+ + "select deptno, sal from emp where sal=1000\n";
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForIntersectWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select deptno, sal\n"
+ + "from (select distinct deptno, sal from emp)\n"
+ + "where sal=1000\n"
+ + "intersect all\n"
+ + "select deptno, sal from emp\n";
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForMinusWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select deptno, sal\n"
+ + "from (select distinct deptno, sal from emp)\n"
+ + "where sal=1000\n"
+ + "except all\n"
+ + "select deptno, sal from emp\n";
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForSortWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select *\n"
+ + "from (select distinct deptno, sal from emp)\n"
+ + "where sal=1000\n"
+ + "order by deptno";
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForJoinWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select *\n"
+ + "from (select distinct deptno, sal from emp) A\n"
+ + "join (select distinct deptno, sal from emp) B\n"
+ + "on A.deptno=B.deptno and A.sal=1000 and B.sal=1000";
+ final RelNode rel = convertSql(sql);
+ assertThat(rel.getRowType().getFieldNames().toString(),
+ is("[DEPTNO, SAL, DEPTNO0, SAL0]"));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 2)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1, 2)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 2, 3)), is(true));
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(0, 1)), is(false));
+ }
+
+ @Test public void testColumnUniquenessForAggregateWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final String sql = ""
+ + "select deptno, ename, sum(sal)\n"
+ + "from emp\n"
+ + "where deptno=1010\n"
+ + "group by deptno, ename";
+ final RelNode rel = convertSql(sql);
+ assertThat(mq.areColumnsUnique(rel, ImmutableBitSet.of(1)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForExchangeWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final FrameworkConfig config = RelBuilderTest.config().build();
+ final RelBuilder builder = RelBuilder.create(config);
+ RelNode exchange = builder.scan("EMP")
+ .project(builder.field("DEPTNO"), builder.field("SAL"))
+ .distinct()
+ .filter(builder.equals(builder.field("SAL"), builder.literal(1)))
+ .exchange(RelDistributions.hash(ImmutableList.of(1)))
+ .build();
+ assertThat(mq.areColumnsUnique(exchange, ImmutableBitSet.of(0)), is(true));
+ }
+
+ @Test public void testColumnUniquenessForCorrelateWithConstantColumns() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ final FrameworkConfig config = RelBuilderTest.config().build();
+ final RelBuilder builder = RelBuilder.create(config);
+ RelNode rel0 = builder.scan("EMP")
+ .project(builder.field("DEPTNO"), builder.field("SAL"))
+ .distinct()
+ .filter(builder.equals(builder.field("SAL"), builder.literal(1)))
+ .build();
+ final Holder<RexCorrelVariable> v = Holder.of(null);
+ final RelNode rel1 = builder.scan("EMP")
+ .variable(v)
+ .project(builder.field("DEPTNO"), builder.field("SAL"))
+ .filter(
+ builder.equals(builder.field(0), builder.field(v.get(), "DEPTNO")))
+ .build();
+ final RelNode correl = builder.push(rel0)
+ .variable(v)
+ .push(rel1)
+ .correlate(JoinRelType.SEMI, v.get().id, builder.field(2, 0, "DEPTNO"))
+ .build();
+ assertThat(mq.areColumnsUnique(correl, ImmutableBitSet.of(0)), is(true));
+ }
+
@Test public void testGroupBy() {
RelNode rel = convertSql("select deptno, count(*), sum(sal) from emp\n"
+ "group by deptno");
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 51b671b..e5ccb6e 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3986,7 +3986,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job,d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
sql(sql).withPre(preProgram)
@@ -4001,7 +4001,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.ename\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.ename";
sql(sql).withPre(preProgram)
@@ -4016,7 +4016,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select d.ename\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.emp as d on e.job = d.job\n"
+ "group by d.ename";
sql(sql).withPre(preProgram)
@@ -4031,7 +4031,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.ename, d.mgr\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.emp as d on e.job = d.job\n"
+ "group by e.ename,d.mgr";
sql(sql).withPre(preProgram)
@@ -4046,7 +4046,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
sql(sql).withPre(preProgram)
@@ -4061,7 +4061,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by d.name";
sql(sql).withPre(preProgram)
@@ -4076,7 +4076,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job,d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
sql(sql).withPre(preProgram)
@@ -4091,7 +4091,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job";
@@ -4107,7 +4107,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by d.name";
@@ -4123,7 +4123,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job, d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "left outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job, d.name";
@@ -4169,7 +4169,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "right outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
sql(sql).withPre(preProgram)
@@ -4184,7 +4184,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "full outer join sales.dept as d on e.job = d.name\n"
+ "group by e.job";
sql(sql).withPre(preProgram)
@@ -4214,7 +4214,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.ename, d.mgr\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "full outer join sales.emp as d on e.job = d.job\n"
+ "group by e.ename,d.mgr";
sql(sql).withPre(preProgram)
@@ -4229,7 +4229,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "full outer join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job";
@@ -4243,7 +4243,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job,d.name\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "and e.deptno + e.empno = d.deptno + 5\n"
+ "group by e.job,d.name";
@@ -4385,7 +4385,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select e.job,sum(sal)\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "join sales.dept as d on e.job = d.name\n"
+ "group by e.job,d.name";
sql(sql).withPre(preProgram)
@@ -4402,7 +4402,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
.build();
final String sql = "select sum(sal)\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "join sales.dept as d on e.job = d.name";
sql(sql).withPre(preProgram)
.withRule(AggregateJoinTransposeRule.EXTENDED)
@@ -4425,7 +4425,7 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
.build();
final String sql = "select sum(sal)\n"
- + "from (select * from sales.emp where empno = 10) as e\n"
+ + "from (select * from sales.emp where ename = 'A') as e\n"
+ "join sales.dept as d on e.job = d.name";
sql(sql).withPre(preProgram)
.with(program)
@@ -4778,6 +4778,28 @@ public class RelOptRulesTest extends RelOptTestBase {
.checkUnchanged();
}
+ /**
+ * The top Aggregate should be removed -- given "deptno=100",
+ * the input of top Aggregate must be already distinct by "mgr"
+ */
+ @Test public void testAggregateRemove7() {
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateRemoveRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final String sql = ""
+ + "select mgr, sum(sum_sal)\n"
+ + "from\n"
+ + "(select mgr, deptno, sum(sal) sum_sal\n"
+ + " from sales.emp\n"
+ + " group by mgr, deptno)\n"
+ + "where deptno=100\n"
+ + "group by mgr";
+ sql(sql)
+ .with(program)
+ .check();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2712">[CALCITE-2712]
* Should remove the left join since the aggregate has no call and
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 d4f5ef5..6f7db5b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5730,7 +5730,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testPushAggregateThroughOuterJoin1">
<Resource name="sql">
<![CDATA[select e.ename
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.dept as d on e.job = d.name
group by e.ename]]>
</Resource>
@@ -5739,7 +5739,7 @@ group by e.ename]]>
LogicalAggregate(group=[{1}])
LogicalJoin(condition=[=($2, $10)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -5750,7 +5750,7 @@ LogicalAggregate(group=[{0}])
LogicalJoin(condition=[=($1, $2)], joinType=[left])
LogicalAggregate(group=[{1, 2}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5797,7 +5797,7 @@ LogicalAggregate(group=[{0, 9}])
<TestCase name="testPushAggregateThroughOuterJoin12">
<Resource name="sql">
<![CDATA[select e.job
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
right outer join sales.dept as d on e.job = d.name
group by e.job]]>
</Resource>
@@ -5806,7 +5806,7 @@ group by e.job]]>
LogicalAggregate(group=[{2}])
LogicalJoin(condition=[=($2, $10)], joinType=[right])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -5817,7 +5817,7 @@ LogicalAggregate(group=[{0}])
LogicalJoin(condition=[=($0, $1)], joinType=[right])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5827,7 +5827,7 @@ LogicalAggregate(group=[{0}])
<TestCase name="testPushAggregateThroughOuterJoin13">
<Resource name="sql">
<![CDATA[select e.job
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
full outer join sales.dept as d on e.job = d.name
group by e.job]]>
</Resource>
@@ -5836,7 +5836,7 @@ group by e.job]]>
LogicalAggregate(group=[{2}])
LogicalJoin(condition=[=($2, $10)], joinType=[full])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -5847,7 +5847,7 @@ LogicalAggregate(group=[{0}])
LogicalJoin(condition=[=($0, $1)], joinType=[full])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5887,7 +5887,7 @@ LogicalProject(MGR=[$1], MGR0=[$0])
<TestCase name="testPushAggregateThroughOuterJoin15">
<Resource name="sql">
<![CDATA[select e.ename, d.mgr
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
full outer join sales.emp as d on e.job = d.job
group by e.ename,d.mgr]]>
</Resource>
@@ -5896,7 +5896,7 @@ group by e.ename,d.mgr]]>
LogicalAggregate(group=[{1, 12}])
LogicalJoin(condition=[=($2, $11)], joinType=[full])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -5907,7 +5907,7 @@ LogicalAggregate(group=[{0, 3}])
LogicalJoin(condition=[=($1, $2)], joinType=[full])
LogicalAggregate(group=[{1, 2}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{2, 3}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -5917,7 +5917,7 @@ LogicalAggregate(group=[{0, 3}])
<TestCase name="testPushAggregateThroughOuterJoin16">
<Resource name="sql">
<![CDATA[select e.job
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
full outer join sales.dept as d on e.job = d.name
and e.deptno + e.empno = d.deptno + 5
group by e.job]]>
@@ -5927,7 +5927,7 @@ group by e.job]]>
LogicalAggregate(group=[{2}])
LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[full])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5939,7 +5939,7 @@ LogicalAggregate(group=[{0}])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[full])
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
@@ -5950,7 +5950,7 @@ LogicalAggregate(group=[{0}])
<TestCase name="testPushAggregateThroughOuterJoin2">
<Resource name="sql">
<![CDATA[select d.ename
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.emp as d on e.job = d.job
group by d.ename]]>
</Resource>
@@ -5959,7 +5959,7 @@ group by d.ename]]>
LogicalAggregate(group=[{10}])
LogicalJoin(condition=[=($2, $11)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -5970,7 +5970,7 @@ LogicalAggregate(group=[{1}])
LogicalJoin(condition=[=($0, $2)], joinType=[left])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -5980,7 +5980,7 @@ LogicalAggregate(group=[{1}])
<TestCase name="testPushAggregateThroughOuterJoin3">
<Resource name="sql">
<![CDATA[select e.ename, d.mgr
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.emp as d on e.job = d.job
group by e.ename,d.mgr]]>
</Resource>
@@ -5989,7 +5989,7 @@ group by e.ename,d.mgr]]>
LogicalAggregate(group=[{1, 12}])
LogicalJoin(condition=[=($2, $11)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -6000,7 +6000,7 @@ LogicalAggregate(group=[{0, 3}])
LogicalJoin(condition=[=($1, $2)], joinType=[left])
LogicalAggregate(group=[{1, 2}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{2, 3}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -6010,7 +6010,7 @@ LogicalAggregate(group=[{0, 3}])
<TestCase name="testPushAggregateThroughOuterJoin4">
<Resource name="sql">
<![CDATA[select e.job
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.dept as d on e.job = d.name
group by e.job]]>
</Resource>
@@ -6019,7 +6019,7 @@ group by e.job]]>
LogicalAggregate(group=[{2}])
LogicalJoin(condition=[=($2, $10)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -6030,7 +6030,7 @@ LogicalProject(JOB=[$0])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6040,7 +6040,7 @@ LogicalProject(JOB=[$0])
<TestCase name="testPushAggregateThroughOuterJoin5">
<Resource name="sql">
<![CDATA[select d.name
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.dept as d on e.job = d.name
group by d.name]]>
</Resource>
@@ -6049,7 +6049,7 @@ group by d.name]]>
LogicalAggregate(group=[{10}])
LogicalJoin(condition=[=($2, $10)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -6060,7 +6060,7 @@ LogicalAggregate(group=[{1}])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6079,7 +6079,7 @@ group by e.job,d.name]]>
LogicalAggregate(group=[{2, 10}])
LogicalJoin(condition=[=($2, $10)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -6089,7 +6089,7 @@ LogicalAggregate(group=[{2, 10}])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6099,7 +6099,7 @@ LogicalJoin(condition=[=($0, $1)], joinType=[left])
<TestCase name="testPushAggregateThroughOuterJoin7">
<Resource name="sql">
<![CDATA[select e.job
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.dept as d on e.job = d.name
and e.deptno + e.empno = d.deptno + 5
group by e.job]]>
@@ -6109,7 +6109,7 @@ group by e.job]]>
LogicalAggregate(group=[{2}])
LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6121,7 +6121,7 @@ LogicalAggregate(group=[{0}])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
@@ -6142,7 +6142,7 @@ group by d.name]]>
LogicalAggregate(group=[{11}])
LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6154,7 +6154,7 @@ LogicalAggregate(group=[{2}])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
@@ -6165,7 +6165,7 @@ LogicalAggregate(group=[{2}])
<TestCase name="testPushAggregateThroughOuterJoin9">
<Resource name="sql">
<![CDATA[select e.job, d.name
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
left outer join sales.dept as d on e.job = d.name
and e.deptno + e.empno = d.deptno + 5
group by e.job, d.name]]>
@@ -6175,7 +6175,7 @@ group by e.job, d.name]]>
LogicalAggregate(group=[{2, 11}])
LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -6187,7 +6187,7 @@ LogicalAggregate(group=[{0, 2}])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
@@ -7325,7 +7325,7 @@ LogicalProject(SUM1=[SUM($7) OVER (PARTITION BY $7 ORDER BY $5 RANGE BETWEEN UNB
<TestCase name="testPushAggregateThroughJoin1">
<Resource name="sql">
<![CDATA[select e.job,d.name
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
join sales.dept as d on e.job = d.name
group by e.job,d.name]]>
</Resource>
@@ -7334,7 +7334,7 @@ group by e.job,d.name]]>
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -7344,7 +7344,7 @@ LogicalAggregate(group=[{2, 10}])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -7354,7 +7354,7 @@ LogicalJoin(condition=[=($0, $1)], joinType=[inner])
<TestCase name="testPushAggregateThroughJoin2">
<Resource name="sql">
<![CDATA[select e.job,d.name
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
join sales.dept as d on e.job = d.name
and e.deptno + e.empno = d.deptno + 5
group by e.job,d.name]]>
@@ -7364,7 +7364,7 @@ group by e.job,d.name]]>
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -7376,7 +7376,7 @@ LogicalAggregate(group=[{0, 2}])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
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)])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1, 2}])
LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
@@ -7528,7 +7528,7 @@ LogicalProject(JOB=[$0], MGR0=[$2], DEPTNO=[$1], HIREDATE1=[$3], COMM1=[$4])
<TestCase name="testPushAggregateSumThroughJoin">
<Resource name="sql">
<![CDATA[select e.job,sum(sal)
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
join sales.dept as d on e.job = d.name
group by e.job,d.name]]>
</Resource>
@@ -7538,7 +7538,7 @@ 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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -7550,7 +7550,7 @@ LogicalProject(JOB=[$0], EXPR$1=[$2])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}], agg#0=[COUNT()])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -7560,7 +7560,7 @@ LogicalProject(JOB=[$0], EXPR$1=[$2])
<TestCase name="testPushAggregateSumWithoutGroupKeyThroughJoin">
<Resource name="sql">
<![CDATA[select sum(sal)
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
join sales.dept as d on e.job = d.name]]>
</Resource>
<Resource name="planBefore">
@@ -7568,7 +7568,7 @@ join sales.dept as d on e.job = d.name]]>
LogicalAggregate(group=[{}], EXPR$0=[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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -7580,7 +7580,7 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($4)])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
LogicalAggregate(group=[{2}], EXPR$0=[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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}], agg#0=[COUNT()])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -7590,7 +7590,7 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($4)])
<TestCase name="testPushAggregateSumThroughJoinAfterAggregateReduce">
<Resource name="sql">
<![CDATA[select sum(sal)
-from (select * from sales.emp where empno = 10) as e
+from (select * from sales.emp where ename = 'A') as e
join sales.dept as d on e.job = d.name]]>
</Resource>
<Resource name="planBefore">
@@ -7598,7 +7598,7 @@ join sales.dept as d on e.job = d.name]]>
LogicalAggregate(group=[{}], EXPR$0=[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)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -7611,7 +7611,7 @@ LogicalProject(EXPR$0=[CASE(=($1, 0), null:INTEGER, $0)])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalAggregate(group=[{2}], EXPR$0=[$SUM0($5)], agg#1=[COUNT()])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[=($0, 10)])
+ LogicalFilter(condition=[=($1, 'A')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{1}], agg#0=[COUNT()])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -8657,6 +8657,38 @@ LogicalAggregate(group=[{0}], EXPR$1=[MAX($1)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testAggregateRemove7">
+ <Resource name="sql">
+ <![CDATA[
+select mgr, sum(sum_sal)
+from
+(select mgr, deptno, sum(sal) sum_sal
+ from sales.emp
+ group by mgr, deptno)
+where deptno=100
+group by mgr
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
+ LogicalProject(MGR=[$0], SUM_SAL=[$2])
+ LogicalFilter(condition=[=($1, 100)])
+ LogicalAggregate(group=[{0, 1}], SUM_SAL=[SUM($2)])
+ LogicalProject(MGR=[$3], DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(MGR=[$0], SUM_SAL=[$2])
+ LogicalFilter(condition=[=($1, 100)])
+ LogicalAggregate(group=[{0, 1}], SUM_SAL=[SUM($2)])
+ LogicalProject(MGR=[$3], DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testAggregateJoinRemove1">
<Resource name="sql">
<![CDATA[select distinct e.deptno from sales.emp e
diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index 595ca2b..4c0b54f 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -621,10 +621,11 @@ where exists (
(1 row)
!ok
-EnumerableHashJoin(condition=[=($0, $10)], joinType=[semi])
- EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=['SMITH':VARCHAR(10)], expr#9=[=($t1, $t8)], expr#10=[IS NOT NULL($t7)], expr#11=[AND($t9, $t10)], proj#0..7=[{exprs}], $condition=[$t11])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t1], DNAME=[$t2], LOC=[$t3])
+ EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=['SMITH':VARCHAR(10)], expr#9=[=($t1, $t8)], expr#10=[IS NOT NULL($t7)], expr#11=[AND($t9, $t10)], DEPTNO=[$t7], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# [DRILL-5644]