"Insert into ... select distinct ..." fails with MALException:mkey.bulk_rotate_xor_hash:operation

[monetdb-team]

Date: 2011-06-23 15:30:35 +0200
From: Gunnar Schröder <<gunnar.schroeder>>
To: MonetDB5 devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: @drstmane

Last updated: 2011-07-29 10:52:47 +0200

Comment 15852

Date: 2011-06-23 15:30:35 +0200
From: Gunnar Schröder <<gunnar.schroeder>>

User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1
Build Identifier:

The SQL-Statement "Insert into ... select distinct ..." and some "Insert into ... select ... group by ..." statements fail.

Reproducible: Always

Steps to Reproduce:

create table table1 (user_id integer, item_id integer);
create table table2 (user_id integer, item_id integer, primary key(user_id, item_id));

insert into table1 values (1,1);

INSERT INTO table2(user_id,item_id)
SELECT DISTINCT USER_ID, ITEM_ID
FROM table1;

delete from table2;
insert into table1 values (1,1);

SELECT DISTINCT USER_ID, ITEM_ID
FROM table1;

INSERT INTO table2(user_id,item_id)
SELECT DISTINCT USER_ID, ITEM_ID
FROM table1;

drop table table1;
drop table table2;

Actual Results:

Fails with exception:

MALException:mkey.bulk_rotate_xor_hash:operation failed Left operand not writeable

Expected Results:

Successful insert operation was expected.

Besides distinct this problem applies also to some group by constructs.

Comment 15902

Date: 2011-07-06 19:03:49 +0200
From: @drstmane

Changeset b69c9f900572 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fixed bug #2825:
since changeset [1c689400661e](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1c689400661e)  bulk_rotate_xor_hash() has side-effect free, i.e.,
produces a new result BAT instead of modifying its first input in place;
thus, the first (left) operand may well be read-only (or a view).
Removed the obsolete check that raised a false exception.

Added test in sql/test/BugTracker-2011/Tests/mkey-bulk_rotate_xor_hash.Bug-2825.*

Comment 16014

Date: 2011-07-29 10:52:47 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.