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 06:53:06.890 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.2.0
- (689064): instance a816c00e-0129-2aee-e733-000000455e90
on database directory
C:\Development\pc-ng-branch\server\working\data\internal\derby
Database Class Loader started - derby.database.classpath=''
2010-06-12 06:53:11.211 GMT Thread[main,5,main] (XID = 5823661), (SESSIONID =
0), 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
******* Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 9
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1.00
optimizer estimated cost: 505.19
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 18
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=18
Number of rows output=9
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 6.40
optimizer estimated cost: 505.19
Source result set:
Project-Restrict ResultSet (10):
Number of opens = 1
Rows seen = 18
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 6.40
optimizer estimated cost: 505.19
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 29
Rows seen from the right = 18
Rows filtered = 0
Rows returned = 18
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 6.40
optimizer estimated cost: 505.19
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 9
Rows seen from the right = 29
Rows filtered = 0
Rows returned = 29
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
6.40
optimizer estimated cost:
467.67
Left result set:
Table Scan ResultSet for TBL_USER at
read committed isolation level using instantaneous share row locking chosen by
the optimizer
Number of opens = 1
Rows seen = 9
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0,
10}
Number of columns fetched=2
Number of pages visited=2
Number of rows qualified=9
Number of rows visited=11
Scan type=heap
start position:
null stop position:
null qualifiers:
Column[0][0] Id: 10
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count:
1.60
optimizer estimated cost:
385.93
Right result set:
Index Row to Base Row ResultSet for
TBL_USER_ACCOUNT:
Number of opens = 9
Rows seen = 29
Columns accessed from heap = {1, 2}
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
6.40
optimizer estimated cost:
81.74
Index Scan ResultSet for
TBL_USER_ACCOUNT using constraint FK_USER_ACCOUNT_USER at read committed
isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 9
Rows seen = 29
Rows filtered = 0
Fetch Size = 16
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of columns
fetched=All
Number of columns
fetched=2
Number of deleted rows
visited=0
Number of pages
visited=9
Number of rows
qualified=29
Number of rows
visited=37
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row
count: 6.40
optimizer estimated
cost: 81.74
Right result set:
Project-Restrict ResultSet (9):
Number of opens = 29
Rows seen = 29
Rows filtered = 11
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count:
6.40
optimizer estimated cost:
37.52
Source result set:
Index Row to Base Row ResultSet for
TBL_ACCOUNT:
Number of opens = 29
Rows seen = 29
Columns accessed from heap = {1, 3}
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
6.40
optimizer estimated cost:
37.52
Index Scan ResultSet for
TBL_ACCOUNT using constraint SQL070717013244600 at read committed isolation
level using share row locking chosen by the optimizer
Number of opens = 29
Rows seen = 29
Rows filtered = 0
Fetch Size = 1
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of columns
fetched=All
Number of columns
fetched=2
Number of deleted rows
visited=0
Number of pages
visited=58
Number of rows
qualified=29
Number of rows
visited=29
Scan type=btree
Tree height=2
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row
count: 6.40
optimizer estimated
cost: 37.52
2010-06-12 06:53:13.265 GMT Thread[main,5,main] (XID = 5823663), (SESSIONID =
0), (DATABASE =
C:\Development\pc-ng-branch\server\working\data\internal/derby), (DRDAID =
null), Cleanup action starting
2010-06-12 06:53:13.265 GMT Thread[main,5,main] (XID = 5823663), (SESSIONID =
0), (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.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)
Cleanup action completed
