"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 11:43:43 AM:
> [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. I understand that is the behavior of the assignment operator. That's why I used it. ;-) I expected the results to be 19 and 6 not 19 and 20. This is just another example of the serialization of evaluation that occurs while processing a single row of data. I have the opinion that while processing that row, the variable @testvalue should keep its original value and only be updated when moving to the next row(statement). If evaluation is serialized in a SELECT statement based on the order in which they are listed, then why aren't they evaluated in that order during the processing of a SET clause? Why does the query engine seem to revert to processing changes in "table column order" and not maintain the "statement sequence order" as specified by the user? That could imply that I need to change the design of my tables so that my update statements will execute in the correct order. If only the changes had happened in the sequence specified in Kai's original SET statement... (sigh) 8-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine
