Thread: BUG #16150: UPDATE set NULL value in non-null columns
The following bug has been logged on the website: Bug reference: 16150 Logged by: Maxim Votyakov Email address: maximaximax@gmail.com PostgreSQL version: 12.0 Operating system: Windows Description: Hi guys Today I found a big problem, I can write a valid UPDATE which put NULL value in non-null column. Let's create a domain, two tables and insert 1 row into them: create domain d_text_not_null as text default '' not null; create table person (name d_text_not_null); create table town (name d_text_not_null); insert into person(name) values ('test'); insert into town(name) values ('test2'); select * from person; -- 1 row with value test Now let's try to update person name to null. It failed and it is correct: update person set name = null; -- SQL Error [23502]: ERROR: domain d_text_not_null does not allow null values But now let's try to update person name to with sub-query. It updates a row without any problem: update person set name = (select name from town where name = 'wrong'); -- 1 row updated Let's check data and see the result: select * from person; -- 1 row with value NULL in non-null column !!! I think it is very dangerous and important bug and it has to be fixed to not damage data in databases - if I backup/restore such tables they become empty.
On Thu, 5 Dec 2019 at 14:48, PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 16150 > Logged by: Maxim Votyakov > Email address: maximaximax@gmail.com > PostgreSQL version: 12.0 > Operating system: Windows > Description: > > Hi guys > > Today I found a big problem, I can write a valid UPDATE which put NULL value > in non-null column. Thanks for reporting this. I am able to reproduce it and it looks like a bug. > > Let's create a domain, two tables and insert 1 row into them: > > create domain d_text_not_null as text default '' not null; > > create table person (name d_text_not_null); > create table town (name d_text_not_null); > > insert into person(name) values ('test'); > insert into town(name) values ('test2'); > > select * from person; -- 1 row with value test > > Now let's try to update person name to null. It failed and it is correct: > > update person set name = null; -- SQL Error [23502]: ERROR: domain > d_text_not_null does not allow null values Here, to validate, we are calling ExecEvalConstraintNotNull so it is not allowing not_null. > > But now let's try to update person name to with sub-query. It updates a row > without any problem: > > update person set name = (select name from town where name = 'wrong'); -- 1 > row updated Here, when we are updating a column by sub-query, we are not calling ExecEvalConstraintNotNull to validate. I am debugging this and will try to write a fix. Thanks and Regards Mahendra Thalor EnterpriseDB: http://www.enterprisedb.com
PG Bug reporting form <noreply@postgresql.org> writes: > Today I found a big problem, I can write a valid UPDATE which put NULL value > in non-null column. You have rediscovered the scenario explained in the "Notes" section of the CREATE DOMAIN reference page [1]. As stated there, we consider that defining a domain with a NOT NULL constraint (or a CHECK that will fail for nulls) is a bad idea, so we're not that excited about trying to bend the datatype semantics to the extent that would be needed to make this problem go away. There are only two possible fixes: (1) throw an error if a sub-select or outer join produces a null in a column of such a domain type, or (2) regard the output column of such a query as not being of the domain type anymore, but its base type. Neither of these are attractive, either from a functionality or performance standpoint. So it's hard to conclude anything except that the SQL committee didn't think this through very well. regards, tom lane [1] https://www.postgresql.org/docs/current/sql-createdomain.html