Date: 2011-08-03 09:27:39 +0200
From: Tim Ruhl <>
To: SQL devs <>
Version: 11.3.7 (Apr2011-SP2) [obsolete]
CC: @njnes

Last updated: 2011-09-16 15:04:34 +0200

Comment 16065

Date: 2011-08-03 09:27:39 +0200
From: Tim Ruhl <>

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.30 (KHTML, like Gecko) Ubuntu/11.04 Chromium/12.0.742.112 Chrome/12.0.742.112 Safari/534.30
Build Identifier:

Using multi-column constraints causes the data in the table to become corrupted. Of each constraint one column is affected and always the same column. I've tried reordering the columns in the table create, changing the column order in the constraint, and changing the column that gets a duplicate value, but in all cases it always was the same column that got corrupted data.

Reproducible: Always

Steps to Reproduce:

Run the following script

DROP TABLE "portmapping";
CREATE TABLE "portmapping" (
"id" int NOT NULL AUTO_INCREMENT,
"port" int NOT NULL,
"type" int NOT NULL,
"appname" varchar(25) NOT NULL,
"description" varchar(255) DEFAULT NULL,
CONSTRAINT "port" UNIQUE ("port","type"),
CONSTRAINT "appname" UNIQUE ("appname","type"),
PRIMARY KEY ("id")
) ;

-- duplicate port and appname
INSERT INTO "portmapping" VALUES (2,1,1,'name','test');
INSERT INTO "portmapping" VALUES (3,1,2,'name','test');

-- duplicate type
INSERT INTO "portmapping" VALUES (4,5,1,'x','test');
INSERT INTO "portmapping" VALUES (5,5,1,'y','test');

-- unique records
INSERT INTO "portmapping" VALUES (6,7,1,'z1','test');
INSERT INTO "portmapping" VALUES (7,8,2,'z2','test');

SELECT * FROM "portmapping";

Actual Results:

+------+-------+------+---------+-------------+
| id | port | type | appname | description |
+======+=======+======+=========+=============+
| 2 | 1 | 1 | name | test |
| 3 | 2049 | 2 | 6��
| test |
| 4 | 1 | 1 | name | test |
| 5 | 2050 | 1 | 5��
| test |
| 6 | 5 | 1 | x | test |
| 7 | 10241 | 2 | �,/ | test |
+------+-------+------+---------+-------------+
6 tuples

Expected Results:

Expected results should be as when the constraint clauses are removed from the table create statement in the script.

+------+------+------+---------+-------------+
| id | port | type | appname | description |
+======+======+======+=========+=============+
| 2 | 1 | 1 | name | test |
| 3 | 1 | 2 | name | test |
| 4 | 5 | 1 | x | test |
| 5 | 5 | 1 | y | test |
| 6 | 7 | 1 | z1 | test |
| 7 | 8 | 2 | z2 | test |
+------+------+------+---------+-------------+
6 tuples

tim@tim-E6510$ mserver5 --version
MonetDB 5 server v11.3.7 "Apr2011-SP2" (32-bit, 32-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 3.8GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.12 2011-01-15 (compiled with 8.12)
openssl: OpenSSL 0.9.8o 01 Jun 2010 (compiled with OpenSSL 0.9.8o 01 Jun 2010)
libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: root@ottar.ins.cwi.nl (i686-pc-linux-gnu)
Compilation: gcc -O2 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize
Linking : /usr/bin/ld -Wl,-Bsymbolic-functions

Comment 16066

Date: 2011-08-03 10:00:13 +0200
From: @sjoerdmullender

When assertions are enabled in the server, it crashes with:
mserver5: .../monetdb5/modules/atoms/str.mx:1658: strLength: Assertion `l <2147483647' failed.
Aborted

Comment 16067

Date: 2011-08-03 10:16:14 +0200
From: @sjoerdmullender

Interesting: if you change the names of the constraints (which in your example are the same as the name of some of the columns), then the query works.

Comment 16068

Date: 2011-08-03 18:41:01 +0200
From: @njnes

fixed by accessing idx columns using unique names (ie prefixed by '%')

Comment 16070

Date: 2011-08-03 19:10:46 +0200
From: @njnes

Changeset a7c28593a171 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=a7c28593a171

Changeset description:

added test for bug #2849

Comment 16240

Date: 2011-09-16 15:04:34 +0200
From: @sjoerdmullender

The Aug2011 version has been released.