You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by hy...@apache.org on 2019/11/21 01:57:20 UTC
[calcite] branch master updated: [CALCITE-3112] Support Window in
RelToSqlConverter (Wenhui Tang)
This is an automated email from the ASF dual-hosted git repository.
hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
View the commit online:
https://github.com/apache/calcite/commit/cacc82ce99258abee0fad636d800814a6dcb1b77
The following commit(s) were added to refs/heads/master by this push:
new cacc82c [CALCITE-3112] Support Window in RelToSqlConverter (Wenhui Tang)
cacc82c is described below
commit cacc82ce99258abee0fad636d800814a6dcb1b77
Author: wenhuitang <we...@yeah.net>
AuthorDate: Tue Jun 11 21:11:45 2019 +0800
[CALCITE-3112] Support Window in RelToSqlConverter (Wenhui Tang)
Close #1271
---
.../java/org/apache/calcite/rel/core/Window.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverter.java | 25 +++++++
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 50 +++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 86 ++++++++++++++++++++++
4 files changed, 162 insertions(+), 1 deletion(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Window.java b/core/src/main/java/org/apache/calcite/rel/core/Window.java
index 67b77a0..cd57b3a 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Window.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Window.java
@@ -390,7 +390,7 @@ public abstract class Window extends SingleRel {
}
@Override public RexCall clone(RelDataType type, List<RexNode> operands) {
- throw new UnsupportedOperationException();
+ return super.clone(type, operands);
}
}
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index cf5cbc2..017b5fc 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -40,6 +40,7 @@ import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rel.core.Values;
+import org.apache.calcite.rel.core.Window;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalSort;
import org.apache.calcite.rel.type.RelDataType;
@@ -251,6 +252,30 @@ public class RelToSqlConverter extends SqlImplementor
}
/** @see #dispatch */
+ public Result visit(Window e) {
+ Result x = visitChild(0, e.getInput());
+ Builder builder = x.builder(e);
+ RelNode input = e.getInput();
+ int inputFieldCount = input.getRowType().getFieldCount();
+ final List<SqlNode> rexOvers = new ArrayList<>();
+ for (Window.Group group: e.groups) {
+ rexOvers.addAll(builder.context.toSql(group, e.constants, inputFieldCount));
+ }
+ final List<SqlNode> selectList = new ArrayList<>();
+
+ for (RelDataTypeField field: input.getRowType().getFieldList()) {
+ addSelect(selectList, builder.context.field(field.getIndex()), e.getRowType());
+ }
+
+ for (SqlNode rexOver: rexOvers) {
+ addSelect(selectList, rexOver, e.getRowType());
+ }
+
+ builder.setSelect(new SqlNodeList(selectList, POS));
+ return builder.result();
+ }
+
+ /** @see #dispatch */
public Result visit(Aggregate e) {
return visitAggregate(e, e.getGroupSet().toList());
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index a2f273d..d2c1800 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -24,6 +24,7 @@ import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.Window;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexCall;
@@ -38,6 +39,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexOver;
import org.apache.calcite.rex.RexPatternFieldRef;
import org.apache.calcite.rex.RexProgram;
+import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexWindow;
import org.apache.calcite.rex.RexWindowBound;
@@ -729,6 +731,54 @@ public abstract class SqlImplementor {
}
}
+ public List<SqlNode> toSql(Window.Group group, ImmutableList<RexLiteral> constants,
+ int inputFieldCount) {
+ final List<SqlNode> rexOvers = new ArrayList<>();
+ final List<SqlNode> partitionKeys = new ArrayList<>();
+ final List<SqlNode> orderByKeys = new ArrayList<>();
+ for (int partition: group.keys) {
+ partitionKeys.add(this.field(partition));
+ }
+ for (RelFieldCollation collation: group.orderKeys.getFieldCollations()) {
+ this.addOrderItem(orderByKeys, collation);
+ }
+ SqlLiteral isRows = SqlLiteral.createBoolean(group.isRows, POS);
+ SqlNode lowerBound = null;
+ SqlNode upperBound = null;
+
+ final SqlLiteral allowPartial = null;
+
+ for (Window.RexWinAggCall winAggCall: group.aggCalls) {
+ SqlAggFunction aggFunction = (SqlAggFunction) winAggCall.getOperator();
+ final SqlWindow sqlWindow = SqlWindow.create(null, null,
+ new SqlNodeList(partitionKeys, POS), new SqlNodeList(orderByKeys, POS),
+ isRows, lowerBound, upperBound, allowPartial, POS);
+ if (aggFunction.allowsFraming()) {
+ lowerBound = createSqlWindowBound(group.lowerBound);
+ upperBound = createSqlWindowBound(group.upperBound);
+ sqlWindow.setLowerBound(lowerBound);
+ sqlWindow.setUpperBound(upperBound);
+ }
+
+ RexShuttle replaceConstants = new RexShuttle() {
+ @Override public RexNode visitInputRef(RexInputRef inputRef) {
+ int index = inputRef.getIndex();
+ RexNode ref;
+ if (index > inputFieldCount - 1) {
+ ref = constants.get(index - inputFieldCount);
+ } else {
+ ref = inputRef;
+ }
+ return ref;
+ }
+ };
+ RexCall aggCall = (RexCall) winAggCall.accept(replaceConstants);
+ List<SqlNode> operands = toSql(null, aggCall.operands);
+ rexOvers.add(createOverCall(aggFunction, operands, sqlWindow));
+ }
+ return rexOvers;
+ }
+
protected Context getAliasContext(RexCorrelVariable variable) {
throw new UnsupportedOperationException();
}
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index f5418a4..ea438d4 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -24,6 +24,7 @@ import org.apache.calcite.plan.hep.HepPlanner;
import org.apache.calcite.plan.hep.HepProgramBuilder;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.rules.ProjectToWindowRule;
import org.apache.calcite.rel.rules.PruneEmptyRules;
import org.apache.calcite.rel.rules.UnionMergeRule;
import org.apache.calcite.rel.type.RelDataType;
@@ -2034,6 +2035,91 @@ public class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-3112">[CALCITE-3112]
+ * Support Window in RelToSqlConverter</a>. */
+ @Test public void testConvertWinodwToSql() {
+ String query0 = "SELECT row_number() over (order by \"hire_date\") FROM \"employee\"";
+ String expected0 = "SELECT ROW_NUMBER() OVER (ORDER BY \"hire_date\") AS \"$0\"\n"
+ + "FROM \"foodmart\".\"employee\"";
+
+ String query1 = "SELECT rank() over (order by \"hire_date\") FROM \"employee\"";
+ String expected1 = "SELECT RANK() OVER (ORDER BY \"hire_date\") AS \"$0\"\n"
+ + "FROM \"foodmart\".\"employee\"";
+
+ String query2 = "SELECT lead(\"employee_id\",1,'NA') over "
+ + "(partition by \"hire_date\" order by \"employee_id\")\n"
+ + "FROM \"employee\"";
+ String expected2 = "SELECT LEAD(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"hire_date\" "
+ + "ORDER BY \"employee_id\") AS \"$0\"\n"
+ + "FROM \"foodmart\".\"employee\"";
+
+ String query3 = "SELECT lag(\"employee_id\",1,'NA') over "
+ + "(partition by \"hire_date\" order by \"employee_id\")\n"
+ + "FROM \"employee\"";
+ String expected3 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS \"$0\"\n"
+ + "FROM \"foodmart\".\"employee\"";
+
+ String query4 = "SELECT lag(\"employee_id\",1,'NA') "
+ + "over (partition by \"hire_date\" order by \"employee_id\") as lag1, "
+ + "lag(\"employee_id\",1,'NA') "
+ + "over (partition by \"birth_date\" order by \"employee_id\") as lag2, "
+ + "count(*) over (partition by \"hire_date\" order by \"employee_id\") as count1, "
+ + "count(*) over (partition by \"birth_date\" order by \"employee_id\") as count2\n"
+ + "FROM \"employee\"";
+ String expected4 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS \"$0\", "
+ + "LAG(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS \"$1\", "
+ + "COUNT(*) OVER (PARTITION BY \"hire_date\" ORDER BY \"employee_id\" "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$2\", "
+ + "COUNT(*) OVER (PARTITION BY \"birth_date\" ORDER BY \"employee_id\" "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$3\"\n"
+ + "FROM \"foodmart\".\"employee\"";
+
+ String query5 = "SELECT lag(\"employee_id\",1,'NA') "
+ + "over (partition by \"hire_date\" order by \"employee_id\") as lag1, "
+ + "lag(\"employee_id\",1,'NA') "
+ + "over (partition by \"birth_date\" order by \"employee_id\") as lag2, "
+ + "max(sum(\"employee_id\")) over (partition by \"hire_date\" order by \"employee_id\") as count1, "
+ + "max(sum(\"employee_id\")) over (partition by \"birth_date\" order by \"employee_id\") as count2\n"
+ + "FROM \"employee\" group by \"employee_id\", \"hire_date\", \"birth_date\"";
+ String expected5 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS \"$0\", "
+ + "LAG(\"employee_id\", 1, 'NA') OVER "
+ + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS \"$1\", "
+ + "MAX(SUM(\"employee_id\")) OVER (PARTITION BY \"hire_date\" ORDER BY \"employee_id\" "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$2\", "
+ + "MAX(SUM(\"employee_id\")) OVER (PARTITION BY \"birth_date\" ORDER BY \"employee_id\" "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$3\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "GROUP BY \"employee_id\", \"hire_date\", \"birth_date\"";
+
+ String query6 = "SELECT lag(\"employee_id\",1,'NA') over "
+ + "(partition by \"hire_date\" order by \"employee_id\"), \"hire_date\"\n"
+ + "FROM \"employee\"\n"
+ + "group by \"hire_date\", \"employee_id\"";
+ String expected6 = "SELECT LAG(\"employee_id\", 1, 'NA') "
+ + "OVER (PARTITION BY \"hire_date\" ORDER BY \"employee_id\"), \"hire_date\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "GROUP BY \"hire_date\", \"employee_id\"";
+
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ProjectToWindowRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ RuleSet rules = RuleSets.ofList(ProjectToWindowRule.PROJECT);
+
+ sql(query0).optimize(rules, hepPlanner).ok(expected0);
+ sql(query1).optimize(rules, hepPlanner).ok(expected1);
+ sql(query2).optimize(rules, hepPlanner).ok(expected2);
+ sql(query3).optimize(rules, hepPlanner).ok(expected3);
+ sql(query4).optimize(rules, hepPlanner).ok(expected4);
+ sql(query5).optimize(rules, hepPlanner).ok(expected5);
+ sql(query6).optimize(rules, hepPlanner).ok(expected6);
+ }
+
@Test public void testRankFunctionForPrintingOfFrameBoundary() {
String query = "SELECT rank() over (order by \"hire_date\") FROM \"employee\"";
String expected = "SELECT RANK() OVER (ORDER BY \"hire_date\")\n"