Enforce error on wrong enum values?

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?

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

1 Like