Update with Sub-Query got wrong result

[blacktear23]

Bug Report

1. Minimal reproduce step (Required)

create table users(id int primary key, company_id int, name varchar(255), updated_at datetime);
create table companies(id int primary key, name varchar(255));
insert into companies values(14, 'Company 1 id 14');
insert into companies values(15, 'Company 2 id 15');
insert into users values(239, 15, 'Company 2 id 15', '2021-03-01 12:12:12.987');
UPDATE `users` SET `company_id`=14,`name`=(SELECT `name` FROM `companies` WHERE companies.id = users.company_id),`updated_at`='2021-04-27 10:28:31.459' WHERE `id` = 239;

select * from users;

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

In MySQL

mysql> select * from users;
+-----+------------+-----------------+---------------------+
| id  | company_id | name            | updated_at          |
+-----+------------+-----------------+---------------------+
| 239 |         14 | Company 1 id 14 | 2021-04-27 10:28:31 |
+-----+------------+-----------------+---------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

For TiDB

mysql> select * from users;
+-----+------------+-----------------+---------------------+
| id  | company_id | name            | updated_at          |
+-----+------------+-----------------+---------------------+
| 239 |         14 | Company 2 id 15 | 2021-04-27 10:28:31 |
+-----+------------+-----------------+---------------------+
1 row in set (0.02 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v5.1.0-alpha
Edition: Community
Git Commit Hash: 19e100c27a37601d674df625034ebddb5196a299
Git Branch: master
UTC Build Time: 2021-05-24 03:49:21
GoVersion: go1.16.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[blacktear23]

But I test the SQL in PostgreSQL 10, same behavers as TiDB. Shall we follow MySQL's logic or just keep the current logic?

[time-and-fate]

Seems that it will be hard to fix this issue.

Consider this case:

create table t(a int, b int, c int);
create table t1(a int, b int);
create table t2(a int, b int);
insert into t values(1, 1, 1);
insert into t1 values(1, 3), (2, 4);
insert into t2 values(1, 5), (3, 6), (4, 7);
update t set a=2, b=(select b from t1 where t1.a = t.a), c=(select b from t2 where t2.a = t.b) where a = 1;

MySQL 8.0.25, 5.7.32:

> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    4 |    7 |
+------+------+------+

TiDB master 04cb5e9, PostgreSQL 13.0, Oracle 19c:

>  select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    3 |    5 |
+------+------+------+

If we want to be consistent with MySQL, we need to get t.a updated before running (select b from t1 where t1.a = t.a) and get t.b updated before running (select b from t2 where t2.a = t.b), and I think this is quite different from our current implementation.

[blacktear23]

@time-and-fate yes, it is hard to fix. I think we should discuss about is this mysql feature we should follow.

[time-and-fate]

I tried to find relevant descriptions from docs of other databases and the SQL standard.

In MySQL's doc on UPDATE statement, it says we should use the updated value of previous assignments, but it doesn't explicitly say it's also true for subquery, and we have also decided not to follow this behavior in #19137.

Postgres explicitly say that we should use the old values here in its doc on UPDATE statement

The SQL standard classifies this kind of usage of subquery in the UPDATE statement as an optional feature, but it also says the expression for assignment should be evaluated before any row of the target table gets updated.

[blacktear23]

I think we should document this at docs. Seems some ORM framework depends on this feature.