Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
None
Description
If I have a column like this:
`col` ENUM('0','1','2','3','4','5','6') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0'
|
And then I do:
update table set col='7' WHERE id=1234;
|
It appears the column gets updated with the value '6'.
I'm very surprised (and only noticed this bug in my application now!!), because I thought the point of ENUMs was also to validate that the input is correct.
I have these in my.cnf:
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY"
|
#old_mode = "" # default: "UTF8_IS_UTF8MB3"
|
innodb_strict_mode = ON
|
Is this the expected behavior?
I thought ENUMs used to give an error if we try to update with an invalid value, in previous MariaDB versions! (like 10.5 or so)
Thank you very much.
UPDATE
Actually, it does seem that if I put some completely incorrect value, like:
update table set col='asdasd' WHERE id=1234;
|
It does give me the expected error:
#1265 - Data truncated for column 'ghosted' at row 1
|
What I suspect is that by providing the value "7", and MariaDB realizes that "7" (as a string) isn't a valid value, it somehow converts it to (int), and then picks up the string value "6" because it's the 7th element in the ENUM list of valid values.
Is this what is happening?
And if so, is there a way I can force MariaDB to be 100% strict and not accept numeric ENUM updates like this, and therefore fail in these scenarios?