List:General Discussion« Previous MessageNext Message »
From:John McCaskey Date:September 21 2005 5:25pm
Subject:SET query with inproper AND doesn't generate error?
View as plain text  
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

johnm@stripped

 


Thread
SET query with inproper AND doesn't generate error?John McCaskey21 Sep
  • Re: SET query with inproper AND doesn't generate error?SGreen21 Sep
    • Re: SET query with inproper AND doesn't generate error?Devananda21 Sep
      • Re: SET query with inproper AND doesn't generate error?SGreen21 Sep