[snip] I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement, I need to read them because I have been operating under some mistaken beliefs for a long time.
This is a real "house of cards" and I am a bit uncomfortable with the situation. I am also surprised this hasn't come up more often. I will be forwarding this to the internals list to get more eyes on the issue. Hopefully one of them can explain why this is "correct" behavior and I can just settle down and just be more deliberate in how I construct my statements. [/snip] I am curious to see what the internals list says on this as well. A reading of SQL 92 and 99 support serialization behavior because "atomic" behavior in this type of operation is likely ambiguous at best. On several other implementations (other DBs) of multi-table updates the behavior is exactly the same as this. There was a bug, http://bugs.mysql.com/bug.php?id=1038 , that had something to do with th join operations, but the bug has been fixed. Careful and deliberate construction of SQL statements should always occur and be tested thouroughly prior to any implementation in a production environment. And make sure to EXPLAIN each and every query when testing. As to your second concern, variables, let us explore why your query behaves as it does. >SET @testvalue=5; >Select @testvalue:=19, @testvalue+1 as bump; >+----------------+------+ >| @testvalue:=19 | bump | >+----------------+------+ >| 19 | 20 | >+----------------+------+ > >From http://dev.mysql.com/doc/mysql/en/Variables.html "You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements:" So you set @testvar to be 5, then when you SELECTed it you performed a reassignment (@testvar:=19) and then bumped it. This is expected behavior, so your reversal gives you the expected behavior because you performed the re-assignment after you bumped the original value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
