MySQL Bugs: #15322: Multiple-Table Update Problem

Bug #15322	Multiple-Table Update Problem
Submitted:	29 Nov 2005 17:50	Modified:	29 Dec 2005 18:26
Reporter:	Ron Unz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	MySQL 5.0.16	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
5.0.16 seems to perform Multiple-Table Updates differently than the 4.0 and 4.1 versions.  The previous versions updated ALL the joined rows, while the new version seems to update only the FIRST joined row.

How to repeat:
Consider a hierarchical data table Tree containing three fields: ID, ParentID, and Description.

Now consider this Multiple-Table Update statement:

UPDATE Tree AS X, Tree AS P SET X.Description='something' WHERE X.parentID=P.ID AND P.ID=12345

The previous versions would have updated all rows having parentID=12345.  The new 5.0 version updates only the first row having parentID=12345

Could you please provide a complete script test case, with table
definition, inserts and the update queries failed.

Thanks in advance.

Under mySQL versions prior to 5.0, the code

UPDATE user NATURAL JOIN user_t 
SET loggedin = 'N', lastlogin = thislogin, lastlogout = lastseen + INTERVAL 30 MINUTE, thislogin = NULL
WHERE loggedin = 'Y'  AND lastseen < NOW() - INTERVAL 30 MINUTE

worked perfectly. But under 5.0 the first line must be changed to

UPDATE user RIGHT JOIN USING (userid)

otherwise only one row is affected (out of a total of about 90,000). The common column is userid, and the columns changed all reside in the first table, though values from the second are used in computing the values to be stored in the first.

I should add that the behaviour is the same under Linux x86 and Mac OS X.

I believe this problem is when database files from older versions of MySQL are used in 5.0. I had this problem on normal JOINs, until I reordered the table on the key I was joining on, like this:

ALTER TABLE mytable ORDER BY id;

Then, I ran the UPDATE ... JOIN ... query and it affected all rows, rather than just one. Not sure if this will help give a clue or not as to the root of the problem, but it might give you an idea as to how to work around the problem.

Hello,

I had the same problem. I just exported all the tables to files via PhpMyAdmin and imported them back. Now everything works fine.

Jan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".