I'm not sure if I'm making wrong assumptions on the semantics of
subselect or if this a problem in PostgreSQL:
I'm using query with the following structure
select a1.id, c1.foo
from a as a1
join b on ...
join c as c1 on ...
where a.id in (
select a2.id
from a as a2
left join c as c2 on ...
where c2.bla is null)
The query unexpectedly returns an empty result set. When I replace the
subselect by the equivalent list of integer literals (i.e. just the ID
values returned by the subselect run as a separate query), the result
set is no longer empty:
select a.id, c.foo
from a
join b on ...
join c on ...
where a.id in (123, 456, ... )
My impression is that PostgreSQL somehow confuses the references to the
tables a and c from the outer and the inner select.
I have tried to isolate the problem with a small set of entries, but
when I delete unrelated entries from my tables, the query plan changes
and the problem no longer occurs, which again confirms my suspicion that
this a problem in Postgres.
The problem occurs with PostgreSQL 8.4.0. I cannot reproduce it with
PostgreSQL 8.3.7.
If this is indeed a bug in Postgres, please let me know the best way to
provide more information. The tables in question have a few hundred rows
each, so I could probably come up with a script demonstrating the
problem.
Best regards,
Harald
*******************************************
innovative systems GmbH Navigation-Multimedia
Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser
Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980
*******************************************
Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat
sindoder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und loeschen Sie diese
Mail.Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have
receivedthis e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying,
disclosureor distribution of the contents in this e-mail is strictly forbidden.
*******************************************
