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, t2;
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 INSERT INTO t1 VALUES('2004-0-31');
11 ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1
12 INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
13 ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
14 INSERT INTO t1 VALUES('2004-10-0');
15 ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1
16 INSERT INTO t1 VALUES('2004-09-31');
17 ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1
18 INSERT INTO t1 VALUES('2004-10-32');
19 ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1
20 INSERT INTO t1 VALUES('2003-02-29');
21 ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
22 INSERT INTO t1 VALUES('2004-13-15');
23 ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1
24 INSERT INTO t1 VALUES('0000-00-00');
25 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
26 INSERT INTO t1 VALUES ('59');
27 ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1
28 set @@sql_mode='STRICT_ALL_TABLES';
29 INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
30 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
31 INSERT INTO t1 VALUES('2004-0-30');
32 ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1
33 INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
34 ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2
35 INSERT INTO t1 VALUES('0000-00-00');
36 INSERT IGNORE INTO t1 VALUES('2004-0-29');
38 Warning 1265 Data truncated for column 'col1' at row 1
39 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
40 INSERT INTO t1 VALUES('0000-00-00');
41 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
42 INSERT IGNORE INTO t1 VALUES('0000-00-00');
44 Warning 1265 Data truncated for column 'col1' at row 1
45 INSERT INTO t1 VALUES ('2004-0-30');
46 INSERT INTO t1 VALUES ('2004-2-30');
47 ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1
48 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
49 INSERT INTO t1 VALUES ('2004-2-30');
50 set @@sql_mode='ansi,traditional';
51 INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
53 Warning 1265 Data truncated for column 'col1' at row 2
54 Warning 1265 Data truncated for column 'col1' at row 3
73 set @@sql_mode='strict_trans_tables';
74 CREATE TABLE t1 (col1 date) engine=myisam;
75 INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
76 ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
77 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
79 Warning 1265 Data truncated for column 'col1' at row 2
80 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
82 Warning 1265 Data truncated for column 'col1' at row 1
83 INSERT INTO t1 VALUES ('2003-02-29');
84 ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
85 INSERT ignore INTO t1 VALUES('2003-02-30');
87 Warning 1265 Data truncated for column 'col1' at row 1
88 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
89 INSERT ignore INTO t1 VALUES('2003-02-31');
100 set @@sql_mode='strict_trans_tables';
101 CREATE TABLE t1 (col1 date) engine=innodb;
102 INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
103 ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1
104 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
105 ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2
106 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
108 Warning 1265 Data truncated for column 'col1' at row 1
109 INSERT INTO t1 VALUES ('2003-02-29');
110 ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1
111 INSERT ignore INTO t1 VALUES('2003-02-30');
113 Warning 1265 Data truncated for column 'col1' at row 1
114 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
115 INSERT ignore INTO t1 VALUES('2003-02-31');
123 set @@sql_mode='ansi,traditional';
124 CREATE TABLE t1 (col1 datetime);
125 INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
126 INSERT INTO t1 VALUES('0000-10-31 15:30:00');
127 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
128 ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
129 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
130 ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
131 INSERT INTO t1 VALUES('2004-09-31 15:30:00');
132 ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
133 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
134 ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
135 INSERT INTO t1 VALUES('2003-02-29 15:30:00');
136 ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
137 INSERT INTO t1 VALUES('2004-13-15 15:30:00');
138 ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
139 INSERT INTO t1 VALUES('0000-00-00 15:30:00');
140 ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
141 INSERT INTO t1 VALUES ('59');
142 ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
149 CREATE TABLE t1 (col1 timestamp);
150 INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
151 INSERT INTO t1 VALUES('0000-10-31 15:30:00');
152 ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1
153 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
154 ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
155 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
156 ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
157 INSERT INTO t1 VALUES('2004-09-31 15:30:00');
158 ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1
159 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
160 ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
161 INSERT INTO t1 VALUES('2003-02-29 15:30:00');
162 ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1
163 INSERT INTO t1 VALUES('2004-13-15 15:30:00');
164 ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1
165 INSERT INTO t1 VALUES('2004-02-29 25:30:00');
166 ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1
167 INSERT INTO t1 VALUES('2004-02-29 15:65:00');
168 ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1
169 INSERT INTO t1 VALUES('2004-02-29 15:31:61');
170 ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1
171 INSERT INTO t1 VALUES('0000-00-00 15:30:00');
172 ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1
173 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
174 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
175 INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
177 Warning 1265 Data truncated for column 'col1' at row 1
178 INSERT INTO t1 VALUES ('59');
179 ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1
180 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
181 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
182 ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1
183 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
184 ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1
185 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
186 ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1
187 INSERT INTO t1 VALUES('2004-02-30 15:30:04');
188 ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1
189 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
190 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
191 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
192 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
193 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
194 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
195 set @@sql_mode='ansi,traditional';
204 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
205 INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
206 INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
207 INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
208 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
210 Note 1265 Data truncated for column 'col1' at row 1
211 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
212 ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1
213 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
214 ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1
215 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
216 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 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
220 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 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
224 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
225 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
226 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
227 ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1
228 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
229 ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
230 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
231 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 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
235 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 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
239 ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1
240 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
241 ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1
242 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
243 ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col3' at row 1
244 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
245 ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
246 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
247 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 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
251 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 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
255 ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1
257 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
258 INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
259 INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
260 INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
261 INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
262 INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
263 ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
264 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
265 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 INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
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:00' for column 'col3' at row 1
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 INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
290 ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1
291 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
292 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 INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
296 INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
297 ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1
298 INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
299 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 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
305 ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1
306 INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
307 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'
311 CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);
312 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 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
318 Error 1365 Division by 0
319 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 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;
346 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 UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;
352 ERROR 22012: Division by 0
353 set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
354 INSERT INTO t1 values (1/0,1/0);
356 Error 1365 Division by 0
357 Error 1365 Division by 0
358 set @@sql_mode='ansi,traditional';
359 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
364 Error 1365 Division by 0
365 Error 1365 Division by 0
366 INSERT INTO t1 (col1) VALUES ('');
367 ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
368 INSERT INTO t1 (col1) VALUES ('a59b');
369 ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
370 INSERT INTO t1 (col1) VALUES ('1a');
371 ERROR 01000: Data truncated for column 'col1' at row 1
372 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
374 Warning 1265 Data truncated for column 'col1' at row 1
375 INSERT IGNORE INTO t1 values (1/0,1/0);
377 Error 1365 Division by 0
378 Error 1365 Division by 0
379 set @@sql_mode='ansi';
380 INSERT INTO t1 values (1/0,1/0);
381 set @@sql_mode='ansi,traditional';
382 INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');
384 Warning 1264 Out of range value for column 'col1' at row 1
385 Warning 1264 Out of range value for column 'col2' at row 1
386 Warning 1264 Out of range value for column 'col1' at row 2
387 Warning 1264 Out of range value for column 'col2' at row 2
388 INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);
390 Warning 1264 Out of range value for column 'col1' at row 1
391 Warning 1264 Out of range value for column 'col2' at row 1
392 Warning 1264 Out of range value for column 'col1' at row 2
393 Warning 1264 Out of range value for column 'col2' at row 2
394 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
415 CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);
416 INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);
417 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 UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;
446 ERROR 22012: Division by 0
447 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
448 ERROR 22012: Division by 0
449 INSERT INTO t1 (col1) VALUES ('');
450 ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
451 INSERT INTO t1 (col1) VALUES ('a59b');
452 ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
453 INSERT INTO t1 (col1) VALUES ('1a');
454 ERROR 01000: Data truncated for column 'col1' at row 1
455 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
457 Warning 1265 Data truncated for column 'col1' at row 1
458 INSERT IGNORE INTO t1 values (1/0,1/0);
460 Error 1365 Division by 0
461 Error 1365 Division by 0
462 INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
464 Warning 1264 Out of range value for column 'col1' at row 1
465 Warning 1264 Out of range value for column 'col2' at row 1
466 Warning 1264 Out of range value for column 'col1' at row 2
467 Warning 1264 Out of range value for column 'col2' at row 2
468 INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');
470 Warning 1264 Out of range value for column 'col1' at row 1
471 Warning 1264 Out of range value for column 'col2' at row 1
472 Warning 1264 Out of range value for column 'col1' at row 2
473 Warning 1264 Out of range value for column 'col2' at row 2
474 INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);
476 Warning 1264 Out of range value for column 'col1' at row 1
477 Warning 1264 Out of range value for column 'col2' at row 1
478 Warning 1264 Out of range value for column 'col1' at row 2
479 Warning 1264 Out of range value for column 'col2' at row 2
480 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
499 CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);
500 INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);
501 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 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
530 ERROR 22012: Division by 0
531 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
532 ERROR 22012: Division by 0
533 INSERT INTO t1 (col1) VALUES ('');
534 ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1
535 INSERT INTO t1 (col1) VALUES ('a59b');
536 ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1
537 INSERT INTO t1 (col1) VALUES ('1a');
538 ERROR 01000: Data truncated for column 'col1' at row 1
539 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
541 Warning 1265 Data truncated for column 'col1' at row 1
542 INSERT IGNORE INTO t1 values (1/0,1/0);
544 Error 1365 Division by 0
545 Error 1365 Division by 0
546 INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
548 Warning 1264 Out of range value for column 'col1' at row 1
549 Warning 1264 Out of range value for column 'col2' at row 1
550 Warning 1264 Out of range value for column 'col1' at row 2
551 Warning 1264 Out of range value for column 'col2' at row 2
552 INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');
554 Warning 1264 Out of range value for column 'col1' at row 1
555 Warning 1264 Out of range value for column 'col2' at row 1
556 Warning 1264 Out of range value for column 'col1' at row 2
557 Warning 1264 Out of range value for column 'col2' at row 2
558 INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);
560 Warning 1264 Out of range value for column 'col1' at row 1
561 Warning 1264 Out of range value for column 'col2' at row 1
562 Warning 1264 Out of range value for column 'col1' at row 2
563 Warning 1264 Out of range value for column 'col2' at row 2
564 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
583 CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);
584 INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);
585 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 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
614 ERROR 22012: Division by 0
615 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
616 ERROR 22012: Division by 0
617 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 INSERT INTO t1 (col1) VALUES ('1a');
622 ERROR 01000: Data truncated for column 'col1' at row 1
623 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
625 Warning 1265 Data truncated for column 'col1' at row 1
626 INSERT IGNORE INTO t1 values (1/0,1/0);
628 Error 1365 Division by 0
629 Error 1365 Division by 0
630 INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
632 Warning 1264 Out of range value for column 'col1' at row 1
633 Warning 1264 Out of range value for column 'col2' at row 1
634 Warning 1264 Out of range value for column 'col1' at row 2
635 Warning 1264 Out of range value for column 'col2' at row 2
636 INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');
638 Warning 1264 Out of range value for column 'col1' at row 1
639 Warning 1264 Out of range value for column 'col2' at row 1
640 Warning 1264 Out of range value for column 'col1' at row 2
641 Warning 1264 Out of range value for column 'col2' at row 2
642 INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);
644 Warning 1264 Out of range value for column 'col1' at row 1
645 Warning 1264 Out of range value for column 'col2' at row 1
646 Warning 1264 Out of range value for column 'col1' at row 2
647 Warning 1264 Out of range value for column 'col2' at row 2
648 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
653 2147483647 4294967295
655 2147483647 4294967295
657 2147483647 4294967295
661 2147483647 4294967295
663 2147483647 4294967295
665 2147483647 4294967295
667 CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);
668 INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);
669 INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');
670 INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);
671 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 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
696 ERROR 22012: Division by 0
697 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
698 ERROR 22012: Division by 0
699 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 INSERT INTO t1 (col1) VALUES ('1a');
704 ERROR 01000: Data truncated for column 'col1' at row 1
705 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
707 Warning 1265 Data truncated for column 'col1' at row 1
708 INSERT IGNORE INTO t1 values (1/0,1/0);
710 Error 1365 Division by 0
711 Error 1365 Division by 0
712 INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
714 Warning 1264 Out of range value for column 'col1' at row 1
715 Warning 1264 Out of range value for column 'col2' at row 1
716 Warning 1264 Out of range value for column 'col1' at row 2
717 Warning 1264 Out of range value for column 'col2' at row 2
718 INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');
720 Warning 1264 Out of range value for column 'col1' at row 1
721 Warning 1264 Out of range value for column 'col2' at row 1
722 Warning 1264 Out of range value for column 'col1' at row 2
723 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);
726 Warning 1264 Out of range value for column 'col1' at row 1
727 Warning 1264 Out of range value for column 'col2' at row 1
728 Warning 1264 Out of range value for column 'col1' at row 2
729 Warning 1264 Out of range value for column 'col2' at row 2
730 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
733 -9223372036854775808 0
735 9223372036854775807 18446744073709551615
736 -9223372036854775808 0
737 9223372036854775807 18446744073709551615
738 -9223372036854774000 0
739 9223372036854775700 1844674407370954000
742 -9223372036854775808 0
743 9223372036854775807 18446744073709551615
744 -9223372036854775808 0
745 9223372036854775807 18446744073709551615
746 -9223372036854775808 0
747 9223372036854775807 18446744073709551615
749 CREATE TABLE t1 (col1 NUMERIC(4,2));
750 INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
752 Note 1265 Data truncated for column 'col1' at row 2
753 Note 1265 Data truncated for column 'col1' at row 5
754 INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');
756 Note 1265 Data truncated for column 'col1' at row 2
757 Note 1265 Data truncated for column 'col1' at row 4
758 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 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
783 ERROR 22012: Division by 0
784 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
785 ERROR 22012: Division by 0
786 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 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
794 Note 1265 Data truncated for column 'col1' at row 1
795 INSERT IGNORE INTO t1 values (1/0);
797 Error 1365 Division by 0
798 INSERT IGNORE INTO t1 VALUES(1000),(-1000);
800 Warning 1264 Out of range value for column 'col1' at row 1
801 Warning 1264 Out of range value for column 'col1' at row 2
802 INSERT IGNORE INTO t1 VALUES('1000'),('-1000');
804 Warning 1264 Out of range value for column 'col1' at row 1
805 Warning 1264 Out of range value for column 'col1' at row 2
806 INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);
808 Warning 1264 Out of range value for column 'col1' at row 1
809 Warning 1264 Out of range value for column 'col1' at row 2
810 UPDATE IGNORE t1 SET col1=1/NULL where col1=0;
835 CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);
836 INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);
837 INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');
838 INSERT INTO t1 (col1) VALUES (3E-46);
839 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 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
850 ERROR 22012: Division by 0
851 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
852 ERROR 22012: Division by 0
853 INSERT INTO t1 (col1) VALUES ('');
854 ERROR 01000: Data truncated for column 'col1' at row 1
855 INSERT INTO t1 (col1) VALUES ('a59b');
856 ERROR 01000: Data truncated for column 'col1' at row 1
857 INSERT INTO t1 (col1) VALUES ('1a');
858 ERROR 01000: Data truncated for column 'col1' at row 1
859 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
861 Warning 1265 Data truncated for column 'col1' at row 1
862 INSERT IGNORE INTO t1 (col1) VALUES (1/0);
864 Error 1365 Division by 0
865 INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
867 Warning 1264 Out of range value for column 'col1' at row 1
868 Warning 1264 Out of range value for column 'col2' at row 1
869 INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');
871 Warning 1264 Out of range value for column 'col1' at row 1
872 Warning 1264 Out of range value for column 'col2' at row 1
885 CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);
886 INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);
887 INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');
888 INSERT INTO t1 (col1) VALUES (-2.2E-330);
889 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 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
899 ERROR 22012: Division by 0
900 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
901 ERROR 22012: Division by 0
902 INSERT INTO t1 (col1) VALUES ('');
903 ERROR 01000: Data truncated for column 'col1' at row 1
904 INSERT INTO t1 (col1) VALUES ('a59b');
905 ERROR 01000: Data truncated for column 'col1' at row 1
906 INSERT INTO t1 (col1) VALUES ('1a');
907 ERROR 01000: Data truncated for column 'col1' at row 1
908 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
910 Warning 1265 Data truncated for column 'col1' at row 1
911 INSERT IGNORE INTO t1 (col1) values (1/0);
913 Error 1365 Division by 0
914 INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);
915 ERROR 22007: Illegal double '1.9E+309' value found during parsing
916 INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');
918 Warning 1264 Out of range value for column 'col1' at row 1
919 Warning 1264 Out of range value for column 'col2' at row 1
920 Warning 1264 Out of range value for column 'col2' at row 1
932 1.79769313486232e+308 0
934 CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
935 INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello ');
936 INSERT INTO t1 (col1) VALUES ('hellobob');
937 ERROR 22001: Data too long for column 'col1' at row 1
938 INSERT INTO t1 (col2) VALUES ('hellobob');
939 ERROR 22001: Data too long for column 'col2' at row 1
940 INSERT INTO t1 (col2) VALUES ('hello ');
942 Note 1265 Data truncated for column 'col2' at row 1
943 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
944 ERROR 22001: Data too long for column 'col1' at row 2
945 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
946 ERROR 22001: Data too long for column 'col2' at row 2
947 INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
949 Warning 1265 Data truncated for column 'col1' at row 1
950 Warning 1265 Data truncated for column 'col2' at row 1
951 UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
953 Warning 1265 Data truncated for column 'col2' at row 2
962 CREATE TABLE t1 (col1 enum('red','blue','green'));
963 INSERT INTO t1 VALUES ('red'),('blue'),('green');
964 INSERT INTO t1 (col1) VALUES ('yellow');
965 ERROR 01000: Data truncated for column 'col1' at row 1
966 INSERT INTO t1 (col1) VALUES ('redd');
967 ERROR 01000: Data truncated for column 'col1' at row 1
968 INSERT INTO t1 VALUES ('');
969 ERROR 01000: Data truncated for column 'col1' at row 1
970 UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
971 ERROR 01000: Data truncated for column 'col1' at row 3
972 INSERT IGNORE INTO t1 VALUES ('yellow');
974 Warning 1265 Data truncated for column 'col1' at row 1
975 UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
977 Warning 1265 Data truncated for column 'col1' at row 2
985 CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);
986 INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');
987 INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');
988 INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
989 ERROR 23000: Column 'col1' cannot be null
990 INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
991 ERROR 23000: Column 'col2' cannot be null
992 INSERT INTO t1 VALUES (103,'',NULL);
993 ERROR 23000: Column 'col3' cannot be null
994 UPDATE t1 SET col1=NULL WHERE col1 =100;
995 ERROR 23000: Column 'col1' cannot be null
996 UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
997 ERROR 23000: Column 'col2' cannot be null
998 UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;
999 ERROR 23000: Column 'col2' cannot be null
1000 INSERT IGNORE INTO t1 values (NULL,NULL,NULL);
1002 Warning 1048 Column 'col1' cannot be null
1003 Warning 1048 Column 'col2' cannot be null
1004 Warning 1048 Column 'col3' cannot be null
1007 100 hello 2004-08-20
1008 101 hell2 2004-08-21
1011 CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
1012 SHOW CREATE TABLE t1;
1014 t1 CREATE TABLE "t1" (
1015 "col1" int(11) NOT NULL DEFAULT '99',
1016 "col2" char(6) NOT NULL
1018 INSERT INTO t1 VALUES (1, 'hello');
1019 INSERT INTO t1 (col2) VALUES ('hello2');
1020 INSERT INTO t1 (col2) VALUES (NULL);
1021 ERROR 23000: Column 'col2' cannot be null
1022 INSERT INTO t1 (col1) VALUES (2);
1023 ERROR HY000: Field 'col2' doesn't have a default value
1024 INSERT INTO t1 VALUES(default(col1),default(col2));
1025 ERROR HY000: Field 'col2' doesn't have a default value
1026 INSERT INTO t1 (col1) SELECT 1;
1027 ERROR HY000: Field 'col2' doesn't have a default value
1028 INSERT INTO t1 SELECT 1,NULL;
1029 ERROR 23000: Column 'col2' cannot be null
1030 INSERT IGNORE INTO t1 values (NULL,NULL);
1032 Warning 1048 Column 'col1' cannot be null
1033 Warning 1048 Column 'col2' cannot be null
1034 INSERT IGNORE INTO t1 (col1) values (3);
1036 Warning 1364 Field 'col2' doesn't have a default value
1037 INSERT IGNORE INTO t1 () values ();
1039 Warning 1364 Field 'col2' doesn't have a default value
1048 set sql_mode='traditional';
1049 create table t1 (charcol char(255), varcharcol varchar(255),
1050 binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
1051 tinyblobcol tinyblob);
1052 insert into t1 (charcol) values (repeat('x',256));
1053 ERROR 22001: Data too long for column 'charcol' at row 1
1054 insert into t1 (varcharcol) values (repeat('x',256));
1055 ERROR 22001: Data too long for column 'varcharcol' at row 1
1056 insert into t1 (binarycol) values (repeat('x',256));
1057 ERROR 22001: Data too long for column 'binarycol' at row 1
1058 insert into t1 (varbinarycol) values (repeat('x',256));
1059 ERROR 22001: Data too long for column 'varbinarycol' at row 1
1060 insert into t1 (tinytextcol) values (repeat('x',256));
1061 ERROR 22001: Data too long for column 'tinytextcol' at row 1
1062 insert into t1 (tinyblobcol) values (repeat('x',256));
1063 ERROR 22001: Data too long for column 'tinyblobcol' at row 1
1065 charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol
1067 set sql_mode='traditional';
1068 create table t1 (col1 datetime);
1069 insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1070 ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1071 insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1072 ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
1073 insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1074 ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date
1075 insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1076 ERROR HY000: Incorrect time value: 'abc' for function str_to_date
1078 insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1080 Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1081 insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1083 Error 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
1084 insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1086 Error 1411 Incorrect time value: '22:22:33 AM' for function str_to_date
1087 insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1089 Error 1411 Incorrect time value: 'abc' for function str_to_date
1090 insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
1091 insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
1092 insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
1102 set sql_mode='traditional';
1103 select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
1107 Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1108 Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1109 Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1111 create table t1 (col1 char(3), col2 integer);
1112 insert into t1 (col1) values (cast(1000 as char(3)));
1113 ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
1114 insert into t1 (col1) values (cast(1000E+0 as char(3)));
1115 ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
1116 insert into t1 (col1) values (cast(1000.0 as char(3)));
1117 ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
1118 insert into t1 (col2) values (cast('abc' as signed integer));
1119 ERROR 22007: Truncated incorrect INTEGER value: 'abc'
1120 insert into t1 (col2) values (10E+0 + 'a');
1121 ERROR 22007: Truncated incorrect DOUBLE value: 'a'
1122 insert into t1 (col2) values (cast('10a' as unsigned integer));
1123 ERROR 22007: Truncated incorrect INTEGER value: '10a'
1124 insert into t1 (col2) values (cast('10' as unsigned integer));
1125 insert into t1 (col2) values (cast('10' as signed integer));
1126 insert into t1 (col2) values (10E+0 + '0 ');
1133 create table t1 (col1 date, col2 datetime, col3 timestamp);
1134 insert into t1 values (0,0,0);
1135 ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1136 insert into t1 values (0.0,0.0,0.0);
1137 ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1138 insert into t1 (col1) values (convert('0000-00-00',date));
1139 ERROR 22007: Incorrect datetime value: '0000-00-00'
1140 insert into t1 (col1) values (cast('0000-00-00' as date));
1141 ERROR 22007: Incorrect datetime value: '0000-00-00'
1142 set sql_mode='no_zero_date';
1143 insert into t1 values (0,0,0);
1145 Warning 1264 Out of range value for column 'col1' at row 1
1146 Warning 1264 Out of range value for column 'col2' at row 1
1147 Warning 1265 Data truncated for column 'col3' at row 1
1148 insert into t1 values (0.0,0.0,0.0);
1150 Warning 1264 Out of range value for column 'col1' at row 1
1151 Warning 1264 Out of range value for column 'col2' at row 1
1152 Warning 1265 Data truncated for column 'col3' at row 1
1154 set sql_mode='traditional';
1155 create table t1 (col1 date);
1156 insert ignore into t1 values ('0000-00-00');
1158 Warning 1265 Data truncated for column 'col1' at row 1
1159 insert into t1 select * from t1;
1160 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1161 insert ignore into t1 values ('0000-00-00');
1163 Warning 1265 Data truncated for column 'col1' at row 1
1164 insert ignore into t1 (col1) values (cast('0000-00-00' as date));
1166 Warning 1292 Incorrect datetime value: '0000-00-00'
1167 insert into t1 select * from t1;
1168 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1169 alter table t1 modify col1 datetime;
1170 ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
1171 alter ignore table t1 modify col1 datetime;
1173 Warning 1264 Out of range value for column 'col1' at row 1
1174 Warning 1264 Out of range value for column 'col1' at row 2
1175 insert into t1 select * from t1;
1176 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
1183 create table t1 (col1 tinyint);
1184 drop procedure if exists t1;
1186 Note 1305 PROCEDURE t1 does not exist
1187 create procedure t1 () begin declare exit handler for sqlexception
1188 select'a'; insert into t1 values (200); end;|
1196 set sql_mode=@org_mode;
1197 SET @@sql_mode = 'traditional';
1198 CREATE TABLE t1 (i int not null);
1199 INSERT INTO t1 VALUES ();
1200 ERROR HY000: Field 'i' doesn't have a default value
1201 INSERT INTO t1 VALUES (DEFAULT);
1202 ERROR HY000: Field 'i' doesn't have a default value
1203 INSERT INTO t1 VALUES (DEFAULT(i));
1204 ERROR HY000: Field 'i' doesn't have a default value
1205 ALTER TABLE t1 ADD j int;
1206 INSERT INTO t1 SET j = 1;
1207 ERROR HY000: Field 'i' doesn't have a default value
1208 INSERT INTO t1 SET j = 1, i = DEFAULT;
1209 ERROR HY000: Field 'i' doesn't have a default value
1210 INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1211 ERROR HY000: Field 'i' doesn't have a default value
1212 INSERT INTO t1 VALUES (DEFAULT,1);
1213 ERROR HY000: Field 'i' doesn't have a default value
1215 SET @@sql_mode = '';
1216 CREATE TABLE t1 (i int not null);
1217 INSERT INTO t1 VALUES ();
1219 Warning 1364 Field 'i' doesn't have a default value
1220 INSERT INTO t1 VALUES (DEFAULT);
1222 Warning 1364 Field 'i' doesn't have a default value
1223 INSERT INTO t1 VALUES (DEFAULT(i));
1224 ERROR HY000: Field 'i' doesn't have a default value
1225 ALTER TABLE t1 ADD j int;
1226 INSERT INTO t1 SET j = 1;
1228 Warning 1364 Field 'i' doesn't have a default value
1229 INSERT INTO t1 SET j = 1, i = DEFAULT;
1231 Warning 1364 Field 'i' doesn't have a default value
1232 INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1233 ERROR HY000: Field 'i' doesn't have a default value
1234 INSERT INTO t1 VALUES (DEFAULT,1);
1236 Warning 1364 Field 'i' doesn't have a default value
1238 set @@sql_mode='traditional';
1239 create table t1(a varchar(65537));
1240 ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1241 create table t1(a varbinary(65537));
1242 ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1243 set @@sql_mode='traditional';
1244 create table t1(a int, b date not null);
1245 alter table t1 modify a bigint unsigned not null;
1246 show create table t1;
1248 t1 CREATE TABLE `t1` (
1249 `a` bigint(20) unsigned NOT NULL,
1251 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1253 set @@sql_mode='traditional';
1254 create table t1 (d date);
1255 insert into t1 values ('2000-10-00');
1256 ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
1257 insert into t1 values (1000);
1258 ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
1259 insert into t1 values ('2000-10-01');
1260 update t1 set d = 1100;
1261 ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
1266 set @@sql_mode='traditional';
1267 create table t1(a int, b timestamp);
1268 alter table t1 add primary key(a);
1269 show create table t1;
1271 t1 CREATE TABLE `t1` (
1272 `a` int(11) NOT NULL DEFAULT '0',
1273 `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1275 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1277 create table t1(a int, b timestamp default 20050102030405);
1278 alter table t1 add primary key(a);
1279 show create table t1;
1281 t1 CREATE TABLE `t1` (
1282 `a` int(11) NOT NULL DEFAULT '0',
1283 `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
1285 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1287 set @@sql_mode='traditional';
1288 create table t1(a bit(2));
1289 insert into t1 values(b'101');
1290 ERROR 22001: Data too long for column 'a' at row 1
1294 set sql_mode='traditional';
1295 create table t1 (date date not null);
1296 create table t2 select date from t1;
1297 show create table t2;
1299 t2 CREATE TABLE `t2` (
1300 `date` date NOT NULL
1301 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1303 set @@sql_mode= @org_mode;
1304 set @@sql_mode='traditional';
1305 create table t1 (i int)
1306 comment '123456789*123456789*123456789*123456789*123456789*
1307 123456789*123456789*123456789*123456789*123456789*';
1308 ERROR HY000: Comment for table 't1' is too long (max = 60)
1311 '123456789*123456789*123456789*123456789*
1312 123456789*123456789*123456789*123456789*
1313 123456789*123456789*123456789*123456789*
1314 123456789*123456789*123456789*123456789*
1315 123456789*123456789*123456789*123456789*
1316 123456789*123456789*123456789*123456789*
1317 123456789*123456789*123456789*123456789*');
1318 ERROR HY000: Comment for field 'i' is too long (max = 255)
1319 set @@sql_mode= @org_mode;
1322 '123456789*123456789*123456789*123456789*
1323 123456789*123456789*123456789*123456789*
1324 123456789*123456789*123456789*123456789*
1325 123456789*123456789*123456789*123456789*
1326 123456789*123456789*123456789*123456789*
1327 123456789*123456789*123456789*123456789*
1328 123456789*123456789*123456789*123456789*');
1330 Warning 1629 Comment for field 'i' is too long (max = 255)
1331 select column_name, column_comment from information_schema.columns where
1332 table_schema = 'test' and table_name = 't1';
1333 column_name column_comment
1334 i 123456789*123456789*123456789*123456789*
1335 123456789*123456789*123456789*123456789*
1336 123456789*123456789*123456789*123456789*
1337 123456789*123456789*123456789*123456789*
1338 123456789*123456789*123456789*123456789*
1339 123456789*123456789*123456789*123456789*
1342 create table t1 (i int)
1343 comment '123456789*123456789*123456789*123456789*123456789*123456789*';
1344 show create table t1;
1346 t1 CREATE TABLE `t1` (
1347 `i` int(11) DEFAULT NULL
1348 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
1350 CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן';
1351 SHOW CREATE TABLE t3;
1353 t3 CREATE TABLE `t3` (
1354 `f1` int(11) DEFAULT NULL
1355 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'
1357 set sql_mode= 'traditional';
1358 create table t1(col1 tinyint, col2 tinyint unsigned,
1359 col3 smallint, col4 smallint unsigned,
1360 col5 mediumint, col6 mediumint unsigned,
1361 col7 int, col8 int unsigned,
1362 col9 bigint, col10 bigint unsigned);
1363 insert into t1(col1) values('-');
1364 ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1
1365 insert into t1(col2) values('+');
1366 ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1
1367 insert into t1(col3) values('-');
1368 ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1
1369 insert into t1(col4) values('+');
1370 ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1
1371 insert into t1(col5) values('-');
1372 ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1
1373 insert into t1(col6) values('+');
1374 ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1
1375 insert into t1(col7) values('-');
1376 ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1
1377 insert into t1(col8) values('+');
1378 ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1
1379 insert into t1(col9) values('-');
1380 ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1
1381 insert into t1(col10) values('+');
1382 ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1
1384 set sql_mode='traditional';
1385 create table t1(a year);
1386 insert into t1 values ('-');
1387 ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1
1388 insert into t1 values ('+');
1389 ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1
1390 insert into t1 values ('');
1391 ERROR HY000: Incorrect integer value: '' for column 'a' at row 1
1392 insert into t1 values ('2000a');
1393 ERROR 01000: Data truncated for column 'a' at row 1
1394 insert into t1 values ('2E3x');
1395 ERROR 01000: Data truncated for column 'a' at row 1
1397 set sql_mode='traditional';
1398 create table t1 (f1 set('a','a'));
1399 ERROR HY000: Column 'f1' has duplicated value 'a' in SET
1400 create table t1 (f1 enum('a','a'));
1401 ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM
1402 set @@sql_mode='NO_ZERO_DATE';
1403 create table t1(a datetime not null);
1404 select count(*) from t1 where a is null;