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/21 03:30:26 UTC
[1/3] incubator-calcite git commit: Better handling of null values
due to GROUPING SETS.
Repository: incubator-calcite
Updated Branches:
refs/heads/master dc662db66 -> 911840a75
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/test/resources/sql/dummy.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/dummy.oq b/core/src/test/resources/sql/dummy.oq
new file mode 100644
index 0000000..10c37d5
--- /dev/null
+++ b/core/src/test/resources/sql/dummy.oq
@@ -0,0 +1,23 @@
+# dummy.oq - Scratch space
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use post
+values 1;
+EXPR$0
+1
+!ok
+# End dummy.oq
[3/3] incubator-calcite git commit: Fix Linq4j.product;
the cartesian product of 0 attributes is one row of 0 attributes.
Posted by jh...@apache.org.
Fix Linq4j.product; the cartesian product of 0 attributes is one row of 0 attributes.
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/b87f66b0
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/b87f66b0
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/b87f66b0
Branch: refs/heads/master
Commit: b87f66b08e90fdbf570b48cba5dc1f751ea399f4
Parents: dc662db
Author: Julian Hyde <jh...@apache.org>
Authored: Thu Nov 20 15:53:38 2014 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Nov 20 16:45:57 2014 -0800
----------------------------------------------------------------------
.../calcite/linq4j/CartesianProductEnumerator.java | 13 ++-----------
.../org/apache/calcite/linq4j/test/Linq4jTest.java | 6 ++++++
2 files changed, 8 insertions(+), 11 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/b87f66b0/linq4j/src/main/java/org/apache/calcite/linq4j/CartesianProductEnumerator.java
----------------------------------------------------------------------
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/CartesianProductEnumerator.java b/linq4j/src/main/java/org/apache/calcite/linq4j/CartesianProductEnumerator.java
index 88fbd5c..9bf3da1 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/CartesianProductEnumerator.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/CartesianProductEnumerator.java
@@ -41,9 +41,6 @@ class CartesianProductEnumerator<T> implements Enumerator<List<T>> {
public boolean moveNext() {
if (first) {
- if (enumerators.isEmpty()) {
- return false;
- }
int i = 0;
for (Enumerator<T> enumerator : enumerators) {
if (!enumerator.moveNext()) {
@@ -54,8 +51,7 @@ class CartesianProductEnumerator<T> implements Enumerator<List<T>> {
first = false;
return true;
}
- int ordinal = enumerators.size() - 1;
- for (;;) {
+ for (int ordinal = enumerators.size() - 1; ordinal >= 0; --ordinal) {
final Enumerator<T> enumerator = enumerators.get(ordinal);
if (enumerator.moveNext()) {
elements[ordinal] = enumerator.current();
@@ -69,13 +65,8 @@ class CartesianProductEnumerator<T> implements Enumerator<List<T>> {
return false;
}
elements[ordinal] = enumerator.current();
-
- // Advance higher rank enumerator.
- if (ordinal == 0) {
- return false;
- }
- --ordinal;
}
+ return false;
}
public void reset() {
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/b87f66b0/linq4j/src/test/java/org/apache/calcite/linq4j/test/Linq4jTest.java
----------------------------------------------------------------------
diff --git a/linq4j/src/test/java/org/apache/calcite/linq4j/test/Linq4jTest.java b/linq4j/src/test/java/org/apache/calcite/linq4j/test/Linq4jTest.java
index 5c92e9e..27ee715 100644
--- a/linq4j/src/test/java/org/apache/calcite/linq4j/test/Linq4jTest.java
+++ b/linq4j/src/test/java/org/apache/calcite/linq4j/test/Linq4jTest.java
@@ -993,6 +993,12 @@ public class Linq4jTest {
final Enumerable<String> xy =
Linq4j.asEnumerable(Arrays.asList("x", "y"));
+ final Enumerator<List<String>> productEmpty =
+ Linq4j.product(Arrays.<Enumerator<String>>asList());
+ assertTrue(productEmpty.moveNext());
+ assertEquals(Arrays.<String>asList(), productEmpty.current());
+ assertFalse(productEmpty.moveNext());
+
final Enumerator<List<String>> product0 =
Linq4j.product(
Arrays.asList(Linq4j.<String>emptyEnumerator()));
[2/3] incubator-calcite git commit: Better handling of null values
due to GROUPING SETS.
Posted by jh...@apache.org.
Better handling of null values due to GROUPING SETS.
Expand GROUPING SETS at validate time, into AggregatingSelectScope, so that we know which columns become nullable, and use the same information at SQL-to-rel time.
Fix an infinite loop when validating a call in a GROUP BY clause.
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/911840a7
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/911840a7
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/911840a7
Branch: refs/heads/master
Commit: 911840a75e22ab53cb896fe2ca6f423ef6221559
Parents: b87f66b
Author: Julian Hyde <ju...@gmail.com>
Authored: Mon Nov 17 08:28:14 2014 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Nov 20 16:45:57 2014 -0800
----------------------------------------------------------------------
.../adapter/enumerable/EnumerableAggregate.java | 3 +-
.../adapter/enumerable/JavaRowFormat.java | 2 +-
.../adapter/enumerable/PhysTypeImpl.java | 2 +-
.../org/apache/calcite/rel/core/Aggregate.java | 24 +-
.../rel/rules/AggregateReduceFunctionsRule.java | 55 ++--
.../java/org/apache/calcite/rex/RexBuilder.java | 10 +-
.../apache/calcite/sql/validate/AggChecker.java | 5 +-
.../sql/validate/AggregatingSelectScope.java | 136 +++++----
.../calcite/sql/validate/DelegatingScope.java | 4 +
.../apache/calcite/sql/validate/EmptyScope.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 15 +-
.../calcite/sql/validate/SqlValidatorScope.java | 4 +
.../calcite/sql/validate/SqlValidatorUtil.java | 230 ++++++++++++++
.../apache/calcite/sql2rel/RelFieldTrimmer.java | 35 +--
.../calcite/sql2rel/SqlToRelConverter.java | 301 +++++--------------
.../org/apache/calcite/util/BuiltInMethod.java | 4 +-
.../java/org/apache/calcite/test/JdbcTest.java | 10 +-
.../calcite/test/SqlToRelConverterTest.java | 17 ++
.../apache/calcite/test/SqlValidatorTest.java | 117 ++++++-
.../calcite/test/SqlValidatorTestCase.java | 14 +-
.../calcite/test/SqlToRelConverterTest.xml | 81 +++--
core/src/test/resources/sql/agg.oq | 238 ++++++++++++---
core/src/test/resources/sql/dummy.oq | 23 ++
23 files changed, 898 insertions(+), 436 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java
index a6d3885..fa945eb 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java
@@ -185,6 +185,7 @@ public class EnumerableAggregate extends Aggregate
inputPhysType.project(groupSet.asList(), getGroupType() != Group.SIMPLE,
JavaRowFormat.LIST);
final int keyArity = groupSet.cardinality();
+ final int indicatorArity = indicator ? keyArity : 0;
final List<AggImpState> aggs =
new ArrayList<AggImpState>(aggCalls.size());
@@ -349,7 +350,7 @@ public class EnumerableAggregate extends Aggregate
} else {
final Type keyType = keyPhysType.getJavaRowType();
key_ = Expressions.parameter(keyType, "key");
- for (int j = 0; j < keyArity; j++) {
+ for (int j = 0; j < keyArity + indicatorArity; j++) {
results.add(
keyPhysType.fieldReference(key_, j));
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/adapter/enumerable/JavaRowFormat.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/JavaRowFormat.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/JavaRowFormat.java
index d1a119c..1144444 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/JavaRowFormat.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/JavaRowFormat.java
@@ -148,7 +148,7 @@ public enum JavaRowFormat {
Expressions.call(
List.class,
null,
- BuiltInMethod.ARRAYS_AS_LIST.method,
+ BuiltInMethod.LIST_N.method,
Expressions.newArrayInit(
Object.class,
expressions)),
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
index 1a9122a..54b6424 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
@@ -564,7 +564,7 @@ public class PhysTypeImpl implements PhysType {
Expressions.call(
List.class,
null,
- BuiltInMethod.ARRAYS_AS_LIST.method,
+ BuiltInMethod.LIST_N.method,
Expressions.newArrayInit(
Object.class,
list)),
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
----------------------------------------------------------------------
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 c8f165c..116569a 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
@@ -216,9 +216,9 @@ public abstract class Aggregate extends SingleRel {
public RelWriter explainTerms(RelWriter pw) {
// We skip the "groups" element if it is a singleton of "group".
super.explainTerms(pw)
- .itemIf("indicator", indicator, indicator)
.item("group", groupSet)
.itemIf("groups", groupSets, getGroupType() != Group.SIMPLE)
+ .itemIf("indicator", indicator, indicator)
.itemIf("aggs", aggCalls, pw.nest());
if (!pw.nest()) {
for (Ord<AggregateCall> ord : Ord.zip(aggCalls)) {
@@ -263,20 +263,16 @@ public abstract class Aggregate extends SingleRel {
final IntList groupList = groupSet.toList();
assert groupList.size() == groupSet.cardinality();
final RelDataTypeFactory.FieldInfoBuilder builder = typeFactory.builder();
+ final List<RelDataTypeField> fieldList = inputRowType.getFieldList();
for (int groupKey : groupList) {
- final RelDataTypeField field = inputRowType.getFieldList().get(groupKey);
- boolean nullable = field.getType().isNullable()
- || indicator
- && !allContain(groupSets, groupKey);
- builder.add(field).nullable(nullable);
+ builder.add(fieldList.get(groupKey));
}
if (indicator) {
for (int groupKey : groupList) {
final RelDataType booleanType =
typeFactory.createTypeWithNullability(
typeFactory.createSqlType(SqlTypeName.BOOLEAN), false);
- builder.add("i$" + inputRowType.getFieldList().get(groupKey),
- booleanType);
+ builder.add("i$" + fieldList.get(groupKey), booleanType);
}
}
for (Ord<AggregateCall> aggCall : Ord.zip(aggCalls)) {
@@ -291,15 +287,6 @@ public abstract class Aggregate extends SingleRel {
return builder.build();
}
- private static boolean allContain(List<ImmutableBitSet> bitSets, int bit) {
- for (ImmutableBitSet bitSet : bitSets) {
- if (!bitSet.get(bit)) {
- return false;
- }
- }
- return true;
- }
-
/**
* Returns whether the inferred type of an {@link AggregateCall} matches the
* type it was given when it was created.
@@ -315,8 +302,7 @@ public abstract class Aggregate extends SingleRel {
AggCallBinding callBinding = aggCall.createBinding(this);
RelDataType type = aggFunction.inferReturnType(callBinding);
RelDataType expectedType = aggCall.type;
- return RelOptUtil.eq(
- "aggCall type",
+ return RelOptUtil.eq("aggCall type",
expectedType,
"inferred type",
type,
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
index acdc65d..4d2df21 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
@@ -40,11 +40,12 @@ import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
-import java.util.HashMap;
import java.util.List;
import java.util.Map;
@@ -132,16 +133,16 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
RexBuilder rexBuilder = oldAggRel.getCluster().getRexBuilder();
List<AggregateCall> oldCalls = oldAggRel.getAggCallList();
- final int nGroups = oldAggRel.getGroupCount();
+ final int groupCount = oldAggRel.getGroupCount();
+ final int indicatorCount = oldAggRel.indicator ? groupCount : 0;
- List<AggregateCall> newCalls = new ArrayList<AggregateCall>();
- Map<AggregateCall, RexNode> aggCallMapping =
- new HashMap<AggregateCall, RexNode>();
+ final List<AggregateCall> newCalls = Lists.newArrayList();
+ final Map<AggregateCall, RexNode> aggCallMapping = Maps.newHashMap();
- List<RexNode> projList = new ArrayList<RexNode>();
+ final List<RexNode> projList = Lists.newArrayList();
- // pass through group key
- for (int i = 0; i < nGroups; ++i) {
+ // pass through group key (+ indicators if present)
+ for (int i = 0; i < groupCount + indicatorCount; ++i) {
projList.add(
rexBuilder.makeInputRef(
getFieldType(oldAggRel, i),
@@ -255,9 +256,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
final int nGroups = oldAggRel.getGroupCount();
List<RelDataType> oldArgTypes = SqlTypeUtil
.projectTypes(oldAggRel.getRowType(), oldCall.getArgList());
- return rexBuilder.addAggCall(
- oldCall,
+ return rexBuilder.addAggCall(oldCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
oldArgTypes);
@@ -303,26 +304,24 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
// NOTE: these references are with respect to the output
// of newAggRel
RexNode numeratorRef =
- rexBuilder.addAggCall(
- sumCall,
+ rexBuilder.addAggCall(sumCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(avgInputType));
RexNode denominatorRef =
- rexBuilder.addAggCall(
- countCall,
+ rexBuilder.addAggCall(countCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(avgInputType));
final RexNode divideRef =
- rexBuilder.makeCall(
- SqlStdOperatorTable.DIVIDE,
+ rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE,
numeratorRef,
denominatorRef);
- return rexBuilder.makeCast(
- oldCall.getType(), divideRef);
+ return rexBuilder.makeCast(oldCall.getType(), divideRef);
}
private RexNode reduceSum(
@@ -362,9 +361,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
// NOTE: these references are with respect to the output
// of newAggRel
RexNode sumZeroRef =
- rexBuilder.addAggCall(
- sumZeroCall,
+ rexBuilder.addAggCall(sumZeroCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(argType));
@@ -375,9 +374,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
return sumZeroRef;
}
RexNode countRef =
- rexBuilder.addAggCall(
- countCall,
+ rexBuilder.addAggCall(countCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(argType));
@@ -437,9 +436,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
sumType,
null);
final RexNode sumArgSquared =
- rexBuilder.addAggCall(
- sumArgSquaredAggCall,
+ rexBuilder.addAggCall(sumArgSquaredAggCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(argType));
@@ -452,9 +451,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
sumType,
null);
final RexNode sumArg =
- rexBuilder.addAggCall(
- sumArgAggCall,
+ rexBuilder.addAggCall(sumArgAggCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(argType));
@@ -473,9 +472,9 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
null,
null);
final RexNode countArg =
- rexBuilder.addAggCall(
- countArgAggCall,
+ rexBuilder.addAggCall(countArgAggCall,
nGroups,
+ oldAggRel.indicator,
newCalls,
aggCallMapping,
ImmutableList.of(argType));
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index 31c4eca..0cc7da1 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -259,15 +259,15 @@ public class RexBuilder {
*
* @param aggCall aggregate call to be added
* @param groupCount number of groups in the aggregate relation
+ * @param indicator Whether the Aggregate has indicator (GROUPING) columns
* @param aggCalls destination list of aggregate calls
* @param aggCallMapping the dictionary of already added calls
* @param aggArgTypes Argument types, not null
+ *
* @return Rex expression for the given aggregate call
*/
- public RexNode addAggCall(
- AggregateCall aggCall,
- int groupCount,
- List<AggregateCall> aggCalls,
+ public RexNode addAggCall(AggregateCall aggCall, int groupCount,
+ boolean indicator, List<AggregateCall> aggCalls,
Map<AggregateCall, RexNode> aggCallMapping,
final List<RelDataType> aggArgTypes) {
if (aggCall.getAggregation() instanceof SqlCountAggFunction
@@ -280,7 +280,7 @@ public class RexBuilder {
}
RexNode rex = aggCallMapping.get(aggCall);
if (rex == null) {
- int index = aggCalls.size() + groupCount;
+ int index = aggCalls.size() + groupCount * (indicator ? 2 : 1);
aggCalls.add(aggCall);
rex = makeInputRef(aggCall.getType(), index);
aggCallMapping.put(aggCall, rex);
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
index 3628d63..1c32594 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
@@ -24,7 +24,6 @@ import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.util.Stacks;
-import com.google.common.collect.ImmutableList;
import com.google.common.collect.Lists;
import java.util.List;
@@ -39,7 +38,7 @@ class AggChecker extends SqlBasicVisitor<Void> {
//~ Instance fields --------------------------------------------------------
private final List<SqlValidatorScope> scopes = Lists.newArrayList();
- private final ImmutableList<SqlNode> groupExprs;
+ private final List<SqlNode> groupExprs;
private boolean distinct;
private SqlValidatorImpl validator;
@@ -61,7 +60,7 @@ class AggChecker extends SqlBasicVisitor<Void> {
List<SqlNode> groupExprs,
boolean distinct) {
this.validator = validator;
- this.groupExprs = ImmutableList.copyOf(groupExprs);
+ this.groupExprs = groupExprs;
this.distinct = distinct;
Stacks.push(this.scopes, scope);
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java b/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
index b6d6c8d..94ee878 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
@@ -16,16 +16,24 @@
*/
package org.apache.calcite.sql.validate;
+import org.apache.calcite.linq4j.Linq4j;
+import org.apache.calcite.linq4j.Ord;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.util.ImmutableBitSet;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+import com.google.common.collect.Sets;
-import java.util.ArrayList;
-import java.util.Collections;
import java.util.List;
+import java.util.Map;
+import java.util.Set;
import static org.apache.calcite.sql.SqlUtil.stripAs;
@@ -42,7 +50,17 @@ public class AggregatingSelectScope
private final SqlSelect select;
private final boolean distinct;
- private final List<SqlNode> groupExprList;
+
+ /** Use while under construction. */
+ private final List<SqlNode> temporaryGroupExprList = Lists.newArrayList();
+
+ /** Use after construction is complete. Assigned from
+ * {@link #temporaryGroupExprList} towards the end of the constructor. */
+ public final ImmutableList<SqlNode> groupExprList;
+ public final ImmutableBitSet groupSet;
+ public final ImmutableList<ImmutableBitSet> groupSets;
+ public final boolean indicator;
+ public final Map<Integer, Integer> groupExprProjection;
//~ Constructors -----------------------------------------------------------
@@ -63,22 +81,37 @@ public class AggregatingSelectScope
super(selectScope);
this.select = select;
this.distinct = distinct;
- if (distinct) {
- groupExprList = null;
- } else if (select.getGroup() != null) {
+ final Map<Integer, Integer> groupExprProjection = Maps.newHashMap();
+ final ImmutableList.Builder<ImmutableList<ImmutableBitSet>> builder =
+ ImmutableList.builder();
+ if (select.getGroup() != null) {
// We deep-copy the group-list in case subsequent validation
// modifies it and makes it no longer equivalent. While copying,
// we fully qualify all identifiers.
- SqlNodeList sqlNodeList =
- (SqlNodeList) this.select.getGroup().accept(
- new SqlValidatorUtil.DeepCopier(parent));
- groupExprList = Lists.newArrayList();
- for (SqlNode node : sqlNodeList) {
- addGroupExpr(node);
+ final SqlNodeList groupList =
+ SqlValidatorUtil.DeepCopier.copy(parent, select.getGroup());
+ for (SqlNode groupExpr : groupList) {
+ SqlValidatorUtil.analyzeGroupItem(this, temporaryGroupExprList,
+ groupExprProjection, builder, groupExpr);
}
- } else {
- groupExprList = null;
}
+ this.groupExprList = ImmutableList.copyOf(temporaryGroupExprList);
+ this.groupExprProjection = ImmutableMap.copyOf(groupExprProjection);
+
+ final Set<ImmutableBitSet> flatGroupSets =
+ Sets.newTreeSet(ImmutableBitSet.COMPARATOR);
+ for (List<ImmutableBitSet> groupSet : Linq4j.product(builder.build())) {
+ flatGroupSets.add(ImmutableBitSet.union(groupSet));
+ }
+
+ // For GROUP BY (), we need a singleton grouping set.
+ if (flatGroupSets.isEmpty()) {
+ flatGroupSets.add(ImmutableBitSet.of());
+ }
+
+ this.groupSet = ImmutableBitSet.range(groupExprList.size());
+ this.groupSets = ImmutableList.copyOf(flatGroupSets);
+ this.indicator = !groupSets.equals(ImmutableList.of(groupSet));
}
//~ Methods ----------------------------------------------------------------
@@ -93,7 +126,7 @@ public class AggregatingSelectScope
*
* @return list of grouping expressions
*/
- private List<SqlNode> getGroupExprs() {
+ private ImmutableList<SqlNode> getGroupExprs() {
if (distinct) {
// Cannot compute this in the constructor: select list has not been
// expanded yet.
@@ -101,16 +134,20 @@ public class AggregatingSelectScope
// Remove the AS operator so the expressions are consistent with
// OrderExpressionExpander.
- List<SqlNode> groupExprs = new ArrayList<SqlNode>();
- for (SqlNode selectItem
- : ((SelectScope) parent).getExpandedSelectList()) {
+ ImmutableList.Builder<SqlNode> groupExprs = ImmutableList.builder();
+ final SelectScope selectScope = (SelectScope) parent;
+ for (SqlNode selectItem : selectScope.getExpandedSelectList()) {
groupExprs.add(stripAs(selectItem));
}
- return groupExprs;
+ return groupExprs.build();
} else if (select.getGroup() != null) {
- return groupExprList;
+ if (groupExprList != null) {
+ return groupExprList;
+ } else {
+ return ImmutableList.copyOf(temporaryGroupExprList);
+ }
} else {
- return Collections.emptyList();
+ return ImmutableList.of();
}
}
@@ -118,6 +155,32 @@ public class AggregatingSelectScope
return select;
}
+ /** Returns whether a field should be nullable due to grouping sets. */
+ public boolean isNullable(int i) {
+ return i < groupExprList.size() && !allContain(groupSets, i);
+ }
+
+ private static boolean allContain(List<ImmutableBitSet> bitSets, int bit) {
+ for (ImmutableBitSet bitSet : bitSets) {
+ if (!bitSet.get(bit)) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ @Override public RelDataType nullifyType(SqlNode node, RelDataType type) {
+ for (Ord<SqlNode> groupExpr : Ord.zip(groupExprList)) {
+ if (groupExpr.e.equalsDeep(node, false)) {
+ if (isNullable(groupExpr.i)) {
+ return validator.getTypeFactory().createTypeWithNullability(type,
+ true);
+ }
+ }
+ }
+ return type;
+ }
+
public SqlValidatorScope getOperandScope(SqlCall call) {
if (call.getOperator().isAggregator()) {
// If we're the 'SUM' node in 'select a + sum(b + c) from t
@@ -171,37 +234,6 @@ public class AggregatingSelectScope
public void validateExpr(SqlNode expr) {
checkAggregateExpr(expr, true);
}
-
- /**
- * Adds a GROUP BY expression.
- *
- * <p>This method is used when the GROUP BY list is validated, and
- * expressions are expanded, in which case they are not structurally
- * identical to the unexpanded form. We leave the previous expression in
- * the list (in case there are occurrences of the expression's unexpanded
- * form in the parse tree.
- *
- * @param expr Expression
- */
- public void addGroupExpr(SqlNode expr) {
- switch (expr.getKind()) {
- case CUBE:
- case GROUPING_SETS:
- case ROLLUP:
- case ROW:
- for (SqlNode child : ((SqlCall) expr).getOperandList()) {
- addGroupExpr(child);
- }
- break;
- default:
- for (SqlNode existingNode : groupExprList) {
- if (existingNode.equalsDeep(expr, false)) {
- return;
- }
- }
- groupExprList.add(expr);
- }
- }
}
// End AggregatingSelectScope.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java b/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java
index 66deffb..34d4822 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java
@@ -113,6 +113,10 @@ public abstract class DelegatingScope implements SqlValidatorScope {
return parent.resolveColumn(name, ctx);
}
+ public RelDataType nullifyType(SqlNode node, RelDataType type) {
+ return parent.nullifyType(node, type);
+ }
+
public SqlValidatorNamespace getTableNamespace(List<String> names) {
return parent.getTableNamespace(names);
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/EmptyScope.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/EmptyScope.java b/core/src/main/java/org/apache/calcite/sql/validate/EmptyScope.java
index db4fefd..6376bd7 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/EmptyScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/EmptyScope.java
@@ -76,6 +76,10 @@ class EmptyScope implements SqlValidatorScope {
: null;
}
+ public RelDataType nullifyType(SqlNode node, RelDataType type) {
+ return type;
+ }
+
public void findAllColumnNames(List<SqlMoniker> result) {
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index c3ff914..55c1678 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -463,6 +463,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
aliases.size());
// If expansion has altered the natural alias, supply an explicit 'AS'.
+ final SqlValidatorScope selectScope = getSelectScope(select);
if (expanded != selectItem) {
String newAlias =
deriveAlias(
@@ -474,14 +475,14 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
selectItem.getParserPosition(),
expanded,
new SqlIdentifier(alias, SqlParserPos.ZERO));
- deriveTypeImpl(scope, expanded);
+ deriveTypeImpl(selectScope, expanded);
}
}
selectItems.add(expanded);
aliases.add(alias);
- final RelDataType type = deriveType(scope, expanded);
+ final RelDataType type = deriveType(selectScope, expanded);
setValidatedNodeTypeImpl(expanded, type);
types.add(Pair.of(alias, type));
return false;
@@ -1355,7 +1356,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
return ns.getType();
}
final SqlNode original = originalExprs.get(node);
- if (original != null) {
+ if (original != null && original != node) {
return getValidatedNodeType(original);
}
return null;
@@ -1414,7 +1415,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
SqlValidatorScope scope,
SqlNode operand) {
DeriveTypeVisitor v = new DeriveTypeVisitor(scope);
- return operand.accept(v);
+ final RelDataType type = operand.accept(v);
+ return scope.nullifyType(operand, type);
}
public RelDataType deriveConstructorType(
@@ -3053,6 +3055,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
SqlNode groupItem) {
switch (groupItem.getKind()) {
case GROUPING_SETS:
+ case ROLLUP:
+ case CUBE:
validateGroupingSets(groupScope, aggregatingScope, (SqlCall) groupItem);
break;
default:
@@ -3061,9 +3065,6 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
final RelDataType type = deriveType(groupScope, groupItem);
setValidatedNodeTypeImpl(groupItem, type);
- if (aggregatingScope != null) {
- aggregatingScope.addGroupExpr(groupItem);
- }
}
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorScope.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorScope.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorScope.java
index db1e294..069ba82 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorScope.java
@@ -165,6 +165,10 @@ public interface SqlValidatorScope {
* @return Namespace of table
*/
SqlValidatorNamespace getTableNamespace(List<String> names);
+
+ /** Converts the type of an expression to nullable, if the context
+ * warrants it. */
+ RelDataType nullifyType(SqlNode node, RelDataType type);
}
// End SqlValidatorScope.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
index 304853a..09c3e67 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
@@ -16,6 +16,8 @@
*/
package org.apache.calcite.sql.validate;
+import org.apache.calcite.linq4j.Linq4j;
+import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.plan.RelOptSchemaWithSampling;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.prepare.Prepare;
@@ -35,13 +37,21 @@ import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Util;
+import com.google.common.annotations.VisibleForTesting;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
import java.nio.charset.Charset;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
+import java.util.Map;
import java.util.Set;
/**
@@ -332,6 +342,221 @@ public class SqlValidatorUtil {
return typeFactory.createStructType(fields);
}
+ /** Analyzes an expression in a GROUP BY clause.
+ *
+ * <p>It may be an expression, an empty list (), or a call to
+ * {@code GROUPING SETS}, {@code CUBE} or {@code ROLLUP}.
+ *
+ * <p>Each group item produces a list of group sets, which are written to
+ * {@code topBuilder}. To find the grouping sets of the query, we will take
+ * the cartesian product of the group sets. */
+ public static void analyzeGroupItem(SqlValidatorScope scope,
+ List<SqlNode> groupExprs, Map<Integer, Integer> groupExprProjection,
+ ImmutableList.Builder<ImmutableList<ImmutableBitSet>> topBuilder,
+ SqlNode groupExpr) {
+ final ImmutableList.Builder<ImmutableBitSet> builder;
+ switch (groupExpr.getKind()) {
+ case CUBE:
+ case ROLLUP:
+ // E.g. ROLLUP(a, (b, c)) becomes [{0}, {1, 2}]
+ // then we roll up to [(0, 1, 2), (0), ()] -- note no (0, 1)
+ List<ImmutableBitSet> bitSets =
+ analyzeGroupTuple(scope, groupExprs,
+ groupExprProjection, ((SqlCall) groupExpr).getOperandList());
+ switch (groupExpr.getKind()) {
+ case ROLLUP:
+ topBuilder.add(rollup(bitSets));
+ return;
+ default:
+ topBuilder.add(cube(bitSets));
+ return;
+ }
+ case OTHER:
+ if (groupExpr instanceof SqlNodeList) {
+ SqlNodeList list = (SqlNodeList) groupExpr;
+ for (SqlNode node : list) {
+ analyzeGroupItem(scope, groupExprs, groupExprProjection, topBuilder,
+ node);
+ }
+ return;
+ }
+ // fall through
+ case GROUPING_SETS:
+ default:
+ builder = ImmutableList.builder();
+ convertGroupSet(scope, groupExprs, groupExprProjection, builder,
+ groupExpr);
+ topBuilder.add(builder.build());
+ }
+ }
+
+ /** Analyzes a GROUPING SETS item in a GROUP BY clause. */
+ private static void convertGroupSet(SqlValidatorScope scope,
+ List<SqlNode> groupExprs, Map<Integer, Integer> groupExprProjection,
+ ImmutableList.Builder<ImmutableBitSet> builder, SqlNode groupExpr) {
+ switch (groupExpr.getKind()) {
+ case GROUPING_SETS:
+ final SqlCall call = (SqlCall) groupExpr;
+ for (SqlNode node : call.getOperandList()) {
+ convertGroupSet(scope, groupExprs, groupExprProjection, builder, node);
+ }
+ return;
+ case ROW:
+ final List<ImmutableBitSet> bitSets =
+ analyzeGroupTuple(scope, groupExprs, groupExprProjection,
+ ((SqlCall) groupExpr).getOperandList());
+ builder.add(ImmutableBitSet.union(bitSets));
+ return;
+ default:
+ builder.add(
+ analyzeGroupExpr(scope, groupExprs, groupExprProjection, groupExpr));
+ return;
+ }
+ }
+
+ /** Analyzes a tuple in a GROUPING SETS clause.
+ *
+ * <p>For example, in {@code GROUP BY GROUPING SETS ((a, b), a, c)},
+ * {@code (a, b)} is a tuple.
+ *
+ * <p>Gathers into {@code groupExprs} the set of distinct expressions being
+ * grouped, and returns a bitmap indicating which expressions this tuple
+ * is grouping. */
+ private static List<ImmutableBitSet>
+ analyzeGroupTuple(SqlValidatorScope scope, List<SqlNode> groupExprs,
+ Map<Integer, Integer> groupExprProjection, List<SqlNode> operandList) {
+ List<ImmutableBitSet> list = Lists.newArrayList();
+ for (SqlNode operand : operandList) {
+ list.add(
+ analyzeGroupExpr(scope, groupExprs, groupExprProjection, operand));
+ }
+ return list;
+ }
+
+ /** Analyzes a component of a tuple in a GROUPING SETS clause. */
+ private static ImmutableBitSet analyzeGroupExpr(SqlValidatorScope scope,
+ List<SqlNode> groupExprs, Map<Integer, Integer> groupExprProjection,
+ SqlNode groupExpr) {
+ final SqlNode expandedGroupExpr =
+ scope.getValidator().expand(groupExpr, scope);
+
+ switch (expandedGroupExpr.getKind()) {
+ case ROW:
+ return ImmutableBitSet.union(
+ analyzeGroupTuple(scope, groupExprs, groupExprProjection,
+ ((SqlCall) expandedGroupExpr).getOperandList()));
+ case OTHER:
+ if (expandedGroupExpr instanceof SqlNodeList
+ && ((SqlNodeList) expandedGroupExpr).size() == 0) {
+ return ImmutableBitSet.of();
+ }
+ }
+
+ final int ref = lookupGroupExpr(groupExprs, groupExpr);
+ if (expandedGroupExpr instanceof SqlIdentifier) {
+ // SQL 2003 does not allow expressions of column references
+ SqlIdentifier expr = (SqlIdentifier) expandedGroupExpr;
+
+ // column references should be fully qualified.
+ assert expr.names.size() == 2;
+ String originalRelName = expr.names.get(0);
+ String originalFieldName = expr.names.get(1);
+
+ int[] nsIndexes = {-1};
+ final SqlValidatorScope[] ancestorScopes = {null};
+ SqlValidatorNamespace foundNs =
+ scope.resolve(
+ originalRelName,
+ ancestorScopes,
+ nsIndexes);
+
+ assert foundNs != null;
+ assert nsIndexes.length == 1;
+ int childNamespaceIndex = nsIndexes[0];
+
+ int namespaceOffset = 0;
+
+ if (childNamespaceIndex > 0) {
+ // If not the first child, need to figure out the width of
+ // output types from all the preceding namespaces
+ assert ancestorScopes[0] instanceof ListScope;
+ List<SqlValidatorNamespace> children =
+ ((ListScope) ancestorScopes[0]).getChildren();
+
+ for (int j = 0; j < childNamespaceIndex; j++) {
+ namespaceOffset +=
+ children.get(j).getRowType().getFieldCount();
+ }
+ }
+
+ RelDataTypeField field =
+ scope.getValidator().getCatalogReader().field(foundNs.getRowType(),
+ originalFieldName);
+ int origPos = namespaceOffset + field.getIndex();
+
+ groupExprProjection.put(origPos, ref);
+ }
+
+ return ImmutableBitSet.of(ref);
+ }
+
+ private static int lookupGroupExpr(List<SqlNode> groupExprs, SqlNode expr) {
+ for (Ord<SqlNode> node : Ord.zip(groupExprs)) {
+ if (node.e.equalsDeep(expr, false)) {
+ return node.i;
+ }
+ }
+ groupExprs.add(expr);
+ return groupExprs.size() - 1;
+ }
+
+ /** Computes the rollup of bit sets.
+ *
+ * <p>For example, <code>rollup({0}, {1})</code>
+ * returns <code>({0, 1}, {0}, {})</code>.
+ *
+ * <p>Bit sets are not necessarily singletons:
+ * <code>rollup({0, 2}, {3, 5})</code>
+ * returns <code>({0, 2, 3, 5}, {0, 2}, {})</code>. */
+ @VisibleForTesting
+ public static ImmutableList<ImmutableBitSet>
+ rollup(List<ImmutableBitSet> bitSets) {
+ Set<ImmutableBitSet> builder = Sets.newLinkedHashSet();
+ for (;;) {
+ final ImmutableBitSet union = ImmutableBitSet.union(bitSets);
+ builder.add(union);
+ if (union.isEmpty()) {
+ break;
+ }
+ bitSets = bitSets.subList(0, bitSets.size() - 1);
+ }
+ return ImmutableList.copyOf(builder);
+ }
+
+ /** Computes the cube of bit sets.
+ *
+ * <p>For example, <code>rollup({0}, {1})</code>
+ * returns <code>({0, 1}, {0}, {})</code>.
+ *
+ * <p>Bit sets are not necessarily singletons:
+ * <code>rollup({0, 2}, {3, 5})</code>
+ * returns <code>({0, 2, 3, 5}, {0, 2}, {})</code>. */
+ @VisibleForTesting
+ public static ImmutableList<ImmutableBitSet>
+ cube(List<ImmutableBitSet> bitSets) {
+ // Given the bit sets [{1}, {2, 3}, {5}],
+ // form the lists [[{1}, {}], [{2, 3}, {}], [{5}, {}]].
+ final Set<List<ImmutableBitSet>> builder = Sets.newLinkedHashSet();
+ for (ImmutableBitSet bitSet : bitSets) {
+ builder.add(Arrays.asList(bitSet, ImmutableBitSet.of()));
+ }
+ Set<ImmutableBitSet> flattenedBitSets = Sets.newLinkedHashSet();
+ for (List<ImmutableBitSet> o : Linq4j.product(builder)) {
+ flattenedBitSets.add(ImmutableBitSet.union(o));
+ }
+ return ImmutableList.copyOf(flattenedBitSets);
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
@@ -343,6 +568,11 @@ public class SqlValidatorUtil {
super(scope);
}
+ /** Copies a list of nodes. */
+ public static SqlNodeList copy(SqlValidatorScope scope, SqlNodeList list) {
+ return (SqlNodeList) list.accept(new DeepCopier(scope));
+ }
+
public SqlNode visit(SqlNodeList list) {
SqlNodeList copy = new SqlNodeList(list.getParserPosition());
for (SqlNode node : list) {
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
index a828870..f2409ff 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
@@ -736,12 +736,9 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
Aggregate aggregate,
ImmutableBitSet fieldsUsed,
Set<RelDataTypeField> extraFields) {
- if (aggregate.indicator) {
- throw new AssertionError(Bug.CALCITE_461_FIXED);
- }
// Fields:
//
- // | sys fields | group fields | agg functions |
+ // | sys fields | group fields | indicator fields | agg functions |
//
// Two kinds of trimming:
//
@@ -750,7 +747,7 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
//
// 2. If aggregate functions are not used, remove them.
//
- // But grouping fields stay, even if they are not used.
+ // But group and indicator fields stay, even if they are not used.
final RelDataType rowType = aggregate.getRowType();
@@ -773,6 +770,13 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
final RelNode newInput = trimResult.left;
final Mapping inputMapping = trimResult.right;
+ // We have to return group keys and (if present) indicators.
+ // So, pretend that the consumer asked for them.
+ final int groupCount = aggregate.getGroupSet().cardinality();
+ final int indicatorCount = aggregate.indicator ? groupCount : 0;
+ fieldsUsed =
+ fieldsUsed.union(ImmutableBitSet.range(groupCount + indicatorCount));
+
// If the input is unchanged, and we need to project all columns,
// there's nothing to do.
if (input == newInput
@@ -783,8 +787,7 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
}
// Which agg calls are used by our consumer?
- final int groupCount = aggregate.getGroupSet().cardinality();
- int j = groupCount;
+ int j = groupCount + indicatorCount;
int usedAggCallCount = 0;
for (int i = 0; i < aggregate.getAggCallList().size(); i++) {
if (fieldsUsed.get(j++)) {
@@ -797,16 +800,14 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
Mappings.create(
MappingType.INVERSE_SURJECTION,
rowType.getFieldCount(),
- groupCount
- + usedAggCallCount);
+ groupCount + indicatorCount + usedAggCallCount);
final ImmutableBitSet newGroupSet =
Mappings.apply(inputMapping, aggregate.getGroupSet());
final ImmutableList<ImmutableBitSet> newGroupSets =
ImmutableList.copyOf(
- Iterables.transform(
- aggregate.getGroupSets(),
+ Iterables.transform(aggregate.getGroupSets(),
new Function<ImmutableBitSet, ImmutableBitSet>() {
public ImmutableBitSet apply(ImmutableBitSet input) {
return Mappings.apply(inputMapping, input);
@@ -818,13 +819,16 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
for (IntPair pair : inputMapping) {
if (pair.source < groupCount) {
mapping.set(pair.source, pair.target);
+ if (aggregate.indicator) {
+ mapping.set(pair.source + groupCount, pair.target + groupCount);
+ }
}
}
// Now create new agg calls, and populate mapping for them.
final List<AggregateCall> newAggCallList =
new ArrayList<AggregateCall>();
- j = groupCount;
+ j = groupCount + indicatorCount;
for (AggregateCall aggCall : aggregate.getAggCallList()) {
if (fieldsUsed.get(j)) {
AggregateCall newAggCall =
@@ -832,17 +836,14 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
if (newAggCall.equals(aggCall)) {
newAggCall = aggCall; // immutable -> canonize to save space
}
- mapping.set(
- j,
- groupCount
- + newAggCallList.size());
+ mapping.set(j, groupCount + indicatorCount + newAggCallList.size());
newAggCallList.add(newAggCall);
}
++j;
}
RelNode newAggregate = aggregateFactory.createAggregate(newInput,
- false, newGroupSet, newGroupSets, newAggCallList);
+ aggregate.indicator, newGroupSet, newGroupSets, newAggCallList);
assert newAggregate.getClass() == aggregate.getClass();
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index ee886f2..9d6d5bb 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -16,7 +16,6 @@
*/
package org.apache.calcite.sql2rel;
-import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.plan.Convention;
import org.apache.calcite.plan.RelOptCluster;
@@ -31,6 +30,7 @@ import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationImpl;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.Collect;
import org.apache.calcite.rel.core.Correlation;
@@ -114,6 +114,7 @@ import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.type.TableFunctionReturnTypeInference;
import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.sql.util.SqlVisitor;
+import org.apache.calcite.sql.validate.AggregatingSelectScope;
import org.apache.calcite.sql.validate.CollectNamespace;
import org.apache.calcite.sql.validate.DelegatingScope;
import org.apache.calcite.sql.validate.ListScope;
@@ -148,7 +149,6 @@ import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.util.AbstractList;
import java.util.ArrayList;
-import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.EnumSet;
@@ -2502,7 +2502,7 @@ public class SqlToRelConverter {
protected final void createAggImpl(
Blackboard bb,
- AggConverter aggConverter,
+ final AggConverter aggConverter,
SqlNodeList selectList,
SqlNodeList groupList,
SqlNode having,
@@ -2532,24 +2532,9 @@ public class SqlToRelConverter {
// Currently farrago allows expressions, not just column references in
// group by list. This is not SQL 2003 compliant.
- final Map<Integer, Integer> groupExprProjection = Maps.newHashMap();
-
- final ImmutableList.Builder<ImmutableList<ImmutableBitSet>> builder =
- ImmutableList.builder();
- for (SqlNode groupExpr : groupList) {
- convertGroupItem(bb, aggConverter, groupExprProjection, builder,
- groupExpr);
- }
-
- final Set<ImmutableBitSet> flatGroupSets =
- Sets.newTreeSet(ImmutableBitSet.COMPARATOR);
- for (List<ImmutableBitSet> groupSet : Linq4j.product(builder.build())) {
- flatGroupSets.add(ImmutableBitSet.union(groupSet));
- }
-
- // For GROUP BY (), we need a singleton grouping set.
- if (flatGroupSets.isEmpty()) {
- flatGroupSets.add(ImmutableBitSet.of());
+ final AggregatingSelectScope scope = aggConverter.aggregatingSelectScope;
+ for (SqlNode groupExpr : scope.groupExprList) {
+ aggConverter.addGroupExpr(groupExpr);
}
RexNode havingExpr = null;
@@ -2594,7 +2579,7 @@ public class SqlToRelConverter {
preNames,
true),
false);
- bb.mapRootRelToFieldProjection.put(bb.root, groupExprProjection);
+ bb.mapRootRelToFieldProjection.put(bb.root, scope.groupExprProjection);
// REVIEW jvs 31-Oct-2007: doesn't the declaration of
// monotonicity here assume sort-based aggregation at
@@ -2609,12 +2594,43 @@ public class SqlToRelConverter {
// Add the aggregator
bb.setRoot(
- createAggregate(bb, false,
- ImmutableBitSet.range(aggConverter.groupExprs.size()),
- ImmutableList.copyOf(flatGroupSets), aggConverter.getAggCalls()),
+ createAggregate(bb, aggConverter.aggregatingSelectScope.indicator,
+ scope.groupSet, scope.groupSets, aggConverter.getAggCalls()),
false);
- bb.mapRootRelToFieldProjection.put(bb.root, groupExprProjection);
+ // Generate NULL values for rolled-up not-null fields.
+ final Aggregate aggregate = (Aggregate) bb.root;
+ if (aggregate.getGroupType() != Aggregate.Group.SIMPLE) {
+ assert aggregate.indicator;
+ List<Pair<RexNode, String>> projects2 = Lists.newArrayList();
+ int converted = 0;
+ final int groupCount = aggregate.getGroupSet().cardinality();
+ for (RelDataTypeField field : aggregate.getRowType().getFieldList()) {
+ final int i = field.getIndex();
+ final RexNode rex;
+ if (i < groupCount && scope.isNullable(i)) {
+ ++converted;
+
+ rex = rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+ rexBuilder.makeInputRef(aggregate, groupCount + i),
+ rexBuilder.makeCast(
+ typeFactory.createTypeWithNullability(
+ field.getType(), true),
+ rexBuilder.constantNull()),
+ rexBuilder.makeInputRef(aggregate, i));
+ } else {
+ rex = rexBuilder.makeInputRef(aggregate, i);
+ }
+ projects2.add(Pair.of(rex, field.getName()));
+ }
+ if (converted > 0) {
+ bb.setRoot(
+ RelOptUtil.createProject(bb.root, projects2, true),
+ false);
+ }
+ }
+
+ bb.mapRootRelToFieldProjection.put(bb.root, scope.groupExprProjection);
// Replace subqueries in having here and modify having to use
// the replaced expressions
@@ -2689,175 +2705,6 @@ public class SqlToRelConverter {
}
}
- private void convertGroupItem(Blackboard bb, AggConverter aggConverter,
- Map<Integer, Integer> groupExprProjection,
- ImmutableList.Builder<ImmutableList<ImmutableBitSet>> topBuilder,
- SqlNode groupExpr) {
- final ImmutableList.Builder<ImmutableBitSet> builder =
- ImmutableList.builder();
- switch (groupExpr.getKind()) {
- case GROUPING_SETS:
- convertGroupSet(bb, aggConverter, groupExprProjection, builder,
- groupExpr);
- topBuilder.add(builder.build());
- return;
- case CUBE:
- case ROLLUP:
- // E.g. ROLLUP(a, (b, c)) becomes [{0}, {1, 2}]
- // then we roll up to [(0, 1, 2), (0), ()] -- note no (0, 1)
- List<ImmutableBitSet> bitSets =
- convertGroupTuple(bb, aggConverter,
- groupExprProjection, ((SqlCall) groupExpr).getOperandList());
- switch (groupExpr.getKind()) {
- case ROLLUP:
- rollup(builder, bitSets);
- break;
- default:
- cube(builder, bitSets);
- break;
- }
- topBuilder.add(builder.build());
- return;
- case OTHER:
- if (groupExpr instanceof SqlNodeList) {
- SqlNodeList list = (SqlNodeList) groupExpr;
- for (SqlNode node : list) {
- convertGroupItem(bb, aggConverter, groupExprProjection, topBuilder,
- node);
- }
- return;
- }
- // fall through
- default:
- convertGroupSet(bb, aggConverter, groupExprProjection, builder,
- groupExpr);
- topBuilder.add(builder.build());
- }
- }
-
- private void rollup(ImmutableList.Builder<ImmutableBitSet> builder,
- List<ImmutableBitSet> bitSets) {
- for (;;) {
- builder.add(ImmutableBitSet.union(bitSets));
- if (bitSets.isEmpty()) {
- break;
- }
- bitSets = bitSets.subList(0, bitSets.size() - 1);
- }
- }
-
- private void cube(ImmutableList.Builder<ImmutableBitSet> builder,
- List<ImmutableBitSet> bitSets) {
- // Given the bit sets [{1}, {2, 3}, {5}],
- // form the lists [[{1}, {}], [{2, 3}, {}], [{5}, {}]].
- final List<List<ImmutableBitSet>> bits = Lists.newArrayList();
- for (ImmutableBitSet bitSet : bitSets) {
- bits.add(Arrays.asList(bitSet, ImmutableBitSet.of()));
- }
- for (List<ImmutableBitSet> o : Linq4j.product(bits)) {
- builder.add(ImmutableBitSet.union(o));
- }
- }
-
- private void convertGroupSet(Blackboard bb, AggConverter aggConverter,
- Map<Integer, Integer> groupExprProjection,
- ImmutableList.Builder<ImmutableBitSet> builder, SqlNode groupExpr) {
- switch (groupExpr.getKind()) {
- case GROUPING_SETS:
- final SqlCall call = (SqlCall) groupExpr;
- for (SqlNode node : call.getOperandList()) {
- convertGroupSet(bb, aggConverter, groupExprProjection, builder, node);
- }
- return;
- case ROW:
- final List<ImmutableBitSet> bitSets =
- convertGroupTuple(bb, aggConverter, groupExprProjection,
- ((SqlCall) groupExpr).getOperandList());
- builder.add(ImmutableBitSet.union(bitSets));
- return;
- default:
- builder.add(
- convertGroupExpr(bb, aggConverter, groupExprProjection, groupExpr));
- return;
- }
- }
-
- private List<ImmutableBitSet> convertGroupTuple(Blackboard bb,
- AggConverter aggConverter, Map<Integer, Integer> groupExprProjection,
- List<SqlNode> operandList) {
- List<ImmutableBitSet> list = Lists.newArrayList();
- for (SqlNode operand : operandList) {
- list.add(
- convertGroupExpr(bb, aggConverter, groupExprProjection, operand));
- }
- return list;
- }
-
- private ImmutableBitSet convertGroupExpr(Blackboard bb,
- AggConverter aggConverter, Map<Integer, Integer> groupExprProjection,
- SqlNode groupExpr) {
- final SqlNode expandedGroupExpr =
- validator.expand(groupExpr, bb.scope);
-
- switch (expandedGroupExpr.getKind()) {
- case ROW:
- return ImmutableBitSet.union(
- convertGroupTuple(bb, aggConverter, groupExprProjection,
- ((SqlCall) expandedGroupExpr).getOperandList()));
- case OTHER:
- if (expandedGroupExpr instanceof SqlNodeList
- && ((SqlNodeList) expandedGroupExpr).size() == 0) {
- return ImmutableBitSet.of();
- }
- }
-
- final int ref = aggConverter.addGroupExpr(expandedGroupExpr);
- if (expandedGroupExpr instanceof SqlIdentifier) {
- // SQL 2003 does not allow expressions of column references
- SqlIdentifier expr = (SqlIdentifier) expandedGroupExpr;
-
- // column references should be fully qualified.
- assert expr.names.size() == 2;
- String originalRelName = expr.names.get(0);
- String originalFieldName = expr.names.get(1);
-
- int[] nsIndexes = {-1};
- final SqlValidatorScope[] ancestorScopes = {null};
- SqlValidatorNamespace foundNs =
- bb.scope.resolve(
- originalRelName,
- ancestorScopes,
- nsIndexes);
-
- assert foundNs != null;
- assert nsIndexes.length == 1;
- int childNamespaceIndex = nsIndexes[0];
-
- int namespaceOffset = 0;
-
- if (childNamespaceIndex > 0) {
- // If not the first child, need to figure out the width of
- // output types from all the preceding namespaces
- assert ancestorScopes[0] instanceof ListScope;
- List<SqlValidatorNamespace> children =
- ((ListScope) ancestorScopes[0]).getChildren();
-
- for (int j = 0; j < childNamespaceIndex; j++) {
- namespaceOffset +=
- children.get(j).getRowType().getFieldCount();
- }
- }
-
- RelDataTypeField field =
- catalogReader.field(foundNs.getRowType(), originalFieldName);
- int origPos = namespaceOffset + field.getIndex();
-
- groupExprProjection.put(origPos, ref);
- }
-
- return ImmutableBitSet.of(ref);
- }
-
/**
* Creates an Aggregate.
*
@@ -4210,9 +4057,9 @@ public class SqlToRelConverter {
// GROUP BY clause, return a reference to the field.
if (agg != null) {
final SqlNode expandedGroupExpr = validator.expand(expr, scope);
- final RexInputRef ref = agg.lookupGroupExpr(expandedGroupExpr);
- if (ref != null) {
- return ref;
+ final int ref = agg.lookupGroupExpr(expandedGroupExpr);
+ if (ref >= 0) {
+ return rexBuilder.makeInputRef(root, ref);
}
if (expr instanceof SqlCall) {
final RexNode rex = agg.lookupAggregates((SqlCall) expr);
@@ -4505,8 +4352,13 @@ public class SqlToRelConverter {
/**
* Converts expressions to aggregates.
*
- * <p>Consider the expression SELECT deptno, SUM(2 * sal) FROM emp GROUP BY
- * deptno Then
+ * <p>Consider the expression
+ *
+ * <blockquote>
+ * {@code SELECT deptno, SUM(2 * sal) FROM emp GROUP BY deptno}
+ * </blockquote>
+ *
+ * <p>Then:
*
* <ul>
* <li>groupExprs = {SqlIdentifier(deptno)}</li>
@@ -4518,9 +4370,9 @@ public class SqlToRelConverter {
*/
protected class AggConverter implements SqlVisitor<Void> {
private final Blackboard bb;
+ public final AggregatingSelectScope aggregatingSelectScope;
- private final Map<String, String> nameMap =
- new HashMap<String, String>();
+ private final Map<String, String> nameMap = Maps.newHashMap();
/**
* The group-by expressions, in {@link SqlNode} format.
@@ -4534,24 +4386,18 @@ public class SqlToRelConverter {
* elements in {@link #groupExprs}; the remaining elements are for
* aggregates.
*/
- private final List<RexNode> convertedInputExprs =
- new ArrayList<RexNode>();
+ private final List<RexNode> convertedInputExprs = Lists.newArrayList();
/**
* Names of {@link #convertedInputExprs}, where the expressions are
* simple mappings to input fields.
*/
- private final List<String> convertedInputExprNames =
- new ArrayList<String>();
-
- private final List<RexInputRef> inputRefs =
- new ArrayList<RexInputRef>();
- private final List<AggregateCall> aggCalls =
- new ArrayList<AggregateCall>();
- private final Map<SqlNode, RexNode> aggMapping =
- new HashMap<SqlNode, RexNode>();
+ private final List<String> convertedInputExprNames = Lists.newArrayList();
+
+ private final List<AggregateCall> aggCalls = Lists.newArrayList();
+ private final Map<SqlNode, RexNode> aggMapping = Maps.newHashMap();
private final Map<AggregateCall, RexNode> aggCallMapping =
- new HashMap<AggregateCall, RexNode>();
+ Maps.newHashMap();
/**
* Creates an AggConverter.
@@ -4564,6 +4410,8 @@ public class SqlToRelConverter {
*/
public AggConverter(Blackboard bb, SqlSelect select) {
this.bb = bb;
+ this.aggregatingSelectScope =
+ (AggregatingSelectScope) bb.getValidator().getSelectScope(select);
// Collect all expressions used in the select list so that aggregate
// calls can be named correctly.
@@ -4587,17 +4435,15 @@ public class SqlToRelConverter {
public int addGroupExpr(SqlNode expr) {
RexNode convExpr = bb.convertExpression(expr);
- RexInputRef ref = lookupGroupExpr(expr);
- if (ref == null) {
- // Don't add duplicates, in e.g. "GROUP BY x, y, x"
- groupExprs.add(expr);
- String name = nameMap.get(expr.toString());
- addExpr(convExpr, name);
- final RelDataType type = convExpr.getType();
- ref = rexBuilder.makeInputRef(type, inputRefs.size());
- inputRefs.add(ref);
+ int ref = lookupGroupExpr(expr);
+ if (ref >= 0) {
+ return ref;
}
- return ref.getIndex();
+ final int index = groupExprs.size();
+ groupExprs.add(expr);
+ String name = nameMap.get(expr.toString());
+ addExpr(convExpr, name);
+ return index;
}
/**
@@ -4708,6 +4554,7 @@ public class SqlToRelConverter {
rexBuilder.addAggCall(
aggCall,
groupExprs.size(),
+ aggregatingSelectScope.indicator,
aggCalls,
aggCallMapping,
argTypes);
@@ -4746,14 +4593,14 @@ public class SqlToRelConverter {
* expressions, returns a reference to the expression, otherwise returns
* null.
*/
- public RexInputRef lookupGroupExpr(SqlNode expr) {
+ public int lookupGroupExpr(SqlNode expr) {
for (int i = 0; i < groupExprs.size(); i++) {
SqlNode groupExpr = groupExprs.get(i);
if (expr.equalsDeep(groupExpr, false)) {
- return inputRefs.get(i);
+ return i;
}
}
- return null;
+ return -1;
}
public RexNode lookupAggregates(SqlCall call) {
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 178ffc9..b60e980 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -64,6 +64,7 @@ import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.Time;
import java.sql.Timestamp;
+import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
@@ -148,7 +149,8 @@ public enum BuiltInMethod {
ARRAY_COMPARER(Functions.class, "arrayComparer"),
FUNCTION0_APPLY(Function0.class, "apply"),
FUNCTION1_APPLY(Function1.class, "apply", Object.class),
- ARRAYS_AS_LIST(FlatLists.class, "of", Object[].class),
+ ARRAYS_AS_LIST(Arrays.class, "asList", Object[].class),
+ LIST_N(FlatLists.class, "of", Object[].class),
LIST2(FlatLists.class, "of", Object.class, Object.class),
LIST3(FlatLists.class, "of", Object.class, Object.class, Object.class),
IDENTITY_COMPARER(Functions.class, "identityComparer"),
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 61f6d51..d5f2623 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2873,7 +2873,7 @@ public class JdbcTest {
+ "from \"hr\".\"emps\"\n"
+ "group by grouping sets((\"deptno\"), ())")
.returnsUnordered(
- "deptno=0; C=4; S=36500.0",
+ "deptno=null; C=4; S=36500.0",
"deptno=10; C=3; S=28500.0",
"deptno=20; C=1; S=8000.0");
}
@@ -2885,7 +2885,7 @@ public class JdbcTest {
+ "from \"hr\".\"emps\"\n"
+ "group by rollup(\"deptno\")")
.returnsUnordered(
- "deptno=0; C=4; S=36500.0",
+ "deptno=null; C=4; S=36500.0",
"deptno=10; C=3; S=28500.0",
"deptno=20; C=1; S=8000.0");
}
@@ -4266,6 +4266,12 @@ public class JdbcTest {
.returnsUnordered(lines);
}
+ /** Runs the dummy script, which is checked in empty but which you may
+ * use as scratch space during development. */
+ @Test public void testRunDummy() throws Exception {
+ checkRun("sql/dummy.oq");
+ }
+
@Test public void testRunAgg() throws Exception {
checkRun("sql/agg.oq");
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index afd3f2b..9e597c6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -251,6 +251,15 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
+ "group by grouping sets (a, (a, b)), grouping sets (c), d").ok();
}
+ @Test public void testRollupSimple() {
+ // a is nullable so is translated as just "a"
+ // b is not null, so is represented as 0 inside Aggregate, then
+ // using "CASE WHEN i$b THEN NULL ELSE b END"
+ sql("select a, b, count(*) as c\n"
+ + "from (values (cast(null as integer), 2)) as t(a, b)\n"
+ + "group by rollup(a, b)").ok();
+ }
+
@Test public void testRollup() {
// Equivalent to {(a, b), (a), ()} * {(c, d), (c), ()}
sql("select 1 from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
@@ -299,6 +308,14 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
+ "group by name, deptno, name)").ok();
}
+ @Test public void testGroupByExpression() {
+ // This used to cause an infinite loop,
+ // SqlValidatorImpl.getValidatedNodeType
+ // calling getValidatedNodeTypeIfKnown
+ // calling getValidatedNodeType.
+ sql("select count(*) from emp group by substring(ename FROM 1 FOR 1)").ok();
+ }
+
@Test public void testAggDistinct() {
sql("select deptno, sum(sal), sum(distinct sal), count(*) "
+ "from emp "
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index ed15304..fd20341 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -28,9 +28,12 @@ import org.apache.calcite.sql.test.SqlTester;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.Bug;
+import org.apache.calcite.util.ImmutableBitSet;
+
+import com.google.common.collect.ImmutableList;
-import org.hamcrest.CoreMatchers;
import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;
@@ -41,6 +44,7 @@ import java.util.List;
import java.util.Locale;
import java.util.logging.Logger;
+import static org.hamcrest.CoreMatchers.equalTo;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.assertTrue;
@@ -4579,9 +4583,92 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
@Test public void testInvalidGroupBy() {
- checkFails(
- "select ^empno^, deptno from emp group by deptno",
- "Expression 'EMPNO' is not being grouped");
+ sql("select ^empno^, deptno from emp group by deptno")
+ .fails("Expression 'EMPNO' is not being grouped");
+ }
+
+ @Test public void testInvalidGroupBy2() {
+ sql("select count(*) from emp group by ^deptno + 'a'^")
+ .fails("(?s)Cannot apply '\\+' to arguments of type.*");
+ }
+
+ @Test public void testInvalidGroupBy3() {
+ sql("select deptno / 2 + 1, count(*) as c\n"
+ + "from emp\n"
+ + "group by rollup(deptno / 2, sal), rollup(empno, ^deptno + 'a'^)")
+ .fails("(?s)Cannot apply '\\+' to arguments of type.*");
+ }
+
+ /** Unit test for
+ * {@link org.apache.calcite.sql.validate.SqlValidatorUtil#rollup}. */
+ @Test public void testRollupBitSets() {
+ assertThat(rollup(ImmutableBitSet.of(1), ImmutableBitSet.of(3)).toString(),
+ equalTo("[{1, 3}, {1}, {}]"));
+ assertThat(rollup(ImmutableBitSet.of(1), ImmutableBitSet.of(3, 4))
+ .toString(),
+ equalTo("[{1, 3, 4}, {1}, {}]"));
+ assertThat(rollup(ImmutableBitSet.of(1, 3), ImmutableBitSet.of(4))
+ .toString(),
+ equalTo("[{1, 3, 4}, {1, 3}, {}]"));
+ assertThat(rollup(ImmutableBitSet.of(1, 4), ImmutableBitSet.of(3))
+ .toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {}]"));
+ // non-disjoint bit sets
+ assertThat(rollup(ImmutableBitSet.of(1, 4), ImmutableBitSet.of(3, 4))
+ .toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {}]"));
+ // some bit sets are empty
+ assertThat(
+ rollup(ImmutableBitSet.of(1, 4), ImmutableBitSet.of(),
+ ImmutableBitSet.of(3, 4), ImmutableBitSet.of()).toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {}]"));
+ assertThat(rollup(ImmutableBitSet.of(1)).toString(),
+ equalTo("[{1}, {}]"));
+ // one empty bit set
+ assertThat(rollup(ImmutableBitSet.of()).toString(),
+ equalTo("[{}]"));
+ // no bit sets
+ assertThat(rollup().toString(),
+ equalTo("[{}]"));
+ }
+
+ private ImmutableList<ImmutableBitSet> rollup(ImmutableBitSet... sets) {
+ return SqlValidatorUtil.rollup(ImmutableList.copyOf(sets));
+ }
+
+ /** Unit test for
+ * {@link org.apache.calcite.sql.validate.SqlValidatorUtil#cube}. */
+ @Test public void testCubeBitSets() {
+ assertThat(cube(ImmutableBitSet.of(1), ImmutableBitSet.of(3)).toString(),
+ equalTo("[{1, 3}, {1}, {3}, {}]"));
+ assertThat(cube(ImmutableBitSet.of(1), ImmutableBitSet.of(3, 4)).toString(),
+ equalTo("[{1, 3, 4}, {1}, {3, 4}, {}]"));
+ assertThat(cube(ImmutableBitSet.of(1, 3), ImmutableBitSet.of(4)).toString(),
+ equalTo("[{1, 3, 4}, {1, 3}, {4}, {}]"));
+ assertThat(cube(ImmutableBitSet.of(1, 4), ImmutableBitSet.of(3)).toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {3}, {}]"));
+ // non-disjoint bit sets
+ assertThat(
+ cube(ImmutableBitSet.of(1, 4), ImmutableBitSet.of(3, 4)).toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {3, 4}, {}]"));
+ // some bit sets are empty, and there are duplicates
+ assertThat(
+ cube(ImmutableBitSet.of(1, 4),
+ ImmutableBitSet.of(),
+ ImmutableBitSet.of(1, 4),
+ ImmutableBitSet.of(3, 4),
+ ImmutableBitSet.of()).toString(),
+ equalTo("[{1, 3, 4}, {1, 4}, {3, 4}, {}]"));
+ assertThat(cube(ImmutableBitSet.of(1)).toString(),
+ equalTo("[{1}, {}]"));
+ assertThat(cube(ImmutableBitSet.of()).toString(),
+ equalTo("[{}]"));
+ assertThat(cube().toString(),
+ equalTo("[{}]"));
+ }
+
+ private ImmutableList<ImmutableBitSet> cube(ImmutableBitSet... sets) {
+ return SqlValidatorUtil.cube(ImmutableList.copyOf(sets));
}
@Test public void testSumInvalidArgs() {
@@ -5483,9 +5570,27 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
@Test public void testRollup() {
+ // DEPTNO is not null in database, but rollup introduces nulls
sql("select deptno, count(*) as c, sum(sal) as s\n"
+ "from emp\n"
- + "group by rollup(deptno)").ok();
+ + "group by rollup(deptno)")
+ .ok()
+ .type(
+ "RecordType(INTEGER DEPTNO, BIGINT NOT NULL C, INTEGER NOT NULL S) NOT NULL");
+
+ // EMPNO stays NOT NULL because it is not rolled up
+ sql("select deptno, empno\n"
+ + "from emp\n"
+ + "group by empno, rollup(deptno)")
+ .ok()
+ .type("RecordType(INTEGER DEPTNO, INTEGER NOT NULL EMPNO) NOT NULL");
+
+ // DEPTNO stays NOT NULL because it is not rolled up
+ sql("select deptno, empno\n"
+ + "from emp\n"
+ + "group by rollup(empno), deptno")
+ .ok()
+ .type("RecordType(INTEGER NOT NULL DEPTNO, INTEGER EMPNO) NOT NULL");
}
@Test public void testGroupByCorrelatedColumnFails() {
@@ -6512,7 +6617,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
case INTERNAL:
break;
default:
- assertThat(name.toUpperCase(), CoreMatchers.equalTo(name));
+ assertThat(name.toUpperCase(), equalTo(name));
break;
}
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/core/src/test/java/org/apache/calcite/test/SqlValidatorTestCase.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTestCase.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTestCase.java
index dddffc9..039cfe8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTestCase.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTestCase.java
@@ -542,20 +542,28 @@ public class SqlValidatorTestCase {
this.sql = sql;
}
- void ok() {
+ Sql ok() {
tester.assertExceptionIsThrown(sql, null);
+ return this;
}
- void fails(String expected) {
+ Sql fails(String expected) {
tester.assertExceptionIsThrown(sql, expected);
+ return this;
}
- void failsIf(boolean b, String expected) {
+ Sql failsIf(boolean b, String expected) {
if (b) {
fails(expected);
} else {
ok();
}
+ return this;
+ }
+
+ public Sql type(String expectedType) {
+ tester.checkResultType(sql, expectedType);
+ return this;
}
}
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/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 53e7d59..513b94f 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2023,9 +2023,11 @@ order by 2]]>
<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]])
+ LogicalProject(DEPTNO=[$0], ENAME=[$1], EXPR$2=[$4])
+ LogicalProject(DEPTNO=[$0], ENAME=[CASE($3, null, $1)], i$#0: DEPTNO INTEGER=[$2], i$#1: ENAME VARCHAR(20)=[$3], EXPR$2=[$4])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], EXPR$2=[SUM($2)])
+ LogicalProject(DEPTNO=[$7], ENAME=[$1], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2040,10 +2042,11 @@ group by sal,
</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]])
+LogicalProject(EXPR$0=[$6])
+ LogicalProject(SAL=[$0], DEPTNO=[CASE($4, null, $1)], ENAME=[CASE($5, null, $2)], i$#0: SAL INTEGER=[$3], i$#1: DEPTNO INTEGER=[$4], i$#2: ENAME VARCHAR(20)=[$5], EXPR$0=[$6])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0, 2}]], indicator=[true], EXPR$0=[SUM($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2055,8 +2058,9 @@ group by grouping sets (a, b), grouping sets (c, d)]]>
<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 }]])
+ LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5], i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 2}, {0, 3}, {1, 2}, {1, 3}]], indicator=[true])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
]]>
</Resource>
</TestCase>
@@ -2068,8 +2072,9 @@ group by grouping sets (a, (a, b)), grouping sets (c), d]]>
<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 }]])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[CASE($5, null, $1)], EXPR$2=[$2], EXPR$3=[$3], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5], i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 2, 3}]], indicator=[true])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
]]>
</Resource>
</TestCase>
@@ -2081,8 +2086,9 @@ group by rollup(a, b), rollup(c, d)]]>
<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 }]])
+ LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5], i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
]]>
</Resource>
</TestCase>
@@ -2094,9 +2100,10 @@ group by cube(a, b)]]>
<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 }]])
+ LogicalProject(EXPR$0=[CASE($2, null, $0)], EXPR$1=[CASE($3, null, $1)], i$#0: EXPR$0 INTEGER=[$2], i$#1: EXPR$1 INTEGER=[$3])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], indicator=[true])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
]]>
</Resource>
</TestCase>
@@ -2108,9 +2115,10 @@ group by rollup(b, (a, d))]]>
<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 }]])
+ LogicalProject(EXPR$1=[CASE($3, null, $0)], EXPR$0=[CASE($4, null, $1)], EXPR$3=[CASE($5, null, $2)], i$#0: EXPR$1 INTEGER=[$3], i$#1: EXPR$0 INTEGER=[$4], i$#2: EXPR$3 INTEGER=[$5])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0}, {}]], indicator=[true])
+ LogicalProject(EXPR$1=[$1], EXPR$0=[$0], EXPR$3=[$3])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
]]>
</Resource>
</TestCase>
@@ -2123,8 +2131,39 @@ group by rollup(a, b), rollup(c, d)]]>
<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 }]])
+ LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6, null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5], i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+ LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0, 2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true])
+ LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRollupSimple">
+ <Resource name="sql">
+ <![CDATA[select a, b, count(*) as c
+from (values (cast(null as integer), 2)) as t(a, b)
+group by rollup(a, b)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(A=[$0], B=[$1], C=[$4])
+ LogicalProject(A=[CASE($2, null, $0)], B=[CASE($3, null, $1)], i$#0: A INTEGER=[$2], i$#1: B INTEGER=[$3], C=[$4])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], C=[COUNT()])
+ LogicalProject(A=[$0], B=[$1])
+ LogicalProject(EXPR$0=[null], EXPR$1=[2])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupByExpression">
+ <Resource name="sql">
+ <![CDATA[select count(*) from emp group by substring(ename FROM 1 FOR 1)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[$1])
+ LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+ LogicalProject($f0=[SUBSTRING($1, 1, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/911840a7/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 5c1adb2..0f97176 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -148,15 +148,30 @@ select count(mod(deptno, 20), gender) as c from emps;
!ok
+# Nulls in GROUP BY
+select x = 1 as x1, count(*) as c
+from (values 0, 1, 2, cast(null as integer)) as t(x)
+group by x = 1;
++-------+---+
+| X1 | C |
++-------+---+
+| false | 2 |
+| true | 1 |
+| | 1 |
++-------+---+
+(3 rows)
+
+!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 |
+| | 5 |
+--------+---+
(4 rows)
@@ -167,106 +182,245 @@ select deptno + 1, count(*) as c from emps group by grouping sets ((), (deptno +
+--------+---+
| EXPR$0 | C |
+--------+---+
-| 0 | 5 |
| 11 | 1 |
| 21 | 2 |
| 41 | 2 |
+| | 5 |
+--------+---+
(4 rows)
!ok
# CUBE
-!if (false) {
-select deptno + 1, count(*) as c from emps group by cube(deptno, gender);
+select deptno + 1, count(*) as c from emp group by cube(deptno, gender);
++--------+---+
+| EXPR$0 | C |
++--------+---+
+| 11 | 1 |
+| 11 | 1 |
+| 11 | 2 |
+| 21 | 1 |
+| 21 | 1 |
+| 31 | 2 |
+| 31 | 2 |
+| 51 | 1 |
+| 51 | 1 |
+| 51 | 2 |
+| 61 | 1 |
+| 61 | 1 |
+| | 1 |
+| | 1 |
+| | 3 |
+| | 6 |
+| | 9 |
++--------+---+
+(17 rows)
+
!ok
-!}
# ROLLUP on 1 column
-select deptno + 1, count(*) as c from emps group by rollup(deptno);
+select deptno + 1, count(*) as c
+from emp
+group by rollup(deptno);
+--------+---+
| EXPR$0 | C |
+--------+---+
-| 1 | 5 |
-| 11 | 1 |
-| 21 | 2 |
-| 41 | 2 |
+| 11 | 2 |
+| 21 | 1 |
+| 31 | 2 |
+| 51 | 2 |
+| 61 | 1 |
+| | 1 |
+| | 9 |
+--------+---+
-(4 rows)
+(7 rows)
!ok
# ROLLUP on 2 columns; project columns in different order
select gender, deptno + 1, count(*) as c
-from emps
+from emp
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 |
+| F | 11 | 1 |
+| F | 31 | 2 |
+| F | 51 | 1 |
+| F | 61 | 1 |
+| F | | 1 |
+| M | 11 | 1 |
+| M | 51 | 1 |
+| | 11 | 2 |
+| | 21 | 1 |
+| | 31 | 2 |
+| | 51 | 2 |
+| | 61 | 1 |
+| | | 1 |
+| | | 9 |
+--------+--------+---+
-(9 rows)
+(15 rows)
!ok
# ROLLUP on column with nulls
# Note the two rows with NULL key (one represents ALL)
select gender, count(*) as c
-from emps
+from emp
group by rollup(gender);
+--------+---+
| GENDER | C |
+--------+---+
-| F | 2 |
-| M | 2 |
-| | 1 |
-| | 5 |
+| F | 6 |
+| M | 3 |
+| | 9 |
+--------+---+
-(4 rows)
+(3 rows)
!ok
# ROLLUP plus ORDER BY
select gender, count(*) as c
-from emps
+from emp
group by rollup(gender)
order by c desc;
+--------+---+
| GENDER | C |
+--------+---+
-| | 5 |
-| F | 2 |
-| M | 2 |
-| | 1 |
+| | 9 |
+| F | 6 |
+| M | 3 |
+--------+---+
-(4 rows)
+(3 rows)
!ok
# ROLLUP cartesian product
-!if (false) {
-select deptno + 1, count(*) as c from emps group by rollup(deptno, gender), rollup(city);
+select deptno, count(*) as c
+from emp
+group by rollup(deptno), rollup(gender);
++--------+---+
+| DEPTNO | C |
++--------+---+
+| 10 | 1 |
+| 10 | 1 |
+| 20 | 1 |
+| 20 | 1 |
+| | 1 |
+| 10 | 2 |
+| 30 | 2 |
+| 30 | 2 |
+| 50 | 1 |
+| 50 | 1 |
+| 50 | 2 |
+| 60 | 1 |
+| 60 | 1 |
+| | 1 |
+| | 3 |
+| | 6 |
+| | 9 |
++--------+---+
+(17 rows)
+
+!ok
+
+# ROLLUP cartesian product of with tuple with expression
+select deptno / 2 + 1 as half1, count(*) as c
+from emp
+group by rollup(deptno / 2, gender), rollup(substring(ename FROM 1 FOR 1));
++-------+---+
+| HALF1 | C |
++-------+---+
+| 11 | 1 |
+| 11 | 1 |
+| 11 | 1 |
+| 11 | 1 |
+| 16 | 1 |
+| 16 | 1 |
+| 16 | 1 |
+| 16 | 1 |
+| 16 | 2 |
+| 16 | 2 |
+| 26 | 1 |
+| 26 | 1 |
+| 26 | 1 |
+| 26 | 1 |
+| 26 | 1 |
+| 26 | 1 |
+| 26 | 2 |
+| 31 | 1 |
+| 31 | 1 |
+| 31 | 1 |
+| 31 | 1 |
+| 6 | 1 |
+| 6 | 1 |
+| 6 | 1 |
+| 6 | 1 |
+| 6 | 1 |
+| 6 | 1 |
+| 6 | 2 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 1 |
+| | 2 |
+| | 2 |
+| | 9 |
++-------+---+
+(40 rows)
+
!ok
-!}
# ROLLUP with HAVING
-select deptno + 1, count(*) as c from emps group by rollup(deptno)
+select deptno + 1 as d1, count(*) as c
+from emp
+group by rollup(deptno)
having count(*) > 3;
-+--------+---+
-| EXPR$0 | C |
-+--------+---+
-| 1 | 5 |
-+--------+---+
++----+---+
+| D1 | C |
++----+---+
+| | 9 |
++----+---+
(1 row)
!ok
+# CUBE and DISTINCT
+select distinct count(*) from emp group by cube(deptno, gender);
++--------+
+| EXPR$0 |
++--------+
+| 1 |
+| 2 |
+| 3 |
+| 6 |
+| 9 |
++--------+
+(5 rows)
+
+!ok
+
+# CUBE and JOIN
+select e.deptno, e.gender, min(e.ename) as min_name
+from emp as e join dept as d using (deptno)
+group by cube(e.deptno, d.deptno, e.gender)
+having count(*) > 2 or gender = 'M' and e.deptno = 10;
++--------+--------+----------+
+| DEPTNO | GENDER | MIN_NAME |
++--------+--------+----------+
+| 10 | M | Bob |
+| 10 | M | Bob |
+| | F | Alice |
+| | | Alice |
++--------+--------+----------+
+(4 rows)
+
+!ok
+
# End agg.oq