insert into table with two foreign keys crashes mserver5

[monetdb-team]

Date: 2012-07-19 09:51:41 +0200
From: @bartscheers
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @njnes

Last updated: 2013-01-22 09:29:16 +0100

Comment 17512

Date: 2012-07-19 09:51:41 +0200
From: @bartscheers

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20100101 Firefox/13.0.1
Build Identifier:

Table t1 has fk to t2 and another fk to t3; t2 has a fk to t1;
We want to do an insert into t1 where the inserts are based on a select from t2;
If we select a fk column that needs to be inserted into t1 that has the column and fk this particular insert causes a crash.

Reproducible: Always

Steps to Reproduce:

see attachments:

1. fk_insert.sql contains the statements
2. run fk_insert.sh

Actual Results:

stopping database 'fk_insert'... done
destroyed database: fk_insert
created database in maintenance mode: fk_insert
starting database 'fk_insert'... done
taken database out of maintenance mode: fk_insert
MAPI = (monetdb) /tmp/.s.monetdb.60000
ACTION= read_line
QUERY = create table t3
(id int auto_increment
,text varchar(8)
,primary key (id)
)
;

     create table t2
       (id int auto_increment
       ,ds int not null
       ,ra double not null
       ,primary key (id)
       ,foreign key (ds) references t3 (id)
       )
     ;
     
     create table t1 
       (id int auto_increment
       ,runcat int
       ,ds int not null
       ,ra double default 0
       ,primary key (id)
       ,foreign key (runcat) references t2 (id)
       ,foreign key (ds) references t3 (id)
       )
     ;
     
     insert into t3 (text) values ('test');
     insert into t2 (ds,ra) select id,20 from t3;
     
     --This one does not work:
     insert into t1 (runcat,ds,ra) select id,ds,0 from t2;
     
     --This one does work:
     --insert into t1 (runcat,ds,ra) select id,1,0 from t2;

ERROR = !Connection terminated

Expected Results:

Something like :
Commenting and uncommenting the last two sql lines, resp.

stopping database 'fk_insert'... FAILED
stop: database is not running: fk_insert
destroyed database: fk_insert
created database in maintenance mode: fk_insert
starting database 'fk_insert'... done
taken database out of maintenance mode: fk_insert
operation successful
operation successful
operation successful
1 affected row, last generated key: 1
1 affected row
1 affected row

Comment 17513

Date: 2012-07-19 09:52:51 +0200
From: @bartscheers

Created attachment 133
run script

Attached file: fk_insert.sh (application/x-shellscript, 177 bytes)
Description: run script

Comment 17514

Date: 2012-07-19 09:53:14 +0200
From: @bartscheers

Created attachment 134
table definitions & insert statements

Attached file: fk_insert.sql (text/x-sql, 686 bytes)
Description: table definitions & insert statements

Comment 17683

Date: 2012-08-24 14:55:59 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18017

Date: 2012-11-26 19:57:19 +0100
From: @njnes

added test insert_into_table_with_2_foreignkeys.Bug-3124.sql

Comment 18018

Date: 2012-11-26 19:58:23 +0100
From: @njnes

fixed by checking if the index-column still exists in the outer most relation (part just under the join).

Comment 18019

Date: 2012-11-26 21:08:28 +0100
From: @njnes

Changeset 54d040897b07 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=54d040897b07

Changeset description:

fixed bug #3124
prevent usage of idx when its not part of the current relation

Comment 18378

Date: 2013-01-22 09:29:16 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.