Bug #43880 If strict SQL mode enabled, attempts to insert invalid ENUM values don't error

• Submitted: 26 Mar 2009 11:54
• Modified: 12 Nov 2013 8:21
• Reporter: James b
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.51a-3ubuntu5.4, 5.0.79, 5.1.32, 6.0.9
• OS: Linux
• CPU Architecture: Any

[26 Mar 2009 11:54] James b

Description:
(See http://forums.mysql.com/read.php?20,249884,249884#msg-249884)

According to my reading of the manual, specifically 
http://dev.mysql.com/doc/refman/5.0/en/enum.html
"If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error. "

Shouldn't this insert be failing with errors?

Thanks in advance, James

How to repeat:

Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

mysql>
mysql> create database teststrict;
Query OK, 1 row affected (0.00 sec)

mysql> use teststrict;
Database changed
mysql> create table test (
-> ID Integer not null primary key,
-> test enum('1','2','3') not null
-> ) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into test set ID=1, test='0';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| ID | test |
+----+------+
| 1 | |
+----+------+
1 row in set (0.00 sec)

[26 Mar 2009 12:45] Valeriy Kravchuk

Thank you for the bug report. Verified just as described with 5.0.79 and 5.1.32:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.32-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
Query OK, 0 rows affected (0.06 sec)

mysql> create table test (
    -> ID Integer not null primary key,
    -> test enum('1','2','3') not null
    -> ) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test set ID=1, test='0';
Query OK, 1 row affected (0.09 sec)

mysql> select * from test;
+----+------+
| ID | test |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)

Looks like '0' is treated as index actually. Any other invalid ENUM value is not accepted:

mysql> insert into test set ID=2, test='4';
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql> select * from test;
+----+------+
| ID | test |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)

[12 Nov 2013 8:21] Erlend Dahl

[5 Jun 2013 1:56] Raghav Kapoor:

This behaviour is documented. See
 
http://dev.mysql.com/doc/refman/5.7/en/enum.html#enum-indexes

When we try to store '0' in an enum defined as {'1', '2', '3'} '0' is treated as an Index value for empty string. An empty string will be stored when we give '0'. It means we can use SELECT * FROM tbl_name WHERE enum_col=0; to find rows into which invalid ENUM values were assigned. If we insert any other value in enum like '4' or '5' or '-1' or '-2' etc in STRICT MODE, we get an error. Therefore, IMO it is not a bug.