Bug #107612 STRICT sql-mode does not always work with ENUM fields
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.
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.
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.