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)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.35
• OS: Any
• CPU Architecture: Any

[11 Dec 2023 6:42] Huaxiong Song

Description:
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.