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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0, 5.7
• OS: Any
• 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.