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:
None 
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
Description:
I'm not sure if this is a bug or intentional, but an UPDATE with a JOIN and GREATEST doesn't set the value I'd expect.  It seems to use the first row for the JOIN.

I've also duplicated on 5.6 (AWS Aurora).

How to repeat:
CREATE TEMPORARY TABLE tmp1 (id INT NOT NULL PRIMARY KEY, value INT NOT NULL);
CREATE TEMPORARY TABLE tmp2 (id2 INT NOT NULL PRIMARY KEY, id INT NOT NULL, value INT NOT NULL, INDEX (id));
INSERT INTO tmp1 VALUES (1, 1);
INSERT INTO tmp2 VALUES (21, 1, 2);
INSERT INTO tmp2 VALUES (22, 1, 3);
UPDATE tmp1 JOIN tmp2 ON tmp1.id = tmp2.id SET tmp1.value = GREATEST(tmp1.value, tmp2.value);

-- I'd expect 3, but it returns 2.
SELECT value FROM tmp1;
[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.