"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 05:20:49 PM:
> [snip] > > 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. > [/snip] > > Most manufacturers are using ANSI SQL 99 IIRC (ymmv) which is available > from ANSI (http://www.ansi.org) for a small fee. It generally takes a > few years for standards to become implemented by mfgs. > > I think that you may be confusing search conditionals and equation > operations too... > > WHERE foo <= bar is a search condition > > IF(t1.foo=t2.bar, 2, 1) is an equation OK, here is the same section of SQL99 ( http://www.ncb.ernet.in/education/modules/dbms/SQL99/ansi-iso-9075-2-1999.pdf) ISO/IEC 9075-2:1999 (E) 14.10 <update statement: searched> (p 687) 9) The <update source> of each <set clause> is effectively evaluated for each row of T before any row of T is updated. 10) A <set clause> specifies one or more object columns and an update value. An object column is a column identified by an <object column> in the <set clause>. The update value is the value specified by the <update source> contained in the <set clause>. NOTE 314 â The data values allowable in the object rows may be constrained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 14.22, ââEffect of replacing some rows in a viewed tableââ. I read 9) as saying that all of the source values, <update source>, to be used to calculate each term of each <set clause> shall be determined before any row updates occur. I read 10) as saying that all column changes computed by the terms of the <set clause> shall be based on the values stored in the <update source> Would a quote from SQL-92 do? (Sorry but I have lost it's source URL.) X3H2-92-154/DBL CBR-002 13.10 <update statement: searched> (p. 395) 6) The <value expression>s are effectively evaluated for each row of T before updating any row of T. 7) A <set clause> specifies an object column and an update value of that column. The object column is the column identified by the <object column> in the <set clause>. The update value is the value specified by the <update source>. Note: The data values allowable in the object row may be con- strained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 11.19, "<view definition>". I read 6) as saying that until you complete all of the calculations on a row, you don't change the data for the row. I could even interpret this as saying that you don't update ANY row on the table until you have completed the calculations for ALL of the rows. I read 7) as saying that you compute all new values based on a fixed set of initial values, the <update source>. Am I just totally confused? I do not believe that any of these specification allows the engine to "see" a change to a row value until after the row (or even the entire statement, depending on how you want to read the clauses dealing with <value expression>s or <update source>) completes it's processing. No, I do not believe I have confused search conditionals with assignment or equation operators. The term <update statement: searched> refers to updates that occur as part of a stand-alone statement (UPDATE ... SET ... WHERE ...) as compared to <update statement: positioned> which occurs within the processing of a cursor (WHERE = searched, CURSOR = positioned). Each type of update is handled distinctly in all three of the SQL specifications I have available to me. To me the MySQL implementation of UPDATE processing does not adhere to these standards. I can't wait for the holidays to end so that the developers can weigh in on this issue. Happy new year! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
