NOT NULL constraint can't be applied after deleting rows with null values

[monetdb-team]

Date: 2013-12-02 16:56:25 +0100
From: Klaudiusz <>
To: SQL devs <>
Version: 11.15.17 (Feb2013-SP5)
CC: @njnes

Last updated: 2014-02-20 15:02:52 +0100

Comment 19373

Date: 2013-12-02 16:56:25 +0100
From: Klaudiusz <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.57 Safari/537.36
Build Identifier:

I can't apply NOT NULL constraint if there were previously null values in this table.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE foo (i INT);
INSERT INTO foo (i) VALUES (NULL);
DELETE from foo where i IS NULL;
ALTER TABLE foo ALTER COLUMN i SET NOT NULL;

Actual Results:

ALTER TABLE: NOT NULL constraint violated for column foo.i

Expected Results:

operation successful

MonetDB 5 server v11.15.20 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 252.2GiB available memory, 32 available cpu cores
Libraries:
libpcre: 7.8 2008-09-05 (compiled with 7.8)
openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010)
libxml2: 2.7.6 (compiled with 2.7.6)
Compiled by: -@- (x86_64-unknown-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 19391

Date: 2013-12-04 11:13:43 +0100
From: MonetDB Mercurial Repository <>

Changeset 8e8a60bd9949 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=8e8a60bd9949

Changeset description:

fixed bug #3403, ie check for NULL's on the real column not only on the
persistent column part (ie not looking at deletes and updates).

Comment 19392

Date: 2013-12-04 11:23:02 +0100
From: MonetDB Mercurial Repository <>

Changeset ff6511d8ac0c 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=ff6511d8ac0c

Changeset description:

added test for bug #3403

Comment 19609

Date: 2014-02-20 15:02:52 +0100
From: @sjoerdmullender

Jan2014 has been released.