Re: bug with aggregate + multi column index + index_scan

Brian Hirt <bhirt@mobygames.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