[
https://issues.apache.org/jira/browse/CALCITE-4565?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Danny Chen updated CALCITE-4565:
--------------------------------
Description:
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.
> 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
> Affects Versions: next
> Reporter: Danny Chen
> Priority: Major
>
> 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.3.4#803005)
