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!