Bug #107612 STRICT sql-mode does not always work with ENUM fields

Submitted: 20 Jun 2022 17:03 Modified: 21 Jun 2022 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 2022 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 2022 7:08] MySQL Verification Team
Hello C. Deniz Akyuz,

Thank you for the report and test case.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.