[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;
Here is the table schema:
CREATE TABLE `dashboard_tab_user` (
`dashboard_tab_user_id` mediumint(8) unsigned NOT NULL auto_increment,
`dashboard_tab_user_server_id` tinyint(3) unsigned NOT NULL default
'0',
`dashboard_tab_account_id` mediumint(8) unsigned default '0',
`dashboard_tab_account_server_id` tinyint(3) unsigned default '0',
`user_id` mediumint(8) unsigned NOT NULL default '0',
`user_server_id` tinyint(3) unsigned NOT NULL default '0',
`dashboard_tab_user_name` char(18) NOT NULL default '',
`snap` enum('on','off') NOT NULL default 'off',
`creation_timestamp` timestamp(14) NOT NULL,
`cols` tinyint(3) NOT NULL default '2',
`rows` tinyint(3) NOT NULL default '2',
`active` enum('on','off') NOT NULL default 'on',
PRIMARY KEY (`dashboard_tab_user_id`,`dashboard_tab_user_server_id`),
KEY `user_id` (`user_id`,`user_server_id`),
CONSTRAINT `dashboard_tab_user_ibfk_1` FOREIGN KEY (`user_id`,
`user_server_id`) REFERENCES `user` (`user_id`, `user_server_id`) ON
DELETE CASCADE
) TYPE=InnoDB
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
