MySQL Bugs: #34813: duplicate values not allowed in enums

Bug #34813	duplicate values not allowed in enums
Submitted:	25 Feb 2008 17:48	Modified:	21 May 2008 20:48
Reporter:	Brian Duggan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.51a, 5.0.54	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	enum

Description:
The behavior changed somewhere between 5.0.26 and 5.0.51 -- enum columns with duplicate values are now an error instead of a warning.

The only reference to this I could find was 
http://bugs.mysql.com/bug.php?id=1427
which says it will be a warning but not an error.

How to repeat:
create table test_foo (a enum('x','x'));

In 5.0.26 :

Query OK, 0 rows affected, 1 warning (0.01 sec)

In 5.0.51a-community

ERROR 1291 (HY000): Column 'a' has duplicated value 'x' in ENUM

Suggested fix:

Either document this someplace (if it is documented, I couldn't find it), or provide a way to allow the former behavior.

thanks

Thank you for a problem report. I think, this is intended behaviour that should be explicitely documented at http://dev.mysql.com/doc/refman/5.0/en/enum.html at least.

I think this change may occur because sql_mode was changed.

In *strict* modes an error is returned with duplicate SET/ENUM specification, else a warning!

No, both servers were in strict mode, but in the earlier version, it was only a warning.

(dz_admin@localhost) dz3> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(dz_admin@localhost) dz3> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.0.26-standard-log |
+---------------------+
1 row in set (0.00 sec)

(dz_admin@localhost) dz3> create table foo (a enum('x','x'));
Query OK, 0 rows affected, 1 warning (0.02 sec)

But...

(root@localhost) dz3_rebuild> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) dz3_rebuild> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 5.0.51a-community |
+-------------------+
1 row in set (0.00 sec)

(root@localhost) dz3_rebuild> create table foo(a enum('x','x'));
ERROR 1291 (HY000): Column 'a' has duplicated value 'x' in ENUM

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

This change in behavior occurred in MySQL 5.0.40, as a result of the fix for Bug#34813. However, it was never pointed out in the ENUM/SET pages. I have now added a note that duplicate values cause an error in strict SQL mode.

Sorry, previous note should say that it was a result of the fix for Bug#27069, not #34813.