assertion failure when deleting rows from table to which a FK constraint is defined

[monetdb-team]

Date: 2014-10-31 13:34:28 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.3 (Oct2014)

Last updated: 2014-11-28 10:03:17 +0100

Comment 20328

Date: 2014-10-31 13:34:28 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

mserver5: gdk_bat.c:2950: BATassertHeadProps: Assertion `!b->H->nil || seennil' failed.
It happens with releases: MonetDB 5 server v11.19.3 "Oct2014" and MonetDB 5 server v11.20.0.

It does NOT happen with release: MonetDB 5 server v11.17.21 "Jan2014-SP3"

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (MonetDB 5 server v11.19.3 "Oct2014")
2. start mclient
3. execute SQL:
   CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
   CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);

-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
INSERT INTO PKTABLE VALUES (5, 10, 'Test5');

SELECT * FROM PKTABLE;

-- Insert successful rows into FK TABLE
INSERT INTO FKTABLE VALUES (1, 2, 4);
INSERT INTO FKTABLE VALUES (1, 3, 5);
INSERT INTO FKTABLE VALUES (2, 4, 8);
INSERT INTO FKTABLE VALUES (NULL, NULL, 0);

-- Check FKTABLE
SELECT * FROM FKTABLE;

-- Delete a row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;

DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;

Actual Results:

bash-4.2$ Oct2014b/bin/mserver5 -d10 --set mapi_port=41000
builtin opt gdk_dbpath = /ufs/dinther/Oct2014b/var/monetdb5/dbfarm/demo
builtin opt gdk_debug = 0
builtin opt gdk_vmtrim = no
builtin opt monet_prompt = >
builtin opt monet_daemon = no
builtin opt mapi_port = 50000
builtin opt mapi_open = false
builtin opt mapi_autosense = false
builtin opt sql_optimizer = default_pipe
builtin opt sql_debug = 0
cmdline opt mapi_port = 41000
cmdline opt gdk_debug = 10
MonetDB 5 server v11.19.3 "Oct2014"
Serving database 'demo', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked
Found 15.356 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:41000/
MonetDB/GIS module loaded
MonetDB/SQL module loaded

mserver5: gdk_bat.c:2950: BATassertHeadProps: Assertion `!b->H->nil || seennil' failed.
30319 Aborted Oct2014b/bin/mserver5 -d10 --set mapi_port=41000
bash-4.2$

bash-4.2$ mclient -p 41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.19.3 (Oct2014), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
operation successful (28.670ms)
sql>CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);
operation successful (6.080ms)
sql>INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
1 affected row (64.206ms)
sql>INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
1 affected row (4.726ms)
sql>INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
1 affected row (34.725ms)
sql>INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
1 affected row (2.567ms)
sql>SELECT * FROM PKTABLE;
+--------+--------+---------+
| ptest1 | ptest2 | ptest3 |
+========+========+=========+
| 1 | 2 | Test1 |
| 1 | 3 | Test1-2 |
| 2 | 4 | Test2 |
| 5 | 10 | Test5 |
+--------+--------+---------+
4 tuples (2.484ms)
sql>INSERT INTO FKTABLE VALUES (1, 2, 4);
1 affected row (22.689ms)
sql>INSERT INTO FKTABLE VALUES (1, 3, 5);
1 affected row (5.887ms)
sql>INSERT INTO FKTABLE VALUES (2, 4, 8);
1 affected row (4.403ms)
sql>INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
1 affected row (59.025ms)
sql>SELECT * FROM FKTABLE;
+--------+--------+--------+
| ftest1 | ftest2 | ftest3 |
+========+========+========+
| 1 | 2 | 4 |
| 1 | 3 | 5 |
| 2 | 4 | 8 |
| null | null | 0 |
+--------+--------+--------+
4 tuples (1.311ms)
sql>DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
sql>
bash-4.2$

Expected Results:

no assertion failure.
no termination of mserver5 process.

It is NOT happening with MonetDB 5 server v11.17.21 "Jan2014-SP3" release.
So something in the new Oct2014 release is broken.

Comment 20405

Date: 2014-10-31 14:54:00 +0100
From: MonetDB Mercurial Repository <>

Changeset f633127500eb made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f633127500eb

Changeset description:

Fix for bug #3612: nil property was incorrectly set.
In fact, if the input bat has a nil value, then the output bat doesn't
necessarily have a nil value.  If the value gets sign extended (in
this bug from int to lng and on to oid), the nil becomes something
totally different.

Comment 20468

Date: 2014-11-28 10:03:17 +0100
From: @sjoerdmullender

Oct2014-SP1 has been released.