1
set @org_mode=@@sql_mode;
2
set @@sql_mode='ansi,traditional';
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;
7
CREATE TABLE t1 (col1 date);
8
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
9
INSERT INTO t1 VALUES('0000-10-31');
10
ERROR 22007: Incorrect date value: '0000-10-31' for column 'col1' at row 1
11
INSERT INTO t1 VALUES('2004-0-31');
12
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1
13
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
14
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
15
INSERT INTO t1 VALUES('2004-10-0');
16
ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1
17
INSERT INTO t1 VALUES('2004-09-31');
18
ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1
19
INSERT INTO t1 VALUES('2004-10-32');
20
ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1
21
INSERT INTO t1 VALUES('2003-02-29');
22
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
23
INSERT INTO t1 VALUES('2004-13-15');
24
ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1
25
INSERT INTO t1 VALUES('0000-00-00');
26
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
27
INSERT INTO t1 VALUES ('59');
28
ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1
29
set @@sql_mode='STRICT_ALL_TABLES';
30
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
31
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
32
INSERT INTO t1 VALUES('2004-0-30');
33
ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1
34
INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
35
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
36
INSERT INTO t1 VALUES('0000-00-00');
37
INSERT IGNORE INTO t1 VALUES('2004-0-29');
39
Warning 1265 Data truncated for column 'col1' at row 1
40
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
41
INSERT INTO t1 VALUES('0000-00-00');
42
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
43
INSERT IGNORE INTO t1 VALUES('0000-00-00');
45
Warning 1265 Data truncated for column 'col1' at row 1
46
INSERT INTO t1 VALUES ('2004-0-30');
47
INSERT INTO t1 VALUES ('2004-2-30');
48
ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1
49
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
50
INSERT INTO t1 VALUES ('2004-2-30');
51
set @@sql_mode='ansi,traditional';
52
INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
54
Warning 1265 Data truncated for column 'col1' at row 2
55
Warning 1265 Data truncated for column 'col1' at row 3
56
select * from t1 order by col1;
71
set @@sql_mode='strict_trans_tables';
72
CREATE TABLE t1 (col1 date) engine=myisam;
73
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
74
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
75
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
77
Warning 1265 Data truncated for column 'col1' at row 2
78
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
80
Warning 1265 Data truncated for column 'col1' at row 1
81
INSERT INTO t1 VALUES ('2003-02-29');
82
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
83
INSERT ignore INTO t1 VALUES('2003-02-30');
85
Warning 1265 Data truncated for column 'col1' at row 1
86
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
87
INSERT ignore INTO t1 VALUES('2003-02-31');
98
set @@sql_mode='strict_trans_tables';
99
CREATE TABLE t1 (col1 date) engine=innodb;
100
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
101
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
102
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
103
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2
104
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
106
Warning 1265 Data truncated for column 'col1' at row 1
107
INSERT INTO t1 VALUES ('2003-02-29');
108
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
109
INSERT ignore INTO t1 VALUES('2003-02-30');
111
Warning 1265 Data truncated for column 'col1' at row 1
112
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
113
INSERT ignore INTO t1 VALUES('2003-02-31');
121
set @@sql_mode='ansi,traditional';
122
CREATE TABLE t1 (col1 datetime);
123
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
124
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
125
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1
126
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
127
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
128
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
129
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
130
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
131
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
132
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
133
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
134
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
135
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
136
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
137
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
138
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
139
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
140
INSERT INTO t1 VALUES ('59');
141
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
147
CREATE TABLE t1 (col1 timestamp);
148
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
149
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
150
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1
151
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
152
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
153
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
154
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
155
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
156
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
157
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
158
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
159
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
160
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
161
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
162
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
163
INSERT INTO t1 VALUES('2004-02-29 25:30:00');
164
ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1
165
INSERT INTO t1 VALUES('2004-02-29 15:65:00');
166
ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1
167
INSERT INTO t1 VALUES('2004-02-29 15:31:61');
168
ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1
169
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
170
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
171
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
172
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
173
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
175
Warning 1265 Data truncated for column 'col1' at row 1
176
INSERT INTO t1 VALUES ('59');
177
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
178
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
179
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
180
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
181
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
182
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
183
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
184
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
185
INSERT INTO t1 VALUES('2004-02-30 15:30:04');
186
ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1
187
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
188
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
189
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
190
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
191
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
192
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
193
set @@sql_mode='ansi,traditional';
202
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
203
INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
204
INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
205
INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
206
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
207
ERROR 22007: Incorrect date value: '0000-10-31 15:30:00' for column 'col1' at row 1
208
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
209
ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1
210
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
211
ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1
212
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
213
ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1
214
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
215
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time
216
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
217
ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1
218
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
219
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time
220
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
221
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
222
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
223
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col2' at row 1
224
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
225
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1
226
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
227
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
228
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
229
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1
230
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
231
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time
232
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
233
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1
234
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
235
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time
236
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
237
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1
238
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
239
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
240
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
241
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col3' at row 1
242
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
243
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
244
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
245
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1
246
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
247
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time
248
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
249
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1
250
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
251
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time
252
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
253
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1
255
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
256
INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
257
INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
258
INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
259
INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
260
ERROR 22007: Incorrect datetime value: '0000-10-31'
261
INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
262
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
263
INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
264
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1
265
INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
266
ERROR 22007: Incorrect datetime value: '0000-00-00'
267
INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
268
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30'
269
INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
270
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
271
INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
272
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'
277
INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
278
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
279
INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
280
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'
284
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
285
INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));
286
INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
287
INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
288
INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
289
ERROR 22007: Incorrect datetime value: '0000-10-31'
290
INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
291
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
292
INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
293
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1
294
INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
295
ERROR 22007: Incorrect datetime value: '0000-00-00'
296
INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
297
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30'
298
INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
299
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
300
INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
301
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1
302
INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));
303
ERROR 22007: Incorrect datetime value: '0000-00-00'
304
INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
305
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30'
306
INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
307
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
308
INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
309
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1
310
INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));
311
ERROR 22007: Incorrect datetime value: '0000-00-00'
313
CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);
314
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);
315
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
320
Error 1365 Division by 0
321
Error 1365 Division by 0
322
INSERT INTO t1 (col1) VALUES(-129);
323
ERROR 22003: Out of range value for column 'col1' at row 1
324
INSERT INTO t1 (col1) VALUES(128);
325
ERROR 22003: Out of range value for column 'col1' at row 1
326
INSERT INTO t1 (col2) VALUES(-1);
327
ERROR 22003: Out of range value for column 'col2' at row 1
328
INSERT INTO t1 (col2) VALUES(256);
329
ERROR 22003: Out of range value for column 'col2' at row 1
330
INSERT INTO t1 (col1) VALUES('-129');
331
ERROR 22003: Out of range value for column 'col1' at row 1
332
INSERT INTO t1 (col1) VALUES('128');
333
ERROR 22003: Out of range value for column 'col1' at row 1
334
INSERT INTO t1 (col2) VALUES('-1');
335
ERROR 22003: Out of range value for column 'col2' at row 1
336
INSERT INTO t1 (col2) VALUES('256');
337
ERROR 22003: Out of range value for column 'col2' at row 1
338
INSERT INTO t1 (col1) VALUES(128.0);
339
ERROR 22003: Out of range value for column 'col1' at row 1
340
INSERT INTO t1 (col2) VALUES(-1.0);
341
ERROR 22003: Out of range value for column 'col2' at row 1
342
INSERT INTO t1 (col2) VALUES(256.0);
343
ERROR 22003: Out of range value for column 'col2' at row 1
344
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;
348
Error 1365 Division by 0
349
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
350
ERROR 22003: Out of range value for column 'col1' at row 1
351
UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;
352
ERROR 22003: Out of range value for column 'col2' at row 3
353
UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;
354
ERROR 22012: Division by 0
355
set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
356
INSERT INTO t1 values (1/0,1/0);
358
Error 1365 Division by 0
359
Error 1365 Division by 0
360
set @@sql_mode='ansi,traditional';
361
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
366
Error 1365 Division by 0
367
Error 1365 Division by 0
368
INSERT INTO t1 (col1) VALUES ('');
369
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
370
INSERT INTO t1 (col1) VALUES ('a59b');
371
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
372
INSERT INTO t1 (col1) VALUES ('1a');
373
ERROR 01000: Data truncated for column 'col1' at row 1
374
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
376
Warning 1265 Data truncated for column 'col1' at row 1
377
INSERT IGNORE INTO t1 values (1/0,1/0);
379
Error 1365 Division by 0
380
Error 1365 Division by 0
381
set @@sql_mode='ansi';
382
INSERT INTO t1 values (1/0,1/0);
383
set @@sql_mode='ansi,traditional';
384
INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');
386
Warning 1264 Out of range value for column 'col1' at row 1
387
Warning 1264 Out of range value for column 'col2' at row 1
388
Warning 1264 Out of range value for column 'col1' at row 2
389
Warning 1264 Out of range value for column 'col2' at row 2
390
INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);
392
Warning 1264 Out of range value for column 'col1' at row 1
393
Warning 1264 Out of range value for column 'col2' at row 1
394
Warning 1264 Out of range value for column 'col1' at row 2
395
Warning 1264 Out of range value for column 'col2' at row 2
396
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
417
CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);
418
INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);
419
INSERT INTO t1 (col1) VALUES(-32769);
420
ERROR 22003: Out of range value for column 'col1' at row 1
421
INSERT INTO t1 (col1) VALUES(32768);
422
ERROR 22003: Out of range value for column 'col1' at row 1
423
INSERT INTO t1 (col2) VALUES(-1);
424
ERROR 22003: Out of range value for column 'col2' at row 1
425
INSERT INTO t1 (col2) VALUES(65536);
426
ERROR 22003: Out of range value for column 'col2' at row 1
427
INSERT INTO t1 (col1) VALUES('-32769');
428
ERROR 22003: Out of range value for column 'col1' at row 1
429
INSERT INTO t1 (col1) VALUES('32768');
430
ERROR 22003: Out of range value for column 'col1' at row 1
431
INSERT INTO t1 (col2) VALUES('-1');
432
ERROR 22003: Out of range value for column 'col2' at row 1
433
INSERT INTO t1 (col2) VALUES('65536');
434
ERROR 22003: Out of range value for column 'col2' at row 1
435
INSERT INTO t1 (col1) VALUES(-32769.0);
436
ERROR 22003: Out of range value for column 'col1' at row 1
437
INSERT INTO t1 (col1) VALUES(32768.0);
438
ERROR 22003: Out of range value for column 'col1' at row 1
439
INSERT INTO t1 (col2) VALUES(-1.0);
440
ERROR 22003: Out of range value for column 'col2' at row 1
441
INSERT INTO t1 (col2) VALUES(65536.0);
442
ERROR 22003: Out of range value for column 'col2' at row 1
443
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
444
ERROR 22003: Out of range value for column 'col1' at row 1
445
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
446
ERROR 22003: Out of range value for column 'col2' at row 3
447
UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;
448
ERROR 22012: Division by 0
449
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
450
ERROR 22012: Division by 0
451
INSERT INTO t1 (col1) VALUES ('');
452
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
453
INSERT INTO t1 (col1) VALUES ('a59b');
454
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
455
INSERT INTO t1 (col1) VALUES ('1a');
456
ERROR 01000: Data truncated for column 'col1' at row 1
457
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
459
Warning 1265 Data truncated for column 'col1' at row 1
460
INSERT IGNORE INTO t1 values (1/0,1/0);
462
Error 1365 Division by 0
463
Error 1365 Division by 0
464
INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
466
Warning 1264 Out of range value for column 'col1' at row 1
467
Warning 1264 Out of range value for column 'col2' at row 1
468
Warning 1264 Out of range value for column 'col1' at row 2
469
Warning 1264 Out of range value for column 'col2' at row 2
470
INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');
472
Warning 1264 Out of range value for column 'col1' at row 1
473
Warning 1264 Out of range value for column 'col2' at row 1
474
Warning 1264 Out of range value for column 'col1' at row 2
475
Warning 1264 Out of range value for column 'col2' at row 2
476
INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);
478
Warning 1264 Out of range value for column 'col1' at row 1
479
Warning 1264 Out of range value for column 'col2' at row 1
480
Warning 1264 Out of range value for column 'col1' at row 2
481
Warning 1264 Out of range value for column 'col2' at row 2
482
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
501
CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);
502
INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);
503
INSERT INTO t1 (col1) VALUES(-8388609);
504
ERROR 22003: Out of range value for column 'col1' at row 1
505
INSERT INTO t1 (col1) VALUES(8388608);
506
ERROR 22003: Out of range value for column 'col1' at row 1
507
INSERT INTO t1 (col2) VALUES(-1);
508
ERROR 22003: Out of range value for column 'col2' at row 1
509
INSERT INTO t1 (col2) VALUES(16777216);
510
ERROR 22003: Out of range value for column 'col2' at row 1
511
INSERT INTO t1 (col1) VALUES('-8388609');
512
ERROR 22003: Out of range value for column 'col1' at row 1
513
INSERT INTO t1 (col1) VALUES('8388608');
514
ERROR 22003: Out of range value for column 'col1' at row 1
515
INSERT INTO t1 (col2) VALUES('-1');
516
ERROR 22003: Out of range value for column 'col2' at row 1
517
INSERT INTO t1 (col2) VALUES('16777216');
518
ERROR 22003: Out of range value for column 'col2' at row 1
519
INSERT INTO t1 (col1) VALUES(-8388609.0);
520
ERROR 22003: Out of range value for column 'col1' at row 1
521
INSERT INTO t1 (col1) VALUES(8388608.0);
522
ERROR 22003: Out of range value for column 'col1' at row 1
523
INSERT INTO t1 (col2) VALUES(-1.0);
524
ERROR 22003: Out of range value for column 'col2' at row 1
525
INSERT INTO t1 (col2) VALUES(16777216.0);
526
ERROR 22003: Out of range value for column 'col2' at row 1
527
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
528
ERROR 22003: Out of range value for column 'col1' at row 1
529
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
530
ERROR 22003: Out of range value for column 'col2' at row 3
531
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
532
ERROR 22012: Division by 0
533
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
534
ERROR 22012: Division by 0
535
INSERT INTO t1 (col1) VALUES ('');
536
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
537
INSERT INTO t1 (col1) VALUES ('a59b');
538
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
539
INSERT INTO t1 (col1) VALUES ('1a');
540
ERROR 01000: Data truncated for column 'col1' at row 1
541
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
543
Warning 1265 Data truncated for column 'col1' at row 1
544
INSERT IGNORE INTO t1 values (1/0,1/0);
546
Error 1365 Division by 0
547
Error 1365 Division by 0
548
INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
550
Warning 1264 Out of range value for column 'col1' at row 1
551
Warning 1264 Out of range value for column 'col2' at row 1
552
Warning 1264 Out of range value for column 'col1' at row 2
553
Warning 1264 Out of range value for column 'col2' at row 2
554
INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');
556
Warning 1264 Out of range value for column 'col1' at row 1
557
Warning 1264 Out of range value for column 'col2' at row 1
558
Warning 1264 Out of range value for column 'col1' at row 2
559
Warning 1264 Out of range value for column 'col2' at row 2
560
INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);
562
Warning 1264 Out of range value for column 'col1' at row 1
563
Warning 1264 Out of range value for column 'col2' at row 1
564
Warning 1264 Out of range value for column 'col1' at row 2
565
Warning 1264 Out of range value for column 'col2' at row 2
566
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
585
CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);
586
INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);
587
INSERT INTO t1 (col1) VALUES(-2147483649);
588
ERROR 22003: Out of range value for column 'col1' at row 1
589
INSERT INTO t1 (col1) VALUES(2147643648);
590
ERROR 22003: Out of range value for column 'col1' at row 1
591
INSERT INTO t1 (col2) VALUES(-1);
592
ERROR 22003: Out of range value for column 'col2' at row 1
593
INSERT INTO t1 (col2) VALUES(4294967296);
594
ERROR 22003: Out of range value for column 'col2' at row 1
595
INSERT INTO t1 (col1) VALUES('-2147483649');
596
ERROR 22003: Out of range value for column 'col1' at row 1
597
INSERT INTO t1 (col1) VALUES('2147643648');
598
ERROR 22003: Out of range value for column 'col1' at row 1
599
INSERT INTO t1 (col2) VALUES('-1');
600
ERROR 22003: Out of range value for column 'col2' at row 1
601
INSERT INTO t1 (col2) VALUES('4294967296');
602
ERROR 22003: Out of range value for column 'col2' at row 1
603
INSERT INTO t1 (col1) VALUES(-2147483649.0);
604
ERROR 22003: Out of range value for column 'col1' at row 1
605
INSERT INTO t1 (col1) VALUES(2147643648.0);
606
ERROR 22003: Out of range value for column 'col1' at row 1
607
INSERT INTO t1 (col2) VALUES(-1.0);
608
ERROR 22003: Out of range value for column 'col2' at row 1
609
INSERT INTO t1 (col2) VALUES(4294967296.0);
610
ERROR 22003: Out of range value for column 'col2' at row 1
611
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
612
ERROR 22003: Out of range value for column 'col1' at row 1
613
UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;
614
ERROR 22003: Out of range value for column 'col2' at row 3
615
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
616
ERROR 22012: Division by 0
617
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
618
ERROR 22012: Division by 0
619
INSERT INTO t1 (col1) VALUES ('');
620
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
621
INSERT INTO t1 (col1) VALUES ('a59b');
622
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
623
INSERT INTO t1 (col1) VALUES ('1a');
624
ERROR 01000: Data truncated for column 'col1' at row 1
625
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
627
Warning 1265 Data truncated for column 'col1' at row 1
628
INSERT IGNORE INTO t1 values (1/0,1/0);
630
Error 1365 Division by 0
631
Error 1365 Division by 0
632
INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
634
Warning 1264 Out of range value for column 'col1' at row 1
635
Warning 1264 Out of range value for column 'col2' at row 1
636
Warning 1264 Out of range value for column 'col1' at row 2
637
Warning 1264 Out of range value for column 'col2' at row 2
638
INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');
640
Warning 1264 Out of range value for column 'col1' at row 1
641
Warning 1264 Out of range value for column 'col2' at row 1
642
Warning 1264 Out of range value for column 'col1' at row 2
643
Warning 1264 Out of range value for column 'col2' at row 2
644
INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);
646
Warning 1264 Out of range value for column 'col1' at row 1
647
Warning 1264 Out of range value for column 'col2' at row 1
648
Warning 1264 Out of range value for column 'col1' at row 2
649
Warning 1264 Out of range value for column 'col2' at row 2
650
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
654
2147483647 4294967295
656
2147483647 4294967295
658
2147483647 4294967295
662
2147483647 4294967295
664
2147483647 4294967295
666
2147483647 4294967295
669
CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);
670
INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);
671
INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');
672
INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);
673
INSERT INTO t1 (col1) VALUES(-9223372036854775809);
674
ERROR 22003: Out of range value for column 'col1' at row 1
675
INSERT INTO t1 (col1) VALUES(9223372036854775808);
676
ERROR 22003: Out of range value for column 'col1' at row 1
677
INSERT INTO t1 (col2) VALUES(-1);
678
ERROR 22003: Out of range value for column 'col2' at row 1
679
INSERT INTO t1 (col2) VALUES(18446744073709551616);
680
ERROR 22003: Out of range value for column 'col2' at row 1
681
INSERT INTO t1 (col1) VALUES('-9223372036854775809');
682
ERROR 22003: Out of range value for column 'col1' at row 1
683
INSERT INTO t1 (col1) VALUES('9223372036854775808');
684
ERROR 22003: Out of range value for column 'col1' at row 1
685
INSERT INTO t1 (col2) VALUES('-1');
686
ERROR 22003: Out of range value for column 'col2' at row 1
687
INSERT INTO t1 (col2) VALUES('18446744073709551616');
688
ERROR 22003: Out of range value for column 'col2' at row 1
689
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);
690
ERROR 22003: Out of range value for column 'col1' at row 1
691
INSERT INTO t1 (col1) VALUES(9223372036854785808.0);
692
ERROR 22003: Out of range value for column 'col1' at row 1
693
INSERT INTO t1 (col2) VALUES(-1.0);
694
ERROR 22003: Out of range value for column 'col2' at row 1
695
INSERT INTO t1 (col2) VALUES(18446744073709551616.0);
696
ERROR 22003: Out of range value for column 'col2' at row 1
697
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
698
ERROR 22012: Division by 0
699
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
700
ERROR 22012: Division by 0
701
INSERT INTO t1 (col1) VALUES ('');
702
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
703
INSERT INTO t1 (col1) VALUES ('a59b');
704
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
705
INSERT INTO t1 (col1) VALUES ('1a');
706
ERROR 01000: Data truncated for column 'col1' at row 1
707
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
709
Warning 1265 Data truncated for column 'col1' at row 1
710
INSERT IGNORE INTO t1 values (1/0,1/0);
712
Error 1365 Division by 0
713
Error 1365 Division by 0
714
INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
716
Warning 1264 Out of range value for column 'col1' at row 1
717
Warning 1264 Out of range value for column 'col2' at row 1
718
Warning 1264 Out of range value for column 'col1' at row 2
719
Warning 1264 Out of range value for column 'col2' at row 2
720
INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');
722
Warning 1264 Out of range value for column 'col1' at row 1
723
Warning 1264 Out of range value for column 'col2' at row 1
724
Warning 1264 Out of range value for column 'col1' at row 2
725
Warning 1264 Out of range value for column 'col2' at row 2
726
INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);
728
Warning 1264 Out of range value for column 'col1' at row 1
729
Warning 1264 Out of range value for column 'col2' at row 1
730
Warning 1264 Out of range value for column 'col1' at row 2
731
Warning 1264 Out of range value for column 'col2' at row 2
732
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
735
-9223372036854775808 0
736
9223372036854775807 18446744073709551615
737
-9223372036854775808 0
738
9223372036854775807 18446744073709551615
739
-9223372036854774000 0
740
9223372036854775700 1844674407370954000
743
-9223372036854775808 0
744
9223372036854775807 18446744073709551615
745
-9223372036854775808 0
746
9223372036854775807 18446744073709551615
747
-9223372036854775808 0
748
9223372036854775807 18446744073709551615
751
CREATE TABLE t1 (col1 NUMERIC(4,2));
752
INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
754
Note 1265 Data truncated for column 'col1' at row 2
755
Note 1265 Data truncated for column 'col1' at row 5
756
INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');
758
Note 1265 Data truncated for column 'col1' at row 2
759
Note 1265 Data truncated for column 'col1' at row 4
760
INSERT INTO t1 VALUES (101.55);
761
ERROR 22003: Out of range value for column 'col1' at row 1
762
INSERT INTO t1 VALUES (101);
763
ERROR 22003: Out of range value for column 'col1' at row 1
764
INSERT INTO t1 VALUES (-101.55);
765
ERROR 22003: Out of range value for column 'col1' at row 1
766
INSERT INTO t1 VALUES (1010.55);
767
ERROR 22003: Out of range value for column 'col1' at row 1
768
INSERT INTO t1 VALUES (1010);
769
ERROR 22003: Out of range value for column 'col1' at row 1
770
INSERT INTO t1 VALUES ('101.55');
771
ERROR 22003: Out of range value for column 'col1' at row 1
772
INSERT INTO t1 VALUES ('101');
773
ERROR 22003: Out of range value for column 'col1' at row 1
774
INSERT INTO t1 VALUES ('-101.55');
775
ERROR 22003: Out of range value for column 'col1' at row 1
776
INSERT INTO t1 VALUES ('-1010.55');
777
ERROR 22003: Out of range value for column 'col1' at row 1
778
INSERT INTO t1 VALUES ('-100E+1');
779
ERROR 22003: Out of range value for column 'col1' at row 1
780
INSERT INTO t1 VALUES ('-100E');
781
ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1
782
UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;
783
ERROR 22003: Out of range value for column 'col1' at row 6
784
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
785
ERROR 22012: Division by 0
786
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
787
ERROR 22012: Division by 0
788
INSERT INTO t1 (col1) VALUES ('');
789
ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1
790
INSERT INTO t1 (col1) VALUES ('a59b');
791
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
792
INSERT INTO t1 (col1) VALUES ('1a');
793
ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1
794
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
796
Note 1265 Data truncated for column 'col1' at row 1
797
INSERT IGNORE INTO t1 values (1/0);
799
Error 1365 Division by 0
800
INSERT IGNORE INTO t1 VALUES(1000),(-1000);
802
Warning 1264 Out of range value for column 'col1' at row 1
803
Warning 1264 Out of range value for column 'col1' at row 2
804
INSERT IGNORE INTO t1 VALUES('1000'),('-1000');
806
Warning 1264 Out of range value for column 'col1' at row 1
807
Warning 1264 Out of range value for column 'col1' at row 2
808
INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);
810
Warning 1264 Out of range value for column 'col1' at row 1
811
Warning 1264 Out of range value for column 'col1' at row 2
812
UPDATE IGNORE t1 SET col1=1/NULL where col1=0;
837
CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);
838
INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);
839
INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');
840
INSERT INTO t1 (col1) VALUES (3E-46);
841
INSERT INTO t1 (col1) VALUES (+3.4E+39);
842
ERROR 22003: Out of range value for column 'col1' at row 1
843
INSERT INTO t1 (col2) VALUES (-1.1E-3);
844
ERROR 22003: Out of range value for column 'col2' at row 1
845
INSERT INTO t1 (col1) VALUES ('+3.4E+39');
846
ERROR 22003: Out of range value for column 'col1' at row 1
847
INSERT INTO t1 (col2) VALUES ('-1.1E-3');
848
ERROR 22003: Out of range value for column 'col2' at row 1
849
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
850
ERROR 22003: Out of range value for column 'col1' at row 2
851
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
852
ERROR 22012: Division by 0
853
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
854
ERROR 22012: Division by 0
855
INSERT INTO t1 (col1) VALUES ('');
856
ERROR 01000: Data truncated for column 'col1' at row 1
857
INSERT INTO t1 (col1) VALUES ('a59b');
858
ERROR 01000: Data truncated for column 'col1' at row 1
859
INSERT INTO t1 (col1) VALUES ('1a');
860
ERROR 01000: Data truncated for column 'col1' at row 1
861
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
863
Warning 1265 Data truncated for column 'col1' at row 1
864
INSERT IGNORE INTO t1 (col1) VALUES (1/0);
866
Error 1365 Division by 0
867
INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
869
Warning 1264 Out of range value for column 'col1' at row 1
870
Warning 1264 Out of range value for column 'col2' at row 1
871
INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');
873
Warning 1264 Out of range value for column 'col1' at row 1
874
Warning 1264 Out of range value for column 'col2' at row 1
887
CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);
888
INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);
889
INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');
890
INSERT INTO t1 (col1) VALUES (-2.2E-330);
891
INSERT INTO t1 (col1) VALUES (+1.7E+309);
892
Got one of the listed errors
893
INSERT INTO t1 (col2) VALUES (-1.1E-3);
894
ERROR 22003: Out of range value for column 'col2' at row 1
895
INSERT INTO t1 (col1) VALUES ('+1.8E+309');
896
ERROR 22003: Out of range value for column 'col1' at row 1
897
INSERT INTO t1 (col2) VALUES ('-1.2E-3');
898
ERROR 22003: Out of range value for column 'col2' at row 1
899
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
900
ERROR 22003: Out of range value for column 'col1' at row 3
901
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
902
ERROR 22012: Division by 0
903
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
904
ERROR 22012: Division by 0
905
INSERT INTO t1 (col1) VALUES ('');
906
ERROR 01000: Data truncated for column 'col1' at row 1
907
INSERT INTO t1 (col1) VALUES ('a59b');
908
ERROR 01000: Data truncated for column 'col1' at row 1
909
INSERT INTO t1 (col1) VALUES ('1a');
910
ERROR 01000: Data truncated for column 'col1' at row 1
911
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
913
Warning 1265 Data truncated for column 'col1' at row 1
914
INSERT IGNORE INTO t1 (col1) values (1/0);
916
Error 1365 Division by 0
917
INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);
918
ERROR 22007: Illegal double '1.9E+309' value found during parsing
919
INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');
921
Warning 1264 Out of range value for column 'col1' at row 1
922
Warning 1264 Out of range value for column 'col2' at row 1
923
Warning 1264 Out of range value for column 'col2' at row 1
924
SELECT * FROM t1 order by col1;
935
1.79769313486232e+308 0
937
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
938
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello ');
939
INSERT INTO t1 (col1) VALUES ('hellobob');
940
ERROR 22001: Data too long for column 'col1' at row 1
941
INSERT INTO t1 (col2) VALUES ('hellobob');
942
ERROR 22001: Data too long for column 'col2' at row 1
943
INSERT INTO t1 (col2) VALUES ('hello ');
945
Note 1265 Data truncated for column 'col2' at row 1
946
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
947
ERROR 22001: Data too long for column 'col1' at row 2
948
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
949
ERROR 22001: Data too long for column 'col2' at row 2
950
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
952
Warning 1265 Data truncated for column 'col1' at row 1
953
Warning 1265 Data truncated for column 'col2' at row 1
954
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
956
Warning 1265 Data truncated for column 'col2' at row 2
965
CREATE TABLE t1 (col1 enum('red','blue','green'));
966
INSERT INTO t1 VALUES ('red'),('blue'),('green');
967
INSERT INTO t1 (col1) VALUES ('yellow');
968
ERROR 01000: Data truncated for column 'col1' at row 1
969
INSERT INTO t1 (col1) VALUES ('redd');
970
ERROR 01000: Data truncated for column 'col1' at row 1
971
INSERT INTO t1 VALUES ('');
972
ERROR 01000: Data truncated for column 'col1' at row 1
973
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
974
ERROR 01000: Data truncated for column 'col1' at row 3
975
INSERT IGNORE INTO t1 VALUES ('yellow');
977
Warning 1265 Data truncated for column 'col1' at row 1
978
UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
980
Warning 1265 Data truncated for column 'col1' at row 2
988
CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);
989
INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');
990
INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');
991
INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
992
ERROR 23000: Column 'col1' cannot be null
993
INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
994
ERROR 23000: Column 'col2' cannot be null
995
INSERT INTO t1 VALUES (103,'',NULL);
996
ERROR 23000: Column 'col3' cannot be null
997
UPDATE t1 SET col1=NULL WHERE col1 =100;
998
ERROR 23000: Column 'col1' cannot be null
999
UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
1000
ERROR 23000: Column 'col2' cannot be null
1001
UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;
1002
ERROR 23000: Column 'col2' cannot be null
1003
INSERT IGNORE INTO t1 values (NULL,NULL,NULL);
1005
Warning 1048 Column 'col1' cannot be null
1006
Warning 1048 Column 'col2' cannot be null
1007
Warning 1048 Column 'col3' cannot be null
1010
100 hello 2004-08-20
1011
101 hell2 2004-08-21
1014
CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
1015
SHOW CREATE TABLE t1;
1017
t1 CREATE TABLE "t1" (
1018
"col1" int(11) NOT NULL DEFAULT '99',
1019
"col2" char(6) NOT NULL
1021
INSERT INTO t1 VALUES (1, 'hello');
1022
INSERT INTO t1 (col2) VALUES ('hello2');
1023
INSERT INTO t1 (col2) VALUES (NULL);
1024
ERROR 23000: Column 'col2' cannot be null
1025
INSERT INTO t1 (col1) VALUES (2);
1026
ERROR HY000: Field 'col2' doesn't have a default value
1027
INSERT INTO t1 VALUES(default(col1),default(col2));
1028
ERROR HY000: Field 'col2' doesn't have a default value
1029
INSERT INTO t1 (col1) SELECT 1;
1030
ERROR HY000: Field 'col2' doesn't have a default value
1031
INSERT INTO t1 SELECT 1,NULL;
1032
ERROR 23000: Column 'col2' cannot be null
1033
INSERT IGNORE INTO t1 values (NULL,NULL);
1035
Warning 1048 Column 'col1' cannot be null
1036
Warning 1048 Column 'col2' cannot be null
1037
INSERT IGNORE INTO t1 (col1) values (3);
1039
Warning 1364 Field 'col2' doesn't have a default value
1040
INSERT IGNORE INTO t1 () values ();
1042
Warning 1364 Field 'col2' doesn't have a default value
1051
set sql_mode='traditional';
1052
create table t1 (charcol char(255), varcharcol varchar(255),
1053
binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
1054
tinyblobcol tinyblob);
1055
insert into t1 (charcol) values (repeat('x',256));
1056
ERROR 22001: Data too long for column 'charcol' at row 1
1057
insert into t1 (varcharcol) values (repeat('x',256));
1058
ERROR 22001: Data too long for column 'varcharcol' at row 1
1059
insert into t1 (binarycol) values (repeat('x',256));
1060
ERROR 22001: Data too long for column 'binarycol' at row 1
1061
insert into t1 (varbinarycol) values (repeat('x',256));
1062
ERROR 22001: Data too long for column 'varbinarycol' at row 1
1063
insert into t1 (tinytextcol) values (repeat('x',256));
1064
ERROR 22001: Data too long for column 'tinytextcol' at row 1
1065
insert into t1 (tinyblobcol) values (repeat('x',256));
1066
ERROR 22001: Data too long for column 'tinyblobcol' at row 1
1068
charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol
1070
set sql_mode='traditional';
1071
create table t1 (col1 datetime);
1072
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1073
ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1074
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1075
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time
1076
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1077
ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_time
1078
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1079
ERROR HY000: Incorrect time value: 'abc' for function str_to_time
1081
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1083
Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1084
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1086
Error 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_time
1087
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1089
Error 1411 Incorrect time value: '22:22:33 AM' for function str_to_time
1090
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1092
Error 1411 Incorrect time value: 'abc' for function str_to_time
1093
insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
1094
insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
1095
insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
1105
set sql_mode='traditional';
1106
select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
1110
Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time
1111
Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time
1112
Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time
1114
create table t1 (col1 char(3), col2 integer);
1115
insert into t1 (col1) values (cast(1000 as char(3)));
1116
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
1117
insert into t1 (col1) values (cast(1000E+0 as char(3)));
1118
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
1119
insert into t1 (col1) values (cast(1000.0 as char(3)));
1120
ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
1121
insert into t1 (col2) values (cast('abc' as signed integer));
1122
ERROR 22007: Truncated incorrect INTEGER value: 'abc'
1123
insert into t1 (col2) values (10E+0 + 'a');
1124
ERROR 22007: Truncated incorrect DOUBLE value: 'a'
1125
insert into t1 (col2) values (cast('10a' as unsigned integer));
1126
ERROR 22007: Truncated incorrect INTEGER value: '10a'
1127
insert into t1 (col2) values (cast('10' as unsigned integer));
1128
insert into t1 (col2) values (cast('10' as signed integer));
1129
insert into t1 (col2) values (10E+0 + '0 ');
1136
create table t1 (col1 date, col2 datetime, col3 timestamp);
1137
insert into t1 values (0,0,0);
1138
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1139
insert into t1 values (0.0,0.0,0.0);
1140
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1141
insert into t1 (col1) values (convert('0000-00-00',date));
1142
ERROR 22007: Incorrect datetime value: '0000-00-00'
1143
insert into t1 (col1) values (cast('0000-00-00' as date));
1144
ERROR 22007: Incorrect datetime value: '0000-00-00'
1145
set sql_mode='no_zero_date';
1146
insert into t1 values (0,0,0);
1148
Warning 1264 Out of range value for column 'col1' at row 1
1149
Warning 1264 Out of range value for column 'col2' at row 1
1150
Warning 1265 Data truncated for column 'col3' at row 1
1151
insert into t1 values (0.0,0.0,0.0);
1153
Warning 1264 Out of range value for column 'col1' at row 1
1154
Warning 1264 Out of range value for column 'col2' at row 1
1155
Warning 1265 Data truncated for column 'col3' at row 1
1157
set sql_mode='traditional';
1158
create table t1 (col1 date);
1159
insert ignore into t1 values ('0000-00-00');
1161
Warning 1265 Data truncated for column 'col1' at row 1
1162
insert into t1 select * from t1;
1163
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1164
insert ignore into t1 values ('0000-00-00');
1166
Warning 1265 Data truncated for column 'col1' at row 1
1167
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
1169
Warning 1292 Incorrect datetime value: '0000-00-00'
1170
insert into t1 select * from t1;
1171
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1172
alter table t1 modify col1 datetime;
1173
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
1174
alter ignore table t1 modify col1 datetime;
1176
Warning 1264 Out of range value for column 'col1' at row 1
1177
Warning 1264 Out of range value for column 'col1' at row 2
1178
insert into t1 select * from t1;
1179
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
1186
create table t1 (col1 tinyint);
1187
drop procedure if exists t1;
1189
Note 1305 PROCEDURE t1 does not exist
1190
create procedure t1 () begin declare exit handler for sqlexception
1191
select'a'; insert into t1 values (200); end;|
1199
set sql_mode=@org_mode;
1200
SET @@sql_mode = 'traditional';
1201
CREATE TABLE t1 (i int not null);
1202
INSERT INTO t1 VALUES ();
1203
ERROR HY000: Field 'i' doesn't have a default value
1204
INSERT INTO t1 VALUES (DEFAULT);
1205
ERROR HY000: Field 'i' doesn't have a default value
1206
INSERT INTO t1 VALUES (DEFAULT(i));
1207
ERROR HY000: Field 'i' doesn't have a default value
1208
ALTER TABLE t1 ADD j int;
1209
INSERT INTO t1 SET j = 1;
1210
ERROR HY000: Field 'i' doesn't have a default value
1211
INSERT INTO t1 SET j = 1, i = DEFAULT;
1212
ERROR HY000: Field 'i' doesn't have a default value
1213
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1214
ERROR HY000: Field 'i' doesn't have a default value
1215
INSERT INTO t1 VALUES (DEFAULT,1);
1216
ERROR HY000: Field 'i' doesn't have a default value
1218
SET @@sql_mode = '';
1219
CREATE TABLE t1 (i int not null);
1220
INSERT INTO t1 VALUES ();
1222
Warning 1364 Field 'i' doesn't have a default value
1223
INSERT INTO t1 VALUES (DEFAULT);
1225
Warning 1364 Field 'i' doesn't have a default value
1226
INSERT INTO t1 VALUES (DEFAULT(i));
1227
ERROR HY000: Field 'i' doesn't have a default value
1228
ALTER TABLE t1 ADD j int;
1229
INSERT INTO t1 SET j = 1;
1231
Warning 1364 Field 'i' doesn't have a default value
1232
INSERT INTO t1 SET j = 1, i = DEFAULT;
1234
Warning 1364 Field 'i' doesn't have a default value
1235
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1236
ERROR HY000: Field 'i' doesn't have a default value
1237
INSERT INTO t1 VALUES (DEFAULT,1);
1239
Warning 1364 Field 'i' doesn't have a default value
1241
set @@sql_mode='traditional';
1242
create table t1(a varchar(65537));
1243
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1244
create table t1(a varbinary(65537));
1245
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1246
set @@sql_mode='traditional';
1247
create table t1(a int, b date not null);
1248
alter table t1 modify a bigint unsigned not null;
1249
show create table t1;
1251
t1 CREATE TABLE `t1` (
1252
`a` bigint(20) unsigned NOT NULL,
1254
) ENGINE=PBXT DEFAULT CHARSET=latin1
1256
set @@sql_mode='traditional';
1257
create table t1 (d date);
1258
insert into t1 values ('2000-10-00');
1259
ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
1260
insert into t1 values (1000);
1261
ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
1262
insert into t1 values ('2000-10-01');
1263
update t1 set d = 1100;
1264
ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
1269
set @@sql_mode='traditional';
1270
create table t1(a int, b timestamp);
1271
alter table t1 add primary key(a);
1272
show create table t1;
1274
t1 CREATE TABLE `t1` (
1275
`a` int(11) NOT NULL DEFAULT '0',
1276
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1278
) ENGINE=PBXT DEFAULT CHARSET=latin1
1280
create table t1(a int, b timestamp default 20050102030405);
1281
alter table t1 add primary key(a);
1282
show create table t1;
1284
t1 CREATE TABLE `t1` (
1285
`a` int(11) NOT NULL DEFAULT '0',
1286
`b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
1288
) ENGINE=PBXT DEFAULT CHARSET=latin1
1290
set @@sql_mode='traditional';
1291
create table t1(a bit(2));
1292
insert into t1 values(b'101');
1293
ERROR 22001: Data too long for column 'a' at row 1
1297
set sql_mode='traditional';
1298
create table t1 (date date not null);
1299
create table t2 select date from t1;
1300
show create table t2;
1302
t2 CREATE TABLE `t2` (
1303
`date` date NOT NULL
1304
) ENGINE=PBXT DEFAULT CHARSET=latin1
1306
set @@sql_mode= @org_mode;
1307
set @@sql_mode='traditional';
1308
create table t1 (i int)
1309
comment '123456789*123456789*123456789*123456789*123456789*
1310
123456789*123456789*123456789*123456789*123456789*';
1311
ERROR HY000: Too long comment for table 't1'
1314
'123456789*123456789*123456789*123456789*
1315
123456789*123456789*123456789*123456789*
1316
123456789*123456789*123456789*123456789*
1317
123456789*123456789*123456789*123456789*
1318
123456789*123456789*123456789*123456789*
1319
123456789*123456789*123456789*123456789*
1320
123456789*123456789*123456789*123456789*');
1321
ERROR HY000: Too long comment for field 'i'
1322
set @@sql_mode= @org_mode;
1325
'123456789*123456789*123456789*123456789*
1326
123456789*123456789*123456789*123456789*
1327
123456789*123456789*123456789*123456789*
1328
123456789*123456789*123456789*123456789*
1329
123456789*123456789*123456789*123456789*
1330
123456789*123456789*123456789*123456789*
1331
123456789*123456789*123456789*123456789*');
1333
Warning 1105 Unknown error
1334
select column_name, column_comment from information_schema.columns where
1335
table_schema = 'test' and table_name = 't1';
1336
column_name column_comment
1337
i 123456789*123456789*123456789*123456789*
1338
123456789*123456789*123456789*123456789*
1339
123456789*123456789*123456789*123456789*
1340
123456789*123456789*123456789*123456789*
1341
123456789*123456789*123456789*123456789*
1342
123456789*123456789*123456789*123456789*
1345
create table t1 (i int)
1346
comment '123456789*123456789*123456789*123456789*123456789*123456789*';
1347
show create table t1;
1349
t1 CREATE TABLE `t1` (
1350
`i` int(11) DEFAULT NULL
1351
) ENGINE=PBXT DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'