Can not swap values by a multi-tables update

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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;

2. What did you expect to see? (Required)

The values of t1.c_str and t2.c_str should be swapped in the above case, that is, the last selection should return an empty set.

3. What did you see instead (Required)

mysql> select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
+-------+-------+
| c_str | c_str |
+-------+-------+
| Alice | Bob   |
+-------+-------+
1 row in set (0.06 sec)

mysql> update t1, t2 set t1.c_str = t2.c_str, t2.c_str = t1.c_str where t1.c_str <= t2.c_str;
Query OK, 1 row affected (0.06 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
+-------+-------+
| c_str | c_str |
+-------+-------+
| Bob   | Bob   |
+-------+-------+
1 row in set (0.05 sec)

4. What is your TiDB version? (Required)

master @ 8978773
release-4.0 @ 3948c7b

[zyguan]

/assign @zz-jason

[cfzjywxk]

The MySQL behavior is explained in link, TiDB implementation is different from MySQL.

[cfzjywxk]

MySQL behaviors are different for single and multiple tables, the TiDB behavior is more reasonable, I think we can document about this.

[ghost]

I believe the SQL standards do talk about what the correct behavior is here. I am not sure if MySQL is necessarily correct, but if TiDB differs, it should be defendable if it is at least standard.

[fzhedu]

I verify this in mysql version 8.0.19, it actually swaps two values from different tables. Should TiDB have the same behavior? @SunRunAway @cfzjywxk @nullnotnil

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t1 (c_str varchar(40));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (c_str varchar(40));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('Alice');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values ('Bob');
Query OK, 1 row affected (0.00 sec)

mysql> select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
+-------+-------+
| c_str | c_str |
+-------+-------+
| Alice | Bob   |
+-------+-------+
1 row in set (0.00 sec)

mysql> update t1, t2 set t1.c_str = t2.c_str, t2.c_str = t1.c_str where t1.c_str <= t2.c_str;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select t1.c_str, t2.c_str from t1, t2 where t1.c_str <= t2.c_str;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

[fzhedu]

Yes, this is a standard behaviour in MySQL, but not only in MySQL, but most relational databases.

responsed from Mysql comminuty
https://bugs.mysql.com/bug.php?id=100527&thanks=2&notify=67
@cfzjywxk @nullnotnil @SunRunAway
So we should recovery the behavior.

[SunRunAway]

I tried on SQLite3

sqlite> create table t(a int, b int);
sqlite> insert into t values(1, 2);
sqlite> select * from t;
1|2
sqlite> update t set a=b, b=a;
sqlite> select * from t;
2|1

[fzhedu]

PG

test=# create table t (a int, b int);
CREATE TABLE
test=# insert into t values (1,3);
INSERT 0 1
test=# select * from t;
 1 | 3

test=# update t set a=b, b=a;
UPDATE 1
test=# select * from t;
 3 | 1

PG does not supprot update more than one tables in one statement:

test=# update t1, t2 set t1.a=t2.a, t2.a=t1.a;
ERROR:  syntax error at or near ","
LINE 1: update t1, t2 set t1.a=t2.a, t2.a=t1.a;

[zyguan]

Oracle http://sqlfiddle.com/#!4/794de/1/0

[fzhedu]

compared Mysql to Oracle, PG and sqlLite, only mysql does not support swaping two columns. We can verify this on http://sqlfiddle.com/#!4/794de/1/0

[ghost]

https://bugs.mysql.com/bug.php?id=100527&thanks=2&notify=67
@cfzjywxk @nullnotnil @SunRunAway
So we should recovery the behavior.

I think it is better to have the standard behavior here.

If it turns out users depend on the (incorrect) MySQL behavior, we can add an option to permit this behavior. But to keep the options to a minimum: change it first, add the option if it becomes required.

[SunRunAway]

I think it is better to have the standard behavior here.
change it first, add the option if it becomes required.

Agree. Let's resolve this issue as @nullnotnil said. That is, TiDB swaps two values from one table if the query is update t set t.a=t.b, t.b=t.a;

cc @cfzjywxk, @lzmhhh123, @fzhedu

[lzmhhh123]

/cc @dyzsr

[dyzsr]

/assign

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions