Re: bug with aggregate + multi column index + index_scan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Hirt <bhirt(at)mobygames(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: bug with aggregate + multi column index + index_scan
Date: 2006-01-29 16:31:46
Message-ID: 5075.1138552306@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Brian Hirt <bhirt(at)mobygames(dot)com> writes:
> I've run across a rather nasty bug in 8.1.2. It seems when the
> planer uses an index_scan within a GroupAggregate for a multi column
> index you can get incorrect results.

Good catch. Looks to me like it only happens if two or more leading
index columns are equated to the same constant value, ie
where id1 = 1 and id2 = 1 and ...
will show the bug but
where id1 = 1 and id2 = 2 and ...
won't. Does that match up with the original behavior that led you to
make the test case?

The problem is that implied equality deduction causes the planner to
conclude id1 = id2, and this extra bit of info is confusing the code
that determines whether the index's sort order can be considered to
match the needs of the GROUP BY clause. So you get a plan that feeds
the IndexScan directly to GroupAggregate, which is wrong because the
data isn't sorted by "grp".

In the related case
select grp,sum(v) from test where id1 = 1 and id2 = 2 and
day = '1/1/2006' group by grp order by sum(v) desc;
it *is* OK to decide that the indexscan result is effectively sorted
by "grp", so it's important to have this check ... it's just not being
done quite right. Thanks for the test case!

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2006-01-29 18:58:19 Re: bug with aggregate + multi column index + index_scan
Previous Message Bernhard Rosenkraenzer 2006-01-29 12:28:43 BUG #2220: PostgreSQL-JDBC 8.1-404 fails to compile with ecj