Cannot set value of indexed column when doing UPDATE

[monetdb-team]

Date: 2012-07-05 20:59:27 +0200
From: Benjie Chen <>
To: SQL devs <>
Version: 11.11.5 (Jul2012)
CC: @njnes

Last updated: 2012-07-17 13:46:40 +0200

Comment 17413

Date: 2012-07-05 20:59:27 +0200
From: Benjie Chen <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_7) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.56 Safari/536.5
Build Identifier: MonetDB v11.9.7 (Apr2012-SP2)

I have a table called "datastore_predicate", which a column "unit_id" that is a FK to another table.

I ran the following and got an error.

sql>update datastore_predicate set unit_id = NULL where id = 30005;
MAPI = (monetdb) /tmp/.s.monetdb.50000
ACTION= read_line
QUERY = update datastore_predicate set unit_id = NULL where id = 30005;
ERROR = !Connection terminated

I also tried unit_id = 1, etc.

Running on Linux MonetDB v11.9.7 (Apr2012-SP2)

Reproducible: Always

Comment 17414

Date: 2012-07-05 21:08:54 +0200
From: Benjie Chen <>

Update: the issue is NOT due to column being a FK, but because the FK is indexed. My ORM automatically adds index for FK columns. When I remove that index, the UPDATE works. So it seems like updating a column with index breaks.

Comment 17421

Date: 2012-07-06 13:53:39 +0200
From: @njnes

do you have an example test (sql script) to reproduce the problem.

Comment 17424

Date: 2012-07-06 15:51:52 +0200
From: Benjie Chen <>

(In reply to comment 2)

do you have an example test (sql script) to reproduce the problem.

Yes. I am going to post a trace that does not work, then 3 traces that do work. The 3 traces that do show that to re-produce the bug, you need FK constraint, an index, and more than one row in second table.

First, trace that does NOT work:

sql>create table "x"("id" int not null primary key);
operation successful (2.242ms)
sql>create table "y"("id" int not null primary key,"x_id" int,constraint "x_id_refs_id" FOREIGN KEY("x_id") references "x"("id"));
operation successful (1.279ms)
sql>\d y
CREATE TABLE "sys"."y" (
"id" INTEGER NOT NULL,
"x_id" INTEGER,
CONSTRAINT "y_id_pkey" PRIMARY KEY ("id"),
CONSTRAINT "x_id_refs_id" FOREIGN KEY ("x_id") REFERENCES "sys"."x" ("id")
);
sql>\d x
CREATE TABLE "sys"."x" (
"id" INTEGER NOT NULL,
CONSTRAINT "x_id_pkey" PRIMARY KEY ("id")
);
sql>create index "y_x_id" ON "y"("x_id");
operation successful (3.404ms)
sql>insert into "x" VALUES(1),(2),(3);
3 affected rows (1.209ms)
sql>insert into "y" VALUES(100,1),(101,2);
2 affected rows (2.040ms)
sql>update y SET x_id = 3 WHERE id = 101;
MAPI = (monetdb) /tmp/.s.monetdb.50000
ACTION= read_line
QUERY = update y SET x_id = 3 WHERE id = 101;
ERROR = !Connection terminated

Now a similar trace that does work - this shows that the FK constraint is necessary to re-create the bug:

sql>create table "x"("id" int not null primary key);
operation successful (1.294ms)
sql>create table "y"("id" int not null primary key,"x_id" int);
operation successful (1.329ms)
sql>create index "y_x_id" ON "y"("x_id");
operation successful (1.109ms)
sql>insert into "x" VALUES(1),(2),(3);
3 affected rows (1.137ms)
sql>insert into "y" VALUES(100,1),(101,2);
2 affected rows (1.195ms)
sql>update y SET x_id = 3 WHERE id = 101;
1 affected row (1.261ms)

Another similar trace that does work - this shows the index is necessary to re-create the bug:

sql>create table "x"("id" int not null primary key);
operation successful (1.303ms)
sql>create table "y"("id" int not null primary key,"x_id" int,constraint "x_id_refs_id" FOREIGN KEY("x_id") references "x"("id"));
operation successful (1.470ms)
sql>insert into "x" VALUES(1),(2),(3);
3 affected rows (1.288ms)
sql>insert into "y" VALUES(100,1),(101,2);
2 affected rows (2.372ms)
sql>update y SET x_id = 3 WHERE id = 101;
1 affected row (2.226ms)

Another trace that does work, shows that the second table must have more than one row to re-produce the bug:

sql>create table "x"("id" int not null primary key);
operation successful (1.316ms)
sql>create table "y"("id" int not null primary key,"x_id" int);
operation successful (1.320ms)
sql>create index "y_x_id" ON "y"("x_id");
operation successful (1.063ms)
sql>insert into "x" VALUES(1),(2),(3);
3 affected rows (1.152ms)
sql>insert into "y" VALUES(100,1);
1 affected row (1.059ms)
sql>update y SET x_id = 3 WHERE id = 100;
1 affected row (1.212ms)

Comment 17426

Date: 2012-07-06 17:12:10 +0200
From: @njnes

Changeset 03516346b4d5 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=03516346b4d5

Changeset description:

fixed bugs 3113 and 3114

3113: single column indices aren't stored, ie don't have updates

3114: properly use the update 'column expression' instead of the original
column expression.

Comment 17428

Date: 2012-07-06 17:12:45 +0200
From: @njnes

fixed crash, index updates do not exist for single column indices

Comment 17431

Date: 2012-07-07 05:38:18 +0200
From: Benjie Chen <>

Similar to 3114, applied changeset to last stable release code, confirming the bug has been solved. Thanks.

(In reply to comment 4)

Changeset 03516346b4d5 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=03516346b4d5

Changeset description:

fixed bugs 3113 and 3114

3113: single column indices aren't stored, ie don't have updates

3114: properly use the update 'column expression' instead of the original
column expression.

Comment 17478

Date: 2012-07-17 13:46:40 +0200
From: @grobian

Fix delivered in Jul2012 release