1set @org_mode=@@sql_mode; 2set @@sql_mode='ansi,traditional'; 3select @@sql_mode; 4@@sql_mode 5REAL_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 6DROP TABLE IF EXISTS t1, t2; 7CREATE TABLE t1 (col1 date); 8INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29'); 9INSERT INTO t1 VALUES('0000-10-31'); 10INSERT INTO t1 VALUES('2004-0-31'); 11ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 1 12INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); 13ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 2 14INSERT INTO t1 VALUES('2004-10-0'); 15ERROR 22007: Incorrect date value: '2004-10-0' for column `test`.`t1`.`col1` at row 1 16INSERT INTO t1 VALUES('2004-09-31'); 17ERROR 22007: Incorrect date value: '2004-09-31' for column `test`.`t1`.`col1` at row 1 18INSERT INTO t1 VALUES('2004-10-32'); 19ERROR 22007: Incorrect date value: '2004-10-32' for column `test`.`t1`.`col1` at row 1 20INSERT INTO t1 VALUES('2003-02-29'); 21ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 22INSERT INTO t1 VALUES('2004-13-15'); 23ERROR 22007: Incorrect date value: '2004-13-15' for column `test`.`t1`.`col1` at row 1 24INSERT INTO t1 VALUES('0000-00-00'); 25ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 26INSERT INTO t1 VALUES ('59'); 27ERROR 22007: Incorrect date value: '59' for column `test`.`t1`.`col1` at row 1 28set @@sql_mode='STRICT_ALL_TABLES'; 29INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31'); 30set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; 31INSERT INTO t1 VALUES('2004-0-30'); 32ERROR 22007: Incorrect date value: '2004-0-30' for column `test`.`t1`.`col1` at row 1 33INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); 34ERROR 22007: Incorrect date value: '2004-0-31' for column `test`.`t1`.`col1` at row 2 35INSERT INTO t1 VALUES('0000-00-00'); 36INSERT IGNORE INTO t1 VALUES('2004-0-29'); 37Warnings: 38Warning 1265 Data truncated for column 'col1' at row 1 39set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; 40INSERT INTO t1 VALUES('0000-00-00'); 41ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 42INSERT IGNORE INTO t1 VALUES('0000-00-00'); 43Warnings: 44Warning 1264 Out of range value for column 'col1' at row 1 45INSERT INTO t1 VALUES ('2004-0-30'); 46INSERT INTO t1 VALUES ('2004-2-30'); 47ERROR 22007: Incorrect date value: '2004-2-30' for column `test`.`t1`.`col1` at row 1 48set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 49INSERT INTO t1 VALUES ('2004-2-30'); 50set @@sql_mode='ansi,traditional'; 51INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00'); 52Warnings: 53Warning 1265 Data truncated for column 'col1' at row 2 54Warning 1264 Out of range value for column 'col1' at row 3 55select * from t1; 56col1 572004-01-01 582004-02-29 590000-10-31 602004-01-02 612004-01-03 622004-00-31 632004-01-04 640000-00-00 650000-00-00 660000-00-00 672004-00-30 682004-02-30 692004-02-29 700000-00-00 710000-00-00 72drop table t1; 73set @@sql_mode='strict_trans_tables'; 74CREATE TABLE t1 (col1 date) engine=myisam; 75INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); 76ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 1 77INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); 78Warnings: 79Warning 1265 Data truncated for column 'col1' at row 2 80INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); 81Warnings: 82Warning 1265 Data truncated for column 'col1' at row 1 83INSERT INTO t1 VALUES ('2003-02-29'); 84ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 85INSERT ignore INTO t1 VALUES('2003-02-30'); 86Warnings: 87Warning 1265 Data truncated for column 'col1' at row 1 88set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 89INSERT ignore INTO t1 VALUES('2003-02-31'); 90select * from t1; 91col1 922004-01-02 930000-00-00 942004-01-03 950000-00-00 962004-01-04 970000-00-00 982003-02-31 99drop table t1; 100set @@sql_mode='strict_trans_tables'; 101CREATE TABLE t1 (col1 date) engine=innodb; 102INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); 103ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 1 104INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); 105ERROR 22007: Incorrect date value: '2004-13-31' for column `test`.`t1`.`col1` at row 2 106INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); 107Warnings: 108Warning 1265 Data truncated for column 'col1' at row 1 109INSERT INTO t1 VALUES ('2003-02-29'); 110ERROR 22007: Incorrect date value: '2003-02-29' for column `test`.`t1`.`col1` at row 1 111INSERT ignore INTO t1 VALUES('2003-02-30'); 112Warnings: 113Warning 1265 Data truncated for column 'col1' at row 1 114set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 115INSERT ignore INTO t1 VALUES('2003-02-31'); 116select * from t1; 117col1 1180000-00-00 1192004-01-04 1200000-00-00 1212003-02-31 122drop table t1; 123set @@sql_mode='ansi,traditional'; 124CREATE TABLE t1 (col1 datetime); 125INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); 126INSERT INTO t1 VALUES('0000-10-31 15:30:00'); 127INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 128ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 129INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 130ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 131INSERT INTO t1 VALUES('2004-09-31 15:30:00'); 132ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 133INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 134ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 135INSERT INTO t1 VALUES('2003-02-29 15:30:00'); 136ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 137INSERT INTO t1 VALUES('2004-13-15 15:30:00'); 138ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column `test`.`t1`.`col1` at row 1 139INSERT INTO t1 VALUES('0000-00-00 15:30:00'); 140ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column `test`.`t1`.`col1` at row 1 141INSERT INTO t1 VALUES ('59'); 142ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`col1` at row 1 143select * from t1; 144col1 1452004-10-31 15:30:00 1462004-02-29 15:30:00 1470000-10-31 15:30:00 148drop table t1; 149CREATE TABLE t1 (col1 timestamp); 150INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); 151INSERT INTO t1 VALUES('0000-10-31 15:30:00'); 152ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col1` at row 1 153INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 154ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 155INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 156ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 157INSERT INTO t1 VALUES('2004-09-31 15:30:00'); 158ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 159INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 160ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 161INSERT INTO t1 VALUES('2003-02-29 15:30:00'); 162ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 163INSERT INTO t1 VALUES('2004-13-15 15:30:00'); 164ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column `test`.`t1`.`col1` at row 1 165INSERT INTO t1 VALUES('2004-02-29 25:30:00'); 166ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column `test`.`t1`.`col1` at row 1 167INSERT INTO t1 VALUES('2004-02-29 15:65:00'); 168ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column `test`.`t1`.`col1` at row 1 169INSERT INTO t1 VALUES('2004-02-29 15:31:61'); 170ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column `test`.`t1`.`col1` at row 1 171INSERT INTO t1 VALUES('0000-00-00 15:30:00'); 172ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column `test`.`t1`.`col1` at row 1 173INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 174ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`col1` at row 1 175INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00'); 176Warnings: 177Warning 1265 Data truncated for column 'col1' at row 1 178INSERT INTO t1 VALUES ('59'); 179ERROR 22007: Incorrect datetime value: '59' for column `test`.`t1`.`col1` at row 1 180set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 181INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 182ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column `test`.`t1`.`col1` at row 1 183INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 184ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column `test`.`t1`.`col1` at row 1 185INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 186ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column `test`.`t1`.`col1` at row 1 187INSERT INTO t1 VALUES('2004-02-30 15:30:04'); 188ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column `test`.`t1`.`col1` at row 1 189INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 190set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; 191INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 192set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; 193INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 194ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`col1` at row 1 195set @@sql_mode='ansi,traditional'; 196SELECT * FROM t1; 197col1 1982004-10-31 15:30:00 1992004-02-29 15:30:00 2000000-00-00 00:00:00 2010000-00-00 00:00:00 2020000-00-00 00:00:00 203DROP TABLE t1; 204CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 205INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); 206INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); 207INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); 208INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 209Warnings: 210Note 1265 Data truncated for column 'col1' at row 1 211INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 212INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 213ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 214INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 215ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 216INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 217ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column `test`.`t1`.`col1` at row 1 218INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 219ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 220INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 221ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column `test`.`t1`.`col1` at row 1 222INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 223ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 224INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 225ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 226INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 227ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 228INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 229ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 230INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 231ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col2` at row 1 232INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 233ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 234INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 235ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col2` at row 1 236INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 237ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 238INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 239ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 240INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 241ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 242INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 243ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 244INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 245ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 246INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 247ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column `test`.`t1`.`col3` at row 1 248INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 249ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 250INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 251ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column `test`.`t1`.`col3` at row 1 252INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 253ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 254INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 255ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 256drop table t1; 257CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 258INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); 259INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); 260INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); 261INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); 262INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); 263ERROR 22007: Incorrect datetime value: '2004-10-0' 264INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); 265ERROR 22007: Incorrect datetime value: '2004-0-10' 266INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); 267ERROR 22007: Incorrect datetime value: '0000-00-00' 268INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); 269INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); 270ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' 271INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); 272ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' 273INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); 274ERROR 22007: Incorrect datetime value: '0000-00-00' 275INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); 276ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 277INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); 278ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' 279INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); 280ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' 281INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); 282ERROR 22007: Incorrect datetime value: '0000-00-00' 283drop table t1; 284CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 285INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); 286INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); 287INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); 288INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); 289INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); 290ERROR 22007: Incorrect datetime value: '2004-10-0' 291INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); 292ERROR 22007: Incorrect datetime value: '2004-0-10' 293INSERT INTO t1 (col1) VALUES('2004-0-10'); 294ERROR 22007: Incorrect date value: '2004-0-10' for column `test`.`t1`.`col1` at row 1 295INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); 296ERROR 22007: Incorrect datetime value: '0000-00-00' 297INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); 298INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); 299ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' 300INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); 301ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' 302INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); 303ERROR 22007: Incorrect datetime value: '0000-00-00' 304INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); 305ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column `test`.`t1`.`col3` at row 1 306INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); 307ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' 308INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); 309ERROR 22007: Incorrect datetime value: '2004-0-10 15:30' 310INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); 311ERROR 22007: Incorrect datetime value: '0000-00-00' 312drop table t1; 313CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); 314INSERT 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); 315SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; 316MOD(col1,0) 317NULL 318NULL 319Warnings: 320Warning 1365 Division by 0 321Warning 1365 Division by 0 322INSERT INTO t1 (col1) VALUES(-129); 323ERROR 22003: Out of range value for column 'col1' at row 1 324INSERT INTO t1 (col1) VALUES(128); 325ERROR 22003: Out of range value for column 'col1' at row 1 326INSERT INTO t1 (col2) VALUES(-1); 327ERROR 22003: Out of range value for column 'col2' at row 1 328INSERT INTO t1 (col2) VALUES(256); 329ERROR 22003: Out of range value for column 'col2' at row 1 330INSERT INTO t1 (col1) VALUES('-129'); 331ERROR 22003: Out of range value for column 'col1' at row 1 332INSERT INTO t1 (col1) VALUES('128'); 333ERROR 22003: Out of range value for column 'col1' at row 1 334INSERT INTO t1 (col2) VALUES('-1'); 335ERROR 22003: Out of range value for column 'col2' at row 1 336INSERT INTO t1 (col2) VALUES('256'); 337ERROR 22003: Out of range value for column 'col2' at row 1 338INSERT INTO t1 (col1) VALUES(128.0); 339ERROR 22003: Out of range value for column 'col1' at row 1 340INSERT INTO t1 (col2) VALUES(-1.0); 341ERROR 22003: Out of range value for column 'col2' at row 1 342INSERT INTO t1 (col2) VALUES(256.0); 343ERROR 22003: Out of range value for column 'col2' at row 1 344SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1; 345MOD(col1,0) 346NULL 347Warnings: 348Warning 1365 Division by 0 349UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 350ERROR 22003: Out of range value for column 'col1' at row 1 351UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0; 352ERROR 22003: Out of range value for column 'col2' at row 3 353UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0; 354ERROR 22012: Division by 0 355set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 356INSERT INTO t1 values (1/0,1/0); 357Warnings: 358Warning 1365 Division by 0 359Warning 1365 Division by 0 360set @@sql_mode='ansi,traditional'; 361SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; 362MOD(col1,0) 363NULL 364NULL 365Warnings: 366Warning 1365 Division by 0 367Warning 1365 Division by 0 368INSERT INTO t1 (col1) VALUES (''); 369ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 370INSERT INTO t1 (col1) VALUES ('a59b'); 371ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 372INSERT INTO t1 (col1) VALUES ('1a'); 373ERROR 01000: Data truncated for column 'col1' at row 1 374INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 375Warnings: 376Warning 1265 Data truncated for column 'col1' at row 1 377INSERT IGNORE INTO t1 values (1/0,1/0); 378Warnings: 379Warning 1365 Division by 0 380Warning 1365 Division by 0 381set @@sql_mode='ansi'; 382INSERT INTO t1 values (1/0,1/0); 383set @@sql_mode='ansi,traditional'; 384INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256'); 385Warnings: 386Warning 1264 Out of range value for column 'col1' at row 1 387Warning 1264 Out of range value for column 'col2' at row 1 388Warning 1264 Out of range value for column 'col1' at row 2 389Warning 1264 Out of range value for column 'col2' at row 2 390INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0); 391Warnings: 392Warning 1264 Out of range value for column 'col1' at row 1 393Warning 1264 Out of range value for column 'col2' at row 1 394Warning 1264 Out of range value for column 'col1' at row 2 395Warning 1264 Out of range value for column 'col2' at row 2 396UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 397SELECT * FROM t1; 398col1 col2 399-128 0 4000 NULL 401127 255 402-128 0 4030 NULL 404127 255 405-128 0 4060 NULL 407127 255 408NULL NULL 4092 NULL 410NULL NULL 411NULL NULL 412-128 0 413127 255 414-128 0 415127 255 416DROP TABLE t1; 417CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED); 418INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0); 419INSERT INTO t1 (col1) VALUES(-32769); 420ERROR 22003: Out of range value for column 'col1' at row 1 421INSERT INTO t1 (col1) VALUES(32768); 422ERROR 22003: Out of range value for column 'col1' at row 1 423INSERT INTO t1 (col2) VALUES(-1); 424ERROR 22003: Out of range value for column 'col2' at row 1 425INSERT INTO t1 (col2) VALUES(65536); 426ERROR 22003: Out of range value for column 'col2' at row 1 427INSERT INTO t1 (col1) VALUES('-32769'); 428ERROR 22003: Out of range value for column 'col1' at row 1 429INSERT INTO t1 (col1) VALUES('32768'); 430ERROR 22003: Out of range value for column 'col1' at row 1 431INSERT INTO t1 (col2) VALUES('-1'); 432ERROR 22003: Out of range value for column 'col2' at row 1 433INSERT INTO t1 (col2) VALUES('65536'); 434ERROR 22003: Out of range value for column 'col2' at row 1 435INSERT INTO t1 (col1) VALUES(-32769.0); 436ERROR 22003: Out of range value for column 'col1' at row 1 437INSERT INTO t1 (col1) VALUES(32768.0); 438ERROR 22003: Out of range value for column 'col1' at row 1 439INSERT INTO t1 (col2) VALUES(-1.0); 440ERROR 22003: Out of range value for column 'col2' at row 1 441INSERT INTO t1 (col2) VALUES(65536.0); 442ERROR 22003: Out of range value for column 'col2' at row 1 443UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 444ERROR 22003: Out of range value for column 'col1' at row 1 445UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; 446ERROR 22003: Out of range value for column 'col2' at row 3 447UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0; 448ERROR 22012: Division by 0 449UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 450ERROR 22012: Division by 0 451INSERT INTO t1 (col1) VALUES (''); 452ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 453INSERT INTO t1 (col1) VALUES ('a59b'); 454ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 455INSERT INTO t1 (col1) VALUES ('1a'); 456ERROR 01000: Data truncated for column 'col1' at row 1 457INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 458Warnings: 459Warning 1265 Data truncated for column 'col1' at row 1 460INSERT IGNORE INTO t1 values (1/0,1/0); 461Warnings: 462Warning 1365 Division by 0 463Warning 1365 Division by 0 464INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536); 465Warnings: 466Warning 1264 Out of range value for column 'col1' at row 1 467Warning 1264 Out of range value for column 'col2' at row 1 468Warning 1264 Out of range value for column 'col1' at row 2 469Warning 1264 Out of range value for column 'col2' at row 2 470INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536'); 471Warnings: 472Warning 1264 Out of range value for column 'col1' at row 1 473Warning 1264 Out of range value for column 'col2' at row 1 474Warning 1264 Out of range value for column 'col1' at row 2 475Warning 1264 Out of range value for column 'col2' at row 2 476INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0); 477Warnings: 478Warning 1264 Out of range value for column 'col1' at row 1 479Warning 1264 Out of range value for column 'col2' at row 1 480Warning 1264 Out of range value for column 'col1' at row 2 481Warning 1264 Out of range value for column 'col2' at row 2 482UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 483SELECT * FROM t1; 484col1 col2 485-32768 0 4860 NULL 48732767 65535 488-32768 0 48932767 65535 490-32768 0 49132767 65535 4922 NULL 493NULL NULL 494-32768 0 49532767 65535 496-32768 0 49732767 65535 498-32768 0 49932767 65535 500DROP TABLE t1; 501CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED); 502INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0); 503INSERT INTO t1 (col1) VALUES(-8388609); 504ERROR 22003: Out of range value for column 'col1' at row 1 505INSERT INTO t1 (col1) VALUES(8388608); 506ERROR 22003: Out of range value for column 'col1' at row 1 507INSERT INTO t1 (col2) VALUES(-1); 508ERROR 22003: Out of range value for column 'col2' at row 1 509INSERT INTO t1 (col2) VALUES(16777216); 510ERROR 22003: Out of range value for column 'col2' at row 1 511INSERT INTO t1 (col1) VALUES('-8388609'); 512ERROR 22003: Out of range value for column 'col1' at row 1 513INSERT INTO t1 (col1) VALUES('8388608'); 514ERROR 22003: Out of range value for column 'col1' at row 1 515INSERT INTO t1 (col2) VALUES('-1'); 516ERROR 22003: Out of range value for column 'col2' at row 1 517INSERT INTO t1 (col2) VALUES('16777216'); 518ERROR 22003: Out of range value for column 'col2' at row 1 519INSERT INTO t1 (col1) VALUES(-8388609.0); 520ERROR 22003: Out of range value for column 'col1' at row 1 521INSERT INTO t1 (col1) VALUES(8388608.0); 522ERROR 22003: Out of range value for column 'col1' at row 1 523INSERT INTO t1 (col2) VALUES(-1.0); 524ERROR 22003: Out of range value for column 'col2' at row 1 525INSERT INTO t1 (col2) VALUES(16777216.0); 526ERROR 22003: Out of range value for column 'col2' at row 1 527UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 528ERROR 22003: Out of range value for column 'col1' at row 1 529UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; 530ERROR 22003: Out of range value for column 'col2' at row 3 531UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 532ERROR 22012: Division by 0 533UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 534ERROR 22012: Division by 0 535INSERT INTO t1 (col1) VALUES (''); 536ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 537INSERT INTO t1 (col1) VALUES ('a59b'); 538ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 539INSERT INTO t1 (col1) VALUES ('1a'); 540ERROR 01000: Data truncated for column 'col1' at row 1 541INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 542Warnings: 543Warning 1265 Data truncated for column 'col1' at row 1 544INSERT IGNORE INTO t1 values (1/0,1/0); 545Warnings: 546Warning 1365 Division by 0 547Warning 1365 Division by 0 548INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216); 549Warnings: 550Warning 1264 Out of range value for column 'col1' at row 1 551Warning 1264 Out of range value for column 'col2' at row 1 552Warning 1264 Out of range value for column 'col1' at row 2 553Warning 1264 Out of range value for column 'col2' at row 2 554INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216'); 555Warnings: 556Warning 1264 Out of range value for column 'col1' at row 1 557Warning 1264 Out of range value for column 'col2' at row 1 558Warning 1264 Out of range value for column 'col1' at row 2 559Warning 1264 Out of range value for column 'col2' at row 2 560INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0); 561Warnings: 562Warning 1264 Out of range value for column 'col1' at row 1 563Warning 1264 Out of range value for column 'col2' at row 1 564Warning 1264 Out of range value for column 'col1' at row 2 565Warning 1264 Out of range value for column 'col2' at row 2 566UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 567SELECT * FROM t1; 568col1 col2 569-8388608 0 5700 NULL 5718388607 16777215 572-8388608 0 5738388607 16777215 574-8388608 0 5758388607 16777215 5762 NULL 577NULL NULL 578-8388608 0 5798388607 16777215 580-8388608 0 5818388607 16777215 582-8388608 0 5838388607 16777215 584DROP TABLE t1; 585CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED); 586INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0); 587INSERT INTO t1 (col1) VALUES(-2147483649); 588ERROR 22003: Out of range value for column 'col1' at row 1 589INSERT INTO t1 (col1) VALUES(2147643648); 590ERROR 22003: Out of range value for column 'col1' at row 1 591INSERT INTO t1 (col2) VALUES(-1); 592ERROR 22003: Out of range value for column 'col2' at row 1 593INSERT INTO t1 (col2) VALUES(4294967296); 594ERROR 22003: Out of range value for column 'col2' at row 1 595INSERT INTO t1 (col1) VALUES('-2147483649'); 596ERROR 22003: Out of range value for column 'col1' at row 1 597INSERT INTO t1 (col1) VALUES('2147643648'); 598ERROR 22003: Out of range value for column 'col1' at row 1 599INSERT INTO t1 (col2) VALUES('-1'); 600ERROR 22003: Out of range value for column 'col2' at row 1 601INSERT INTO t1 (col2) VALUES('4294967296'); 602ERROR 22003: Out of range value for column 'col2' at row 1 603INSERT INTO t1 (col1) VALUES(-2147483649.0); 604ERROR 22003: Out of range value for column 'col1' at row 1 605INSERT INTO t1 (col1) VALUES(2147643648.0); 606ERROR 22003: Out of range value for column 'col1' at row 1 607INSERT INTO t1 (col2) VALUES(-1.0); 608ERROR 22003: Out of range value for column 'col2' at row 1 609INSERT INTO t1 (col2) VALUES(4294967296.0); 610ERROR 22003: Out of range value for column 'col2' at row 1 611UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 612ERROR 22003: Out of range value for column 'col1' at row 1 613UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0; 614ERROR 22003: Out of range value for column 'col2' at row 3 615UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 616ERROR 22012: Division by 0 617UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 618ERROR 22012: Division by 0 619INSERT INTO t1 (col1) VALUES (''); 620ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 621INSERT INTO t1 (col1) VALUES ('a59b'); 622ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 623INSERT INTO t1 (col1) VALUES ('1a'); 624ERROR 01000: Data truncated for column 'col1' at row 1 625INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 626Warnings: 627Warning 1265 Data truncated for column 'col1' at row 1 628INSERT IGNORE INTO t1 values (1/0,1/0); 629Warnings: 630Warning 1365 Division by 0 631Warning 1365 Division by 0 632INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296); 633Warnings: 634Warning 1264 Out of range value for column 'col1' at row 1 635Warning 1264 Out of range value for column 'col2' at row 1 636Warning 1264 Out of range value for column 'col1' at row 2 637Warning 1264 Out of range value for column 'col2' at row 2 638INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296'); 639Warnings: 640Warning 1264 Out of range value for column 'col1' at row 1 641Warning 1264 Out of range value for column 'col2' at row 1 642Warning 1264 Out of range value for column 'col1' at row 2 643Warning 1264 Out of range value for column 'col2' at row 2 644INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0); 645Warnings: 646Warning 1264 Out of range value for column 'col1' at row 1 647Warning 1264 Out of range value for column 'col2' at row 1 648Warning 1264 Out of range value for column 'col1' at row 2 649Warning 1264 Out of range value for column 'col2' at row 2 650UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 651SELECT * FROM t1; 652col1 col2 653-2147483648 0 6540 NULL 6552147483647 4294967295 656-2147483648 0 6572147483647 4294967295 658-2147483648 0 6592147483647 4294967295 6602 NULL 661NULL NULL 662-2147483648 0 6632147483647 4294967295 664-2147483648 0 6652147483647 4294967295 666-2147483648 0 6672147483647 4294967295 668DROP TABLE t1; 669CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED); 670INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615); 671INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); 672INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); 673INSERT INTO t1 (col1) VALUES(-9223372036854775809); 674ERROR 22003: Out of range value for column 'col1' at row 1 675INSERT INTO t1 (col1) VALUES(9223372036854775808); 676ERROR 22003: Out of range value for column 'col1' at row 1 677INSERT INTO t1 (col2) VALUES(-1); 678ERROR 22003: Out of range value for column 'col2' at row 1 679INSERT INTO t1 (col2) VALUES(18446744073709551616); 680ERROR 22003: Out of range value for column 'col2' at row 1 681INSERT INTO t1 (col1) VALUES('-9223372036854775809'); 682ERROR 22003: Out of range value for column 'col1' at row 1 683INSERT INTO t1 (col1) VALUES('9223372036854775808'); 684ERROR 22003: Out of range value for column 'col1' at row 1 685INSERT INTO t1 (col2) VALUES('-1'); 686ERROR 22003: Out of range value for column 'col2' at row 1 687INSERT INTO t1 (col2) VALUES('18446744073709551616'); 688ERROR 22003: Out of range value for column 'col2' at row 1 689INSERT INTO t1 (col1) VALUES(-9223372036854785809.0); 690ERROR 22003: Out of range value for column 'col1' at row 1 691INSERT INTO t1 (col1) VALUES(9223372036854785808.0); 692ERROR 22003: Out of range value for column 'col1' at row 1 693INSERT INTO t1 (col2) VALUES(-1.0); 694ERROR 22003: Out of range value for column 'col2' at row 1 695INSERT INTO t1 (col2) VALUES(18446744073709551616.0); 696ERROR 22003: Out of range value for column 'col2' at row 1 697UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 698ERROR 22012: Division by 0 699UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 700ERROR 22012: Division by 0 701INSERT INTO t1 (col1) VALUES (''); 702ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`col1` at row 1 703INSERT INTO t1 (col1) VALUES ('a59b'); 704ERROR 22007: Incorrect integer value: 'a59b' for column `test`.`t1`.`col1` at row 1 705INSERT INTO t1 (col1) VALUES ('1a'); 706ERROR 01000: Data truncated for column 'col1' at row 1 707INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 708Warnings: 709Warning 1265 Data truncated for column 'col1' at row 1 710INSERT IGNORE INTO t1 values (1/0,1/0); 711Warnings: 712Warning 1365 Division by 0 713Warning 1365 Division by 0 714INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); 715Warnings: 716Warning 1264 Out of range value for column 'col1' at row 1 717Warning 1264 Out of range value for column 'col2' at row 1 718Warning 1264 Out of range value for column 'col1' at row 2 719Warning 1264 Out of range value for column 'col2' at row 2 720INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); 721Warnings: 722Warning 1264 Out of range value for column 'col1' at row 1 723Warning 1264 Out of range value for column 'col2' at row 1 724Warning 1264 Out of range value for column 'col1' at row 2 725Warning 1264 Out of range value for column 'col2' at row 2 726INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0); 727Warnings: 728Warning 1264 Out of range value for column 'col1' at row 1 729Warning 1264 Out of range value for column 'col2' at row 1 730Warning 1264 Out of range value for column 'col1' at row 2 731Warning 1264 Out of range value for column 'col2' at row 2 732UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 733SELECT * FROM t1; 734col1 col2 735-9223372036854775808 0 7360 NULL 7379223372036854775807 18446744073709551615 738-9223372036854775808 0 7399223372036854775807 18446744073709551615 740-9223372036854774000 0 7419223372036854775700 1844674407370954000 7422 NULL 743NULL NULL 744-9223372036854775808 0 7459223372036854775807 18446744073709551615 746-9223372036854775808 0 7479223372036854775807 18446744073709551615 748-9223372036854775808 0 7499223372036854775807 18446744073709551615 750DROP TABLE t1; 751CREATE TABLE t1 (col1 NUMERIC(4,2)); 752INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); 753Warnings: 754Note 1265 Data truncated for column 'col1' at row 2 755Note 1265 Data truncated for column 'col1' at row 5 756INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); 757Warnings: 758Note 1265 Data truncated for column 'col1' at row 2 759Note 1265 Data truncated for column 'col1' at row 4 760INSERT INTO t1 VALUES (101.55); 761ERROR 22003: Out of range value for column 'col1' at row 1 762INSERT INTO t1 VALUES (101); 763ERROR 22003: Out of range value for column 'col1' at row 1 764INSERT INTO t1 VALUES (-101.55); 765ERROR 22003: Out of range value for column 'col1' at row 1 766INSERT INTO t1 VALUES (1010.55); 767ERROR 22003: Out of range value for column 'col1' at row 1 768INSERT INTO t1 VALUES (1010); 769ERROR 22003: Out of range value for column 'col1' at row 1 770INSERT INTO t1 VALUES ('101.55'); 771ERROR 22003: Out of range value for column 'col1' at row 1 772INSERT INTO t1 VALUES ('101'); 773ERROR 22003: Out of range value for column 'col1' at row 1 774INSERT INTO t1 VALUES ('-101.55'); 775ERROR 22003: Out of range value for column 'col1' at row 1 776INSERT INTO t1 VALUES ('-1010.55'); 777ERROR 22003: Out of range value for column 'col1' at row 1 778INSERT INTO t1 VALUES ('-100E+1'); 779ERROR 22003: Out of range value for column 'col1' at row 1 780INSERT INTO t1 VALUES ('-100E'); 781ERROR 01000: Data truncated for column 'col1' at row 1 782UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; 783ERROR 22003: Out of range value for column 'col1' at row 6 784UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 785ERROR 22012: Division by 0 786UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 787ERROR 22012: Division by 0 788INSERT INTO t1 (col1) VALUES (''); 789ERROR 22007: Incorrect decimal value: '' for column `test`.`t1`.`col1` at row 1 790INSERT INTO t1 (col1) VALUES ('a59b'); 791ERROR 22007: Incorrect decimal value: 'a59b' for column `test`.`t1`.`col1` at row 1 792INSERT INTO t1 (col1) VALUES ('1a'); 793ERROR 01000: Data truncated for column 'col1' at row 1 794INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 795Warnings: 796Warning 1265 Data truncated for column 'col1' at row 1 797INSERT IGNORE INTO t1 values (1/0); 798Warnings: 799Warning 1365 Division by 0 800INSERT IGNORE INTO t1 VALUES(1000),(-1000); 801Warnings: 802Warning 1264 Out of range value for column 'col1' at row 1 803Warning 1264 Out of range value for column 'col1' at row 2 804INSERT IGNORE INTO t1 VALUES('1000'),('-1000'); 805Warnings: 806Warning 1264 Out of range value for column 'col1' at row 1 807Warning 1264 Out of range value for column 'col1' at row 2 808INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0); 809Warnings: 810Warning 1264 Out of range value for column 'col1' at row 1 811Warning 1264 Out of range value for column 'col1' at row 2 812UPDATE IGNORE t1 SET col1=1/NULL where col1=0; 813SELECT * FROM t1; 814col1 81510.55 81610.56 817NULL 818-10.55 819-10.56 82011.00 82110.00 82210.55 82310.56 824-10.55 825-10.56 82611.00 82710.00 8282.00 829NULL 83099.99 831-99.99 83299.99 833-99.99 83499.99 835-99.99 836DROP TABLE t1; 837CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED); 838INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38); 839INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38'); 840INSERT INTO t1 (col1) VALUES (3E-46); 841INSERT INTO t1 (col1) VALUES (+3.4E+39); 842ERROR 22003: Out of range value for column 'col1' at row 1 843INSERT INTO t1 (col2) VALUES (-1.1E-3); 844ERROR 22003: Out of range value for column 'col2' at row 1 845INSERT INTO t1 (col1) VALUES ('+3.4E+39'); 846ERROR 22003: Out of range value for column 'col1' at row 1 847INSERT INTO t1 (col2) VALUES ('-1.1E-3'); 848ERROR 22003: Out of range value for column 'col2' at row 1 849UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; 850ERROR 22003: Out of range value for column 'col1' at row 2 851UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; 852ERROR 22012: Division by 0 853UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; 854ERROR 22012: Division by 0 855INSERT INTO t1 (col1) VALUES (''); 856ERROR 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1 857INSERT INTO t1 (col1) VALUES ('a59b'); 858ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`col1` at row 1 859INSERT INTO t1 (col1) VALUES ('1a'); 860ERROR 01000: Data truncated for column 'col1' at row 1 861INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 862Warnings: 863Warning 1265 Data truncated for column 'col1' at row 1 864INSERT IGNORE INTO t1 (col1) VALUES (1/0); 865Warnings: 866Warning 1365 Division by 0 867INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39); 868Warnings: 869Warning 1264 Out of range value for column 'col1' at row 1 870Warning 1264 Out of range value for column 'col2' at row 1 871INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39'); 872Warnings: 873Warning 1264 Out of range value for column 'col1' at row 1 874Warning 1264 Out of range value for column 'col2' at row 1 875SELECT * FROM t1; 876col1 col2 877-1.1e-37 0 8783.4e38 3.4e38 879-1.1e-37 0 8803.4e38 3.4e38 8810 NULL 8822 NULL 883NULL NULL 8843.40282e38 0 8853.40282e38 0 886DROP TABLE t1; 887CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); 888INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308); 889INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308'); 890INSERT INTO t1 (col1) VALUES (-2.2E-330); 891INSERT INTO t1 (col1) VALUES (+1.7E+309); 892Got one of the listed errors 893INSERT INTO t1 (col2) VALUES (-1.1E-3); 894ERROR 22003: Out of range value for column 'col2' at row 1 895INSERT INTO t1 (col1) VALUES ('+1.8E+309'); 896ERROR 22003: Out of range value for column 'col1' at row 1 897INSERT INTO t1 (col2) VALUES ('-1.2E-3'); 898ERROR 22003: Out of range value for column 'col2' at row 1 899UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; 900ERROR 22003: DOUBLE value is out of range in '"test"."t1"."col1" * 5000' 901UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; 902ERROR 22012: Division by 0 903UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; 904ERROR 22012: Division by 0 905INSERT INTO t1 (col1) VALUES (''); 906ERROR 22007: Incorrect double value: '' for column `test`.`t1`.`col1` at row 1 907INSERT INTO t1 (col1) VALUES ('a59b'); 908ERROR 22007: Incorrect double value: 'a59b' for column `test`.`t1`.`col1` at row 1 909INSERT INTO t1 (col1) VALUES ('1a'); 910ERROR 01000: Data truncated for column 'col1' at row 1 911INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 912Warnings: 913Warning 1265 Data truncated for column 'col1' at row 1 914INSERT IGNORE INTO t1 (col1) values (1/0); 915Warnings: 916Warning 1365 Division by 0 917INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309); 918ERROR 22007: Illegal double '1.9E+309' value found during parsing 919INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309'); 920Warnings: 921Warning 1264 Out of range value for column 'col1' at row 1 922Warning 1264 Out of range value for column 'col2' at row 1 923Warning 1264 Out of range value for column 'col2' at row 1 924SELECT * FROM t1; 925col1 col2 926-2.2e-307 0 9271e-303 0 9281.7e308 1.7e308 929-2.2e-307 0 930-2e-307 0 9311.7e308 1.7e308 9320 NULL 9332 NULL 934NULL NULL 9351.7976931348623157e308 0 936DROP TABLE t1; 937CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); 938INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); 939INSERT INTO t1 (col1) VALUES ('hellobob'); 940ERROR 22001: Data too long for column 'col1' at row 1 941INSERT INTO t1 (col2) VALUES ('hellobob'); 942ERROR 22001: Data too long for column 'col2' at row 1 943INSERT INTO t1 (col2) VALUES ('hello '); 944Warnings: 945Note 1265 Data truncated for column 'col2' at row 1 946UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; 947ERROR 22001: Data too long for column 'col1' at row 2 948UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; 949ERROR 22001: Data too long for column 'col2' at row 2 950INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); 951Warnings: 952Warning 1265 Data truncated for column 'col1' at row 1 953Warning 1265 Data truncated for column 'col2' at row 1 954UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; 955Warnings: 956Warning 1265 Data truncated for column 'col2' at row 2 957SELECT * FROM t1; 958col1 col2 959hello hello 960he hellot 961hello hello 962NULL hello 963hello hellob 964DROP TABLE t1; 965CREATE TABLE t1 (col1 enum('red','blue','green')); 966INSERT INTO t1 VALUES ('red'),('blue'),('green'); 967INSERT INTO t1 (col1) VALUES ('yellow'); 968ERROR 01000: Data truncated for column 'col1' at row 1 969INSERT INTO t1 (col1) VALUES ('redd'); 970ERROR 01000: Data truncated for column 'col1' at row 1 971INSERT INTO t1 VALUES (''); 972ERROR 01000: Data truncated for column 'col1' at row 1 973UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; 974ERROR 01000: Data truncated for column 'col1' at row 3 975INSERT IGNORE INTO t1 VALUES ('yellow'); 976Warnings: 977Warning 1265 Data truncated for column 'col1' at row 1 978UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; 979Warnings: 980Warning 1265 Data truncated for column 'col1' at row 2 981SELECT * FROM t1; 982col1 983red 984 985green 986 987DROP TABLE t1; 988CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL); 989INSERT INTO t1 VALUES (100, 'hello', '2004-08-20'); 990INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21'); 991INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); 992ERROR 23000: Column 'col1' cannot be null 993INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); 994ERROR 23000: Column 'col2' cannot be null 995INSERT INTO t1 VALUES (103,'',NULL); 996ERROR 23000: Column 'col3' cannot be null 997UPDATE t1 SET col1=NULL WHERE col1 =100; 998ERROR 23000: Column 'col1' cannot be null 999UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; 1000ERROR 23000: Column 'col2' cannot be null 1001UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; 1002ERROR 23000: Column 'col2' cannot be null 1003INSERT IGNORE INTO t1 values (NULL,NULL,NULL); 1004Warnings: 1005Warning 1048 Column 'col1' cannot be null 1006Warning 1048 Column 'col2' cannot be null 1007Warning 1048 Column 'col3' cannot be null 1008SELECT * FROM t1; 1009col1 col2 col3 1010100 hello 2004-08-20 1011101 hell2 2004-08-21 10120 0000-00-00 1013DROP TABLE t1; 1014CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); 1015SHOW CREATE TABLE t1; 1016Table Create Table 1017t1 CREATE TABLE "t1" ( 1018 "col1" int(11) NOT NULL DEFAULT 99, 1019 "col2" char(6) NOT NULL 1020) 1021INSERT INTO t1 VALUES (1, 'hello'); 1022INSERT INTO t1 (col2) VALUES ('hello2'); 1023INSERT INTO t1 (col2) VALUES (NULL); 1024ERROR 23000: Column 'col2' cannot be null 1025INSERT INTO t1 (col1) VALUES (2); 1026ERROR HY000: Field 'col2' doesn't have a default value 1027INSERT INTO t1 VALUES(default(col1),default(col2)); 1028ERROR HY000: Field 'col2' doesn't have a default value 1029INSERT INTO t1 (col1) SELECT 1; 1030ERROR HY000: Field 'col2' doesn't have a default value 1031INSERT INTO t1 SELECT 1,NULL; 1032ERROR 23000: Column 'col2' cannot be null 1033INSERT IGNORE INTO t1 values (NULL,NULL); 1034Warnings: 1035Warning 1048 Column 'col1' cannot be null 1036Warning 1048 Column 'col2' cannot be null 1037INSERT IGNORE INTO t1 (col1) values (3); 1038Warnings: 1039Warning 1364 Field 'col2' doesn't have a default value 1040INSERT IGNORE INTO t1 () values (); 1041Warnings: 1042Warning 1364 Field 'col2' doesn't have a default value 1043SELECT * FROM t1; 1044col1 col2 10451 hello 104699 hello2 10470 10483 104999 1050DROP TABLE t1; 1051set sql_mode='traditional'; 1052create table t1 (charcol char(255), varcharcol varchar(255), 1053binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, 1054tinyblobcol tinyblob); 1055insert into t1 (charcol) values (repeat('x',256)); 1056ERROR 22001: Data too long for column 'charcol' at row 1 1057insert into t1 (varcharcol) values (repeat('x',256)); 1058ERROR 22001: Data too long for column 'varcharcol' at row 1 1059insert into t1 (binarycol) values (repeat('x',256)); 1060ERROR 22001: Data too long for column 'binarycol' at row 1 1061insert into t1 (varbinarycol) values (repeat('x',256)); 1062ERROR 22001: Data too long for column 'varbinarycol' at row 1 1063insert into t1 (tinytextcol) values (repeat('x',256)); 1064ERROR 22001: Data too long for column 'tinytextcol' at row 1 1065insert into t1 (tinyblobcol) values (repeat('x',256)); 1066ERROR 22001: Data too long for column 'tinyblobcol' at row 1 1067select * from t1; 1068charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol 1069drop table t1; 1070set sql_mode='traditional'; 1071create table t1 (col1 datetime); 1072insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); 1073ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc' 1074insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 1075ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 1076insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); 1077ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date 1078insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); 1079ERROR HY000: Incorrect time value: 'abc' for function str_to_date 1080set sql_mode=''; 1081insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); 1082Warnings: 1083Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc' 1084insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 1085Warnings: 1086Warning 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 1087insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); 1088Warnings: 1089Warning 1411 Incorrect time value: '22:22:33 AM' for function str_to_date 1090insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); 1091Warnings: 1092Warning 1411 Incorrect time value: 'abc' for function str_to_date 1093insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); 1094insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); 1095insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); 1096select * from t1; 1097col1 10982004-10-31 15:30:00 1099NULL 1100NULL 1101NULL 11022004-10-31 15:30:00 11032004-12-12 11:22:33 11042004-12-12 10:22:59 1105set sql_mode='traditional'; 1106select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; 1107count(*) 11087 1109Warnings: 1110Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1111drop table t1; 1112create table t1 (col1 char(3), col2 integer); 1113insert into t1 (col1) values (cast(1000 as char(3))); 1114ERROR 22007: Truncated incorrect CHAR(3) value: '1000' 1115insert into t1 (col1) values (cast(1000E+0 as char(3))); 1116ERROR 22007: Truncated incorrect CHAR(3) value: '1000' 1117insert into t1 (col1) values (cast(1000.0 as char(3))); 1118ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' 1119insert into t1 (col2) values (cast('abc' as signed integer)); 1120ERROR 22007: Truncated incorrect INTEGER value: 'abc' 1121insert into t1 (col2) values (10E+0 + 'a'); 1122ERROR 22007: Truncated incorrect DOUBLE value: 'a' 1123insert into t1 (col2) values (cast('10a' as unsigned integer)); 1124ERROR 22007: Truncated incorrect INTEGER value: '10a' 1125insert into t1 (col2) values (cast('10' as unsigned integer)); 1126insert into t1 (col2) values (cast('10' as signed integer)); 1127insert into t1 (col2) values (10E+0 + '0 '); 1128Warnings: 1129Note 1292 Truncated incorrect DOUBLE value: '0 ' 1130select * from t1; 1131col1 col2 1132NULL 10 1133NULL 10 1134NULL 10 1135drop table t1; 1136create table t1 (col1 date, col2 datetime, col3 timestamp); 1137insert into t1 values (0,0,0); 1138ERROR 22007: Incorrect date value: '0' for column `test`.`t1`.`col1` at row 1 1139insert into t1 values (0.0,0.0,0.0); 1140ERROR 22007: Incorrect date value: '0.0' for column `test`.`t1`.`col1` at row 1 1141insert into t1 (col1) values (convert('0000-00-00',date)); 1142ERROR 22007: Incorrect datetime value: '0000-00-00' 1143insert into t1 (col1) values (cast('0000-00-00' as date)); 1144ERROR 22007: Incorrect datetime value: '0000-00-00' 1145set sql_mode='no_zero_date'; 1146insert into t1 values (0,0,0); 1147Warnings: 1148Warning 1264 Out of range value for column 'col1' at row 1 1149Warning 1264 Out of range value for column 'col2' at row 1 1150Warning 1265 Data truncated for column 'col3' at row 1 1151insert into t1 values (0.0,0.0,0.0); 1152Warnings: 1153Warning 1264 Out of range value for column 'col1' at row 1 1154Warning 1264 Out of range value for column 'col2' at row 1 1155Warning 1265 Data truncated for column 'col3' at row 1 1156drop table t1; 1157set sql_mode='traditional'; 1158create table t1 (col1 date); 1159insert ignore into t1 values ('0000-00-00'); 1160Warnings: 1161Warning 1264 Out of range value for column 'col1' at row 1 1162insert into t1 select * from t1; 1163ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 1164insert ignore into t1 values ('0000-00-00'); 1165Warnings: 1166Warning 1264 Out of range value for column 'col1' at row 1 1167insert ignore into t1 (col1) values (cast('0000-00-00' as date)); 1168Warnings: 1169Warning 1292 Incorrect datetime value: '0000-00-00' 1170insert into t1 select * from t1; 1171ERROR 22007: Incorrect date value: '0000-00-00' for column ``.`(temporary)`.`col1` at row 1 1172alter table t1 modify col1 datetime; 1173ERROR 22007: Incorrect datetime value: '0000-00-00' for column `test`.`t1`.`col1` at row 1 1174alter ignore table t1 modify col1 datetime; 1175Warnings: 1176Warning 1264 Out of range value for column 'col1' at row 1 1177Warning 1264 Out of range value for column 'col1' at row 2 1178insert into t1 select * from t1; 1179ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column ``.`(temporary)`.`col1` at row 1 1180select * from t1; 1181col1 11820000-00-00 00:00:00 11830000-00-00 00:00:00 1184NULL 1185drop table t1; 1186create table t1 (col1 tinyint); 1187drop procedure if exists t1; 1188Warnings: 1189Note 1305 PROCEDURE test.t1 does not exist 1190create procedure t1 () begin declare exit handler for sqlexception 1191select'a'; insert into t1 values (200); end;| 1192call t1(); 1193a 1194a 1195select * from t1; 1196col1 1197drop procedure t1; 1198drop table t1; 1199set sql_mode=@org_mode; 1200SET @@sql_mode = 'traditional'; 1201CREATE TABLE t1 (i int not null); 1202INSERT INTO t1 VALUES (); 1203ERROR HY000: Field 'i' doesn't have a default value 1204INSERT INTO t1 VALUES (DEFAULT); 1205ERROR HY000: Field 'i' doesn't have a default value 1206INSERT INTO t1 VALUES (DEFAULT(i)); 1207ERROR HY000: Field 'i' doesn't have a default value 1208ALTER TABLE t1 ADD j int; 1209INSERT INTO t1 SET j = 1; 1210ERROR HY000: Field 'i' doesn't have a default value 1211INSERT INTO t1 SET j = 1, i = DEFAULT; 1212ERROR HY000: Field 'i' doesn't have a default value 1213INSERT INTO t1 SET j = 1, i = DEFAULT(i); 1214ERROR HY000: Field 'i' doesn't have a default value 1215INSERT INTO t1 VALUES (DEFAULT,1); 1216ERROR HY000: Field 'i' doesn't have a default value 1217DROP TABLE t1; 1218SET @@sql_mode = ''; 1219CREATE TABLE t1 (i int not null); 1220INSERT INTO t1 VALUES (); 1221Warnings: 1222Warning 1364 Field 'i' doesn't have a default value 1223INSERT INTO t1 VALUES (DEFAULT); 1224Warnings: 1225Warning 1364 Field 'i' doesn't have a default value 1226INSERT INTO t1 VALUES (DEFAULT(i)); 1227ERROR HY000: Field 'i' doesn't have a default value 1228ALTER TABLE t1 ADD j int; 1229INSERT INTO t1 SET j = 1; 1230Warnings: 1231Warning 1364 Field 'i' doesn't have a default value 1232INSERT INTO t1 SET j = 1, i = DEFAULT; 1233Warnings: 1234Warning 1364 Field 'i' doesn't have a default value 1235INSERT INTO t1 SET j = 1, i = DEFAULT(i); 1236ERROR HY000: Field 'i' doesn't have a default value 1237INSERT INTO t1 VALUES (DEFAULT,1); 1238Warnings: 1239Warning 1364 Field 'i' doesn't have a default value 1240DROP TABLE t1; 1241set @@sql_mode='traditional'; 1242create table t1(a varchar(65537)); 1243ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead 1244create table t1(a varbinary(65537)); 1245ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead 1246set @@sql_mode='traditional'; 1247create table t1(a int, b date not null); 1248alter table t1 modify a bigint unsigned not null; 1249show create table t1; 1250Table Create Table 1251t1 CREATE TABLE `t1` ( 1252 `a` bigint(20) unsigned NOT NULL, 1253 `b` date NOT NULL 1254) ENGINE=MyISAM DEFAULT CHARSET=latin1 1255drop table t1; 1256set @@sql_mode='traditional'; 1257create table t1 (d date); 1258insert into t1 values ('2000-10-00'); 1259ERROR 22007: Incorrect date value: '2000-10-00' for column `test`.`t1`.`d` at row 1 1260insert into t1 values (1000); 1261ERROR 22007: Incorrect date value: '1000' for column `test`.`t1`.`d` at row 1 1262insert into t1 values ('2000-10-01'); 1263update t1 set d = 1100; 1264ERROR 22007: Incorrect date value: '1100' for column `test`.`t1`.`d` at row 1 1265select * from t1; 1266d 12672000-10-01 1268drop table t1; 1269set @@sql_mode='traditional'; 1270create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1271alter table t1 add primary key(a); 1272show create table t1; 1273Table Create Table 1274t1 CREATE TABLE `t1` ( 1275 `a` int(11) NOT NULL, 1276 `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 1277 PRIMARY KEY (`a`) 1278) ENGINE=MyISAM DEFAULT CHARSET=latin1 1279drop table t1; 1280create table t1(a int, b timestamp not null default 20050102030405); 1281alter table t1 add primary key(a); 1282show create table t1; 1283Table Create Table 1284t1 CREATE TABLE `t1` ( 1285 `a` int(11) NOT NULL, 1286 `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05', 1287 PRIMARY KEY (`a`) 1288) ENGINE=MyISAM DEFAULT CHARSET=latin1 1289drop table t1; 1290set @@sql_mode='traditional'; 1291create table t1(a bit(2)); 1292insert into t1 values(b'101'); 1293ERROR 22001: Data too long for column 'a' at row 1 1294select * from t1; 1295a 1296drop table t1; 1297set sql_mode='traditional'; 1298create table t1 (date date not null); 1299create table t2 select date from t1; 1300show create table t2; 1301Table Create Table 1302t2 CREATE TABLE `t2` ( 1303 `date` date NOT NULL 1304) ENGINE=MyISAM DEFAULT CHARSET=latin1 1305drop table t2,t1; 1306set @@sql_mode= @org_mode; 1307set @@sql_mode='traditional'; 1308create table t1 (i int) 1309comment '123456789*123456789*123456789*123456789*123456789* 1310 123456789*123456789*123456789*123456789*123456789* 1311 123456789*123456789*123456789*123456789*123456789* 1312 123456789*123456789*123456789*123456789*123456789* 1313 123456789*123456789*123456789*123456789*123456789* 1314 123456789*123456789*123456789*123456789*123456789* 1315 123456789*123456789*123456789*123456789*123456789* 1316 123456789*123456789*123456789*123456789*123456789* 1317 123456789*123456789*123456789*123456789*123456789* 1318 123456789*123456789*123456789*123456789*123456789* 1319 123456789*123456789*123456789*123456789*123456789* 1320 123456789*123456789*123456789*123456789*123456789* 1321 123456789*123456789*123456789*123456789*123456789* 1322 123456789*123456789*123456789*123456789*123456789* 1323 123456789*123456789*123456789*123456789*123456789* 1324 123456789*123456789*123456789*123456789*123456789* 1325 123456789*123456789*123456789*123456789*123456789* 1326 123456789*123456789*123456789*123456789*123456789* 1327 123456789*123456789*123456789*123456789*123456789* 1328 123456789*123456789*123456789*123456789*123456789* 1329 123456789*123456789*123456789*123456789*123456789* 1330 123456789*123456789*123456789*123456789*123456789* 1331 123456789*123456789*123456789*123456789*123456789* 1332 123456789*123456789*123456789*123456789*123456789* 1333 123456789*123456789*123456789*123456789*123456789* 1334 123456789*123456789*123456789*123456789*123456789* 1335 123456789*123456789*123456789*123456789*123456789* 1336 123456789*123456789*123456789*123456789*123456789* 1337 123456789*123456789*123456789*123456789*123456789* 1338 123456789*123456789*123456789*123456789*123456789* 1339 123456789*123456789*123456789*123456789*123456789* 1340 123456789*123456789*123456789*123456789*123456789* 1341 123456789*123456789*123456789*123456789*123456789* 1342 123456789*123456789*123456789*123456789*123456789* 1343 123456789*123456789*123456789*123456789*123456789* 1344 123456789*123456789*123456789*123456789*123456789*'; 1345ERROR HY000: Comment for table 't1' is too long (max = 2048) 1346create table t1 ( 1347i int comment 1348'123456789*123456789*123456789*123456789* 1349 123456789*123456789*123456789*123456789* 1350 123456789*123456789*123456789*123456789* 1351 123456789*123456789*123456789*123456789* 1352 123456789*123456789*123456789*123456789* 1353 123456789*123456789*123456789*123456789* 1354 123456789*123456789*123456789*123456789* 1355 123456789*123456789*123456789*123456789* 1356 123456789*123456789*123456789*123456789* 1357 123456789*123456789*123456789*123456789* 1358 123456789*123456789*123456789*123456789* 1359 123456789*123456789*123456789*123456789* 1360 123456789*123456789*123456789*123456789* 1361 123456789*123456789*123456789*123456789* 1362 123456789*123456789*123456789*123456789* 1363 123456789*123456789*123456789*123456789* 1364 123456789*123456789*123456789*123456789* 1365 123456789*123456789*123456789*123456789* 1366 123456789*123456789*123456789*123456789* 1367 123456789*123456789*123456789*123456789* 1368 123456789*123456789*123456789*123456789* 1369 123456789*123456789*123456789*123456789* 1370 123456789*123456789*123456789*123456789* 1371 123456789*123456789*123456789*123456789* 1372 123456789*123456789*123456789*123456789* 1373 123456789*123456789*123456789*123456789* 1374 123456789*123456789*123456789*123456789*'); 1375ERROR HY000: Comment for field 'i' is too long (max = 1024) 1376set @@sql_mode= @org_mode; 1377SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1378create table t1 1379(i int comment 1380'123456789*123456789*123456789*123456789* 1381 123456789*123456789*123456789*123456789* 1382 123456789*123456789*123456789*123456789* 1383 123456789*123456789*123456789*123456789* 1384 123456789*123456789*123456789*123456789* 1385 123456789*123456789*123456789*123456789* 1386 123456789*123456789*123456789*123456789* 1387 123456789*123456789*123456789*123456789* 1388 123456789*123456789*123456789*123456789* 1389 123456789*123456789*123456789*123456789* 1390 123456789*123456789*123456789*123456789* 1391 123456789*123456789*123456789*123456789* 1392 123456789*123456789*123456789*123456789* 1393 123456789*123456789*123456789*123456789* 1394 123456789*123456789*123456789*123456789* 1395 123456789*123456789*123456789*123456789* 1396 123456789*123456789*123456789*123456789* 1397 123456789*123456789*123456789*123456789* 1398 123456789*123456789*123456789*123456789* 1399 123456789*123456789*123456789*123456789* 1400 123456789*123456789*123456789*123456789* 1401 123456789*123456789*123456789*123456789* 1402 123456789*123456789*123456789*123456789* 1403 123456789*123456789*123456789*123456789* 1404 123456789*123456789*123456789*123456789* 1405 123456789*123456789*123456789*123456789* 1406 123456789*123456789*123456789*123456789*'); 1407Warnings: 1408Warning 1629 Comment for field 'i' is too long (max = 1024) 1409select column_name, column_comment from information_schema.columns where 1410table_schema = 'test' and table_name = 't1'; 1411column_name column_comment 1412i 123456789*123456789*123456789*123456789* 1413 123456789*123456789*123456789*123456789* 1414 123456789*123456789*123456789*123456789* 1415 123456789*123456789*123456789*123456789* 1416 123456789*123456789*123456789*123456789* 1417 123456789*123456789*123456789*123456789* 1418 123456789*123456789*123456789*123456789* 1419 123456789*123456789*123456789*123456789* 1420 123456789*123456789*123456789*123456789* 1421 123456789*123456789*123456789*123456789* 1422 123456789*123456789*123456789*123456789* 1423 123456789*123456789*123456789*123456789* 1424 123456789*123456789*123456789*123456789* 1425 123456789*123456789*123456789*123456789* 1426 123456789*123456789*123456789*123456789* 1427 123456789*123456789*123456789*123456789* 1428 123456789*123456789*123456789*123456789* 1429 123456789*123456789*123456789*123456789* 1430 123456789*123456789*123456789*123456789* 1431 123456789*123456789*123456789*123456789* 1432 123456789*123456789*123456789*123456789* 1433 123456789*123456789*123456789*123456789* 1434 123456789*123456789*123456789*123456789* 1435 123456789*123456789*123456789*12345 1436drop table t1; 1437set names utf8; 1438create table t1 (i int) 1439comment '123456789*123456789*123456789*123456789*123456789*123456789*'; 1440show create table t1; 1441Table Create Table 1442t1 CREATE TABLE `t1` ( 1443 `i` int(11) DEFAULT NULL 1444) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*' 1445drop table t1; 1446CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'; 1447SHOW CREATE TABLE t3; 1448Table Create Table 1449t3 CREATE TABLE `t3` ( 1450 `f1` int(11) DEFAULT NULL 1451) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן' 1452DROP TABLE t3; 1453set sql_mode= 'traditional'; 1454create table t1(col1 tinyint, col2 tinyint unsigned, 1455col3 smallint, col4 smallint unsigned, 1456col5 mediumint, col6 mediumint unsigned, 1457col7 int, col8 int unsigned, 1458col9 bigint, col10 bigint unsigned); 1459insert into t1(col1) values('-'); 1460ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col1` at row 1 1461insert into t1(col2) values('+'); 1462ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col2` at row 1 1463insert into t1(col3) values('-'); 1464ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col3` at row 1 1465insert into t1(col4) values('+'); 1466ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col4` at row 1 1467insert into t1(col5) values('-'); 1468ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col5` at row 1 1469insert into t1(col6) values('+'); 1470ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col6` at row 1 1471insert into t1(col7) values('-'); 1472ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col7` at row 1 1473insert into t1(col8) values('+'); 1474ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col8` at row 1 1475insert into t1(col9) values('-'); 1476ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`col9` at row 1 1477insert into t1(col10) values('+'); 1478ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`col10` at row 1 1479drop table t1; 1480set sql_mode='traditional'; 1481create table t1(a year); 1482insert into t1 values ('-'); 1483ERROR 22007: Incorrect integer value: '-' for column `test`.`t1`.`a` at row 1 1484insert into t1 values ('+'); 1485ERROR 22007: Incorrect integer value: '+' for column `test`.`t1`.`a` at row 1 1486insert into t1 values (''); 1487ERROR 22007: Incorrect integer value: '' for column `test`.`t1`.`a` at row 1 1488insert into t1 values ('2000a'); 1489ERROR 01000: Data truncated for column 'a' at row 1 1490insert into t1 values ('2E3x'); 1491ERROR 01000: Data truncated for column 'a' at row 1 1492drop table t1; 1493set sql_mode='traditional'; 1494create table t1 (f1 set('a','a')); 1495ERROR HY000: Column 'f1' has duplicated value 'a' in SET 1496create table t1 (f1 enum('a','a')); 1497ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM 1498set @@sql_mode='NO_ZERO_DATE'; 1499create table t1(a datetime not null); 1500select count(*) from t1 where a is null; 1501count(*) 15020 1503drop table t1; 1504End of 5.0 tests 1505# 1506# Start of 5.6 tests 1507# 1508# 1509# WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME 1510# 1511# 1512# STR_TO_DATE with NO_ZERO_DATE did not return NULL (with warning) 1513# in get_date(). Only did in val_str() and val_int(). 1514SET sql_mode='NO_ZERO_DATE'; 1515SELECT 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; 1516STR_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 15172001-00-00 2001-00-00 20010001 20000001 NULL 1518Warnings: 1519Warning 1411 Incorrect datetime value: '0000' for function str_to_date 1520SET sql_mode='NO_ZERO_IN_DATE'; 1521SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1; 1522STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0000','%Y')+1 1523NULL NULL NULL 1 1524Warnings: 1525Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1526Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1527Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1528# 1529# End of 5.6 tests 1530# 1531