MySQL Bugs: #29355: empty ENUM and different behaviour of SELECT-WHERE and UPDATE-WHERE

Bug #29355	empty ENUM and different behaviour of SELECT-WHERE and UPDATE-WHERE
Submitted:	26 Jun 2007 9:16	Modified:	6 Mar 2008 13:06
Reporter:	Sjon Hortensius	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.37	OS:	Linux (2.6.21.4)
Assigned to:		CPU Architecture:	Any
Tags:	enum

Description:
When updating a row where the ENUM column is '' [0]; mysql cannot find the row. A select statement will find it however? The problem seems related to the 'id' index; removing it fixes the bug. The update also starts working when Response=0 is used instead of Response=''

How to repeat:
CREATE TABLE `mysql_bug` (
  `id` int(11) NOT NULL auto_increment,
  `Responded` timestamp NOT NULL default '0000-00-00 00:00:00',
  `Response` enum('approved') NOT NULL default 'approved',
  PRIMARY KEY  (`id`),
  KEY `id` (`id`,`Response`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=2 ;

INSERT INTO `mysql_bug` (`id`, `Responded`, `Response`) VALUES 
(1, '0000-00-00 00:00:00', '');

SELECT * FROM mysql_bug
WHERE id = 1 AND Response = ''; # 1 row found

UPDATE mysql_bug SET Response = 'approved', Responded = NOW()
WHERE id = 1 AND Response = ''; # 0 rows affected

SELECT * FROM mysql_bug
WHERE id = 1 AND Response = ''; # 1 row found

Thank you for the report.

I can not repeat described behaviour with current development sources, although bug is repeatable with MySQL 5.0.41.

Please, wait next release.

This bug still occurs on 5.0.54; can someone update the status back to open?

Results from 5.0.41:

=====mysql-5.0.41-linux-i686/=====
=====bug29355=====
CREATE TABLE `mysql_bug` (
`id` int(11) NOT NULL auto_increment,
`Responded` timestamp NOT NULL default '0000-00-00 00:00:00',
`Response` enum('approved') NOT NULL default 'approved',
PRIMARY KEY  (`id`),
KEY `id` (`id`,`Response`)
);
INSERT INTO `mysql_bug` (`id`, `Responded`, `Response`) VALUES (1, '0000-00-00 00:00:00', '');
Warnings:
Warning 1265    Data truncated for column 'Response' at row 1
SELECT * FROM mysql_bug WHERE id = 1 AND Response = '';
id      Responded       Response
1       0000-00-00 00:00:00
UPDATE mysql_bug SET Response = 'approved', Responded = NOW() WHERE id = 1 AND Response = '';
SELECT * FROM mysql_bug WHERE id = 1;
id      Responded       Response
1       0000-00-00 00:00:00

From 5.0.51a, 5.0.52 and 5.0.56:

=====mysql-enterprise-gpl-5.0.52-linux-i686/=====
=====bug29355=====
CREATE TABLE `mysql_bug` (
`id` int(11) NOT NULL auto_increment,
`Responded` timestamp NOT NULL default '0000-00-00 00:00:00',
`Response` enum('approved') NOT NULL default 'approved',
PRIMARY KEY  (`id`),
KEY `id` (`id`,`Response`)
);
INSERT INTO `mysql_bug` (`id`, `Responded`, `Response`) VALUES (1, '0000-00-00 00:00:00', '');
Warnings:
Warning 1265    Data truncated for column 'Response' at row 1
SELECT * FROM mysql_bug WHERE id = 1 AND Response = '';
id      Responded       Response
1       0000-00-00 00:00:00
UPDATE mysql_bug SET Response = 'approved', Responded = NOW() WHERE id = 1 AND Response = '';
SELECT * FROM mysql_bug WHERE id = 1;
id      Responded       Response
1       2008-03-05 22:02:54     approved

This shows UPDATE finds the row.

If this is not your case please indicate accurate version of MySQL you use (file name) and operating system.

My apologies, it seems the test case is slightly different; my current UPDATE statement that still leads to this bug is the following:

UPDATE mysql_bug SET Response = 'Responded' WHERE Response = '';

This leads to 0 rows being updated; even though a SELECT will return the row inserted. My server reports itself as:

mysql  Ver 14.12 Distrib 5.0.54, for slackware-linux-gnu (i486) using  EditLine wrapper

Same correct result:
=====mysql-5.0=====
=====bug29355=====
CREATE TABLE `mysql_bug` (
`id` int(11) NOT NULL auto_increment,
`Responded` timestamp NOT NULL default '0000-00-00 00:00:00',
`Response` enum('approved') NOT NULL default 'approved',
PRIMARY KEY  (`id`),
KEY `id` (`id`,`Response`)
);
INSERT INTO `mysql_bug` (`id`, `Responded`, `Response`) VALUES (1, '0000-00-00 00:00:00', '');
Warnings:
Warning 1265    Data truncated for column 'Response' at row 1
SELECT * FROM mysql_bug WHERE id = 1 AND Response = '';
id      Responded       Response
1       0000-00-00 00:00:00
UPDATE mysql_bug SET Response = 'approved', Responded = NOW() WHERE  Response = '';
SELECT * FROM mysql_bug WHERE id = 1;
id      Responded       Response
1       2008-03-06 15:53:00     approved

Your testcase does indeed work correct. But using the exact query I posted, it does not:

CREATE TABLE `mysql_bug` (`id` int(11) NOT NULL auto_increment, `Responded` timestamp NOT NULL default '0000-00-00 00:00:00', `Response` enum('approved') NOT NULL default 'approved', PRIMARY KEY  (`id`), KEY `id` (`id`,`Response`));

INSERT INTO `mysql_bug` (`id`, `Responded`, `Response`) VALUES (1, '0000-00-00 00:00:00','');

SELECT * FROM mysql_bug WHERE id = 1 AND Response = ''; 
id 	Responded 	Response
1 	0000-00-00 00:00:00 	 

UPDATE mysql_bug SET Response = 'Responded' WHERE Response = ''; # 0 rows affected

SELECT * FROM mysql_bug WHERE id = 1;
id 	Responded 	Response
1 	0000-00-00 00:00:00

You insert incorrect value in Response field:

UPDATE mysql_bug SET Response = 'Responded' WHERE Response = '';
Warnings:
Warning 1265    Data truncated for column 'Response' at row 1