BUG #1241: returns different result for the same result with differnt plans. - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1241
Logged by:          Xiaoyu Wang

Email address:      wangxy@cs.brandeis.edu

PostgreSQL version: 7.4.3

Operating system:   Mandrake Linux 9.2 3.3.1-2mdk

Description:        returns different result for the same result with
differnt plans.

Details:

database: TPC-H with scale factor=1.0
query: 13.sql (TPC-H)

select
  c_count,
  count(*) as custdist
from
  (
    select
      c_custkey,
      count(o_orderkey)
    from
      customer left outer join orders on
        c_custkey = o_custkey
        and o_comment not like '%special%requests'
    group by
      c_custkey
  ) as c_orders (c_custkey, c_count)
group by
  c_count
order by
  custdist desc,
  c_count desc;


I ran the query with enable_mergejoin set to on/off, the results are
different. Postgres chose Merge Left Join when enable_mergejoin is on and
Hash Left Join when it is off. I dumped the results to two files, merge.data
and hash.data. Here is the result when I do a diff:

diff merge.data hash.data
3d2
<        0 |    50004
41a41
>        0 |        4