[MDEV-30729] Updating with invalid ENUM value seems to pick the last valid one Created: 2023-02-25  Updated: 2023-05-19  Resolved: 2023-03-26

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Nuno Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: 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?

 Comments   
Comment by Sergei Golubchik [ 2023-03-26 ]

Yes, this is what's happening. And no there seems to be no way to switch this behavior off.

Comment by Nuno [ 2023-03-26 ]

Thanks serg.

Is there a way this could be converted to a Feature Request, or do you believe it wouldn't make sense for MariaDB to provide a Fix or a Config for this?

Comment by Sergei Golubchik [ 2023-05-19 ]

This behavior was added more than 20 years ago for LOAD DATA. I might agree that it didn't have to work in INSERT/UPDATE. And it's questionable even for LOAD DATA, because in your case both '6' and '7' would mean the same ENUM value. But we cannot just remove 20yr-old behavior.

I'd say that generally it's better to avoid numbers as ENUM labels, but you already know that.

You can try to use

col TINYINT UNSIGNED NOT NULL DEFAULT 0 CHECK (col < 7)

Comment by Nuno [ 2023-05-19 ]

Thank you serg, I understand.

I don't suggest to change a 20yr-old behavior, but if we could make it a new flag under sql_mode, like the way we have the "strict" flags, that'd be ideal, I believe.

Thank you for the suggestion of the TINYINT with CHECK.
I have *LOADS* of ENUMs like this, and I'm worried how many times in the past I've been caught by this subtle behavior!!

I also have some ENUMs that are mostly numbers, but there are also some options that are letters, e.g. '0','1','2','s','f','12'
These are rare anyway, but I won't be able to move these to TINYINT

Thanks! Good day.

Generated at Thu Feb 08 10:18:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.