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 (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
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.
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)
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.
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.
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)
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:
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)
Comment 17478
Date: 2012-07-17 13:46:40 +0200
From: @grobian
Fix delivered in Jul2012 release
The text was updated successfully, but these errors were encountered: