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

From: Mahendra Singh <mahi6run(at)gmail(dot)com>
To: maximaximax(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16150: UPDATE set NULL value in non-null columns
Date: 2019-12-05 11:35:49
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

On Thu, 5 Dec 2019 at 14:48, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
> Bug reference: 16150
> Logged by: Maxim Votyakov
> Email address: maximaximax(at)gmail(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-12-05 12:28:51 BUG #16152: postgresql10-plpython-10.11-2PGDG.rhel7.x86_64 requires an unexistant package
Previous Message RideNext 2019-12-05 10:50:54 RE: Postgres takes more than 6 minutes to come up during host/standby switch over