Greg Stark wrote:
> On Thu, Jul 16, 2009 at 9:07 PM, Roman Kononov<kononov(at)ftml(dot)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
>> 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.
In response to
pgsql-bugs by date
|Next:||From: Jan-Ivar Mellingen||Date: 2009-07-17 08:12:41|
|Subject: Huge speed penalty using <>TRUE instead of =FALSE|
|Previous:||From: Alvaro Herrera||Date: 2009-07-17 00:17:59|
|Subject: Re: Bug 4906?|