Hi there,
I've just tested this with 10.5.3.0 and it also fails. This time with a
different stack-trace (see below). This time the NPE is in
ColumnReference.remapColumnReferencesToExpressions(). I've also
attached the derby log for this crash.
Given this looks like a serious bug in the latest release, I've uploaded
a zipped copy of the database to a free file sharing site ... you can
get it at the link below:
http://www.mediafire.com/file/id0ljd5davj/derby.zip
java.lang.NullPointerException
at
org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
Source)
at
org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown
Source)
at
org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
Source)
at
org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown
Source)
at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown
Source)
at
org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source)
at
org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown
Source)
at
org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
Source)
at
org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(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.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown
Source)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
at org.apache.derby.tools.ij.main(Unknown Source)
PS: The sql got a bit munged last time because my email used HTML email
... so here it is again:
SELECT user0_.user_id AS col_0_0_,
SUM(account2_.balance) AS col_1_0_
FROM tbl_user user0_
INNER JOIN tbl_user_account accountlin1_
ON user0_.user_id = accountlin1_.user_id
INNER JOIN tbl_account account2_
ON accountlin1_.account_id = account2_.account_id
WHERE user0_.deleted = 'N'
AND ( account2_.account_type IN ( 'USER-01', 'USER' ) )
GROUP BY user0_.user_id
HAVING SUM(account2_.balance) >= 100.0
Regards,
Matt
On 12/06/2010 5:04 PM, Matt Doran wrote:
Hi there,
I have a relatively simple query that triggers a NPE in derby. The
query is generated out of Hibernate, but it's pretty easy to
understand (calculating users with total balances above 100). See below.
SELECT user0_.user_id AS col_0_0_,
/SUM/(account2_.balance) AS col_1_0_
FROM tbl_user user0_
INNER JOIN tbl_user_account accountlin1_
ON user0_.user_id = accountlin1_.user_id
INNER JOIN tbl_account account2_
ON accountlin1_.account_id = account2_.account_id
WHERE user0_.deleted = 'N'
AND ( account2_.account_type IN ( 'USER-01', 'USER' ) )
GROUP BY user0_.user_id
HAVING /SUM/(account2_.balance) >= 100.0
When I run this I get the following a nasty NPE ... see stack trace
below. If I remove the "HAVING" clause the query executes
successfully. I've attached a derby log with the query plans on
showing firstly the successful query without the HAVING clause and
then the stacktrace running the above query.
I'm running 10.4.2.0. And can provider anyone interested a copy of
the DB if that would help (it's only small - 500kb).
Any clues on this one?
java.lang.NullPointerException
at
org.apache.derby.impl.sql.execute.BasicSortObserver.getClone(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BasicSortObserver.insertNonDuplicateKey(Unknown
Source)
at
org.apache.derby.impl.sql.execute.AggregateSortObserver.insertNonDuplicateKey(Unknown
Source)
at
org.apache.derby.impl.store.access.sort.SortBuffer.insert(Unknown
Source)
at
org.apache.derby.impl.store.access.sort.MergeInserter.insert(Unknown
Source)
at
org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.loadSorter(Unknown
Source)
at
org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.openCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
Source)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown
Source)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown
Source)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
at org.apache.derby.tools.ij.main(Unknown Source)
Regards,
Matt
----------------------------------------------------------------
2010-06-12 07:21:50.018 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.3.0
- (802917): instance a816c00e-0129-2b09-3253-000000421138
on database directory
C:\Development\pc-ng-branch\server\working\data\internal\derby
Database Class Loader started - derby.database.classpath=''
2010-06-12 07:21:55.569 GMT Thread[main,5,main] (XID = 5823747), (SESSIONID =
1), (DATABASE =
C:\Development\pc-ng-branch\server\working\data\internal/derby), (DRDAID =
null), Cleanup action starting
2010-06-12 07:21:55.569 GMT Thread[main,5,main] (XID = 5823747), (SESSIONID =
1), (DATABASE =
C:\Development\pc-ng-branch\server\working\data\internal/derby), (DRDAID =
null), Failed Statement is: select user0_.user_id as col_0_0_,
sum(account2_.balance) as col_1_0_ from tbl_user user0_ inner join
tbl_user_account accountlin1_ on user0_.user_id=accountlin1_.user_id inner join
tbl_account account2_ on accountlin1_.account_id=account2_.account_id where
user0_.deleted='N' and (account2_.account_type in ('USER-01' , 'USER')) group
by user0_.user_id having sum(account2_.balance) < 100.0
java.lang.NullPointerException
at
org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
Source)
at
org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown
Source)
at
org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
Source)
at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown
Source)
at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source)
at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source)
at
org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source)
at
org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
Source)
at
org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(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.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
at org.apache.derby.tools.ij.main(Unknown Source)
Cleanup action completed
