calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject calcite git commit: [CALCITE-1506] Push OVER Clause to underlying SQL via JDBC adapter (Christian Tzolov)
Date Mon, 28 Nov 2016 18:28:47 GMT
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 <christian.tzolov@gmail.com>
Authored: Wed Nov 23 16:31:17 2016 +0100
Committer: Julian Hyde <jhyde@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>. */


Mime
View raw message