The following bug has been logged online:

Bug reference: 1241
Logged by: Xiaoyu Wang

Email address: wangxy (AT) cs (DOT) 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



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

http://archives.postgresql.org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
This is not *nearly* enough information to let someone else reproduce
the problem. (Heck, I can't even tell which answer is wrong.)
Could you boil it down to a self-contained test script?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
Found it. This has actually been broken ever since we implemented outer
joins in PG 7.1. If the join is large enough to need to be broken into
multiple hash batches, and some of the inner batches are completely empty,
the code would skip processing those batches altogether. Which is fine ...
unless it's a LEFT JOIN :-(. Here's the patch for the 7.4 branch:

Index: nodeHashjoin.c
================================================== =================
RCS file: /cvsroot/pgsql-server/src/backend/executor/nodeHashjoin.c,v
retrieving revision 1.57.2.1
diff -c -r1.57.2.1 nodeHashjoin.c
*** nodeHashjoin.c 25 Nov 2003 19:17:16 -0000 1.57.2.1
--- nodeHashjoin.c 17 Sep 2004 18:23:05 -0000
***************
*** 602,613 ****
}

/*
! * We can skip over any batches that are empty on either side. Release
! * associated temp files right away.
*/
while (newbatch <= nbatch &&
! (innerBatchSize[newbatch - 1] == 0L ||
! outerBatchSize[newbatch - 1] == 0L))
{
BufFileClose(hashtable->innerBatchFile[newbatch - 1]);
hashtable->innerBatchFile[newbatch - 1] = NULL;
--- 602,615 ----
}

/*
! * Normally we can skip over any batches that are empty on either side
! * --- but for JOIN_LEFT, can only skip when left side is empty.
! * Release associated temp files right away.
*/
while (newbatch <= nbatch &&
! (outerBatchSize[newbatch - 1] == 0L ||
! (innerBatchSize[newbatch - 1] == 0L &&
! hjstate->js.jointype != JOIN_LEFT)))
{
BufFileClose(hashtable->innerBatchFile[newbatch - 1]);
hashtable->innerBatchFile[newbatch - 1] = NULL;



regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Mark Shewmaker <mark (AT) primefactor (DOT) com> writes:
cc'd to pgbugs in case anyone else is wondering the same.

It's not. If you look at ExecInitHashJoin you'll see that it rejects
JOIN_RIGHT, should the planner be buggy enough to ask it to do that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings