Re: bug with aggregate + multi column index + index_scan

Tom,

Yes, what you describe are exactly the circumstances that are
required for our query to fail. Once again, thanks for the great
help and quick fix. Do you think this fix will make 8.1.3?

Best Regards,

Brian Hirt

On Jan 29, 2006, at 9:31 AM, Tom Lane wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq