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`;
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`;