bug with aggregate + multi column index + index_scan

From: Brian Hirt <bhirt(at)mobygames(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: Brian Hirt <bhirt(at)mobygames(dot)com>
Subject: bug with aggregate + multi column index + index_scan
Date: 2006-01-29 05:54:13
Message-ID: 2F3C2966-C9D0-4721-8CEE-0A477D623CEF@mobygames.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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. fwiw i also see this on a dual
xeon box running 8.1.1 and redhat 7.3.

I've created a simple test case that I hope isolates the problems
sufficiently.

x86imac:/tmp bhirt$ psql --echo-all --file=test weblogs
select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------
PostgreSQL 8.1.2 on i686-apple-darwin8.4.1, compiled by GCC i686-
apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5250)
(1 row)

create table test (
id1 int4,
id2 int4,
day date,
grp text,
v int4);
CREATE TABLE
create index test_idx on test (id1,id2,day,grp);
CREATE INDEX
insert into test values (1,1,'1/1/2006','there',1);
INSERT 0 1
insert into test values (1,1,'1/2/2006','there',2);
INSERT 0 1
insert into test values (1,1,'1/3/2006','there',3);
INSERT 0 1
insert into test values (1,1,'1/1/2006','hi',2);
INSERT 0 1
insert into test values (1,1,'1/2/2006','hi',3);
INSERT 0 1
insert into test values (1,1,'1/3/2006','hi',4);
INSERT 0 1
select grp,sum(v) from test where id1 = 1 and id2 = 1 and day between
'1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;
grp | sum
-------+-----
hi | 4
hi | 3
there | 3
hi | 2
there | 2
there | 1
(6 rows)

set enable_indexscan to false;
SET
select grp,sum(v) from test where id1 = 1 and id2 = 1 and day between
'1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;
grp | sum
-------+-----
hi | 9
there | 6
(2 rows)

x86imac:/tmp bhirt$

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bernhard Rosenkraenzer 2006-01-29 12:28:43 BUG #2220: PostgreSQL-JDBC 8.1-404 fails to compile with ecj
Previous Message Tom Lane 2006-01-29 05:25:59 Re: BUG #2218: Variables selected in VIEWs under different names break queries using those views