"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
