You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2024/08/20 19:26:00 UTC

[jira] [Resolved] (CALCITE-4565) UpperBound and lowerBound are silently re-written incorrectly during sql to rel conversion

     [ 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)