"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
01:32:20 PM:
> [snip]
> 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).
> [/snip]
>
> This has nothing to do with serialization, you have reassigned the
> variable value, and like any other programming language the new value is
> valid until it is reset by something else, so any subsequent operation
> will use that value. I believ that what you would like here is a
> CONSTANT
>
> [snip]
> 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
> [/snip]
>
> It is because you are defining the order of the update in the UPDATE
> statement itself;
>
> UPDATE t2, t1, t0
>
> You have now specified the order of the update regardless of the actual
> updates that occur in the SET statements. SETs do not define order, even
> in single table/multiple column updates. If I do
I don't visualize it that way. I see the UPDATE ... JOIN ... portion of
the statement as defining an internally-constructed virtual table that
consists of all of the columns of each table participating in this
particular statement. This table is "populated with records" (mapped to
the actual data records) according to the inclusiveness (inner or outer)
and direction (left or right) of the JOIN clause and according to all of
the conditions established in the ON clause(s). The effect is, there is a
virtual table that becomes the target/source of the UPDATE or SELECT
statement being executed.
Now, each row of the virtual table must also be evaluated according to the
conditions specified in the WHERE clause (optimizations do exist that
shortcut out many row-by-row comparisons). For those rows that made it to
the table (from the JOINS) and pass the WHERE conditions, the column
transformations (formulas, simple reads, or assignments) specified in the
SET/SELECT clause are applied and those changes are either cached (until
the transaction commits or gets rolled back) or are committed to disk on a
row-by-row basis until we either run out of rows in the virtual table or
encounter an exception.
I think that where we truly disagree is about the order in which the
column transformations should be made. I say that each transformation
performed during the processing of a row should be based on the values
present when the processing of that row starts and performed in the order
specified in the SET/SELECT clause. If I understand you correctly, you are
saying that the transformations must be performed in the column order of
the virtual table and not in the sequence specified in the statement being
executed.
I also disagree with you that the column order of the virtual table should
have any effect on the final results. To use another variable-based
example (this is based on the principle that the value of an assignment
operation is the value that was assigned):
SET @Basevalue = 10;
SELECT @Basevalue:= @Basevalue + 10,@Basevalue:= @Basevalue +
10,@Basevalue:= @Basevalue + 10;
I expect the results {20,20,20} not {20,30,40} because for each formula,
@Basevalue (originally, on entry) is 10 not the results of the formula
that happened to be listed before it in the statement. If I repeated the
SELECT statement I would get {30,30,30} because on entry to that statement
@Basevalue had the initial value of 20. On exit, @Basevalue would be 30.
> SELECT t2.foo, t1.bar
>
> my results come out in the order specified in the SELECT statement, not
> the order in the FROM or subsequent condition statements...so the
> behavior really is no different between SELECT and UPDATE.
>
>
I agree, both SELECT and UPDATE (mis-)behave the same way.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
