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]
