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 <[email protected]> Authored: Tue Aug 30 11:41:00 2016 -0700 Committer: Julian Hyde <[email protected]> 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}
