wrong results for group by queries in the presence of primary keys

[monetdb-team]

Date: 2011-05-05 16:34:58 +0200
From: Daniel Boesswetter <<daniel.boesswetter>>
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: @njnes

Last updated: 2011-05-31 13:59:26 +0200

Comment 15787

Date: 2011-05-05 16:34:58 +0200
From: Daniel Boesswetter <<daniel.boesswetter>>

User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.17) Gecko/20110422 Ubuntu/9.10 (karmic) Firefox/3.6.17
Build Identifier:

Attached you will find a SQL script which creates 4 tables, inserts a few tuples and then queries them twice: the first query produces too few rows, but after dropping a primary key on one of the tables (it matters which one), the second query returns the correct results.

Looks like the optimizer optimizes a little too eagerly in the presence of primary keys.

Reproducible: Always

Steps to Reproduce:

--drop table facts;
create table facts (a_id bigint, b_id bigint);
insert into facts values(1,1);
insert into facts values(2,1);
insert into facts values(3,1);
insert into facts values(4,1);
insert into facts values(5,1);
insert into facts values(6,1);
insert into facts values(1,2);
insert into facts values(2,2);
insert into facts values(3,2);
insert into facts values(4,2);
insert into facts values(5,2);
insert into facts values(6,2);
insert into facts values(1,3);
insert into facts values(2,3);
insert into facts values(3,3);
insert into facts values(4,3);
insert into facts values(5,3);
insert into facts values(6,3);

--drop table a;
create table a (id bigint not null primary key, c_id bigint);
insert into a values(1,1);
insert into a values(2,1);
insert into a values(3,2);
insert into a values(4,2);
insert into a values(5,3);
insert into a values(6,3);

--drop table b;
create table b (id bigint not null primary key, name varchar(20));
insert into b values(1,'b1');
insert into b values(2,'b2');
insert into b values(3,'b3');

--drop table c;
create table c (id bigint not null primary key, name varchar(20));
insert into c values(1,'c1');
insert into c values(2,'c2');
insert into c values(3,'c3');

-- produces a wrong result:
select b.name, c.id, c.name from facts left join a on a_id = a.id left join b on b_id = b.id left join c on c_id = c.id group by b.name, c.name, c.id;

alter table c drop constraint c_id_pkey;

-- produces correct result:
select b.name, c.id, c.name from facts left join a on a_id = a.id left join b on b_id = b.id left join c on c_id = c.id group by b.name, c.name, c.id;

Actual Results:

+------+------+------+
| name | id | name |
+======+======+======+
| b1 | 1 | c1 |
| b1 | 2 | c2 |
| b1 | 3 | c3 |
+------+------+------+

Expected Results:

+------+------+------+
| name | id | name |
+======+======+======+
| b1 | 1 | c1 |
| b1 | 2 | c2 |
| b1 | 3 | c3 |
| b2 | 1 | c1 |
| b2 | 2 | c2 |
| b2 | 3 | c3 |
| b3 | 1 | c1 |
| b3 | 2 | c2 |
| b3 | 3 | c3 |
+------+------+------+

Michael Sioutis has reproduced the bug with the "latest development release" which he reported to monetdb-users on 09.04.2011.

Comment 15797

Date: 2011-05-13 15:27:23 +0200
From: @njnes

fixed (was a bug in exp_find_name).
Added test to BugTracker-2011

Comment 15801

Date: 2011-05-13 15:54:47 +0200
From: @njnes

Changeset 7316fbd4e9d2 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=7316fbd4e9d2

Changeset description:

fix bug #2798 (use correct old project list)

fixed bug #2807 (aggr on with primary key problems). Properly find column using
an expression.

Comment 15802

Date: 2011-05-13 15:54:49 +0200
From: @njnes

Changeset f1da207efb5c made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f1da207efb5c

Changeset description:

added test for bug #2807