[
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.
was:
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)
