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.
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.