MySQL Bugs: #10120: NOT NULL ENUM type can be passed any arbitrary value, leaving field unset

Bug #10120	NOT NULL ENUM type can be passed any arbitrary value, leaving field unset
Submitted:	24 Apr 2005 7:57	Modified:	24 Apr 2005 15:16
Reporter:	Hal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	5.0.4	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:

A field defined as an enumerated type is supposed to have one of the values listed in the definition.  It can also be null if there is no NOT NULL modifier in the definition for that field.  What is actually happening is that an ENUM type defined as NOT NULL is permitting arbitrary non-null values to be passed in INSERT and UPDATE statements, which violates the rules as defined in the mysql documentation.  Internally, the value is left empty in the database field. 

How to repeat:
create table custdata (
custname varchar(20) primary key,
custtype enum ('S2','S1','S4') not null
);

insert into custdata (custname,custtype) values ('monroe','any kind of junk');
update custdata set custtype = -5.37 where custname = 'monroe';

I expect an error indicating that invalid values were being passed in, but I did not get any. The resulting field in the database has a value that is unset.  (Note: This is different from a value of null ("NULL"), which would not be allowed anyway since the field is defined as NOT NULL in the table definition, so at least that rule is not being violated.)

Again, I am basing my reaction on the documentation provided on the mysql website.

Suggested fix:
Arbitrary values should not be permitted to be passed to ENUM types in INSERT and UPDATE statements.  Only the enumerated values should be allowed, and possibly the null value ("NULL") if the field is defined without the NOT NULL restriction.

Trying to set a wrong enum value gives you a warning:

mysql> insert into custdata (custname,custtype) values ('monroe','any kind of junk');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'custtype' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

You can request that an error is raised instead of a warning by using the "traditional" or "strict" sql_mode settings, see
http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Thank you for the pointer to that information; it was not obvious.  I added sql-mode = TRADITIONAL to my my.cnf file.

OK, so now when I pass a literal string "NULL" it fails, as expected.  But I can still pass a literal string "0".  mysql accepts it (although the value field is still left empty as before).  I don't think this was the intended behavior, but perhaps you can clear my understanding once again on this point.

Thanks,

Maybe TRADITIONAL is not strict enough?  After reading through the list of possible operating modes (there is a lot to read about this ...), I thought that TRADITIONAL described what I wanted.