You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by drv <dr...@uk.ibm.com> on 2012/10/17 15:30:20 UTC

Nested SELECT in selected columns list causes NullPointerException if it references ROW_NUMBER() OVER() function

==================================================
These queries fail:
==================================================

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

=> The exception 'java.lang.NullPointerException' was thrown while
evaluating an expression.


OR using a values() expression instead of table 'blah':

SELECT rn, (SELECT rn FROM (SELECT row_number() over() rn FROM (values(1),
(2), (3)) V ) as T2 where T2.rn = T1.rn+1) rn2
FROM (SELECT row_number() over() rn from (values(1), (2), (3)) V) as T1

=> DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
java.lang.NullPointerExceptionXJ001.U


==================================================
Exception logged in derby.log:
==================================================

Wed Oct 17 14:21:30 BST 2012 Thread[DRDAConnThread_13,5,derby.daemons] (XID
= 9070), (SESSIONID = 11), (DATABASE = gaiandb), (DRDAID =
????????.????-1011338240841373641{10}), Cleanup action starting
Wed Oct 17 14:21:30 BST 2012 Thread[DRDAConnThread_13,5,derby.daemons] (XID
= 9070), (SESSIONID = 11), (DATABASE = gaiandb), (DRDAID =
????????.????-1011338240841373641{10}), Failed Statement is: SELECT rn,
(SELECT rn FROM (SELECT row_number() over() rn FROM (values(1), (2), (3)) V
) as T2 where T2.rn = T1.rn+1) rn2
FROM (SELECT row_number() over() rn from (values(1), (2), (3)) V) as T1
java.lang.NullPointerException
	at org.apache.derby.impl.sql.compile.ResultColumnList.updateArrays(Unknown
Source)
	at
org.apache.derby.impl.sql.compile.ResultColumnList.mapSourceColumns(Unknown
Source)
	at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown
Source)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown
Source)
	at org.apache.derby.impl.sql.compile.WindowResultSetNode.generate(Unknown
Source)
	at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown
Source)
	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown
Source)
	at
org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(Unknown
Source)
	at org.apache.derby.impl.sql.compile.CursorNode.generate(Unknown Source)
	at org.apache.derby.impl.sql.compile.StatementNode.generate(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
	at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
	at org.apache.derby.impl.drda.DRDAStatement.prepare(Unknown Source)
	at org.apache.derby.impl.drda.DRDAStatement.explicitPrepare(Unknown Source)
	at org.apache.derby.impl.drda.DRDAConnThread.parsePRPSQLSTT(Unknown Source)
	at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
Source)
	at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
Cleanup action completed
-- 
View this message in context: http://old.nabble.com/Nested-SELECT-in-selected-columns-list-causes-NullPointerException-if-it-references-ROW_NUMBER%28%29-OVER%28%29-function-tp34567925p34567925.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.


Re: Nested SELECT in selected columns list causes NullPointerException if it references ROW_NUMBER() OVER() function

Posted by Rick Hillegas <ri...@oracle.com>.
On 10/17/12 6:30 AM, drv wrote:
> ==================================================
> These queries fail:
> ==================================================
>
> 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
>
> =>  The exception 'java.lang.NullPointerException' was thrown while
> evaluating an expression.
>
>
> OR using a values() expression instead of table 'blah':
>
> SELECT rn, (SELECT rn FROM (SELECT row_number() over() rn FROM (values(1),
> (2), (3)) V ) as T2 where T2.rn = T1.rn+1) rn2
> FROM (SELECT row_number() over() rn from (values(1), (2), (3)) V) as T1
>
> =>  DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
> java.lang.NullPointerExceptionXJ001.U
>
>
> ==================================================
> Exception logged in derby.log:
> ==================================================
>
> Wed Oct 17 14:21:30 BST 2012 Thread[DRDAConnThread_13,5,derby.daemons] (XID
> = 9070), (SESSIONID = 11), (DATABASE = gaiandb), (DRDAID =
> ????????.????-1011338240841373641{10}), Cleanup action starting
> Wed Oct 17 14:21:30 BST 2012 Thread[DRDAConnThread_13,5,derby.daemons] (XID
> = 9070), (SESSIONID = 11), (DATABASE = gaiandb), (DRDAID =
> ????????.????-1011338240841373641{10}), Failed Statement is: SELECT rn,
> (SELECT rn FROM (SELECT row_number() over() rn FROM (values(1), (2), (3)) V
> ) as T2 where T2.rn = T1.rn+1) rn2
> FROM (SELECT row_number() over() rn from (values(1), (2), (3)) V) as T1
> java.lang.NullPointerException
> 	at org.apache.derby.impl.sql.compile.ResultColumnList.updateArrays(Unknown
> Source)
> 	at
> org.apache.derby.impl.sql.compile.ResultColumnList.mapSourceColumns(Unknown
> Source)
> 	at
> org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown
> Source)
> 	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown
> Source)
> 	at org.apache.derby.impl.sql.compile.WindowResultSetNode.generate(Unknown
> Source)
> 	at
> org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown
> Source)
> 	at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown
> Source)
> 	at
> org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(Unknown
> Source)
> 	at org.apache.derby.impl.sql.compile.CursorNode.generate(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.StatementNode.generate(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> 	at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> Source)
> 	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> 	at org.apache.derby.impl.drda.DRDAStatement.prepare(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAStatement.explicitPrepare(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAConnThread.parsePRPSQLSTT(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
> Source)
> 	at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
> Cleanup action completed
Thanks for the compact repro. I have verified this behavior logged the 
bug as https://issues.apache.org/jira/browse/DERBY-5954.

Thanks,
-Rick

Re: Nested SELECT in selected columns list causes NullPointerException if it references ROW_NUMBER() OVER() function

Posted by Rick Hillegas <ri...@oracle.com>.
On 10/17/12 7:07 AM, drv wrote:
> NOTE:
>
> I have tested this with Derby 10.5, 10.8 and 10.9
>
> The queries *work with Derby 10.5* ; but fail with 10.8 and 10.9
>
Thanks. I've added that comment to DERBY-5954. That issue is probably 
the best place to continue this discussion.

Thanks,
-Rick

Re: Nested SELECT in selected columns list causes NullPointerException if it references ROW_NUMBER() OVER() function

Posted by drv <dr...@uk.ibm.com>.
NOTE:

I have tested this with Derby 10.5, 10.8 and 10.9

The queries *work with Derby 10.5* ; but fail with 10.8 and 10.9


-- 
View this message in context: http://old.nabble.com/Nested-SELECT-in-selected-columns-list-causes-NullPointerException-if-it-references-ROW_NUMBER%28%29-OVER%28%29-function-tp34567925p34568113.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.