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