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