Bug #2133 ENUM fields do not INSERT or UPDATE with '0' (zero) as a value

• Submitted: 16 Dec 2003 17:51
• Modified: 17 Dec 2003 3:56
• Reporter: Matisse Enzer
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.15a
• OS: Linux (linux 2.4.20-24.8 kernel)
• CPU Architecture: Any

[16 Dec 2003 17:51] Matisse Enzer

Description:
ENUM columns do not handle a value of '0'.

The column ends up having an empty value (index 0) as if '0' was not a valid value.

How to repeat:
Put the following column in a table:

   `enum_column` enum('1','0') default NULL
or
    `enum_column` enum('0','1') default NULL

now insert and/or update a record with a value '0'. The resuling record has an empty value instead of '0'

Suggested fix:
None known.
In my case I switched to an int(1) column.

[16 Dec 2003 17:52] Matisse Enzer

Chnaged synopis to mention INSERt as well as UPDATE

[17 Dec 2003 3:56] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

create table test (`enum_column` enum('1','0') default NULL);
insert into test values ('0');
select * from test;
+-------------+
| enum_column |
+-------------+
| 0           |
+-------------+

 
insert into test values (0);
select * from test;
+-------------+
| enum_column |
+-------------+
| 0           |
|             |
+-------------+

What happens here is caused by the fact that ENUM list of allowed values conists of strings '0', '1' not numbers 0, 1

When you use 'N' it is inserted as such. When you use N MySQL inserts value-with-index-N. When N is out of range empty value is entered.

Here is one more test:

create table test (`enum_column` enum('2','0','1') default NULL);
insert into test values ('0');

select * from test;
+-------------+
| enum_column |
+-------------+
| 0           |

update test set enum_column='1';
 
select * from test;
+-------------+
| enum_column |
+-------------+
| 1           |

update test set enum_column=1;
 
select * from test;
+-------------+
| enum_column |
+-------------+
| 2           |

Because column is defined as ENUM('2', '0', '1')
value with idnex 1 is '2' not '1'

update test set enum_column=0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
select * from test;
+-------------+
| enum_column |
+-------------+
|             |
+-------------+

0 is out of range. Note that MySQL reports
 Warnings: 1