The peculiarity of the MySQL 5.1.30 optimizer is the order of the tables in the UPDATE

Good day. I will talk about one of the funny features of the MySQL 5.1.30 optimizer, which forces you to carefully check queries before updating.
For the curious: “Now SET-expressions are not executed in the order of expressions from left to right, but in the order of updated tables”.


I want to immediately say that I am not the author of the topic, but is travisbickle . He could not publish the post because of low karma - so all thanks go there.

Like any vigil admin, I once again updated, stumbled into different places and decided that everything works.
A month has passed and today I saw only zeros on the monthly ratings chart for January ...
For the operation of transferring the current rating to the monthly and to the total, a simple request that runs once a day is answered.

UPDATE
xE_tvchannels_dyn AS t0,
xE_tvchannels_monthrating AS t1
SET
t0.ratingflushed = 1233522000,
t1.rating = t1.rating + t0.rating,
t0.totalrating = t0.totalrating + t0.rating,
t0.rating = 0
WHERE
t0.id = t1.cid AND
t1. month = '200902' AND
t0.rating > 0 AND
t0.ratingflushed <1233522000

* This source code was highlighted with Source Code Highlighter . maxshopen

The reason turned out to be prosaic - now SET-expressions are not executed in the order of expressions from left to right, but in the order of updated tables, so this query honestly performed `t0`.`ratingflushed` = 1233522000,` t0``rating` = 0, and only then `t1`.`rating` =` t1`.`rating` + `t0`.`rating`.
')
Cured, of course UPDATE `xE_tvchannels_monthrating` AS `t1`, `xE_tvchannels_dyn` AS `t0`

Be attentive, comrades!