-
Notifications
You must be signed in to change notification settings - Fork 5.8k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Update with Sub-Query got wrong result #24855
Comments
But I test the SQL in PostgreSQL 10, same behavers as TiDB. Shall we follow MySQL's logic or just keep the current logic? |
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:
TiDB master 04cb5e9, PostgreSQL 13.0, Oracle 19c:
If we want to be consistent with MySQL, we need to get |
@time-and-fate yes, it is hard to fix. I think we should discuss about is this mysql feature we should follow. |
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. |
I think we should document this at docs. Seems some ORM framework depends on this feature. |
Bug Report
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
In MySQL
3. What did you see instead (Required)
For TiDB
4. What is your TiDB version? (Required)
The text was updated successfully, but these errors were encountered: