[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16117196#comment-16117196 ] MinJi Kim commented on CALCITE-1931: Thanks again for reviewing this! https://github.com/apache/calcite/pull/511 > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16117488#comment-16117488 ] Julian Hyde commented on CALCITE-1931: -- What does the SQL standard say about these functions' return types? Are these new return types completely un-controversial? If not, should we let people specify a policy, as we did in CALCITE-845? The extra arg to {{SqlRankFunction}}'s constructor looks like a breaking change. Leave the current constructor, marked deprecated. > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16118693#comment-16118693 ] MinJi Kim commented on CALCITE-1931: I think CUME_DIST() and PERCENT_RANK() for example, should return floating point, since generally their return values are less than or equal to 1 (I looked at Oracle and MS SqlServer, and they both return fractional values and 1 for these functions). I changed the other functions (e.g. NTILE/RANK/DENSE_RANK) to return BIGINT rather than INT. For example, MS SqlServer returns BIGINT for NTILE/RANK/DENSE_RANK functions. I didn't think they were controversial since I don't think CUME_DIST() should return integers, otherwise all the values would be zero. But perhaps somebody is using that behavior somewhere. I can definitely add a way for users to specify return values of RANK functions as in CALCITE-845. I would probably just allow two policies, one for CUME_DIST/PERCENT_RANK and another for NTILE/RANK/DENSE_RANK (with the default being the new behavior as in this patch). Do you think that makes sense? > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16119151#comment-16119151 ] Julian Hyde commented on CALCITE-1931: -- That makes sense. Thanks for doing the research. Let me know when you have a commit ready for review. > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16119154#comment-16119154 ] Julian Hyde commented on CALCITE-1931: -- One more thing. Can you add a paragraph to the release notes (HISTORY.md). Something like this: bq. Breaking change. 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. > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1931) rank/ntile/etc. functions should not always return INTEGER
[ https://issues.apache.org/jira/browse/CALCITE-1931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16120073#comment-16120073 ] MinJi Kim commented on CALCITE-1931: I have updated the pull request with some additional changes for you to review. Thanks! https://github.com/apache/calcite/pull/511 > rank/ntile/etc. functions should not always return INTEGER > -- > > Key: CALCITE-1931 > URL: https://issues.apache.org/jira/browse/CALCITE-1931 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > For example, rank() and percent_rank() both return INTEGER. For rank(), it > would be better to return BIGINT; for percent_rank(), we should actually > return double or float since it is a percent value. There are other rank > functions that should return floating number instead of integer. -- This message was sent by Atlassian JIRA (v6.4.14#64029)