db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5954) NPE in SELECT involving subselects and windows functions
Date Fri, 19 Oct 2012 23:40:13 GMT

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

Bryan Pendleton commented on DERBY-5954:
----------------------------------------

Thanks for working on this, Dag!

The patch looks clear and straightforward.

Just thinking abstractly, it seems like there are two different ways that the new
visitor code could be wrong:
1) It could fail to descend into a sub-query when in fact it should, or
2) It could descend into a sub-query that it shouldn't descend into.

Trying to think more concretely, I wonder about the literal use of SelectNode
as the argument to the Visitor instance.

What if, for example, there is a UNION in there; for example, what if we saw:

SELECT rn_t1, ( 
     SELECT rn_t2 FROM ( 
         SELECT row_number() over() as rn_t2 FROM derby5954
                      UNION
          SELECT somecolumn FROM sometable) 
         as T2 
         where T2.rn_t2 = T1.rn_t1) 
     as rn_outer 
     FROM (SELECT a as rn_t1 from derby5954) as T1") 

I'm not even sure that what I'm typing is syntactically valid; I'm just trying
to inspire some other test cases that might be revealing.

                
> 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

Mime
View raw message