Bug #98899 | UPDATE with JOIN and GREATEST sets wrong vaule | ||
---|---|---|---|
Submitted: | 10 Mar 2020 20:52 | Modified: | 11 Mar 2020 23:07 |
Reporter: | Stephen Sigwart | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 8.0.17 | OS: | MacOS (10.15.3) |
Assigned to: | CPU Architecture: | x86 |
[10 Mar 2020 20:52]
Stephen Sigwart
[11 Mar 2020 13:46]
MySQL Verification Team
Hi Mr. Sigwart, Thank you for your bug report. However, this is not a bug. You are updating table tmp1, which has only one row. Hence, JOINing this table with any other will produce only one row, which is the first one, since it is the first row, among all, that satisfies the condition. This is a consequence of how you have written your DML statement. Also, multi-table UPDATEs are functioning like this since their introduction, some 15 years ago. If you wish to JOIN with the other table, so that a larger values get into the result set, you should rewrite your query in a different manner. Like, with a nested query. Not a bug.
[11 Mar 2020 23:07]
Stephen Sigwart
Thank you, Sinisa. I figured that might be the case and already updated my query. I just didn't find anywhere in the documentation that explains this behavior.
[12 Mar 2020 13:06]
MySQL Verification Team
Hi Mr. Sigwart, You are welcome. You will not find the explanation in our Manual, but you will find it in any textbook on SQL.