Bug #107612 STRICT sql-mode does not always work with ENUM fields
Submitted: 20 Jun 17:03 Modified: 21 Jun 7:08
Reporter: C. Deniz Akyuz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.28, 5.7.38, 8.0.29 OS:MacOS
Assigned to: CPU Architecture:ARM
Tags: sql-mode, strict, STRICT sql mode, strict_all_tables, strict_trans_tables

[20 Jun 17:03] C. Deniz Akyuz
Description:
Setting sql-mode to STRICT via the the flags STRICT_ALL_TABLES or STRICT_TRANS_TABLES ought to fail for invalid values (instead of changing them silently to valid 0s or blanks).

Although sql-mode is set to STRICT, the insertion of '0' into an ENUM field succeeds, with a resulting value of '' and enum-index of 0. 

Inserting 0 or '' both fail as expected when sql-mode is set to STRICT. 

Note: the ENUM field in the example (obviously) does not contain the elements '' or '0' - as also emphasized in the enum-index being 0. 

How to repeat:
SET @@SESSION.sql_mode := CONCAT('STRICT_ALL_TABLES,', @@SESSION.sql_mode);

DROP TABLE IF EXISTS TEST;

CREATE TABLE TEST
(   id   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
  , type ENUM('aaaaa', 'bbbbb', 'ccccc') NOT NULL 
);

INSERT INTO TEST (id, type) VALUES(NULL, 'aaaaa');
INSERT INTO TEST (id, type) VALUES(NULL, 2); -- 2nd enum value 'bbbbb'
INSERT INTO TEST (id, type) VALUES(NULL, 'ccccc');
INSERT INTO TEST (id, type) VALUES(NULL, '0'); -- Should fail but it does not!

SELECT t.*, CONCAT('[',t.type,']') type_b, CAST(t.type AS SIGNED) enum_index FROM TEST t;

/**
id	type	type_b	enum_index
1	aaaaa	[aaaaa]	1
2	bbbbb	[bbbbb]	2
3	ccccc	[ccccc]	3
4			[]		0	<- blank ENUM with enum-index value 0 inserted  
**/

-- -----
-- These 3 examples below show the expected behavior for STRICT sql-mode

INSERT INTO TEST (id, type) VALUES(NULL, ''); -- Should fail and it does fail!
-- 1265 - Data truncated for column 'type' at row 1

INSERT INTO TEST (id, type) VALUES(NULL, 0); -- Should fail and it does fail!
-- 1265 - Data truncated for column 'type' at row 1

INSERT INTO TEST (id, type) VALUES(NULL, 'xx'); -- Should fail and it does fail!
-- 1265 - Data truncated for column 'type' at row 1

-- -----

Suggested fix:
Inserting a value of '0' into an ENUM field should fail when sql-mode is set to STRICT. It already does fail as expected when inserting 0, or '', or any other invalid element.
[21 Jun 7:08] MySQL Verification Team
Hello C. Deniz Akyuz,

Thank you for the report and test case.

regards,
Umesh