Re: Optimization rules for semi and anti joins

I wrote:
> Hence semijoins can be rearranged just as freely as inner joins.

I guess nobody checked my work, because that claim is bogus.  Consider
A semijoin (B innerjoin C on (Pbc)) on (Pab)=? (A semijoin B on (Pab)) innerjoin C on (Pbc)

In the second form the inner join is now using indeterminate B values.
What's more, if there are multiple C rows joining to some B, we could
get duplicated A rows, which can never happen in the first form.  So
semijoins do not commute with inner joins in their RHS.  A more accurate
statement seems to be that semijoins can be treated like innerjoins
for the purposes of rearrangement of other special joins.
        regards, tom lane