| Bug #49964 | order of set commans is important | ||
|---|---|---|---|
| Submitted: | 28 Dec 2009 19:44 | Modified: | 29 Dec 2009 9:10 |
| Reporter: | Miran Cvenkel | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.31-community | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | update multiple fields | ||
[28 Dec 2009 20:41]
Peter Laursen
Also I would expect that all columns to be updated with conditionals should be evaluated against the situation before any of the updates specified is performed. Very good catch in my opinion! Peter (not a MySQL person)
[28 Dec 2009 20:44]
Sveta Smirnova
Thank you for the report. This is documented and expected behavior. Please see http://dev.mysql.com/doc/refman/5.1/en/user-variables.html for details: "...The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed..."
[28 Dec 2009 21:49]
Peter Laursen
@Sveta .. mistaken reply here in my opinion. Ceran's example has no 'user variable' at all! It is a plain "UPDATE column1 .. , column2 .." type of statement (but with conditionals). 'User variables' use @character - where do you´find @it?
[28 Dec 2009 21:55]
Sergei Golubchik
Still, not a bug: http://dev.mysql.com/doc/refman/5.1/en/update.html " Single-table UPDATE assignments are generally evaluated from left to right. "
[28 Dec 2009 22:45]
Peter Laursen
So what does the term 'generally' mean in this context? Mostly like *this* and sometimes (but rarely) like *that*? 'generally' ???? One more 'loose-talk' example in documentation has been documented (at least)! :-) Except for that I'd prefer reference to SQL standards and teh behavior of other mainstream' databases rather than MySQL docs. I find this behavior counter-intuitive (what does not exclude that it is correct - only a reference to docs is not enough as the docs may be as buggy as the code).
[29 Dec 2009 9:10]
Miran Cvenkel
So, mybe this should be turned into feature request.
Description: So, I don't know if this is realy a bug, mybe all databases are doing this, but I doubt doh. It turns out that this: A. update table set f1 = if(f1 = '','foo',f1), f2 = if(f1 <> '','foo',f2); is same thing as: B. update table set f1 = if(f1 = '','foo',f1); update table set f2 = if(f1 <> '','foo',f2); Goal: update f1 if it is '', else update f2. If you look at A, it does not work as expected coz line 2 already sees result of update command at line 1, so order of 'set' commands is important here, doh I belive nobody would expect that. How to repeat: look up. Suggested fix: reverse order of updates, knoving that order is important , like; update table set f2 = if(f1 <> '','foo',f2), f1 = if(f1 = '','foo',f1)