| blob | 241f4198bf76e1f96f8f31adab0694c349c890f2 |
1 set @org_mode=@@sql_mode;
2 set @@sql_mode='ansi,traditional';
3 select @@sql_mode;
4 @@sql_mode
5 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
6 DROP TABLE IF EXISTS t1, t2;
7 CREATE TABLE t1 (col1 date);
8 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');
37 Warnings:
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');
43 Warnings:
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');
52 Warnings:
53 Warning 1265 Data truncated for column 'col1' at row 2
54 Warning 1265 Data truncated for column 'col1' at row 3
55 select * from t1;
56 col1
57 2004-01-01
58 2004-02-29
59 0000-10-31
60 2004-01-02
61 2004-01-03
62 2004-00-31
63 2004-01-04
64 0000-00-00
65 0000-00-00
66 0000-00-00
67 2004-00-30
68 2004-02-30
69 2004-02-29
70 0000-00-00
71 0000-00-00
72 drop table t1;
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');
78 Warnings:
79 Warning 1265 Data truncated for column 'col1' at row 2
80 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
81 Warnings:
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');
86 Warnings:
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');
90 select * from t1;
91 col1
92 2004-01-02
93 0000-00-00
94 2004-01-03
95 0000-00-00
96 2004-01-04
97 0000-00-00
98 2003-02-31
99 drop table t1;
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');
107 Warnings:
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');
112 Warnings:
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');
116 select * from t1;
117 col1
118 0000-00-00
119 2004-01-04
120 0000-00-00
121 2003-02-31
122 drop table t1;
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
143 select * from t1;
144 col1
145 2004-10-31 15:30:00
146 2004-02-29 15:30:00
147 0000-10-31 15:30:00
148 drop table t1;
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');
176 Warnings:
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';
196 SELECT * FROM t1;
197 col1
198 2004-10-31 15:30:00
199 2004-02-29 15:30:00
200 0000-00-00 00:00:00
201 0000-00-00 00:00:00
202 0000-00-00 00:00:00
203 DROP TABLE t1;
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'));
209 Warnings:
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
256 drop table t1;
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'
283 drop table t1;
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'
310 drop table t1;
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;
314 MOD(col1,0)
315 NULL
316 NULL
317 Warnings:
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;
343 MOD(col1,0)
344 NULL
345 Warnings:
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);
355 Warnings:
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;
360 MOD(col1,0)
361 NULL
362 NULL
363 Warnings:
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');
373 Warnings:
374 Warning 1265 Data truncated for column 'col1' at row 1
375 INSERT IGNORE INTO t1 values (1/0,1/0);
376 Warnings:
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');
383 Warnings:
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);
389 Warnings:
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;
395 SELECT * FROM t1;
396 col1 col2
397 -128 0
398 0 NULL
399 127 255
400 -128 0
401 0 NULL
402 127 255
403 -128 0
404 0 NULL
405 127 255
406 NULL NULL
407 2 NULL
408 NULL NULL
409 NULL NULL
410 -128 0
411 127 255
412 -128 0
413 127 255
414 DROP TABLE t1;
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');
456 Warnings:
457 Warning 1265 Data truncated for column 'col1' at row 1
458 INSERT IGNORE INTO t1 values (1/0,1/0);
459 Warnings:
460 Error 1365 Division by 0
461 Error 1365 Division by 0
462 INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
463 Warnings:
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');
469 Warnings:
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);
475 Warnings:
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;
481 SELECT * FROM t1;
482 col1 col2
483 -32768 0
484 0 NULL
485 32767 65535
486 -32768 0
487 32767 65535
488 -32768 0
489 32767 65535
490 2 NULL
491 NULL NULL
492 -32768 0
493 32767 65535
494 -32768 0
495 32767 65535
496 -32768 0
497 32767 65535
498 DROP TABLE t1;
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');
540 Warnings:
541 Warning 1265 Data truncated for column 'col1' at row 1
542 INSERT IGNORE INTO t1 values (1/0,1/0);
543 Warnings:
544 Error 1365 Division by 0
545 Error 1365 Division by 0
546 INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
547 Warnings:
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');
553 Warnings:
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);
559 Warnings:
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;
565 SELECT * FROM t1;
566 col1 col2
567 -8388608 0
568 0 NULL
569 8388607 16777215
570 -8388608 0
571 8388607 16777215
572 -8388608 0
573 8388607 16777215
574 2 NULL
575 NULL NULL
576 -8388608 0
577 8388607 16777215
578 -8388608 0
579 8388607 16777215
580 -8388608 0
581 8388607 16777215
582 DROP TABLE t1;
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');
624 Warnings:
625 Warning 1265 Data truncated for column 'col1' at row 1
626 INSERT IGNORE INTO t1 values (1/0,1/0);
627 Warnings:
628 Error 1365 Division by 0
629 Error 1365 Division by 0
630 INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
631 Warnings:
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');
637 Warnings:
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);
643 Warnings:
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;
649 SELECT * FROM t1;
650 col1 col2
651 -2147483648 0
652 0 NULL
653 2147483647 4294967295
654 -2147483648 0
655 2147483647 4294967295
656 -2147483648 0
657 2147483647 4294967295
658 2 NULL
659 NULL NULL
660 -2147483648 0
661 2147483647 4294967295
662 -2147483648 0
663 2147483647 4294967295
664 -2147483648 0
665 2147483647 4294967295
666 DROP TABLE t1;
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');
706 Warnings:
707 Warning 1265 Data truncated for column 'col1' at row 1
708 INSERT IGNORE INTO t1 values (1/0,1/0);
709 Warnings:
710 Error 1365 Division by 0
711 Error 1365 Division by 0
712 INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
713 Warnings:
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');
719 Warnings:
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);
725 Warnings:
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;
731 SELECT * FROM t1;
732 col1 col2
733 -9223372036854775808 0
734 0 NULL
735 9223372036854775807 18446744073709551615
736 -9223372036854775808 0
737 9223372036854775807 18446744073709551615
738 -9223372036854774000 0
739 9223372036854775700 1844674407370954000
740 2 NULL
741 NULL NULL
742 -9223372036854775808 0
743 9223372036854775807 18446744073709551615
744 -9223372036854775808 0
745 9223372036854775807 18446744073709551615
746 -9223372036854775808 0
747 9223372036854775807 18446744073709551615
748 DROP TABLE t1;
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);
751 Warnings:
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');
755 Warnings:
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');
793 Warnings:
794 Note 1265 Data truncated for column 'col1' at row 1
795 INSERT IGNORE INTO t1 values (1/0);
796 Warnings:
797 Error 1365 Division by 0
798 INSERT IGNORE INTO t1 VALUES(1000),(-1000);
799 Warnings:
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');
803 Warnings:
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);
807 Warnings:
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;
811 SELECT * FROM t1;
812 col1
813 10.55
814 10.56
815 NULL
816 -10.55
817 -10.56
818 11.00
819 10.00
820 10.55
821 10.56
822 -10.55
823 -10.56
824 11.00
825 10.00
826 2.00
827 NULL
828 99.99
829 -99.99
830 99.99
831 -99.99
832 99.99
833 -99.99
834 DROP TABLE t1;
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');
860 Warnings:
861 Warning 1265 Data truncated for column 'col1' at row 1
862 INSERT IGNORE INTO t1 (col1) VALUES (1/0);
863 Warnings:
864 Error 1365 Division by 0
865 INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
866 Warnings:
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');
870 Warnings:
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
873 SELECT * FROM t1;
874 col1 col2
875 -1.1e-37 0
876 3.4e+38 3.4e+38
877 -1.1e-37 0
878 3.4e+38 3.4e+38
879 0 NULL
880 2 NULL
881 NULL NULL
882 3.40282e+38 0
883 3.40282e+38 0
884 DROP TABLE t1;
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');
909 Warnings:
910 Warning 1265 Data truncated for column 'col1' at row 1
911 INSERT IGNORE INTO t1 (col1) values (1/0);
912 Warnings:
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');
917 Warnings:
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
921 SELECT * FROM t1;
922 col1 col2
923 -2.2e-307 0
924 1e-303 0
925 NULL 1.7e+308
926 -2.2e-307 0
927 -2e-307 0
928 NULL 1.7e+308
929 0 NULL
930 2 NULL
931 NULL NULL
932 1.79769313486232e+308 0
933 DROP TABLE t1;
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 ');
941 Warnings:
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');
948 Warnings:
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';
952 Warnings:
953 Warning 1265 Data truncated for column 'col2' at row 2
954 SELECT * FROM t1;
955 col1 col2
956 hello hello
957 he hellot
958 hello hello
959 NULL hello
960 hello hellob
961 DROP TABLE t1;
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');
973 Warnings:
974 Warning 1265 Data truncated for column 'col1' at row 1
975 UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
976 Warnings:
977 Warning 1265 Data truncated for column 'col1' at row 2
978 SELECT * FROM t1;
979 col1
980 red
982 green
984 DROP TABLE t1;
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);
1001 Warnings:
1002 Warning 1048 Column 'col1' cannot be null
1003 Warning 1048 Column 'col2' cannot be null
1004 Warning 1048 Column 'col3' cannot be null
1005 SELECT * FROM t1;
1006 col1 col2 col3
1007 100 hello 2004-08-20
1008 101 hell2 2004-08-21
1009 0 0000-00-00
1010 DROP TABLE t1;
1011 CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
1012 SHOW CREATE TABLE t1;
1013 Table Create Table
1014 t1 CREATE TABLE "t1" (
1015 "col1" int(11) NOT NULL DEFAULT '99',
1016 "col2" char(6) NOT NULL
1017 )
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);
1031 Warnings:
1032 Warning 1048 Column 'col1' cannot be null
1033 Warning 1048 Column 'col2' cannot be null
1034 INSERT IGNORE INTO t1 (col1) values (3);
1035 Warnings:
1036 Warning 1364 Field 'col2' doesn't have a default value
1037 INSERT IGNORE INTO t1 () values ();
1038 Warnings:
1039 Warning 1364 Field 'col2' doesn't have a default value
1040 SELECT * FROM t1;
1041 col1 col2
1042 1 hello
1043 99 hello2
1044 0
1045 3
1046 99
1047 DROP TABLE t1;
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
1064 select * from t1;
1065 charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol
1066 drop table t1;
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
1077 set sql_mode='';
1078 insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1079 Warnings:
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'));
1082 Warnings:
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'));
1085 Warnings:
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'));
1088 Warnings:
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'));
1093 select * from t1;
1094 col1
1095 2004-10-31 15:30:00
1096 NULL
1097 NULL
1098 NULL
1099 2004-10-31 15:30:00
1100 2004-12-12 11:22:33
1101 2004-12-12 10:22:59
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;
1104 count(*)
1105 7
1106 Warnings:
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
1110 drop table t1;
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 ');
1127 select * from t1;
1128 col1 col2
1129 NULL 10
1130 NULL 10
1131 NULL 10
1132 drop table t1;
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);
1144 Warnings:
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);
1149 Warnings:
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
1153 drop table t1;
1154 set sql_mode='traditional';
1155 create table t1 (col1 date);
1156 insert ignore into t1 values ('0000-00-00');
1157 Warnings:
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');
1162 Warnings:
1163 Warning 1265 Data truncated for column 'col1' at row 1
1164 insert ignore into t1 (col1) values (cast('0000-00-00' as date));
1165 Warnings:
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;
1172 Warnings:
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
1177 select * from t1;
1178 col1
1179 0000-00-00 00:00:00
1180 0000-00-00 00:00:00
1181 NULL
1182 drop table t1;
1183 create table t1 (col1 tinyint);
1184 drop procedure if exists t1;
1185 Warnings:
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;|
1189 call t1();
1190 a
1191 a
1192 select * from t1;
1193 col1
1194 drop procedure t1;
1195 drop table t1;
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
1214 DROP TABLE t1;
1215 SET @@sql_mode = '';
1216 CREATE TABLE t1 (i int not null);
1217 INSERT INTO t1 VALUES ();
1218 Warnings:
1219 Warning 1364 Field 'i' doesn't have a default value
1220 INSERT INTO t1 VALUES (DEFAULT);
1221 Warnings:
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;
1227 Warnings:
1228 Warning 1364 Field 'i' doesn't have a default value
1229 INSERT INTO t1 SET j = 1, i = DEFAULT;
1230 Warnings:
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);
1235 Warnings:
1236 Warning 1364 Field 'i' doesn't have a default value
1237 DROP TABLE t1;
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;
1247 Table Create Table
1248 t1 CREATE TABLE `t1` (
1249 `a` bigint(20) unsigned NOT NULL,
1250 `b` date NOT NULL
1251 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1252 drop table t1;
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
1262 select * from t1;
1263 d
1264 2000-10-01
1265 drop table t1;
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;
1270 Table Create Table
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,
1274 PRIMARY KEY (`a`)
1275 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1276 drop table t1;
1277 create table t1(a int, b timestamp default 20050102030405);
1278 alter table t1 add primary key(a);
1279 show create table t1;
1280 Table Create Table
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',
1284 PRIMARY KEY (`a`)
1285 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1286 drop table t1;
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
1291 select * from t1;
1292 a
1293 drop table t1;
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;
1298 Table Create Table
1299 t2 CREATE TABLE `t2` (
1300 `date` date NOT NULL
1301 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1302 drop table t2,t1;
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)
1309 create table t1 (
1310 i int comment
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;
1320 create table t1
1321 (i int comment
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*');
1329 Warnings:
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*
1340 drop table t1;
1341 set names utf8;
1342 create table t1 (i int)
1343 comment '123456789*123456789*123456789*123456789*123456789*123456789*';
1344 show create table t1;
1345 Table Create Table
1346 t1 CREATE TABLE `t1` (
1347 `i` int(11) DEFAULT NULL
1348 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
1349 drop table t1;
1350 CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן';
1351 SHOW CREATE TABLE t3;
1352 Table Create Table
1353 t3 CREATE TABLE `t3` (
1354 `f1` int(11) DEFAULT NULL
1355 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'
1356 DROP TABLE t3;
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
1383 drop table t1;
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
1396 drop table t1;
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;
1405 count(*)
1406 0
1407 drop table t1;
1408 End of 5.0 tests