Bug #101925 Inconsistent behavior in multi-update

• Submitted: 9 Dec 2020 9:34
• Modified: 9 Dec 2020 14:43
• Reporter: Yan Dong
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: MacOS
• CPU Architecture: x86
• Tags: UPDATE

[9 Dec 2020 9:34] Yan Dong

Description:
Behaviors of multi-update are inconsistent.

create table t1 (a int);
drop table if exists t2;
create table t2 (b int);
insert into t1 values (1);
insert into t2 values (2);

# This query can swap the values

update t1, t2 set a=b, b=a where a < b;
select * from t1, t2;
+---+---+
| a | b |
+---+---+
| 2 | 1 |
+---+---+

# While this one can not

update t1, t2 set a=b, b=a;
select * from t1, t2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+

How to repeat:
drop table if exists t1;
create table t1 (a int);
drop table if exists t2;
create table t2 (b int);
insert into t1 values (1);
insert into t2 values (2);

update t1, t2 set a=b, b=a where a < b;
select * from t1, t2;

update t1, t2 set a=b, b=a;
select * from t1, t2;

Suggested fix:
Support value swapping under any conditions in multi-update.

[9 Dec 2020 14:43] MySQL Verification Team

Hi Mr. Dong,

Thank you for your bug report.

However, this is not a bug.

Your second DML statement, unlike the first one, operates on the full Cartesian product of the who tables, since there are no common columns nor filtering conditions. With Cartesian products you can never know what results will you get.

Not a bug.