alter table alter_not_null_test alter test set NOT NULL; is accepted when test contains null. This used to be restricted but isn't anymore

[monetdb-team]

Date: 2014-09-19 11:57:22 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2014-10-31 14:14:14 +0100

Comment 20164

Date: 2014-09-19 11:57:22 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

alter table alter_not_null_test alter test set not null; is accepted when test contains null.
In previous version (MonetDB 5 server v11.17.21 "Jan2014-SP3") this was restricted correctly, but in the default version this is now accepted incorrectly.

Reproducible: Always

Steps to Reproduce:

1. Start MonetDB 5 server v11.20.0 (compiled from default on 19 sept)
2. run the below SQL statements.
3. run the below SQL statements:
   create table alter_not_null_test (test int not null);
   select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
   -- null is marked as false
   insert into alter_not_null_test values (null);
   -- should report: Error: INSERT INTO: NOT NULL constraint violated for column alter_not_null_test.test SQLState: M0M29 ErrorCode: 0

alter table alter_not_null_test alter test set null;
select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
-- null is now marked as true
insert into alter_not_null_test values (null);
insert into alter_not_null_test values (null);
select * from alter_not_null_test;
-- should list 2 rows, both null

alter table alter_not_null_test alter test set not null;
-- this should fail as there are rows that have null for column: test
select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
-- null is now marked as false

insert into alter_not_null_test values (null);
-- should report: Error: INSERT INTO: NOT NULL constraint violated for column alter_not_null_test.test SQLState: M0M29 ErrorCode: 0
select * from alter_not_null_test;
-- should list 2 rows, both null

drop table alter_not_null_test;

Actual Results:

bash-4.2$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create table alter_not_null_test (test int not null);
operation successful (120.038ms)
sql>select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
| id | name | type | type_ | type_ | table | defau | null | number | storag |
: : : : digit : scale : id : lt : : : e :
: : : : s : : : : : : :
+======+======+======+=======+=======+=======+=======+=======+========+========+
| 7348 | test | int | 32 | 0 | 7349 | null | false | 0 | null |
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
1 tuple (5.721ms)
sql>insert into alter_not_null_test values (null);
INSERT INTO: NOT NULL constraint violated for column alter_not_null_test.test
sql>alter table alter_not_null_test alter test set null;
operation successful (9.936ms)
sql>select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
| id | name | type | type | type_ | table | defau | null | number | storag |
: : : : digit : scale : id : lt : : : e :
: : : : s : : : : : : :
+======+======+======+=======+=======+=======+=======+=======+========+========+
| 7348 | test | int | 32 | 0 | 7349 | null | true | 0 | null |
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
1 tuple (7.098ms)
sql>insert into alter_not_null_test values (null);
1 affected rows (2.631ms)
sql>insert into alter_not_null_test values (null);
1 affected rows (5.385ms)
sql>select * from alter_not_null_test;
+------+
| test |
+======+
| null |
| null |
+------+
2 tuples (2.481ms)
sql>alter table alter_not_null_test alter test set not null;
operation successful (4.444ms)
sql>select * from sys.columns where name = 'test' and table_id in (select id from sys.tables where name = 'alter_not_null_test');
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
| id | name | type | type | type_ | table | defau | null | number | storag |
: : : : digit : scale : _id : lt : : : e :
: : : : s : : : : : : :
+======+======+======+=======+=======+=======+=======+=======+========+========+
| 7348 | test | int | 32 | 0 | 7349 | null | false | 0 | null |
+------+------+------+-------+-------+-------+-------+-------+--------+--------+
1 tuple (11.232ms)
sql>insert into alter_not_null_test values (null);
INSERT INTO: NOT NULL constraint violated for column alter_not_null_test.test
sql>select * from alter_not_null_test;+------+
| test |
+======+
| null |
| null |
+------+
2 tuples (1.954ms)
sql>drop table alter_not_null_test;
operation successful (33.528ms)
sql>

Expected Results:

sql>alter table alter_not_null_test alter test set not null;
Error: ALTER TABLE: NOT NULL constraint violated for column alter_not_null_test.test

The statement:

alter table alter_not_null_test alter test set not null;
-- this should fail as there are rows that have null for column: test

should fail, like it does in: MonetDB 5 server v11.17.21 "Jan2014-SP3".

So something is changed in the default branch which breaks this behavior. The new behavior is incorrect and should be fixed.

Comment 20185

Date: 2014-09-21 20:12:10 +0200
From: @njnes

fixed a bug in the check for null values

Comment 20360

Date: 2014-10-31 14:14:14 +0100
From: @sjoerdmullender

Oct2014 has been released.