Обсуждение: 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