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

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4565?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17320579#comment-17320579 ] 

Julian Hyde commented on CALCITE-4565:
--------------------------------------

I would focus on the experience of the SQL user. What query did they write? Do we consider it valid? If it is valid, what should the result be?

It seems that when a windowed aggregate has no {{ORDER BY}}, Oracle considers finite {{ROWS}} and {{RANGE}} bounds to be invalid, whereas PostgreSQL allows {{ROWS}} bounds and then sorts rows according to 'natural order'. Although relying on natural order in SQL is discouraged, there is precedent for it: all dialects of SQL allow {{LIMIT}} and/or {{OFFSET}} without {{ORDER BY}}; and all dialects of SQL allow {{ORDER BY}} on a non-unique key in both queries and windowed aggregates. In both of these cases, results are non-deterministic.

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