UPDATE with correlated subquery causes assertion (or segfault)

[monetdb-team]

Date: 2018-01-24 15:02:36 +0100
From: @drstmane
To: SQL devs <>
Version: 11.27.11 (Jul2017-SP3)
CC: @njnes

Last updated: 2018-02-12 16:12:06 +0100

Comment 26108

Date: 2018-01-24 15:02:36 +0100
From: @drstmane

This script (DDL + update statement; no data required!):

========
start transaction;

CREATE TABLE "PRIMARY" (
"ID" integer NOT NULL,
"LEN" real NOT NULL,
CONSTRAINT "PK_PRIMARY" PRIMARY KEY ("ID")
);

CREATE TABLE "FOREIGN" (
"ID" integer NOT NULL,
"POS" real NOT NULL,
CONSTRAINT "FK_FOREIGN" FOREIGN KEY ("ID") REFERENCES "PRIMARY" ("ID")
);

alter table "FOREIGN" add column "RelPos" real default NULL;

update "FOREIGN"
set "RelPos" = "POS" / (
select "LEN"
from "PRIMARY"
where "PRIMARY"."ID" = "FOREIGN"."ID"
)
where "POS" between 0 and (
select "LEN"
from "PRIMARY"
where "PRIMARY"."ID" = "FOREIGN"."ID"
)
;

rollback;

causes:

========
could not find L10.LEN
FOREIGN.POS
L13.L13
FOREIGN.POS
L13.L13
mserver5: .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:575: exp_bin: Assertion `s' failed.

Thread 6 "mserver5" received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffd3cc2700 (LWP 30631)]
0x00007ffff44e99fb in raise () from /lib64/libc.so.6
(gdb) bt
0 0x00007ffff44e99fb in raise () from /lib64/libc.so.6
1 0x00007ffff44eb800 in abort () from /lib64/libc.so.6
2 0x00007ffff44e20da in __assert_fail_base () from /lib64/libc.so.6
3 0x00007ffff44e2152 in __assert_fail () from /lib64/libc.so.6
4 0x00007fffeef05b2b in exp_bin (be=0x7fffbc0cb1f0, e=0x7fffbc0d6830, left=0x7fffbc0dded0, right=0x7fffbc0de2b0, grp=0x0, ext=0x0, cnt=0x0, sel=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:575
5 0x00007fffeef055ac in exp_bin (be=0x7fffbc0cb1f0, e=0x7fffbc0d68e0, left=0x7fffbc0dded0, right=0x7fffbc0de2b0, grp=0x0, ext=0x0, cnt=0x0, sel=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:515
6 0x00007fffeef1021f in rel2bin_groupby (be=0x7fffbc0cb1f0, rel=0x7fffbc0d83d0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:2753
7 0x00007fffeef19bff in subrel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d83d0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4855
8 0x00007fffeef0ed36 in rel2bin_project (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50b0, refs=0x7fffbc0db1e0, topn=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:2465
9 0x00007fffeef19ba3 in subrel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50b0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4847
10 0x00007fffeef0ed36 in rel2bin_project (be=0x7fffbc0cb1f0, rel=0x7fffbc0d5730, refs=0x7fffbc0db1e0, topn=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:2465
11 0x00007fffeef19ba3 in subrel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d5730, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4847
12 0x00007fffeef1746a in rel2bin_update (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50e0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4286
13 0x00007fffeef19cca in subrel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50e0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4872
14 0x00007fffeef19e4b in _subrel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50e0, refs=0x7fffbc0db1e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4904
15 0x00007fffeef19f63 in output_rel_bin (be=0x7fffbc0cb1f0, rel=0x7fffbc0d50e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:4927
16 0x00007fffeef35270 in sql_relation2stmt (be=0x7fffbc0cb1f0, r=0x7fffbc0d50e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/sql_gencode.c:529
17 0x00007fffeef35479 in backend_dumpstmt (be=0x7fffbc0cb1f0, mb=0x7fffbc1346d0, r=0x7fffbc0d50e0, top=1, add_end=1, query=0x7fffbc104140 "update "FOREIGN"\n set "RelPos" = "POS" / (\n select "LEN"\n from "PRIMARY"\n where "PRIMARY"."ID" = "FOREIGN"."ID"\n )\n where "POS" between 0 and (\n select "LEN"\n from "PRIMARY"\n where "PRIMARY"."ID" = "F"...)
at .../Jul2017/source/MonetDB/sql/backends/monet5/sql_gencode.c:569
18 0x00007fffeef35cd8 in backend_dumpproc (be=0x7fffbc0cb1f0, c=0x7ffff0301338, cq=0x7fffbc104440, r=0x7fffbc0d50e0) at .../Jul2017/source/MonetDB/sql/backends/monet5/sql_gencode.c:696
19 0x00007fffeeef9584 in SQLparser (c=0x7ffff0301338) at .../Jul2017/source/MonetDB/sql/backends/monet5/sql_scenario.c:1189
20 0x00007ffff7a131ae in runPhase (c=0x7ffff0301338, phase=1) at .../Jul2017/source/MonetDB/monetdb5/mal/mal_scenario.c:506
21 0x00007ffff7a132f2 in runScenarioBody (c=0x7ffff0301338) at .../Jul2017/source/MonetDB/monetdb5/mal/mal_scenario.c:539
22 0x00007ffff7a13495 in runScenario (c=0x7ffff0301338) at .../Jul2017/source/MonetDB/monetdb5/mal/mal_scenario.c:568
23 0x00007ffff7a1504a in MSserveClient (dummy=0x7ffff0301338) at .../Jul2017/source/MonetDB/monetdb5/mal/mal_session.c:473
24 0x00007ffff7a14b3a in MSscheduleClient (command=0x7fffbc0008d0 "\360\256\f\274\377\177", challenge=0x7fffd3cc1d6b "clN31XaVW", fin=0x7fffbc002970, fout=0x7fffc8002b70, protocol=PROTOCOL_9, blocksize=8190, compute_column_widths=0) at .../Jul2017/source/MonetDB/monetdb5/mal/mal_session.c:364
25 0x00007ffff7ab41b1 in doChallenge (data=0x7fffc80008d0) at .../Jul2017/source/MonetDB/monetdb5/modules/mal/mal_mapi.c:273
26 0x00007ffff768c471 in thread_starter (arg=0x7fffc8004c60) at .../Jul2017/source/MonetDB/gdk/gdk_system.c:485
27 0x00007ffff488f36d in start_thread () from /lib64/libpthread.so.0
28 0x00007ffff45c3e1f in clone () from /lib64/libc.so.6
(gdb) up
1 0x00007ffff44eb800 in abort () from /lib64/libc.so.6
(gdb)
2 0x00007ffff44e20da in __assert_fail_base () from /lib64/libc.so.6
(gdb)
3 0x00007ffff44e2152 in __assert_fail () from /lib64/libc.so.6
(gdb)
4 0x00007fffeef05b2b in exp_bin (be=0x7fffbc0cb1f0, e=0x7fffbc0d6830, left=0x7fffbc0dded0, right=0x7fffbc0de2b0, grp=0x0, ext=0x0, cnt=0x0, sel=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:575
575 assert(s);
(gdb) li
570 s = stmt_project(be, ext, s);
571 if (!s && right) {
572 fprintf(stderr, "could not find %s.%s\n", (char*)e->l, (char*)e->r);
573 print_stmtlist(sql->sa, left);
574 print_stmtlist(sql->sa, right);
575 assert(s);
576 return NULL;
577 }
578 } break;
579 case e_cmp: {
(gdb) p be
$1 = (backend *) 0x7fffbc0cb1f0
(gdb) p *be
$2 = {console = 0, language = 83 'S', mvc = 0x7fffbc00ae20, out = 0x7fffc8002b70, output_format = OFMT_CSV, client = 0x7ffff0301338, mb = 0x7fffbc1346d0, mvc_var = 5, vtop = 1, q = 0x7fffbc104440}
(gdb) p ext
$3 = (stmt *) 0x0
(gdb) p *ext
Cannot access memory at address 0x0
(gdb) p right
$4 = (stmt *) 0x7fffbc0de2b0
(gdb) p *right
$5 = {type = st_list, op1 = 0x0, op2 = 0x0, op3 = 0x0, op4 = {aval = 0x7fffbc0de240, lval = 0x7fffbc0de240, stval = 0x7fffbc0de240, cval = 0x7fffbc0de240, idxval = 0x7fffbc0de240, tval = 0x7fffbc0de240, typeval = {type = 0x7fffbc0de240, digits = 3183328701, scale = 3183328701}, aggrval = 0x7fffbc0de240, funcval = 0x7fffbc0de240, rel = 0x7fffbc0de240}, nrcols = 0 '\000', key = 1 '\001', aggr = 0 '\000',
partition = 0 '\000', flag = 0, nr = 0, tname = 0x0, cname = 0x0, q = 0xbdbdbdbdbdbdbdbd}
(gdb) up
5 0x00007fffeef055ac in exp_bin (be=0x7fffbc0cb1f0, e=0x7fffbc0d68e0, left=0x7fffbc0dded0, right=0x7fffbc0de2b0, grp=0x0, ext=0x0, cnt=0x0, sel=0x0) at .../Jul2017/source/MonetDB/sql/backends/monet5/rel_bin.c:515
515 as = exp_bin(be, at, left, right, NULL, NULL, NULL, sel);
(gdb) li
510 list *l = sa_list(sql->sa);
511
512 for (en = attr->h; en; en = en->next) {
513 sql_exp at = en->data;
514
515 as = exp_bin(be, at, left, right, NULL, NULL, NULL, sel);
516
517 if (as && as->nrcols <= 0 && left)
518 as = stmt_const(be, bin_first_column(be, left), as);
519 / insert single value into a column */
(gdb) p be
$6 = (backend *) 0x7fffbc0cb1f0
(gdb) p *be
$7 = {console = 0, language = 83 'S', mvc = 0x7fffbc00ae20, out = 0x7fffc8002b70, output_format = OFMT_CSV, client = 0x7ffff0301338, mb = 0x7fffbc1346d0, mvc_var = 5, vtop = 1, q = 0x7fffbc104440}
(gdb) p at
$8 = (sql_exp *) 0x7fffbc0d6830
(gdb) p *at
$9 = {type = e_column, name = 0x7fffbc0d6890 "LEN", rname = 0x7fffbc0d68a0 "PRIMARY", l = 0x7fffbc0d6740, r = 0x7fffbc0d0590, f = 0x0, flag = 64, card = 3 '\003', tpe = {type = 0x1931940, digits = 24, scale = 0}, used = 0, p = 0x0}
(gdb) p left
$10 = (stmt *) 0x7fffbc0dded0
(gdb) p *left
$11 = {type = st_list, op1 = 0x0, op2 = 0x0, op3 = 0x0, op4 = {aval = 0x7fffbc0dde60, lval = 0x7fffbc0dde60, stval = 0x7fffbc0dde60, cval = 0x7fffbc0dde60, idxval = 0x7fffbc0dde60, tval = 0x7fffbc0dde60, typeval = {type = 0x7fffbc0dde60, digits = 3183328701, scale = 3183328701}, aggrval = 0x7fffbc0dde60, funcval = 0x7fffbc0dde60, rel = 0x7fffbc0dde60}, nrcols = 1 '\001', key = 0 '\000', aggr = 0 '\000',
partition = 0 '\000', flag = 0, nr = 0, tname = 0x0, cname = 0x0, q = 0xbdbdbdbdbdbdbdbd}
(gdb) p right
$12 = (stmt *) 0x7fffbc0de2b0
(gdb) p *right
$13 = {type = st_list, op1 = 0x0, op2 = 0x0, op3 = 0x0, op4 = {aval = 0x7fffbc0de240, lval = 0x7fffbc0de240, stval = 0x7fffbc0de240, cval = 0x7fffbc0de240, idxval = 0x7fffbc0de240, tval = 0x7fffbc0de240, typeval = {type = 0x7fffbc0de240, digits = 3183328701, scale = 3183328701}, aggrval = 0x7fffbc0de240, funcval = 0x7fffbc0de240, rel = 0x7fffbc0de240}, nrcols = 0 '\000', key = 1 '\001', aggr = 0 '\000',
partition = 0 '\000', flag = 0, nr = 0, tname = 0x0, cname = 0x0, q = 0xbdbdbdbdbdbdbdbd}
(gdb) p sel
$14 = (stmt *) 0x0
(gdb)

Comment 26109

Date: 2018-01-24 15:07:32 +0100
From: @drstmane

ps:
It's actually the correlated subquery in the WHERE clause that causes the problem.
The correlated subquery in the SET clause appears to work without the correlated subquery in the WHERE clause.
The assertion / crash also occurs with only the correlated subquery in the WHERE clause, i.e., without the correlated subquery in the SET clause.

Comment 26113

Date: 2018-01-24 18:34:32 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=49c6abdb8aa5

Changeset description:

added test for Bug #6520 "UPDATE with correlated subquery causes assertion (or segfault)"

Comment 26130

Date: 2018-01-27 14:12:38 +0100
From: MonetDB Mercurial Repository <>

Changeset 1a9b08289b80 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1a9b08289b80

Changeset description:

fix bug #6520, add projection in update when needed

Comment 26131

Date: 2018-01-27 14:13:07 +0100
From: @njnes

fixed by adding proper projection