Bug #105784 update a set not_null_column = null, not_null_column = 2 should not raise an err
Submitted: 3 Dec 2021 3:53 Modified: 3 Dec 2021 5:58
Reporter: Huaiyu Xu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0, 5.7 OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 2021 3:53] Huaiyu Xu
Description:
create table t(a int not null,b int);
insert into t values(1,1);
update t set a=null,a=2;

mysql> update t set a=null,a=2;
ERROR 1048 (23000): Column 'a' cannot be null

The assignment is evaluated from left to right, we can update `a` using `2` without raising the not-null error message.

How to repeat:
create table t(a int not null,b int);
insert into t values(1,1);
update t set a=null,a=2;

Suggested fix:
update t set a = 2 without raising an error
[3 Dec 2021 5:58] MySQL Verification Team
Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh
[3 Dec 2021 8:49] Roy Lyseng
Posted by developer:
 
According to SQL standard, a target column shall not occur more than once in the SET clause of an UPDATE statement.