MySQL Bugs: #23113: Different behavior on altering ENUM fields between 5.0 and 5.1

Bug #23113	Different behavior on altering ENUM fields between 5.0 and 5.1
Submitted:	9 Oct 2006 19:56	Modified:	10 Nov 2008 17:53
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.12-BK	OS:	Linux (Linux, others?)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	regression

Description:
If an ENUM field is altered in 5.0, it keeps the literal values, while in 5.1 it assigns the values according to their position.

How to repeat:
MySQL 5.0.26:

mysql> CREATE TABLE tt (
    ->   id INT UNSIGNED NOT NULL
    ->      AUTO_INCREMENT PRIMARY KEY,
    ->   val ENUM('a', 'b', 'c') NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tt (val) VALUES
    ->   ('a'), ('c'), ('b'), ('b'), ('a');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
|  2 | c   |
|  3 | b   |
|  4 | b   |
|  5 | a   |
+----+-----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE tt
    ->   MODIFY val ENUM('a', 'z', 'b', 'c') NOT NULL;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
|  2 | c   |
|  3 | b   |
|  4 | b   |
|  5 | a   |
+----+-----+
5 rows in set (0.00 sec)

MySQL 5.1.12:

mysql> CREATE TABLE tt (
    ->   id INT UNSIGNED NOT NULL
    ->      AUTO_INCREMENT PRIMARY KEY,
    ->   val ENUM('a', 'b', 'c') NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tt (val) VALUES
    ->   ('a'), ('c'), ('b'), ('b'), ('a');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
|  2 | c   |
|  3 | b   |
|  4 | b   |
|  5 | a   |
+----+-----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE tt
    ->   MODIFY val ENUM('a', 'z', 'b', 'c') NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
|  2 | b   |
|  3 | z   |
|  4 | z   |
|  5 | a   |
+----+-----+
5 rows in set (0.00 sec)

Thank you for the bug report.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.26-debug | 
+--------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   | 
|  2 | c   | 
|  3 | b   | 
|  4 | b   | 
|  5 | a   | 
+----+-----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE tt
    -> MODIFY val ENUM('a', 'z', 'b', 'c') NOT NULL;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   | 
|  2 | c   | 
|  3 | b   | 
|  4 | b   | 
|  5 | a   | 
+----+-----+
5 rows in set (0.01 sec)

mysql> 
---------------------------------------------------------
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   | 
|  2 | c   | 
|  3 | b   | 
|  4 | b   | 
|  5 | a   | 
+----+-----+
5 rows in set (0.01 sec)

mysql> ALTER TABLE tt
    -> MODIFY val ENUM('a', 'z', 'b', 'c') NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tt;
+----+-----+
| id | val |
+----+-----+
|  1 | a   | 
|  2 | b   | 
|  3 | z   | 
|  4 | z   | 
|  5 | a   | 
+----+-----+
5 rows in set (0.00 sec)

Is this an expected change?

It's impossible to investigate whether it's expected or not quickly.
Needs to be re-triaged - it's a regression, after all.

1. What happens in 5.0 if you insert 'a','b','c' as valid ENUMS, and then MODIFY to make 'a','b','z' the allowable values?
2. Does the SQL standard have anything to say about what should happen?

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/56278

2771 Ramil Kalimullin	2008-10-15
      Fix for bug#23113: Different behavior on altering ENUM fields between 5.0 and 5.1
      
      Problem: mysqld doesn't detect that enum data must be reinserted performing
      'ALTER TABLE' in some cases.
      
      Fix: reinsert data altering an enum field if enum values are changed.

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/56969

2776 Ramil Kalimullin	2008-10-24
      Fix for bug#23113: Different behavior on altering ENUM fields between 5.0 and 5.1
      
      Problem: mysqld doesn't detect that enum data must be reinserted performing
      'ALTER TABLE' in some cases.
      
      Fix: reinsert data altering an enum field if enum values are changed.

Pushed into 5.1.30  (revid:ramil@mysql.com-20081024080003-n97jpnf6xr4ftt6g) (version source revid:mats@sun.com-20081103175049-ulv24ke1m2lpi632) (pib:5)

Noted in 5.1.30 changelog.

ALTER TABLE for an ENUM column could change column values.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.8-alpha  (revid:ramil@mysql.com-20081024080003-n97jpnf6xr4ftt6g) (version source revid:davi.arnaut@sun.com-20081024142954-k0uqv2e2hzcta51f) (pib:5)

Note in 6.0.8 changelog.

6.0.9 changelog, not 6.0.8.

Pushed into 5.1.30-ndb-6.2.17  (revid:ramil@mysql.com-20081024080003-n97jpnf6xr4ftt6g) (version source revid:tomas.ulin@sun.com-20081121161314-jhz3twf5xjt8stnh) (pib:5)

Pushed into 5.1.30-ndb-6.3.20  (revid:ramil@mysql.com-20081024080003-n97jpnf6xr4ftt6g) (version source revid:tomas.ulin@sun.com-20081121210644-zas6e9wa5kuj7d6f) (pib:5)

Pushed into 5.1.30-ndb-6.4.0  (revid:ramil@mysql.com-20081024080003-n97jpnf6xr4ftt6g) (version source revid:tomas.ulin@sun.com-20081126125835-5sohkzk2jjwpq1wp) (pib:5)