Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UNIQUE constraint does not set tkey property on the corresponding BAT #3374

monetdb-team opened this issue Nov 30, 2020 · 0 comments

UNIQUE constraint does not set tkey property on the corresponding BAT #3374

monetdb-team opened this issue Nov 30, 2020 · 0 comments


Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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 in the MonetDB repo, refers to this bug.

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

Changeset description:

mark a extend.project(grpby) column as key (when we only have one grpby column).
This solves bug #3374 (and possibly other performance issues)

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant