Re: BUG #4926: too few pathkeys for mergeclauses

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Version 8.3 has the same bug, apparently introduced along with the
> equivalence classes. In 8.2, the merge condition is reduced into (i=x
> AND i=y), IOW the planner eliminates the duplicate condition. I believe
> 8.2 would otherwise have the same problem as well.

The fact that 8.2 eliminates the redundant condition is more or less
accidental, I think. It's using equal() to detect duplicate
RestrictInfos coming up from the two input relations for the join,
while later versions rely on pointer equality for that. You can fool
8.2 by commuting the duplicate condition, but it still doesn't fail:

regression=# explain select * from a left outer join b on i=x and i=y and x=i;
QUERY PLAN
-----------------------------------------------------------------
Merge Left Join (cost=285.12..325.93 rows=2140 width=12)
Merge Cond: ((a.i = b.x) AND (a.i = b.y) AND (a.i = b.x))
-> Sort (cost=149.78..155.13 rows=2140 width=4)
Sort Key: a.i
-> Seq Scan on a (cost=0.00..31.40 rows=2140 width=4)
-> Sort (cost=135.34..140.19 rows=1940 width=8)
Sort Key: b.x, b.y
-> Seq Scan on b (cost=0.00..29.40 rows=1940 width=8)
(8 rows)

I think what this case may show is simply that the consistency
checking I added to create_mergejoin_plan in 8.3 is too strict.
Not quite convinced yet though.

Another possible solution for this particular case is to allow the
equivclass code to deduce x=y as an equivalence class, that is
the plan should enforce that check at the scan of b and then just
have one sort key for the merge. Not sure how complicated that is,
however, and in any case it may not fix every possible failure
case for create_mergejoin_plan.

regards, tom lane