db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ryan P Bobko <r...@ostrich-emulators.com>
Subject inexplicable query problem
Date Sun, 09 Mar 2008 01:21:21 GMT
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
-- 
I would never want to leave this county
Where roads are fast and knowledge easy
	--Kitchens of Distinction

Mime
View raw message