Thread: BUG #2807: constraint violation

BUG #2807: constraint violation

From
"Ross Cohen"
Date:
The following bug has been logged online:

Bug reference:      2807
Logged by:          Ross Cohen
Email address:      rcohen@snurgle.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)

Re: BUG #2807: constraint violation

From
Tom Lane
Date:
"Ross Cohen" <rcohen@snurgle.org> writes:
> Description:        constraint violation

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