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 <[email protected]> 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"
