Bug #495 Duplicate values for enum in table def

• Submitted: 24 May 2003 17:19
• Modified: 26 May 2003 3:41
• Reporter: Mike Robinson
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.0
• OS: Linux (Linux)
• CPU Architecture: Any

[24 May 2003 17:19] Mike Robinson

Description:
Defining a table column of type enum and specifying
duplicate values in the definition is allowed and gives
no warning. Expected behaviour? IMHO, defining an enum
like this should throw an error.

How to repeat:
CREATE TABLE test (
  testval enum('Y','N','Y') default 'N' NOT NULL,
)

[26 May 2003 3:41] Indrek Siitan

This is the intended behaviour of the ENUM field.

Internally, enum is kept as a number, so if you create an ENUM('Y','N','Y') field, it translates to 
ENUM(1,2,3), where 1='Y', 2='N', 3='Y'. So, for example, you can do the following operations on 
the table you created earlier:

mysql> insert into test values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------+
| testval |
+---------+
| Y       |
| N       |
| Y       |
+---------+
3 rows in set (0.00 sec)

mysql> select testval+0 from test;
+-----------+
| testval+0 |
+-----------+
|         1 |
|         2 |
|         3 |
+-----------+
3 rows in set (0.01 sec)

But thanks for bringing it up, I'll make sure this gets better documented at http://
www.mysql.com/doc/en/ENUM.html