Bug #29251 MySQL coerces special 0 enum values to '' when ALTERing the column
Submitted: 20 Jun 2007 21:32 Modified: 11 Jul 2007 22:27
Reporter: Neil Skrypuch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.42, 5.1, 4.1 OS:Linux (Gentoo)
Assigned to: Gleb Shchepa

[20 Jun 2007 21:32] Neil Skrypuch
Description:
When ALTERing an enum column with an '' element in its definition, MySQL will coerce all of the special 0 enum values into ''. This causes data loss when invalid entries are inserted into the column (due to a typo in the column definition, for example), and then the user attempts to ALTER the column definition appropriately. After the ALTERing of the column, the user attempts to update all of the rows with the special 0 enum value, only to find that they've been replaced with ''s.

For example:

mysql> DROP TABLE `testenum`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `testenum` (`project` enum('org.eclipse.emf','org.eclipse.emf.ecore.sdo','org.eclipse.emft','org.eclipse.m2t','org.eclipse.mdt','org.eclipse.uml2','org.eclipse.xsd') NOT NULL, `component` enum('','a','b') NOT NULL, PRIMARY KEY(`project`, `component`));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `testenum` (`project`, `component`) VALUES ('org.eclipse.emf', 'abc'), ('org.eclipse.emf', '');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

Warning (Code 1265): Data truncated for column 'component' at row 1
mysql> SELECT COUNT(*), `component` FROM `testenum` GROUP BY `component`;
+----------+-----------+
| COUNT(*) | component |
+----------+-----------+
|        1 |           |
|        1 |           |
+----------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `testenum` WHERE `component` = '';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `testenum` WHERE `component` = 0;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b');
ERROR 1062 (23000): Duplicate entry 'org.eclipse.emf-' for key 1
mysql>
mysql> -- The duplicates are one problem, although it can be worked around, but with negative side effects...
mysql>
mysql> UPDATE `testenum` SET `component` = 'b' WHERE `component` != 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE `testenum` SET `component` = 'abc' WHERE `component` = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM `testenum`;
+-----------------+-----------+
| project         | component |
+-----------------+-----------+
| org.eclipse.emf |           |
| org.eclipse.emf | b         |
+-----------------+-----------+
2 rows in set (0.00 sec)

How to repeat:
DROP TABLE `testenum`;
CREATE TABLE `testenum` (`project` enum('org.eclipse.emf','org.eclipse.emf.ecore.sdo','org.eclipse.emft','org.eclipse.m2t','org.eclipse.mdt','org.eclipse.uml2','org.eclipse.xsd') NOT NULL, `component` enum('','a','b') NOT NULL, PRIMARY KEY(`project`, `component`));
INSERT INTO `testenum` (`project`, `component`) VALUES ('org.eclipse.emf', 'abc'), ('org.eclipse.emf', '');
SELECT COUNT(*), `component` FROM `testenum` GROUP BY `component`;
SELECT COUNT(*) FROM `testenum` WHERE `component` = '';
SELECT COUNT(*) FROM `testenum` WHERE `component` = 0;
ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b');

-- The duplicates are one problem, although it can be worked around, but with negative side effects...

UPDATE `testenum` SET `component` = 'b' WHERE `component` != 0;
ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b');
UPDATE `testenum` SET `component` = 'abc' WHERE `component` = 0;
SELECT * FROM `testenum`;

Suggested fix:
MySQL should preserve the special 0 values for an enum column when ALTERing it's definition, instead of coercing it into a regular '' value.

In other words, for the above case, the end result should be:

mysql> SELECT * FROM `testenum`;
+-----------------+-----------+
| project         | component |
+-----------------+-----------+
| org.eclipse.emf | abc       |
| org.eclipse.emf | b         |
+-----------------+-----------+
2 rows in set (0.00 sec)
[20 Jun 2007 21:36] Neil Skrypuch
SQL to repeat the bug

Attachment: mysqlenumbug.sql (application/octet-stream, text), 982 bytes.

[21 Jun 2007 7:15] Sveta Smirnova
Thank you for the report.

Verified as described.
[27 Jun 2007 2:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29652

ChangeSet@1.2670, 2007-06-27 03:38:14+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29251.
  Sometimes special 0 ENUM values was ALTERed to normal
  empty string ENUM values.
  
  Special 0 ENUM value has the same string representation
  as normal ENUM value defined as '' (empty string).
  The do_field_string function was used to convert
  ENUM data at an ALTER TABLE request, but this
  function doesn't care about numerical "indices" of
  ENUM values, i.e. do_field_string doesn't distinguish
  a special 0 value from an empty string value.
  
  A new copy function called do_field_enum has been added to
  copy special 0 ENUM values without conversion to an empty
  string.
[1 Jul 2007 19:57] Bugs System
Pushed into 5.1.21-beta
[1 Jul 2007 20:02] Bugs System
Pushed into 5.0.46
[1 Jul 2007 20:03] Bugs System
Pushed into 4.1.24
[11 Jul 2007 22:27] Paul Dubois
Noted in 4.1.24, 5.0.46, 5.1.21 changelogs.

If an ENUM column contained '' as one of its members (represented
with numeric value greater than 0), and the column contained
error values (represented as 0 and displayed as ''), using ALTER
TABLE to modify the column definition caused the 0 values to be
given the numeric value of the non-zero '' member.