Bug #117 UPDATE / DELETE fails to find existing rows
Submitted: 3 Mar 2003 11:20 Modified: 4 Mar 2003 9:46
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23 OS:Any (all)
Assigned to: CPU Architecture:Any

[3 Mar 2003 11:20] Sinisa Milivojevic
Description:
SELECT .. WHERE ....

returns N rows

UPDATE/DELETE with 100 %  exact WHERE conditions fails.

This is due to the fact that in WHERE condtions there are IS NULL clauses which
should not be tested within select->check_quick(safe_update, limit)).

How to repeat:
drop table if exists  `user_info`;
CREATE TABLE `user_info` (
  `uid` mediumint(8) unsigned NOT NULL auto_increment,
  `gid` mediumint(8) unsigned NOT NULL default '0',
  `phone_confirmed` char(0) default NULL,
  `username` varchar(20) binary NOT NULL default '',
  `nickname` varchar(20) binary NOT NULL default '',
  `title` varchar(12) NOT NULL default '´¶³q·|­û',
  `passwd` varchar(20) binary NOT NULL default '',
  `email` varchar(50) default NULL,
  `icq` int(10) unsigned NOT NULL default '0',
  `country` char(2) NOT NULL default 'HK',
  `district` varchar(20) NOT NULL default '',
  `sex` enum('M','F') NOT NULL default 'M',
  `occupation` varchar(20) NOT NULL default '',
  `interest` varchar(20) NOT NULL default '',
  `homepage` varchar(60) NOT NULL default '',
  `homepage_name` varchar(60) NOT NULL default '',
  `regdate` date NOT NULL default '0000-00-00',
  `plogo` varchar(132) NOT NULL default '',
  `signature` text NOT NULL,
  `ip` varchar(15) NOT NULL default '',
  `last_online` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_topic` varchar(80) NOT NULL default '',
  `post_total` mediumint(8) unsigned NOT NULL default '0',
  `aver_on_time` smallint(5) unsigned NOT NULL default '0',
  `today_on_time` smallint(5) unsigned NOT NULL default '0',
  `last_31d_on_time` varchar(156) NOT NULL default '',
  `last_12m_on_time` varchar(60) NOT NULL default '',
  `about` text NOT NULL,
  PRIMARY KEY  (`username`),
  UNIQUE KEY `nickname` (`nickname`),
  UNIQUE KEY `uid` (`uid`),
  UNIQUE KEY `email` (`email`)
) TYPE=MyISAM;

INSERT INTO `user_info` VALUES 
(1,1,NULL,'vita','vita','a','a','vita@a.com',1,'uk','a','M','a','a','
a','a','2003-03-03','a','a','a','2003-03-03 12:36:03','a',1,1,1,'a','a','a');
INSERT INTO `user_info` VALUES 
(2,1,NULL,'egor','egor','a','a',NULL,1,'uk','a','M','a','a','a','a','
2002-11-11','a','a','a','2003-01-01 00:00:00','a',1,1,1,'a','a','a');
INSERT INTO `user_info` VALUES 
(3,1,NULL,'sasha','sasha','a','a',NULL,1,'uk','a','M','a','a','a','a'
,'2003-02-12','a','a','a','2003-02-18 00:02:13','a',1,1,1,'a','a','a');
INSERT INTO `user_info` VALUES 
(4,1,NULL,'tony','tony','a','a','tony2001@php.net',1,'uk','a','M'
,'a','a','a','a','2003-03-03','a','a','a','2003-03-03 
10:10:13','a',1,1,1,'a','a','a');

select * from user_info where regdate < now()- interval 9 day and  last_online<now()-interval 10 day and phone_confirmed is null;

update user_info set email="1" where regdate < now()- interval 9 day and last_online<now()-interval 10 day and  phone_confirmed is null;

select * from user_info where regdate < now()- interval 9 day and  last_online<now()-interval 10 day and  phone_confirmed is null;

drop table if exists  `user_info`;
[4 Mar 2003 9:46] Sinisa Milivojevic
A fix:

===== sql/field.h 1.46 vs edited =====
*** /tmp/field.h-1.46-8825      Tue Dec  3 13:08:21 2002
--- edited/sql/field.h  Tue Mar  4 19:23:57 2003
***************
*** 65,71 ****
    virtual String *val_str(String*,String *)=0;
    virtual Item_result result_type () const=0;
    virtual Item_result cmp_type () const { return result_type(); }
!   bool eq(Field *field) { return ptr == field->ptr; }
    virtual bool eq_def(Field *field);
    virtual uint32 pack_length() const { return (uint32) field_length; }
    virtual void reset(void) { bzero(ptr,pack_length()); }
--- 65,71 ----
    virtual String *val_str(String*,String *)=0;
    virtual Item_result result_type () const=0;
    virtual Item_result cmp_type () const { return result_type(); }
!   bool eq(Field *field) { return ptr == field->ptr && null_ptr == field->null_ptr; }
    virtual bool eq_def(Field *field);
    virtual uint32 pack_length() const { return (uint32) field_length; }
    virtual void reset(void) { bzero(ptr,pack_length()); }
[/mnt/work/mysql-4.0]$