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] [Comment Edited] (DERBY-5954) NPE in SELECT involving subselects and windows functions
Date Fri, 19 Oct 2012 15:24:11 GMT

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

Dag H. Wanvik edited comment on DERBY-5954 at 10/19/12 3:23 PM:
----------------------------------------------------------------

This query with only one row_number causes an assert with the debug build:

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


Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber
expected to be >= 0 for virtual column RN_T2
	at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:120)
	at org.apache.derby.impl.sql.compile.VirtualColumnNode.generateExpression(VirtualColumnNode.java:231)
	at org.apache.derby.impl.sql.compile.ResultColumn.generateExpression(ResultColumn.java:1059)
	at org.apache.derby.impl.sql.compile.ResultColumnList.generateCore(ResultColumnList.java:1426)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1557)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1337)
	at org.apache.derby.impl.sql.compile.WindowResultSetNode.generate(WindowResultSetNode.java:409)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1482)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1337)
	at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
	at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:641)

Interestingly, a similar query without row_number, gives a wrong result; i think:

SELECT a_t1, ( 
     SELECT a_t2 FROM ( 
         SELECT a as a_t2 FROM derby5954)  
         as T2
         where T2.a_t2 = T1.a_t1 + 1  )  
     as a_outer 
     FROM (SELECT a as a_t1 from derby5954) as T1

gives:

1 2
2 3
3 4
4 5
5 6
6 7
7 0

In the last row, for the value of a=7, I think the scalar subquery in the select list should
give "null", not 0.

[Update: The last above is misleading pilot error, the 0 comes from ResultSet#getInt, which
will return 0 if the result column is NULL. So no error there.]

                
      was (Author: dagw):
    This query with only one row_number causes an assert with the debug build:

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


Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber
expected to be >= 0 for virtual column RN_T2
	at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:120)
	at org.apache.derby.impl.sql.compile.VirtualColumnNode.generateExpression(VirtualColumnNode.java:231)
	at org.apache.derby.impl.sql.compile.ResultColumn.generateExpression(ResultColumn.java:1059)
	at org.apache.derby.impl.sql.compile.ResultColumnList.generateCore(ResultColumnList.java:1426)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1557)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1337)
	at org.apache.derby.impl.sql.compile.WindowResultSetNode.generate(WindowResultSetNode.java:409)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1482)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1337)
	at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
	at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:641)

Interestingly, a similar query without row_number, gives a wrong result; i think:

SELECT a_t1, ( 
     SELECT a_t2 FROM ( 
         SELECT a as a_t2 FROM derby5954)  
         as T2
         where T2.a_t2 = T1.a_t1 + 1  )  
     as a_outer 
     FROM (SELECT a as a_t1 from derby5954) as T1

gives:

1 2
2 3
3 4
4 5
5 6
6 7
7 0

In the last row, for the value of a=7, I think the scalar subquery in the select list should
give "null", not 0.


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