You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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:
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:
The text was updated successfully, but these errors were encountered: