Devananda <[EMAIL PROTECTED]> wrote on 09/21/2005 03:07:53 PM: > [EMAIL PROTECTED] wrote: > > "John McCaskey" <[EMAIL PROTECTED]> wrote on 09/21/2005 01:25:24 PM: > > > > > >>Ok, > >> > >> > >> > >>So I had another developer come to me today complaining mysql wouldn't > >>set a column to NULL. I figured out it was because instead of comma > >>delimitating his fields to SET he was delimiting with AND, however mysql > >>took this query and didn't generate any error. I'm assuming this is > >>like == vs = in c++, somehow mysql evaluated it as a Boolean logic > >>statement that turned into a valid query. I don't have the time to > >>think about it too much, but I thought it would be interesting to hear > >>how it was valid and why it didn't generate an error (or maybe its > >>actually a bug and should be an error?). > >> > >> > >> > >>Here is the query: > >> > >> > >> > >>UPDATE dashboard_tab_user SET dashboard_tab_account_id = NULL AND > >>dashboard_tab_account_server_id = NULL WHERE dashboard_tab_user_id = 194 > >>AND dashboard_tab_user_server_id = 99; > >> > >> <snip> > >> > >>The query would run and set dashboard_tab_account_id to NULL correctly, > >>but would not modify dashboard_tab_account_server_id in anyway. > >> > >> > >> > >>John A. McCaskey > >> > >>[EMAIL PROTECTED] > >> > >> > > > > > > Here is a similar example that uses some old data I have lying about.... > > > > localhost.test>select * from appointment; > > +----+-----------+--------------+---------------------+ > > | ID | OwnerName | MeetWithName | Date | > > +----+-----------+--------------+---------------------+ > > | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | > > | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | > > | 3 | John | Mary | 2004-07-31 12:15:00 | > > | 4 | John | Mary | 2004-08-04 12:15:00 | > > | 5 | John | Mary | 2004-08-11 11:15:00 | > > | 6 | John | Mary | 2004-08-25 12:15:00 | > > | 7 | Scott | David | 2004-08-01 09:00:00 | > > | 8 | Scott | James | 2004-08-07 15:00:00 | > > | 9 | Shawn | Scott | 2004-08-06 00:00:00 | > > | 10 | Scott | Anna | 2004-08-22 00:00:00 | > > | 11 | Shawn | Scott | 2003-08-07 16:00:00 | > > +----+-----------+--------------+---------------------+ > > 11 rows in set (0.11 sec) > > > > update appointment set ownername = null and meetwithname=null where id = > > 11; > > > > localhost.test>select * from appointment; > > +----+-----------+--------------+---------------------+ > > | ID | OwnerName | MeetWithName | Date | > > +----+-----------+--------------+---------------------+ > > | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | > > | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | > > | 3 | John | Mary | 2004-07-31 12:15:00 | > > | 4 | John | Mary | 2004-08-04 12:15:00 | > > | 5 | John | Mary | 2004-08-11 11:15:00 | > > | 6 | John | Mary | 2004-08-25 12:15:00 | > > | 7 | Scott | David | 2004-08-01 09:00:00 | > > | 8 | Scott | James | 2004-08-07 15:00:00 | > > | 9 | Shawn | Scott | 2004-08-06 00:00:00 | > > | 10 | Scott | Anna | 2004-08-22 00:00:00 | > > | 11 | | Scott | 2003-08-07 16:00:00 | > > +----+-----------+--------------+---------------------+ > > 11 rows in set (0.00 sec) > > > > The SET clause is parsed and evaluated like this: > > > > ownername = (NULL and (meetwithname=NULL)). > > > > Anything "and"-ed with a null is NULL so there is no need to evaluate the > > right side of the "NULL and (...)" expression which is the right side of > > the assignment operator. That leaves the SET clause as simply: ownername > > = NULL with no remaining reference to the second field. The second field > > is not modified because the section of the equation that would have caused > > a change to it is never evaluated. > > > > I don't see this as a bug. I would like to see a warning that a column
> > assignment has been optimized away. What says the list? > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > I ran into this a while back myself, and wracked my brains and > eventually asked the list why it was happening. What I struggled to > understand is why the syntax for AND inside a SET clause is different > than that of an AND inside a WHERE clause. > > For example, using C-like syntax for ='s and =='s: > "SET a = b AND c = d" --- means SET a = (b AND (c == d)) > "WHERE a = b AND c = d" --- means WHERE (a == b) AND (c == d) > > > I don't see this as a bug. I would like to see a warning that a column > > assignment has been optimized away. > > The issue is really broader than the right side being optimized away > because of "NULL and (...)" - it is about the precedence of '=' and > 'AND' in a SET clause, and that this is different than the precedence of > 'AND' in a WHERE clause. I've helped a couple people with this since I > ran into it myself. Perhaps a warning (like "ambiguous use of AND in SET > clause") could be issued to help folks recognize when this is happening > and encourage the clear use of parentheses. > > Just my $0.02 > Devananda vdv Devananda, I completely agree with your rephrasing of the problem. I can see where the second column's expression could have actually been evaluated as a comparison and not necessarily dropped by an optimization. The inner (c==d) would have been (columnvalue == NULL) in the original post. That evaluates to NULL, leaving us with (NULL and (NULL)) as the outer expression which also evaluates to NULL. In regards to creating a warning message, I don't see how the AND was ambiguous in this usage. What if instead of NULL literals, they had used column names (as in your example)? In this situation, that may have been the exact logical phrase I wanted to write and if I had received a warning, it would have confused me. I don't see any way from the perspective of the SQL parser or the SQL engine to avoid looking at the original SET expression in any other way. Since there are not two or more ways to parse the expression, there is no ambiguity. The only ambiguity is with the users (we humans) who logically equate the word "and" with a comma in the context of items-in-lists. The computer doesn't understand that usage of the word "and" so it has no ambiguity to warn us about. I am having a really hard time trying to come up with any problem other than "user needs more training" (PEBKAC). I don't mean that in a rude way but how would a developer of SQL software somehow detect an otherwize acceptable expression as being a problem? What say we just chalk this one up as a "learning experience"? :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
