Re: BUG #16150: UPDATE set NULL value in non-null columns

On Thu, 5 Dec 2019 at 14:48, PG Bug reporting form
<[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      16150
> Logged by:          Maxim Votyakov
> Email address:      [email protected]
> 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