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 2016/11/28 18:28:47 UTC
calcite git commit: [CALCITE-1506] Push OVER Clause to underlying SQL
via JDBC adapter (Christian Tzolov)
Repository: calcite
Updated Branches:
refs/heads/master 808ca6219 -> e4fb4c4ed
[CALCITE-1506] Push OVER Clause to underlying SQL via JDBC adapter (Christian Tzolov)
Close apache/calcite#329
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/e4fb4c4e
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/e4fb4c4e
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/e4fb4c4e
Branch: refs/heads/master
Commit: e4fb4c4ed46febbbc2790f142e9e2b1d18b0ee18
Parents: 808ca62
Author: Christian Tzolov <ch...@gmail.com>
Authored: Wed Nov 23 16:31:17 2016 +0100
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Nov 28 08:45:43 2016 -0800
----------------------------------------------------------------------
.../calcite/rel/rel2sql/SqlImplementor.java | 95 ++++++++++++++
.../apache/calcite/test/JdbcAdapterTest.java | 130 +++++++++++++++++++
2 files changed, 225 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/e4fb4c4e/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
----------------------------------------------------------------------
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 5baf029..e42e84f 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
@@ -25,11 +25,15 @@ import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexFieldCollation;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexLocalRef;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexOver;
import org.apache.calcite.rex.RexProgram;
+import org.apache.calcite.rex.RexWindow;
+import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlBinaryOperator;
@@ -48,6 +52,7 @@ import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlSelectKeyword;
import org.apache.calcite.sql.SqlSetOperator;
+import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlSumEmptyIsZeroAggFunction;
@@ -568,6 +573,10 @@ public abstract class SqlImplementor {
new SqlNodeList(thenList, POS), elseNode);
default:
+ if (rex instanceof RexOver) {
+ return toSql(program, (RexOver) rex);
+ }
+
final RexCall call = (RexCall) stripCastFromString(rex);
final SqlOperator op = call.getOperator();
final List<SqlNode> nodeList = toSql(program, call.getOperands());
@@ -595,6 +604,92 @@ public abstract class SqlImplementor {
}
}
+ private SqlCall toSql(RexProgram program, RexOver rexOver) {
+ final RexWindow rexWindow = rexOver.getWindow();
+ final SqlNodeList partitionList = new SqlNodeList(
+ toSql(program, rexWindow.partitionKeys), POS);
+
+ ImmutableList.Builder<SqlNode> orderNodes = ImmutableList.builder();
+ if (rexWindow.orderKeys != null) {
+ for (RexFieldCollation rfc : rexWindow.orderKeys) {
+ orderNodes.add(toSql(program, rfc));
+ }
+ }
+ final SqlNodeList orderList =
+ new SqlNodeList(orderNodes.build(), POS);
+
+ final SqlLiteral isRows =
+ SqlLiteral.createBoolean(rexWindow.isRows(), POS);
+
+ final SqlNode lowerBound =
+ createSqlWindowBound(rexWindow.getLowerBound());
+ final SqlNode upperBound =
+ createSqlWindowBound(rexWindow.getUpperBound());
+
+ // null defaults to true.
+ // During parsing the allowPartial == false (e.g. disallow partial)
+ // is expand into CASE expression and is handled as a such.
+ // Not sure if we can collapse this CASE expression back into
+ // "disallow partial" and set the allowPartial = false.
+ final SqlLiteral allowPartial = null;
+
+ final SqlWindow sqlWindow = SqlWindow.create(null, null, partitionList,
+ orderList, isRows, lowerBound, upperBound, allowPartial, POS);
+
+ final List<SqlNode> nodeList = toSql(program, rexOver.getOperands());
+ final SqlCall aggFunctionCall =
+ rexOver.getAggOperator().createCall(POS, nodeList);
+
+ return SqlStdOperatorTable.OVER.createCall(POS, aggFunctionCall,
+ sqlWindow);
+ }
+
+ private SqlNode toSql(RexProgram program, RexFieldCollation rfc) {
+ SqlNode node = toSql(program, rfc.left);
+ switch (rfc.getDirection()) {
+ case DESCENDING:
+ case STRICTLY_DESCENDING:
+ node = SqlStdOperatorTable.DESC.createCall(POS, node);
+ }
+ if (rfc.getNullDirection()
+ != dialect.defaultNullDirection(rfc.getDirection())) {
+ switch (rfc.getNullDirection()) {
+ case FIRST:
+ node = SqlStdOperatorTable.NULLS_FIRST.createCall(POS, node);
+ break;
+ case LAST:
+ node = SqlStdOperatorTable.NULLS_LAST.createCall(POS, node);
+ break;
+ }
+ }
+ return node;
+ }
+
+ private SqlNode createSqlWindowBound(RexWindowBound rexWindowBound) {
+ if (rexWindowBound.isCurrentRow()) {
+ return SqlWindow.createCurrentRow(POS);
+ }
+ if (rexWindowBound.isPreceding()) {
+ if (rexWindowBound.isUnbounded()) {
+ return SqlWindow.createUnboundedPreceding(POS);
+ } else {
+ SqlNode literal = toSql(null, rexWindowBound.getOffset());
+ return SqlWindow.createPreceding(literal, POS);
+ }
+ }
+ if (rexWindowBound.isFollowing()) {
+ if (rexWindowBound.isUnbounded()) {
+ return SqlWindow.createUnboundedFollowing(POS);
+ } else {
+ SqlNode literal = toSql(null, rexWindowBound.getOffset());
+ return SqlWindow.createFollowing(literal, POS);
+ }
+ }
+
+ throw new AssertionError("Unsupported Window bound: "
+ + rexWindowBound);
+ }
+
private SqlNode createLeftCall(SqlOperator op, List<SqlNode> nodeList) {
if (nodeList.size() == 2) {
return op.createCall(new SqlNodeList(nodeList, POS));
http://git-wip-us.apache.org/repos/asf/calcite/blob/e4fb4c4e/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 4b65017..f45a9b5 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -379,6 +379,136 @@ public class JdbcAdapterTest {
}
/** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1506">[CALCITE-1506]
+ * Push OVER Clause to underlying SQL via JDBC adapter</a>.
+ *
+ * <p>Test runs only on Postgres; the default database, Hsqldb, does not
+ * support OVER. */
+ @Test public void testOverDefault() {
+ CalciteAssert
+ .model(JdbcTest.FOODMART_MODEL)
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.POSTGRESQL)
+ .query("select \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " last_value(\"time_id\") over ()"
+ + " as \"last_version\" from \"expense_fact\"")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], "
+ + "time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6],"
+ + " last_version=[LAST_VALUE($3) OVER (RANGE BETWEEN UNBOUNDED"
+ + " PRECEDING AND UNBOUNDED FOLLOWING)])\n"
+ + " JdbcTableScan(table=[[foodmart, expense_fact]])\n")
+ .runs()
+ .planHasSql("SELECT \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " LAST_VALUE(\"time_id\") OVER (RANGE BETWEEN UNBOUNDED"
+ + " PRECEDING AND UNBOUNDED FOLLOWING) AS \"last_version\"\n"
+ + "FROM \"foodmart\".\"expense_fact\"");
+ }
+
+ @Test public void testOverRowsBetweenBoundFollowingAndFollowing() {
+ CalciteAssert
+ .model(JdbcTest.FOODMART_MODEL)
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.POSTGRESQL)
+ .query("select \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " last_value(\"time_id\") over (partition by \"account_id\""
+ + " order by \"time_id\" rows between 1 following and 10 following)"
+ + " as \"last_version\" from \"expense_fact\"")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], "
+ + "time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6],"
+ + " last_version=[LAST_VALUE($3) OVER (PARTITION BY $1"
+ + " ORDER BY $3 ROWS BETWEEN 1 FOLLOWING AND 10 FOLLOWING)])\n"
+ + " JdbcTableScan(table=[[foodmart, expense_fact]])\n")
+ .runs()
+ .planHasSql("SELECT \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " LAST_VALUE(\"time_id\") OVER (PARTITION BY \"account_id\""
+ + " ORDER BY \"time_id\" ROWS BETWEEN 1 FOLLOWING"
+ + " AND 10 FOLLOWING) AS \"last_version\"\n"
+ + "FROM \"foodmart\".\"expense_fact\"");
+ }
+
+ @Test public void testOverRowsBetweenBoundPrecedingAndCurrent() {
+ CalciteAssert
+ .model(JdbcTest.FOODMART_MODEL)
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.POSTGRESQL)
+ .query("select \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " last_value(\"time_id\") over (partition by \"account_id\""
+ + " order by \"time_id\" rows between 3 preceding and current row)"
+ + " as \"last_version\" from \"expense_fact\"")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], "
+ + "time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6],"
+ + " last_version=[LAST_VALUE($3) OVER (PARTITION BY $1"
+ + " ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)])\n"
+ + " JdbcTableScan(table=[[foodmart, expense_fact]])\n")
+ .runs()
+ .planHasSql("SELECT \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " LAST_VALUE(\"time_id\") OVER (PARTITION BY \"account_id\""
+ + " ORDER BY \"time_id\" ROWS BETWEEN 3 PRECEDING"
+ + " AND CURRENT ROW) AS \"last_version\"\n"
+ + "FROM \"foodmart\".\"expense_fact\"");
+ }
+
+ @Test public void testOverDisallowPartial() {
+ CalciteAssert
+ .model(JdbcTest.FOODMART_MODEL)
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.POSTGRESQL)
+ .query("select \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " last_value(\"time_id\") over (partition by \"account_id\""
+ + " order by \"time_id\" rows 3 preceding disallow partial)"
+ + " as \"last_version\" from \"expense_fact\"")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2],"
+ + " time_id=[$3], category_id=[$4], currency_id=[$5],"
+ + " amount=[$6], last_version=[CASE(>=(COUNT() OVER"
+ + " (PARTITION BY $1 ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND"
+ + " CURRENT ROW), 2), LAST_VALUE($3) OVER (PARTITION BY $1"
+ + " ORDER BY $3 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),"
+ + " null)])\n JdbcTableScan(table=[[foodmart,"
+ + " expense_fact]])\n")
+ .runs()
+ .planHasSql("SELECT \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " CASE WHEN (COUNT(*) OVER (PARTITION BY \"account_id\""
+ + " ORDER BY \"time_id\" ROWS BETWEEN 3 PRECEDING"
+ + " AND CURRENT ROW)) >= 2 THEN LAST_VALUE(\"time_id\")"
+ + " OVER (PARTITION BY \"account_id\" ORDER BY \"time_id\""
+ + " ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)"
+ + " ELSE NULL END AS \"last_version\"\n"
+ + "FROM \"foodmart\".\"expense_fact\"");
+ }
+
+ @Test public void testLastValueOver() {
+ CalciteAssert
+ .model(JdbcTest.FOODMART_MODEL)
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.POSTGRESQL)
+ .query("select \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " last_value(\"time_id\") over (partition by \"account_id\""
+ + " order by \"time_id\") as \"last_version\""
+ + " from \"expense_fact\"")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2],"
+ + " time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6],"
+ + " last_version=[LAST_VALUE($3) OVER (PARTITION BY $1 ORDER BY $3"
+ + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])\n"
+ + " JdbcTableScan(table=[[foodmart, expense_fact]])\n")
+ .runs()
+ .planHasSql("SELECT \"store_id\", \"account_id\", \"exp_date\","
+ + " \"time_id\", \"category_id\", \"currency_id\", \"amount\","
+ + " LAST_VALUE(\"time_id\") OVER (PARTITION BY \"account_id\""
+ + " ORDER BY \"time_id\" RANGE BETWEEN UNBOUNDED PRECEDING AND"
+ + " CURRENT ROW) AS \"last_version\""
+ + "\nFROM \"foodmart\".\"expense_fact\"");
+ }
+
+ /** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-259">[CALCITE-259]
* Using sub-queries in CASE statement against JDBC tables generates invalid
* Oracle SQL</a>. */