COMMENT ON TABLE abc IS NULL invalidly sets the remark column to null where remark column is defined as NOT NULLable

[monetdb-team]

Date: 2018-08-23 15:54:34 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)
CC: dspea, essaytrophy, mauvetreeofficial, serinalevis9, syzek, yadavtheddie007

Last updated: 2020-05-24 09:58:00 +0200

Comment 26602

Date: 2018-08-23 15:54:34 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

COMMENT ON TABLE abc IS NULL;
invalidly sets the remark column to null where remark column is defined as NOT NULLable.
Expected that this command should delete the comment record from the sys.comments table.

Reproducible: Always

Steps to Reproduce:

select id, remark from comments;
-- shows 0 rows, initially there are no comments set

create table abc (nr int);
comment on table abc is 'abc_rem';
select id, remark from comments;
-- shows 1 row as expected

comment on table abc is null;
select id, remark from comments;
-- shows 1 row but with remark column being null. this is not expected and not allowed as the remark column is defined as NOT NULL

-- show that the comment column id and remark are both set to NOT NULL
select number, name, type, type_digits, "null" from _columns where table_id in (select id from _tables where name = 'comments' and system);
-- \dt comments

comment on table abc is '';
select id, remark from comments;
-- shows 0 rows, the row is now deleted as expected

comment on table abc is null;
select id, remark from comments;
-- shows 1 row! So it is created again but with remark column being null

drop table abc;
select id, remark from comments;
-- shows 0 rows, the row is deleted as expected (implicitly deleted by the drop table statement)

Expected Results:

It should not be possible to set a non nullable column value to null.

COMMENT ON TABLE abc IS NULL; should remove the record from the sys.comments table, similar as COMMENT ON TABLE abc IS ''; command.

Comment 26603

Date: 2018-08-23 16:10:21 +0200
From: MonetDB Mercurial Repository <>

Changeset ff7d5cb5122a made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test and desired output for bug #6639

Comment 26604

Date: 2018-08-23 19:03:30 +0200
From: MonetDB Mercurial Repository <>

Changeset 887e8dc60816 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Adding checks for violations of NOT NULLable columns containing NULL (as can happen in sys.comments.remark, see bug #6639)
Also add dedicated sql file to check data integrity on geom table "sys"."spatial_ref_sys"
Also add dedicated sql file to check data integrity on bam schema tables

Comment 26605

Date: 2018-08-27 13:27:29 +0200
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

Don't add a NULL to the sys.comments column.
This fixes bug #6639.