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 2014/11/14 22:33:04 UTC
[56/58] [abbrv] incubator-calcite git commit: [CALCITE-370] Support
GROUPING SETS, CUBE, ROLLUP in SQL and algebra
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/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 1ada2df..f7036ab 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -934,7 +934,7 @@ Sort(sort0=[$1], dir0=[ASC])
</TestCase>
<TestCase name="testOrderByOrdinalDesc">
<Resource name="sql">
- <![CDATA[select empno + 1, deptno, empno from emp order by 2 desc]]>
+ <![CDATA[select empno + 1, deptno, empno from emp order by 2.5 desc]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -1148,9 +1148,9 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
</TestCase>
<TestCase name="testOverOrderWindow">
<Resource name="sql">
- <![CDATA[select last_value(deptno) over w
+ <![CDATA[select last_value(deptno) over (order by empno)
from emp
-window w as (order by empno)]]>
+]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -1161,9 +1161,9 @@ LogicalProject(EXPR$0=[LAST_VALUE($7) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED
</TestCase>
<TestCase name="testOverOrderFollowingWindow">
<Resource name="sql">
- <![CDATA[select last_value(deptno) over w
+ <![CDATA[select last_value(deptno) over (order by empno rows 2 following)
from emp
-window w as (order by empno rows 2 following)]]>
+]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -1977,4 +1977,155 @@ LogicalAggregate(group=[{0}], EXPR$1=[MAX($1)], EXPR$2=[MAX($2)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSingletonGroupingSet">
+ <Resource name="sql">
+ <![CDATA[select sum(sal) from emp group by grouping sets (deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$1])
+ LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupEmpty">
+ <Resource name="sql">
+ <![CDATA[select sum(deptno) from emp group by ()]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupByWithDuplicates">
+ <Resource name="sql">
+ <![CDATA[select sum(sal) from emp group by (), ()]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject(SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupingSets">
+ <Resource name="sql">
+ <![CDATA[select deptno, ename, sum(sal) from emp
+group by grouping sets ((deptno), (ename, deptno))
+order by 2]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+Sort(sort0=[$1], dir0=[ASC])
+ 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>
+ <TestCase name="testDuplicateGroupingSets">
+ <Resource name="sql">
+ <![CDATA[select sum(sal) from emp
+group by sal,
+ grouping sets (deptno,
+ grouping sets ((deptno, ename), ename),
+ (ename)),
+ ()]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+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>
+ <TestCase name="testGroupingSetsCartesianProduct">
+ <Resource name="sql">
+ <![CDATA[select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)
+group by grouping sets (a, b), grouping sets (c, d)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 2}, {0, 3}, {1, 2}, {1, 3}]])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupingSetsCartesianProduct2">
+ <Resource name="sql">
+ <![CDATA[select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)
+group by grouping sets (a, (a, b)), grouping sets (c), d]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 2, 3}]])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRollup">
+ <Resource name="sql">
+ <![CDATA[select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)
+group by rollup(a, b), rollup(c, d)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ 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>
+ <TestCase name="testCube">
+ <Resource name="sql">
+ <![CDATA[select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)
+group by cube(a, b)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRollupTuples">
+ <Resource name="sql">
+ <![CDATA[select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)
+group by rollup(b, (a, d))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ 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>
+ <TestCase name="testGroupingSetsWith">
+ <Resource name="sql">
+ <![CDATA[with t(a, b, c, d) as (values (1, 2, 3, 4))
+select 1 from t
+group by rollup(a, b), rollup(c, d)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[1])
+ 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>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/core/src/test/resources/sql/agg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq
index 51bed8b..5c1adb2 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -148,4 +148,125 @@ select count(mod(deptno, 20), gender) as c from emps;
!ok
+# Basic GROUPING SETS
+select deptno, count(*) as c from emps group by grouping sets ((), (deptno));
++--------+---+
+| DEPTNO | C |
++--------+---+
+| 0 | 5 |
+| 10 | 1 |
+| 20 | 2 |
+| 40 | 2 |
++--------+---+
+(4 rows)
+
+!ok
+
+# GROUPING SETS on expression
+select deptno + 1, count(*) as c from emps group by grouping sets ((), (deptno + 1));
++--------+---+
+| EXPR$0 | C |
++--------+---+
+| 0 | 5 |
+| 11 | 1 |
+| 21 | 2 |
+| 41 | 2 |
++--------+---+
+(4 rows)
+
+!ok
+
+# CUBE
+!if (false) {
+select deptno + 1, count(*) as c from emps group by cube(deptno, gender);
+!ok
+!}
+
+# ROLLUP on 1 column
+select deptno + 1, count(*) as c from emps group by rollup(deptno);
++--------+---+
+| EXPR$0 | C |
++--------+---+
+| 1 | 5 |
+| 11 | 1 |
+| 21 | 2 |
+| 41 | 2 |
++--------+---+
+(4 rows)
+
+!ok
+
+# ROLLUP on 2 columns; project columns in different order
+select gender, deptno + 1, count(*) as c
+from emps
+group by rollup(deptno, gender);
++--------+--------+---+
+| GENDER | EXPR$1 | C |
++--------+--------+---+
+| F | 21 | 1 |
+| F | 41 | 1 |
+| M | 21 | 1 |
+| M | 41 | 1 |
+| | 1 | 5 |
+| | 11 | 1 |
+| | 11 | 1 |
+| | 21 | 2 |
+| | 41 | 2 |
++--------+--------+---+
+(9 rows)
+
+!ok
+
+# ROLLUP on column with nulls
+# Note the two rows with NULL key (one represents ALL)
+select gender, count(*) as c
+from emps
+group by rollup(gender);
++--------+---+
+| GENDER | C |
++--------+---+
+| F | 2 |
+| M | 2 |
+| | 1 |
+| | 5 |
++--------+---+
+(4 rows)
+
+!ok
+
+# ROLLUP plus ORDER BY
+select gender, count(*) as c
+from emps
+group by rollup(gender)
+order by c desc;
++--------+---+
+| GENDER | C |
++--------+---+
+| | 5 |
+| F | 2 |
+| M | 2 |
+| | 1 |
++--------+---+
+(4 rows)
+
+!ok
+
+# ROLLUP cartesian product
+!if (false) {
+select deptno + 1, count(*) as c from emps group by rollup(deptno, gender), rollup(city);
+!ok
+!}
+
+# ROLLUP with HAVING
+select deptno + 1, count(*) as c from emps group by rollup(deptno)
+having count(*) > 3;
++--------+---+
+| EXPR$0 | C |
++--------+---+
+| 1 | 5 |
++--------+---+
+(1 row)
+
+!ok
+
# End agg.oq
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java
----------------------------------------------------------------------
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java b/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java
index 0e2d436..b2f64a7 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java
@@ -666,6 +666,30 @@ public abstract class EnumerableDefaults {
}
/**
+ * Groups the elements of a sequence according to a list of
+ * specified key selector functions, initializing an accumulator for each
+ * group and adding to it each time an element with the same key is seen.
+ * Creates a result value from each accumulator and its key using a
+ * specified function.
+ *
+ * <p>This method exists to support SQL {@code GROUPING SETS}.
+ * It does not correspond to any method in {@link Enumerable}.
+ */
+ public static <TSource, TKey, TAccumulate, TResult> Enumerable<TResult>
+ groupByMultiple(Enumerable<TSource> enumerable,
+ List<Function1<TSource, TKey>> keySelectors,
+ Function0<TAccumulate> accumulatorInitializer,
+ Function2<TAccumulate, TSource, TAccumulate> accumulatorAdder,
+ final Function2<TKey, TAccumulate, TResult> resultSelector) {
+ return groupByMultiple_(new HashMap<TKey, TAccumulate>(),
+ enumerable,
+ keySelectors,
+ accumulatorInitializer,
+ accumulatorAdder,
+ resultSelector);
+ }
+
+ /**
* Groups the elements of a sequence according to a
* specified key selector function, initializing an accumulator for each
* group and adding to it each time an element with the same key is seen.
@@ -710,26 +734,42 @@ public abstract class EnumerableDefaults {
} finally {
os.close();
}
- return new AbstractEnumerable2<TResult>() {
- public Iterator<TResult> iterator() {
- final Iterator<Map.Entry<TKey, TAccumulate>> iterator =
- map.entrySet().iterator();
- return new Iterator<TResult>() {
- public boolean hasNext() {
- return iterator.hasNext();
- }
-
- public TResult next() {
- final Map.Entry<TKey, TAccumulate> entry = iterator.next();
- return resultSelector.apply(entry.getKey(), entry.getValue());
- }
+ return new LookupResultEnumerable<TResult, TKey, TAccumulate>(map,
+ resultSelector);
+ }
- public void remove() {
- throw new UnsupportedOperationException();
+ private static <TSource, TKey, TAccumulate, TResult> Enumerable<TResult>
+ groupByMultiple_(final Map<TKey, TAccumulate> map,
+ Enumerable<TSource> enumerable,
+ List<Function1<TSource, TKey>> keySelectors,
+ Function0<TAccumulate> accumulatorInitializer,
+ Function2<TAccumulate, TSource, TAccumulate> accumulatorAdder,
+ final Function2<TKey, TAccumulate, TResult> resultSelector) {
+ final Enumerator<TSource> os = enumerable.enumerator();
+ try {
+ while (os.moveNext()) {
+ for (Function1<TSource, TKey> keySelector : keySelectors) {
+ TSource o = os.current();
+ TKey key = keySelector.apply(o);
+ TAccumulate accumulator = map.get(key);
+ if (accumulator == null) {
+ accumulator = accumulatorInitializer.apply();
+ accumulator = accumulatorAdder.apply(accumulator, o);
+ map.put(key, accumulator);
+ } else {
+ TAccumulate accumulator0 = accumulator;
+ accumulator = accumulatorAdder.apply(accumulator, o);
+ if (accumulator != accumulator0) {
+ map.put(key, accumulator);
+ }
}
- };
+ }
}
- };
+ } finally {
+ os.close();
+ }
+ return new LookupResultEnumerable<TResult, TKey, TAccumulate>(map,
+ resultSelector);
}
private static <TSource, TKey, TResult> Enumerable<TResult>
@@ -2364,6 +2404,38 @@ public abstract class EnumerableDefaults {
return map.values();
}
}
+
+ /** Reads a populated map, applying a selector function. */
+ private static class LookupResultEnumerable<TResult, TKey, TAccumulate>
+ extends AbstractEnumerable2<TResult> {
+ private final Map<TKey, TAccumulate> map;
+ private final Function2<TKey, TAccumulate, TResult> resultSelector;
+
+ public LookupResultEnumerable(Map<TKey, TAccumulate> map,
+ Function2<TKey, TAccumulate, TResult> resultSelector) {
+ this.map = map;
+ this.resultSelector = resultSelector;
+ }
+
+ public Iterator<TResult> iterator() {
+ final Iterator<Map.Entry<TKey, TAccumulate>> iterator =
+ map.entrySet().iterator();
+ return new Iterator<TResult>() {
+ public boolean hasNext() {
+ return iterator.hasNext();
+ }
+
+ public TResult next() {
+ final Map.Entry<TKey, TAccumulate> entry = iterator.next();
+ return resultSelector.apply(entry.getKey(), entry.getValue());
+ }
+
+ public void remove() {
+ throw new UnsupportedOperationException();
+ }
+ };
+ }
+ }
}
// End EnumerableDefaults.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/linq4j/src/main/java/org/apache/calcite/linq4j/Linq4j.java
----------------------------------------------------------------------
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/Linq4j.java b/linq4j/src/main/java/org/apache/calcite/linq4j/Linq4j.java
index 2b11705..b50998d 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/Linq4j.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/Linq4j.java
@@ -16,6 +16,8 @@
*/
package org.apache.calcite.linq4j;
+import com.google.common.collect.Lists;
+
import java.io.Closeable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
@@ -375,6 +377,21 @@ public abstract class Linq4j {
return new CartesianProductEnumerator<T>(enumerators);
}
+ /** Returns the cartesian product of an iterable of iterables. */
+ public static <T> Iterable<List<T>> product(
+ final Iterable<? extends Iterable<T>> iterables) {
+ return new Iterable<List<T>>() {
+ public Iterator<List<T>> iterator() {
+ final List<Enumerator<T>> enumerators = Lists.newArrayList();
+ for (Iterable<T> iterable : iterables) {
+ enumerators.add(iterableEnumerator(iterable));
+ }
+ return enumeratorIterator(
+ new CartesianProductEnumerator<T>(enumerators));
+ }
+ };
+ }
+
/**
* Returns whether the arguments are equal to each other.
*
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
----------------------------------------------------------------------
diff --git a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
index 81940f7..35f4661 100644
--- a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
+++ b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
@@ -45,10 +45,12 @@ public class MongoAggregate
RelOptCluster cluster,
RelTraitSet traitSet,
RelNode child,
+ boolean indicator,
ImmutableBitSet groupSet,
+ List<ImmutableBitSet> groupSets,
List<AggregateCall> aggCalls)
throws InvalidRelException {
- super(cluster, traitSet, child, groupSet, aggCalls);
+ super(cluster, traitSet, child, indicator, groupSet, groupSets, aggCalls);
assert getConvention() == MongoRel.CONVENTION;
assert getConvention() == child.getConvention();
@@ -58,13 +60,21 @@ public class MongoAggregate
"distinct aggregation not supported");
}
}
+ switch (getGroupType()) {
+ case SIMPLE:
+ break;
+ default:
+ throw new InvalidRelException("unsupported group type: "
+ + getGroupType());
+ }
}
@Override public Aggregate copy(RelTraitSet traitSet, RelNode input,
- ImmutableBitSet groupSet, List<AggregateCall> aggCalls) {
+ boolean indicator, ImmutableBitSet groupSet,
+ List<ImmutableBitSet> groupSets, List<AggregateCall> aggCalls) {
try {
- return new MongoAggregate(getCluster(), traitSet, input, groupSet,
- aggCalls);
+ return new MongoAggregate(getCluster(), traitSet, input, indicator,
+ groupSet, groupSets, aggCalls);
} catch (InvalidRelException e) {
// Semantic error not possible. Must be a bug. Convert to
// internal error.
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/6d79b5eb/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
----------------------------------------------------------------------
diff --git a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
index 88a45db..2b2f250 100644
--- a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
+++ b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
@@ -455,7 +455,9 @@ public class MongoRules {
rel.getCluster(),
traitSet,
convert(agg.getInput(), traitSet),
+ agg.indicator,
agg.getGroupSet(),
+ agg.getGroupSets(),
agg.getAggCallList());
} catch (InvalidRelException e) {
LOGGER.warning(e.toString());