Re: BUG #4926: too few pathkeys for mergeclauses

Greg Stark wrote:
> On Thu, Jul 16, 2009 at 9:07 PM, Roman Kononov<kononov@ftml.net> wrote:
>> test=# create table junk(i int);
>> CREATE TABLE
>> test=# select * from junk left outer join (select coalesce(i,1) as x,
>> coalesce(i,2) as y from junk) t on coalesce(i,3)=x and coalesce(i,4)=y and
>> coalesce(i,5)=x;
>> ERROR:  too few pathkeys for mergeclauses
>
> Thanks for the bug report. That's definitely not supposed to be
> happening. It's always nice when it's easy to reproduce the problem
> like this.

Yep. This can be further reduced into this:

CREATE TABLE a (i integer);
CREATE TABLE b (x integer, y integer);

select * from a left outer join b on i=x and i=y and i=x;

The planner is choosing a merge join, where the outer side (table a) is
sorted by (i), and the inner side is sorted by (x, y). But that doesn't
work with the merge condition (i=x AND i=y AND i=x).

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.

I can see two different things that you could say is at fault here:

1. We no longer eliminate the duplicate condition, but the
find_mergeclauses_for_pathkeys() + make_inner_pathkeys_for_merge()
combination relies on there being no duplicates. We should try harder to
eliminate duplicates in left join clauses.

2. make_inner_pathkeys_for_merge() should have created sort order (x, y,
x) for the inner side.

The first solution is what we probably want, to avoid unnecessary work
at execution time.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com