Jay Blanchard schrieb:
[snip] I hope I am not so naive that Kai and I are the only two who sees aI don't think it is clear, what Shawn's and my point is.
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.
It is clear, that a multi table update will be executed in a serialized manner. I don't want to think of the difficulties in implementing a true atomic multi table update.
But it is not clear, on which data the subsequent updates work.
Currently the first update uses the initial data. The second uses the initial data and the updated data from the first table. The third update sees the updated data from the first two tables and the initial data for the remaining, etc...
Here comes the "house of cards" into play:
- What if an upgraded optimizer decides to swap table 4 and table 6 in an update line. A query on the same data could perform differently. Or on the other side an optimizer will never be allowed to change any order in the update and every join is automatically "straight".
- What about the equivalence in a SELECT between
FROM table1 LEFT JOIN table2
and
FROM table2 RIGHT JOIN table1
Doesn't this apply to updates, because in the first case the update order is table1 followed by table2 and vice versa in the second case.
So the question is not, if a multi table update should be implemented serialized. The answer is clear: yes it should.
The question is, on which data the subsequent updates should work.
In my opinion every subsequent update should work on the initial data.
- An optimizer can perform the updates in any order without changing their result.
- From the clients point of view the update acts atomically. There is initial data, an update and then changed data. There is no intermediate state during the update that the client can get / must be aware of.
- The equivalence between severel joins remains.
Greetings Kai
-- Kai Ruhnau Software
t a r g e t systemelectronic gmbh
kölner str. 99
42651 Solingen
germany
Tel: +49 (0)212 22 20 9 - 710
Fax: +49 (0)212 20 10 45
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
