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
