Repository: calcite
Updated Branches:
  refs/heads/master c2d388578 -> 54ed57f82


[CALCITE-1931] Change the return type of RANK and other aggregate functions

Various aggregate functions that used to return INTEGER now return other
types: RANK, DENSE_RANK, and NTILE now return BIGINT;
CUME_DIST and PERCENT_RANK now return DOUBLE.

Close apache/calcite#511


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/54ed57f8
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/54ed57f8
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/54ed57f8

Branch: refs/heads/master
Commit: 54ed57f8244384cf56323450feb5e6cfd6bc6245
Parents: c2d3885
Author: Minji Kim <mi...@dremio.com>
Authored: Tue Aug 30 11:41:00 2016 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 9 14:26:02 2017 -0700

----------------------------------------------------------------------
 .../calcite/rel/type/RelDataTypeSystem.java     | 19 ++++++++++----
 .../calcite/rel/type/RelDataTypeSystemImpl.java | 10 ++++++++
 .../org/apache/calcite/sql/SqlRankFunction.java |  9 ++++++-
 .../calcite/sql/fun/SqlNtileAggFunction.java    |  2 +-
 .../calcite/sql/fun/SqlStdOperatorTable.java    | 12 +++++----
 .../apache/calcite/sql/type/ReturnTypes.java    | 26 ++++++++++++++++++++
 .../java/org/apache/calcite/test/JdbcTest.java  | 16 ++++++------
 .../calcite/test/SqlToRelConverterTest.xml      | 14 ++++++-----
 site/_docs/history.md                           |  9 +++++++
 9 files changed, 91 insertions(+), 26 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystem.java
----------------------------------------------------------------------
diff --git 
a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystem.java 
b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystem.java
index ff9d81f..858567c 100644
--- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystem.java
+++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystem.java
@@ -59,21 +59,30 @@ public interface RelDataTypeSystem {
   /** Returns the LITERAL string for the type, either PREFIX/SUFFIX. */
   String getLiteral(SqlTypeName typeName, boolean isPrefix);
 
-  /** Returns if the type is case sensitive true or not (false) */
+  /** Returns whether the type is case sensitive. */
   boolean isCaseSensitive(SqlTypeName typeName);
 
-  /** Returns if the type can be auto increment true or not (false) */
+  /** Returns whether the type can be auto increment. */
   boolean isAutoincrement(SqlTypeName typeName);
 
-  /** Returns the numeric type Radix, 2 or 10. 0 represent not applicable*/
+  /** Returns the numeric type radix, typically 2 or 10.
+   * 0 means "not applicable". */
   int getNumTypeRadix(SqlTypeName typeName);
 
   /**
-   * Returns the return type of SUM aggregate function inferred from its
-   * argument type.
+   * Returns the return type of a call to the {@code SUM} aggregate function
+   * inferred from its argument type.
    */
   RelDataType deriveSumType(RelDataTypeFactory typeFactory, RelDataType 
argumentType);
 
+  /** Returns the return type of the {@code CUME_DIST} and {@code PERCENT_RANK}
+   * aggregate functions. */
+  RelDataType deriveFractionalRankType(RelDataTypeFactory typeFactory);
+
+  /** Returns the return type of the {@code NTILE}, {@code RANK},
+   * {@code DENSE_RANK}, and {@code ROW_NUMBER} aggregate functions. */
+  RelDataType deriveRankType(RelDataTypeFactory typeFactory);
+
   /** Whether two record types are considered distinct if their field names
    * are the same but in different cases. */
   boolean isSchemaCaseSensitive();

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
----------------------------------------------------------------------
diff --git 
a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java 
b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
index 65c32ab..1a1f130 100644
--- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
+++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
@@ -212,6 +212,16 @@ public abstract class RelDataTypeSystemImpl implements 
RelDataTypeSystem {
     return argumentType;
   }
 
+  @Override public RelDataType deriveFractionalRankType(RelDataTypeFactory 
typeFactory) {
+    return typeFactory.createTypeWithNullability(
+        typeFactory.createSqlType(SqlTypeName.DOUBLE), false);
+  }
+
+  @Override public RelDataType deriveRankType(RelDataTypeFactory typeFactory) {
+    return typeFactory.createTypeWithNullability(
+        typeFactory.createSqlType(SqlTypeName.BIGINT), false);
+  }
+
   public boolean isSchemaCaseSensitive() {
     return true;
   }

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java 
b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
index 8bf4721..f330463 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
@@ -18,6 +18,7 @@ package org.apache.calcite.sql;
 
 import org.apache.calcite.sql.type.OperandTypes;
 import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
 
 /**
  * Operator which aggregates sets of values into a result.
@@ -25,8 +26,14 @@ import org.apache.calcite.sql.type.ReturnTypes;
 public class SqlRankFunction extends SqlAggFunction {
   //~ Constructors -----------------------------------------------------------
 
+  @Deprecated
   public SqlRankFunction(boolean requiresOrder, SqlKind kind) {
-    super(kind.name(), null, kind, ReturnTypes.INTEGER, null,
+    this(kind, ReturnTypes.INTEGER, requiresOrder);
+  }
+
+  public SqlRankFunction(SqlKind kind, SqlReturnTypeInference returnTypes,
+      boolean requiresOrder) {
+    super(kind.name(), null, kind, returnTypes, null,
         OperandTypes.NILADIC, SqlFunctionCategory.NUMERIC, requiresOrder,
         true);
   }

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java
----------------------------------------------------------------------
diff --git 
a/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java 
b/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java
index 55eb8cb..18677f5 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java
@@ -32,7 +32,7 @@ public class SqlNtileAggFunction extends SqlAggFunction {
         "NTILE",
         null,
         SqlKind.NTILE,
-        ReturnTypes.INTEGER,
+        ReturnTypes.RANK,
         null,
         OperandTypes.POSITIVE_INTEGER_LITERAL,
         SqlFunctionCategory.NUMERIC,

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git 
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java 
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 2ba8b25..8300add 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -983,31 +983,33 @@ public class SqlStdOperatorTable extends 
ReflectiveSqlOperatorTable {
    * <code>CUME_DIST</code> window function.
    */
   public static final SqlRankFunction CUME_DIST =
-      new SqlRankFunction(true, SqlKind.CUME_DIST);
+      new SqlRankFunction(SqlKind.CUME_DIST, ReturnTypes.FRACTIONAL_RANK, 
true);
 
   /**
    * <code>DENSE_RANK</code> window function.
    */
   public static final SqlRankFunction DENSE_RANK =
-      new SqlRankFunction(true, SqlKind.DENSE_RANK);
+      new SqlRankFunction(SqlKind.DENSE_RANK, ReturnTypes.RANK, true);
 
   /**
    * <code>PERCENT_RANK</code> window function.
    */
   public static final SqlRankFunction PERCENT_RANK =
-      new SqlRankFunction(true, SqlKind.PERCENT_RANK);
+      new SqlRankFunction(SqlKind.PERCENT_RANK,
+          ReturnTypes.FRACTIONAL_RANK,
+          true);
 
   /**
    * <code>RANK</code> window function.
    */
   public static final SqlRankFunction RANK =
-      new SqlRankFunction(true, SqlKind.RANK);
+      new SqlRankFunction(SqlKind.RANK, ReturnTypes.RANK, true);
 
   /**
    * <code>ROW_NUMBER</code> window function.
    */
   public static final SqlRankFunction ROW_NUMBER =
-      new SqlRankFunction(false, SqlKind.ROW_NUMBER);
+      new SqlRankFunction(SqlKind.ROW_NUMBER, ReturnTypes.RANK, false);
 
   //-------------------------------------------------------------
   //                   SPECIAL OPERATORS

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java 
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index b8ec91e..715f59c 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -785,6 +785,32 @@ public abstract class ReturnTypes {
               .deriveSumType(typeFactory, opBinding.getOperandType(0));
         }
       };
+
+  /**
+   * Type-inference strategy for the {@code CUME_DIST} and {@code PERCENT_RANK}
+   * aggregate functions.
+   */
+  public static final SqlReturnTypeInference FRACTIONAL_RANK =
+      new SqlReturnTypeInference() {
+        @Override public RelDataType
+        inferReturnType(SqlOperatorBinding opBinding) {
+          final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
+          return 
typeFactory.getTypeSystem().deriveFractionalRankType(typeFactory);
+        }
+      };
+
+  /**
+   * Type-inference strategy for the {@code NTILE}, {@code RANK},
+   * {@code DENSE_RANK}, and {@code ROW_NUMBER} aggregate functions.
+   */
+  public static final SqlReturnTypeInference RANK =
+      new SqlReturnTypeInference() {
+        @Override public RelDataType
+        inferReturnType(SqlOperatorBinding opBinding) {
+          final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
+          return typeFactory.getTypeSystem().deriveRankType(typeFactory);
+        }
+      };
 }
 
 // End ReturnTypes.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java 
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index d848592..03120a3 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -3526,7 +3526,7 @@ public class JdbcTest {
             + " rank() over (partition by \"deptno\" order by \"empid\" desc) 
as rd\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, empid INTEGER NOT NULL, commission 
INTEGER, RCNF INTEGER NOT NULL, RCNL INTEGER NOT NULL, R INTEGER NOT NULL, RD 
INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, empid INTEGER NOT NULL, commission 
INTEGER, RCNF BIGINT NOT NULL, RCNL BIGINT NOT NULL, R BIGINT NOT NULL, RD 
BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; empid=100; commission=1000; RCNF=2; RCNL=1; R=1; RD=3",
             "deptno=10; empid=110; commission=250; RCNF=3; RCNL=2; R=2; RD=2",
@@ -3541,7 +3541,7 @@ public class JdbcTest {
             + " rank() over (order by \"deptno\") as r\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, R INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, R BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; R=1",
             "deptno=10; R=1",
@@ -3556,7 +3556,7 @@ public class JdbcTest {
             + " rank() over (order by \"deptno\" desc) as r\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, R INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, R BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; R=2",
             "deptno=10; R=2",
@@ -3571,7 +3571,7 @@ public class JdbcTest {
             + " dense_rank() over (order by \"deptno\") as r\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, R INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, R BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; R=1",
             "deptno=10; R=1",
@@ -3586,7 +3586,7 @@ public class JdbcTest {
             + " dense_rank() over (order by \"deptno\" desc) as r\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, R INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, R BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; R=2",
             "deptno=10; R=2",
@@ -3850,7 +3850,7 @@ public class JdbcTest {
         .query("select rn, ntile(1) over (order by rn) l\n"
             + " from " + START_OF_GROUP_DATA)
         .typeIs(
-            "[RN INTEGER NOT NULL, L INTEGER NOT NULL]")
+            "[RN INTEGER NOT NULL, L BIGINT NOT NULL]")
         .returnsUnordered(
             "RN=1; L=1",
             "RN=2; L=1",
@@ -3870,7 +3870,7 @@ public class JdbcTest {
         .query("select rn, ntile(2) over (order by rn) l\n"
             + " from " + START_OF_GROUP_DATA)
         .typeIs(
-            "[RN INTEGER NOT NULL, L INTEGER NOT NULL]")
+            "[RN INTEGER NOT NULL, L BIGINT NOT NULL]")
         .returnsUnordered(
             "RN=1; L=1",
             "RN=2; L=1",
@@ -3989,7 +3989,7 @@ public class JdbcTest {
             + " row_number() over (partition by \"deptno\" order by \"empid\" 
desc) as rd\n"
             + "from \"hr\".\"emps\"")
         .typeIs(
-            "[deptno INTEGER NOT NULL, empid INTEGER NOT NULL, commission 
INTEGER, R INTEGER NOT NULL, RCNF INTEGER NOT NULL, RCNL INTEGER NOT NULL, R 
INTEGER NOT NULL, RD INTEGER NOT NULL]")
+            "[deptno INTEGER NOT NULL, empid INTEGER NOT NULL, commission 
INTEGER, R BIGINT NOT NULL, RCNF BIGINT NOT NULL, RCNL BIGINT NOT NULL, R 
BIGINT NOT NULL, RD BIGINT NOT NULL]")
         .returnsUnordered(
             "deptno=10; empid=100; commission=1000; R=1; RCNF=2; RCNL=1; R=1; 
RD=3",
             "deptno=10; empid=110; commission=250; R=3; RCNF=3; RCNL=2; R=2; 
RD=2",

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git 
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml 
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 0ce33b6..94824a9 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3404,12 +3404,14 @@ from (select min(deptno) as x,
         <Resource name="plan">
             <![CDATA[
 LogicalProject(X=[$0], Y=[$1], Z=[$2], EMPNO=[$3])
-  LogicalJoin(condition=[AND(=($0, $10), =($1, $3))], joinType=[inner])
-    LogicalProject(X=[$2], Y=[RANK() OVER (ORDER BY $1 RANGE BETWEEN UNBOUNDED 
PRECEDING AND CURRENT ROW)], Z=[MAX($1) OVER (PARTITION BY $0 RANGE BETWEEN 
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
-      LogicalAggregate(group=[{0, 1}], X=[MIN($0)])
-        LogicalProject(DEPTNO=[$7], EMPNO=[$0])
-          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+  LogicalProject(X=[$0], Y=[$1], Z=[$2], EMPNO=[$3], ENAME=[$4], JOB=[$5], 
MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO=[$10], SLACKER=[$11])
+    LogicalJoin(condition=[AND(=($0, $10), =($1, $12))], joinType=[inner])
+      LogicalProject(X=[$2], Y=[RANK() OVER (ORDER BY $1 RANGE BETWEEN 
UNBOUNDED PRECEDING AND CURRENT ROW)], Z=[MAX($1) OVER (PARTITION BY $0 RANGE 
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+        LogicalAggregate(group=[{0, 1}], X=[MIN($0)])
+          LogicalProject(DEPTNO=[$7], EMPNO=[$0])
+            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], 
EMPNO0=[CAST($0):BIGINT NOT NULL])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>

http://git-wip-us.apache.org/repos/asf/calcite/blob/54ed57f8/site/_docs/history.md
----------------------------------------------------------------------
diff --git a/site/_docs/history.md b/site/_docs/history.md
index 96d2210..a3f1919 100644
--- a/site/_docs/history.md
+++ b/site/_docs/history.md
@@ -38,6 +38,15 @@ Guava versions 14.0 to 21.0;
 Druid version 0.10.0;
 other software versions as specified in `pom.xml`.
 
+#### Bug-fixes, API changes and minor enhancements
+
+* [<a 
href="https://issues.apache.org/jira/browse/CALCITE-1931";>CALCITE-1931</a>]
+  Change the return type of RANK and other aggregate functions.
+  Various aggregate functions that used to return `INTEGER` now return other
+  types: `RANK`, `DENSE_RANK`, and `NTILE` now return `BIGINT`;
+  `CUME_DIST` and `PERCENT_RANK` now return `DOUBLE`.
+  (**This is a breaking change**.)
+
 ## <a 
href="https://github.com/apache/calcite/releases/tag/calcite-1.13.0";>1.13.0</a> 
/ 2017-06-20
 {: #v1-13-0}
 

Reply via email to