« back to all changes in this revision
Viewing changes to tests/r/strict.result
- browse files at revision 784.2.1
- compare with another revision
- compare with revision 2187
- download diff
- download tarball
- view history from revision 784.2.1
- Committer: Stewart Smith
- Date: 2009-01-12 02:31:02 UTC
- mto: (784.1.2 for-brian)
- mto: This revision was merged to the branch mainline in revision 785.
- Revision ID: stewart@flamingspork.com-20090112023102-pjm59eaj0rnhw3lm
fix strict test for drizzle.
move date related things into strict-date.test to be worked on after jay's work has been merged.
move date related things into strict-date.test to be worked on after jay's work has been merged.
- files added:
- tests/suite/broken/t/strict-date.test
- files renamed:
- tests/suite/broken/r/strict.result => tests/r/strict.result
- tests/suite/broken/t/strict.test => tests/t/strict.test
added
removed
tests/r/strict.result
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);
1
CREATE TABLE t1 (col1 INT);
2
INSERT INTO t1 VALUES(-2147483648),(0),(2147483647),('-2147483648'),('2147483647'),(-2147483648.0),(2147483647.0);
585
3
INSERT INTO t1 (col1) VALUES(-2147483649);
586
4
ERROR 22003: Out of range value for column 'col1' at row 1
587
5
INSERT INTO t1 (col1) VALUES(2147643648);
588
6
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
7
INSERT INTO t1 (col1) VALUES('-2147483649');
594
8
ERROR 22003: Out of range value for column 'col1' at row 1
595
9
INSERT INTO t1 (col1) VALUES('2147643648');
596
10
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
11
INSERT INTO t1 (col1) VALUES(-2147483649.0);
602
12
ERROR 22003: Out of range value for column 'col1' at row 1
603
13
INSERT INTO t1 (col1) VALUES(2147643648.0);
604
14
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
15
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
610
16
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
17
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
614
18
ERROR 22012: Division by 0
615
19
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
623
27
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
624
28
Warnings:
625
29
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;
30
INSERT IGNORE INTO t1 values (1/0);
31
Warnings:
32
Error 1365 Division by 0
33
INSERT IGNORE INTO t1 values (-2147483649),(2147643648);
34
Warnings:
35
Warning 1264 Out of range value for column 'col1' at row 1
36
Warning 1264 Out of range value for column 'col1' at row 2
37
INSERT IGNORE INTO t1 values ('-2147483649'),('2147643648');
38
Warnings:
39
Warning 1264 Out of range value for column 'col1' at row 1
40
Warning 1264 Out of range value for column 'col1' at row 2
41
INSERT IGNORE INTO t1 values (-2147483649.0),(2147643648.0);
42
Warnings:
43
Warning 1264 Out of range value for column 'col1' at row 1
44
Warning 1264 Out of range value for column 'col1' at row 2
649
45
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
46
col1
47
-2147483648
48
0
49
2147483647
50
-2147483648
51
2147483647
52
-2147483648
53
2147483647
54
2
55
NULL
56
-2147483648
57
2147483647
58
-2147483648
59
2147483647
60
-2147483648
61
2147483647
666
62
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);
63
CREATE TABLE t1 (col1 BIGINT);
64
INSERT INTO t1 VALUES(-9223372036854775808),(0),(9223372036854775807);
65
INSERT INTO t1 VALUES('-9223372036854775808'),('9223372036854775807');
66
INSERT INTO t1 VALUES(-9223372036854774000.0),(9223372036854775700.0);
671
67
INSERT INTO t1 (col1) VALUES(-9223372036854775809);
672
68
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
69
INSERT INTO t1 (col1) VALUES('-9223372036854775809');
680
70
ERROR 22003: Out of range value for column 'col1' at row 1
681
71
INSERT INTO t1 (col1) VALUES('9223372036854775808');
682
72
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
73
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);
688
74
ERROR 22003: Out of range value for column 'col1' at row 1
689
75
INSERT INTO t1 (col1) VALUES(9223372036854785808.0);
690
76
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
77
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
696
78
ERROR 22012: Division by 0
697
79
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
705
87
INSERT IGNORE INTO t1 (col1) VALUES ('2a');
706
88
Warnings:
707
89
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;
90
INSERT IGNORE INTO t1 values (1/0);
91
Warnings:
92
Error 1365 Division by 0
93
INSERT IGNORE INTO t1 VALUES(-9223372036854775809),(9223372036854775808);
94
Warnings:
95
Warning 1264 Out of range value for column 'col1' at row 1
96
INSERT IGNORE INTO t1 VALUES('-9223372036854775809'),('9223372036854775808');
97
Warnings:
98
Warning 1264 Out of range value for column 'col1' at row 1
99
Warning 1264 Out of range value for column 'col1' at row 2
100
INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0),(9223372036854785808.0);
101
Warnings:
102
Warning 1264 Out of range value for column 'col1' at row 1
103
Warning 1264 Out of range value for column 'col1' at row 2
731
104
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
105
col1
106
-9223372036854775808
107
0
108
9223372036854775807
109
-9223372036854775808
110
9223372036854775807
111
-9223372036854774000
112
9223372036854775700
113
2
114
NULL
115
-9223372036854775808
116
-9223372036854775808
117
-9223372036854775808
118
9223372036854775807
119
-9223372036854775808
120
9223372036854775807
748
121
DROP TABLE t1;
749
122
CREATE TABLE t1 (col1 NUMERIC(4,2));
750
123
INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
832
205
99.99
833
206
-99.99
834
207
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.4e38 3.4e38
877
-1.1e-37 0
878
3.4e38 3.4e38
879
0 NULL
880
2 NULL
881
NULL NULL
882
3.40282e38 0
883
3.40282e38 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.7e308
926
-2.2e-307 0
927
-2e-307 0
928
NULL 1.7e308
929
0 NULL
930
2 NULL
931
NULL NULL
932
1.7976931348623157e308 0
933
DROP TABLE t1;
934
208
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
935
209
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello ');
936
210
Warnings:
1013
287
CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
1014
288
SHOW CREATE TABLE t1;
1015
289
Table Create Table
1016
t1 CREATE TABLE "t1" (
1017
"col1" int(11) NOT NULL DEFAULT '99',
1018
"col2" char(6) NOT NULL
1019
)
290
t1 CREATE TABLE `t1` (
291
`col1` int NOT NULL,
292
`col2` varchar(6) NOT NULL
293
) ENGINE=InnoDB
1020
294
INSERT INTO t1 VALUES (1, 'hello');
1021
295
INSERT INTO t1 (col2) VALUES ('hello2');
1022
296
INSERT INTO t1 (col2) VALUES (NULL);
1034
308
Warning 1048 Column 'col1' cannot be null
1035
309
Warning 1048 Column 'col2' cannot be null
1036
310
INSERT IGNORE INTO t1 (col1) values (3);
1037
Warnings:
1038
Warning 1364 Field 'col2' doesn't have a default value
311
ERROR HY000: Field 'col2' doesn't have a default value
1039
312
INSERT IGNORE INTO t1 () values ();
1040
Warnings:
1041
Warning 1364 Field 'col2' doesn't have a default value
313
ERROR HY000: Field 'col2' doesn't have a default value
1042
314
SELECT * FROM t1;
1043
315
col1 col2
1044
316
1 hello
1045
317
99 hello2
1046
318
0
1047
3
1048
99
1049
319
DROP TABLE t1;
1050
set sql_mode='traditional';
1051
320
create table t1 (charcol char(255), varcharcol varchar(255),
1052
binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
1053
tinyblobcol tinyblob);
321
varbinarycol varbinary(255));
1054
322
insert into t1 (charcol) values (repeat('x',256));
1055
323
ERROR 22001: Data too long for column 'charcol' at row 1
1056
324
insert into t1 (varcharcol) values (repeat('x',256));
1057
325
ERROR 22001: Data too long for column 'varcharcol' at row 1
1058
insert into t1 (binarycol) values (repeat('x',256));
1059
ERROR 22001: Data too long for column 'binarycol' at row 1
1060
326
insert into t1 (varbinarycol) values (repeat('x',256));
1061
327
ERROR 22001: Data too long for column 'varbinarycol' at row 1
1062
insert into t1 (tinytextcol) values (repeat('x',256));
1063
ERROR 22001: Data too long for column 'tinytextcol' at row 1
1064
insert into t1 (tinyblobcol) values (repeat('x',256));
1065
ERROR 22001: Data too long for column 'tinyblobcol' at row 1
1066
328
select * from t1;
1067
charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol
329
charcol varcharcol varbinarycol
1068
330
drop table t1;
1069
set sql_mode='traditional';
1070
331
create table t1 (col1 datetime);
1071
332
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1072
333
ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1076
337
ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date
1077
338
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1078
339
ERROR HY000: Incorrect time value: 'abc' for function str_to_date
1079
set sql_mode='';
1080
340
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
1081
Warnings:
1082
Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc'
341
ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'
1083
342
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
1084
Warnings:
1085
Error 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
343
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date
1086
344
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
1087
Warnings:
1088
Error 1411 Incorrect time value: '22:22:33 AM' for function str_to_date
345
ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date
1089
346
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
1090
Warnings:
1091
Error 1411 Incorrect time value: 'abc' for function str_to_date
347
ERROR HY000: Incorrect time value: 'abc' for function str_to_date
1092
348
insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
1093
349
insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
1094
350
insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
1095
351
select * from t1;
1096
352
col1
1097
353
2004-10-31 15:30:00
1098
NULL
1099
NULL
1100
NULL
1101
2004-10-31 15:30:00
1102
354
2004-12-12 11:22:33
1103
355
2004-12-12 10:22:59
1104
set sql_mode='traditional';
1105
356
select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
1106
357
count(*)
1107
7
358
3
1108
359
Warnings:
1109
360
Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1110
361
Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date
1117
368
ERROR 22007: Truncated incorrect CHAR(3) value: '1000'
1118
369
insert into t1 (col1) values (cast(1000.0 as char(3)));
1119
370
ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'
1120
insert into t1 (col2) values (cast('abc' as signed integer));
1121
ERROR 22007: Truncated incorrect INTEGER value: 'abc'
371
insert into t1 (col2) values (cast('abc' as DECIMAL));
372
ERROR 22007: Truncated incorrect DECIMAL value: 'abc'
1122
373
insert into t1 (col2) values (10E+0 + 'a');
1123
374
ERROR 22007: Truncated incorrect DOUBLE value: 'a'
1124
insert into t1 (col2) values (cast('10a' as unsigned integer));
1125
ERROR 22007: Truncated incorrect INTEGER value: '10a'
1126
insert into t1 (col2) values (cast('10' as unsigned integer));
1127
insert into t1 (col2) values (cast('10' as signed integer));
375
insert into t1 (col2) values ('10a');
376
ERROR 01000: Data truncated for column 'col2' at row 1
377
insert into t1 (col2) values (cast('10a' as DECIMAL));
378
insert into t1 (col2) values (cast('10' as DECIMAL));
379
insert into t1 (col2) values (cast('10' as DECIMAL));
1128
380
insert into t1 (col2) values (10E+0 + '0 ');
1129
381
select * from t1;
1130
382
col1 col2
1131
383
NULL 10
1132
384
NULL 10
1133
385
NULL 10
1134
drop table t1;
1135
create table t1 (col1 date, col2 datetime, col3 timestamp);
1136
insert into t1 values (0,0,0);
1137
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1138
insert into t1 values (0.0,0.0,0.0);
1139
ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
1140
insert into t1 (col1) values (convert('0000-00-00',date));
1141
ERROR 22007: Incorrect datetime value: '0000-00-00'
1142
insert into t1 (col1) values (cast('0000-00-00' as date));
1143
ERROR 22007: Incorrect datetime value: '0000-00-00'
1144
set sql_mode='no_zero_date';
1145
insert into t1 values (0,0,0);
1146
Warnings:
1147
Warning 1264 Out of range value for column 'col1' at row 1
1148
Warning 1264 Out of range value for column 'col2' at row 1
1149
Warning 1265 Data truncated for column 'col3' at row 1
1150
insert into t1 values (0.0,0.0,0.0);
1151
Warnings:
1152
Warning 1264 Out of range value for column 'col1' at row 1
1153
Warning 1264 Out of range value for column 'col2' at row 1
1154
Warning 1265 Data truncated for column 'col3' at row 1
1155
drop table t1;
1156
set sql_mode='traditional';
1157
create table t1 (col1 date);
1158
insert ignore into t1 values ('0000-00-00');
1159
Warnings:
1160
Warning 1265 Data truncated for column 'col1' at row 1
1161
insert into t1 select * from t1;
1162
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1163
insert ignore into t1 values ('0000-00-00');
1164
Warnings:
1165
Warning 1265 Data truncated for column 'col1' at row 1
1166
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
1167
Warnings:
1168
Warning 1292 Incorrect datetime value: '0000-00-00'
1169
insert into t1 select * from t1;
1170
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
1171
alter table t1 modify col1 datetime;
1172
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1
1173
alter ignore table t1 modify col1 datetime;
1174
Warnings:
1175
Warning 1264 Out of range value for column 'col1' at row 1
1176
Warning 1264 Out of range value for column 'col1' at row 2
1177
insert into t1 select * from t1;
1178
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1
1179
select * from t1;
1180
col1
1181
0000-00-00 00:00:00
1182
0000-00-00 00:00:00
1183
NULL
1184
drop table t1;
1185
create table t1 (col1 tinyint);
1186
drop procedure if exists t1;
1187
Warnings:
1188
Note 1305 PROCEDURE t1 does not exist
1189
create procedure t1 () begin declare exit handler for sqlexception
1190
select'a'; insert into t1 values (200); end;|
1191
call t1();
1192
a
1193
a
1194
select * from t1;
1195
col1
1196
drop procedure t1;
1197
drop table t1;
1198
set sql_mode=@org_mode;
1199
SET @@sql_mode = 'traditional';
1200
CREATE TABLE t1 (i int not null);
1201
INSERT INTO t1 VALUES ();
1202
ERROR HY000: Field 'i' doesn't have a default value
1203
INSERT INTO t1 VALUES (DEFAULT);
1204
ERROR HY000: Field 'i' doesn't have a default value
1205
INSERT INTO t1 VALUES (DEFAULT(i));
1206
ERROR HY000: Field 'i' doesn't have a default value
1207
ALTER TABLE t1 ADD j int;
1208
INSERT INTO t1 SET j = 1;
1209
ERROR HY000: Field 'i' doesn't have a default value
1210
INSERT INTO t1 SET j = 1, i = DEFAULT;
1211
ERROR HY000: Field 'i' doesn't have a default value
1212
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1213
ERROR HY000: Field 'i' doesn't have a default value
1214
INSERT INTO t1 VALUES (DEFAULT,1);
1215
ERROR HY000: Field 'i' doesn't have a default value
1216
DROP TABLE t1;
1217
SET @@sql_mode = '';
1218
CREATE TABLE t1 (i int not null);
1219
INSERT INTO t1 VALUES ();
1220
Warnings:
1221
Warning 1364 Field 'i' doesn't have a default value
1222
INSERT INTO t1 VALUES (DEFAULT);
1223
Warnings:
1224
Warning 1364 Field 'i' doesn't have a default value
1225
INSERT INTO t1 VALUES (DEFAULT(i));
1226
ERROR HY000: Field 'i' doesn't have a default value
1227
ALTER TABLE t1 ADD j int;
1228
INSERT INTO t1 SET j = 1;
1229
Warnings:
1230
Warning 1364 Field 'i' doesn't have a default value
1231
INSERT INTO t1 SET j = 1, i = DEFAULT;
1232
Warnings:
1233
Warning 1364 Field 'i' doesn't have a default value
1234
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1235
ERROR HY000: Field 'i' doesn't have a default value
1236
INSERT INTO t1 VALUES (DEFAULT,1);
1237
Warnings:
1238
Warning 1364 Field 'i' doesn't have a default value
1239
DROP TABLE t1;
1240
set @@sql_mode='traditional';
386
NULL 10
387
drop table t1;
388
CREATE TABLE t1 (i int not null);
389
INSERT INTO t1 VALUES ();
390
ERROR HY000: Field 'i' doesn't have a default value
391
INSERT INTO t1 VALUES (DEFAULT);
392
ERROR HY000: Field 'i' doesn't have a default value
393
INSERT INTO t1 VALUES (DEFAULT(i));
394
ERROR HY000: Field 'i' doesn't have a default value
395
ALTER TABLE t1 ADD j int;
396
INSERT INTO t1 SET j = 1;
397
ERROR HY000: Field 'i' doesn't have a default value
398
INSERT INTO t1 SET j = 1, i = DEFAULT;
399
ERROR HY000: Field 'i' doesn't have a default value
400
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
401
ERROR HY000: Field 'i' doesn't have a default value
402
INSERT INTO t1 VALUES (DEFAULT,1);
403
ERROR HY000: Field 'i' doesn't have a default value
404
DROP TABLE t1;
405
CREATE TABLE t1 (i int not null);
406
INSERT INTO t1 VALUES ();
407
ERROR HY000: Field 'i' doesn't have a default value
408
INSERT INTO t1 VALUES (DEFAULT);
409
ERROR HY000: Field 'i' doesn't have a default value
410
INSERT INTO t1 VALUES (DEFAULT(i));
411
ERROR HY000: Field 'i' doesn't have a default value
412
ALTER TABLE t1 ADD j int;
413
INSERT INTO t1 SET j = 1;
414
ERROR HY000: Field 'i' doesn't have a default value
415
INSERT INTO t1 SET j = 1, i = DEFAULT;
416
ERROR HY000: Field 'i' doesn't have a default value
417
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
418
ERROR HY000: Field 'i' doesn't have a default value
419
INSERT INTO t1 VALUES (DEFAULT,1);
420
ERROR HY000: Field 'i' doesn't have a default value
421
DROP TABLE t1;
1241
422
create table t1(a varchar(65537));
1242
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
423
ERROR 42000: Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead
1243
424
create table t1(a varbinary(65537));
1244
425
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
1245
set @@sql_mode='traditional';
1246
426
create table t1(a int, b date not null);
1247
alter table t1 modify a bigint unsigned not null;
427
alter table t1 modify a bigint not null;
1248
428
show create table t1;
1249
429
Table Create Table
1250
430
t1 CREATE TABLE `t1` (
1251
`a` bigint(20) unsigned NOT NULL,
431
`a` bigint NOT NULL,
1252
432
`b` date NOT NULL
1253
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1254
drop table t1;
1255
set @@sql_mode='traditional';
1256
create table t1 (d date);
1257
insert into t1 values ('2000-10-00');
1258
ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
1259
insert into t1 values (1000);
1260
ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
1261
insert into t1 values ('2000-10-01');
1262
update t1 set d = 1100;
1263
ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
1264
select * from t1;
1265
d
1266
2000-10-01
1267
drop table t1;
1268
set @@sql_mode='traditional';
433
) ENGINE=InnoDB
434
drop table t1;
1269
435
create table t1(a int, b timestamp);
1270
436
alter table t1 add primary key(a);
1271
437
show create table t1;
1272
438
Table Create Table
1273
439
t1 CREATE TABLE `t1` (
1274
`a` int(11) NOT NULL DEFAULT '0',
440
`a` int NOT NULL,
1275
441
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1276
442
PRIMARY KEY (`a`)
1277
) ENGINE=MyISAM DEFAULT CHARSET=latin1
443
) ENGINE=InnoDB
1278
444
drop table t1;
1279
445
create table t1(a int, b timestamp default 20050102030405);
1280
446
alter table t1 add primary key(a);
1281
447
show create table t1;
1282
448
Table Create Table
1283
449
t1 CREATE TABLE `t1` (
1284
`a` int(11) NOT NULL DEFAULT '0',
1285
`b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',
450
`a` int NOT NULL,
451
`b` timestamp NOT NULL,
1286
452
PRIMARY KEY (`a`)
1287
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1288
drop table t1;
1289
set @@sql_mode='traditional';
1290
create table t1(a bit(2));
1291
insert into t1 values(b'101');
1292
ERROR 22001: Data too long for column 'a' at row 1
1293
select * from t1;
1294
a
1295
drop table t1;
1296
set sql_mode='traditional';
453
) ENGINE=InnoDB
454
drop table t1;
1297
455
create table t1 (date date not null);
1298
456
create table t2 select date from t1;
1299
457
show create table t2;
1300
458
Table Create Table
1301
459
t2 CREATE TABLE `t2` (
1302
460
`date` date NOT NULL
1303
) ENGINE=MyISAM DEFAULT CHARSET=latin1
461
) ENGINE=InnoDB
1304
462
drop table t2,t1;
1305
set @@sql_mode= @org_mode;
1306
set names utf8;
1307
463
create table t1 (i int)
1308
464
comment '123456789*123456789*123456789*123456789*123456789*123456789*';
1309
465
show create table t1;
1310
466
Table Create Table
1311
467
t1 CREATE TABLE `t1` (
1312
`i` int(11) DEFAULT NULL
1313
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
468
`i` int
469
) ENGINE=InnoDB COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
1314
470
drop table t1;
1315
set sql_mode= 'traditional';
1316
create table t1(col1 tinyint, col2 tinyint unsigned,
1317
col3 smallint, col4 smallint unsigned,
1318
col5 mediumint, col6 mediumint unsigned,
1319
col7 int, col8 int unsigned,
1320
col9 bigint, col10 bigint unsigned);
471
create table t1(col1 int, col2 int,
472
col3 int, col4 int,
473
col7 int, col8 int,
474
col9 bigint, col10 bigint);
1321
475
insert into t1(col1) values('-');
1322
476
ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1
1323
477
insert into t1(col2) values('+');
1326
480
ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1
1327
481
insert into t1(col4) values('+');
1328
482
ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1
1329
insert into t1(col5) values('-');
1330
ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1
1331
insert into t1(col6) values('+');
1332
ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1
1333
483
insert into t1(col7) values('-');
1334
484
ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1
1335
485
insert into t1(col8) values('+');
1339
489
insert into t1(col10) values('+');
1340
490
ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1
1341
491
drop table t1;
1342
set sql_mode='traditional';
1343
create table t1(a year);
1344
insert into t1 values ('-');
1345
ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1
1346
insert into t1 values ('+');
1347
ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1
1348
insert into t1 values ('');
1349
ERROR HY000: Incorrect integer value: '' for column 'a' at row 1
1350
insert into t1 values ('2000a');
1351
ERROR 01000: Data truncated for column 'a' at row 1
1352
insert into t1 values ('2E3x');
1353
ERROR 01000: Data truncated for column 'a' at row 1
1354
drop table t1;
1355
set sql_mode='traditional';
1356
create table t1 (f1 set('a','a'));
1357
ERROR HY000: Column 'f1' has duplicated value 'a' in SET
1358
492
create table t1 (f1 enum('a','a'));
1359
493
ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM
1360
set @@sql_mode='NO_ZERO_DATE';
1361
create table t1(a datetime not null);
1362
select count(*) from t1 where a is null;
1363
count(*)
1364
0
1365
drop table t1;
1366
494
End of 5.0 tests
Loggerhead is a web-based interface for Breezy
Version: 2.0.1