Re: Spurious rows returned with left join?

Edmund Bacon <ebacon@onesystem.com> writes:
> Consider the following:
> ...
> Note that I get 2 rows where t1_a = 3.

Are you getting a Merge Right Join plan for that?  If so, you're likely
getting bit by this bug:

2006-03-17 14:38  tgl

    * src/: backend/executor/nodeMergejoin.c,
    test/regress/expected/join.out, test/regress/expected/join_1.out,
    test/regress/sql/join.sql (REL8_1_STABLE),
    backend/executor/nodeMergejoin.c, test/regress/expected/join.out,
    test/regress/expected/join_1.out, test/regress/sql/join.sql: Fix
    bug introduced into mergejoin logic by performance improvement
    patch of 2005-05-13.  When we find that a new inner tuple can't
    possibly match any outer tuple (because it contains a NULL), we
    can't immediately skip the tuple when we are in NEXTINNER state.
    Doing so can lead to emitting multiple copies of the tuple in
    FillInner mode, because we may rescan the tuple after returning to
    a previous marked tuple.  Instead, proceed to NEXTOUTER state the
    same as we used to do.    After we've found that there's no need to
    return to the marked position, we can go to SKIPINNER_ADVANCE state
    instead of SKIP_TEST when the inner tuple is unmatchable; this
    preserves the performance improvement.    Per bug report from Bruce.
    I also made a couple of cosmetic code rearrangements and added a
    regression test for the problem.

This'll be fixed in 8.1.4, or if you're in a hurry you can get the patch
from our CVS server.

            regards, tom lane