NullPointerException in UPDATE with COALESCE and subquery

• Key: DERBY-1574
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Dag H. Wanvik
• Reporter: Christian d'Heureuse
• Component/s: SQL
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.1.3.2, 10.1.4.0, 10.2.1.6
• Fix Version/s: 10.2.1.6

Description

The following statements generate a NullPointerException:

CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);

UPDATE t1
   SET i = COALESCE(
      (SELECT i FROM t2 WHERE t2.i=t1.i),
      0);

Any further SQL statements generate an internal error in RawStore, e.g.:
SELECT * FROM t1;

Comments

[Mike Matrigali]

If at all possible always include the full stack trace from derby.log when reporting a bug. Here is the
stack from the original problem against the trunk showing that the issue is in the optimizer:

2006-07-25 20:19:59.104 GMT Thread[main,5,main] (XID = 131), (SESSIONID = 0), (D
ATABASE = wombat), (DRDAID = null), Failed Statement is: UPDATE t1^M
   SET i = COALESCE(^M
      (SELECT i FROM t2 WHERE t2.i=t1.i),^M
      0)^M
java.lang.NullPointerException^M
    at org.apache.derby.iapi.util.JBitSet.or(JBitSet.java:241)^M
    at org.apache.derby.impl.sql.compile.OptimizerImpl.<init>(OptimizerImpl.java
:254)^M
    at org.apache.derby.impl.sql.compile.Level2OptimizerImpl.<init>(Level2Optimi
zerImpl.java:76)^M
    at org.apache.derby.impl.sql.compile.Level2OptimizerFactoryImpl.getOptimizer
Impl(Level2OptimizerFactoryImpl.java:98)^M
    at org.apache.derby.impl.sql.compile.OptimizerFactoryImpl.getOptimizer(Optim
izerFactoryImpl.java:159)^M
    at org.apache.derby.impl.sql.compile.ResultSetNode.getOptimizer(ResultSetNod
e.java:1635)^M
    at org.apache.derby.impl.sql.compile.SelectNode.optimize(SelectNode.java:163
9)^M
    at org.apache.derby.impl.sql.compile.SubqueryNode.optimize(SubqueryNode.java
:1683)^M
    at org.apache.derby.impl.sql.compile.SubqueryList.optimize(SubqueryList.java
:121)^M
    at org.apache.derby.impl.sql.compile.SelectNode.optimize(SelectNode.java:166
4)^M
    at org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(DMLStatementN
ode.java:328)^M
    at org.apache.derby.impl.sql.compile.DMLModStatementNode.optimize(DMLModStat
ementNode.java:1352)^M
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.ja
va:395)^M
    at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:
118)^M
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareIn
ternalStatement(GenericLanguageConnectionContext.java:713)^M
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567
)^M
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516
)^M
    at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)^M
    at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)^M
    at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)^M
    at org.apache.derby.impl.tools.ij.Main.go(Main.java:207)^M
    at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173)^M
    at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)^M
    at org.apache.derby.tools.ij.main(ij.java:60)^M
Cleanup action completed^M

Subsequent errors in raw store indicates there is also an error handling problem in the optimizer code
which leaves the execution context unusable for subsequent queries. The error is:

ERROR 40XT0: An internal error was identified by RawStore module.^M
    at org.apache.derby.iapi.error.StandardException.newException(StandardExcept
ion.java:294)^M
    at org.apache.derby.impl.store.raw.xact.Xact.setActiveState(Xact.java:1772)^
M
    at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Xact.java:1271)^M
    at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(Ope
nConglomerate.java:865)^M
    at org.apache.derby.impl.store.access.heap.Heap.open(Heap.java:614)^M
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTra
nsaction.java:478)^M
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTra
nsaction.java:1315)^M
    at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaInde
x(DataDictionaryImpl.java:7339)^M
    at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.locateSchemaRow(Data
DictionaryImpl.java:1532)^M
    at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(
DataDictionaryImpl.java:1442)^M
    at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(Query
TreeNode.java:1504)^M
    at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(Query
TreeNode.java:1456)^M
    at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(FromB
aseTable.java:2379)^M
    at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(FromBase
Table.java:2107)^M
    at org.apache.derby.impl.sql.compile.FromList.bindTables(FromList.java:300)^
M
    at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(SelectNode.
java:472)^M
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(DMLStatemen
tNode.java:220)^M
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.
java:158)^M
    at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:252)^M
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.ja
va:344)^M
    at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:
118)^M
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareIn
ternalStatement(GenericLanguageConnectionContext.java:713)^M
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567
)^M
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516
)^M
    at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)^M
    at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)^M
    at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:312)^M
    at org.apache.derby.impl.tools.ij.Main.go(Main.java:207)^M
    at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173)^M
    at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)^M
    at org.apache.derby.tools.ij.main(ij.java:60)^M
Cleanup action completed^M

[A B]

> Here is the stack from the original problem against the trunk showing that the issue is in the optimizer:

For the record, I ran the simple repro against an early version of 10.1(10.1.2.3) and also against 10.0, and the NPE reproduces for both of those codelines, as well. So in case anyone is wondering (I know I was), this is *not* a regression. For what that's worth...

[Mike Matrigali]

AS posted to list:
A B commented on DERBY-1574:
----------------------------
For the record, I ran the simple repro against an early version of 10.1(10.1.2.3) and also against 10.0, and the NPE reproduces for both of those codelines, as well. So in case anyone is wondering (I know I was), this is *not* a regression. For what that's worth...

[A B]

Adding additional versions to the "Affects" list, per my previous comment. Since I was able to reproduce the problem in my 10.0, 10.1, and 10.2 codelines I just set everything from 10.0 and forward as affected. If that's not the correct thing to do, please advise and/or update as appropriate. Thanks.

[Dag H. Wanvik]

I looked at it out of curiosity (I am not very familiar with this part
of the code); it seems the problem is related to the fact that the
preprocess phase has not been run prior to the optimize phase for the
subquery, leading to the referencedTableMap being empty (immediate
cause for NPE).

I made this experiment patch which seems to work for the case in the
issue. It should not be committed until someone more knowledgeable of
this part of the code has looked at it (it may be altogether the wrong
solution for all I know). Also I had to guess a bit at where the call to
preprocess should be placed as well as the arguments, so caveats apply.

I ran derbylang, but had a small diff in the execution plan (enclosed).
It may not be a problem, not sure.

[Manish Khettry]

To me it seems CoalesceFunctionNode should provide a preprocess method but I'm not an authority either! While working on 883, I noticed that CoalesceFunctionNode didn't implement the accept method which caused a NPE with group by's.

[Dag H. Wanvik]

Thanks, Manish!
So I tried to put the preprocess forwarding into CoalesceFunctionNode,
and that works too. It also looks more in line with the
present design, so I have proceeded with that solution.

This patch
- adds a preprocess method to CoalesceFunctionNode to override the one
  in ValueNode, thus making sure the arguments get handled.
- adds a printSubNodes method to CoalesceFunctionNode (was missing
  too, I discovered, when I was trying to look at the parse tree after
  binding).
- adds a new test case to coalesceTests.java and an updated master

I have run derbyall without errors and also run the modified test to verify that
it exposes the problem in absence of the fix. The patch is ready for review.

[Yip Ng]

I have reviewed the latest patch and it looks good. I confirmed that without this patch, it will throw NPE from the above case. After applying the patch, The arguments in the coalesce now gets handled properly and returns the expected result:

ij> update t1 set i = coalesce((select i from t2 where t2.i=t1.i), 0);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.

I think this patch is ready to be committed. +1

[Knut Anders Hatlen]

Thanks Dag for fixing the bug, and Yip for reviewing the patch. Committed revision 431593.