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 1f4b61989faec2a4b5a4a8eac58cd621843cf2b3 Author: Julian Hyde <[email protected]> AuthorDate: Tue Jan 22 10:44:29 2019 -0800 [CALCITE-2796] JDBC adapter should convert 'GROUP BY ROLLUP(x, y)' to 'GROUP BY x, y WITH ROLLUP' for MySQL 5 Add 'Util.select(List<E>, List<Integer>)'. In Aggregate.Group, broaden patterns of grouping sets that are considered a "rollup". Previously [{}, {0}, {0,1}] would have been considered a rollup, but [{}, {1}, {0, 1}] would not, because of bit order. Now they are both considered rollups. Add Group.getRollup(), which generates the sequence of bits, for example [0, 1] and [1, 0] for the previous examples. --- .../java/org/apache/calcite/plan/RelOptUtil.java | 11 +- .../org/apache/calcite/rel/RelFieldCollation.java | 3 +- .../org/apache/calcite/rel/core/Aggregate.java | 70 ++++++++-- .../calcite/rel/rel2sql/RelToSqlConverter.java | 135 +++++++++++++++++-- .../java/org/apache/calcite/sql/SqlDialect.java | 39 +++++- .../calcite/sql/dialect/MysqlSqlDialect.java | 11 ++ .../apache/calcite/sql/fun/SqlRollupOperator.java | 18 +++ .../java/org/apache/calcite/tools/Programs.java | 6 + .../java/org/apache/calcite/tools/RelBuilder.java | 6 +- .../main/java/org/apache/calcite/util/Util.java | 14 ++ .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 145 +++++++++++++++++++++ .../calcite/test/InduceGroupingTypeTest.java | 74 +++++++++-- .../java/org/apache/calcite/util/UtilTest.java | 34 +++++ .../calcite/adapter/geode/rel/GeodeRules.java | 14 +- 14 files changed, 520 insertions(+), 60 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java index c7f9048..52a9ef2 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java @@ -3014,16 +3014,7 @@ public abstract class RelOptUtil { return relBuilder.getRexBuilder().makeInputRef(child, pos); } }; - final List<String> names = new AbstractList<String>() { - public int size() { - return posList.size(); - } - - public String get(int index) { - final int pos = posList.get(index); - return fieldNames.get(pos); - } - }; + final List<String> names = Util.select(fieldNames, posList); return relBuilder .push(child) .projectNamed(exprs, names, false) diff --git a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java index f837474..fd897d6 100644 --- a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java +++ b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java @@ -19,6 +19,7 @@ package org.apache.calcite.rel; import org.apache.calcite.sql.validate.SqlMonotonicity; import java.util.Objects; +import javax.annotation.Nonnull; /** * Definition of the ordering of one field of a {@link RelNode} whose @@ -125,7 +126,7 @@ public class RelFieldCollation { /** Returns the null direction if not specified. Consistent with Oracle, * NULLS are sorted as if they were positive infinity. */ - public NullDirection defaultNullDirection() { + public @Nonnull NullDirection defaultNullDirection() { switch (this) { case ASCENDING: case STRICTLY_ASCENDING: diff --git a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java index 16dc5fa..82b4f70 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java @@ -48,6 +48,7 @@ import com.google.common.collect.ImmutableList; import com.google.common.math.IntMath; import java.util.HashSet; +import java.util.LinkedHashSet; import java.util.List; import java.util.Objects; import java.util.Set; @@ -476,20 +477,69 @@ public abstract class Aggregate extends SingleRel { if (groupSets.size() == IntMath.pow(2, groupSet.cardinality())) { return CUBE; } - checkRollup: - if (groupSets.size() == groupSet.cardinality() + 1) { - ImmutableBitSet g = groupSet; - for (ImmutableBitSet bitSet : groupSets) { - if (!bitSet.equals(g)) { - break checkRollup; - } - g = g.clear(g.length() - 1); - } - assert g.isEmpty(); + if (isRollup(groupSet, groupSets)) { return ROLLUP; } return OTHER; } + + /** Returns whether a list of sets is a rollup. + * + * <p>For example, if {@code groupSet} is <code>{2, 4, 5}</code>, then + * <code>[{2, 4, 5], {2, 5}, {5}, {}]</code> is a rollup. The first item is + * equal to {@code groupSet}, and each subsequent item is a subset with one + * fewer bit than the previous. + * + * @see #getRollup(List) */ + public static boolean isRollup(ImmutableBitSet groupSet, + List<ImmutableBitSet> groupSets) { + if (groupSets.size() != groupSet.cardinality() + 1) { + return false; + } + ImmutableBitSet g = null; + for (ImmutableBitSet bitSet : groupSets) { + if (g == null) { + // First item must equal groupSet + if (!bitSet.equals(groupSet)) { + return false; + } + } else { + // Each subsequent items must be a subset with one fewer bit than the + // previous item + if (!g.contains(bitSet) + || g.except(bitSet).cardinality() != 1) { + return false; + } + } + g = bitSet; + } + assert g.isEmpty(); + return true; + } + + /** Returns the ordered list of bits in a rollup. + * + * <p>For example, given a {@code groupSets} value + * <code>[{2, 4, 5], {2, 5}, {5}, {}]</code>, returns the list + * {@code [5, 2, 4]}, which are the succession of bits + * added to each of the sets starting with the empty set. + * + * @see #isRollup(ImmutableBitSet, List) */ + public static List<Integer> getRollup(List<ImmutableBitSet> groupSets) { + final Set<Integer> set = new LinkedHashSet<>(); + ImmutableBitSet g = null; + for (ImmutableBitSet bitSet : groupSets) { + if (g == null) { + // First item must equal groupSet + } else { + // Each subsequent items must be a subset with one fewer bit than the + // previous item + set.addAll(g.except(bitSet).toList()); + } + g = bitSet; + } + return ImmutableList.copyOf(set).reverse(); + } } //~ Inner Classes ---------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 1e46fd6..496a65c 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -17,6 +17,8 @@ package org.apache.calcite.rel.rel2sql; import org.apache.calcite.linq4j.tree.Expressions; +import org.apache.calcite.rel.RelCollation; +import org.apache.calcite.rel.RelCollations; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; @@ -35,6 +37,8 @@ import org.apache.calcite.rel.core.TableModify; import org.apache.calcite.rel.core.TableScan; import org.apache.calcite.rel.core.Union; import org.apache.calcite.rel.core.Values; +import org.apache.calcite.rel.logical.LogicalProject; +import org.apache.calcite.rel.logical.LogicalSort; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexLiteral; @@ -50,6 +54,7 @@ import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlInsert; import org.apache.calcite.sql.SqlIntervalLiteral; import org.apache.calcite.sql.SqlJoin; +import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlMatchRecognize; import org.apache.calcite.sql.SqlNode; @@ -62,6 +67,7 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.validate.SqlValidatorUtil; import org.apache.calcite.util.Pair; +import org.apache.calcite.util.Permutation; import org.apache.calcite.util.ReflectUtil; import org.apache.calcite.util.ReflectiveVisitor; @@ -69,14 +75,18 @@ import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; import com.google.common.collect.Iterables; import com.google.common.collect.Lists; +import com.google.common.collect.Ordering; import java.util.ArrayDeque; import java.util.ArrayList; import java.util.Deque; +import java.util.LinkedHashSet; import java.util.List; import java.util.Locale; import java.util.Map; +import java.util.Set; import java.util.SortedSet; +import java.util.stream.Collectors; /** * Utility to convert relational expressions to SQL abstract syntax tree. @@ -193,6 +203,10 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Aggregate e) { + return visitAggregate(e, e.getGroupSet().toList()); + } + + private Result visitAggregate(Aggregate e, List<Integer> groupKeyList) { // "select a, b, sum(x) from ( ... ) group by a, b" final Result x = visitChild(0, e.getInput()); final Builder builder; @@ -202,18 +216,13 @@ public class RelToSqlConverter extends SqlImplementor } else { builder = x.builder(e, Clause.GROUP_BY); } - List<SqlNode> groupByList = Expressions.list(); final List<SqlNode> selectList = new ArrayList<>(); - for (int group : e.getGroupSet()) { - final SqlNode field = builder.context.field(group); - addSelect(selectList, field, e.getRowType()); - groupByList.add(field); - } + final List<SqlNode> groupByList = + generateGroupList(builder, selectList, e, groupKeyList); for (AggregateCall aggCall : e.getAggCallList()) { SqlNode aggCallSqlNode = builder.context.toSql(aggCall); if (aggCall.getAggregation() instanceof SqlSingleValueAggFunction) { - aggCallSqlNode = dialect. - rewriteSingleValueExpr(aggCallSqlNode); + aggCallSqlNode = dialect.rewriteSingleValueExpr(aggCallSqlNode); } addSelect(selectList, aggCallSqlNode, e.getRowType()); } @@ -226,6 +235,60 @@ public class RelToSqlConverter extends SqlImplementor return builder.result(); } + /** Generates the GROUP BY items, for example {@code GROUP BY x, y}, + * {@code GROUP BY CUBE (x, y)} or {@code GROUP BY ROLLUP (x, y)}. + * + * <p>Also populates the SELECT clause. If the GROUP BY list is simple, the + * SELECT will be identical; if the GROUP BY list contains GROUPING SETS, + * CUBE or ROLLUP, the SELECT clause will contain the distinct leaf + * expressions. */ + private List<SqlNode> generateGroupList(Builder builder, + List<SqlNode> selectList, Aggregate aggregate, List<Integer> groupList) { + final List<Integer> sortedGroupList = + Ordering.natural().sortedCopy(groupList); + assert aggregate.getGroupSet().asList().equals(sortedGroupList) + : "groupList " + groupList + " must be equal to groupSet " + + aggregate.getGroupSet() + ", just possibly a different order"; + + final List<SqlNode> groupKeys = new ArrayList<>(); + for (int key : groupList) { + final SqlNode field = builder.context.field(key); + groupKeys.add(field); + } + for (int key : sortedGroupList) { + final SqlNode field = builder.context.field(key); + addSelect(selectList, field, aggregate.getRowType()); + } + switch (aggregate.getGroupType()) { + case SIMPLE: + return ImmutableList.copyOf(groupKeys); + case CUBE: + if (aggregate.getGroupSet().cardinality() > 1) { + return ImmutableList.of( + SqlStdOperatorTable.CUBE.createCall(SqlParserPos.ZERO, groupKeys)); + } + // a singleton CUBE and ROLLUP are the same but we prefer ROLLUP; + // fall through + case ROLLUP: + return ImmutableList.of( + SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO, groupKeys)); + default: + case OTHER: + return ImmutableList.of( + SqlStdOperatorTable.GROUPING_SETS.createCall(SqlParserPos.ZERO, + aggregate.getGroupSets().stream() + .map(groupSet -> + new SqlNodeList( + groupSet.asList().stream() + .map(key -> + groupKeys.get(aggregate.getGroupSet() + .indexOf(key))) + .collect(Collectors.toList()), + SqlParserPos.ZERO)) + .collect(Collectors.toList()))); + } + } + /** @see #dispatch */ public Result visit(TableScan e) { final SqlIdentifier identifier = @@ -341,6 +404,43 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Sort e) { + if (e.getInput() instanceof Aggregate) { + final Aggregate aggregate = (Aggregate) e.getInput(); + if (hasTrickyRollup(e, aggregate)) { + // MySQL 5 does not support standard "GROUP BY ROLLUP(x, y)", only + // the non-standard "GROUP BY x, y WITH ROLLUP". + // It does not allow "WITH ROLLUP" in combination with "ORDER BY", + // but "GROUP BY x, y WITH ROLLUP" implicitly sorts by x, y, + // so skip the ORDER BY. + final Set<Integer> groupList = new LinkedHashSet<>(); + for (RelFieldCollation fc : e.collation.getFieldCollations()) { + groupList.add(aggregate.getGroupSet().nth(fc.getFieldIndex())); + } + groupList.addAll(Aggregate.Group.getRollup(aggregate.getGroupSets())); + return offsetFetch(e, + visitAggregate(aggregate, ImmutableList.copyOf(groupList))); + } + } + if (e.getInput() instanceof Project) { + // Deal with the case Sort(Project(Aggregate ...)) + // by converting it to Project(Sort(Aggregate ...)). + final Project project = (Project) e.getInput(); + final Permutation permutation = project.getPermutation(); + if (permutation != null + && project.getInput() instanceof Aggregate) { + final Aggregate aggregate = (Aggregate) project.getInput(); + if (hasTrickyRollup(e, aggregate)) { + final RelCollation collation = + RelCollations.permute(e.collation, permutation); + final Sort sort2 = + LogicalSort.create(aggregate, collation, e.offset, e.fetch); + final Project project2 = + LogicalProject.create(sort2, project.getProjects(), + project.getRowType()); + return visit(project2); + } + } + } Result x = visitChild(0, e.getInput()); Builder builder = x.builder(e, Clause.ORDER_BY); List<SqlNode> orderByList = Expressions.list(); @@ -351,19 +451,34 @@ public class RelToSqlConverter extends SqlImplementor builder.setOrderBy(new SqlNodeList(orderByList, POS)); x = builder.result(); } + x = offsetFetch(e, x); + return x; + } + + Result offsetFetch(Sort e, Result x) { if (e.fetch != null) { - builder = x.builder(e, Clause.FETCH); + final Builder builder = x.builder(e, Clause.FETCH); builder.setFetch(builder.context.toSql(null, e.fetch)); x = builder.result(); } if (e.offset != null) { - builder = x.builder(e, Clause.OFFSET); + final Builder builder = x.builder(e, Clause.OFFSET); builder.setOffset(builder.context.toSql(null, e.offset)); x = builder.result(); } return x; } + public boolean hasTrickyRollup(Sort e, Aggregate aggregate) { + return !dialect.supportsAggregateFunction(SqlKind.ROLLUP) + && dialect.supportsGroupByWithRollup() + && (aggregate.getGroupType() == Aggregate.Group.ROLLUP + || aggregate.getGroupType() == Aggregate.Group.CUBE + && aggregate.getGroupSet().cardinality() == 1) + && e.collation.getFieldCollations().stream().allMatch(fc -> + fc.getFieldIndex() < aggregate.getGroupSet().cardinality()); + } + /** @see #dispatch */ public Result visit(TableModify modify) { final Map<String, RelDataType> pairs = ImmutableMap.of(); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 231a9a4..de8c702 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -856,6 +856,41 @@ public class SqlDialect { return true; } + /** + * Returns whether this dialect supports "WITH ROLLUP" in the "GROUP BY" + * clause. + * + * <p>For instance, in MySQL version 5, + * + * <blockquote> + * <code> + * SELECT deptno, job, COUNT(*) AS c + * FROM emp + * GROUP BY deptno, job WITH ROLLUP + * </code> + * </blockquote> + * + * <p>is equivalent to standard SQL + * + * <blockquote> + * <code> + * SELECT deptno, job, COUNT(*) AS c + * FROM emp + * GROUP BY ROLLUP(deptno, job) + * ORDER BY deptno, job + * </code> + * </blockquote> + * + * <p>The "WITH ROLLUP" clause was introduced in MySQL and is not standard + * SQL. + * + * <p>See also {@link #supportsAggregateFunction(SqlKind)} applied to + * {@link SqlKind#ROLLUP}, which returns true in MySQL 8 and higher. + */ + public boolean supportsGroupByWithRollup() { + return false; + } + /** Returns how NULL values are sorted if an ORDER BY item does not contain * NULLS ASCENDING or NULLS DESCENDING. */ public NullCollation getNullCollation() { @@ -864,7 +899,7 @@ public class SqlDialect { /** Returns whether NULL values are sorted first or last, in this dialect, * in an ORDER BY item of a given direction. */ - public RelFieldCollation.NullDirection defaultNullDirection( + public @Nonnull RelFieldCollation.NullDirection defaultNullDirection( RelFieldCollation.Direction direction) { switch (direction) { case ASCENDING: @@ -1060,7 +1095,7 @@ public class SqlDialect { String identifierQuoteString(); Context withIdentifierQuoteString(String identifierQuoteString); @Nonnull NullCollation nullCollation(); - Context withNullCollation(@Nonnull NullCollation nullCollation); + @Nonnull Context withNullCollation(@Nonnull NullCollation nullCollation); @Nonnull RelDataTypeSystem dataTypeSystem(); Context withDataTypeSystem(@Nonnull RelDataTypeSystem dataTypeSystem); JethroDataSqlDialect.JethroInfo jethroInfo(); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java index 6446386..0ac80f3 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java @@ -58,9 +58,12 @@ public class MysqlSqlDialect extends SqlDialect { ReturnTypes.BOOLEAN, InferTypes.FIRST_KNOWN, OperandTypes.ANY, SqlFunctionCategory.SYSTEM); + private final int majorVersion; + /** Creates a MysqlSqlDialect. */ public MysqlSqlDialect(Context context) { super(context); + majorVersion = context.databaseMajorVersion(); } @Override public boolean supportsCharSet() { @@ -86,6 +89,10 @@ public class MysqlSqlDialect extends SqlDialect { case MAX: case SINGLE_VALUE: return true; + case ROLLUP: + // MySQL 5 does not support standard "GROUP BY ROLLUP(x, y)", + // only the non-standard "GROUP BY x, y WITH ROLLUP". + return majorVersion >= 8; } return false; } @@ -94,6 +101,10 @@ public class MysqlSqlDialect extends SqlDialect { return false; } + @Override public boolean supportsGroupByWithRollup() { + return true; + } + @Override public CalendarPolicy getCalendarPolicy() { return CalendarPolicy.SHIFT; } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java index 09dd97c..1bffe06 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java @@ -34,6 +34,24 @@ class SqlRollupOperator extends SqlInternalOperator { @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { + switch (kind) { + case ROLLUP: + if (!writer.getDialect().supportsAggregateFunction(kind) + && writer.getDialect().supportsGroupByWithRollup()) { + // MySQL version 5: generate "GROUP BY x, y WITH ROLLUP". + // MySQL version 8 and higher is SQL-compliant, + // so generate "GROUP BY ROLLUP(x, y)" + final SqlWriter.Frame groupFrame = + writer.startList(SqlWriter.FrameTypeEnum.GROUP_BY_LIST); + for (SqlNode operand : call.getOperandList()) { + writer.sep(","); + operand.unparse(writer, 2, 3); + } + writer.endList(groupFrame); + writer.keyword("WITH ROLLUP"); + return; + } + } unparseCube(writer, call); } diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java b/core/src/main/java/org/apache/calcite/tools/Programs.java index f479aff..de5ae2a 100644 --- a/core/src/main/java/org/apache/calcite/tools/Programs.java +++ b/core/src/main/java/org/apache/calcite/tools/Programs.java @@ -194,6 +194,12 @@ public class Programs { list.add(metadataProvider); } hepPlanner.registerMetadataProviders(list); + for (RelOptMaterialization materialization : materializations) { + hepPlanner.addMaterialization(materialization); + } + for (RelOptLattice lattice : lattices) { + hepPlanner.addLattice(lattice); + } RelMetadataProvider plannerChain = ChainedRelMetadataProvider.of(list); rel.getCluster().setMetadataProvider(plannerChain); 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 7f85432..462d6cb 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -553,19 +553,19 @@ public class RelBuilder { } /** Creates a call to a scalar operator. */ - public RexNode call(SqlOperator operator, RexNode... operands) { + public @Nonnull RexNode call(SqlOperator operator, RexNode... operands) { return call(operator, ImmutableList.copyOf(operands)); } /** Creates a call to a scalar operator. */ - private RexNode call(SqlOperator operator, List<RexNode> operandList) { + private @Nonnull RexNode call(SqlOperator operator, List<RexNode> operandList) { final RexBuilder builder = cluster.getRexBuilder(); final RelDataType type = builder.deriveReturnType(operator, operandList); return builder.makeCall(type, operator, operandList); } /** Creates a call to a scalar operator. */ - public RexNode call(SqlOperator operator, + public @Nonnull RexNode call(SqlOperator operator, Iterable<? extends RexNode> operands) { return call(operator, ImmutableList.copyOf(operands)); } diff --git a/core/src/main/java/org/apache/calcite/util/Util.java b/core/src/main/java/org/apache/calcite/util/Util.java index b4b3148..d5c9920 100644 --- a/core/src/main/java/org/apache/calcite/util/Util.java +++ b/core/src/main/java/org/apache/calcite/util/Util.java @@ -2402,6 +2402,20 @@ public class Util { return new FilteringIterator<>(iterator, predicate); } + /** Returns a view of a list, picking the elements of a list with the given + * set of ordinals. */ + public static <E> List<E> select(List<E> list, List<Integer> ordinals) { + return new AbstractList<E>() { + @Override public int size() { + return ordinals.size(); + } + + @Override public E get(int index) { + return list.get(ordinals.get(index)); + } + }; + } + //~ Inner Classes ---------------------------------------------------------- /** diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index 9a79b50..378c405 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -217,6 +217,140 @@ public class RelToSqlConverterTest { .ok(expectedMySql); } + /** Tests GROUP BY ROLLUP of two columns. The SQL for MySQL has + * "GROUP BY ... ROLLUP" but no "ORDER BY". */ + @Test public void testSelectQueryWithGroupByRollup() { + final String query = "select \"product_class_id\", \"brand_name\"\n" + + "from \"product\"\n" + + "group by rollup(\"product_class_id\", \"brand_name\")\n" + + "order by 1, 2"; + final String expected = "SELECT \"product_class_id\", \"brand_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n" + + "ORDER BY \"product_class_id\", \"brand_name\""; + final String expectedMySql = "SELECT `product_class_id`, `brand_name`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP"; + final String expectedMySql8 = "SELECT `product_class_id`, `brand_name`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n" + + "ORDER BY `product_class_id` NULLS LAST, `brand_name` NULLS LAST"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql) + .withMysql8() + .ok(expectedMySql8); + } + + /** As {@link #testSelectQueryWithGroupByRollup()}, + * but ORDER BY columns reversed. */ + @Test public void testSelectQueryWithGroupByRollup2() { + final String query = "select \"product_class_id\", \"brand_name\"\n" + + "from \"product\"\n" + + "group by rollup(\"product_class_id\", \"brand_name\")\n" + + "order by 2, 1"; + final String expected = "SELECT \"product_class_id\", \"brand_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n" + + "ORDER BY \"brand_name\", \"product_class_id\""; + final String expectedMySql = "SELECT `product_class_id`, `brand_name`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `brand_name`, `product_class_id` WITH ROLLUP"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql); + } + + /** CUBE of one column is equivalent to ROLLUP, and Calcite recognizes + * this. */ + @Test public void testSelectQueryWithSingletonCube() { + final String query = "select \"product_class_id\", count(*) as c\n" + + "from \"product\"\n" + + "group by cube(\"product_class_id\")\n" + + "order by 1, 2"; + final String expected = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\")\n" + + "ORDER BY \"product_class_id\", COUNT(*)"; + final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_class_id` WITH ROLLUP\n" + + "ORDER BY `product_class_id` IS NULL, `product_class_id`," + + " COUNT(*) IS NULL, COUNT(*)"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql); + } + + /** As {@link #testSelectQueryWithSingletonCube()}, but no ORDER BY + * clause. */ + @Test public void testSelectQueryWithSingletonCubeNoOrderBy() { + final String query = "select \"product_class_id\", count(*) as c\n" + + "from \"product\"\n" + + "group by cube(\"product_class_id\")"; + final String expected = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\")"; + final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_class_id` WITH ROLLUP"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql); + } + + /** Cannot rewrite if ORDER BY contains a column not in GROUP BY (in this + * case COUNT(*)). */ + @Test public void testSelectQueryWithRollupOrderByCount() { + final String query = "select \"product_class_id\", \"brand_name\",\n" + + " count(*) as c\n" + + "from \"product\"\n" + + "group by rollup(\"product_class_id\", \"brand_name\")\n" + + "order by 1, 2, 3"; + final String expected = "SELECT \"product_class_id\", \"brand_name\"," + + " COUNT(*) AS \"C\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n" + + "ORDER BY \"product_class_id\", \"brand_name\", COUNT(*)"; + final String expectedMySql = "SELECT `product_class_id`, `brand_name`," + + " COUNT(*) AS `C`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP\n" + + "ORDER BY `product_class_id` IS NULL, `product_class_id`," + + " `brand_name` IS NULL, `brand_name`," + + " COUNT(*) IS NULL, COUNT(*)"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql); + } + + /** As {@link #testSelectQueryWithSingletonCube()}, but with LIMIT. */ + @Test public void testSelectQueryWithCubeLimit() { + final String query = "select \"product_class_id\", count(*) as c\n" + + "from \"product\"\n" + + "group by cube(\"product_class_id\")\n" + + "limit 5"; + final String expected = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY ROLLUP(\"product_class_id\")\n" + + "FETCH NEXT 5 ROWS ONLY"; + // If a MySQL 5 query has GROUP BY ... ROLLUP, you cannot add ORDER BY, + // but you can add LIMIT. + final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_class_id` WITH ROLLUP\n" + + "LIMIT 5"; + sql(query) + .ok(expected) + .withMysql() + .ok(expectedMySql); + } + @Test public void testSelectQueryWithMinAggregateFunction() { String query = "select min(\"net_weight\") from \"product\" group by \"product_class_id\" "; final String expected = "SELECT MIN(\"net_weight\")\n" @@ -3149,6 +3283,17 @@ public class RelToSqlConverterTest { return dialect(SqlDialect.DatabaseProduct.MYSQL.getDialect()); } + Sql withMysql8() { + final SqlDialect mysqlDialect = DatabaseProduct.MYSQL.getDialect(); + return dialect( + new SqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseProduct(DatabaseProduct.MYSQL) + .withDatabaseMajorVersion(8) + .withIdentifierQuoteString(mysqlDialect.quoteIdentifier("") + .substring(0, 1)) + .withNullCollation(mysqlDialect.getNullCollation()))); + } + Sql withOracle() { return dialect(SqlDialect.DatabaseProduct.ORACLE.getDialect()); } diff --git a/core/src/test/java/org/apache/calcite/test/InduceGroupingTypeTest.java b/core/src/test/java/org/apache/calcite/test/InduceGroupingTypeTest.java index de82061..59f8c6b 100644 --- a/core/src/test/java/org/apache/calcite/test/InduceGroupingTypeTest.java +++ b/core/src/test/java/org/apache/calcite/test/InduceGroupingTypeTest.java @@ -24,7 +24,9 @@ import org.junit.Test; import java.util.ArrayList; import java.util.List; +import static org.hamcrest.core.Is.is; import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertThat; import static org.junit.Assert.fail; /** @@ -36,18 +38,31 @@ public class InduceGroupingTypeTest { final ImmutableBitSet groupSet = ImmutableBitSet.of(1, 2, 4, 5); // SIMPLE - List<ImmutableBitSet> groupSets = new ArrayList<>(); + final List<ImmutableBitSet> groupSets = new ArrayList<>(); groupSets.add(groupSet); assertEquals(Aggregate.Group.SIMPLE, Aggregate.Group.induce(groupSet, groupSets)); + // CUBE (has only one bit, so could also be ROLLUP) + groupSets.clear(); + final ImmutableBitSet groupSet0 = ImmutableBitSet.of(2); + groupSets.add(groupSet0); + groupSets.add(ImmutableBitSet.of()); + assertEquals(Aggregate.Group.CUBE, + Aggregate.Group.induce(groupSet0, groupSets)); + assertThat(Aggregate.Group.isRollup(groupSet0, groupSets), is(true)); + assertThat(Aggregate.Group.getRollup(groupSets).toString(), + is("[2]")); + // CUBE - groupSets = ImmutableBitSet.ORDERING.sortedCopy(groupSet.powerSet()); + final List<ImmutableBitSet> groupSets0 = + ImmutableBitSet.ORDERING.sortedCopy(groupSet.powerSet()); assertEquals(Aggregate.Group.CUBE, - Aggregate.Group.induce(groupSet, groupSets)); + Aggregate.Group.induce(groupSet, groupSets0)); + assertThat(Aggregate.Group.isRollup(groupSet, groupSets0), is(false)); // ROLLUP - groupSets = new ArrayList<>(); + groupSets.clear(); groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); groupSets.add(ImmutableBitSet.of(1, 2, 4)); groupSets.add(ImmutableBitSet.of(1, 2)); @@ -55,9 +70,36 @@ public class InduceGroupingTypeTest { groupSets.add(ImmutableBitSet.of()); assertEquals(Aggregate.Group.ROLLUP, Aggregate.Group.induce(groupSet, groupSets)); + assertThat(Aggregate.Group.isRollup(groupSet, groupSets), is(true)); + assertThat(Aggregate.Group.getRollup(groupSets).toString(), + is("[1, 2, 4, 5]")); + + // ROLLUP, not removing bits in order + groupSets.clear(); + groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); + groupSets.add(ImmutableBitSet.of(1, 4, 5)); + groupSets.add(ImmutableBitSet.of(4, 5)); + groupSets.add(ImmutableBitSet.of(4)); + groupSets.add(ImmutableBitSet.of()); + assertEquals(Aggregate.Group.ROLLUP, + Aggregate.Group.induce(groupSet, groupSets)); + assertThat(Aggregate.Group.getRollup(groupSets).toString(), + is("[4, 5, 1, 2]")); + + // ROLLUP, removing bits in reverse order + groupSets.clear(); + groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); + groupSets.add(ImmutableBitSet.of(2, 4, 5)); + groupSets.add(ImmutableBitSet.of(4, 5)); + groupSets.add(ImmutableBitSet.of(5)); + groupSets.add(ImmutableBitSet.of()); + assertEquals(Aggregate.Group.ROLLUP, + Aggregate.Group.induce(groupSet, groupSets)); + assertThat(Aggregate.Group.getRollup(groupSets).toString(), + is("[5, 4, 2, 1]")); // OTHER - groupSets = new ArrayList<>(); + groupSets.clear(); groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); groupSets.add(ImmutableBitSet.of(1, 2, 4)); groupSets.add(ImmutableBitSet.of(1, 2)); @@ -65,7 +107,7 @@ public class InduceGroupingTypeTest { assertEquals(Aggregate.Group.OTHER, Aggregate.Group.induce(groupSet, groupSets)); - groupSets = new ArrayList<>(); + groupSets.clear(); groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); groupSets.add(ImmutableBitSet.of(1, 2, 4)); groupSets.add(ImmutableBitSet.of(1, 2)); @@ -73,7 +115,16 @@ public class InduceGroupingTypeTest { assertEquals(Aggregate.Group.OTHER, Aggregate.Group.induce(groupSet, groupSets)); - groupSets = new ArrayList<>(); + groupSets.clear(); + groupSets.add(ImmutableBitSet.of(1, 2, 4, 5)); + groupSets.add(ImmutableBitSet.of(1, 2, 4)); + groupSets.add(ImmutableBitSet.of(1, 2)); + groupSets.add(ImmutableBitSet.of(1, 4)); + groupSets.add(ImmutableBitSet.of()); + assertEquals(Aggregate.Group.OTHER, + Aggregate.Group.induce(groupSet, groupSets)); + + groupSets.clear(); groupSets.add(ImmutableBitSet.of(1, 2, 5)); groupSets.add(ImmutableBitSet.of(1, 2, 4)); groupSets.add(ImmutableBitSet.of(1, 2)); @@ -87,15 +138,16 @@ public class InduceGroupingTypeTest { // ok } - groupSets = ImmutableBitSet.ORDERING.sortedCopy(groupSets); + List<ImmutableBitSet> groupSets1 = + ImmutableBitSet.ORDERING.sortedCopy(groupSets); assertEquals(Aggregate.Group.OTHER, - Aggregate.Group.induce(groupSet, groupSets)); + Aggregate.Group.induce(groupSet, groupSets1)); - groupSets = new ArrayList<>(); + groupSets.clear(); assertEquals(Aggregate.Group.OTHER, Aggregate.Group.induce(groupSet, groupSets)); - groupSets = new ArrayList<>(); + groupSets.clear(); groupSets.add(ImmutableBitSet.of()); assertEquals(Aggregate.Group.OTHER, Aggregate.Group.induce(groupSet, groupSets)); diff --git a/core/src/test/java/org/apache/calcite/util/UtilTest.java b/core/src/test/java/org/apache/calcite/util/UtilTest.java index 2063fe2..bd9c28d 100644 --- a/core/src/test/java/org/apache/calcite/util/UtilTest.java +++ b/core/src/test/java/org/apache/calcite/util/UtilTest.java @@ -2395,6 +2395,40 @@ public class UtilTest { isIterable(Arrays.asList("John", "Paul", "Ringo"))); } + /** Tests {@link Util#select(List, List)}. */ + @Test public void testSelect() { + final List<String> beatles = + Arrays.asList("John", "Paul", "George", "Ringo"); + final List<String> nullBeatles = + Arrays.asList("John", "Paul", null, "Ringo"); + + final List<Integer> emptyOrdinals = Collections.emptyList(); + assertThat(Util.select(beatles, emptyOrdinals).isEmpty(), is(true)); + assertThat(Util.select(beatles, emptyOrdinals).toString(), is("[]")); + + final List<Integer> ordinal0 = Collections.singletonList(0); + assertThat(Util.select(beatles, ordinal0).isEmpty(), is(false)); + assertThat(Util.select(beatles, ordinal0).toString(), is("[John]")); + + final List<Integer> ordinal20 = Arrays.asList(2, 0); + assertThat(Util.select(beatles, ordinal20).isEmpty(), is(false)); + assertThat(Util.select(beatles, ordinal20).toString(), + is("[George, John]")); + + final List<Integer> ordinal232 = Arrays.asList(2, 3, 2); + assertThat(Util.select(beatles, ordinal232).isEmpty(), is(false)); + assertThat(Util.select(beatles, ordinal232).toString(), + is("[George, Ringo, George]")); + assertThat(Util.select(beatles, ordinal232), + isIterable(Arrays.asList("George", "Ringo", "George"))); + + assertThat(Util.select(nullBeatles, ordinal232).isEmpty(), is(false)); + assertThat(Util.select(nullBeatles, ordinal232).toString(), + is("[null, Ringo, null]")); + assertThat(Util.select(nullBeatles, ordinal232), + isIterable(Arrays.asList(null, "Ringo", null))); + } + @Test public void testEquivalenceSet() { final EquivalenceSet<String> c = new EquivalenceSet<>(); assertThat(c.size(), is(0)); diff --git a/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeRules.java b/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeRules.java index 9a87610..55cf161 100644 --- a/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeRules.java +++ b/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeRules.java @@ -39,7 +39,6 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.validate.SqlValidatorUtil; -import java.util.AbstractList; import java.util.ArrayList; import java.util.List; import java.util.function.Predicate; @@ -80,18 +79,7 @@ public class GeodeRules { } static List<String> geodeFieldNames(final RelDataType rowType) { - - List<String> fieldNames = new AbstractList<String>() { - @Override public String get(int index) { - return rowType.getFieldList().get(index).getName(); - } - - @Override public int size() { - return rowType.getFieldCount(); - } - }; - - return SqlValidatorUtil.uniquify(fieldNames, true); + return SqlValidatorUtil.uniquify(rowType.getFieldNames(), true); } /**
