"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> Description: returns different result for the same result with
> differnt plans.
> 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:
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