EVOLUTION-MANAGER

Edit File: strict.result

set @org_mode=@@sql_mode; set @@sql_mode='ansi,traditional'; select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (col1 date); INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29'); INSERT INTO t1 VALUES('0000-10-31'); INSERT INTO t1 VALUES('2004-0-31'); ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 2 INSERT INTO t1 VALUES('2004-10-0'); ERROR 22007: Incorrect date value: '2004-10-0' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-09-31'); ERROR 22007: Incorrect date value: '2004-09-31' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-32'); ERROR 22007: Incorrect date value: '2004-10-32' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2003-02-29'); ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-13-15'); ERROR 22007: Incorrect date value: '2004-13-15' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES ('59'); ERROR 22007: Incorrect date value: '59' for column `test`.`t1`.`col1` at row 1 set @@sql_mode='STRICT_ALL_TABLES'; INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31'); set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; INSERT INTO t1 VALUES('2004-0-30'); ERROR 22007: Incorrect date value: '2004-0-30' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 2 INSERT INTO t1 VALUES('0000-00-00'); INSERT IGNORE INTO t1 VALUES('2004-0-29'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; INSERT INTO t1 VALUES('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 INSERT IGNORE INTO t1 VALUES('0000-00-00'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('2004-0-30'); INSERT INTO t1 VALUES ('2004-2-30'); ERROR 22007: Incorrect date value: '2004-2-30' for column `test`.`t1`.`col1` at row 1 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; INSERT INTO t1 VALUES ('2004-2-30'); set @@sql_mode='ansi,traditional'; INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00'); Warnings: Warning 1265 Data truncated for column 'col1' at row 2 Warning 1264 Out of range value for column 'col1' at row 3 select * from t1; col1 2004-01-01 2004-02-29 0000-10-31 2004-01-02 2004-01-03 2004-00-31 2004-01-04 0000-00-00 0000-00-00 0000-00-00 2004-00-30 2004-02-30 2004-02-29 0000-00-00 0000-00-00 drop table t1; set @@sql_mode='strict_trans_tables'; CREATE TABLE t1 (col1 date) engine=myisam; INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); Warnings: Warning 1265 Data truncated for column 'col1' at row 2 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT INTO t1 VALUES ('2003-02-29'); ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 INSERT ignore INTO t1 VALUES('2003-02-30'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; INSERT ignore INTO t1 VALUES('2003-02-31'); select * from t1; col1 2004-01-02 0000-00-00 2004-01-03 0000-00-00 2004-01-04 0000-00-00 2003-02-31 drop table t1; set @@sql_mode='strict_trans_tables'; CREATE TABLE t1 (col1 date) engine=innodb; INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 2 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT INTO t1 VALUES ('2003-02-29'); ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 INSERT ignore INTO t1 VALUES('2003-02-30'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; INSERT ignore INTO t1 VALUES('2003-02-31'); select * from t1; col1 0000-00-00 2004-01-04 0000-00-00 2003-02-31 drop table t1; set @@sql_mode='ansi,traditional'; CREATE TABLE t1 (col1 datetime); INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); INSERT INTO t1 VALUES('0000-10-31 15:30:00'); INSERT INTO t1 VALUES('2004-0-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-0 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-09-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2003-02-29 15:30:00'); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('0000-00-00 15:30:00'); ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES ('59'); ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`col1` at row 1 select * from t1; col1 2004-10-31 15:30:00 2004-02-29 15:30:00 0000-10-31 15:30:00 drop table t1; CREATE TABLE t1 (col1 timestamp); INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); INSERT INTO t1 VALUES('0000-10-31 15:30:00'); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-0-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-0 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-09-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2003-02-29 15:30:00'); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-13-15 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-02-29 25:30:00'); ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-02-29 15:65:00'); ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-02-29 15:31:61'); ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('0000-00-00 15:30:00'); ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('0000-00-00 00:00:00'); ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`col1` at row 1 INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT INTO t1 VALUES ('59'); ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`col1` at row 1 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; INSERT INTO t1 VALUES('2004-0-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-0 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-10-32 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('2004-02-30 15:30:04'); ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 VALUES('0000-00-00 00:00:00'); set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; INSERT INTO t1 VALUES('0000-00-00 00:00:00'); set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; INSERT INTO t1 VALUES('0000-00-00 00:00:00'); ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`col1` at row 1 set @@sql_mode='ansi,traditional'; SELECT * FROM t1; col1 2004-10-31 15:30:00 2004-02-29 15:30:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 DROP TABLE t1; CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); Warnings: Note 1265 Data truncated for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col2` at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col2` at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col3` at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col3` at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date drop table t1; CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); ERROR 22007: Incorrect datetime value: '2004-0-10' INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); ERROR 22007: Incorrect datetime value: '2004-0-10' INSERT INTO t1 (col1) VALUES('2004-0-10'); ERROR 22007: Incorrect date value: '2004-0-10' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0); SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; MOD(col1,0) NULL NULL Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT INTO t1 (col1) VALUES(-129); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(128); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(256); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES('-129'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES('128'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES('-1'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES('256'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES(128.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1.0); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(256.0); ERROR 22003: Out of range value for column 'col2' at row 1 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1; MOD(col1,0) NULL Warnings: Warning 1365 Division by 0 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; ERROR 22003: Out of range value for column 'col1' at row 1 UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0; ERROR 22003: Out of range value for column 'col2' at row 3 UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; INSERT INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 set @@sql_mode='ansi,traditional'; SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; MOD(col1,0) NULL NULL Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 set @@sql_mode='ansi'; INSERT INTO t1 values (1/0,1/0); set @@sql_mode='ansi,traditional'; INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 -128 0 0 NULL 127 255 -128 0 0 NULL 127 255 -128 0 0 NULL 127 255 NULL NULL 2 NULL NULL NULL NULL NULL -128 0 127 255 -128 0 127 255 DROP TABLE t1; CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED); INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0); INSERT INTO t1 (col1) VALUES(-32769); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(32768); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(65536); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES('-32769'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES('32768'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES('-1'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES('65536'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES(-32769.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(32768.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1.0); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(65536.0); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; ERROR 22003: Out of range value for column 'col1' at row 1 UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; ERROR 22003: Out of range value for column 'col2' at row 3 UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 -32768 0 0 NULL 32767 65535 -32768 0 32767 65535 -32768 0 32767 65535 2 NULL NULL NULL -32768 0 32767 65535 -32768 0 32767 65535 -32768 0 32767 65535 DROP TABLE t1; CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED); INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0); INSERT INTO t1 (col1) VALUES(-8388609); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(8388608); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(16777216); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES('-8388609'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES('8388608'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES('-1'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES('16777216'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES(-8388609.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(8388608.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1.0); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(16777216.0); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; ERROR 22003: Out of range value for column 'col1' at row 1 UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; ERROR 22003: Out of range value for column 'col2' at row 3 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 -8388608 0 0 NULL 8388607 16777215 -8388608 0 8388607 16777215 -8388608 0 8388607 16777215 2 NULL NULL NULL -8388608 0 8388607 16777215 -8388608 0 8388607 16777215 -8388608 0 8388607 16777215 DROP TABLE t1; CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED); INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0); INSERT INTO t1 (col1) VALUES(-2147483649); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(2147643648); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(4294967296); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES('-2147483649'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES('2147643648'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES('-1'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES('4294967296'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES(-2147483649.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(2147643648.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1.0); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(4294967296.0); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; ERROR 22003: Out of range value for column 'col1' at row 1 UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0; ERROR 22003: Out of range value for column 'col2' at row 3 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 -2147483648 0 0 NULL 2147483647 4294967295 -2147483648 0 2147483647 4294967295 -2147483648 0 2147483647 4294967295 2 NULL NULL NULL -2147483648 0 2147483647 4294967295 -2147483648 0 2147483647 4294967295 -2147483648 0 2147483647 4294967295 DROP TABLE t1; CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED); INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615); INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); INSERT INTO t1 (col1) VALUES(-9223372036854775809); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(9223372036854775808); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(18446744073709551616); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES('-9223372036854775809'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES('9223372036854775808'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES('-1'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES('18446744073709551616'); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES(-9223372036854785809.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(9223372036854785808.0); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(-1.0); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(18446744073709551616.0); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0,1/0); Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 Warning 1264 Out of range value for column 'col2' at row 2 UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; col1 col2 -9223372036854775808 0 0 NULL 9223372036854775807 18446744073709551615 -9223372036854775808 0 9223372036854775807 18446744073709551615 -9223372036854774000 0 9223372036854775700 1844674407370954000 2 NULL NULL NULL -9223372036854775808 0 9223372036854775807 18446744073709551615 -9223372036854775808 0 9223372036854775807 18446744073709551615 -9223372036854775808 0 9223372036854775807 18446744073709551615 DROP TABLE t1; CREATE TABLE t1 (col1 NUMERIC(4,2)); INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); Warnings: Note 1265 Data truncated for column 'col1' at row 2 Note 1265 Data truncated for column 'col1' at row 5 INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); Warnings: Note 1265 Data truncated for column 'col1' at row 2 Note 1265 Data truncated for column 'col1' at row 4 INSERT INTO t1 VALUES (101.55); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES (101); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES (-101.55); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES (1010.55); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES (1010); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('101.55'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('101'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-101.55'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-1010.55'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E+1'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E'); ERROR 01000: Data truncated for column 'col1' at row 1 UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; ERROR 22003: Out of range value for column 'col1' at row 6 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect decimal value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect decimal value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0); Warnings: Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES(1000),(-1000); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 INSERT IGNORE INTO t1 VALUES('1000'),('-1000'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 UPDATE IGNORE t1 SET col1=1/NULL where col1=0; SELECT * FROM t1; col1 10.55 10.56 NULL -10.55 -10.56 11.00 10.00 10.55 10.56 -10.55 -10.56 11.00 10.00 2.00 NULL 99.99 -99.99 99.99 -99.99 99.99 -99.99 DROP TABLE t1; CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED); INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38); INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38'); INSERT INTO t1 (col1) VALUES (3E-46); INSERT INTO t1 (col1) VALUES (+3.4E+39); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES (-1.1E-3); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES ('+3.4E+39'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('-1.1E-3'); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; ERROR 22003: Out of range value for column 'col1' at row 2 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES (1/0); Warnings: Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 SELECT * FROM t1; col1 col2 -1.1e-37 0 3.4e38 3.4e38 -1.1e-37 0 3.4e38 3.4e38 0 NULL 2 NULL NULL NULL 3.40282e38 0 3.40282e38 0 DROP TABLE t1; CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308); INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308'); INSERT INTO t1 (col1) VALUES (-2.2E-330); INSERT INTO t1 (col1) VALUES (+1.7E+309); Got one of the listed errors INSERT INTO t1 (col2) VALUES (-1.1E-3); ERROR 22003: Out of range value for column 'col2' at row 1 INSERT INTO t1 (col1) VALUES ('+1.8E+309'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('-1.2E-3'); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; ERROR 22003: DOUBLE value is out of range in '"test"."t1"."col1" * 5000' UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); ERROR 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`col1` at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) values (1/0); Warnings: Warning 1365 Division by 0 INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309); ERROR 22007: Illegal double '1.9E+309' value found during parsing INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 SELECT * FROM t1; col1 col2 -2.2e-307 0 1e-303 0 1.7e308 1.7e308 -2.2e-307 0 -2e-307 0 1.7e308 1.7e308 0 NULL 2 NULL NULL NULL 1.7976931348623157e308 0 DROP TABLE t1; CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); INSERT INTO t1 (col1) VALUES ('hellobob'); ERROR 22001: Data too long for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('hellobob'); ERROR 22001: Data too long for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES ('hello '); Warnings: Note 1265 Data truncated for column 'col2' at row 1 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; ERROR 22001: Data too long for column 'col1' at row 2 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; ERROR 22001: Data too long for column 'col2' at row 2 INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 Warning 1265 Data truncated for column 'col2' at row 1 UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; Warnings: Warning 1265 Data truncated for column 'col2' at row 2 SELECT * FROM t1; col1 col2 hello hello he hellot hello hello NULL hello hello hellob DROP TABLE t1; CREATE TABLE t1 (col1 enum('red','blue','green')); INSERT INTO t1 VALUES ('red'),('blue'),('green'); INSERT INTO t1 (col1) VALUES ('yellow'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('redd'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT INTO t1 VALUES (''); ERROR 01000: Data truncated for column 'col1' at row 1 UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; ERROR 01000: Data truncated for column 'col1' at row 3 INSERT IGNORE INTO t1 VALUES ('yellow'); Warnings: Warning 1265 Data truncated for column 'col1' at row 1 UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; Warnings: Warning 1265 Data truncated for column 'col1' at row 2 SELECT * FROM t1; col1 red green DROP TABLE t1; CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL); INSERT INTO t1 VALUES (100, 'hello', '2004-08-20'); INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21'); INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); ERROR 23000: Column 'col1' cannot be null INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); ERROR 23000: Column 'col2' cannot be null INSERT INTO t1 VALUES (103,'',NULL); ERROR 23000: Column 'col3' cannot be null UPDATE t1 SET col1=NULL WHERE col1 =100; ERROR 23000: Column 'col1' cannot be null UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; ERROR 23000: Column 'col2' cannot be null UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; ERROR 23000: Column 'col2' cannot be null INSERT IGNORE INTO t1 values (NULL,NULL,NULL); Warnings: Warning 1048 Column 'col1' cannot be null Warning 1048 Column 'col2' cannot be null Warning 1048 Column 'col3' cannot be null SELECT * FROM t1; col1 col2 col3 100 hello 2004-08-20 101 hell2 2004-08-21 0 0000-00-00 DROP TABLE t1; CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE "t1" ( "col1" int(11) NOT NULL DEFAULT 99, "col2" char(6) NOT NULL ) INSERT INTO t1 VALUES (1, 'hello'); INSERT INTO t1 (col2) VALUES ('hello2'); INSERT INTO t1 (col2) VALUES (NULL); ERROR 23000: Column 'col2' cannot be null INSERT INTO t1 (col1) VALUES (2); ERROR HY000: Field 'col2' doesn't have a default value INSERT INTO t1 VALUES(default(col1),default(col2)); ERROR HY000: Field 'col2' doesn't have a default value INSERT INTO t1 (col1) SELECT 1; ERROR HY000: Field 'col2' doesn't have a default value INSERT INTO t1 SELECT 1,NULL; ERROR 23000: Column 'col2' cannot be null INSERT IGNORE INTO t1 values (NULL,NULL); Warnings: Warning 1048 Column 'col1' cannot be null Warning 1048 Column 'col2' cannot be null INSERT IGNORE INTO t1 (col1) values (3); Warnings: Warning 1364 Field 'col2' doesn't have a default value INSERT IGNORE INTO t1 () values (); Warnings: Warning 1364 Field 'col2' doesn't have a default value SELECT * FROM t1; col1 col2 1 hello 99 hello2 0 3 99 DROP TABLE t1; set sql_mode='traditional'; create table t1 (charcol char(255), varcharcol varchar(255), binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, tinyblobcol tinyblob); insert into t1 (charcol) values (repeat('x',256)); ERROR 22001: Data too long for column 'charcol' at row 1 insert into t1 (varcharcol) values (repeat('x',256)); ERROR 22001: Data too long for column 'varcharcol' at row 1 insert into t1 (binarycol) values (repeat('x',256)); ERROR 22001: Data too long for column 'binarycol' at row 1 insert into t1 (varbinarycol) values (repeat('x',256)); ERROR 22001: Data too long for column 'varbinarycol' at row 1 insert into t1 (tinytextcol) values (repeat('x',256)); ERROR 22001: Data too long for column 'tinytextcol' at row 1 insert into t1 (tinyblobcol) values (repeat('x',256)); ERROR 22001: Data too long for column 'tinyblobcol' at row 1 select * from t1; charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol drop table t1; set sql_mode='traditional'; create table t1 (col1 datetime); insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc' insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); ERROR HY000: Incorrect time value: 'abc' for function str_to_date set sql_mode=''; insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); Warnings: Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc' insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); Warnings: Warning 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_date insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); Warnings: Warning 1411 Incorrect time value: '22:22:33 AM' for function str_to_date insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); Warnings: Warning 1411 Incorrect time value: 'abc' for function str_to_date insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); select * from t1; col1 2004-10-31 15:30:00 NULL NULL NULL 2004-10-31 15:30:00 2004-12-12 11:22:33 2004-12-12 10:22:59 set sql_mode='traditional'; select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; count(*) 7 Warnings: Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date drop table t1; create table t1 (col1 char(3), col2 integer); insert into t1 (col1) values (cast(1000 as char(3))); ERROR 22007: Truncated incorrect CHAR(3) value: '1000' insert into t1 (col1) values (cast(1000E+0 as char(3))); ERROR 22007: Truncated incorrect CHAR(3) value: '1000' insert into t1 (col1) values (cast(1000.0 as char(3))); ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' insert into t1 (col2) values (cast('abc' as signed integer)); ERROR 22007: Truncated incorrect INTEGER value: 'abc' insert into t1 (col2) values (10E+0 + 'a'); ERROR 22007: Truncated incorrect DOUBLE value: 'a' insert into t1 (col2) values (cast('10a' as unsigned integer)); ERROR 22007: Truncated incorrect INTEGER value: '10a' insert into t1 (col2) values (cast('10' as unsigned integer)); insert into t1 (col2) values (cast('10' as signed integer)); insert into t1 (col2) values (10E+0 + '0 '); Warnings: Note 1292 Truncated incorrect DOUBLE value: '0 ' select * from t1; col1 col2 NULL 10 NULL 10 NULL 10 drop table t1; create table t1 (col1 date, col2 datetime, col3 timestamp); insert into t1 values (0,0,0); ERROR 22007: Incorrect date value: '0' for column `test`.`t1`.`col1` at row 1 insert into t1 values (0.0,0.0,0.0); ERROR 22007: Incorrect date value: '0.0' for column `test`.`t1`.`col1` at row 1 insert into t1 (col1) values (convert('0000-00-00',date)); ERROR 22007: Incorrect datetime value: '0000-00-00' insert into t1 (col1) values (cast('0000-00-00' as date)); ERROR 22007: Incorrect datetime value: '0000-00-00' set sql_mode='no_zero_date'; insert into t1 values (0,0,0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1265 Data truncated for column 'col3' at row 1 insert into t1 values (0.0,0.0,0.0); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col2' at row 1 Warning 1265 Data truncated for column 'col3' at row 1 drop table t1; set sql_mode='traditional'; create table t1 (col1 date); insert ignore into t1 values ('0000-00-00'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 insert into t1 select * from t1; ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 insert ignore into t1 values ('0000-00-00'); Warnings: Warning 1264 Out of range value for column 'col1' at row 1 insert ignore into t1 (col1) values (cast('0000-00-00' as date)); Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' insert into t1 select * from t1; ERROR 22007: Incorrect date value: '0000-00-00' for column ``.`(temporary)`.`col1` at row 1 alter table t1 modify col1 datetime; ERROR 22007: Incorrect datetime value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 alter ignore table t1 modify col1 datetime; Warnings: Warning 1264 Out of range value for column 'col1' at row 1 Warning 1264 Out of range value for column 'col1' at row 2 insert into t1 select * from t1; ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column ``.`(temporary)`.`col1` at row 1 select * from t1; col1 0000-00-00 00:00:00 0000-00-00 00:00:00 NULL drop table t1; create table t1 (col1 tinyint); drop procedure if exists t1; Warnings: Note 1305 PROCEDURE test.t1 does not exist create procedure t1 () begin declare exit handler for sqlexception select'a'; insert into t1 values (200); end;| call t1(); a a select * from t1; col1 drop procedure t1; drop table t1; set sql_mode=@org_mode; SET @@sql_mode = 'traditional'; CREATE TABLE t1 (i int not null); INSERT INTO t1 VALUES (); ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT); ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT(i)); ERROR HY000: Field 'i' doesn't have a default value ALTER TABLE t1 ADD j int; INSERT INTO t1 SET j = 1; ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 SET j = 1, i = DEFAULT; ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 SET j = 1, i = DEFAULT(i); ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT,1); ERROR HY000: Field 'i' doesn't have a default value DROP TABLE t1; SET @@sql_mode = ''; CREATE TABLE t1 (i int not null); INSERT INTO t1 VALUES (); Warnings: Warning 1364 Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT); Warnings: Warning 1364 Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT(i)); ERROR HY000: Field 'i' doesn't have a default value ALTER TABLE t1 ADD j int; INSERT INTO t1 SET j = 1; Warnings: Warning 1364 Field 'i' doesn't have a default value INSERT INTO t1 SET j = 1, i = DEFAULT; Warnings: Warning 1364 Field 'i' doesn't have a default value INSERT INTO t1 SET j = 1, i = DEFAULT(i); ERROR HY000: Field 'i' doesn't have a default value INSERT INTO t1 VALUES (DEFAULT,1); Warnings: Warning 1364 Field 'i' doesn't have a default value DROP TABLE t1; set @@sql_mode='traditional'; create table t1(a varchar(65537)); ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead create table t1(a varbinary(65537)); ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead set @@sql_mode='traditional'; create table t1(a int, b date not null); alter table t1 modify a bigint unsigned not null; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned NOT NULL, `b` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; set @@sql_mode='traditional'; create table t1 (d date); insert into t1 values ('2000-10-00'); ERROR 22007: Incorrect date value: '2000-10-00' for column `test`.`t1`.`d` at row 1 insert into t1 values (1000); ERROR 22007: Incorrect date value: '1000' for column `test`.`t1`.`d` at row 1 insert into t1 values ('2000-10-01'); update t1 set d = 1100; ERROR 22007: Incorrect date value: '1100' for column `test`.`t1`.`d` at row 1 select * from t1; d 2000-10-01 drop table t1; set @@sql_mode='traditional'; create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); alter table t1 add primary key(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1(a int, b timestamp not null default 20050102030405); alter table t1 add primary key(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; set @@sql_mode='traditional'; create table t1(a bit(2)); insert into t1 values(b'101'); ERROR 22001: Data too long for column 'a' at row 1 select * from t1; a drop table t1; set sql_mode='traditional'; create table t1 (date date not null); create table t2 select date from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `date` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t2,t1; set @@sql_mode= @org_mode; set @@sql_mode='traditional'; create table t1 (i int) comment '123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*123456789*'; ERROR HY000: Comment for table 't1' is too long (max = 2048) create table t1 ( i int comment '123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*'); ERROR HY000: Comment for field 'i' is too long (max = 1024) set @@sql_mode= @org_mode; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR create table t1 (i int comment '123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789*'); Warnings: Warning 1629 Comment for field 'i' is too long (max = 1024) select column_name, column_comment from information_schema.columns where table_schema = 'test' and table_name = 't1'; column_name column_comment i 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*123456789* 123456789*123456789*123456789*12345 drop table t1; set names utf8; create table t1 (i int) comment '123456789*123456789*123456789*123456789*123456789*123456789*'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*' drop table t1; CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( `f1` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן' DROP TABLE t3; set sql_mode= 'traditional'; create table t1(col1 tinyint, col2 tinyint unsigned, col3 smallint, col4 smallint unsigned, col5 mediumint, col6 mediumint unsigned, col7 int, col8 int unsigned, col9 bigint, col10 bigint unsigned); insert into t1(col1) values('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col1` at row 1 insert into t1(col2) values('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col2` at row 1 insert into t1(col3) values('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col3` at row 1 insert into t1(col4) values('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col4` at row 1 insert into t1(col5) values('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col5` at row 1 insert into t1(col6) values('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col6` at row 1 insert into t1(col7) values('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col7` at row 1 insert into t1(col8) values('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col8` at row 1 insert into t1(col9) values('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col9` at row 1 insert into t1(col10) values('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col10` at row 1 drop table t1; set sql_mode='traditional'; create table t1(a year); insert into t1 values ('-'); ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`a` at row 1 insert into t1 values ('+'); ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`a` at row 1 insert into t1 values (''); ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`a` at row 1 insert into t1 values ('2000a'); ERROR 01000: Data truncated for column 'a' at row 1 insert into t1 values ('2E3x'); ERROR 01000: Data truncated for column 'a' at row 1 drop table t1; set sql_mode='traditional'; create table t1 (f1 set('a','a')); ERROR HY000: Column 'f1' has duplicated value 'a' in SET create table t1 (f1 enum('a','a')); ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM set @@sql_mode='NO_ZERO_DATE'; create table t1(a datetime not null); select count(*) from t1 where a is null; count(*) 0 drop table t1; End of 5.0 tests # # Start of 5.6 tests # # # WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME # # # STR_TO_DATE with NO_ZERO_DATE did not return NULL (with warning) # in get_date(). Only did in val_str() and val_int(). SET sql_mode='NO_ZERO_DATE'; SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0','%Y')+1, STR_TO_DATE('0000','%Y')+1; STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0','%Y')+1 STR_TO_DATE('0000','%Y')+1 2001-00-00 2001-00-00 20010001 20000001 NULL Warnings: Warning 1411 Incorrect datetime value: '0000' for function str_to_date SET sql_mode='NO_ZERO_IN_DATE'; SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1; STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0000','%Y')+1 NULL NULL NULL 1 Warnings: Warning 1411 Incorrect datetime value: '2001' for function str_to_date Warning 1411 Incorrect datetime value: '2001' for function str_to_date Warning 1411 Incorrect datetime value: '2001' for function str_to_date # # End of 5.6 tests #