The following bug has been logged online:

Bug reference: 2807
Logged by: Ross Cohen
Email address: rcohen (AT) snurgle (DOT) org
PostgreSQL version: 8.2
Operating system: linux (fedora core 4, released RPMS)
Description: constraint violation
Details:

This query:
select
photo_0.id,
cert_2.id,
user_profile_3.id,
interest_profile_4.id
from photo as photo_0
left join cert as cert_2
on ((cert_2.cert_domain = 'photo' and
cert_2.cert_type = 'has-photo' and
cert_2.right_id = photo_0.id ))
left join user_profile as user_profile_3
on ((user_profile_3.id = cert_2.left_id ))
left join interest_profile as interest_profile_4
on ((interest_profile_4.id = cert_2.left_id ))
where
-- these 2 clauses should give the same results
( user_profile_3.id = '19' or interest_profile_4.id = '19' );
-- cert_2.left_id = '19';

Returns tuples with both the user_profile and interest_profile ids as null.

The plan is as follows:
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
Hash Left Join (cost=3569.99..3617.56 rows=59 width=32)
Hash Cond: (photo_0.id = cert_2.right_id)
-> Seq Scan on photo photo_0 (cost=0.00..41.71 rows=1171 width=8)
-> Hash (cost=3569.99..3569.99 rows=1 width=32)
-> Nested Loop Left Join (cost=0.00..3569.99 rows=1 width=32)
Filter: ((user_profile_3.id = 19::bigint) OR
(interest_profile_4.id = 19::bigint))
-> Nested Loop Left Join (cost=0.00..3497.60 rows=11
width=32)
-> Index Scan using cert_cert_type_id_index on cert
cert_2 (cost=0.00..3461.27 rows=11 width=24)
Index Cond: ((cert_type)::text =
'has-photo'::text)
Filter: ((cert_domain)::text = 'photo'::text)
-> Index Scan using interest_profile_pkey on
interest_profile interest_profile_4 (cost=0.00..3.29 rows=1 width=8)
Index Cond: (interest_profile_4.id =
cert_2.left_id)
-> Index Scan using user_profile_pkey on user_profile
user_profile_3 (cost=0.00..6.56 rows=1 width=8)
Index Cond: (user_profile_3.id = cert_2.left_id)
(14 rows)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Ross Cohen" <rcohen (AT) snurgle (DOT) org> writes:
Thanks for the report. I've applied a patch that I believe fixes this
.... at least, it fixes Jeff Davis' test case.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org