BUG #4936: Bad result for SQL query

The following bug has been logged online:

Bug reference:      4936
Logged by:          Mathieu Dupuis
Email address:      mdupuis@premieresloges.ca
PostgreSQL version: 8.4.0
Operating system:   Ubuntu
Description:        Bad result for SQL query
Details:

I have found a probleme with a sql query that I run in one of my program.

I have simplified the query as much as I can to figure out what the problem
is. Have have also compere the result with the same data between postgresql
8.4 and postgresql 8.3 and the resultset is different.

Here are the result of the simplest query that recreate the problem, as you
can see the p.id and sod.performance_id are the same because I join on those
fields but if I compare with one, I have a different result that if I
compare the other.

-- 1) Return 1 rows (bad)
select p.performance_datetime, sod.id
from saleorder_details sod, performance p, sale
where p.id in (select 21030434)
  and p.id = sod.performance_id
  and sale.id = sod.sale_id;

-- 2) Return many rows (good)
select p.performance_datetime, sod.id
from saleorder_details sod, performance p, sale
where p.id in (21030434)
  and p.id = sod.performance_id
  and sale.id = sod.sale_id;

-- 3) Return many rows (good)
select p.performance_datetime, sod.id
from saleorder_details sod, performance p, sale
where sod.performance_id in (select 21030434)
  and p.id = sod.performance_id
  and sale.id = sod.sale_id;