You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
Start MonetDB 5 server v11.20.0 (compiled from default on 19 sept)
run the below SQL statements.
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.
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:
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.
The text was updated successfully, but these errors were encountered: