[jira] [Commented] (CALCITE-3877) In RexWindow, make fields upperBound and lowerBound not-nullable

["Danny Chen (Jira)" <ji...@apache.org>]

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

Danny Chen commented on CALCITE-3877:
-------------------------------------

Hi Julian ~

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);
{code}

PostgreSQL 9.6 returns the result as:

{code:sql}
0
1
2
2
2
2
2
{code}

While the Oracle throws exception:
{noformat}ORA-30485: missing ORDER BY expression in the window specification{noformat}

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.


> In RexWindow, make fields upperBound and lowerBound not-nullable
> ----------------------------------------------------------------
>
>                 Key: CALCITE-3877
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3877
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Steven Talbot
>            Assignee: Julian Hyde
>            Priority: Major
>             Fix For: 1.23.0
>
>
> Null lowerBound/upperBond in RexWindow causes NPE in RexShuttle. Here's a test that can be dropped into RexShuttleTest.java: 
> {code:java}
> @Test public void handlesNullBoundsInRexWindow() {
>   final RelBuilder builder = RelBuilder.create(RelBuilderTest.config().build());
>   final RexNode over = builder.getRexBuilder().makeOver(
>       builder.getTypeFactory().createSqlType(SqlTypeName.BIGINT),
>       SqlStdOperatorTable.ROW_NUMBER,
>       ImmutableList.of(),
>       ImmutableList.of(),
>       ImmutableList.of(),
>       null,
>       null,
>       true,
>       true,
>       true,
>       true,
>        true
>   );
>   assertThat(over.accept(new RexShuttle()), is(over));
> }
> {code}
> That will raise an NPE. Fix is to handle NULLs when doing the child accept on the RexWindowBound[s] in RexShuttle.visitWindow.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)