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:35 UTC
[calcite] branch master updated (8ba2d85 -> 1745f75)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git.
from 8ba2d85 [CALCITE-3424] AssertionError thrown for user-defined table function with array argument (Igor Guzenko)
new 331e4fe Add RelBuilder.fields(ImmutableBitSet)
new 2a2edba [CALCITE-3362] Add some tests for empty Lattice (Wang Yanlin)
new f9103e6 [CALCITE-3428] Refine RelMdColumnUniqueness for Filter by considering constant columns (Jin Xing)
new 1745f75 Cosmetic
The 4 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
.../rel/metadata/RelMdColumnUniqueness.java | 37 ++++
.../rel/rules/AbstractMaterializedViewRule.java | 4 +-
.../calcite/rel/rules/AggregateRemoveRule.java | 4 +-
.../apache/calcite/sql2rel/InitializerContext.java | 2 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 17 +-
.../java/org/apache/calcite/util/XmlOutput.java | 4 +-
.../java/org/apache/calcite/test/LatticeTest.java | 73 ++++++-
.../org/apache/calcite/test/RelMetadataTest.java | 141 ++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 58 +++--
.../apache/calcite/test/SqlToRelConverterTest.java | 96 ++++----
.../org/apache/calcite/test/catalog/Fixture.java | 1 +
.../org/apache/calcite/test/RelOptRulesTest.xml | 136 +++++++-----
.../apache/calcite/test/SqlToRelConverterTest.xml | 243 ++++++++++++---------
core/src/test/resources/sql/sub-query.iq | 9 +-
14 files changed, 574 insertions(+), 251 deletions(-)
[calcite] 01/04: Add RelBuilder.fields(ImmutableBitSet)
Posted by jh...@apache.org.
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 331e4feef8d6f98b29b331f62c2c109ea3ba7e00
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Tue Oct 22 13:13:17 2019 -0700
Add RelBuilder.fields(ImmutableBitSet)
---
.../calcite/rel/rules/AbstractMaterializedViewRule.java | 4 ++--
.../apache/calcite/rel/rules/AggregateRemoveRule.java | 4 ++--
.../main/java/org/apache/calcite/tools/RelBuilder.java | 17 +++++++++--------
3 files changed, 13 insertions(+), 12 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java
index 06f33cc..516c159 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java
@@ -1205,7 +1205,7 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule {
// Aggregate was not inserted but we need to prune columns
result = relBuilder
.push(result)
- .project(relBuilder.fields(groupSet.asList()))
+ .project(relBuilder.fields(groupSet))
.build();
}
if (topProject != null) {
@@ -1490,7 +1490,7 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule {
// Aggregate was not inserted but we need to prune columns
result = relBuilder
.push(result)
- .project(relBuilder.fields(groupSet.asList()))
+ .project(relBuilder.fields(groupSet))
.build();
}
// We introduce a project on top, as group by columns order is lost
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
index 1cfd621..2ea110f 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
@@ -115,13 +115,13 @@ public class AggregateRemoveRule extends RelOptRule {
final RelNode newInput = convert(input, aggregate.getTraitSet().simplify());
relBuilder.push(newInput);
if (!projects.isEmpty()) {
- projects.addAll(0, relBuilder.fields(aggregate.getGroupSet().asList()));
+ projects.addAll(0, relBuilder.fields(aggregate.getGroupSet()));
relBuilder.project(projects);
} else if (newInput.getRowType().getFieldCount()
> aggregate.getRowType().getFieldCount()) {
// If aggregate was projecting a subset of columns, and there were no
// aggregate functions, add a project for the same effect.
- relBuilder.project(relBuilder.fields(aggregate.getGroupSet().asList()));
+ relBuilder.project(relBuilder.fields(aggregate.getGroupSet()));
}
call.transformTo(relBuilder.build());
}
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index a8b51a4..a86a96c 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -565,6 +565,11 @@ public class RelBuilder {
return nodes.build();
}
+ /** Returns references to fields for a given bit set of input ordinals. */
+ public ImmutableList<RexNode> fields(ImmutableBitSet ordinals) {
+ return fields(ordinals.asList());
+ }
+
/** Returns references to fields identified by name. */
public ImmutableList<RexNode> fields(Iterable<String> fieldNames) {
final ImmutableList.Builder<RexNode> builder = ImmutableList.builder();
@@ -818,12 +823,8 @@ public class RelBuilder {
throw new IllegalArgumentException("out of bounds: " + groupSet);
}
Objects.requireNonNull(groupSets);
- final ImmutableList<RexNode> nodes =
- fields(ImmutableIntList.of(groupSet.toArray()));
- final List<ImmutableList<RexNode>> nodeLists =
- Util.transform(groupSets,
- bitSet -> fields(ImmutableIntList.of(bitSet.toArray())));
- return groupKey_(nodes, nodeLists);
+ final ImmutableList<RexNode> nodes = fields(groupSet);
+ return groupKey_(nodes, Util.transform(groupSets, bitSet -> fields(bitSet)));
}
@Deprecated // to be removed before 2.0
@@ -1571,13 +1572,13 @@ public class RelBuilder {
final Boolean unique = mq.areColumnsUnique(peek(), groupSet);
if (unique != null && unique) {
// Rel is already unique.
- return project(fields(groupSet.asList()));
+ return project(fields(groupSet));
}
}
final Double maxRowCount = mq.getMaxRowCount(peek());
if (maxRowCount != null && maxRowCount <= 1D) {
// If there is at most one row, rel is already unique.
- return project(fields(groupSet.asList()));
+ return project(fields(groupSet));
}
}
final ImmutableList<ImmutableBitSet> groupSets;
[calcite] 03/04: [CALCITE-3428] Refine RelMdColumnUniqueness for
Filter by considering constant columns (Jin Xing)
Posted by jh...@apache.org.
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]
[calcite] 02/04: [CALCITE-3362] Add some tests for empty Lattice
(Wang Yanlin)
Posted by jh...@apache.org.
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 2a2edbaef2667c9807aa3748be76f217d7b73490
Author: yanzhi <ya...@antfin.com>
AuthorDate: Wed Sep 18 21:17:24 2019 +0800
[CALCITE-3362] Add some tests for empty Lattice (Wang Yanlin)
Close apache/calcite#1465
---
.../java/org/apache/calcite/test/LatticeTest.java | 73 +++++++++++++++++++---
1 file changed, 65 insertions(+), 8 deletions(-)
diff --git a/core/src/test/java/org/apache/calcite/test/LatticeTest.java b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
index 912bae0..a5595a7 100644
--- a/core/src/test/java/org/apache/calcite/test/LatticeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
@@ -718,31 +718,88 @@ public class LatticeTest {
/** A tile with no measures should inherit default measure list from the
* lattice. */
@Test public void testTileWithNoMeasures() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " defaultMeasures: [ {\n"
+ + " agg: 'count'\n"
+ + " } ],\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .query("select count(t.\"the_year\", t.\"quarter\")\n"
+ + "from \"foodmart\".\"sales_fact_1997\" as s\n"
+ + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
+ .enableMaterializations(true)
+ .explainContains("EnumerableAggregate(group=[{}], EXPR$0=[COUNT($0, $1)])\n"
+ + " EnumerableTableScan(table=[[adhoc, m{32, 36}")
+ .returnsCount(1);
}
/** A lattice with no default measure list should get "count(*)" is its
* default measure. */
@Test public void testLatticeWithNoMeasures() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .query("select count(*)\n"
+ + "from \"foodmart\".\"sales_fact_1997\" as s\n"
+ + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
+ .enableMaterializations(true)
+ .explainContains("EnumerableAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+ + " EnumerableTableScan(table=[[adhoc, m{32, 36}")
+ .returnsCount(1);
}
@Test public void testDimensionIsInvalidColumn() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'invalid_column'],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .connectThrows("Unknown lattice column 'invalid_column'");
}
@Test public void testMeasureArgIsInvalidColumn() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " defaultMeasures: [ {\n"
+ + " agg: 'sum',\n"
+ + " args: 'invalid_column'\n"
+ + " } ],\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .connectThrows("Unknown lattice column 'invalid_column'");
}
- /** It is an error for "customer_id" to be a measure arg, because is not a
- * unique alias. Both "c" and "t" have "customer_id". */
+ /** It is an error for "time_id" to be a measure arg, because is not a
+ * unique alias. Both "s" and "t" have "time_id". */
@Test public void testMeasureArgIsNotUniqueAlias() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " defaultMeasures: [ {\n"
+ + " agg: 'count',\n"
+ + " args: 'time_id'\n"
+ + " } ],\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .connectThrows("Lattice column alias 'time_id' is not unique");
}
@Test public void testMeasureAggIsInvalid() {
- // TODO
+ foodmartModel(" auto: false,\n"
+ + " defaultMeasures: [ {\n"
+ + " agg: 'invalid_count',\n"
+ + " args: 'customer_id'\n"
+ + " } ],\n"
+ + " tiles: [ {\n"
+ + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
+ + " measures: [ ]\n"
+ + " } ]\n")
+ .connectThrows("Unknown lattice aggregate function invalid_count");
}
@Test public void testTwoLattices() {
[calcite] 04/04: Cosmetic
Posted by jh...@apache.org.
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 1745f752561be04ae34d1fa08593c2d3ba4470e8
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Wed Oct 23 10:43:56 2019 -0700
Cosmetic
Improve fix for [CALCITE-3259]; the previous fix generated spurious
newlines in XML reference files such as SqlToRelConverterTest.xml.
Add newlines into some SQL queries added recently in
SqlToRelConverterTest.
Re-generate SqlToRelConverterTest, putting tests into quasi-alphabetical
order, to prevent merge conflicts, and re-generate "sql" resources
without whitespace at end of lines.
---
.../apache/calcite/sql2rel/InitializerContext.java | 2 +-
.../java/org/apache/calcite/util/XmlOutput.java | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 96 ++++----
.../org/apache/calcite/test/catalog/Fixture.java | 1 +
.../apache/calcite/test/SqlToRelConverterTest.xml | 243 ++++++++++++---------
5 files changed, 189 insertions(+), 157 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/InitializerContext.java b/core/src/main/java/org/apache/calcite/sql2rel/InitializerContext.java
index a93fbf4..9c0414e 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/InitializerContext.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/InitializerContext.java
@@ -40,7 +40,7 @@ public interface InitializerContext {
* );
* </pre>
*
- * You can use the string format expression "my_udf(a)" and "a + 1"
+ * <p>You can use the string format expression "my_udf(a)" and "a + 1"
* as the initializer expression of column b and c.
*
* <p>Calcite doesn't really need this now because the DDL nodes
diff --git a/core/src/main/java/org/apache/calcite/util/XmlOutput.java b/core/src/main/java/org/apache/calcite/util/XmlOutput.java
index ad59d68..148c3e4 100644
--- a/core/src/main/java/org/apache/calcite/util/XmlOutput.java
+++ b/core/src/main/java/org/apache/calcite/util/XmlOutput.java
@@ -372,11 +372,11 @@ public class XmlOutput {
out.print(data);
out.println("]]>");
} else if (!specials) {
- out.print(data);
+ out.println(data);
} else {
stringEncodeXML(data, out);
+ out.println();
}
- out.println();
out.flush();
tagsWritten++;
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index cd3421a..9a0945d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2790,100 +2790,102 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
- * Test case for <a href="https://issues.apache.org/jira/browse/CALCITE-2962">[CALCITE-2962]
- * RelStructuredTypeFlattener generates wrong types for nested column when flattenProjection</a>.
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-2962">[CALCITE-2962]
+ * RelStructuredTypeFlattener generates wrong types for nested column when
+ * flattenProjection</a>.
*/
@Test public void testSelectNestedColumnType() {
- final String sql =
- "select\n"
- + " char_length(coord.\"unit\") as unit_length\n"
- + "from\n"
- + " (\n"
- + " select\n"
- + " fname,\n"
- + " coord\n"
- + " from\n"
- + " customer.contact_peek\n"
- + " where\n"
- + " coord.x > 1\n"
- + " and coord.y > 1\n"
- + " ) as view\n"
- + "where\n"
- + " fname = 'john'";
+ final String sql = "select\n"
+ + " char_length(coord.\"unit\") as unit_length\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " fname,\n"
+ + " coord\n"
+ + " from\n"
+ + " customer.contact_peek\n"
+ + " where\n"
+ + " coord.x > 1\n"
+ + " and coord.y > 1\n"
+ + " ) as view\n"
+ + "where\n"
+ + " fname = 'john'";
sql(sql).ok();
}
@Test public void testNestedStructFieldAccess() {
- final String sql =
- "select dn.skill['others'] from sales.dept_nested dn";
+ final String sql = "select dn.skill['others']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testNestedStructPrimitiveFieldAccess() {
- final String sql =
- "select dn.skill['others']['a'] from sales.dept_nested dn";
+ final String sql = "select dn.skill['others']['a']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testFunctionWithStructInput() {
- final String sql =
- "select json_type(skill) from sales.dept_nested";
+ final String sql = "select json_type(skill)\n"
+ + "from sales.dept_nested";
sql(sql).ok();
}
@Test public void testAggregateFunctionForStructInput() {
- final String sql = "select "
- + "collect(skill) as collect_skill, count(skill) as count_skill, count(*) as count_star, "
- + "approx_count_distinct(skill) as approx_count_distinct_skill, "
- + "max(skill) as max_skill, min(skill) as min_skill, "
- + "any_value(skill) as any_value_skill "
+ final String sql = "select collect(skill) as collect_skill,\n"
+ + " count(skill) as count_skill, count(*) as count_star,\n"
+ + " approx_count_distinct(skill) as approx_count_distinct_skill,\n"
+ + " max(skill) as max_skill, min(skill) as min_skill,\n"
+ + " any_value(skill) as any_value_skill\n"
+ "from sales.dept_nested";
sql(sql).ok();
}
@Test public void testAggregateFunctionForStructInputByName() {
- final String sql = "select "
- + "collect(skill) as collect_skill, count(skill) as count_skill, count(*) as count_star, "
- + "approx_count_distinct(skill) as approx_count_distinct_skill, "
- + "max(skill) as max_skill, min(skill) as min_skill, "
- + "any_value(skill) as any_value_skill "
+ final String sql = "select collect(skill) as collect_skill,\n"
+ + " count(skill) as count_skill, count(*) as count_star,\n"
+ + " approx_count_distinct(skill) as approx_count_distinct_skill,\n"
+ + " max(skill) as max_skill, min(skill) as min_skill,\n"
+ + " any_value(skill) as any_value_skill\n"
+ "from sales.dept_nested group by name";
sql(sql).ok();
}
@Test public void testNestedPrimitiveFieldAccess() {
- final String sql =
- "select dn.skill['desc'] from sales.dept_nested dn";
+ final String sql = "select dn.skill['desc']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testArrayElementNestedPrimitive() {
- final String sql =
- "select dn.employees[0]['empno'] from sales.dept_nested dn";
+ final String sql = "select dn.employees[0]['empno']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testArrayElementDoublyNestedPrimitive() {
- final String sql =
- "select dn.employees[0]['detail']['skills'][0]['type'] from sales.dept_nested dn";
+ final String sql = "select dn.employees[0]['detail']['skills'][0]['type']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testArrayElementDoublyNestedStruct() {
- final String sql =
- "select dn.employees[0]['detail']['skills'][0] from sales.dept_nested dn";
+ final String sql = "select dn.employees[0]['detail']['skills'][0]\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
@Test public void testArrayElementThreeTimesNestedStruct() {
- final String sql =
- "select dn.employees[0]['detail']['skills'][0]['others'] from sales.dept_nested dn";
+ final String sql = ""
+ + "select dn.employees[0]['detail']['skills'][0]['others']\n"
+ + "from sales.dept_nested dn";
sql(sql).ok();
}
-
/**
- * Test case for <a href="https://issues.apache.org/jira/browse/CALCITE-3003">[CALCITE-3003]
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-3003">[CALCITE-3003]
* AssertionError when GROUP BY nested field</a>.
*/
@Test public void testGroupByNestedColumn() {
@@ -3556,7 +3558,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testProjectAggregatesIgnoreNullsAndNot() {
final String sql = "select lead(sal, 4) IGNORE NULLS, lead(sal, 4) over (w)\n"
- + " from emp window w as (order by empno)";
+ + "from emp window w as (order by empno)";
sql(sql).ok();
}
diff --git a/core/src/test/java/org/apache/calcite/test/catalog/Fixture.java b/core/src/test/java/org/apache/calcite/test/catalog/Fixture.java
index db39b06..33013bf 100644
--- a/core/src/test/java/org/apache/calcite/test/catalog/Fixture.java
+++ b/core/src/test/java/org/apache/calcite/test/catalog/Fixture.java
@@ -163,4 +163,5 @@ abstract class AbstractFixture {
this.typeFactory = typeFactory;
}
}
+
// End Fixture.java
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index bdde39f..34ad975 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -16,6 +16,42 @@ See the License for the specific language governing permissions and
limitations under the License.
-->
<Root>
+ <TestCase name="testAggregateFunctionForStructInput">
+ <Resource name="sql">
+ <![CDATA[select collect(skill) as collect_skill,
+ count(skill) as count_skill, count(*) as count_star,
+ approx_count_distinct(skill) as approx_count_distinct_skill,
+ max(skill) as max_skill, min(skill) as min_skill,
+ any_value(skill) as any_value_skill
+from sales.dept_nested]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(COLLECT_SKILL=[$0], COUNT_SKILL=[$1], COUNT_STAR=[$1], APPROX_COUNT_DISTINCT_SKILL=[$2], MAX_SKILL=[ROW($3.TYPE, $3.DESC, ROW($3.OTHERS.A, $3.OTHERS.B))], MIN_SKILL=[ROW($4.TYPE, $4.DESC, ROW($4.OTHERS.A, $4.OTHERS.B))], ANY_VALUE_SKILL=[ROW($5.TYPE, $5.DESC, ROW($5.OTHERS.A, $5.OTHERS.B))])
+ LogicalAggregate(group=[{}], COLLECT_SKILL=[COLLECT($0)], COUNT_SKILL=[COUNT()], APPROX_COUNT_DISTINCT_SKILL=[COUNT(DISTINCT $0)], MAX_SKILL=[MAX($0)], MIN_SKILL=[MIN($0)], ANY_VALUE_SKILL=[ANY_VALUE($0)])
+ LogicalProject(SKILL=[ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateFunctionForStructInputByName">
+ <Resource name="sql">
+ <![CDATA[select collect(skill) as collect_skill,
+ count(skill) as count_skill, count(*) as count_star,
+ approx_count_distinct(skill) as approx_count_distinct_skill,
+ max(skill) as max_skill, min(skill) as min_skill,
+ any_value(skill) as any_value_skill
+from sales.dept_nested group by name]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(COLLECT_SKILL=[$1], COUNT_SKILL=[$2], COUNT_STAR=[$2], APPROX_COUNT_DISTINCT_SKILL=[$3], MAX_SKILL=[ROW($4.TYPE, $4.DESC, ROW($4.OTHERS.A, $4.OTHERS.B))], MIN_SKILL=[ROW($5.TYPE, $5.DESC, ROW($5.OTHERS.A, $5.OTHERS.B))], ANY_VALUE_SKILL=[ROW($6.TYPE, $6.DESC, ROW($6.OTHERS.A, $6.OTHERS.B))])
+ LogicalAggregate(group=[{0}], COLLECT_SKILL=[COLLECT($1)], COUNT_SKILL=[COUNT()], APPROX_COUNT_DISTINCT_SKILL=[COUNT(DISTINCT $1)], MAX_SKILL=[MAX($1)], MIN_SKILL=[MIN($1)], ANY_VALUE_SKILL=[ANY_VALUE($1)])
+ LogicalProject(NAME=[$1], SKILL=[ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testCase">
<Resource name="plan">
<![CDATA[
@@ -213,6 +249,54 @@ from emp
group by deptno]]>
</Resource>
</TestCase>
+ <TestCase name="testArrayElementDoublyNestedPrimitive">
+ <Resource name="sql">
+ <![CDATA[select dn.employees[0]['detail']['skills'][0]['type']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[ITEM(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0), 'type')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testArrayElementDoublyNestedStruct">
+ <Resource name="sql">
+ <![CDATA[select dn.employees[0]['detail']['skills'][0]
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).TYPE, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).DESC, ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.A, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.B))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testArrayElementNestedPrimitive">
+ <Resource name="sql">
+ <![CDATA[select dn.employees[0]['empno']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[ITEM(ITEM($3, 0), 'empno')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testArrayElementThreeTimesNestedStruct">
+ <Resource name="sql">
+ <![CDATA[select dn.employees[0]['detail']['skills'][0]['others']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.A, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.B)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testCollectionTableWithLateral">
<Resource name="sql">
<![CDATA[select * from dept, lateral table(ramp(dept.deptno))]]>
@@ -882,62 +966,6 @@ LogicalProject(ZIP=[$3])
<![CDATA[select empa.home_address.zip from sales.emp_address empa where empa.home_address.city = 'abc']]>
</Resource>
</TestCase>
- <TestCase name="testNestedStructFieldAccess">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[ROW($2.OTHERS.A, $2.OTHERS.B)])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testNestedStructPrimitiveFieldAccess">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[$2.OTHERS.A])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testNestedPrimitiveFieldAccess">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[$2.DESC])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testArrayElementNestedPrimitive">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[ITEM(ITEM($3, 0), 'empno')])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testArrayElementDoublyNestedPrimitive">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[ITEM(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0), 'type')])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testArrayElementDoublyNestedStruct">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).TYPE, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).DESC, ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.A, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.B))])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testArrayElementThreeTimesNestedStruct">
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[ROW(ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.A, ITEM(ITEM(ITEM(ITEM($3, 0), 'detail'), 'skills'), 0).OTHERS.B)])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
<TestCase name="testSelectNestedColumnType">
<Resource name="plan">
<![CDATA[
@@ -1221,6 +1249,18 @@ LogicalProject(NAME=[$0])
]]>
</Resource>
</TestCase>
+ <TestCase name="testFunctionWithStructInput">
+ <Resource name="sql">
+ <![CDATA[select json_type(skill)
+from sales.dept_nested]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[JSON_TYPE(ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B)))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testOrder">
<Resource name="sql">
<![CDATA[select empno from emp order by empno, empno desc]]>
@@ -3432,6 +3472,42 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $2], EXPR$2=[COUNT()])
]]>
</Resource>
</TestCase>
+ <TestCase name="testNestedPrimitiveFieldAccess">
+ <Resource name="sql">
+ <![CDATA[select dn.skill['desc']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$2.DESC])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedStructFieldAccess">
+ <Resource name="sql">
+ <![CDATA[select dn.skill['others']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[ROW($2.OTHERS.A, $2.OTHERS.B)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedStructPrimitiveFieldAccess">
+ <Resource name="sql">
+ <![CDATA[select dn.skill['others']['a']
+from sales.dept_nested dn]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$2.OTHERS.A])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSimplifyExistsAggregateSubQuery">
<Resource name="sql">
<![CDATA[SELECT e1.empno
@@ -4747,7 +4823,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE], updateColu
<TestCase name="testUpdateSubQueryWithNotIn">
<Resource name="sql">
<![CDATA[update emp
-set empno = 1 where empno not in(
+set empno = 1 where empno not in (
select empno from emp where empno=2)]]>
</Resource>
<Resource name="plan">
@@ -4768,8 +4844,8 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE], updateColu
<TestCase name="testUpdateSubQueryWithIn1">
<Resource name="sql">
<![CDATA[update emp
-set empno = 1 where empno in (
- select empno from emp where empno=2)]]>
+set empno = 1 where emp.empno in (
+ select emp.empno from emp where emp.empno=2)]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -6248,7 +6324,7 @@ LogicalProject(C=[$0], N=[$1])
<TestCase name="testProjectApproximateAndExactAggregates">
<Resource name="sql">
<![CDATA[SELECT empno, count(distinct ename),
-approx_count_distinct(ename) "
+approx_count_distinct(ename)
FROM emp
GROUP BY empno]]>
</Resource>
@@ -6272,51 +6348,4 @@ LogicalProject(EXPR$0=[IGNORE NULLS(LEAD($5, 4))], EXPR$1=[LEAD($5, 4) OVER (ORD
]]>
</Resource>
</TestCase>
- <TestCase name="testFunctionWithStructInput">
- <Resource name="sql">
- <![CDATA[select json_type(skill) from sales.dept_nested]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[JSON_TYPE(ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B)))])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testAggregateFunctionForStructInput">
- <Resource name="sql">
- <![CDATA[select
- collect(skill) as collect_skill, count(skill) as count_skill, count(*) as count_star,
- approx_count_distinct(skill) as approx_count_distinct_skill,
- max(skill) as max_skill, min(skill) as min_skill,
- any_value(skill) as any_value_skill
- from sales.dept_nested]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(COLLECT_SKILL=[$0], COUNT_SKILL=[$1], COUNT_STAR=[$1], APPROX_COUNT_DISTINCT_SKILL=[$2], MAX_SKILL=[ROW($3.TYPE, $3.DESC, ROW($3.OTHERS.A, $3.OTHERS.B))], MIN_SKILL=[ROW($4.TYPE, $4.DESC, ROW($4.OTHERS.A, $4.OTHERS.B))], ANY_VALUE_SKILL=[ROW($5.TYPE, $5.DESC, ROW($5.OTHERS.A, $5.OTHERS.B))])
- LogicalAggregate(group=[{}], COLLECT_SKILL=[COLLECT($0)], COUNT_SKILL=[COUNT()], APPROX_COUNT_DISTINCT_SKILL=[COUNT(DISTINCT $0)], MAX_SKILL=[MAX($0)], MIN_SKILL=[MIN($0)], ANY_VALUE_SKILL=[ANY_VALUE($0)])
- LogicalProject(SKILL=[ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B))])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testAggregateFunctionForStructInputByName">
- <Resource name="sql">
- <![CDATA[select
- collect(skill) as collect_skill, count(skill) as count_skill, count(*) as count_star,
- approx_count_distinct(skill) as approx_count_distinct_skill,
- max(skill) as max_skill, min(skill) as min_skill,
- any_value(skill) as any_value_skill
- from sales.dept_nested group by name]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(COLLECT_SKILL=[$1], COUNT_SKILL=[$2], COUNT_STAR=[$2], APPROX_COUNT_DISTINCT_SKILL=[$3], MAX_SKILL=[ROW($4.TYPE, $4.DESC, ROW($4.OTHERS.A, $4.OTHERS.B))], MIN_SKILL=[ROW($5.TYPE, $5.DESC, ROW($5.OTHERS.A, $5.OTHERS.B))], ANY_VALUE_SKILL=[ROW($6.TYPE, $6.DESC, ROW($6.OTHERS.A, $6.OTHERS.B))])
- LogicalAggregate(group=[{0}], COLLECT_SKILL=[COLLECT($1)], COUNT_SKILL=[COUNT()], APPROX_COUNT_DISTINCT_SKILL=[COUNT(DISTINCT $1)], MAX_SKILL=[MAX($1)], MIN_SKILL=[MIN($1)], ANY_VALUE_SKILL=[ANY_VALUE($1)])
- LogicalProject(NAME=[$1], SKILL=[ROW($2.TYPE, $2.DESC, ROW($2.OTHERS.A, $2.OTHERS.B))])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- </TestCase>
</Root>