Re: BUG #5255: COUNT(*) returns wrong result with LEFT JOIN - Mailing list pgsql-bugs

"Matteo Beccati" <php@beccati.com> writes:
> With the following data set a SELECT * query returns 1 rows, while SELECT
> COUNT(*) returns 2.

Hm, looks like the join-elimination patch is firing mistakenly.  It's not
so much the count(*) that does it as the lack of any select-list
references to a:

regression=# explain SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
                           QUERY PLAN
-----------------------------------------------------------------
 Hash Left Join  (cost=64.00..132.85 rows=720 width=12)
   Hash Cond: (b.a_id = a.id)
   Filter: ((a.id IS NULL) OR (a.id > 0))
   ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on a  (cost=0.00..34.00 rows=2400 width=4)
(6 rows)

regression=# explain SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
(1 row)

I guess we missed something about when it's safe to do this optimization.

            regards, tom lane