Hi Ryan,
Without knowing what version of derby you are using, I dare say you've
hit DERBY-3301:
http://issues.apache.org/jira/browse/DERBY-3301
The bug is basically a too optimistic optimization, causing derby to
return the wrong result with nested "where [not] exists" queries.
DERBY-3301 was recently reported and fixed on the main trunk, but was
also merged to the 10.3 branch. Whenever 10.4, or the next
update-release of 10.3 ships, it should be available.
HTH,
Thomas
Ryan P Bobko wrote:
Hi Folks,
I've run into a problem with a query that I can't for the life of me resolve.
I'd like your advice.
This query returns 0 rows:
select d.id, s.description, c.description, d.title from document d
join status s on d.statusid=s.id
join source c on d.sourceid=c.id
where s.id=6 and d.id not in (select docid from docedition);
And this one returns 19 rows (as expected):
select d.id from document d
join status s on d.statusid=s.id
join source c on d.sourceid=c.id
where s.id=6 and d.id not in (select docid from docedition);
The only difference is what is what columns are selected, and it's driving me
batty! I'm willing to stipulate that there's something wrong with my setup
because I cannot create a minimal example that shows the problem. I've run
SYSCS_UTIL.SYSCS_CHECK_TABLE on all the tables with no problems. The document
and docedition tables are around 200 000 rows big, but the other tables are
around 20 rows each. I looked at the execution plan, but nothing seems out of
the ordinary except that the final projection calculates 0 rows returned.
Any ideas? I've run out.
ry
