db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: inexplicable query problem
Date Sun, 09 Mar 2008 10:51:44 GMT
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
>   


Mime
View raw message