[
https://issues.apache.org/jira/browse/CALCITE-4565?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-4565.
----------------------------------
Fix Version/s: 1.38.0
Resolution: Duplicate
Resolving as a duplicate of CALCITE-5883. I have changed my mind, and now agree
that both are bugs. I will respond in the other case.
> UpperBound and lowerBound are silently re-written incorrectly during sql to
> rel conversion
> ------------------------------------------------------------------------------------------
>
> Key: CALCITE-4565
> URL: https://issues.apache.org/jira/browse/CALCITE-4565
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Danny Chen
> Priority: Major
> Fix For: 1.38.0
>
>
> In SqlToRelConverter.convertover there is a code snippet:
> {code:java}
> } else if (orderList.size() == 0) {
> // Without ORDER BY, there must be no bracketing.
> sqlLowerBound = SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO);
> sqlUpperBound = SqlWindow.createUnboundedFollowing(SqlParserPos.ZERO);
> }
> {code}
> The SqlToRelConverter reset the lower/upper bounds silently when there is no
> ORDER BY.
> While i used the SQL below to check the behavior:
> {code:sql}
> create table t(
> a int,
> b int
> );
> insert into t values(1, 2);
> insert into t values(3, 4);
> insert into t values(5, 6);
> insert into t values(7, 8);
> insert into t values(9, 10);
> insert into t values(11, 12);
> insert into t values(13, 14);
> SELECT count(1) over (rows between 2 preceding and 1 preceding) from T;
> -- PostgreSQL 9.6 returns the result as:
> -- 0
> -- 1
> -- 2
> -- 2
> -- 2
> -- 2
> -- 2
> -- While the Oracle throws exception:
> -- ORA-30485: missing ORDER BY expression in the window specification
> {code}
> So what is the correct behavior here ? Overall rewriting the bounds seem
> different with all the other sql engines and it returns the wrong result
> always.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
