Enforce error on wrong enum values?


#1

When I create a table like this:

create table t(e enum ('a','b','c'));

and then run the following INSERT statements:

insert into t(e) values (null);
insert into t(e) values ('a');
insert into t(e) values ('x');

the table ends up having 3 rows, where 'x' was substituted with the empty string. I would in this case actually have expected an error as many other RDBMS systems supporting enums do. MySQL 8.0 and 5.7 throw an exception (Error: WARN_DATA_TRUNCATED: Data truncated for column 'e' at row 1), whereas MySQL 5.6 and 5.5 show the same behavior as MemSQL.

Please also note that I also tested with set global data_conversion_compatibility_level = '6.5', but that didn’t make any difference.

Is there any other setting to get the database to throw an exception for illegal enum values?


#2

Hello. Thanks for pointing this out.

Currently, we allow invalid enum values to be inserted into a column. In a future MemSQL version, we will add a check for this case and generate an error when it occurs.

Scott