You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/03/27 22:25:40 UTC
[calcite] 02/05: [CALCITE-2796] JDBC adapter should convert 'GROUP
BY ROLLUP(x, y)' to 'GROUP BY x, y WITH ROLLUP' for MySQL 5
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 <jh...@apache.org>
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);
}
/**