http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index 42986b8..235dfb4 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -34,7 +34,6 @@ import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelRoot; import org.apache.calcite.rel.SingleRel; -import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.Collect; import org.apache.calcite.rel.core.CorrelationId; @@ -238,6 +237,7 @@ public class SqlToRelConverter { private final SqlNodeToRexConverter exprConverter; private int explainParamCount; public final SqlToRelConverter.Config config; + private final RelBuilder relBuilder; /** * Fields used in name resolution for correlated sub-queries. @@ -318,6 +318,7 @@ public class SqlToRelConverter { this.exprConverter = new SqlNodeToRexConverterImpl(convertletTable); this.explainParamCount = 0; this.config = new ConfigBuilder().withConfig(config).build(); + this.relBuilder = RelFactories.LOGICAL_BUILDER.create(cluster, null); } //~ Methods ---------------------------------------------------------------- @@ -527,8 +528,6 @@ public class SqlToRelConverter { * @return Field trimmer */ protected RelFieldTrimmer newFieldTrimmer() { - final RelBuilder relBuilder = - RelFactories.LOGICAL_BUILDER.create(cluster, null); return new RelFieldTrimmer(validator, relBuilder); } @@ -757,9 +756,8 @@ public class SqlToRelConverter { // different. final ImmutableBitSet groupSet = ImmutableBitSet.range(rel.getRowType().getFieldCount()); - rel = - createAggregate(bb, false, groupSet, ImmutableList.of(groupSet), - ImmutableList.<AggregateCall>of()); + rel = createAggregate(bb, groupSet, ImmutableList.of(groupSet), + ImmutableList.<AggregateCall>of()); bb.setRoot( rel, @@ -1099,7 +1097,7 @@ public class SqlToRelConverter { final int keyCount = leftKeys.size(); final List<Integer> args = ImmutableIntList.range(0, keyCount); LogicalAggregate aggregate = - LogicalAggregate.create(seek, false, ImmutableBitSet.of(), null, + LogicalAggregate.create(seek, ImmutableBitSet.of(), null, ImmutableList.of( AggregateCall.create(SqlStdOperatorTable.COUNT, false, ImmutableList.<Integer>of(), -1, longType, null), @@ -2706,7 +2704,7 @@ public class SqlToRelConverter { aggConverter.addGroupExpr(groupExpr); } - RexNode havingExpr = null; + final RexNode havingExpr; final List<Pair<RexNode, String>> projects = Lists.newArrayList(); try { @@ -2763,42 +2761,10 @@ public class SqlToRelConverter { // Add the aggregator bb.setRoot( - createAggregate(bb, r.indicator, r.groupSet, r.groupSets, + createAggregate(bb, r.groupSet, r.groupSets, aggConverter.getAggCalls()), false); - // Generate NULL values for rolled-up not-null fields. - final Aggregate aggregate = (Aggregate) bb.root; - if (aggregate.getGroupType() != Aggregate.Group.SIMPLE) { - assert aggregate.indicator; - List<Pair<RexNode, String>> projects2 = Lists.newArrayList(); - int converted = 0; - final int groupCount = aggregate.getGroupSet().cardinality(); - for (RelDataTypeField field : aggregate.getRowType().getFieldList()) { - final int i = field.getIndex(); - final RexNode rex; - if (i < groupCount && r.isNullable(i)) { - ++converted; - - rex = rexBuilder.makeCall(SqlStdOperatorTable.CASE, - rexBuilder.makeInputRef(aggregate, groupCount + i), - rexBuilder.makeCast( - typeFactory.createTypeWithNullability( - field.getType(), true), - rexBuilder.constantNull()), - rexBuilder.makeInputRef(aggregate, i)); - } else { - rex = rexBuilder.makeInputRef(aggregate, i); - } - projects2.add(Pair.of(rex, field.getName())); - } - if (converted > 0) { - bb.setRoot( - RelOptUtil.createProject(bb.root, projects2, true), - false); - } - } - bb.mapRootRelToFieldProjection.put(bb.root, r.groupExprProjection); // Replace sub-queries in having here and modify having to use @@ -2807,9 +2773,8 @@ public class SqlToRelConverter { SqlNode newHaving = pushDownNotForIn(bb.scope, having); replaceSubQueries(bb, newHaving, RelOptUtil.Logic.UNKNOWN_AS_FALSE); havingExpr = bb.convertExpression(newHaving); - if (havingExpr.isAlwaysTrue()) { - havingExpr = null; - } + } else { + havingExpr = relBuilder.literal(true); } // Now convert the other sub-queries in the select list. @@ -2855,19 +2820,15 @@ public class SqlToRelConverter { } // implement HAVING (we have already checked that it is non-trivial) + relBuilder.push(bb.root); if (havingExpr != null) { - final RelFactories.FilterFactory factory = - RelFactories.DEFAULT_FILTER_FACTORY; - bb.setRoot(factory.createFilter(bb.root, havingExpr), false); + relBuilder.filter(havingExpr); } // implement the SELECT list - bb.setRoot( - RelOptUtil.createProject( - bb.root, - projects, - true), - false); + relBuilder.project(Pair.left(projects), Pair.right(projects)) + .rename(Pair.right(projects)); + bb.setRoot(relBuilder.build(), false); // Tell bb which of group columns are sorted. bb.columnMonotonicities.clear(); @@ -2890,17 +2851,14 @@ public class SqlToRelConverter { * parameter. * * @param bb Blackboard - * @param indicator Whether to output fields indicating grouping sets * @param groupSet Bit set of ordinals of grouping columns * @param groupSets Grouping sets * @param aggCalls Array of calls to aggregate functions * @return LogicalAggregate */ - protected RelNode createAggregate(Blackboard bb, boolean indicator, - ImmutableBitSet groupSet, ImmutableList<ImmutableBitSet> groupSets, - List<AggregateCall> aggCalls) { - return LogicalAggregate.create( - bb.root, indicator, groupSet, groupSets, aggCalls); + protected RelNode createAggregate(Blackboard bb, ImmutableBitSet groupSet, + ImmutableList<ImmutableBitSet> groupSets, List<AggregateCall> aggCalls) { + return LogicalAggregate.create(bb.root, groupSet, groupSets, aggCalls); } public RexDynamicParam convertDynamicParam( @@ -3537,7 +3495,7 @@ public class SqlToRelConverter { } final Pair<RexNode, Map<String, Integer>> e0 = bb.lookupExp(qualified); RexNode e = e0.left; - for (String name : qualified.suffixTranslated()) { + for (String name : qualified.suffix()) { if (e == e0.left && e0.right != null) { int i = e0.right.get(name); e = rexBuilder.makeFieldAccess(e, i); @@ -4199,7 +4157,14 @@ public class SqlToRelConverter { if (node == null) { return null; } else { - return Pair.of(node, null); + final Map<String, Integer> fieldOffsets = new HashMap<>(); + for (RelDataTypeField f : resolve.rowType().getFieldList()) { + if (!fieldOffsets.containsKey(f.getName())) { + fieldOffsets.put(f.getName(), f.getIndex()); + } + } + final Map<String, Integer> map = ImmutableMap.copyOf(fieldOffsets); + return Pair.of(node, map); } } else { // We're referencing a relational expression which has not been @@ -4223,8 +4188,7 @@ public class SqlToRelConverter { builder.addAll(c.getRowType().getFieldList()); if (i == resolve.path.steps().get(0).i) { for (RelDataTypeField field : c.getRowType().getFieldList()) { - fields.put(c.translate(field.getName()), - field.getIndex() + offset); + fields.put(field.getName(), field.getIndex() + offset); } } ++i; @@ -4924,7 +4888,7 @@ public class SqlToRelConverter { rexBuilder.addAggCall( aggCall, groupExprs.size(), - r.indicator, + false, aggCalls, aggCallMapping, argTypes); @@ -4964,36 +4928,6 @@ public class SqlToRelConverter { // assert call.getOperator().isAggregator(); assert bb.agg == this; - switch (call.getKind()) { - case GROUPING: - case GROUP_ID: - final RelDataType type = validator.getValidatedNodeType(call); - if (!aggregatingSelectScope.resolved.get().indicator) { - return rexBuilder.makeExactLiteral( - TWO.pow(effectiveArgCount(call)).subtract(BigDecimal.ONE), type); - } else { - final List<Integer> operands; - switch (call.getKind()) { - case GROUP_ID: - operands = ImmutableIntList.range(0, groupExprs.size()); - break; - default: - operands = Lists.newArrayList(); - for (SqlNode operand : call.getOperandList()) { - final int x = lookupGroupExpr(operand); - assert x >= 0; - operands.add(x); - } - } - RexNode node = null; - int shift = operands.size(); - for (int operand : operands) { - node = bitValue(node, type, operand, --shift); - } - return node; - } - } - for (Map.Entry<SqlNode, Ord<AuxiliaryConverter>> e : auxiliaryGroupExprs.entrySet()) { if (call.equalsDeep(e.getKey(), Litmus.IGNORE)) { @@ -5008,35 +4942,6 @@ public class SqlToRelConverter { return aggMapping.get(call); } - private int effectiveArgCount(SqlCall call) { - switch (call.getKind()) { - case GROUPING: - return call.operandCount(); - case GROUP_ID: - return groupExprs.size(); - default: - throw new AssertionError(call.getKind()); - } - } - - private RexNode bitValue(RexNode previous, RelDataType type, int x, - int shift) { - final AggregatingSelectScope.Resolved r = - aggregatingSelectScope.resolved.get(); - RexNode node = rexBuilder.makeCall(SqlStdOperatorTable.CASE, - rexBuilder.makeInputRef(bb.root, r.groupExprList.size() + x), - rexBuilder.makeExactLiteral(BigDecimal.ONE, type), - rexBuilder.makeExactLiteral(BigDecimal.ZERO, type)); - if (shift > 0) { - node = rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, node, - rexBuilder.makeExactLiteral(TWO.pow(shift), type)); - } - if (previous != null) { - node = rexBuilder.makeCall(SqlStdOperatorTable.PLUS, previous, node); - } - return node; - } - public List<Pair<RexNode, String>> getPreExprs() { return convertedInputExprs; }
http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/main/java/org/apache/calcite/tools/RelBuilder.java ---------------------------------------------------------------------- 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 4ef40df..fe822f0 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -575,11 +575,16 @@ public class RelBuilder { return call(SqlStdOperatorTable.NOT, operand); } - /** Creates an =. */ + /** Creates an {@code =}. */ public RexNode equals(RexNode operand0, RexNode operand1) { return call(SqlStdOperatorTable.EQUALS, operand0, operand1); } + /** Creates a {@code <>}. */ + public RexNode notEquals(RexNode operand0, RexNode operand1) { + return call(SqlStdOperatorTable.NOT_EQUALS, operand0, operand1); + } + /** Creates a IS NULL. */ public RexNode isNull(RexNode operand) { return call(SqlStdOperatorTable.IS_NULL, operand); @@ -681,8 +686,20 @@ public class RelBuilder { * <p>This method of creating a group key does not allow you to group on new * expressions, only column projections, but is efficient, especially when you * are coming from an existing {@link Aggregate}. */ + public GroupKey groupKey(ImmutableBitSet groupSet, + ImmutableList<ImmutableBitSet> groupSets) { + return groupKey_(groupSet, false, groupSets); + } + + /** @deprecated Use {@link #groupKey(ImmutableBitSet, ImmutableList)}. */ + @Deprecated // to be removed before 2.0 public GroupKey groupKey(ImmutableBitSet groupSet, boolean indicator, ImmutableList<ImmutableBitSet> groupSets) { + return groupKey_(groupSet, indicator, groupSets); + } + + private GroupKey groupKey_(ImmutableBitSet groupSet, boolean indicator, + ImmutableList<ImmutableBitSet> groupSets) { if (groupSet.length() > peek().getRowType().getFieldCount()) { throw new IllegalArgumentException("out of bounds: " + groupSet); } @@ -949,6 +966,46 @@ public class RelBuilder { return project(ImmutableList.copyOf(nodes)); } + /** Ensures that the field names match those given. + * + * <p>If all fields have the same name, adds nothing; + * if any fields do not have the same name, adds a {@link Project}. + * + * <p>Note that the names can be short-lived. Other {@code RelBuilder} + * operations make no guarantees about the field names of the rows they + * produce. + * + * @param fieldNames List of desired field names; may contain null values or + * have fewer fields than the current row type + */ + public RelBuilder rename(List<String> fieldNames) { + final List<String> oldFieldNames = peek().getRowType().getFieldNames(); + Preconditions.checkArgument(fieldNames.size() <= oldFieldNames.size(), + "More names than fields"); + final List<String> newFieldNames = new ArrayList<>(oldFieldNames); + for (int i = 0; i < fieldNames.size(); i++) { + final String s = fieldNames.get(i); + if (s != null) { + newFieldNames.set(i, s); + } + } + if (oldFieldNames.equals(newFieldNames)) { + return this; + } + project(fields(), newFieldNames, true); + + // If, after de-duplication, the field names are unchanged, discard the + // identity project we just created. + if (peek().getRowType().getFieldNames().equals(oldFieldNames)) { + final RelNode r = peek(); + if (r instanceof Project) { + stack.pop(); + push(((Project) r).getInput()); + } + } + return this; + } + /** Infers the alias of an expression. * * <p>If the expression was created by {@link #alias}, replaces the expression @@ -992,8 +1049,7 @@ public class RelBuilder { /** Creates an {@link org.apache.calcite.rel.core.Aggregate} with a list of * calls. */ public RelBuilder aggregate(GroupKey groupKey, Iterable<AggCall> aggCalls) { - final RelDataType inputRowType = peek().getRowType(); - final List<RexNode> extraNodes = projects(inputRowType); + final List<RexNode> extraNodes = new ArrayList<>(fields()); final GroupKeyImpl groupKey_ = (GroupKeyImpl) groupKey; final ImmutableBitSet groupSet = ImmutableBitSet.of(registerExpressions(extraNodes, groupKey_.nodes)); @@ -1051,9 +1107,7 @@ public class RelBuilder { } } } - if (extraNodes.size() > inputRowType.getFieldCount()) { - project(extraNodes); - } + project(extraNodes); final Frame frame = stack.pop(); final RelNode r = frame.rel; final List<AggregateCall> aggregateCalls = new ArrayList<>(); @@ -1064,6 +1118,12 @@ public class RelBuilder { final List<Integer> args = registerExpressions(extraNodes, aggCall1.operands); final int filterArg = aggCall1.filter == null ? -1 : registerExpression(extraNodes, aggCall1.filter); + if (aggCall1.distinct && !aggCall1.aggFunction.isQuantifierAllowed()) { + throw new IllegalArgumentException("DISTINCT not allowed"); + } + if (aggCall1.filter != null && !aggCall1.aggFunction.allowsFilter()) { + throw new IllegalArgumentException("FILTER not allowed"); + } aggregateCall = AggregateCall.create(aggCall1.aggFunction, aggCall1.distinct, args, filterArg, groupSet.cardinality(), r, null, aggCall1.alias); @@ -1124,15 +1184,6 @@ public class RelBuilder { return this; } - private List<RexNode> projects(RelDataType inputRowType) { - final List<RexNode> exprList = new ArrayList<>(); - for (RelDataTypeField field : inputRowType.getFieldList()) { - final RexBuilder rexBuilder = cluster.getRexBuilder(); - exprList.add(rexBuilder.makeInputRef(field.getType(), field.getIndex())); - } - return exprList; - } - private static int registerExpression(List<RexNode> exprList, RexNode node) { int i = exprList.indexOf(node); if (i < 0) { @@ -1545,9 +1596,8 @@ public class RelBuilder { public RelBuilder sortLimit(int offset, int fetch, Iterable<? extends RexNode> nodes) { final List<RelFieldCollation> fieldCollations = new ArrayList<>(); - final RelDataType inputRowType = peek().getRowType(); - final List<RexNode> extraNodes = projects(inputRowType); - final List<RexNode> originalExtraNodes = ImmutableList.copyOf(extraNodes); + final List<RexNode> originalExtraNodes = fields(); + final List<RexNode> extraNodes = new ArrayList<>(originalExtraNodes); for (RexNode node : nodes) { fieldCollations.add( collation(node, RelFieldCollation.Direction.ASCENDING, null, @@ -1705,6 +1755,7 @@ public class RelBuilder { GroupKeyImpl(ImmutableList<RexNode> nodes, boolean indicator, ImmutableList<ImmutableList<RexNode>> nodeLists, String alias) { this.nodes = Preconditions.checkNotNull(nodes); + assert !indicator; this.indicator = indicator; this.nodeLists = nodeLists; this.alias = alias; http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties ---------------------------------------------------------------------- diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties index 1f52475..74d0c0d 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -107,7 +107,6 @@ GroupingInWrongClause={0} operator may only occur in SELECT, HAVING or ORDER BY NotSelectDistinctExpr=Expression ''{0}'' is not in the select clause AggregateIllegalInClause=Aggregate expression is illegal in {0} clause WindowedAggregateIllegalInClause=Windowed aggregate expression is illegal in {0} clause -AggregateIllegalInGroupBy=Aggregate expression is illegal in GROUP BY clause NestedAggIllegal=Aggregate expressions cannot be nested AggregateInFilterIllegal=FILTER must not contain aggregate expression AggregateIllegalInOrderBy=Aggregate expression is illegal in ORDER BY clause of non-aggregating SELECT http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java index 5869024..9749b6d 100644 --- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java +++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java @@ -138,8 +138,7 @@ public class RelWriterTest { final RelDataType bigIntType = cluster.getTypeFactory().createSqlType(SqlTypeName.BIGINT); LogicalAggregate aggregate = - LogicalAggregate.create(filter, false, - ImmutableBitSet.of(0), null, + LogicalAggregate.create(filter, ImmutableBitSet.of(0), null, ImmutableList.of( AggregateCall.create(SqlStdOperatorTable.COUNT, true, ImmutableList.of(1), -1, bigIntType, http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java index 4eafeae..8a337cc 100644 --- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java @@ -487,6 +487,64 @@ public class RelBuilderTest { assertThat(str(root), is(expected)); } + @Test public void testRename() { + final RelBuilder builder = RelBuilder.create(config().build()); + + // No rename necessary (null name is ignored) + RelNode root = + builder.scan("DEPT") + .rename(Arrays.asList("DEPTNO", null)) + .build(); + final String expected = "LogicalTableScan(table=[[scott, DEPT]])\n"; + assertThat(str(root), is(expected)); + + // No rename necessary (prefix matches) + root = + builder.scan("DEPT") + .rename(ImmutableList.of("DEPTNO")) + .build(); + assertThat(str(root), is(expected)); + + // Add project to rename fields + root = + builder.scan("DEPT") + .rename(Arrays.asList("NAME", null, "DEPTNO")) + .build(); + final String expected2 = "" + + "LogicalProject(NAME=[$0], DNAME=[$1], DEPTNO=[$2])\n" + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + assertThat(str(root), is(expected2)); + + // If our requested list has non-unique names, we might get the same field + // names we started with. Don't add a useless project. + root = + builder.scan("DEPT") + .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) + .build(); + final String expected3 = "" + + "LogicalProject(DEPTNO=[$0], DNAME=[$1], DEPTNO0=[$2])\n" + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + assertThat(str(root), is(expected3)); + root = + builder.scan("DEPT") + .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) + .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) + .build(); + // No extra Project + assertThat(str(root), is(expected3)); + + // Name list too long + try { + root = + builder.scan("DEPT") + .rename(ImmutableList.of("NAME", "DEPTNO", "Y", "Z")) + .build(); + fail("expected error, got " + root); + } catch (IllegalArgumentException e) { + assertThat(e.getMessage(), is("More names than fields")); + } + } + @Test public void testPermute() { final RelBuilder builder = RelBuilder.create(config().build()); RelNode root = @@ -587,7 +645,7 @@ public class RelBuilderTest { RelNode root = builder.scan("EMP") .aggregate( - builder.groupKey(ImmutableBitSet.of(7), true, + builder.groupKey(ImmutableBitSet.of(7), ImmutableList.of(ImmutableBitSet.of(7), ImmutableBitSet.of())), builder.aggregateCall(SqlStdOperatorTable.COUNT, false, @@ -595,7 +653,7 @@ public class RelBuilderTest { builder.field("EMPNO"), builder.literal(100)), "C")) .build(); final String expected = "" - + "LogicalAggregate(group=[{7}], groups=[[{7}, {}]], indicator=[true], C=[COUNT() FILTER $8])\n" + + "LogicalAggregate(group=[{7}], groups=[[{7}, {}]], C=[COUNT() FILTER $8])\n" + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[>($0, 100)])\n" + " LogicalTableScan(table=[[scott, EMP]])\n"; assertThat(str(root), is(expected)); @@ -649,7 +707,7 @@ public class RelBuilderTest { try { RelNode root = builder.scan("EMP") - .aggregate(builder.groupKey(ImmutableBitSet.of(17), false, null)) + .aggregate(builder.groupKey(ImmutableBitSet.of(17), null)) .build(); fail("expected error, got " + root); } catch (IllegalArgumentException e) { @@ -663,7 +721,7 @@ public class RelBuilderTest { RelNode root = builder.scan("EMP") .aggregate( - builder.groupKey(ImmutableBitSet.of(7), true, + builder.groupKey(ImmutableBitSet.of(7), ImmutableList.of(ImmutableBitSet.of(4), ImmutableBitSet.of()))) .build(); @@ -679,17 +737,61 @@ public class RelBuilderTest { RelNode root = builder.scan("EMP") .aggregate( - builder.groupKey(ImmutableBitSet.of(7, 6), true, + builder.groupKey(ImmutableBitSet.of(7, 6), ImmutableList.of(ImmutableBitSet.of(7), ImmutableBitSet.of(6), ImmutableBitSet.of(7)))) .build(); final String expected = "" - + "LogicalAggregate(group=[{6, 7}], groups=[[{6}, {7}]], indicator=[true])\n" + + "LogicalAggregate(group=[{6, 7}], groups=[[{6}, {7}]])\n" + " LogicalTableScan(table=[[scott, EMP]])\n"; assertThat(str(root), is(expected)); } + @Test public void testAggregateGrouping() { + final RelBuilder builder = RelBuilder.create(config().build()); + RelNode root = + builder.scan("EMP") + .aggregate(builder.groupKey(6, 7), + builder.aggregateCall(SqlStdOperatorTable.GROUPING, false, null, + "g", builder.field("DEPTNO"))) + .build(); + final String expected = "" + + "LogicalAggregate(group=[{6, 7}], g=[GROUPING($7)])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n"; + assertThat(str(root), is(expected)); + } + + @Test public void testAggregateGroupingWithDistinctFails() { + final RelBuilder builder = RelBuilder.create(config().build()); + try { + RelNode root = + builder.scan("EMP") + .aggregate(builder.groupKey(6, 7), + builder.aggregateCall(SqlStdOperatorTable.GROUPING, true, null, + "g", builder.field("DEPTNO"))) + .build(); + fail("expected error, got " + root); + } catch (IllegalArgumentException e) { + assertThat(e.getMessage(), is("DISTINCT not allowed")); + } + } + + @Test public void testAggregateGroupingWithFilterFails() { + final RelBuilder builder = RelBuilder.create(config().build()); + try { + RelNode root = + builder.scan("EMP") + .aggregate(builder.groupKey(6, 7), + builder.aggregateCall(SqlStdOperatorTable.GROUPING, false, + builder.literal(true), "g", builder.field("DEPTNO"))) + .build(); + fail("expected error, got " + root); + } catch (IllegalArgumentException e) { + assertThat(e.getMessage(), is("FILTER not allowed")); + } + } + @Test public void testDistinct() { // Equivalent SQL: // SELECT DISTINCT deptno http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java index cf0c0e8..115da8e 100644 --- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java @@ -1280,7 +1280,7 @@ public class RelMetadataTest extends SqlToRelTestBase { // Aggregate final LogicalAggregate aggregate = - LogicalAggregate.create(join, false, ImmutableBitSet.of(2, 0), + LogicalAggregate.create(join, ImmutableBitSet.of(2, 0), ImmutableList.<ImmutableBitSet>of(), ImmutableList.of( AggregateCall.create( @@ -2162,7 +2162,7 @@ public class RelMetadataTest extends SqlToRelTestBase { + "group by grouping sets ((deptno), (ename, deptno))"; final Map<Class<? extends RelNode>, Integer> expected = new HashMap<>(); expected.put(TableScan.class, 1); - expected.put(Project.class, 3); + expected.put(Project.class, 2); expected.put(Aggregate.class, 1); checkNodeTypeCount(sql, expected); } http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 44e510e..95c47fc 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -5243,34 +5243,55 @@ public class SqlValidatorTest extends SqlValidatorTestCase { } @Test public void testGroupId() { + final String groupIdOnlyInAggregate = + "GROUP_ID operator may only occur in an aggregate query"; + final String groupIdWrongClause = + "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"; + sql("select deptno, group_id() from emp group by deptno").ok(); sql("select deptno, ^group_id^ as x from emp group by deptno") .fails("Column 'GROUP_ID' not found in any table"); sql("select deptno, ^group_id(deptno)^ from emp group by deptno") .fails("Invalid number of arguments to function 'GROUP_ID'\\. " + "Was expecting 0 arguments"); + // Oracle throws "GROUPING function only supported with GROUP BY CUBE or + // ROLLUP" + sql("select ^group_id()^ from emp") + .fails(groupIdOnlyInAggregate); sql("select deptno from emp order by ^group_id(deptno)^") - .fails("GROUP_ID operator may only occur in an aggregate query"); + .fails(groupIdOnlyInAggregate); + // Oracle throws "GROUPING function only supported with GROUP BY CUBE or + // ROLLUP" + sql("select 1 from emp order by ^group_id()^") + .fails(groupIdOnlyInAggregate); + sql("select 1 from emp order by ^grouping(deptno)^") + .fails("GROUPING operator may only occur in an aggregate query"); + // Oracle throws "group function is not allowed here" sql("select deptno from emp where ^group_id()^ = 1") - .fails("GROUP_ID operator may only occur in an aggregate query"); + .fails(groupIdOnlyInAggregate); + // Oracle throws "group function is not allowed here" + sql("select deptno from emp group by ^group_id()^") + .fails(groupIdWrongClause); sql("select deptno from emp where ^group_id()^ = 1 group by deptno") - .fails( - "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"); + .fails(groupIdWrongClause); sql("select deptno from emp group by deptno, ^group_id()^") - .fails( - "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"); + .fails(groupIdWrongClause); sql("select deptno from emp\n" + "group by grouping sets(deptno, ^group_id()^)") - .fails( - "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"); + .fails(groupIdWrongClause); sql("select deptno from emp\n" + "group by cube(empno, ^group_id()^)") - .fails( - "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"); + .fails(groupIdWrongClause); sql("select deptno from emp\n" + "group by rollup(empno, ^group_id()^)") - .fails( - "GROUP_ID operator may only occur in SELECT, HAVING or ORDER BY clause"); + .fails(groupIdWrongClause); + sql("select grouping(^group_id()^) from emp") + .fails(groupIdOnlyInAggregate); + // Oracle throws "not a GROUP BY expression" + sql("select grouping(^group_id()^) from emp group by deptno") + .fails(groupIdWrongClause); + sql("select ^grouping(sum(empno))^ from emp group by deptno") + .fails("Aggregate expressions cannot be nested"); } @Test public void testCubeGrouping() { http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index 2da30be..f575271 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -600,10 +600,12 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$2]) <Resource name="planAfter"> <![CDATA[ LogicalProject(EXPR$0=[$1], EXPR$1=[$2]) - LogicalAggregate(group=[{0}], EXPR$0=[COUNT($0)], EXPR$1=[MIN($1)]) - LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)]) - LogicalProject(DEPTNO=[$7], SAL=[$5]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], EXPR$0=[$1], EXPR$1=[CAST($2):INTEGER NOT NULL]) + LogicalAggregate(group=[{0}], EXPR$0=[COUNT($0) FILTER $2], EXPR$1=[MIN($1) FILTER $2]) + LogicalProject(DEPTNO=[$0], EXPR$1=[$1], $g_0=[=($2, 0)]) + LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)], $g=[GROUPING($0)]) + LogicalProject(DEPTNO=[$7], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -1089,9 +1091,9 @@ LogicalProject(DNAME=[$0], C=[$1]) <Resource name="planBefore"> <![CDATA[ LogicalProject(DDEPTNO=[$0], DNAME=[$1], C=[$2]) - LogicalProject(DDEPTNO=[CASE($2, null, $0)], DNAME=[CASE($3, null, $1)], C=[$4]) - LogicalFilter(condition=[=(CASE($3, null, $1), 'Charlie')]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], C=[COUNT()]) + LogicalProject(DDEPTNO=[$0], DNAME=[$1], C=[$2]) + LogicalFilter(condition=[=($1, 'Charlie')]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], C=[COUNT()]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> @@ -1115,21 +1117,19 @@ LogicalProject(DDEPTNO=[$0], DNAME=[$1], C=[$2]) <Resource name="planBefore"> <![CDATA[ LogicalProject(DNAME=[$0], DDEPTNO=[$1], C=[$2]) - LogicalProject(DNAME=[$0], DDEPTNO=[CASE($3, null, $1)], C=[$4]) - LogicalFilter(condition=[=($0, 'Charlie')]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], C=[COUNT()]) - LogicalProject(DNAME=[$1], DDEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalFilter(condition=[=($0, 'Charlie')]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], C=[COUNT()]) + LogicalProject(DNAME=[$1], DDEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ LogicalProject(DNAME=[$0], DDEPTNO=[$1], C=[$2]) - LogicalProject(DNAME=[$0], DDEPTNO=[CASE($3, null, $1)], C=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], C=[COUNT()]) - LogicalFilter(condition=[=($0, 'Charlie')]) - LogicalProject(DNAME=[$1], DDEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], C=[COUNT()]) + LogicalFilter(condition=[=($0, 'Charlie')]) + LogicalProject(DNAME=[$1], DDEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> </TestCase> @@ -3110,28 +3110,25 @@ group by rollup(deptno,job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[$9]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[$9]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[2]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[2]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3144,28 +3141,25 @@ group by rollup(deptno,job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[$9]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[null]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[null]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[$9]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[null]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], U=[null]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[null]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], U=[null]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[null]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], U=[null]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3179,28 +3173,25 @@ group by rollup(deptno, job) </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3213,28 +3204,25 @@ group by rollup(deptno, job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT()]) - LogicalProject(DEPTNO=[$7], JOB=[$2]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT()]) + LogicalProject(DEPTNO=[$7], JOB=[$2]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[$SUM0($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) - LogicalProject(DEPTNO=[$7], JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) - LogicalProject(DEPTNO=[$7], JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[$SUM0($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) + LogicalProject(DEPTNO=[$7], JOB=[$2]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) + LogicalProject(DEPTNO=[$7], JOB=[$2]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3247,28 +3235,25 @@ group by rollup(deptno, job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[$SUM0($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[$SUM0($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3281,28 +3266,25 @@ group by rollup(deptno, job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[MAX($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[MAX($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3315,28 +3297,25 @@ LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4 </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[MIN($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[MIN($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[MIN($2)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[MIN($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[MIN($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[MIN($2)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[MIN($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[MIN($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3349,26 +3328,23 @@ group by rollup(deptno, job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[AVG($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[AVG($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[AVG($2)]) - LogicalUnion(all=[true]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[AVG($2)]) + LogicalUnion(all=[true]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3381,28 +3357,25 @@ group by rollup(deptno,job)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4], EXPR$3=[$5], EXPR$4=[$6], EXPR$5=[$7]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4], EXPR$3=[$5], EXPR$4=[$6], EXPR$5=[$7]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalUnion(all=[true]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalUnion(all=[true]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4], EXPR$3=[$5], EXPR$4=[$6], EXPR$5=[$7]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[SUM($2)], EXPR$3=[$SUM0($3)], EXPR$4=[MIN($4)], EXPR$5=[MAX($5)]) - LogicalUnion(all=[true]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[SUM($2)], EXPR$3=[$SUM0($3)], EXPR$4=[MIN($4)], EXPR$5=[MAX($5)]) + LogicalUnion(all=[true]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[SUM($2)], EXPR$3=[COUNT()], EXPR$4=[MIN($0)], EXPR$5=[MAX($2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3470,21 +3443,19 @@ LogicalFilter(condition=[>($1, 5000)]) </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(ENAME=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null, $2)]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]], indicator=[true]) - LogicalFilter(condition=[>($1, 5000)]) - LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]]) + LogicalFilter(condition=[>($1, 5000)]) + LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(ENAME=[CASE($3, null, $0)], SAL=[CASE($4, null, $1)], DEPTNO=[CASE($5, null, $2)]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]], indicator=[true]) - LogicalFilter(condition=[>($1, 5000)]) - LogicalAggregate(group=[{0, 1, 2}]) - LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0}, {}]]) + LogicalFilter(condition=[>($1, 5000)]) + LogicalAggregate(group=[{0, 1, 2}]) + LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -4911,21 +4882,19 @@ group by rollup(x, y)]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(X=[$0], EXPR$1=[$4], Y=[$1]) - LogicalProject(X=[CASE($2, null, $0)], Y=[CASE($3, null, $1)], i$X=[$2], i$Y=[$3], EXPR$1=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$1=[SUM($2)]) - LogicalProject(X=[$0], Y=[$1], Z=[$2]) - LogicalProject(X=[$7], Y=[$0], Z=[$5], ZZ=[*($5, 2)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalProject(X=[$0], EXPR$1=[$2], Y=[$1]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$1=[SUM($2)]) + LogicalProject(X=[$0], Y=[$1], Z=[$2]) + LogicalProject(X=[$7], Y=[$0], Z=[$5], ZZ=[*($5, 2)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(X=[$0], EXPR$1=[$4], Y=[$1]) - LogicalProject(X=[CASE($2, null, $0)], Y=[CASE($3, null, $1)], i$X=[$2], i$Y=[$3], EXPR$1=[$4]) - LogicalProject(DEPTNO=[$1], EMPNO=[$0], i$DEPTNO=[$3], i$EMPNO=[$2], EXPR$1=[$4]) - LogicalAggregate(group=[{0, 7}], groups=[[{0, 7}, {7}, {}]], indicator=[true], EXPR$1=[SUM($5)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalProject(X=[$0], EXPR$1=[$2], Y=[$1]) + LogicalProject(DEPTNO=[$1], EMPNO=[$0], EXPR$1=[$2]) + LogicalAggregate(group=[{0, 7}], groups=[[{0, 7}, {7}, {}]], EXPR$1=[SUM($5)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -5084,10 +5053,10 @@ LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)], EXPR$2=[SUM($2)]) <Resource name="planAfter"> <![CDATA[ LogicalProject(DEPTNO=[$0], EXPR$1=[$1], EXPR$2=[CAST($2):INTEGER NOT NULL]) - LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $5], EXPR$2=[MIN($4) FILTER $6]) - LogicalProject(DEPTNO=[$0], ENAME=[$1], i$DEPTNO=[$2], i$ENAME=[$3], EXPR$2=[$4], $i0_1=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 3)], $i0=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 1)]) - LogicalProject(DEPTNO=[$1], ENAME=[$0], i$DEPTNO=[$3], i$ENAME=[$2], EXPR$2=[$4]) - LogicalAggregate(group=[{1, 7}], groups=[[{1, 7}, {7}]], indicator=[true], EXPR$2=[SUM($5)]) + LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $3], EXPR$2=[MIN($2) FILTER $4]) + LogicalProject(DEPTNO=[$0], ENAME=[$1], EXPR$2=[$2], $g_0=[=($3, 0)], $g_1=[=($3, 1)]) + LogicalProject(DEPTNO=[$1], ENAME=[$0], EXPR$2=[$2], $g=[$3]) + LogicalAggregate(group=[{1, 7}], groups=[[{1, 7}, {7}]], EXPR$2=[SUM($5)], $g=[GROUPING($7, $1)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -5098,18 +5067,16 @@ LogicalProject(DEPTNO=[$0], EXPR$1=[$1], EXPR$2=[CAST($2):INTEGER NOT NULL]) </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT(DISTINCT $2)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT(DISTINCT $2)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], indicator=[true], EXPR$2=[COUNT($2)]) - LogicalAggregate(group=[{0, 1, 2}]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT($2) FILTER $3]) + LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], $g_0=[=($3, 0)]) + LogicalAggregate(group=[{0, 1, 2}], $g=[GROUPING($0, $1, $2)]) LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> @@ -5121,19 +5088,17 @@ LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4 </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$4], EXPR$3=[$5]) - LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], i$DEPTNO=[$2], i$JOB=[$3], EXPR$2=[$4], EXPR$3=[$5]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT(DISTINCT $2)], EXPR$3=[SUM($3)]) - LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1], SAL=[$5]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT(DISTINCT $2)], EXPR$3=[SUM($3)]) + LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)], EXPR$2=[$4], EXPR$3=[CAST($5):INTEGER NOT NULL]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT($2) FILTER $7], EXPR$3=[MIN($6) FILTER $8]) - LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], i$DEPTNO=[$3], i$JOB=[$4], i$ENAME=[$5], EXPR$3=[$6], $i0_1_2=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 7)], $i0_1=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 3)]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}]], indicator=[true], EXPR$3=[SUM($3)]) +LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[$2], EXPR$3=[CAST($3):INTEGER NOT NULL]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT($2) FILTER $4], EXPR$3=[MIN($3) FILTER $5]) + LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], EXPR$3=[$3], $g_0=[=($4, 0)], $g_1=[=($4, 1)]) + LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}]], EXPR$3=[SUM($3)], $g=[GROUPING($0, $1, $2)]) LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1], SAL=[$5]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> @@ -5597,19 +5562,21 @@ group by e.deptno, d.deptno]]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalAggregate(group=[{7, 9}]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +LogicalProject(DEPTNO=[$0], DEPTNO0=[$1]) + LogicalAggregate(group=[{7, 9}]) + LogicalJoin(condition=[=($7, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ LogicalProject(DEPTNO=[$0], DEPTNO0=[$1]) - LogicalJoin(condition=[=($0, $1)], joinType=[inner]) - LogicalAggregate(group=[{7}]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO=[$0], DEPTNO0=[$1]) + LogicalJoin(condition=[=($0, $1)], joinType=[inner]) + LogicalAggregate(group=[{7}]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> </TestCase> @@ -5819,10 +5786,10 @@ LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)], EXPR$2=[COUNT(DISTINC </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $6], EXPR$2=[COUNT($2) FILTER $7]) - LogicalProject(DEPTNO=[$0], ENAME=[$1], JOB=[$2], i$DEPTNO=[$3], i$ENAME=[$4], i$JOB=[$5], $i0_1=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 3)], $i0_2=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 5)], $i0=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 1)]) - LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], i$DEPTNO=[$5], i$ENAME=[$3], i$JOB=[$4]) - LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 7}, {2, 7}, {7}]], indicator=[true]) +LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $3], EXPR$2=[COUNT($2) FILTER $4]) + LogicalProject(DEPTNO=[$0], ENAME=[$1], JOB=[$2], $g_1=[=($3, 1)], $g_2=[=($3, 2)]) + LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], $g=[$3]) + LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 7}, {2, 7}]], $g=[GROUPING($7, $1, $2)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -5841,9 +5808,9 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT(DISTINCT $0)], EXPR$1=[COUNT(DISTINCT </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $4], EXPR$1=[COUNT($1) FILTER $5]) - LogicalProject(ENAME=[$0], JOB=[$1], i$ENAME=[$2], i$JOB=[$3], $i0=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 1)], $i1=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 2)], $=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 0)]) - LogicalAggregate(group=[{1, 2}], groups=[[{1}, {2}, {}]], indicator=[true]) +LogicalAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $2], EXPR$1=[COUNT($1) FILTER $3]) + LogicalProject(ENAME=[$0], JOB=[$1], $g_1=[=($2, 1)], $g_2=[=($2, 2)]) + LogicalAggregate(group=[{1, 2}], groups=[[{1}, {2}]], $g=[GROUPING($1, $2)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -5863,9 +5830,9 @@ LogicalAggregate(group=[{0}], CDDJ=[COUNT(DISTINCT $0, $1)], S=[SUM($2)]) <Resource name="planAfter"> <![CDATA[ LogicalProject(DEPTNO=[$0], CDDJ=[$1], S=[CAST($2):INTEGER NOT NULL]) - LogicalAggregate(group=[{0}], CDDJ=[COUNT($0, $1) FILTER $5], S=[MIN($4) FILTER $6]) - LogicalProject(DEPTNO=[$0], JOB=[$1], i$DEPTNO=[$2], i$JOB=[$3], S=[$4], $i0_1=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 3)], $i0=[=(+(CASE($2, 0, 1), CASE($3, 0, 2)), 1)]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], S=[SUM($2)]) + LogicalAggregate(group=[{0}], CDDJ=[COUNT($0, $1) FILTER $3], S=[MIN($2) FILTER $4]) + LogicalProject(DEPTNO=[$0], JOB=[$1], S=[$2], $g_0=[=($3, 0)], $g_1=[=($3, 1)]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], S=[SUM($2)], $g=[GROUPING($0, $1)]) LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> @@ -5922,9 +5889,9 @@ LogicalAggregate(group=[{0}], CDE=[COUNT(DISTINCT $1)], CDJE=[COUNT(DISTINCT $2, <Resource name="planAfter"> <![CDATA[ LogicalProject(DEPTNO=[$0], CDE=[$1], CDJE=[$2], CDDJ=[$3], S=[CAST($4):INTEGER NOT NULL]) - LogicalAggregate(group=[{0}], CDE=[COUNT($1) FILTER $8], CDJE=[COUNT($2, $1) FILTER $7], CDDJ=[COUNT($0, $2) FILTER $9], S=[MIN($6) FILTER $10]) - LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], i$DEPTNO=[$5], i$ENAME=[$3], i$JOB=[$4], S=[$6], $i0_1_2=[=(+(+(CASE($5, 0, 1), CASE($3, 0, 2)), CASE($4, 0, 4)), 7)], $i0_1=[=(+(+(CASE($5, 0, 1), CASE($3, 0, 2)), CASE($4, 0, 4)), 3)], $i0_2=[=(+(+(CASE($5, 0, 1), CASE($3, 0, 2)), CASE($4, 0, 4)), 5)], $i0=[=(+(+(CASE($5, 0, 1), CASE($3, 0, 2)), CASE($4, 0, 4)), 1)]) - LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 2, 7}, {1, 7}, {2, 7}, {7}]], indicator=[true], S=[SUM($5)]) + LogicalAggregate(group=[{0}], CDE=[COUNT($1) FILTER $5], CDJE=[COUNT($2, $1) FILTER $4], CDDJ=[COUNT($0, $2) FILTER $6], S=[MIN($3) FILTER $7]) + LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], S=[$3], $g_0=[=($4, 0)], $g_1=[=($4, 1)], $g_2=[=($4, 2)], $g_3=[=($4, 3)]) + LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 2, 7}, {1, 7}, {2, 7}, {7}]], S=[SUM($5)], $g=[GROUPING($7, $1, $2)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -7488,9 +7455,9 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM(DISTINCT $1)], EXPR$2=[SUM(DISTINCT $2 <Resource name="planAfter"> <![CDATA[ LogicalProject(NAME=[$0], EXPR$1=[CAST($1):BIGINT NOT NULL], EXPR$2=[CAST($2):INTEGER NOT NULL]) - LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $6], EXPR$2=[SUM($2) FILTER $7]) - LogicalProject(NAME=[$0], CN=[$1], SM=[$2], i$NAME=[$3], i$CN=[$4], i$SM=[$5], $i0_1=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 3)], $i0_2=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 5)], $i0=[=(+(+(CASE($3, 0, 1), CASE($4, 0, 2)), CASE($5, 0, 4)), 1)]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}, {0}]], indicator=[true]) + LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $3], EXPR$2=[SUM($2) FILTER $4]) + LogicalProject(NAME=[$0], CN=[$1], SM=[$2], $g_1=[=($3, 1)], $g_2=[=($3, 2)]) + LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}]], $g=[GROUPING($0, $1, $2)]) LogicalAggregate(group=[{0}], CN=[COUNT()], SM=[SUM($1)]) LogicalProject(NAME=[$1], DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) http://git-wip-us.apache.org/repos/asf/calcite/blob/1e7ae1c3/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- 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 2e8f904..41fce01 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -2174,11 +2174,9 @@ order by 2]]> <Resource name="plan"> <![CDATA[ LogicalSort(sort0=[$1], dir0=[ASC]) - LogicalProject(DEPTNO=[$0], ENAME=[$1], EXPR$2=[$4]) - LogicalProject(DEPTNO=[$0], ENAME=[CASE($3, null, $1)], i$DEPTNO=[$2], i$ENAME=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], EXPR$2=[SUM($2)]) - LogicalProject(DEPTNO=[$7], ENAME=[$1], SAL=[$5]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$2=[SUM($2)]) + LogicalProject(DEPTNO=[$7], ENAME=[$1], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -2193,11 +2191,10 @@ group by sal, </Resource> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[$6]) - LogicalProject(SAL=[$0], DEPTNO=[CASE($4, null, $1)], ENAME=[CASE($5, null, $2)], i$SAL=[$3], i$DEPTNO=[$4], i$ENAME=[$5], EXPR$0=[$6]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0, 2}]], indicator=[true], EXPR$0=[SUM($0)]) - LogicalProject(SAL=[$5], DEPTNO=[$7], ENAME=[$1]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalProject(EXPR$0=[$3]) + LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0, 2}]], EXPR$0=[SUM($0)]) + LogicalProject(SAL=[$5], DEPTNO=[$7], ENAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -2210,9 +2207,8 @@ group by grouping sets (a, b), grouping sets (c, d)]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7]) - LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 2}, {0, 3}, {1, 2}, {1, 3}]], indicator=[true]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 2}, {0, 3}, {1, 2}, {1, 3}]]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2225,9 +2221,8 @@ group by grouping sets (a, (a, b)), grouping sets (c), d]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[$0], EXPR$1=[CASE($5, null, $1)], EXPR$2=[$2], EXPR$3=[$3], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7]) - LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 2, 3}]], indicator=[true]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 2, 3}]]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2240,9 +2235,8 @@ group by rollup(a, b), rollup(c, d)]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7]) - LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2255,10 +2249,9 @@ group by cube(a, b)]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[CASE($2, null, $0)], EXPR$1=[CASE($3, null, $1)], i$EXPR$0=[$2], i$EXPR$1=[$3]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], indicator=[true]) - LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]]) + LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2271,10 +2264,9 @@ group by rollup(b, (a, d))]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$1=[CASE($3, null, $0)], EXPR$0=[CASE($4, null, $1)], EXPR$3=[CASE($5, null, $2)], i$EXPR$1=[$3], i$EXPR$0=[$4], i$EXPR$3=[$5]) - LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0}, {}]], indicator=[true]) - LogicalProject(EXPR$1=[$1], EXPR$0=[$0], EXPR$3=[$3]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0}, {}]]) + LogicalProject(EXPR$1=[$1], EXPR$0=[$0], EXPR$3=[$3]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2287,9 +2279,8 @@ group by rollup(a, b), rollup(c, d)]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7]) - LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true]) - LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]]) + LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2301,11 +2292,9 @@ group by rollup(a, b)]]> </Resource> <Resource name="plan"> <![CDATA[ -LogicalProject(A=[$0], B=[$1], C=[$4]) - LogicalProject(A=[CASE($2, null, $0)], B=[CASE($3, null, $1)], i$A=[$2], i$B=[$3], C=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], C=[COUNT()]) - LogicalProject(A=[null], B=[2]) - LogicalValues(tuples=[[{ 0 }]]) +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], C=[COUNT()]) + LogicalProject(A=[null], B=[2]) + LogicalValues(tuples=[[{ 0 }]]) ]]> </Resource> </TestCase> @@ -2333,9 +2322,8 @@ group by grouping sets ((a, b), c), grouping sets ((x, y), ())]]> <Resource name="plan"> <![CDATA[ LogicalProject(EXPR$0=[1]) - LogicalProject(EXPR$0=[CASE($5, null, $0)], EXPR$1=[CASE($6, null, $1)], EXPR$2=[CASE($7, null, $2)], EXPR$3=[CASE($8, null, $3)], EXPR$4=[CASE($9, null, $4)], i$EXPR$0=[$5], i$EXPR$1=[$6], i$EXPR$2=[$7], i$EXPR$3=[$8], i$EXPR$4=[$9]) - LogicalAggregate(group=[{0, 1, 2, 3, 4}], groups=[[{0, 1, 3, 4}, {0, 1}, {2, 3, 4}, {2}]], indicator=[true]) - LogicalValues(tuples=[[{ 0, 1, 2, 3, 4 }]]) + LogicalAggregate(group=[{0, 1, 2, 3, 4}], groups=[[{0, 1, 3, 4}, {0, 1}, {2, 3, 4}, {2}]]) + LogicalValues(tuples=[[{ 0, 1, 2, 3, 4 }]]) ]]> </Resource> </TestCase> @@ -2350,8 +2338,8 @@ order by 2]]> <Resource name="plan"> <![CDATA[ LogicalSort(sort0=[$1], dir0=[ASC]) - LogicalProject(DEPTNO=[$1], EXPR$1=[1], EXPR$2=[$2], EXPR$3=[1]) - LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) + LogicalProject(DEPTNO=[$1], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4]) + LogicalAggregate(group=[{0, 1}], EXPR$1=[GROUPING($1)], EXPR$2=[COUNT()], EXPR$3=[GROUPING($0)]) LogicalProject(EMPNO=[$0], DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> @@ -2366,11 +2354,10 @@ group by rollup(empno, deptno)]]> </Resource> <Resource name="plan"> <![CDATA[ -LogicalProject(DEPTNO=[$1], EXPR$1=[CASE($3, 1, 0)], EXPR$2=[$4], EXPR$3=[CASE($2, 1, 0)]) - LogicalProject(EMPNO=[CASE($2, null, $0)], DEPTNO=[CASE($3, null, $1)], i$EMPNO=[$2], i$DEPTNO=[$3], EXPR$2=[$4]) - LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT()]) - LogicalProject(EMPNO=[$0], DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalProject(DEPTNO=[$1], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$1=[GROUPING($1)], EXPR$2=[COUNT()], EXPR$3=[GROUPING($0)]) + LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -2726,9 +2713,10 @@ GROUP BY empno, EXPR$2]]> </Resource> <Resource name="plan"> <![CDATA[ -LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) - LogicalProject(EMPNO=[$0], EXPR$2=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +LogicalProject(EMPNO=[$0], EXPR$2=[$1], EXPR$20=[$2]) + LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()]) + LogicalProject(EMPNO=[$0], EXPR$2=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -2848,9 +2836,10 @@ group by hop(rowtime, interval '1' hour, interval '3' hour)]]> <Resource name="plan"> <![CDATA[ LogicalDelta - LogicalAggregate(group=[{0}], C=[COUNT()]) - LogicalProject($f0=[HOP($0, 3600000, 10800000)]) - LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) + LogicalProject(ROWTIME=[$0], C=[$1]) + LogicalAggregate(group=[{0}], C=[COUNT()]) + LogicalProject($f0=[HOP($0, 3600000, 10800000)]) + LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) ]]> </Resource> </TestCase>
