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 'col1' at row 1 12INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); 13ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2 14INSERT INTO t1 VALUES('2004-10-0'); 15ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1 16INSERT INTO t1 VALUES('2004-09-31'); 17ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1 18INSERT INTO t1 VALUES('2004-10-32'); 19ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1 20INSERT INTO t1 VALUES('2003-02-29'); 21ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 22INSERT INTO t1 VALUES('2004-13-15'); 23ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1 24INSERT INTO t1 VALUES('0000-00-00'); 25ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 26INSERT INTO t1 VALUES ('59'); 27ERROR 22007: Incorrect date value: '59' for column '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'; 31Warnings: 32Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. 33INSERT INTO t1 VALUES('2004-0-30'); 34ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1 35INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); 36ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2 37INSERT INTO t1 VALUES('0000-00-00'); 38INSERT IGNORE INTO t1 VALUES('2004-0-29'); 39Warnings: 40Warning 1264 Out of range value for column 'col1' at row 1 41set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; 42Warnings: 43Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 44INSERT INTO t1 VALUES('0000-00-00'); 45ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 46INSERT IGNORE INTO t1 VALUES('0000-00-00'); 47Warnings: 48Warning 1264 Out of range value for column 'col1' at row 1 49INSERT INTO t1 VALUES ('2004-0-30'); 50INSERT INTO t1 VALUES ('2004-2-30'); 51ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1 52set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 53INSERT INTO t1 VALUES ('2004-2-30'); 54set @@sql_mode='ansi,traditional'; 55INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00'); 56Warnings: 57Warning 1265 Data truncated for column 'col1' at row 2 58Warning 1264 Out of range value for column 'col1' at row 3 59select * from t1; 60col1 612004-01-01 622004-02-29 630000-10-31 642004-01-02 652004-01-03 662004-00-31 672004-01-04 680000-00-00 690000-00-00 700000-00-00 712004-00-30 722004-02-30 732004-02-29 740000-00-00 750000-00-00 76drop table t1; 77set @@sql_mode='strict_trans_tables'; 78CREATE TABLE t1 (col1 date) engine=myisam; 79INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); 80ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1 81INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); 82Warnings: 83Warning 1265 Data truncated for column 'col1' at row 2 84INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); 85Warnings: 86Warning 1265 Data truncated for column 'col1' at row 1 87INSERT INTO t1 VALUES ('2003-02-29'); 88ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 89INSERT ignore INTO t1 VALUES('2003-02-30'); 90Warnings: 91Warning 1264 Out of range value for column 'col1' at row 1 92set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 93INSERT ignore INTO t1 VALUES('2003-02-31'); 94select * from t1; 95col1 962004-01-02 970000-00-00 982004-01-03 990000-00-00 1002004-01-04 1010000-00-00 1022003-02-31 103drop table t1; 104set @@sql_mode='strict_trans_tables'; 105CREATE TABLE t1 (col1 date) engine=innodb; 106INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); 107ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1 108INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); 109ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2 110INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); 111Warnings: 112Warning 1265 Data truncated for column 'col1' at row 1 113INSERT INTO t1 VALUES ('2003-02-29'); 114ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 115INSERT ignore INTO t1 VALUES('2003-02-30'); 116Warnings: 117Warning 1264 Out of range value for column 'col1' at row 1 118set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 119INSERT ignore INTO t1 VALUES('2003-02-31'); 120select * from t1; 121col1 1220000-00-00 1232004-01-04 1240000-00-00 1252003-02-31 126drop table t1; 127set @@sql_mode='ansi,traditional'; 128CREATE TABLE t1 (col1 datetime); 129INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); 130INSERT INTO t1 VALUES('0000-10-31 15:30:00'); 131INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 132ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 133INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 134ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 135INSERT INTO t1 VALUES('2004-09-31 15:30:00'); 136ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 137INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 138ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 139INSERT INTO t1 VALUES('2003-02-29 15:30:00'); 140ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1 141INSERT INTO t1 VALUES('2004-13-15 15:30:00'); 142ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 143INSERT INTO t1 VALUES('0000-00-00 15:30:00'); 144ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1 145INSERT INTO t1 VALUES ('59'); 146ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1 147select * from t1; 148col1 1492004-10-31 15:30:00 1502004-02-29 15:30:00 1510000-10-31 15:30:00 152drop table t1; 153CREATE TABLE t1 (col1 timestamp); 154INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); 155INSERT INTO t1 VALUES('0000-10-31 15:30:00'); 156ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1 157INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 158ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 159INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 160ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 161INSERT INTO t1 VALUES('2004-09-31 15:30:00'); 162ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 163INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 164ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 165INSERT INTO t1 VALUES('2003-02-29 15:30:00'); 166ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1 167INSERT INTO t1 VALUES('2004-13-15 15:30:00'); 168ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 169INSERT INTO t1 VALUES('2004-02-29 25:30:00'); 170ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1 171INSERT INTO t1 VALUES('2004-02-29 15:65:00'); 172ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1 173INSERT INTO t1 VALUES('2004-02-29 15:31:61'); 174ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1 175INSERT INTO t1 VALUES('0000-00-00 15:30:00'); 176ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1 177INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 178ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 179INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00'); 180Warnings: 181Warning 1264 Out of range value for column 'col1' at row 1 182INSERT INTO t1 VALUES ('59'); 183ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1 184set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; 185INSERT INTO t1 VALUES('2004-0-31 15:30:00'); 186ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 187INSERT INTO t1 VALUES('2004-10-0 15:30:00'); 188ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 189INSERT INTO t1 VALUES('2004-10-32 15:30:00'); 190ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 191INSERT INTO t1 VALUES('2004-02-30 15:30:04'); 192ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1 193INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 194set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; 195Warnings: 196Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. 197INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 198set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; 199Warnings: 200Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 201INSERT INTO t1 VALUES('0000-00-00 00:00:00'); 202ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 203set @@sql_mode='ansi,traditional'; 204SELECT * FROM t1; 205col1 2062004-10-31 15:30:00 2072004-02-29 15:30:00 2080000-00-00 00:00:00 2090000-00-00 00:00:00 2100000-00-00 00:00:00 211DROP TABLE t1; 212CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 213INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); 214INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); 215INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); 216INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 217ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date 218INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 219ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 220INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 221ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 222INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 223ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1 224INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 225ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 226INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 227ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1 228INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 229ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 230INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 231ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 232INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 233ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date 234INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 235ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 236INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 237ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 238INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 239ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1 240INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 241ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 242INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 243ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1 244INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 245ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 246INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 247ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 248INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); 249ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date 250INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); 251ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date 252INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); 253ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function str_to_date 254INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); 255ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1 256INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 257ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 258INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); 259ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1 260INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); 261ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date 262INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); 263ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date 264drop table t1; 265CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 266INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); 267INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); 268INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); 269INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); 270INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); 271ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 272INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); 273ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 274INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); 275ERROR 22007: Incorrect datetime value: '0000-00-00' 276INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); 277INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); 278ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 279INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); 280ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 281INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); 282ERROR 22007: Incorrect datetime value: '0000-00-00' 283INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); 284ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 285INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); 286ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 287INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); 288ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 289INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); 290ERROR 22007: Incorrect datetime value: '0000-00-00' 291drop table t1; 292CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); 293INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); 294INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); 295INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); 296INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); 297INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); 298ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 299INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); 300ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 301INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); 302ERROR 22007: Incorrect datetime value: '0000-00-00' 303INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); 304INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); 305ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 306INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); 307ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 308INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); 309ERROR 22007: Incorrect datetime value: '0000-00-00' 310INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); 311ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 312INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); 313ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 314INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); 315ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 316INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); 317ERROR 22007: Incorrect datetime value: '0000-00-00' 318drop table t1; 319CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); 320INSERT 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); 321SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; 322MOD(col1,0) 323NULL 324NULL 325Warnings: 326Warning 1365 Division by 0 327Warning 1365 Division by 0 328INSERT INTO t1 (col1) VALUES(-129); 329ERROR 22003: Out of range value for column 'col1' at row 1 330INSERT INTO t1 (col1) VALUES(128); 331ERROR 22003: Out of range value for column 'col1' at row 1 332INSERT INTO t1 (col2) VALUES(-1); 333ERROR 22003: Out of range value for column 'col2' at row 1 334INSERT INTO t1 (col2) VALUES(256); 335ERROR 22003: Out of range value for column 'col2' at row 1 336INSERT INTO t1 (col1) VALUES('-129'); 337ERROR 22003: Out of range value for column 'col1' at row 1 338INSERT INTO t1 (col1) VALUES('128'); 339ERROR 22003: Out of range value for column 'col1' at row 1 340INSERT INTO t1 (col2) VALUES('-1'); 341ERROR 22003: Out of range value for column 'col2' at row 1 342INSERT INTO t1 (col2) VALUES('256'); 343ERROR 22003: Out of range value for column 'col2' at row 1 344INSERT INTO t1 (col1) VALUES(128.0); 345ERROR 22003: Out of range value for column 'col1' at row 1 346INSERT INTO t1 (col2) VALUES(-1.0); 347ERROR 22003: Out of range value for column 'col2' at row 1 348INSERT INTO t1 (col2) VALUES(256.0); 349ERROR 22003: Out of range value for column 'col2' at row 1 350SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1; 351MOD(col1,0) 352NULL 353Warnings: 354Warning 1365 Division by 0 355UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 356ERROR 22003: Out of range value for column 'col1' at row 1 357UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0; 358ERROR 22003: Out of range value for column 'col2' at row 3 359UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0; 360ERROR 22012: Division by 0 361set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 362Warnings: 363Warning 1681 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release. 364INSERT INTO t1 values (1/0,1/0); 365Warnings: 366Warning 1365 Division by 0 367Warning 1365 Division by 0 368set @@sql_mode='ansi,traditional'; 369SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; 370MOD(col1,0) 371NULL 372NULL 373Warnings: 374Warning 1365 Division by 0 375Warning 1365 Division by 0 376INSERT INTO t1 (col1) VALUES (''); 377ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 378INSERT INTO t1 (col1) VALUES ('a59b'); 379ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 380INSERT INTO t1 (col1) VALUES ('1a'); 381ERROR 01000: Data truncated for column 'col1' at row 1 382INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 383Warnings: 384Warning 1265 Data truncated for column 'col1' at row 1 385INSERT IGNORE INTO t1 values (1/0,1/0); 386Warnings: 387Warning 1365 Division by 0 388Warning 1365 Division by 0 389set @@sql_mode='ansi'; 390INSERT INTO t1 values (1/0,1/0); 391set @@sql_mode='ansi,traditional'; 392INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256'); 393Warnings: 394Warning 1264 Out of range value for column 'col1' at row 1 395Warning 1264 Out of range value for column 'col2' at row 1 396Warning 1264 Out of range value for column 'col1' at row 2 397Warning 1264 Out of range value for column 'col2' at row 2 398INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0); 399Warnings: 400Warning 1264 Out of range value for column 'col1' at row 1 401Warning 1264 Out of range value for column 'col2' at row 1 402Warning 1264 Out of range value for column 'col1' at row 2 403Warning 1264 Out of range value for column 'col2' at row 2 404UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 405SELECT * FROM t1; 406col1 col2 407-128 0 4080 NULL 409127 255 410-128 0 4110 NULL 412127 255 413-128 0 4140 NULL 415127 255 416NULL NULL 4172 NULL 418NULL NULL 419NULL NULL 420-128 0 421127 255 422-128 0 423127 255 424DROP TABLE t1; 425CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED); 426INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0); 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'); 436ERROR 22003: Out of range value for column 'col1' at row 1 437INSERT INTO t1 (col1) VALUES('32768'); 438ERROR 22003: Out of range value for column 'col1' at row 1 439INSERT INTO t1 (col2) VALUES('-1'); 440ERROR 22003: Out of range value for column 'col2' at row 1 441INSERT INTO t1 (col2) VALUES('65536'); 442ERROR 22003: Out of range value for column 'col2' at row 1 443INSERT INTO t1 (col1) VALUES(-32769.0); 444ERROR 22003: Out of range value for column 'col1' at row 1 445INSERT INTO t1 (col1) VALUES(32768.0); 446ERROR 22003: Out of range value for column 'col1' at row 1 447INSERT INTO t1 (col2) VALUES(-1.0); 448ERROR 22003: Out of range value for column 'col2' at row 1 449INSERT INTO t1 (col2) VALUES(65536.0); 450ERROR 22003: Out of range value for column 'col2' at row 1 451UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 452ERROR 22003: Out of range value for column 'col1' at row 1 453UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; 454ERROR 22003: Out of range value for column 'col2' at row 3 455UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0; 456ERROR 22012: Division by 0 457UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 458ERROR 22012: Division by 0 459INSERT INTO t1 (col1) VALUES (''); 460ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 461INSERT INTO t1 (col1) VALUES ('a59b'); 462ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 463INSERT INTO t1 (col1) VALUES ('1a'); 464ERROR 01000: Data truncated for column 'col1' at row 1 465INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 466Warnings: 467Warning 1265 Data truncated for column 'col1' at row 1 468INSERT IGNORE INTO t1 values (1/0,1/0); 469Warnings: 470Warning 1365 Division by 0 471Warning 1365 Division by 0 472INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536); 473Warnings: 474Warning 1264 Out of range value for column 'col1' at row 1 475Warning 1264 Out of range value for column 'col2' at row 1 476Warning 1264 Out of range value for column 'col1' at row 2 477Warning 1264 Out of range value for column 'col2' at row 2 478INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536'); 479Warnings: 480Warning 1264 Out of range value for column 'col1' at row 1 481Warning 1264 Out of range value for column 'col2' at row 1 482Warning 1264 Out of range value for column 'col1' at row 2 483Warning 1264 Out of range value for column 'col2' at row 2 484INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0); 485Warnings: 486Warning 1264 Out of range value for column 'col1' at row 1 487Warning 1264 Out of range value for column 'col2' at row 1 488Warning 1264 Out of range value for column 'col1' at row 2 489Warning 1264 Out of range value for column 'col2' at row 2 490UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 491SELECT * FROM t1; 492col1 col2 493-32768 0 4940 NULL 49532767 65535 496-32768 0 49732767 65535 498-32768 0 49932767 65535 5002 NULL 501NULL NULL 502-32768 0 50332767 65535 504-32768 0 50532767 65535 506-32768 0 50732767 65535 508DROP TABLE t1; 509CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED); 510INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0); 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'); 520ERROR 22003: Out of range value for column 'col1' at row 1 521INSERT INTO t1 (col1) VALUES('8388608'); 522ERROR 22003: Out of range value for column 'col1' at row 1 523INSERT INTO t1 (col2) VALUES('-1'); 524ERROR 22003: Out of range value for column 'col2' at row 1 525INSERT INTO t1 (col2) VALUES('16777216'); 526ERROR 22003: Out of range value for column 'col2' at row 1 527INSERT INTO t1 (col1) VALUES(-8388609.0); 528ERROR 22003: Out of range value for column 'col1' at row 1 529INSERT INTO t1 (col1) VALUES(8388608.0); 530ERROR 22003: Out of range value for column 'col1' at row 1 531INSERT INTO t1 (col2) VALUES(-1.0); 532ERROR 22003: Out of range value for column 'col2' at row 1 533INSERT INTO t1 (col2) VALUES(16777216.0); 534ERROR 22003: Out of range value for column 'col2' at row 1 535UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 536ERROR 22003: Out of range value for column 'col1' at row 1 537UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; 538ERROR 22003: Out of range value for column 'col2' at row 3 539UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 540ERROR 22012: Division by 0 541UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 542ERROR 22012: Division by 0 543INSERT INTO t1 (col1) VALUES (''); 544ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 545INSERT INTO t1 (col1) VALUES ('a59b'); 546ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 547INSERT INTO t1 (col1) VALUES ('1a'); 548ERROR 01000: Data truncated for column 'col1' at row 1 549INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 550Warnings: 551Warning 1265 Data truncated for column 'col1' at row 1 552INSERT IGNORE INTO t1 values (1/0,1/0); 553Warnings: 554Warning 1365 Division by 0 555Warning 1365 Division by 0 556INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216); 557Warnings: 558Warning 1264 Out of range value for column 'col1' at row 1 559Warning 1264 Out of range value for column 'col2' at row 1 560Warning 1264 Out of range value for column 'col1' at row 2 561Warning 1264 Out of range value for column 'col2' at row 2 562INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216'); 563Warnings: 564Warning 1264 Out of range value for column 'col1' at row 1 565Warning 1264 Out of range value for column 'col2' at row 1 566Warning 1264 Out of range value for column 'col1' at row 2 567Warning 1264 Out of range value for column 'col2' at row 2 568INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0); 569Warnings: 570Warning 1264 Out of range value for column 'col1' at row 1 571Warning 1264 Out of range value for column 'col2' at row 1 572Warning 1264 Out of range value for column 'col1' at row 2 573Warning 1264 Out of range value for column 'col2' at row 2 574UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 575SELECT * FROM t1; 576col1 col2 577-8388608 0 5780 NULL 5798388607 16777215 580-8388608 0 5818388607 16777215 582-8388608 0 5838388607 16777215 5842 NULL 585NULL NULL 586-8388608 0 5878388607 16777215 588-8388608 0 5898388607 16777215 590-8388608 0 5918388607 16777215 592DROP TABLE t1; 593CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED); 594INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0); 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'); 604ERROR 22003: Out of range value for column 'col1' at row 1 605INSERT INTO t1 (col1) VALUES('2147643648'); 606ERROR 22003: Out of range value for column 'col1' at row 1 607INSERT INTO t1 (col2) VALUES('-1'); 608ERROR 22003: Out of range value for column 'col2' at row 1 609INSERT INTO t1 (col2) VALUES('4294967296'); 610ERROR 22003: Out of range value for column 'col2' at row 1 611INSERT INTO t1 (col1) VALUES(-2147483649.0); 612ERROR 22003: Out of range value for column 'col1' at row 1 613INSERT INTO t1 (col1) VALUES(2147643648.0); 614ERROR 22003: Out of range value for column 'col1' at row 1 615INSERT INTO t1 (col2) VALUES(-1.0); 616ERROR 22003: Out of range value for column 'col2' at row 1 617INSERT INTO t1 (col2) VALUES(4294967296.0); 618ERROR 22003: Out of range value for column 'col2' at row 1 619UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; 620ERROR 22003: Out of range value for column 'col1' at row 1 621UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0; 622ERROR 22003: Out of range value for column 'col2' at row 3 623UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 624ERROR 22012: Division by 0 625UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 626ERROR 22012: Division by 0 627INSERT INTO t1 (col1) VALUES (''); 628ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 629INSERT INTO t1 (col1) VALUES ('a59b'); 630ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 631INSERT INTO t1 (col1) VALUES ('1a'); 632ERROR 01000: Data truncated for column 'col1' at row 1 633INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 634Warnings: 635Warning 1265 Data truncated for column 'col1' at row 1 636INSERT IGNORE INTO t1 values (1/0,1/0); 637Warnings: 638Warning 1365 Division by 0 639Warning 1365 Division by 0 640INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296); 641Warnings: 642Warning 1264 Out of range value for column 'col1' at row 1 643Warning 1264 Out of range value for column 'col2' at row 1 644Warning 1264 Out of range value for column 'col1' at row 2 645Warning 1264 Out of range value for column 'col2' at row 2 646INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296'); 647Warnings: 648Warning 1264 Out of range value for column 'col1' at row 1 649Warning 1264 Out of range value for column 'col2' at row 1 650Warning 1264 Out of range value for column 'col1' at row 2 651Warning 1264 Out of range value for column 'col2' at row 2 652INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0); 653Warnings: 654Warning 1264 Out of range value for column 'col1' at row 1 655Warning 1264 Out of range value for column 'col2' at row 1 656Warning 1264 Out of range value for column 'col1' at row 2 657Warning 1264 Out of range value for column 'col2' at row 2 658UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 659SELECT * FROM t1; 660col1 col2 661-2147483648 0 6620 NULL 6632147483647 4294967295 664-2147483648 0 6652147483647 4294967295 666-2147483648 0 6672147483647 4294967295 6682 NULL 669NULL NULL 670-2147483648 0 6712147483647 4294967295 672-2147483648 0 6732147483647 4294967295 674-2147483648 0 6752147483647 4294967295 676DROP TABLE t1; 677CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED); 678INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615); 679INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); 680INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); 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('-9223372036854775809'); 690ERROR 22003: Out of range value for column 'col1' at row 1 691INSERT INTO t1 (col1) VALUES('9223372036854775808'); 692ERROR 22003: Out of range value for column 'col1' at row 1 693INSERT INTO t1 (col2) VALUES('-1'); 694ERROR 22003: Out of range value for column 'col2' at row 1 695INSERT INTO t1 (col2) VALUES('18446744073709551616'); 696ERROR 22003: Out of range value for column 'col2' at row 1 697INSERT INTO t1 (col1) VALUES(-9223372036854785809.0); 698ERROR 22003: Out of range value for column 'col1' at row 1 699INSERT INTO t1 (col1) VALUES(9223372036854785808.0); 700ERROR 22003: Out of range value for column 'col1' at row 1 701INSERT INTO t1 (col2) VALUES(-1.0); 702ERROR 22003: Out of range value for column 'col2' at row 1 703INSERT INTO t1 (col2) VALUES(18446744073709551616.0); 704ERROR 22003: Out of range value for column 'col2' at row 1 705UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 706ERROR 22012: Division by 0 707UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 708ERROR 22012: Division by 0 709INSERT INTO t1 (col1) VALUES (''); 710ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 711INSERT INTO t1 (col1) VALUES ('a59b'); 712ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 713INSERT INTO t1 (col1) VALUES ('1a'); 714ERROR 01000: Data truncated for column 'col1' at row 1 715INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 716Warnings: 717Warning 1265 Data truncated for column 'col1' at row 1 718INSERT IGNORE INTO t1 values (1/0,1/0); 719Warnings: 720Warning 1365 Division by 0 721Warning 1365 Division by 0 722INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); 723Warnings: 724Warning 1264 Out of range value for column 'col1' at row 1 725Warning 1264 Out of range value for column 'col2' at row 1 726Warning 1264 Out of range value for column 'col1' at row 2 727Warning 1264 Out of range value for column 'col2' at row 2 728INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); 729Warnings: 730Warning 1264 Out of range value for column 'col1' at row 1 731Warning 1264 Out of range value for column 'col2' at row 1 732Warning 1264 Out of range value for column 'col1' at row 2 733Warning 1264 Out of range value for column 'col2' at row 2 734INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0); 735Warnings: 736Warning 1264 Out of range value for column 'col1' at row 1 737Warning 1264 Out of range value for column 'col2' at row 1 738Warning 1264 Out of range value for column 'col1' at row 2 739Warning 1264 Out of range value for column 'col2' at row 2 740UPDATE IGNORE t1 SET col2=1/NULL where col1=0; 741SELECT * FROM t1; 742col1 col2 743-9223372036854775808 0 7440 NULL 7459223372036854775807 18446744073709551615 746-9223372036854775808 0 7479223372036854775807 18446744073709551615 748-9223372036854774000 0 7499223372036854775700 1844674407370954000 7502 NULL 751NULL NULL 752-9223372036854775808 0 7539223372036854775807 18446744073709551615 754-9223372036854775808 0 7559223372036854775807 18446744073709551615 756-9223372036854775808 0 7579223372036854775807 18446744073709551615 758DROP TABLE t1; 759CREATE TABLE t1 (col1 NUMERIC(4,2)); 760INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); 761Warnings: 762Note 1265 Data truncated for column 'col1' at row 2 763Note 1265 Data truncated for column 'col1' at row 5 764INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); 765Warnings: 766Note 1265 Data truncated for column 'col1' at row 2 767Note 1265 Data truncated for column 'col1' at row 4 768INSERT INTO t1 VALUES (101.55); 769ERROR 22003: Out of range value for column 'col1' at row 1 770INSERT INTO t1 VALUES (101); 771ERROR 22003: Out of range value for column 'col1' at row 1 772INSERT INTO t1 VALUES (-101.55); 773ERROR 22003: Out of range value for column 'col1' at row 1 774INSERT INTO t1 VALUES (1010.55); 775ERROR 22003: Out of range value for column 'col1' at row 1 776INSERT INTO t1 VALUES (1010); 777ERROR 22003: Out of range value for column 'col1' at row 1 778INSERT INTO t1 VALUES ('101.55'); 779ERROR 22003: Out of range value for column 'col1' at row 1 780INSERT INTO t1 VALUES ('101'); 781ERROR 22003: Out of range value for column 'col1' at row 1 782INSERT INTO t1 VALUES ('-101.55'); 783ERROR 22003: Out of range value for column 'col1' at row 1 784INSERT INTO t1 VALUES ('-1010.55'); 785ERROR 22003: Out of range value for column 'col1' at row 1 786INSERT INTO t1 VALUES ('-100E+1'); 787ERROR 22003: Out of range value for column 'col1' at row 1 788INSERT INTO t1 VALUES ('-100E'); 789ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1 790UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; 791ERROR 22003: Out of range value for column 'col1' at row 6 792UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; 793ERROR 22012: Division by 0 794UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; 795ERROR 22012: Division by 0 796INSERT INTO t1 (col1) VALUES (''); 797ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1 798INSERT INTO t1 (col1) VALUES ('a59b'); 799ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1 800INSERT INTO t1 (col1) VALUES ('1a'); 801ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1 802INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 803Warnings: 804Note 1265 Data truncated for column 'col1' at row 1 805INSERT IGNORE INTO t1 values (1/0); 806Warnings: 807Warning 1365 Division by 0 808INSERT IGNORE INTO t1 VALUES(1000),(-1000); 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 812INSERT IGNORE INTO t1 VALUES('1000'),('-1000'); 813Warnings: 814Warning 1264 Out of range value for column 'col1' at row 1 815Warning 1264 Out of range value for column 'col1' at row 2 816INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0); 817Warnings: 818Warning 1264 Out of range value for column 'col1' at row 1 819Warning 1264 Out of range value for column 'col1' at row 2 820UPDATE IGNORE t1 SET col1=1/NULL where col1=0; 821SELECT * FROM t1; 822col1 82310.55 82410.56 825NULL 826-10.55 827-10.56 82811.00 82910.00 83010.55 83110.56 832-10.55 833-10.56 83411.00 83510.00 8362.00 837NULL 83899.99 839-99.99 84099.99 841-99.99 84299.99 843-99.99 844DROP TABLE t1; 845CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED); 846INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38); 847INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38'); 848INSERT INTO t1 (col1) VALUES (3E-46); 849INSERT INTO t1 (col1) VALUES (+3.4E+39); 850ERROR 22003: Out of range value for column 'col1' at row 1 851INSERT INTO t1 (col2) VALUES (-1.1E-3); 852ERROR 22003: Out of range value for column 'col2' at row 1 853INSERT INTO t1 (col1) VALUES ('+3.4E+39'); 854ERROR 22003: Out of range value for column 'col1' at row 1 855INSERT INTO t1 (col2) VALUES ('-1.1E-3'); 856ERROR 22003: Out of range value for column 'col2' at row 1 857UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; 858ERROR 22003: Out of range value for column 'col1' at row 2 859UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; 860ERROR 22012: Division by 0 861UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; 862ERROR 22012: Division by 0 863INSERT INTO t1 (col1) VALUES (''); 864ERROR 01000: Data truncated for column 'col1' at row 1 865INSERT INTO t1 (col1) VALUES ('a59b'); 866ERROR 01000: Data truncated for column 'col1' at row 1 867INSERT INTO t1 (col1) VALUES ('1a'); 868ERROR 01000: Data truncated for column 'col1' at row 1 869INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 870Warnings: 871Warning 1265 Data truncated for column 'col1' at row 1 872INSERT IGNORE INTO t1 (col1) VALUES (1/0); 873Warnings: 874Warning 1365 Division by 0 875INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39); 876Warnings: 877Warning 1264 Out of range value for column 'col1' at row 1 878Warning 1264 Out of range value for column 'col2' at row 1 879INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39'); 880Warnings: 881Warning 1264 Out of range value for column 'col1' at row 1 882Warning 1264 Out of range value for column 'col2' at row 1 883SELECT * FROM t1; 884col1 col2 885-1.1e-37 0 8863.4e38 3.4e38 887-1.1e-37 0 8883.4e38 3.4e38 8890 NULL 8902 NULL 891NULL NULL 8923.40282e38 0 8933.40282e38 0 894DROP TABLE t1; 895CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); 896INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308); 897INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308'); 898INSERT INTO t1 (col1) VALUES (-2.2E-330); 899INSERT INTO t1 (col1) VALUES (+1.7E+309); 900Got one of the listed errors 901INSERT INTO t1 (col2) VALUES (-1.1E-3); 902ERROR 22003: Out of range value for column 'col2' at row 1 903INSERT INTO t1 (col1) VALUES ('+1.8E+309'); 904ERROR 22003: Out of range value for column 'col1' at row 1 905INSERT INTO t1 (col2) VALUES ('-1.2E-3'); 906ERROR 22003: Out of range value for column 'col2' at row 1 907UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; 908ERROR 22003: DOUBLE value is out of range in '("test"."t1"."col1" * 5000)' 909UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; 910ERROR 22012: Division by 0 911UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; 912ERROR 22012: Division by 0 913INSERT INTO t1 (col1) VALUES (''); 914ERROR 01000: Data truncated for column 'col1' at row 1 915INSERT INTO t1 (col1) VALUES ('a59b'); 916ERROR 01000: Data truncated for column 'col1' at row 1 917INSERT INTO t1 (col1) VALUES ('1a'); 918ERROR 01000: Data truncated for column 'col1' at row 1 919INSERT IGNORE INTO t1 (col1) VALUES ('2a'); 920Warnings: 921Warning 1265 Data truncated for column 'col1' at row 1 922INSERT IGNORE INTO t1 (col1) values (1/0); 923Warnings: 924Warning 1365 Division by 0 925INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309); 926ERROR 22007: Illegal double '1.9E+309' value found during parsing 927INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309'); 928Warnings: 929Warning 1264 Out of range value for column 'col1' at row 1 930Warning 1264 Out of range value for column 'col2' at row 1 931Warning 1264 Out of range value for column 'col2' at row 1 932SELECT * FROM t1; 933col1 col2 934-2.2e-307 0 9351e-303 0 9361.7e308 1.7e308 937-2.2e-307 0 938-2e-307 0 9391.7e308 1.7e308 9400 NULL 9412 NULL 942NULL NULL 9431.7976931348623157e308 0 944DROP TABLE t1; 945CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); 946INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); 947INSERT INTO t1 (col1) VALUES ('hellobob'); 948ERROR 22001: Data too long for column 'col1' at row 1 949INSERT INTO t1 (col2) VALUES ('hellobob'); 950ERROR 22001: Data too long for column 'col2' at row 1 951INSERT INTO t1 (col2) VALUES ('hello '); 952Warnings: 953Note 1265 Data truncated for column 'col2' at row 1 954UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; 955ERROR 22001: Data too long for column 'col1' at row 2 956UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; 957ERROR 22001: Data too long for column 'col2' at row 2 958INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); 959Warnings: 960Warning 1265 Data truncated for column 'col1' at row 1 961Warning 1265 Data truncated for column 'col2' at row 1 962UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; 963Warnings: 964Warning 1265 Data truncated for column 'col2' at row 2 965SELECT * FROM t1; 966col1 col2 967hello hello 968he hellot 969hello hello 970NULL hello 971hello hellob 972DROP TABLE t1; 973CREATE TABLE t1 (col1 enum('red','blue','green')); 974INSERT INTO t1 VALUES ('red'),('blue'),('green'); 975INSERT INTO t1 (col1) VALUES ('yellow'); 976ERROR 01000: Data truncated for column 'col1' at row 1 977INSERT INTO t1 (col1) VALUES ('redd'); 978ERROR 01000: Data truncated for column 'col1' at row 1 979INSERT INTO t1 VALUES (''); 980ERROR 01000: Data truncated for column 'col1' at row 1 981UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; 982ERROR 01000: Data truncated for column 'col1' at row 3 983INSERT IGNORE INTO t1 VALUES ('yellow'); 984Warnings: 985Warning 1265 Data truncated for column 'col1' at row 1 986UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; 987Warnings: 988Warning 1265 Data truncated for column 'col1' at row 2 989SELECT * FROM t1; 990col1 991red 992 993green 994 995DROP TABLE t1; 996CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL); 997INSERT INTO t1 VALUES (100, 'hello', '2004-08-20'); 998INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21'); 999INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); 1000ERROR 23000: Column 'col1' cannot be null 1001INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); 1002ERROR 23000: Column 'col2' cannot be null 1003INSERT INTO t1 VALUES (103,'',NULL); 1004ERROR 23000: Column 'col3' cannot be null 1005UPDATE t1 SET col1=NULL WHERE col1 =100; 1006ERROR 23000: Column 'col1' cannot be null 1007UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; 1008ERROR 23000: Column 'col2' cannot be null 1009UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; 1010ERROR 23000: Column 'col2' cannot be null 1011INSERT IGNORE INTO t1 values (NULL,NULL,NULL); 1012Warnings: 1013Warning 1048 Column 'col1' cannot be null 1014Warning 1048 Column 'col2' cannot be null 1015Warning 1048 Column 'col3' cannot be null 1016SELECT * FROM t1; 1017col1 col2 col3 1018100 hello 2004-08-20 1019101 hell2 2004-08-21 10200 0000-00-00 1021DROP TABLE t1; 1022CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); 1023SHOW CREATE TABLE t1; 1024Table Create Table 1025t1 CREATE TABLE "t1" ( 1026 "col1" int(11) NOT NULL DEFAULT '99', 1027 "col2" char(6) NOT NULL 1028) 1029INSERT INTO t1 VALUES (1, 'hello'); 1030INSERT INTO t1 (col2) VALUES ('hello2'); 1031INSERT INTO t1 (col2) VALUES (NULL); 1032ERROR 23000: Column 'col2' cannot be null 1033INSERT INTO t1 (col1) VALUES (2); 1034ERROR HY000: Field 'col2' doesn't have a default value 1035INSERT INTO t1 VALUES(default(col1),default(col2)); 1036ERROR HY000: Field 'col2' doesn't have a default value 1037INSERT INTO t1 (col1) SELECT 1; 1038ERROR HY000: Field 'col2' doesn't have a default value 1039INSERT INTO t1 SELECT 1,NULL; 1040ERROR 23000: Column 'col2' cannot be null 1041INSERT IGNORE INTO t1 values (NULL,NULL); 1042Warnings: 1043Warning 1048 Column 'col1' cannot be null 1044Warning 1048 Column 'col2' cannot be null 1045INSERT IGNORE INTO t1 (col1) values (3); 1046Warnings: 1047Warning 1364 Field 'col2' doesn't have a default value 1048INSERT IGNORE INTO t1 () values (); 1049Warnings: 1050Warning 1364 Field 'col2' doesn't have a default value 1051SELECT * FROM t1; 1052col1 col2 10531 hello 105499 hello2 10550 10563 105799 1058DROP TABLE t1; 1059set sql_mode='traditional'; 1060create table t1 (charcol char(255), varcharcol varchar(255), 1061binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, 1062tinyblobcol tinyblob); 1063insert into t1 (charcol) values (repeat('x',256)); 1064ERROR 22001: Data too long for column 'charcol' at row 1 1065insert into t1 (varcharcol) values (repeat('x',256)); 1066ERROR 22001: Data too long for column 'varcharcol' at row 1 1067insert into t1 (binarycol) values (repeat('x',256)); 1068ERROR 22001: Data too long for column 'binarycol' at row 1 1069insert into t1 (varbinarycol) values (repeat('x',256)); 1070ERROR 22001: Data too long for column 'varbinarycol' at row 1 1071insert into t1 (tinytextcol) values (repeat('x',256)); 1072ERROR 22001: Data too long for column 'tinytextcol' at row 1 1073insert into t1 (tinyblobcol) values (repeat('x',256)); 1074ERROR 22001: Data too long for column 'tinyblobcol' at row 1 1075select * from t1; 1076charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol 1077drop table t1; 1078set sql_mode='traditional'; 1079create table t1 (col1 datetime); 1080insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); 1081ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc' 1082insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 1083ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 1084insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); 1085ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date 1086insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); 1087ERROR HY000: Incorrect time value: 'abc' for function str_to_date 1088set sql_mode=''; 1089insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); 1090Warnings: 1091Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc' 1092insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 1093Warnings: 1094Warning 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_date 1095insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); 1096Warnings: 1097Warning 1411 Incorrect time value: '22:22:33 AM' for function str_to_date 1098insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); 1099Warnings: 1100Warning 1411 Incorrect time value: 'abc' for function str_to_date 1101insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); 1102insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); 1103insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); 1104select * from t1; 1105col1 11062004-10-31 15:30:00 1107NULL 1108NULL 1109NULL 11102004-10-31 15:30:00 11112004-12-12 11:22:33 11122004-12-12 10:22:59 1113set sql_mode='traditional'; 1114select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; 1115count(*) 11167 1117Warnings: 1118Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1119Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1120Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1121Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1122Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1123Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date 1124drop table t1; 1125create table t1 (col1 char(3), col2 integer); 1126insert into t1 (col1) values (cast(1000 as char(3))); 1127ERROR 22007: Truncated incorrect CHAR(3) value: '1000' 1128insert into t1 (col1) values (cast(1000E+0 as char(3))); 1129ERROR 22007: Truncated incorrect CHAR(3) value: '1000' 1130insert into t1 (col1) values (cast(1000.0 as char(3))); 1131ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' 1132insert into t1 (col2) values (cast('abc' as signed integer)); 1133ERROR 22007: Truncated incorrect INTEGER value: 'abc' 1134insert into t1 (col2) values (10E+0 + 'a'); 1135ERROR 22007: Truncated incorrect DOUBLE value: 'a' 1136insert into t1 (col2) values (cast('10a' as unsigned integer)); 1137ERROR 22007: Truncated incorrect INTEGER value: '10a' 1138insert into t1 (col2) values (cast('10' as unsigned integer)); 1139insert into t1 (col2) values (cast('10' as signed integer)); 1140insert into t1 (col2) values (10E+0 + '0 '); 1141select * from t1; 1142col1 col2 1143NULL 10 1144NULL 10 1145NULL 10 1146drop table t1; 1147create table t1 (col1 date, col2 datetime, col3 timestamp); 1148insert into t1 values (0,0,0); 1149ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 1150insert into t1 values (0.0,0.0,0.0); 1151ERROR 22007: Incorrect date value: '0.0' for column 'col1' at row 1 1152insert into t1 (col1) values (convert('0000-00-00',date)); 1153ERROR 22007: Incorrect datetime value: '0000-00-00' 1154insert into t1 (col1) values (cast('0000-00-00' as date)); 1155ERROR 22007: Incorrect datetime value: '0000-00-00' 1156set sql_mode='no_zero_date'; 1157Warnings: 1158Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 1159insert into t1 values (0,0,0); 1160Warnings: 1161Warning 1264 Out of range value for column 'col1' at row 1 1162Warning 1264 Out of range value for column 'col2' at row 1 1163Warning 1264 Out of range value for column 'col3' at row 1 1164insert into t1 values (0.0,0.0,0.0); 1165Warnings: 1166Warning 1264 Out of range value for column 'col1' at row 1 1167Warning 1264 Out of range value for column 'col2' at row 1 1168Warning 1264 Out of range value for column 'col3' at row 1 1169drop table t1; 1170set sql_mode='traditional'; 1171create table t1 (col1 date); 1172insert ignore into t1 values ('0000-00-00'); 1173Warnings: 1174Warning 1264 Out of range value for column 'col1' at row 1 1175insert into t1 select * from t1; 1176ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 1177insert ignore into t1 values ('0000-00-00'); 1178Warnings: 1179Warning 1264 Out of range value for column 'col1' at row 1 1180insert ignore into t1 (col1) values (cast('0000-00-00' as date)); 1181Warnings: 1182Warning 1292 Incorrect datetime value: '0000-00-00' 1183insert into t1 select * from t1; 1184ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 1185alter table t1 modify col1 datetime; 1186ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1 1187alter ignore table t1 modify col1 datetime; 1188Warnings: 1189Warning 1681 'IGNORE' is deprecated and will be removed in a future release. 1190Warning 1264 Out of range value for column 'col1' at row 1 1191Warning 1264 Out of range value for column 'col1' at row 2 1192insert into t1 select * from t1; 1193ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 1194select * from t1; 1195col1 11960000-00-00 00:00:00 11970000-00-00 00:00:00 1198NULL 1199drop table t1; 1200create table t1 (col1 tinyint); 1201drop procedure if exists t1; 1202Warnings: 1203Note 1305 PROCEDURE test.t1 does not exist 1204create procedure t1 () begin declare exit handler for sqlexception 1205select'a'; insert into t1 values (200); end;| 1206call t1(); 1207a 1208a 1209select * from t1; 1210col1 1211drop procedure t1; 1212drop table t1; 1213set sql_mode=@org_mode; 1214SET @@sql_mode = 'traditional'; 1215CREATE TABLE t1 (i int not null); 1216INSERT INTO t1 VALUES (); 1217ERROR HY000: Field 'i' doesn't have a default value 1218INSERT INTO t1 VALUES (DEFAULT); 1219ERROR HY000: Field 'i' doesn't have a default value 1220INSERT INTO t1 VALUES (DEFAULT(i)); 1221ERROR HY000: Field 'i' doesn't have a default value 1222ALTER TABLE t1 ADD j int; 1223INSERT INTO t1 SET j = 1; 1224ERROR HY000: Field 'i' doesn't have a default value 1225INSERT INTO t1 SET j = 1, i = DEFAULT; 1226ERROR HY000: Field 'i' doesn't have a default value 1227INSERT INTO t1 SET j = 1, i = DEFAULT(i); 1228ERROR HY000: Field 'i' doesn't have a default value 1229INSERT INTO t1 VALUES (DEFAULT,1); 1230ERROR HY000: Field 'i' doesn't have a default value 1231DROP TABLE t1; 1232SET @@sql_mode = ''; 1233CREATE TABLE t1 (i int not null); 1234INSERT INTO t1 VALUES (); 1235Warnings: 1236Warning 1364 Field 'i' doesn't have a default value 1237INSERT INTO t1 VALUES (DEFAULT); 1238Warnings: 1239Warning 1364 Field 'i' doesn't have a default value 1240INSERT INTO t1 VALUES (DEFAULT(i)); 1241ERROR HY000: Field 'i' doesn't have a default value 1242ALTER TABLE t1 ADD j int; 1243INSERT INTO t1 SET j = 1; 1244Warnings: 1245Warning 1364 Field 'i' doesn't have a default value 1246INSERT INTO t1 SET j = 1, i = DEFAULT; 1247Warnings: 1248Warning 1364 Field 'i' doesn't have a default value 1249INSERT INTO t1 SET j = 1, i = DEFAULT(i); 1250ERROR HY000: Field 'i' doesn't have a default value 1251INSERT INTO t1 VALUES (DEFAULT,1); 1252Warnings: 1253Warning 1364 Field 'i' doesn't have a default value 1254DROP TABLE t1; 1255set @@sql_mode='traditional'; 1256create table t1(a varchar(65537)); 1257ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead 1258create table t1(a varbinary(65537)); 1259ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead 1260set @@sql_mode='traditional'; 1261create table t1(a int, b date not null); 1262alter table t1 modify a bigint unsigned not null; 1263show create table t1; 1264Table Create Table 1265t1 CREATE TABLE `t1` ( 1266 `a` bigint(20) unsigned NOT NULL, 1267 `b` date NOT NULL 1268) ENGINE=MyISAM DEFAULT CHARSET=latin1 1269drop table t1; 1270set @@sql_mode='traditional'; 1271create table t1 (d date); 1272insert into t1 values ('2000-10-00'); 1273ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1 1274insert into t1 values (1000); 1275ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1 1276insert into t1 values ('2000-10-01'); 1277update t1 set d = 1100; 1278ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1 1279select * from t1; 1280d 12812000-10-01 1282drop table t1; 1283set @@sql_mode='traditional'; 1284create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1285alter table t1 add primary key(a); 1286show create table t1; 1287Table Create Table 1288t1 CREATE TABLE `t1` ( 1289 `a` int(11) NOT NULL DEFAULT '0', 1290 `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 1291 PRIMARY KEY (`a`) 1292) ENGINE=MyISAM DEFAULT CHARSET=latin1 1293drop table t1; 1294create table t1(a int, b timestamp not null default 20050102030405); 1295alter table t1 add primary key(a); 1296show create table t1; 1297Table Create Table 1298t1 CREATE TABLE `t1` ( 1299 `a` int(11) NOT NULL DEFAULT '0', 1300 `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05', 1301 PRIMARY KEY (`a`) 1302) ENGINE=MyISAM DEFAULT CHARSET=latin1 1303drop table t1; 1304set @@sql_mode='traditional'; 1305create table t1(a bit(2)); 1306insert into t1 values(b'101'); 1307ERROR 22001: Data too long for column 'a' at row 1 1308select * from t1; 1309a 1310drop table t1; 1311set sql_mode='traditional'; 1312create table t1 (date date not null); 1313create table t2 select date from t1; 1314show create table t2; 1315Table Create Table 1316t2 CREATE TABLE `t2` ( 1317 `date` date NOT NULL 1318) ENGINE=MyISAM DEFAULT CHARSET=latin1 1319drop table t2,t1; 1320set @@sql_mode= @org_mode; 1321set @@sql_mode='traditional'; 1322create table t1 (i int) 1323comment '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* 1345 123456789*123456789*123456789*123456789*123456789* 1346 123456789*123456789*123456789*123456789*123456789* 1347 123456789*123456789*123456789*123456789*123456789* 1348 123456789*123456789*123456789*123456789*123456789* 1349 123456789*123456789*123456789*123456789*123456789* 1350 123456789*123456789*123456789*123456789*123456789* 1351 123456789*123456789*123456789*123456789*123456789* 1352 123456789*123456789*123456789*123456789*123456789* 1353 123456789*123456789*123456789*123456789*123456789* 1354 123456789*123456789*123456789*123456789*123456789* 1355 123456789*123456789*123456789*123456789*123456789* 1356 123456789*123456789*123456789*123456789*123456789* 1357 123456789*123456789*123456789*123456789*123456789* 1358 123456789*123456789*123456789*123456789*123456789*'; 1359ERROR HY000: Comment for table 't1' is too long (max = 2048) 1360create table t1 ( 1361i int comment 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* 1375 123456789*123456789*123456789*123456789* 1376 123456789*123456789*123456789*123456789* 1377 123456789*123456789*123456789*123456789* 1378 123456789*123456789*123456789*123456789* 1379 123456789*123456789*123456789*123456789* 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*'); 1389ERROR HY000: Comment for field 'i' is too long (max = 1024) 1390set @@sql_mode= @org_mode; 1391create table t1 1392(i int comment 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* 1407 123456789*123456789*123456789*123456789* 1408 123456789*123456789*123456789*123456789* 1409 123456789*123456789*123456789*123456789* 1410 123456789*123456789*123456789*123456789* 1411 123456789*123456789*123456789*123456789* 1412 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*'); 1420Warnings: 1421Warning 1629 Comment for field 'i' is too long (max = 1024) 1422select column_name, column_comment from information_schema.columns where 1423table_schema = 'test' and table_name = 't1'; 1424column_name column_comment 1425i 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*123456789* 1436 123456789*123456789*123456789*123456789* 1437 123456789*123456789*123456789*123456789* 1438 123456789*123456789*123456789*123456789* 1439 123456789*123456789*123456789*123456789* 1440 123456789*123456789*123456789*123456789* 1441 123456789*123456789*123456789*123456789* 1442 123456789*123456789*123456789*123456789* 1443 123456789*123456789*123456789*123456789* 1444 123456789*123456789*123456789*123456789* 1445 123456789*123456789*123456789*123456789* 1446 123456789*123456789*123456789*123456789* 1447 123456789*123456789*123456789*123456789* 1448 123456789*123456789*123456789*12345 1449drop table t1; 1450set names utf8; 1451create table t1 (i int) 1452comment '123456789*123456789*123456789*123456789*123456789*123456789*'; 1453show create table t1; 1454Table Create Table 1455t1 CREATE TABLE `t1` ( 1456 `i` int(11) DEFAULT NULL 1457) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*' 1458drop table t1; 1459CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'; 1460SHOW CREATE TABLE t3; 1461Table Create Table 1462t3 CREATE TABLE `t3` ( 1463 `f1` int(11) DEFAULT NULL 1464) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן' 1465DROP TABLE t3; 1466set sql_mode= 'traditional'; 1467create table t1(col1 tinyint, col2 tinyint unsigned, 1468col3 smallint, col4 smallint unsigned, 1469col5 mediumint, col6 mediumint unsigned, 1470col7 int, col8 int unsigned, 1471col9 bigint, col10 bigint unsigned); 1472insert into t1(col1) values('-'); 1473ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1 1474insert into t1(col2) values('+'); 1475ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1 1476insert into t1(col3) values('-'); 1477ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1 1478insert into t1(col4) values('+'); 1479ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1 1480insert into t1(col5) values('-'); 1481ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1 1482insert into t1(col6) values('+'); 1483ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1 1484insert into t1(col7) values('-'); 1485ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1 1486insert into t1(col8) values('+'); 1487ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1 1488insert into t1(col9) values('-'); 1489ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1 1490insert into t1(col10) values('+'); 1491ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1 1492drop table t1; 1493set sql_mode='traditional'; 1494create table t1(a year); 1495insert into t1 values ('-'); 1496ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1 1497insert into t1 values ('+'); 1498ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1 1499insert into t1 values (''); 1500ERROR HY000: Incorrect integer value: '' for column 'a' at row 1 1501insert into t1 values ('2000a'); 1502ERROR 01000: Data truncated for column 'a' at row 1 1503insert into t1 values ('2E3x'); 1504ERROR 01000: Data truncated for column 'a' at row 1 1505drop table t1; 1506set sql_mode='traditional'; 1507create table t1 (f1 set('a','a')); 1508ERROR HY000: Column 'f1' has duplicated value 'a' in SET 1509create table t1 (f1 enum('a','a')); 1510ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM 1511set @@sql_mode='NO_ZERO_DATE'; 1512Warnings: 1513Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 1514create table t1(a datetime not null); 1515select count(*) from t1 where a is null; 1516count(*) 15170 1518drop table t1; 1519End of 5.0 tests 1520# 1521# Start of 5.6 tests 1522# 1523# 1524# WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME 1525# 1526# 1527# STR_TO_DATE with NO_ZERO_DATE did not return NULL (with warning) 1528# in get_date(). Only did in val_str() and val_int(). 1529SET sql_mode='NO_ZERO_DATE'; 1530Warnings: 1531Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 1532SELECT 1533STR_TO_DATE('2001','%Y'), 1534CONCAT(STR_TO_DATE('2001','%Y')), 1535STR_TO_DATE('2001','%Y')+1; 1536STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 1537NULL NULL NULL 1538Warnings: 1539Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1540Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1541Warning 1411 Incorrect datetime value: '2001' for function str_to_date 1542# 1543# End of 5.6 tests 1544# 1545