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$
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 |