MySQL Bugs: #29251: MySQL coerces special 0 enum values to '' when ALTERing the column

Bug #29251	MySQL coerces special 0 enum values to '' when ALTERing the column
Submitted:	20 Jun 2007 23:32	Modified:	12 Jul 2007 0:27
Reporter:	Neil Skrypuch
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.42, 5.1, 4.1	OS:	Linux (Gentoo)
Assigned to:	Gleb Shchepa	Target Version:

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','o
rg.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','o
rg.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)

SQL to repeat the bug

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

Thank you for the report.

Verified as described.

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.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Pushed into 4.1.24

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.