"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 03:50:24 PM:
> [snip] > ... a bunch of stuff.... > [/snip] > > It alomost sounds as if you're coming from a M$ camp....what you > describe are VIEWS. In a way it is, an internally generated view of the source/destination tables. > > Anyhow, the ANSI SQL standards specify some of the behaviors we have > been talking about. Many DB manufacturers use the standards and > implement other "features" like you describe that fall outside of the > standards. Can you send me a link to those, I would like to read them so that I can stay up to date. I was able to find a version of SQL-2003 (see below), which one are you thinking of? > > I will repeat that with regards to the actions that you describe > concerning variables that you would be looking for something called a > CONSTANT. Variables are just that...variable. > And they will change their value to match the last update applied to it. But they don't change their values (for calculation purposes) until the engine finishes the row. > Anyhow, it has been a good discussion. No bugs were uprooted and > everyone's understanding may have risen a notch. After a couple of > decades using SQL I still find interesting topics to discuss. Yes, I too have enjoyed this conversation. Thank you for thinking out loud with me on such an important topic. I have one last pitch to make, though, so I don't think you are quite off the hook yet. ;-) I went looking and found a nearly finished copy of the SQL-2003 spec (but not the official one) at http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing too much from what I quote below. (book page: 849, PDF page 873) ********* begin excerpts ******************* ISO/IEC 9075-2:2003 (E) 14.11 <update statement: searched> 5) Case: a) If <target table> contains ONLY, then Case: i) If a <search condition> is not specified, then all rows of T for which there is no subrow in a proper subtable of T are the subject rows. ii) If a <search condition> is specified, then it is applied to each row of T with the exposed <correlation name>s or <table or query name>s of the <table reference> bound to that row, and the subject rows are those rows for which the result of the <search condition> is True and for which there is no subrow in a proper subtable of T. The <search condition> is effectively evaluated for each row of T before updating any row of T. Each <subquery> in the <search condition> is effectively executed for each row of T and the results used in the application of the <search condition> to the given row of T. If any executed <subquery> contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. b) Otherwise, Case: i) If a <search condition> is not specified, then all rows of T are the subject rows. ii) If a <search condition> is specified, then it is applied to each row of T with the exposed <table name> of the <target table> bound to that row, and the subject rows are those rows for which the result of the <search condition> is True. The <search condition> is effectively evaluated for each row of T before any row of T is updated. Each <subquery> in the <search condition> is effectively executed for each row of T and the results used in the application of the <search condition> to the given row of T. If any executed <subquery> contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. NOTE 389 â outer reference is defined in Subclause 6.7, â<column reference>â. 6) If T is a base table, then each subject row is also an object row; otherwise, an object row is any row of a leaf generally underlying table of T from which a subject row is derived. 7) Equivalent <object column>s shall not appear more than once in a <set clause list>. 9) The <update source> of each <set clause> is effectively evaluated for each row of T before any row of T is updated. 10) For each subject row, a candidate new row is constructed by copying the subject row and updating it as specified by each <set clause> by applying the General Rules of Subclause 14.12, â<set clause list>â. ********** end excerpts *********** If I interpret the above excerpted section and the other (referenced) sections appropriately (which I may not be, after such a quick read) it seems as though the SQL engine is supposed to determine the <target table> (which may be composed of multiple tables, views, and/or subqueries) then apply changes to each row of the <target table> by starting from a copy of the subject row and transforming it according to the <set clause list> then replacing that row in the <target table> with the results of the transformations. To be even more succinct: Start from a snapshot of all initial values and construct a new row based on values from the snapshot. (much as each generation of a "cellular automata"-baed simulation is computed). By my interpretation of these rules, I believe that MySQL fails to follow specification as Kai's sample data demonstrates. The failure is because changed data is immediately available as source data even though all of the row's transformations are not complete. The transformation to be applied to any column is not insulated from the results of the transformations applied to any of columns listed before it in the <target table>. However, I could not find a reference to what happens to the values of user-defined variables if they are assigned more than once per UPDATE statement. ( That contingency could be possibly covered by paragraph 7, preventing multiple updates ) It seems that variables are more implementation-defined than I expected. (or I just couldn't find the right section....) Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
