strict.result 57.5 KB
Newer Older
1
set @org_mode=@@sql_mode;
2 3 4
set @@sql_mode='ansi,traditional';
select @@sql_mode;
@@sql_mode
5
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
6
DROP TABLE IF EXISTS t1, t2;
7
CREATE TABLE t1 (col1 date);
8 9
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
INSERT INTO t1 VALUES('0000-10-31');
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
INSERT INTO t1 VALUES('2004-0-31');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
INSERT INTO t1 VALUES('2004-10-0');
ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31');
ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32');
ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1
INSERT INTO t1 VALUES('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-13-15');
ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES';
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
31 32
INSERT INTO t1 VALUES('2004-0-30');
ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1
33 34 35
INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
INSERT INTO t1 VALUES('0000-00-00');
36 37 38
INSERT IGNORE INTO t1 VALUES('2004-0-29');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
39 40 41
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
INSERT INTO t1 VALUES('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
42 43 44
INSERT IGNORE INTO t1 VALUES('0000-00-00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
45 46 47 48 49 50 51 52 53 54 55 56 57 58
INSERT INTO t1 VALUES ('2004-0-30');
INSERT INTO t1 VALUES ('2004-2-30');
ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT INTO t1 VALUES ('2004-2-30');
set @@sql_mode='ansi,traditional';
INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
Warning	1265	Data truncated for column 'col1' at row 3
select * from t1;
col1
2004-01-01
2004-02-29
59
0000-10-31
60 61 62 63 64
2004-01-02
2004-01-03
2004-00-31
2004-01-04
0000-00-00
65 66
0000-00-00
0000-00-00
67 68 69 70 71 72
2004-00-30
2004-02-30
2004-02-29
0000-00-00
0000-00-00
drop table t1;
73
set @@sql_mode='strict_trans_tables';
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
CREATE TABLE t1 (col1 date) engine=myisam;
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT ignore INTO t1 VALUES('2003-02-31');
select * from t1;
col1
2004-01-02
0000-00-00
2004-01-03
0000-00-00
2004-01-04
0000-00-00
2003-02-31
drop table t1;
100
set @@sql_mode='strict_trans_tables';
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
CREATE TABLE t1 (col1 date) engine=innodb;
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('2003-02-29');
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
INSERT ignore INTO t1 VALUES('2003-02-30');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT ignore INTO t1 VALUES('2003-02-31');
select * from t1;
col1
0000-00-00
2004-01-04
0000-00-00
2003-02-31
drop table t1;
set @@sql_mode='ansi,traditional';
CREATE TABLE t1 (col1 datetime);
125 126
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
127 128 129 130 131 132 133 134
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
135 136
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
137 138 139 140 141 142 143 144 145 146
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
select * from t1;
col1
2004-10-31 15:30:00
2004-02-29 15:30:00
147
0000-10-31 15:30:00
148 149 150 151 152 153 154 155 156 157 158 159 160
drop table t1;
CREATE TABLE t1 (col1 timestamp);
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
161 162
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 25:30:00');
ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 15:65:00');
ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-29 15:31:61');
ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('59');
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
INSERT INTO t1 VALUES('2004-02-30 15:30:04');
ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
set @@sql_mode='ansi,traditional';
SELECT * FROM t1;
col1
2004-10-31 15:30:00
2004-02-29 15:30:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
DROP TABLE t1;
204 205 206 207 208
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
209 210
Warnings:
Note	1265	Data truncated for column 'col1' at row 1
211 212 213 214 215 216
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1
217
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
218
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
219 220
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1
221
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
222
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
223 224 225 226 227 228 229 230 231
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1
232
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
233
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
234 235
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1
236
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
237
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
238 239
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1
240 241 242 243 244 245 246 247
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1
248
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
249
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
250 251
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1
252
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
253
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
254 255 256 257 258 259 260 261 262 263 264 265
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1
drop table t1;
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1
266
INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
267
ERROR 22007: Incorrect datetime value: '0000-00-00'
268 269 270 271 272
INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1
273
INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));
274
ERROR 22007: Incorrect datetime value: '0000-00-00'
275
INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
276
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
277 278 279 280
INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1
281
INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));
282
ERROR 22007: Incorrect datetime value: '0000-00-00'
283 284 285 286 287 288 289 290 291 292
drop table t1;
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));
INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1
293
INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
294
ERROR 22007: Incorrect datetime value: '0000-00-00'
295 296 297 298 299
INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1
300
INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));
301
ERROR 22007: Incorrect datetime value: '0000-00-00'
302
INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
303
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
304 305 306 307
INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1
308
INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));
309
ERROR 22007: Incorrect datetime value: '0000-00-00'
310
drop table t1;
311 312
CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);
INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0);
313 314 315 316 317 318 319
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
MOD(col1,0)
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
320
INSERT INTO t1 (col1) VALUES(-129);
321
ERROR 22003: Out of range value for column 'col1' at row 1
322
INSERT INTO t1 (col1) VALUES(128);
323
ERROR 22003: Out of range value for column 'col1' at row 1
324
INSERT INTO t1 (col2) VALUES(-1);
325
ERROR 22003: Out of range value for column 'col2' at row 1
326
INSERT INTO t1 (col2) VALUES(256);
327
ERROR 22003: Out of range value for column 'col2' at row 1
328
INSERT INTO t1 (col1) VALUES('-129');
329
ERROR 22003: Out of range value for column 'col1' at row 1
330
INSERT INTO t1 (col1) VALUES('128');
331
ERROR 22003: Out of range value for column 'col1' at row 1
332
INSERT INTO t1 (col2) VALUES('-1');
333
ERROR 22003: Out of range value for column 'col2' at row 1
334
INSERT INTO t1 (col2) VALUES('256');
335
ERROR 22003: Out of range value for column 'col2' at row 1
336
INSERT INTO t1 (col1) VALUES(128.0);
337
ERROR 22003: Out of range value for column 'col1' at row 1
338
INSERT INTO t1 (col2) VALUES(-1.0);
339
ERROR 22003: Out of range value for column 'col2' at row 1
340
INSERT INTO t1 (col2) VALUES(256.0);
341
ERROR 22003: Out of range value for column 'col2' at row 1
342 343 344 345 346
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;
MOD(col1,0)
NULL
Warnings:
Error	1365	Division by 0
347
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
348
ERROR 22003: Out of range value for column 'col1' at row 1
349
UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;
350
ERROR 22003: Out of range value for column 'col2' at row 3
351 352
UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
353 354 355 356 357 358 359 360 361 362 363 364 365
set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
INSERT INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
set @@sql_mode='ansi,traditional';
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
MOD(col1,0)
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
366 367 368 369 370 371 372 373 374 375 376 377 378
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
379 380 381
set @@sql_mode='ansi';
INSERT INTO t1 values (1/0,1/0);
set @@sql_mode='ansi,traditional';
382 383
INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');
Warnings:
384 385 386 387
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
388 389
INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);
Warnings:
390 391 392 393
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
394 395 396 397 398 399 400 401 402 403 404 405
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-128	0
0	NULL
127	255
-128	0
0	NULL
127	255
-128	0
0	NULL
127	255
406
NULL	NULL
407 408
2	NULL
NULL	NULL
409
NULL	NULL
410 411 412 413 414 415 416 417
-128	0
127	255
-128	0
127	255
DROP TABLE t1;
CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);
INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);
INSERT INTO t1 (col1) VALUES(-32769);
418
ERROR 22003: Out of range value for column 'col1' at row 1
419
INSERT INTO t1 (col1) VALUES(32768);
420
ERROR 22003: Out of range value for column 'col1' at row 1
421
INSERT INTO t1 (col2) VALUES(-1);
422
ERROR 22003: Out of range value for column 'col2' at row 1
423
INSERT INTO t1 (col2) VALUES(65536);
424
ERROR 22003: Out of range value for column 'col2' at row 1
425
INSERT INTO t1 (col1) VALUES('-32769');
426
ERROR 22003: Out of range value for column 'col1' at row 1
427
INSERT INTO t1 (col1) VALUES('32768');
428
ERROR 22003: Out of range value for column 'col1' at row 1
429
INSERT INTO t1 (col2) VALUES('-1');
430
ERROR 22003: Out of range value for column 'col2' at row 1
431
INSERT INTO t1 (col2) VALUES('65536');
432
ERROR 22003: Out of range value for column 'col2' at row 1
433
INSERT INTO t1 (col1) VALUES(-32769.0);
434
ERROR 22003: Out of range value for column 'col1' at row 1
435
INSERT INTO t1 (col1) VALUES(32768.0);
436
ERROR 22003: Out of range value for column 'col1' at row 1
437
INSERT INTO t1 (col2) VALUES(-1.0);
438
ERROR 22003: Out of range value for column 'col2' at row 1
439
INSERT INTO t1 (col2) VALUES(65536.0);
440
ERROR 22003: Out of range value for column 'col2' at row 1
441
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
442
ERROR 22003: Out of range value for column 'col1' at row 1
443
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
444
ERROR 22003: Out of range value for column 'col2' at row 3
445 446
UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
447
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
Warnings:
464 465 466 467
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
468 469
INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');
Warnings:
470 471 472 473
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
474 475
INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);
Warnings:
476 477 478 479
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-32768	0
0	NULL
32767	65535
-32768	0
32767	65535
-32768	0
32767	65535
2	NULL
NULL	NULL
-32768	0
32767	65535
-32768	0
32767	65535
-32768	0
32767	65535
DROP TABLE t1;
CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);
INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);
INSERT INTO t1 (col1) VALUES(-8388609);
502
ERROR 22003: Out of range value for column 'col1' at row 1
503
INSERT INTO t1 (col1) VALUES(8388608);
504
ERROR 22003: Out of range value for column 'col1' at row 1
505
INSERT INTO t1 (col2) VALUES(-1);
506
ERROR 22003: Out of range value for column 'col2' at row 1
507
INSERT INTO t1 (col2) VALUES(16777216);
508
ERROR 22003: Out of range value for column 'col2' at row 1
509
INSERT INTO t1 (col1) VALUES('-8388609');
510
ERROR 22003: Out of range value for column 'col1' at row 1
511
INSERT INTO t1 (col1) VALUES('8388608');
512
ERROR 22003: Out of range value for column 'col1' at row 1
513
INSERT INTO t1 (col2) VALUES('-1');
514
ERROR 22003: Out of range value for column 'col2' at row 1
515
INSERT INTO t1 (col2) VALUES('16777216');
516
ERROR 22003: Out of range value for column 'col2' at row 1
517
INSERT INTO t1 (col1) VALUES(-8388609.0);
518
ERROR 22003: Out of range value for column 'col1' at row 1
519
INSERT INTO t1 (col1) VALUES(8388608.0);
520
ERROR 22003: Out of range value for column 'col1' at row 1
521
INSERT INTO t1 (col2) VALUES(-1.0);
522
ERROR 22003: Out of range value for column 'col2' at row 1
523
INSERT INTO t1 (col2) VALUES(16777216.0);
524
ERROR 22003: Out of range value for column 'col2' at row 1
525
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
526
ERROR 22003: Out of range value for column 'col1' at row 1
527
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
528
ERROR 22003: Out of range value for column 'col2' at row 3
529 530
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
531
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
Warnings:
548 549 550 551
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
552 553
INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');
Warnings:
554 555 556 557
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
558 559
INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);
Warnings:
560 561 562 563
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-8388608	0
0	NULL
8388607	16777215
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
2	NULL
NULL	NULL
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
-8388608	0
8388607	16777215
DROP TABLE t1;
CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);
INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);
INSERT INTO t1 (col1) VALUES(-2147483649);
586
ERROR 22003: Out of range value for column 'col1' at row 1
587
INSERT INTO t1 (col1) VALUES(2147643648);
588
ERROR 22003: Out of range value for column 'col1' at row 1
589
INSERT INTO t1 (col2) VALUES(-1);
590
ERROR 22003: Out of range value for column 'col2' at row 1
591
INSERT INTO t1 (col2) VALUES(4294967296);
592
ERROR 22003: Out of range value for column 'col2' at row 1
593
INSERT INTO t1 (col1) VALUES('-2147483649');
594
ERROR 22003: Out of range value for column 'col1' at row 1
595
INSERT INTO t1 (col1) VALUES('2147643648');
596
ERROR 22003: Out of range value for column 'col1' at row 1
597
INSERT INTO t1 (col2) VALUES('-1');
598
ERROR 22003: Out of range value for column 'col2' at row 1
599
INSERT INTO t1 (col2) VALUES('4294967296');
600
ERROR 22003: Out of range value for column 'col2' at row 1
601
INSERT INTO t1 (col1) VALUES(-2147483649.0);
602
ERROR 22003: Out of range value for column 'col1' at row 1
603
INSERT INTO t1 (col1) VALUES(2147643648.0);
604
ERROR 22003: Out of range value for column 'col1' at row 1
605
INSERT INTO t1 (col2) VALUES(-1.0);
606
ERROR 22003: Out of range value for column 'col2' at row 1
607
INSERT INTO t1 (col2) VALUES(4294967296.0);
608
ERROR 22003: Out of range value for column 'col2' at row 1
609
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
610
ERROR 22003: Out of range value for column 'col1' at row 1
611
UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;
612
ERROR 22003: Out of range value for column 'col2' at row 3
613 614
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
615
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
616 617
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
618
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
619
INSERT INTO t1 (col1) VALUES ('a59b');
620
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
621 622 623 624 625 626 627 628 629 630 631
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
Warnings:
632 633 634 635
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
636 637
INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');
Warnings:
638 639 640 641
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
642 643
INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);
Warnings:
644 645 646 647
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-2147483648	0
0	NULL
2147483647	4294967295
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
2	NULL
NULL	NULL
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
-2147483648	0
2147483647	4294967295
DROP TABLE t1;
CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);
INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);
INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');
INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);
INSERT INTO t1 (col1) VALUES(-9223372036854775809);
672
ERROR 22003: Out of range value for column 'col1' at row 1
673
INSERT INTO t1 (col1) VALUES(9223372036854775808);
674
ERROR 22003: Out of range value for column 'col1' at row 1
675
INSERT INTO t1 (col2) VALUES(-1);
676
ERROR 22003: Out of range value for column 'col2' at row 1
677
INSERT INTO t1 (col2) VALUES(18446744073709551616);
678
ERROR 22003: Out of range value for column 'col2' at row 1
679
INSERT INTO t1 (col1) VALUES('-9223372036854775809');
680
ERROR 22003: Out of range value for column 'col1' at row 1
681
INSERT INTO t1 (col1) VALUES('9223372036854775808');
682
ERROR 22003: Out of range value for column 'col1' at row 1
683
INSERT INTO t1 (col2) VALUES('-1');
684
ERROR 22003: Out of range value for column 'col2' at row 1
685
INSERT INTO t1 (col2) VALUES('18446744073709551616');
686
ERROR 22003: Out of range value for column 'col2' at row 1
687
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);
688
ERROR 22003: Out of range value for column 'col1' at row 1
689
INSERT INTO t1 (col1) VALUES(9223372036854785808.0);
690
ERROR 22003: Out of range value for column 'col1' at row 1
691
INSERT INTO t1 (col2) VALUES(-1.0);
692
ERROR 22003: Out of range value for column 'col2' at row 1
693
INSERT INTO t1 (col2) VALUES(18446744073709551616.0);
694
ERROR 22003: Out of range value for column 'col2' at row 1
695 696
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
697
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
698 699
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
700
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
701
INSERT INTO t1 (col1) VALUES ('a59b');
702
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
703 704 705 706 707 708 709 710 711 712 713
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 values (1/0,1/0);
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
Warnings:
714 715 716 717
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
718 719
INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');
Warnings:
720 721 722 723
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
724
INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);
725
Warnings:
726 727 728 729
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
Warning	1264	Out of range value for column 'col2' at row 2
730 731 732 733 734 735 736 737
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
SELECT * FROM t1;
col1	col2
-9223372036854775808	0
0	NULL
9223372036854775807	18446744073709551615
-9223372036854775808	0
9223372036854775807	18446744073709551615
738 739
-9223372036854774000	0
9223372036854775700	1844674407370954000
740 741
2	NULL
NULL	NULL
742 743
-9223372036854775808	0
9223372036854775807	18446744073709551615
744 745 746 747 748 749 750
-9223372036854775808	0
9223372036854775807	18446744073709551615
-9223372036854775808	0
9223372036854775807	18446744073709551615
DROP TABLE t1;
CREATE TABLE t1 (col1 NUMERIC(4,2));
INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
751 752 753
Warnings:
Note	1265	Data truncated for column 'col1' at row 2
Note	1265	Data truncated for column 'col1' at row 5
754 755 756 757 758
INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');
Warnings:
Note	1265	Data truncated for column 'col1' at row 2
Note	1265	Data truncated for column 'col1' at row 4
INSERT INTO t1 VALUES (101.55);
759
ERROR 22003: Out of range value for column 'col1' at row 1
760
INSERT INTO t1 VALUES (101);
761
ERROR 22003: Out of range value for column 'col1' at row 1
762
INSERT INTO t1 VALUES (-101.55);
763
ERROR 22003: Out of range value for column 'col1' at row 1
764
INSERT INTO t1 VALUES (1010.55);
765
ERROR 22003: Out of range value for column 'col1' at row 1
766
INSERT INTO t1 VALUES (1010);
767
ERROR 22003: Out of range value for column 'col1' at row 1
768
INSERT INTO t1 VALUES ('101.55');
769
ERROR 22003: Out of range value for column 'col1' at row 1
770
INSERT INTO t1 VALUES ('101');
771
ERROR 22003: Out of range value for column 'col1' at row 1
772
INSERT INTO t1 VALUES ('-101.55');
773
ERROR 22003: Out of range value for column 'col1' at row 1
774
INSERT INTO t1 VALUES ('-1010.55');
775
ERROR 22003: Out of range value for column 'col1' at row 1
776
INSERT INTO t1 VALUES ('-100E+1');
777
ERROR 22003: Out of range value for column 'col1' at row 1
778
INSERT INTO t1 VALUES ('-100E');
779
ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1
780
UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;
781
ERROR 22003: Out of range value for column 'col1' at row 6
782 783
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
784
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
785 786
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
787
ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1
788
INSERT INTO t1 (col1) VALUES ('a59b');
789
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
790
INSERT INTO t1 (col1) VALUES ('1a');
791
ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1
792 793
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
794
Note	1265	Data truncated for column 'col1' at row 1
795 796 797 798 799
INSERT IGNORE INTO t1 values (1/0);
Warnings:
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES(1000),(-1000);
Warnings:
800 801
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
802 803
INSERT IGNORE INTO t1 VALUES('1000'),('-1000');
Warnings:
804 805
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
806 807
INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);
Warnings:
808 809
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
810 811 812 813 814 815 816 817 818 819 820
UPDATE IGNORE t1 SET col1=1/NULL where col1=0;
SELECT * FROM t1;
col1
10.55
10.56
NULL
-10.55
-10.56
11.00
10.00
10.55
821
10.56
822
-10.55
823
-10.56
824 825 826 827
11.00
10.00
2.00
NULL
828
99.99
829
-99.99
830
99.99
831
-99.99
832
99.99
833 834
-99.99
DROP TABLE t1;
835
CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);
836 837
INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);
INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');
838 839
INSERT INTO t1 (col1) VALUES (3E-46);
INSERT INTO t1 (col1) VALUES (+3.4E+39);
840
ERROR 22003: Out of range value for column 'col1' at row 1
841
INSERT INTO t1 (col2) VALUES (-1.1E-3);
842
ERROR 22003: Out of range value for column 'col2' at row 1
843
INSERT INTO t1 (col1) VALUES ('+3.4E+39');
844
ERROR 22003: Out of range value for column 'col1' at row 1
845
INSERT INTO t1 (col2) VALUES ('-1.1E-3');
846
ERROR 22003: Out of range value for column 'col2' at row 1
847
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
848
ERROR 22003: Out of range value for column 'col1' at row 2
849 850
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
ERROR 22012: Division by 0
851
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
852 853 854 855 856 857 858 859 860 861 862 863 864 865 866
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES (1/0);
Warnings:
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
Warnings:
867 868
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
869 870
INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');
Warnings:
871 872
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
873 874
SELECT * FROM t1;
col1	col2
875
-1.1e-37	0
876
3.4e+38	3.4e+38
877
-1.1e-37	0
878 879 880 881 882 883 884 885
3.4e+38	3.4e+38
0	NULL
2	NULL
NULL	NULL
3.40282e+38	0
3.40282e+38	0
DROP TABLE t1;
CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);
monty@mysql.com's avatar
monty@mysql.com committed
886 887
INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);
INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');
888 889
INSERT INTO t1 (col1) VALUES (-2.2E-330);
INSERT INTO t1 (col1) VALUES (+1.7E+309);
890
Got one of the listed errors
891
INSERT INTO t1 (col2) VALUES (-1.1E-3);
892
ERROR 22003: Out of range value for column 'col2' at row 1
893
INSERT INTO t1 (col1) VALUES ('+1.8E+309');
894
ERROR 22003: Out of range value for column 'col1' at row 1
895
INSERT INTO t1 (col2) VALUES ('-1.2E-3');
896
ERROR 22003: Out of range value for column 'col2' at row 1
897
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
898
ERROR 22003: Out of range value for column 'col1' at row 3
899 900
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
ERROR 22012: Division by 0
901
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918
ERROR 22012: Division by 0
INSERT INTO t1 (col1) VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('a59b');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('1a');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
INSERT IGNORE INTO t1 (col1) values (1/0);
Warnings:
Error	1365	Division by 0
INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);
ERROR 22007: Illegal double '1.9E+309' value found during parsing
INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');
Warnings:
919 920 921
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
922 923
SELECT * FROM t1;
col1	col2
924
-2.2e-307	0
monty@mysql.com's avatar
monty@mysql.com committed
925
1e-303	0
926
1.7e+308	1.7e+308
927
-2.2e-307	0
monty@mysql.com's avatar
monty@mysql.com committed
928
-2e-307	0
929
1.7e+308	1.7e+308
930
0	NULL
931 932 933 934
2	NULL
NULL	NULL
1.79769313486232e+308	0
DROP TABLE t1;
935 936
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello ');
937
INSERT INTO t1 (col1) VALUES ('hellobob');
938
ERROR 22001: Data too long for column 'col1' at row 1
939
INSERT INTO t1 (col2) VALUES ('hellobob');
940
ERROR 22001: Data too long for column 'col2' at row 1
941
INSERT INTO t1 (col2) VALUES ('hello  ');
bar@mysql.com's avatar
bar@mysql.com committed
942 943
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
944
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
945
ERROR 22001: Data too long for column 'col1' at row 2
946
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
947
ERROR 22001: Data too long for column 'col2' at row 2
948 949 950 951 952 953 954 955 956 957
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
Warning	1265	Data truncated for column 'col2' at row 1
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
Warnings:
Warning	1265	Data truncated for column 'col2' at row 2
SELECT * FROM t1;
col1	col2
hello	hello
958 959
he	hellot
hello	hello 
960
NULL	hello 
961
hello	hellob
962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995
DROP TABLE t1;
CREATE TABLE t1 (col1 enum('red','blue','green'));
INSERT INTO t1 VALUES ('red'),('blue'),('green');
INSERT INTO t1 (col1) VALUES ('yellow');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 (col1) VALUES ('redd');
ERROR 01000: Data truncated for column 'col1' at row 1
INSERT INTO t1 VALUES ('');
ERROR 01000: Data truncated for column 'col1' at row 1
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
ERROR 01000: Data truncated for column 'col1' at row 3
INSERT IGNORE INTO t1 VALUES ('yellow');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
Warnings:
Warning	1265	Data truncated for column 'col1' at row 2
SELECT * FROM t1;
col1
red

green

DROP TABLE t1;
CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);
INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');
INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');
INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
ERROR 23000: Column 'col1' cannot be null
INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
ERROR 23000: Column 'col2' cannot be null
INSERT INTO t1 VALUES (103,'',NULL);
ERROR 23000: Column 'col3' cannot be null
UPDATE t1 SET col1=NULL WHERE col1 =100;
996
ERROR 23000: Column 'col1' cannot be null
997
UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
998
ERROR 23000: Column 'col2' cannot be null
999
UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;
1000
ERROR 23000: Column 'col2' cannot be null
1001 1002
INSERT IGNORE INTO t1 values (NULL,NULL,NULL);
Warnings:
1003 1004 1005
Warning	1048	Column 'col1' cannot be null
Warning	1048	Column 'col2' cannot be null
Warning	1048	Column 'col3' cannot be null
1006 1007 1008 1009 1010 1011 1012 1013 1014 1015
SELECT * FROM t1;
col1	col2	col3
100	hello	2004-08-20
101	hell2	2004-08-21
0		0000-00-00
DROP TABLE t1;
CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE "t1" (
1016
  "col1" int(11) NOT NULL DEFAULT '99',
1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029
  "col2" char(6) NOT NULL
)
INSERT INTO t1 VALUES (1, 'hello');
INSERT INTO t1 (col2) VALUES ('hello2');
INSERT INTO t1 (col2) VALUES (NULL);
ERROR 23000: Column 'col2' cannot be null
INSERT INTO t1 (col1) VALUES (2);
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 VALUES(default(col1),default(col2));
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 (col1) SELECT 1;
ERROR HY000: Field 'col2' doesn't have a default value
INSERT INTO t1 SELECT 1,NULL;
1030
ERROR 23000: Column 'col2' cannot be null
1031 1032
INSERT IGNORE INTO t1 values (NULL,NULL);
Warnings:
1033 1034
Warning	1048	Column 'col1' cannot be null
Warning	1048	Column 'col2' cannot be null
1035
INSERT IGNORE INTO t1 (col1) values (3);
1036 1037
Warnings:
Warning	1364	Field 'col2' doesn't have a default value
1038
INSERT IGNORE INTO t1 () values ();
1039 1040
Warnings:
Warning	1364	Field 'col2' doesn't have a default value
1041 1042 1043 1044 1045 1046 1047 1048
SELECT * FROM t1;
col1	col2
1	hello
99	hello2
0	
3	
99	
DROP TABLE t1;
1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067
set sql_mode='traditional';
create table t1 (charcol char(255), varcharcol varchar(255),
binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
tinyblobcol tinyblob);
insert into t1 (charcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'charcol' at row 1
insert into t1 (varcharcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'varcharcol' at row 1
insert into t1 (binarycol) values (repeat('x',256));
ERROR 22001: Data too long for column 'binarycol' at row 1
insert into t1 (varbinarycol) values (repeat('x',256));
ERROR 22001: Data too long for column 'varbinarycol' at row 1
insert into t1 (tinytextcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'tinytextcol' at row 1
insert into t1 (tinyblobcol) values (repeat('x',256));
ERROR 22001: Data too long for column 'tinyblobcol' at row 1
select * from t1;
charcol	varcharcol	binarycol	varbinarycol	tinytextcol	tinyblobcol
drop table t1;
1068 1069 1070 1071 1072
set sql_mode='traditional';
create table t1 (col1 datetime);
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1073
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
1074
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1075
ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date
1076
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1077
ERROR HY000: Incorrect time value: 'abc' for function str_to_date
1078 1079 1080 1081 1082 1083
set sql_mode='';
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
Warnings:
Warning	1292	Truncated incorrect datetime value: '31.10.2004 15.30 abc'
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
Warnings:
1084
Error	1411	Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
1085 1086
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
Warnings:
1087
Error	1411	Incorrect time value: '22:22:33 AM' for function str_to_date
1088 1089
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
Warnings:
1090
Error	1411	Incorrect time value: 'abc' for function str_to_date
1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107
insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
select * from t1;
col1
2004-10-31 15:30:00
NULL
NULL
NULL
2004-10-31 15:30:00
2004-12-12 11:22:33
2004-12-12 10:22:59
set sql_mode='traditional';
select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
count(*)
7
Warnings:
1108 1109 1110
Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1111
drop table t1;
1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138
create table t1 (col1 char(3), col2 integer);
insert into t1 (col1) values (cast(1000 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
insert into t1 (col1) values (cast(1000E+0 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
insert into t1 (col1) values (cast(1000.0 as char(3)));
ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
insert into t1 (col2) values (cast('abc' as signed integer));
ERROR 22007: Truncated incorrect INTEGER value: 'abc'
insert into t1 (col2) values (10E+0 + 'a');
ERROR 22007: Truncated incorrect DOUBLE value: 'a'
insert into t1 (col2) values (cast('10a' as unsigned integer));
ERROR 22007: Truncated incorrect INTEGER value: '10a'
insert into t1 (col2) values (cast('10' as unsigned integer));
insert into t1 (col2) values (cast('10' as signed integer));
insert into t1 (col2) values (10E+0 + '0 ');
select * from t1;
col1	col2
NULL	10
NULL	10
NULL	10
drop table t1;
create table t1 (col1 date, col2 datetime, col3 timestamp);
insert into t1 values (0,0,0);
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
insert into t1 values (0.0,0.0,0.0);
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1139
insert into t1 (col1) values (convert('0000-00-00',date));
1140
ERROR 22007: Incorrect datetime value: '0000-00-00'
1141
insert into t1 (col1) values (cast('0000-00-00' as date));
1142
ERROR 22007: Incorrect datetime value: '0000-00-00'
1143 1144 1145
set sql_mode='no_zero_date';
insert into t1 values (0,0,0);
Warnings:
1146 1147
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
1148 1149 1150
Warning	1265	Data truncated for column 'col3' at row 1
insert into t1 values (0.0,0.0,0.0);
Warnings:
1151 1152
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col2' at row 1
1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164
Warning	1265	Data truncated for column 'col3' at row 1
drop table t1;
set sql_mode='traditional';
create table t1 (col1 date);
insert ignore into t1 values ('0000-00-00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
insert into t1 select * from t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
insert ignore into t1 values ('0000-00-00');
Warnings:
Warning	1265	Data truncated for column 'col1' at row 1
1165 1166
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
Warnings:
1167
Warning	1292	Incorrect datetime value: '0000-00-00'
1168 1169 1170 1171 1172 1173
insert into t1 select * from t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
alter table t1 modify col1 datetime;
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
alter ignore table t1 modify col1 datetime;
Warnings:
1174 1175
Warning	1264	Out of range value for column 'col1' at row 1
Warning	1264	Out of range value for column 'col1' at row 2
1176 1177 1178 1179 1180 1181
insert into t1 select * from t1;
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
select * from t1;
col1
0000-00-00 00:00:00
0000-00-00 00:00:00
1182
NULL
1183
drop table t1;
1184 1185 1186 1187 1188 1189 1190
create table t1 (col1 tinyint);
drop procedure if exists t1;
Warnings:
Note	1305	PROCEDURE t1 does not exist
create procedure t1 () begin declare exit handler for sqlexception
select'a'; insert into t1 values (200); end;|
call t1();
1191 1192
a
a
1193 1194 1195 1196
select * from t1;
col1
drop procedure t1;
drop table t1;
1197
set sql_mode=@org_mode;
1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238
SET @@sql_mode = 'traditional';
CREATE TABLE t1 (i int not null);
INSERT INTO t1 VALUES ();
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT(i));
ERROR HY000: Field 'i' doesn't have a default value
ALTER TABLE t1 ADD j int;
INSERT INTO t1 SET j = 1;
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT;
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT,1);
ERROR HY000: Field 'i' doesn't have a default value
DROP TABLE t1;
SET @@sql_mode = '';
CREATE TABLE t1 (i int not null);
INSERT INTO t1 VALUES ();
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT);
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT(i));
ERROR HY000: Field 'i' doesn't have a default value
ALTER TABLE t1 ADD j int;
INSERT INTO t1 SET j = 1;
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT;
Warnings:
Warning	1364	Field 'i' doesn't have a default value
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
ERROR HY000: Field 'i' doesn't have a default value
INSERT INTO t1 VALUES (DEFAULT,1);
Warnings:
Warning	1364	Field 'i' doesn't have a default value
DROP TABLE t1;
1239 1240 1241 1242 1243
set @@sql_mode='traditional';
create table t1(a varchar(65537));
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
create table t1(a varbinary(65537));
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1244 1245 1246 1247 1248 1249 1250 1251 1252
set @@sql_mode='traditional';
create table t1(a int, b date not null);
alter table t1 modify a bigint unsigned not null;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(20) unsigned NOT NULL,
  `b` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1253 1254
drop table t1;
set @@sql_mode='traditional';
1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265
create table t1 (d date);
insert into t1 values ('2000-10-00');
ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
insert into t1 values (1000);
ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
insert into t1 values ('2000-10-01');
update t1 set d = 1100;
ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
select * from t1;
d
2000-10-01
1266
drop table t1;
1267
set @@sql_mode='traditional';
1268 1269 1270 1271 1272
create table t1(a int, b timestamp);
alter table t1 add primary key(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1273 1274
  `a` int(11) NOT NULL DEFAULT '0',
  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1275
  PRIMARY KEY (`a`)
1276 1277 1278 1279 1280 1281 1282
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1(a int, b timestamp default 20050102030405);
alter table t1 add primary key(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
1283 1284
  `a` int(11) NOT NULL DEFAULT '0',
  `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
1285
  PRIMARY KEY (`a`)
1286 1287
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
ramil@mysql.com's avatar
ramil@mysql.com committed
1288
set @@sql_mode='traditional';
1289 1290 1291 1292 1293 1294
create table t1(a bit(2));
insert into t1 values(b'101');
ERROR 22001: Data too long for column 'a' at row 1
select * from t1;
a
drop table t1;
1295 1296 1297 1298 1299 1300 1301 1302 1303 1304
set sql_mode='traditional';
create table t1 (date date not null);
create table t2 select date from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t2,t1;
set @@sql_mode= @org_mode;
1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341
set @@sql_mode='traditional';
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*';
ERROR HY000: Too long comment for table 't1'
create table t1 (
i int comment
'123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*');
ERROR HY000: Too long comment for field 'i'
set @@sql_mode= @org_mode;
create table t1
(i int comment
'123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*');
Warnings:
Warning	1105	Unknown error
select column_name, column_comment from information_schema.columns where
table_schema = 'test' and table_name = 't1';
column_name	column_comment
i	123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
drop table t1;
1342 1343 1344 1345 1346 1347
set names utf8;
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*123456789*';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
gluh@mysql.com/vva.(none)'s avatar
gluh@mysql.com/vva.(none) committed
1348
  `i` int(11) DEFAULT NULL
1349 1350
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
drop table t1;
1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390
set sql_mode= 'traditional';
create table t1(col1 tinyint, col2 tinyint unsigned, 
col3 smallint, col4 smallint unsigned,
col5 mediumint, col6 mediumint unsigned,
col7 int, col8 int unsigned,
col9 bigint, col10 bigint unsigned);
insert into t1(col1) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1
insert into t1(col2) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1
insert into t1(col3) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1
insert into t1(col4) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1
insert into t1(col5) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1
insert into t1(col6) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1
insert into t1(col7) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1
insert into t1(col8) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1
insert into t1(col9) values('-');
ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1
insert into t1(col10) values('+');
ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1
drop table t1;
set sql_mode='traditional';
create table t1(a year);
insert into t1 values ('-');
ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1
insert into t1 values ('+');
ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1
insert into t1 values ('');
ERROR HY000: Incorrect integer value: '' for column 'a' at row 1
insert into t1 values ('2000a');
ERROR 01000: Data truncated for column 'a' at row 1
insert into t1 values ('2E3x');
ERROR 01000: Data truncated for column 'a' at row 1
drop table t1;
1391 1392 1393 1394 1395
set sql_mode='traditional';
create table t1 (f1 set('a','a'));
ERROR HY000: Column 'f1' has duplicated value 'a' in SET
create table t1 (f1 enum('a','a'));
ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM
1396 1397 1398 1399 1400 1401
set @@sql_mode='NO_ZERO_DATE';
create table t1(a datetime not null);
select count(*) from t1 where a is null;
count(*)
0
drop table t1;
1402
End of 5.0 tests