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
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.57 Safari/537.36
Build Identifier:
A UNIQUE constraint on a column does not set tkey property on the corresponding BAT.
This has a performance impact on the algorithms which check that property for optimization purposes and find it unset.
For example:
create table x as select distinct name from sys.tables with data;
alter table x add unique (name);
select * from x where name='tables';
This calls a BATsubselect, which does check the tkey property. It finds it unset, so it cannot compute the correct result estimate (which would be 1 in this case).
Another thing: it builds a hash table on the fly to perform a hash-select. But isn't a hash table already built and stored persistently, with the unique constraint? Can't that one be used?
somehow the combination of the extend (group by result for distinct) and
leftfetchjoin to retrieve the column data doesn't result in a set tkey in
the create table with data. In a single select distinct it is set. So
We now generate a setKey when we know the result of the distinct (in this case) is unique (ie a single column result). Then the key info is propagated solving the rest of the problems.
The text was updated successfully, but these errors were encountered:
Date: 2013-09-27 14:12:50 +0200
From: @swingbit
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @mlkersten, @njnes
Last updated: 2016-06-23 10:24:06 +0200
Comment 19200
Date: 2013-09-27 14:12:50 +0200
From: @swingbit
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.57 Safari/537.36
Build Identifier:
A UNIQUE constraint on a column does not set tkey property on the corresponding BAT.
This has a performance impact on the algorithms which check that property for optimization purposes and find it unset.
For example:
create table x as select distinct name from sys.tables with data;
alter table x add unique (name);
select * from x where name='tables';
This calls a BATsubselect, which does check the tkey property. It finds it unset, so it cannot compute the correct result estimate (which would be 1 in this case).
Another thing: it builds a hash table on the fly to perform a hash-select. But isn't a hash table already built and stored persistently, with the unique constraint? Can't that one be used?
Same considerations for join and other operators.
Reproducible: Always
Comment 19262
Date: 2013-10-09 14:41:56 +0200
From: @njnes
unfortunately unique keys are not that unique, only if also the NOT NULL constraint holds we could set tkey.
Comment 19263
Date: 2013-10-09 14:46:33 +0200
From: @swingbit
Good point.
Then the key property should be set when a column is marked unique and not null.
Comment 19302
Date: 2013-10-23 15:00:38 +0200
From: @sjoerdmullender
I believe changesets 0dc0ef3e0c0f and f6d11cb5e2d3 fixed this issue.
Roberto, can you check?
Comment 19304
Date: 2013-10-24 18:18:43 +0200
From: @swingbit
Yes, the property is set as expected, thanks
Comment 19383
Date: 2013-12-03 13:59:37 +0100
From: @sjoerdmullender
Feb2013-SP6 has been released.
Comment 20862
Date: 2015-05-11 17:27:06 +0200
From: @swingbit
This issue was fixed in Feb2013 SP6, but it seems it's back in Oct2014.
A column marked NOT NULL and with a UNIQUE constraint does not have the tkey property set.
Comment 20875
Date: 2015-05-20 21:57:09 +0200
From: @njnes
with deletes (mark by the oid lists) we cannot set the bat tkey.
Comment 20876
Date: 2015-05-20 22:06:55 +0200
From: @njnes
somehow the combination of the extend (group by result for distinct) and
leftfetchjoin to retrieve the column data doesn't result in a set tkey in
the create table with data. In a single select distinct it is set. So
indeed some more fixing needed.
Comment 21597
Date: 2015-11-25 22:34:05 +0100
From: @mlkersten
Is this one fixed in the latest release?
Comment 21735
Date: 2016-01-18 11:42:47 +0100
From: @swingbit
Martin: not yet in Jul2015 SP2
Comment 22057
Date: 2016-04-17 10:07:32 +0200
From: MonetDB Mercurial Repository <>
Changeset 3fa0a17123c9 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=3fa0a17123c9
Changeset description:
Comment 22058
Date: 2016-04-17 10:08:23 +0200
From: @njnes
We now generate a setKey when we know the result of the distinct (in this case) is unique (ie a single column result). Then the key info is propagated solving the rest of the problems.
The text was updated successfully, but these errors were encountered: