db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5954) NPE in SELECT involving subselects and windows functions
Date Tue, 06 Nov 2012 16:44:12 GMT

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

Dag H. Wanvik commented on DERBY-5954:

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

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:,,,,,
>            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

View raw message