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:39 UTC
[02/50] [abbrv] incubator-calcite git commit: Support AVG
aggregations in MongoDB, support basic calculations in mongo (+, -, /, *, case,
etc)
Support AVG aggregations in MongoDB, support basic calculations in mongo (+, -, /, *, case, etc)
fixes #18
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/9e272f29
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/9e272f29
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/9e272f29
Branch: refs/heads/branch-release
Commit: 9e272f2913ac6f7e3eb9e99cffcd0571df227a78
Parents: b18a4df
Author: Vladimir Sitnikov <si...@gmail.com>
Authored: Sat Apr 18 13:55:55 2015 +0300
Committer: Vladimir Sitnikov <si...@gmail.com>
Committed: Sat Apr 18 13:55:55 2015 +0300
----------------------------------------------------------------------
.../calcite/adapter/mongodb/MongoAggregate.java | 4 ++
.../calcite/adapter/mongodb/MongoRules.java | 64 +++++++++++++++++++-
.../org/apache/calcite/test/MongoAdapterIT.java | 41 ++++++++++++-
3 files changed, 103 insertions(+), 6 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/9e272f29/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
----------------------------------------------------------------------
diff --git a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
index 35f4661..fdf043d 100644
--- a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
+++ b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoAggregate.java
@@ -171,6 +171,10 @@ public class MongoAggregate
assert args.size() == 1;
final String inName = inNames.get(args.get(0));
return "{$max: " + MongoRules.maybeQuote("$" + inName) + "}";
+ } else if (aggregation == SqlStdOperatorTable.AVG) {
+ assert args.size() == 1;
+ final String inName = inNames.get(args.get(0));
+ return "{$avg: " + MongoRules.maybeQuote("$" + inName) + "}";
} else {
throw new AssertionError("unknown aggregate " + aggregation);
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/9e272f29/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
----------------------------------------------------------------------
diff --git a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
index 0249cce..e7596b3 100644
--- a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
+++ b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoRules.java
@@ -38,15 +38,19 @@ import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.Bug;
+import org.apache.calcite.util.Util;
import org.apache.calcite.util.trace.CalciteTrace;
import java.util.AbstractList;
import java.util.ArrayList;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import java.util.logging.Logger;
/**
@@ -124,6 +128,29 @@ public class MongoRules {
private final JavaTypeFactory typeFactory;
private final List<String> inFields;
+ private static final Map<SqlOperator, String> MONGO_OPERATORS =
+ new HashMap<SqlOperator, String>();
+
+ static {
+ // Arithmetic
+ MONGO_OPERATORS.put(SqlStdOperatorTable.DIVIDE, "$divide");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.MULTIPLY, "$multiply");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.MOD, "$mod");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.PLUS, "$add");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.MINUS, "$subtract");
+ // Boolean
+ MONGO_OPERATORS.put(SqlStdOperatorTable.AND, "$and");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.OR, "$or");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.NOT, "$not");
+ // Comparison
+ MONGO_OPERATORS.put(SqlStdOperatorTable.EQUALS, "$eq");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.NOT_EQUALS, "$ne");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.GREATER_THAN, "$gt");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, "$gte");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.LESS_THAN, "$lt");
+ MONGO_OPERATORS.put(SqlStdOperatorTable.LESS_THAN_OR_EQUAL, "$lte");
+ }
+
protected RexToMongoTranslator(JavaTypeFactory typeFactory,
List<String> inFields) {
super(true);
@@ -135,10 +162,10 @@ public class MongoRules {
if (literal.getValue() == null) {
return "null";
}
- return "{$ifNull: [null, "
+ return "{$literal: "
+ RexToLixTranslator.translateLiteral(literal, literal.getType(),
typeFactory, RexImpTable.NullAs.NOT_POSSIBLE)
- + "]}";
+ + "}";
}
@Override public String visitInputRef(RexInputRef inputRef) {
@@ -155,6 +182,10 @@ public class MongoRules {
if (call.getKind() == SqlKind.CAST) {
return strings.get(0);
}
+ String stdOperator = MONGO_OPERATORS.get(call.getOperator());
+ if (stdOperator != null) {
+ return "{" + stdOperator + ": [" + Util.commaList(strings) + "]}";
+ }
if (call.getOperator() == SqlStdOperatorTable.ITEM) {
final RexNode op1 = call.operands.get(1);
if (op1 instanceof RexLiteral
@@ -166,7 +197,34 @@ public class MongoRules {
return strings.get(0) + "[" + strings.get(1) + "]";
}
}
- return super.visitCall(call);
+ if (call.getOperator() == SqlStdOperatorTable.CASE) {
+ StringBuilder sb = new StringBuilder();
+ StringBuilder finish = new StringBuilder();
+ // case(a, b, c) -> $cond:[a, b, c]
+ // case(a, b, c, d) -> $cond:[a, b, $cond:[c, d, null]]
+ // case(a, b, c, d, e) -> $cond:[a, b, $cond:[c, d, e]]
+ for (int i = 0; i < strings.size(); i += 2) {
+ sb.append("{$cond:[");
+ finish.append("]}");
+
+ sb.append(strings.get(i));
+ sb.append(',');
+ sb.append(strings.get(i + 1));
+ sb.append(',');
+ if (i == strings.size() - 3) {
+ sb.append(strings.get(i + 2));
+ break;
+ }
+ if (i == strings.size() - 2) {
+ sb.append("null");
+ break;
+ }
+ }
+ sb.append(finish);
+ return sb.toString();
+ }
+ throw new IllegalArgumentException("Translation of " + call.toString()
+ + " is not supported by MongoProject");
}
private String stripQuotes(String s) {
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/9e272f29/mongodb/src/test/java/org/apache/calcite/test/MongoAdapterIT.java
----------------------------------------------------------------------
diff --git a/mongodb/src/test/java/org/apache/calcite/test/MongoAdapterIT.java b/mongodb/src/test/java/org/apache/calcite/test/MongoAdapterIT.java
index 46cb2f1..7d63293 100644
--- a/mongodb/src/test/java/org/apache/calcite/test/MongoAdapterIT.java
+++ b/mongodb/src/test/java/org/apache/calcite/test/MongoAdapterIT.java
@@ -424,7 +424,6 @@ public class MongoAdapterIT {
"{$group: {_id: {}, 'EXPR$0': {$sum: 1}}}"));
}
- @Ignore("Returns 2 instead of 2*29353 == 58706")
@Test public void testCountGroupByEmptyMultiplyBy2() {
CalciteAssert.that()
.enable(enabled())
@@ -433,7 +432,8 @@ public class MongoAdapterIT {
.returns("EXPR$0=58706\n")
.queryContains(
mongoChecker(
- "{$group: {_id: {}, 'EXPR$0': {$sum: 1}}}"));
+ "{$group: {_id: {}, _0: {$sum: 1}}}",
+ "{$project: {'EXPR$0': {$multiply: ['$_0', {$literal: 2}]}}}"));
}
@Test public void testGroupByOneColumnNotProjected() {
@@ -489,6 +489,41 @@ public class MongoAdapterIT {
"{$sort: {STATE: 1}}"));
}
+ @Test public void testGroupByAvg() {
+ CalciteAssert.that()
+ .enable(enabled())
+ .with(ZIPS)
+ .query(
+ "select state, avg(pop) as a from zips group by state order by state")
+ .limit(2)
+ .returns("STATE=AK; A=2793.3230769230768\n"
+ + "STATE=AL; A=7126.255731922399\n")
+ .queryContains(
+ mongoChecker(
+ "{$project: {POP: '$pop', STATE: '$state'}}",
+ "{$group: {_id: '$STATE', A: {$avg: '$POP'}}}",
+ "{$project: {STATE: '$_id', A: '$A'}}",
+ "{$sort: {STATE: 1}}"));
+ }
+
+ @Test public void testGroupByAvgSumCount() {
+ CalciteAssert.that()
+ .enable(enabled())
+ .with(ZIPS)
+ .query(
+ "select state, avg(pop) as a, sum(pop) as s, count(pop) as c from zips group by state order by state")
+ .limit(2)
+ .returns("STATE=AK; A=2793.3230769230768; S=544698; C=195\n"
+ + "STATE=AL; A=7126.255731922399; S=4040587; C=567\n")
+ .queryContains(
+ mongoChecker(
+ "{$project: {POP: '$pop', STATE: '$state'}}",
+ "{$group: {_id: '$STATE', _1: {$sum: '$POP'}, _2: {$sum: {$cond: [ {$eq: ['POP', null]}, 0, 1]}}}}",
+ "{$project: {STATE: '$_id', _1: '$_1', _2: '$_2'}}",
+ "{$sort: {STATE: 1}}",
+ "{$project: {STATE: 1, A: {$divide: [{$cond:[{$eq: ['$_2', {$literal: 0}]},null,'$_1']}, '$_2']}, S: {$cond:[{$eq: ['$_2', {$literal: 0}]},null,'$_1']}, C: '$_2'}}"));
+ }
+
@Test public void testGroupByHaving() {
CalciteAssert.that()
.enable(enabled())
@@ -642,7 +677,7 @@ public class MongoAdapterIT {
mongoChecker(
"{$project: {CITY: '$city', STATE: '$state'}}",
"{$sort: {STATE: 1, CITY: 1}}",
- "{$project: {STATE: 1, CITY: 1, ZERO: {$ifNull: [null, 0]}}}"));
+ "{$project: {STATE: 1, CITY: 1, ZERO: {$literal: 0}}}"));
}
@Test public void testFilter() {