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 2015/08/01 00:42:54 UTC
[17/50] [abbrv] incubator-calcite git commit: [CALCITE-704] FILTER
clause for aggregate functions
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 1b2139b..7bc58be 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -198,9 +198,8 @@ public class SqlToRelConverter {
protected final RelOptCluster cluster;
private DefaultValueFactory defaultValueFactory;
private SubqueryConverter subqueryConverter;
- protected final List<RelNode> leaves = new ArrayList<RelNode>();
- private final List<SqlDynamicParam> dynamicParamSqlNodes =
- new ArrayList<SqlDynamicParam>();
+ protected final List<RelNode> leaves = new ArrayList<>();
+ private final List<SqlDynamicParam> dynamicParamSqlNodes = new ArrayList<>();
private final SqlOperatorTable opTab;
private boolean shouldConvertTableAccess;
protected final RelDataTypeFactory typeFactory;
@@ -215,7 +214,7 @@ public class SqlToRelConverter {
* Fields used in name resolution for correlated subqueries.
*/
private final Map<String, DeferredLookup> mapCorrelToDeferred =
- new HashMap<String, DeferredLookup>();
+ new HashMap<>();
private int nextCorrel = 0;
private static final String CORREL_PREFIX = "$cor";
@@ -224,7 +223,7 @@ public class SqlToRelConverter {
* Stack of names of datasets requested by the <code>
* TABLE(SAMPLE(<datasetName>, <query>))</code> construct.
*/
- private final Stack<String> datasetStack = new Stack<String>();
+ private final Stack<String> datasetStack = new Stack<>();
/**
* Mapping of non-correlated subqueries that have been converted to their
@@ -232,7 +231,7 @@ public class SqlToRelConverter {
* already been evaluated.
*/
private final Map<SqlNode, RexNode> mapConvertedNonCorrSubqs =
- new HashMap<SqlNode, RexNode>();
+ new HashMap<>();
public final RelOptTable.ViewExpander viewExpander;
@@ -612,9 +611,8 @@ public class SqlToRelConverter {
bb,
select.getWhere());
- List<SqlNode> orderExprList = new ArrayList<SqlNode>();
- List<RelFieldCollation> collationList =
- new ArrayList<RelFieldCollation>();
+ final List<SqlNode> orderExprList = new ArrayList<>();
+ final List<RelFieldCollation> collationList = new ArrayList<>();
gatherOrderExprs(
bb,
select,
@@ -668,7 +666,7 @@ public class SqlToRelConverter {
if (checkForDupExprs && (rel instanceof LogicalProject)) {
LogicalProject project = (LogicalProject) rel;
final List<RexNode> projectExprs = project.getProjects();
- List<Integer> origins = new ArrayList<Integer>();
+ final List<Integer> origins = new ArrayList<>();
int dupCount = 0;
for (int i = 0; i < projectExprs.size(); i++) {
int x = findExpr(projectExprs.get(i), projectExprs, i);
@@ -782,7 +780,7 @@ public class SqlToRelConverter {
// If extra expressions were added to the project list for sorting,
// add another project to remove them.
if (orderExprList.size() > 0) {
- List<RexNode> exprs = new ArrayList<RexNode>();
+ final List<RexNode> exprs = new ArrayList<>();
final RelDataType rowType = bb.root.getRowType();
final int fieldCount =
rowType.getFieldCount() - orderExprList.size();
@@ -1044,10 +1042,10 @@ public class SqlToRelConverter {
LogicalAggregate aggregate =
LogicalAggregate.create(seek, false, ImmutableBitSet.of(), null,
ImmutableList.of(
- new AggregateCall(SqlStdOperatorTable.COUNT, false,
- ImmutableList.<Integer>of(), longType, null),
- new AggregateCall(SqlStdOperatorTable.COUNT, false,
- args, longType, null)));
+ AggregateCall.create(SqlStdOperatorTable.COUNT, false,
+ ImmutableList.<Integer>of(), -1, longType, null),
+ AggregateCall.create(SqlStdOperatorTable.COUNT, false,
+ args, -1, longType, null)));
LogicalJoin join =
LogicalJoin.create(bb.root,
aggregate,
@@ -1311,7 +1309,7 @@ public class SqlToRelConverter {
final List<RexNode> leftKeys,
SqlNodeList valuesList,
boolean isNotIn) {
- List<RexNode> comparisons = new ArrayList<RexNode>();
+ final List<RexNode> comparisons = new ArrayList<>();
for (SqlNode rightVals : valuesList) {
RexNode rexComparison;
if (leftKeys.size() == 1) {
@@ -1451,7 +1449,7 @@ public class SqlToRelConverter {
null);
}
- List<RelNode> unionInputs = new ArrayList<RelNode>();
+ final List<RelNode> unionInputs = new ArrayList<>();
for (SqlNode node : rows) {
SqlBasicCall call;
if (isRowConstructor(node)) {
@@ -1702,8 +1700,7 @@ public class SqlToRelConverter {
public RexNode convertExpression(
SqlNode node,
Map<String, RexNode> nameToNodeMap) {
- final Map<String, RelDataType> nameToTypeMap =
- new HashMap<String, RelDataType>();
+ final Map<String, RelDataType> nameToTypeMap = new HashMap<>();
for (Map.Entry<String, RexNode> entry : nameToNodeMap.entrySet()) {
nameToTypeMap.put(entry.getKey(), entry.getValue().getType());
}
@@ -2162,8 +2159,8 @@ public class SqlToRelConverter {
}
}
- final List<RexNode> extraLeftExprs = new ArrayList<RexNode>();
- final List<RexNode> extraRightExprs = new ArrayList<RexNode>();
+ final List<RexNode> extraLeftExprs = new ArrayList<>();
+ final List<RexNode> extraRightExprs = new ArrayList<>();
final int leftCount = leftRel.getRowType().getFieldCount();
final int rightCount = rightRel.getRowType().getFieldCount();
if (!containsGet(joinCond)) {
@@ -2187,8 +2184,7 @@ public class SqlToRelConverter {
new RexInputRef(index, field.getType()),
field.getName());
} else {
- return Pair.<RexNode, String>of(
- extraLeftExprs.get(index - leftCount), null);
+ return Pair.of(extraLeftExprs.get(index - leftCount), null);
}
}
},
@@ -2274,8 +2270,8 @@ public class SqlToRelConverter {
case AND:
case OR:
case EQUALS:
- RexCall call = (RexCall) node;
- List<RexNode> list = new ArrayList<RexNode>();
+ final RexCall call = (RexCall) node;
+ final List<RexNode> list = new ArrayList<>();
List<RexNode> operands = Lists.newArrayList(call.getOperands());
for (int i = 0; i < operands.size(); i++) {
RexNode operand = operands.get(i);
@@ -2426,8 +2422,8 @@ public class SqlToRelConverter {
bb.setRoot(ImmutableList.of(leftRel, rightRel));
return bb.convertExpression(condition);
case USING:
- SqlNodeList list = (SqlNodeList) condition;
- List<String> nameList = new ArrayList<String>();
+ final SqlNodeList list = (SqlNodeList) condition;
+ final List<String> nameList = new ArrayList<>();
for (SqlNode columnName : list) {
final SqlIdentifier id = (SqlIdentifier) columnName;
String name = id.getSimple();
@@ -2691,6 +2687,7 @@ public class SqlToRelConverter {
// (yet) appear in the validator type.
final SelectScope selectScope =
SqlValidatorUtil.getEnclosingSelectScope(bb.scope);
+ assert selectScope != null;
final SqlValidatorNamespace selectNamespace =
validator.getNamespace(selectScope.getNode());
final List<String> names =
@@ -3054,8 +3051,8 @@ public class SqlToRelConverter {
RelDataType sourceRowType = sourceRel.getRowType();
final RexNode sourceRef =
rexBuilder.makeRangeReference(sourceRowType, 0, false);
- final List<String> targetColumnNames = new ArrayList<String>();
- final List<RexNode> columnExprs = new ArrayList<RexNode>();
+ final List<String> targetColumnNames = new ArrayList<>();
+ final List<RexNode> columnExprs = new ArrayList<>();
collectInsertTargets(call, sourceRef, targetColumnNames, columnExprs);
final RelOptTable targetTable = getTargetTable(call);
@@ -3063,10 +3060,10 @@ public class SqlToRelConverter {
final List<RelDataTypeField> targetFields =
targetRowType.getFieldList();
final List<RexNode> sourceExps =
- new ArrayList<RexNode>(
+ new ArrayList<>(
Collections.<RexNode>nCopies(targetFields.size(), null));
final List<String> fieldNames =
- new ArrayList<String>(
+ new ArrayList<>(
Collections.<String>nCopies(targetFields.size(), null));
// Walk the name list and place the associated value in the
@@ -3155,7 +3152,7 @@ public class SqlToRelConverter {
RelOptTable targetTable = getTargetTable(call);
// convert update column list from SqlIdentifier to String
- List<String> targetColumnNameList = new ArrayList<String>();
+ final List<String> targetColumnNameList = new ArrayList<>();
for (SqlNode node : call.getTargetColumnList()) {
SqlIdentifier id = (SqlIdentifier) node;
String name = id.getSimple();
@@ -3172,7 +3169,7 @@ public class SqlToRelConverter {
RelOptTable targetTable = getTargetTable(call);
// convert update column list from SqlIdentifier to String
- List<String> targetColumnNameList = new ArrayList<String>();
+ final List<String> targetColumnNameList = new ArrayList<>();
SqlUpdate updateCall = call.getUpdateCall();
if (updateCall != null) {
for (SqlNode targetColumn : updateCall.getTargetColumnList()) {
@@ -3221,7 +3218,7 @@ public class SqlToRelConverter {
LogicalJoin join = (LogicalJoin) mergeSourceRel.getInput(0);
int nSourceFields = join.getLeft().getRowType().getFieldCount();
- List<RexNode> projects = new ArrayList<RexNode>();
+ final List<RexNode> projects = new ArrayList<>();
for (int level1Idx = 0; level1Idx < nLevel1Exprs; level1Idx++) {
if ((level2InsertExprs != null)
&& (level1InsertExprs.get(level1Idx) instanceof RexInputRef)) {
@@ -3347,8 +3344,8 @@ public class SqlToRelConverter {
Blackboard bb) {
// NOTE: Wael 2/04/05: this implementation is not the most efficient in
// terms of planning since it generates XOs that can be reduced.
- List<Object> joinList = new ArrayList<Object>();
- List<SqlNode> lastList = new ArrayList<SqlNode>();
+ final List<Object> joinList = new ArrayList<>();
+ List<SqlNode> lastList = new ArrayList<>();
for (int i = 0; i < operands.size(); i++) {
SqlNode operand = operands.get(i);
if (!(operand instanceof SqlCall)) {
@@ -3395,7 +3392,7 @@ public class SqlToRelConverter {
if (lastList.size() > 0) {
joinList.add(lastList);
}
- lastList = new ArrayList<SqlNode>();
+ lastList = new ArrayList<>();
Collect collect =
new Collect(
cluster,
@@ -3413,8 +3410,8 @@ public class SqlToRelConverter {
Object o = joinList.get(i);
if (o instanceof List) {
List<SqlNode> projectList = (List<SqlNode>) o;
- final List<RexNode> selectList = new ArrayList<RexNode>();
- final List<String> fieldNameList = new ArrayList<String>();
+ final List<RexNode> selectList = new ArrayList<>();
+ final List<String> fieldNameList = new ArrayList<>();
for (int j = 0; j < projectList.size(); j++) {
SqlNode operand = projectList.get(j);
selectList.add(bb.convertExpression(operand));
@@ -3485,14 +3482,13 @@ public class SqlToRelConverter {
replaceSubqueries(bb, selectList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
- List<String> fieldNames = new ArrayList<String>();
- List<RexNode> exprs = new ArrayList<RexNode>();
- Collection<String> aliases = new TreeSet<String>();
+ List<String> fieldNames = new ArrayList<>();
+ final List<RexNode> exprs = new ArrayList<>();
+ final Collection<String> aliases = new TreeSet<>();
// Project any system fields. (Must be done before regular select items,
// because offsets may be affected.)
- final List<SqlMonotonicity> columnMonotonicityList =
- new ArrayList<SqlMonotonicity>();
+ final List<SqlMonotonicity> columnMonotonicityList = new ArrayList<>();
extraSelectItems(
bb,
select,
@@ -3618,14 +3614,13 @@ public class SqlToRelConverter {
return;
}
- List<RelNode> unionRels = new ArrayList<RelNode>();
+ final List<RelNode> unionRels = new ArrayList<>();
for (SqlNode rowConstructor1 : values.getOperandList()) {
SqlCall rowConstructor = (SqlCall) rowConstructor1;
Blackboard tmpBb = createBlackboard(bb.scope, null);
replaceSubqueries(tmpBb, rowConstructor,
RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
- List<Pair<RexNode, String>> exps =
- new ArrayList<Pair<RexNode, String>>();
+ final List<Pair<RexNode, String>> exps = new ArrayList<>();
for (Ord<SqlNode> operand : Ord.zip(rowConstructor.getOperandList())) {
exps.add(
Pair.of(
@@ -3686,7 +3681,7 @@ public class SqlToRelConverter {
public RelNode root;
private List<RelNode> inputs;
private final Map<String, RexNode> mapCorrelateVariableToRexNode =
- new HashMap<String, RexNode>();
+ new HashMap<>();
List<RelNode> cursors;
@@ -3718,14 +3713,12 @@ public class SqlToRelConverter {
* "right" to the subquery.
*/
private final Map<RelNode, Map<Integer, Integer>>
- mapRootRelToFieldProjection =
- new HashMap<RelNode, Map<Integer, Integer>>();
+ mapRootRelToFieldProjection = new HashMap<>();
private final List<SqlMonotonicity> columnMonotonicities =
- new ArrayList<SqlMonotonicity>();
+ new ArrayList<>();
- private final List<RelDataTypeField> systemFieldList =
- new ArrayList<RelDataTypeField>();
+ private final List<RelDataTypeField> systemFieldList = new ArrayList<>();
/**
* Creates a Blackboard.
@@ -3742,7 +3735,7 @@ public class SqlToRelConverter {
Map<String, RexNode> nameToNodeMap) {
this.scope = scope;
this.nameToNodeMap = nameToNodeMap;
- this.cursors = new ArrayList<RelNode>();
+ this.cursors = new ArrayList<>();
subqueryNeedsOuterJoin = false;
}
@@ -4216,7 +4209,7 @@ public class SqlToRelConverter {
public RexNode visit(SqlCall call) {
if (agg != null) {
final SqlOperator op = call.getOperator();
- if (op.isAggregator()) {
+ if (op.isAggregator() || op.getKind() == SqlKind.FILTER) {
return agg.lookupAggregates(call);
}
}
@@ -4469,78 +4462,94 @@ public class SqlToRelConverter {
}
public Void visit(SqlCall call) {
- if (call.getOperator().isAggregator()) {
- assert bb.agg == this;
- List<Integer> args = new ArrayList<Integer>();
- List<RelDataType> argTypes =
- call.getOperator() instanceof SqlCountAggFunction
- ? new ArrayList<RelDataType>(call.getOperandList().size())
- : null;
- try {
- // switch out of agg mode
- bb.agg = null;
- for (SqlNode operand : call.getOperandList()) {
- RexNode convertedExpr;
-
- // special case for COUNT(*): delete the *
- if (operand instanceof SqlIdentifier) {
- SqlIdentifier id = (SqlIdentifier) operand;
- if (id.isStar()) {
- assert call.operandCount() == 1;
- assert args.isEmpty();
- break;
- }
- }
- convertedExpr = bb.convertExpression(operand);
- assert convertedExpr != null;
- if (argTypes != null) {
- argTypes.add(convertedExpr.getType());
- }
- args.add(lookupOrCreateGroupExpr(convertedExpr));
- }
- } finally {
- // switch back into agg mode
- bb.agg = this;
- }
-
- final SqlAggFunction aggFunction =
- (SqlAggFunction) call.getOperator();
- RelDataType type = validator.deriveType(bb.scope, call);
- boolean distinct = false;
- SqlLiteral quantifier = call.getFunctionQuantifier();
- if ((null != quantifier)
- && (quantifier.getValue() == SqlSelectKeyword.DISTINCT)) {
- distinct = true;
- }
- final AggregateCall aggCall =
- new AggregateCall(
- aggFunction,
- distinct,
- args,
- type,
- nameMap.get(call.toString()));
- RexNode rex =
- rexBuilder.addAggCall(
- aggCall,
- groupExprs.size(),
- aggregatingSelectScope.indicator,
- aggCalls,
- aggCallMapping,
- argTypes);
- aggMapping.put(call, rex);
- } else if (call instanceof SqlSelect) {
+ switch (call.getKind()) {
+ case FILTER:
+ translateAgg((SqlCall) call.operand(0), call.operand(1), call);
+ return null;
+ case SELECT:
// rchen 2006-10-17:
// for now do not detect aggregates in subqueries.
return null;
- } else {
+ }
+ if (call.getOperator().isAggregator()) {
+ translateAgg(call, null, call);
+ return null;
+ }
+ for (SqlNode operand : call.getOperandList()) {
+ // Operands are occasionally null, e.g. switched CASE arg 0.
+ if (operand != null) {
+ operand.accept(this);
+ }
+ }
+ return null;
+ }
+
+ private void translateAgg(SqlCall call, SqlNode filter, SqlCall outerCall) {
+ assert bb.agg == this;
+ final List<Integer> args = new ArrayList<>();
+ int filterArg = -1;
+ final List<RelDataType> argTypes =
+ call.getOperator() instanceof SqlCountAggFunction
+ ? new ArrayList<RelDataType>(call.getOperandList().size())
+ : null;
+ try {
+ // switch out of agg mode
+ bb.agg = null;
for (SqlNode operand : call.getOperandList()) {
- // Operands are occasionally null, e.g. switched CASE arg 0.
- if (operand != null) {
- operand.accept(this);
+
+ // special case for COUNT(*): delete the *
+ if (operand instanceof SqlIdentifier) {
+ SqlIdentifier id = (SqlIdentifier) operand;
+ if (id.isStar()) {
+ assert call.operandCount() == 1;
+ assert args.isEmpty();
+ break;
+ }
+ }
+ RexNode convertedExpr = bb.convertExpression(operand);
+ assert convertedExpr != null;
+ if (argTypes != null) {
+ argTypes.add(convertedExpr.getType());
}
+ args.add(lookupOrCreateGroupExpr(convertedExpr));
}
+
+ if (filter != null) {
+ RexNode convertedExpr = bb.convertExpression(filter);
+ assert convertedExpr != null;
+ filterArg = lookupOrCreateGroupExpr(convertedExpr);
+ }
+ } finally {
+ // switch back into agg mode
+ bb.agg = this;
}
- return null;
+
+ final SqlAggFunction aggFunction =
+ (SqlAggFunction) call.getOperator();
+ RelDataType type = validator.deriveType(bb.scope, call);
+ boolean distinct = false;
+ SqlLiteral quantifier = call.getFunctionQuantifier();
+ if ((null != quantifier)
+ && (quantifier.getValue() == SqlSelectKeyword.DISTINCT)) {
+ distinct = true;
+ }
+ final AggregateCall aggCall =
+ AggregateCall.create(
+ aggFunction,
+ distinct,
+ args,
+ filterArg,
+ type,
+ nameMap.get(outerCall.toString()));
+ RexNode rex =
+ rexBuilder.addAggCall(
+ aggCall,
+ groupExprs.size(),
+ aggregatingSelectScope.indicator,
+ aggCalls,
+ aggCallMapping,
+ argTypes);
+ aggMapping.put(outerCall, rex);
}
private int lookupOrCreateGroupExpr(RexNode expr) {
@@ -4661,7 +4670,7 @@ public class SqlToRelConverter {
*/
private static class LookupContext {
private final List<Pair<RelNode, Integer>> relOffsetList =
- new ArrayList<Pair<RelNode, Integer>>();
+ new ArrayList<>();
/**
* Creates a LookupContext with multiple input relational expressions.
@@ -4768,7 +4777,7 @@ public class SqlToRelConverter {
// Replace original expression with CAST of not one
// of the supported types
if (histogramType != type) {
- exprs = new ArrayList<RexNode>(exprs);
+ exprs = new ArrayList<>(exprs);
exprs.set(
0,
reinterpretCast
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/main/java/org/apache/calcite/util/Bug.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java b/core/src/main/java/org/apache/calcite/util/Bug.java
index a0ced22..a1309ab 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -156,9 +156,13 @@ public abstract class Bug {
* Table aliases should follow case-sensitivity policy</a> is fixed. */
public static final boolean CALCITE_319_FIXED = false;
- /** Whether
+ /** Whether the remaining issues raised in
* <a href="https://issues.apache.org/jira/browse/CALCITE-461">[CALCITE-461]
- * Convert more planner rules to handle grouping sets</a> is fixed. */
+ * Convert more planner rules to handle grouping sets</a> are fixed.
+ *
+ * <p>Now that [CALCITE-461] is fixed, the tracking bug is
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-574">[CALCITE-574]
+ * Remove org.apache.calcite.util.Bug.CALCITE_461_FIXED</a>. */
public static final boolean CALCITE_461_FIXED = false;
/** Whether
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/main/java/org/apache/calcite/util/mapping/Mappings.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/mapping/Mappings.java b/core/src/main/java/org/apache/calcite/util/mapping/Mappings.java
index 385df5b..556f090 100644
--- a/core/src/main/java/org/apache/calcite/util/mapping/Mappings.java
+++ b/core/src/main/java/org/apache/calcite/util/mapping/Mappings.java
@@ -626,9 +626,7 @@ public abstract class Mappings {
int target = mapping.getTargetOpt(source);
return target < 0 ? null : target + offset;
}
- },
- mapping.getSourceCount(),
- targetCount);
+ }, mapping.getSourceCount(), targetCount);
}
/**
@@ -713,6 +711,12 @@ public abstract class Mappings {
};
}
+ /** Applies a mapping to an optional integer, returning an optional
+ * result. */
+ public static int apply(TargetMapping mapping, int i) {
+ return i < 0 ? i : mapping.getTarget(i);
+ }
+
//~ Inner Interfaces -------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
----------------------------------------------------------------------
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 60f14ba..e2321bb 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -94,10 +94,12 @@ AggregateIllegalInClause=Aggregate expression is illegal in {0} clause
WindowedAggregateIllegalInClause=Windowed aggregate expression is illegal in {0} clause
AggregateIllegalInGroupBy=Aggregate expression is illegal in GROUP BY clause
NestedAggIllegal=Aggregate expressions cannot be nested
+AggregateInFilterIllegal=FILTER must not contain aggregate expression
AggregateIllegalInOrderBy=Aggregate expression is illegal in ORDER BY clause of non-aggregating SELECT
CondMustBeBoolean={0} clause must be a condition
HavingMustBeBoolean=HAVING clause must be a condition
OverNonAggregate=OVER must be applied to aggregate function
+FilterNonAggregate=FILTER must be applied to aggregate function
CannotOverrideWindowAttribute=Cannot override window attribute
ColumnCountMismatchInSetop=Column count mismatch in {0}
ColumnTypeMismatchInSetop=Type mismatch in column {0,number} of {1}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
index f5dd8a2..cff82e9 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
@@ -139,11 +139,12 @@ public class RelWriterTest {
LogicalAggregate.create(filter, false,
ImmutableBitSet.of(0), null,
ImmutableList.of(
- new AggregateCall(SqlStdOperatorTable.COUNT,
- true, ImmutableList.of(1), bigIntType, "c"),
- new AggregateCall(SqlStdOperatorTable.COUNT,
- false, ImmutableList.<Integer>of(), bigIntType,
- "d")));
+ AggregateCall.create(SqlStdOperatorTable.COUNT,
+ true, ImmutableList.of(1), -1, bigIntType,
+ "c"),
+ AggregateCall.create(SqlStdOperatorTable.COUNT,
+ false, ImmutableList.<Integer>of(), -1,
+ bigIntType, "d")));
aggregate.explain(writer);
return writer.asString();
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java b/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
index e5731f4..8acd917 100644
--- a/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
@@ -163,9 +163,8 @@ public class TraitPropagationTest {
.build());
// aggregate on s, count
- AggregateCall aggCall = new AggregateCall(SqlStdOperatorTable.COUNT,
- false, Collections.singletonList(1),
- sqlBigInt, "cnt");
+ AggregateCall aggCall = AggregateCall.create(SqlStdOperatorTable.COUNT,
+ false, Collections.singletonList(1), -1, sqlBigInt, "cnt");
RelNode agg = new LogicalAggregate(cluster,
cluster.traitSetOf(Convention.NONE), project, false,
ImmutableBitSet.of(0), null, Collections.singletonList(aggCall));
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 11eaf7c..a04b5dd 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -719,6 +719,17 @@ public class SqlParserTest {
checkExp("ln(power(2,2))", "LN(POWER(2, 2))");
}
+ @Test public void testAggregateFilter() {
+ sql("select sum(sal) filter (where gender = 'F') as femaleSal,\n"
+ + " sum(sal) filter (where true) allSal,\n"
+ + " count(distinct deptno) filter (where (deptno < 40))\n"
+ + "from emp")
+ .ok("SELECT (SUM(`SAL`) FILTER (WHERE (`GENDER` = 'F'))) AS `FEMALESAL`,"
+ + " (SUM(`SAL`) FILTER (WHERE TRUE)) AS `ALLSAL`,"
+ + " (COUNT(DISTINCT `DEPTNO`) FILTER (WHERE (`DEPTNO` < 40)))\n"
+ + "FROM `EMP`");
+ }
+
@Test public void testGroup() {
check(
"select deptno, min(foo) as x from emp group by deptno, gender",
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 c5a75ca..678f053 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2590,6 +2590,31 @@ public class JdbcTest {
.returns("c0=1997; m0=85452\n");
}
+ @Test public void testAggregateFilter() {
+ final String s = "select \"the_month\",\n"
+ + " count(*) as \"c\",\n"
+ + " count(*) filter (where \"day_of_month\" > 20) as \"c2\"\n"
+ + "from \"time_by_day\" as \"time_by_day\"\n"
+ + "where \"time_by_day\".\"the_year\" = 1997\n"
+ + "group by \"time_by_day\".\"the_month\"\n"
+ + "order by \"time_by_day\".\"the_month\"";
+ CalciteAssert.that()
+ .with(CalciteAssert.Config.FOODMART_CLONE)
+ .query(s)
+ .returns("the_month=April; c=30; c2=10\n"
+ + "the_month=August; c=31; c2=11\n"
+ + "the_month=December; c=31; c2=11\n"
+ + "the_month=February; c=28; c2=8\n"
+ + "the_month=January; c=31; c2=11\n"
+ + "the_month=July; c=31; c2=11\n"
+ + "the_month=June; c=30; c2=10\n"
+ + "the_month=March; c=31; c2=11\n"
+ + "the_month=May; c=31; c2=11\n"
+ + "the_month=November; c=30; c2=10\n"
+ + "the_month=October; c=31; c2=11\n"
+ + "the_month=September; c=30; c2=10\n");
+ }
+
/** Tests a simple IN query implemented as a semi-join. */
@Test public void testSimpleIn() {
CalciteAssert.hr()
@@ -5227,6 +5252,52 @@ public class JdbcTest {
.withDefaultSchema("adhoc");
}
+ /** Tests user-defined aggregate function with FILTER.
+ *
+ * <p>Also tests that we do not try to push ADAF to JDBC source. */
+ @Test public void testUserDefinedAggregateFunctionWithFilter() throws Exception {
+ final String sum = MyStaticSumFunction.class.getName();
+ final String sum2 = MySumFunction.class.getName();
+ final CalciteAssert.AssertThat with = CalciteAssert.model("{\n"
+ + " version: '1.0',\n"
+ + " schemas: [\n"
+ + SCOTT_SCHEMA
+ + ",\n"
+ + " {\n"
+ + " name: 'adhoc',\n"
+ + " functions: [\n"
+ + " {\n"
+ + " name: 'MY_SUM',\n"
+ + " className: '" + sum + "'\n"
+ + " },\n"
+ + " {\n"
+ + " name: 'MY_SUM2',\n"
+ + " className: '" + sum2 + "'\n"
+ + " }\n"
+ + " ]\n"
+ + " }\n"
+ + " ]\n"
+ + "}")
+ .withDefaultSchema("adhoc");
+ with.query("select deptno, \"adhoc\".my_sum(deptno) as p\n"
+ + "from scott.emp\n"
+ + "group by deptno\n")
+ .returns(
+ "DEPTNO=20; P=100\n"
+ + "DEPTNO=10; P=30\n"
+ + "DEPTNO=30; P=180\n");
+
+ with.query("select deptno,\n"
+ + " \"adhoc\".my_sum(deptno) filter (where job = 'CLERK') as c,\n"
+ + " \"adhoc\".my_sum(deptno) filter (where job = 'XXX') as x\n"
+ + "from scott.emp\n"
+ + "group by deptno\n")
+ .returns(
+ "DEPTNO=20; C=40; X=0\n"
+ + "DEPTNO=10; C=10; X=0\n"
+ + "DEPTNO=30; C=30; X=0\n");
+ }
+
/** Tests resolution of functions using schema paths. */
@Test public void testPath() throws Exception {
final String name = MyPlusFunction.class.getName();
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 c19ec03..c3a1f40 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -350,6 +350,12 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
+ "group by deptno").ok();
}
+ @Test public void testAggFilter() {
+ sql("select deptno, sum(sal * 2) filter (where empno < 10), count(*) "
+ + "from emp "
+ + "group by deptno").ok();
+ }
+
@Test public void testSelectDistinct() {
sql("select distinct sal + 5 from emp").ok();
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 aa097d9..a2e6c20 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6151,6 +6151,28 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
check("select sum(empno) from emp order by sum(empno)");
}
+ @Test public void testAggregateFilter() {
+ sql("select sum(empno) filter (where deptno < 10) as s from emp")
+ .type("RecordType(INTEGER S) NOT NULL");
+ }
+
+ @Test public void testAggregateFilterNotBoolean() {
+ sql("select sum(empno) filter (where ^deptno + 10^) from emp")
+ .fails("FILTER clause must be a condition");
+ }
+
+ @Test public void testAggregateFilterInHaving() {
+ sql("select sum(empno) as s from emp\n"
+ + "group by deptno\n"
+ + "having sum(empno) filter (where deptno < 20) > 10")
+ .ok();
+ }
+
+ @Test public void testAggregateFilterContainsAggregate() {
+ sql("select sum(empno) filter (where ^count(*) < 10^) from emp")
+ .fails("FILTER must not contain aggregate expression");
+ }
+
@Test public void testCorrelatingVariables() {
// reference to unqualified correlating column
check("select * from emp where exists (\n"
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 2d85361..7ad5b41 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2409,4 +2409,16 @@ LogicalAggregate(group=[{0}], EXPR$1=[MIN($1)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testAggFilter">
+ <Resource name="sql">
+ <![CDATA[select deptno, sum(sal * 2) filter (where empno < 10), count(*) from emp group by deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $2], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], $f1=[*($5, 2)], $f2=[<($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/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 c61e10e..c54655e 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -553,4 +553,126 @@ from emp group by cube(deptno, gender);
!ok
+!use scott
+
+# Aggregate FILTER
+select deptno,
+ sum(sal) filter (where job = 'CLERK') c_sal,
+ sum(sal) filter (where job = 'CLERK' and deptno > 10) c10_sal,
+ max(sal) filter (where job = 'CLERK') as max_c,
+ min(sal) filter (where job = 'CLERK') as min_c,
+ max(sal) filter (where job = 'CLERK')
+ - min(sal) filter (where job = 'CLERK') as range_c,
+ max(sal) filter (where job = 'SALESMAN')
+ - min(sal) filter (where job = 'SALESMAN') as range_m
+from "scott".emp
+group by deptno;
++--------+---------+---------+---------+---------+---------+---------+
+| DEPTNO | C_SAL | C10_SAL | MAX_C | MIN_C | RANGE_C | RANGE_M |
++--------+---------+---------+---------+---------+---------+---------+
+| 10 | 1300.00 | | 1300.00 | 1300.00 | 0.00 | |
+| 20 | 1900.00 | 1900.00 | 1100.00 | 800.00 | 300.00 | |
+| 30 | 950.00 | 950.00 | 950.00 | 950.00 | 0.00 | 350.00 |
++--------+---------+---------+---------+---------+---------+---------+
+(3 rows)
+
+!ok
+
+# Aggregate FILTER on condition in GROUP BY
+select deptno,
+ sum(sal) filter (where deptno = 10) sal_10
+from "scott".emp
+group by deptno;
++--------+---------+
+| DEPTNO | SAL_10 |
++--------+---------+
+| 10 | 8750.00 |
+| 20 | |
+| 30 | |
++--------+---------+
+(3 rows)
+
+!ok
+
+# Aggregate FILTER with HAVING
+select deptno
+from "scott".emp
+group by deptno
+having sum(sal) filter (where job = 'CLERK') > 1000;
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
++--------+
+(2 rows)
+
+!ok
+
+# Aggregate FILTER with ORDER BY
+select deptno
+from "scott".emp
+group by deptno
+order by sum(sal) filter (where job = 'CLERK');
++--------+
+| DEPTNO |
++--------+
+| 30 |
+| 10 |
+| 20 |
++--------+
+(3 rows)
+
+!ok
+
+# Aggregate FILTER with JOIN
+select dept.deptno,
+ sum(sal) filter (where 1 < 2) as s,
+ sum(sal) as s1,
+ count(*) filter (where emp.ename < dept.dname) as c
+from "scott".emp
+join "scott".dept using (deptno)
+group by dept.deptno;
++--------+----------+----------+---+
+| DEPTNO | S | S1 | C |
++--------+----------+----------+---+
+| 10 | 8750.00 | 8750.00 | 0 |
+| 20 | 10875.00 | 10875.00 | 3 |
+| 30 | 9400.00 | 9400.00 | 4 |
++--------+----------+----------+---+
+(3 rows)
+
+!ok
+
+# Aggregate FILTER with DISTINCT
+select deptno,
+ count(distinct job) as cdj
+from "scott".emp
+group by deptno;
++--------+-----+
+| DEPTNO | CDJ |
++--------+-----+
+| 10 | 3 |
+| 20 | 3 |
+| 30 | 3 |
++--------+-----+
+(3 rows)
+
+!ok
+
+select deptno,
+ count(distinct job) filter (where job <> 'SALESMAN') as cdj
+from "scott".emp
+group by deptno;
++--------+-----+
+| DEPTNO | CDJ |
++--------+-----+
+| 10 | 3 |
+| 20 | 3 |
+| 30 | 2 |
++--------+-----+
+(3 rows)
+
+!ok
+
# End agg.oq
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f5434a49/doc/REFERENCE.md
----------------------------------------------------------------------
diff --git a/doc/REFERENCE.md b/doc/REFERENCE.md
index e0c6278..b67ad57 100644
--- a/doc/REFERENCE.md
+++ b/doc/REFERENCE.md
@@ -477,6 +477,20 @@ Not implemented:
### Aggregate functions
+Syntax:
+
+```SQL
+aggregateCall:
+ agg( [ DISTINCT ] value [, value]* ) [ FILTER ( WHERE condition ) ]
+ | agg(*) [ FILTER ( WHERE condition ) ]
+```
+
+If `FILTER` is present, the aggregate function only considers rows for which
+*condition* evaluates to TRUE.
+
+If `DISTINCT` is present, duplicate argument values are eliminated before being
+passed to the aggregate function.
+
| Operator syntax | Description
| ---------------------------------- | -----------
| COUNT( [ DISTINCT ] value [, value]* ) | Returns the number of input rows for which *value* is not null (wholly not null if *value* is composite)