Bug #61361 Possible of duplicated primary key by enum type
Submitted: 31 May 2011 10:59 Modified: 15 Oct 2011 17:34
Reporter: Dennis Cheung Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.9 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2011 10:59] Dennis Cheung
Description:
In a table with primary key type ENUM, is expected only valid key will exists in table. However, you can insert null enum into the table.

How to repeat:
CREATE TABLE `runtime_config` (
  `dummy_id` enum('') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`dummy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$

INSERT INTO `runtime_config` VALUES('');
INSERT INTO `runtime_config` VALUES(0);

SELECT dummy_id FROM runtime_config;
[31 May 2011 11:27] Peter Laursen
A slightly elaborated test case (in my understanding it has nothing wiht NULL to do, but something weird happens.

DROP TABLE IF EXISTS `runtime_config`;

CREATE TABLE `runtime_config` (
  `dummy_id` ENUM('') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`dummy_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `runtime_config` VALUES(''); -- reports success
INSERT INTO `runtime_config` VALUES(''); -- Error Code : 1062 - Duplicate entry '' for key 'PRIMARY'
INSERT INTO `runtime_config` VALUES(0); -- reports success
SHOW WARNINGS; -- Warning Code : 1265 - Data truncated for column 'dummy_id' at row 1
INSERT INTO `runtime_config` VALUES(0); -- Error Code : 1062 - Duplicate entry '' for key 'PRIMARY'
INSERT INTO `runtime_config` VALUES(NULL); -- Error Code : 1048 - Column 'dummy_id' cannot be null

SELECT COUNT(dummy_id) FROM runtime_config; -- 2
SELECT COUNT(dummy_id) FROM runtime_config WHERE dummy_id = ''; -- 1

SELECT HEX(dummy_id) FROM runtime_config;
+---------------+
| HEX(dummy_id) |
+---------------+
|               |
|               |
+---------------+
2 rows in set (0.00 sec)

I am mystified what the 3rd INSERT here actually inserts!

Peter
(not a MySQL person)
[31 May 2011 11:31] Peter Laursen
To me it looks like it INSERTS 'empty string' to *data* twice, but different data are UPDATED to *index*.

(I am using 5.5.13 BTW)
[31 May 2011 11:46] Peter Laursen
add to test case: 

ALTER TABLE `test`.`runtime_config` CHANGE `dummy_id` `dummy_id` ENUM('') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  DROP PRIMARY KEY; -- success

ALTER TABLE `test`.`runtime_config` CHANGE `dummy_id` `dummy_id` ENUM('') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  ADD PRIMARY KEY(`dummy_id`); -- success

SHOW WARNINGS; -- empty set

(and SELECT output is unchaged)
[31 May 2011 18:46] Peter Laursen
add: 

INSERT INTO `runtime_config` VALUES(''); -- success
INSERT INTO `runtime_config` VALUES(0); --  success with warning
INSERT INTO `runtime_config` VALUES(1); -- duplicate entry
INSERT INTO `runtime_config` VALUES(X); -- Error Code : 1054 - Unknown column 'x' in 'field list'

If "0" (without quotes) truncates (almost! :-) ) to '' why does not "X" (also without quotes) ?
[1 Jun 2011 8:56] Shane Bester
Trying to figure out if the docs are explaining it: "If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. More about this later."

http://dev.mysql.com/doc/refman/5.6/en/enum.html

On 5.6.3 i get:

mysql> select dummy_id+0 from runtime_config where dummy_id='';
+------------+
| dummy_id+0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select dummy_id+0 from runtime_config where dummy_id=0;
+------------+
| dummy_id+0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)
[1 Jun 2011 9:38] Peter Laursen
Why is this implemented like that?  What is special about ENUM's as compared to other datatypes, so that it needs a 'special error value' INSERTED?

HEX() on both columns does not reveal any difference!
[1 Jun 2011 10:49] Peter Laursen
Compare:

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (id CHAR(3) PRIMARY KEY) ENGINE=INNODB;
INSERT INTO tmp VALUES('the');
INSERT INTO tmp VALUES('there'); -- Error Code : 1062 - Duplicate entry 'the' for key 'PRIMARY'

No INSERT happens of the truncated value ("the") with a 'special error code marker'.  I do not understand why truncation when inserting ENUM's isn't handled exactly the same way as is the case for other datatypes.
[15 Oct 2011 17:34] Valeriy Kravchuk
As Shane explained, it seems internally ENUM is stored as numeric value (index of item). So, you can insert (once!) some value not present in table's definition - PRIMARY key will get value 0.

This is not a bug.
[15 Oct 2011 17:44] Peter Laursen
the implementation is still crazy! :-)