"William Leite Araújo" <william.bh@gmail.com> writes:
> The second does really what I desire. Show all records in table
> "posto", including that's without count in table "autuados". But the first,
> doesn't. Some elements on table "Posto" are ommited.
What PG version is this? 8.1.0 through 8.1.3 had a logic bug in merge
right joins, which perhaps is your problem, although the commit message
only mentions duplicate rows not missing ones.
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): Fix bug introduced intomergejoin logic by
performanceimprovement patch of 2005-05-13. When we find that a new inner tuple can't possibly match any outertuple
(becauseit contains a NULL), we can't immediately skip thetuple when we are in NEXTINNER state. Doing so can lead
toemittingmultiple copies of the tuple in FillInner mode, because wemay rescan the tuple after returning to a previous
markedtuple. Instead, proceed to NEXTOUTER state the same as we used to do. After we've found that there's no need to
returnto the markedposition, we can go to SKIPINNER_ADVANCE state instead of SKIP_TESTwhen the inner tuple is
unmatchable;this preserves the performanceimprovement. Per bug report from Bruce. I also made a couple ofcosmetic
coderearrangements and added a regression test for theproblem.
regards, tom lane