[jira] [Comment Edited] (DERBY-5954) NPE in SELECT involving subselects and windows functions

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

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

Dag H. Wanvik edited comment on DERBY-5954 at 11/6/12 4:45 PM:
---------------------------------------------------------------

I'll try to explain why I believe the changed code is correct. Sorry for answering late, I have been out for a while.
What we are looking for here is any "<in-line window specification>" in the sense of SQL 2003 section 6.10 "Window Function" contained in a select list (syntax rule 3b). The meaning of the in-line window specification is described in Syntax Rule 7-9 of section 6.10.
Pertinent quote (simplified):

7) Let SL be the <select list> that simply contains[1] OF (the window function)
8d) Let SLNEW be the <select list> that is obtained from SL by replacing OF by:
     OFT OVER WSN (where WSN is an equivalent synthesized window on the table expression TE)
Rule 9 f) and g) describes how our in-lined window is transformed to be appended to any existing windows for the table expression.

Now, earlier, we found window functions *not* simply contained in the SL, i.e. inside a nested SELECT since the visitor went to deep. The in-lined window specification properly belonged at the level of that nested SELECT's table expression.

So, Bryan's Criterion 1) is satisfied, in that the visitor previously visited *all* nodes contained in the select list.
After the change we do not visit nodes *under* a simply contained (nested) SELECT statement. But that is OK, since any OF previously (erroneously) found there are not *simply contained in the outer SL.

So 1) holds I believe. Now, as for 2) could we (still) be descending into any subquery we should not (we obviously did before)?
That would need to be in a case where we had some legal syntax in the select list (SL) containing in-lined window specification *not* inside a SELECT node. Can that happen? Well, besides SELECT we can have a VALUES subquery in a SELECT list. But we forbid window functions inside a VALUES subquery, cf. this line in RowResultSetNode#bindExpressions:

SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");

So, since we now forbid the visitor to descend into nested SELECTs and any nested VALUES are innocuous, we can not find any "wrong" in-lined window function. So 2) also holds.


[1] A1 simply contains B1 if A1 contains B1 without an intervening instance of <A> or an intervening instance of
<B>.

Makes sense?

                
      was (Author: dagw):
    I'll try to explain why I believe the changed code is correct. Sorry for answering late, I have been out for a while.
What we are looking for here is any "<in-line window specification>" in the sense of SQL 2003 section 6.10 "Window Function" contained in a select list (syntax rule 3b). The meaning of the in-line window specification is described in Syntax Rule 7-9 of section 6.10.
Pertinent quote (simplified):

7) Let SL be the <select list> that simply contains[1] OF (the window function)
8d) Let SLNEW be the <select list> that is obtained from SL by replacing OF by:
     OFT OVER WSN (where WSN is an equivalent synthesized window on the table expression TE)
Rule 9 f) and g) describes how our inlined window is transformed to be appended to any existing windows for the table expression.

Now, earlier, we found window functions *not* simply contained in the SL, i.e. inside a nested SELECT since the visitor went to deep. The inlined window specification properly belonged at the level of that nested SELECT's table expression.
So, Bryans Criterion 1) is satisfied, in that the visitor previously visited *all* nodes contained in the select list.
After the change we do not visit nodes *under* a simply contained (nested) SELECT statement. But that is OK, since any OF previosuly (erroneously) found there are not *simply contained in the outer SL.

So 1) holds I believe. Now, as for 2) could we (still) be descending into any subquery we should not (we obviously did before)?
That would need to be in a case where we had some legal syntax in the select list (SL) containing in-lined window specification *not* sinde a SELECT node. Can that happen? Well, besides SELECT we can have a VALUES subquery in a SELECT list. But we forbid window functions inside a VALUES subquery, cf. this line in RowResultSetNode#bindExpressions:

SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");

So, since we now forbid the visitor to descend into nested SELECTs and any nested VALUES are innocuous, we can not find any "wrong" in-lined window function. So 2) also holds.


[1] A1 simply contains B1 if A1 contains B1 without an intervening instance of <A> or an intervening instance of
<B>.

Makes sense?

                  
> NPE in SELECT involving subselects and windows functions
> --------------------------------------------------------
>
>                 Key: DERBY-5954
>                 URL: https://issues.apache.org/jira/browse/DERBY-5954
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.9.1.0
>            Reporter: Rick Hillegas
>            Assignee: Dag H. Wanvik
>         Attachments: derby-5954.diff, derby-5954-with-test.diff, derby-5954-with-test.stat
>
>
> A user reports and I have verified an NPE on the following SELECT:
> connect 'jdbc:derby:memory:db;create=true';
> create table blah ( a int );
> insert into blah values (1), (2), (3), (4), (5), (6), (7);
> SELECT rn, (SELECT rn FROM (SELECT row_number() over() rn FROM blah ) as T2
> where T2.rn = T1.rn+1) rn2
> FROM (SELECT row_number() over() rn from blah) as T1;

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira