Bug #100527 it is different when swaping column values from a table or different tables
Submitted: 14 Aug 2020 8:07 Modified: 26 Aug 2020 2:47
Reporter: river fang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[14 Aug 2020 8:07] river fang
Description:
update t1, t2 set t1.c_str = t2.c_str, t2.c_str = t1.c_str;
results: swap succesfully.

update t set t.a_str = t.c_str, t.c_str = t.a_str;
results: swap faild, t.a_str and t.c_str both equal to c_str.

such swap function works differently in obove two cases, is it necessary to keep the same for the two cases? or is it a standard behavior in mysql?

How to repeat:
drop table if exists t1, t2;
create table t1 (c_str varchar(40));
create table t2 (c_str varchar(40));
insert into t1 values ('Alice');
insert into t2 values ('Bob');
select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
update t1, t2 set t1.c_str = t2.c_str, t2.c_str = t1.c_str where t1.c_str <= t2.c_str;
select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
[14 Aug 2020 13:53] MySQL Verification Team
Hi Mr. fang,

Thank you very much for your bug report.

However, this is not a bug.

When you use one table, try using user variables for swapping values. That is according to SQL standard.

Here are my results, with slightly changed test case:

--------------- Begin column values swap for the two tables ---------------------
c_str	c_str
Alice	Bob
c_str	c_str
Bob	Alice
----------------- End column values swap for the two tables    -------------------

--------------- Begin column values swap for the single table ---------------------
c_str	a_str
Alice	Bob
c_str	a_str
Bob	Alice
----------------- End column values swap for the single table    -------------------

In the second example, a table in question has two VARCHAR columns.

Not a bug.
[17 Aug 2020 3:33] river fang
Is it a standard in Mysql that swaping column values from a table or different tables works differently?
Because we consider whether our product should keep the same behavior with Mysql.
[17 Aug 2020 12:17] MySQL Verification Team
Yes, this is a standard behaviour in MySQL, but not only in MySQL, but most relational databases.
[24 Aug 2020 3:36] river fang
http://sqlfiddle.com/#!18/794dec/1

We found Mysql is different from other RDBMS. You can try it on the above site.
[26 Aug 2020 2:47] river fang
It is not a standard behavior for many other RDBMS, like Oracle, SQLlite and PG, except for Mysql.