Bug #113387 update with 'and' 'or' causes unexpected behavior
Submitted: 11 Dec 2023 6:42 Modified: 11 Dec 2023 11:31
Reporter: Huaxiong Song (OCA) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[11 Dec 2023 6:42] Huaxiong Song
Update multiple tables at the same time, but incorrectly add 'and' or 'or', no error is reported, and the data is updated to unexpected values.

How to repeat:
Run mysql-test with the case:

create table t1(c1 int);

create table t2(c2 int);

insert into t1 values(5),(5),(5);

insert into t2 values(5),(5),(5);

# Update with 'and', no error happens.
update t1, t2 set t1.c1=10 and t2.c2=10;

select * from t1;
select * from t2;

# Re-init
update t1 set c1=5;
update t2 set c2=5;

# Update with 'or', no error happens
update t1, t2 set t1.c1=10 or t2.c2=10;

select * from t1;
select * from t2;

# Cleanup
drop table t1, t2;

Suggested fix:
Reject this syntax to avoid unexpected updates.
[11 Dec 2023 6:43] Huaxiong Song
MySQL Version is 8.0.35
[11 Dec 2023 11:31] MySQL Verification Team
Hi Mr. Song,

Thank you for your bug report.

However, SQL standard allows boolean operator to be used in any expression, including the SET expression as well.

MySQL strictly follows SQL Standard 2016, where it is stipulated. It is specified in most chapters of the Foundation, with most of the references in the chapter 4 and 14.

It is up to the query designer to create DMLs correctly.

Not a bug.