NullPointerException for 'NOT EXISTS' with nested subquery

• Key: DERBY-3321
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Thomas Nielsen
• Reporter: Jørgen Løland
• Component/s: SQL
• Affects Version/s: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.2.1, 10.4.1.3
• Fix Version/s: 10.3.3.0, 10.4.1.3

Description

Queries with 'not exists' followed by a nested subquery results in NPE:

-----------8<--------------
connect 'jdbc:derby:testdb;create=true';
create table a (aa int, bb int);
-- 0 rows inserted/updated/deleted

create table b (bb int);
-- 0 rows inserted/updated/deleted

insert into a values (1, 1), (1, 2), (2, 2);
-- 3 rows inserted/updated/deleted

insert into b values (1);
-- 1 row inserted/updated/deleted

select * from a
   where not exists
   (select bb from b where a.bb=b.bb);

-- AA |BB
-- ----------------------
-- 1 |2
-- 2 |2

select bb from (select bb from b) p;
-- BB
-- -----------
-- 1

select * from a
   where not exists
   (select bb from (select bb from b) p where a.bb=p.bb);
-- ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
------------>8----------------

Comments

[Jørgen Løland]

Attached derby.log.

This issue also applies to 'exists', i.e. the same query without 'not'

[Dyre Tjeldvoll]

HI Jørgen,

I don't think this is the exact same problem as DERBY-3310 since that happens during execution, and your problem appears to happen during compilation (according to the call stack).
But it seems like both problems could be related to eager optimization of (NOT) EXISTS. In your case I think the problem is that in
SelectNode.bindTargetExpressions:584 bindTargetListOnly is temporarily set to true. This will inhibit calling fromList.bindExpressions( fromListParam ); in SelectNode.bindExpression(). This is a problem since
it is this call that will initialize FromSubquery.resultColumns (it is initially, null). Since the initialization is skipped you're all set for the NPE further down when you reference resultColumns in FromSubquery.getMatchingColumn().

If I keep bindTargetListOnly false I get

select * from a
   where not exists
   (select bb from (select bb from b) p where a.bb=p.bb);
AA |BB
-----------------------
1 |2
2 |2

2 rows selected

bindTargetListOnly is used elsewhere as well, so this is probably not a viable fix.

[Kathey Marsden]

Verified this back to 10.0.2.1 so it's not a regression. Below is the full stack trace.
   (select bb from (select bb from b) p where a.bb=p.bb);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88)
        at org.apache.derby.impl.jdbc.Util.javaException(Util.java:245)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1946)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:613)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:556)
        at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
        at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
        at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:353)
        at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
        at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
        at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
        at org.apache.derby.tools.ij.main(ij.java:59)
Caused by: java.lang.NullPointerException
        at org.apache.derby.impl.sql.compile.FromSubquery.getMatchingColumn(FromSubquery.java:289)
        at org.apache.derby.impl.sql.compile.FromList.bindColumnReference(FromList.java:602)
        at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(ColumnReference.java:349)
        at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:552)
        at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:680)
        at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:445)
        at org.apache.derby.impl.sql.compile.SelectNode.bindTargetExpressions(SelectNode.java:597)
        at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(SubqueryNode.java:483)
        at org.apache.derby.impl.sql.compile.UnaryOperatorNode.bindOperand(UnaryOperatorNode.java:333)
        at org.apache.derby.impl.sql.compile.UnaryLogicalOperatorNode.bindExpression(UnaryLogicalOperatorNode.java:74)
        at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:468)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:236)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:314)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConne
ctionContext.java:768)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:607)
        ... 9 more

[Thomas Nielsen]

Attaching patch proposal and test extention for this issue.

Like Dyres initial analysis shows, we fail to bind the columns of the innermost subquery and end up with an NPE.
The patch solves this by checking the fromList for a FromSubquery using a CollectNodesVistior and basing the value of bindTargetListOnly on the contents of the visior.

suites.All and derbyAll are running

[Thomas Nielsen]

Got some unrelated OutOfMemoryExceptions off the head of trunk when running suites.All
Reran only the lang._Suite, and it ran without problems.

[Thomas Nielsen]

derbyAll ran cleanly, except for the non-empty transaction table others also have reported seeing every now and then.

[Dyre Tjeldvoll]

Patch looks good to me. I would like to commit it shortly, unless there are further comments.

[Dyre Tjeldvoll]

Committed revision 634316.

[Thomas Nielsen]

Thanks Dyre!

Marking as resolved.

[Jørgen Løland]

Thank you for the patch, Thomas