Updating a column with another column is not compatible with MySQL 8.0

[dlyixue]

Bug Report

When I update a column with another column, I will get old values in TiDB, but I will get new values in MySQL.
It is not compatible with MYSQL 8.0.
Thank for your reply.
Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0 (c0 int, c1 int);
INSERT INTO t0 VALUES (1, 2);
INSERT INTO t0 VALUES (3, 4);
INSERT INTO t0 VALUES (5, 6);
SELECT * FROM t0;
UPDATE t0 SET c1 = 123, c0 = c1;
SELECT * FROM t0;

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

In MySQL

mysql> SELECT * FROM t0;
+------+------+
| c0   | c1   |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE t0 SET c1 = 123, c0 = c1;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM t0;
+------+------+
| c0   | c1   |
+------+------+
|  123 |  123 |
|  123 |  123 |
|  123 |  123 |
+------+------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

In TiDB

mysql> SELECT * FROM t0;
+------+------+
| c0   | c1   |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE t0 SET c1 = 123, c0 = c1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM t0;
+------+------+
| c0   | c1   |
+------+------+
|    2 |  123 |
|    4 |  123 |
|    6 |  123 |
+------+------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:51:57
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[zanmato1984]

MySQL has this behavior explicitly documented as:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Ref: https://dev.mysql.com/doc/refman/8.4/en/update.html

[zanmato1984]

Changing sig.