1
-- source include/have_innodb.inc
3
set @org_mode=@@sql_mode;
4
set @@sql_mode='ansi,traditional';
8
DROP TABLE IF EXISTS t1, t2;
11
# Test INSERT with DATE
13
CREATE TABLE t1 (col1 date);
14
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
15
INSERT INTO t1 VALUES('0000-10-31');
17
# All test cases expected to fail should return
18
# SQLSTATE 22007 <invalid date value>
20
INSERT INTO t1 VALUES('2004-0-31');
22
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
24
INSERT INTO t1 VALUES('2004-10-0');
26
INSERT INTO t1 VALUES('2004-09-31');
28
INSERT INTO t1 VALUES('2004-10-32');
30
INSERT INTO t1 VALUES('2003-02-29');
32
INSERT INTO t1 VALUES('2004-13-15');
34
INSERT INTO t1 VALUES('0000-00-00');
35
# Standard says we should return SQLSTATE 22018
37
INSERT INTO t1 VALUES ('59');
39
# Test the different related modes
40
set @@sql_mode='STRICT_ALL_TABLES';
41
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
42
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
44
INSERT INTO t1 VALUES('2004-0-30');
46
INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
47
INSERT INTO t1 VALUES('0000-00-00');
48
INSERT IGNORE INTO t1 VALUES('2004-0-29');
49
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
51
INSERT INTO t1 VALUES('0000-00-00');
52
INSERT IGNORE INTO t1 VALUES('0000-00-00');
53
INSERT INTO t1 VALUES ('2004-0-30');
55
INSERT INTO t1 VALUES ('2004-2-30');
56
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
57
INSERT INTO t1 VALUES ('2004-2-30');
58
set @@sql_mode='ansi,traditional';
59
INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
63
# Test difference in behaviour with InnoDB and MyISAM tables
65
set @@sql_mode='strict_trans_tables';
66
CREATE TABLE t1 (col1 date) engine=myisam;
68
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
69
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
70
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
72
INSERT INTO t1 VALUES ('2003-02-29');
73
INSERT ignore INTO t1 VALUES('2003-02-30');
74
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
75
INSERT ignore INTO t1 VALUES('2003-02-31');
79
set @@sql_mode='strict_trans_tables';
80
CREATE TABLE t1 (col1 date) engine=innodb;
82
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
84
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
85
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
87
INSERT INTO t1 VALUES ('2003-02-29');
88
INSERT ignore INTO t1 VALUES('2003-02-30');
89
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
90
INSERT ignore INTO t1 VALUES('2003-02-31');
95
# Test INSERT with DATETIME
97
CREATE TABLE t1 (col1 datetime);
98
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
99
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
101
# All test cases expected to fail should return
102
# SQLSTATE 22007 <invalid datetime value>
104
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
106
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
108
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
110
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
112
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
114
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
116
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
117
# Standard says we should return SQLSTATE 22018
119
INSERT INTO t1 VALUES ('59');
124
# Test INSERT with TIMESTAMP
126
CREATE TABLE t1 (col1 timestamp);
127
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
129
# All test cases expected to fail should return
130
# SQLSTATE 22007 <invalid datetime value>
131
# Standard says we should return ok, but we can't as this is out of range
133
INSERT INTO t1 VALUES('0000-10-31 15:30:00');
135
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
137
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
139
INSERT INTO t1 VALUES('2004-09-31 15:30:00');
141
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
143
INSERT INTO t1 VALUES('2003-02-29 15:30:00');
145
INSERT INTO t1 VALUES('2004-13-15 15:30:00');
147
INSERT INTO t1 VALUES('2004-02-29 25:30:00');
149
INSERT INTO t1 VALUES('2004-02-29 15:65:00');
151
INSERT INTO t1 VALUES('2004-02-29 15:31:61');
153
INSERT INTO t1 VALUES('0000-00-00 15:30:00');
155
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
156
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
157
# Standard says we should return SQLSTATE 22018
159
INSERT INTO t1 VALUES ('59');
161
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
163
INSERT INTO t1 VALUES('2004-0-31 15:30:00');
165
INSERT INTO t1 VALUES('2004-10-0 15:30:00');
167
INSERT INTO t1 VALUES('2004-10-32 15:30:00');
169
INSERT INTO t1 VALUES('2004-02-30 15:30:04');
170
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
171
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
172
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
173
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
175
INSERT INTO t1 VALUES('0000-00-00 00:00:00');
176
set @@sql_mode='ansi,traditional';
180
#### Test INSERT with STR_TO_DATE into DATE/DATETIME/TIMESTAMP
182
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
184
INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
185
INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
186
INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
188
## Test INSERT with STR_TO_DATE into DATE
189
# All test cases expected to fail should return
190
# SQLSTATE 22007 <invalid date value>
192
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
195
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
197
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
199
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
201
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
203
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
205
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
207
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
209
## Test INSERT with STR_TO_DATE into DATETIME
210
# All test cases expected to fail should return
211
# SQLSTATE 22007 <invalid datetime value>
213
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
216
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
218
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
220
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
222
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
224
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
226
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
228
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
230
## Test INSERT with STR_TO_DATE into TIMESTAMP
231
# All test cases expected to fail should return
232
# SQLSTATE 22007 <invalid datetime value>
235
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
237
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
239
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
241
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
243
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
245
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
247
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
249
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
253
#### Test INSERT with CAST AS DATE/DATETIME into DATE/DATETIME/TIMESTAMP
255
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
257
INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
258
INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
259
INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
262
## Test INSERT with CAST AS DATE into DATE
263
# All test cases expected to fail should return
264
# SQLSTATE 22007 <invalid date value>
266
INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
269
INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
271
INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
273
# deactivated because of Bug#8294
274
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
276
# INSERT INTO t1 (col1) VALUES(CAST('2004-9-31' AS DATE));
278
# INSERT INTO t1 (col1) VALUES(CAST('2004-10-32' AS DATE));
280
# INSERT INTO t1 (col1) VALUES(CAST('2003-02-29' AS DATE));
282
# INSERT INTO t1 (col1) VALUES(CAST('2004-13-15' AS DATE));
284
# deactivated because of Bug#6145
285
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
287
INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
289
## Test INSERT with CAST AS DATETIME into DATETIME
290
# All test cases expected to fail should return
291
# SQLSTATE 22007 <invalid datetime value>
293
INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
296
INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
298
INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
300
# deactivated because of Bug#8294
301
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
303
#INSERT INTO t1 (col2) VALUES(CAST('2004-9-31 15:30' AS DATETIME));
305
#INSERT INTO t1 (col2) VALUES(CAST('2004-10-32 15:30' AS DATETIME));
307
#INSERT INTO t1 (col2) VALUES(CAST('2003-02-29 15:30' AS DATETIME));
309
#INSERT INTO t1 (col2) VALUES(CAST('2004-13-15 15:30' AS DATETIME));
311
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
313
INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));
315
## Test INSERT with CAST AS DATETIME into TIMESTAMP
316
# All test cases expected to fail should return
317
# SQLSTATE 22007 <invalid datetime value>
319
INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
321
# We accept this to be a failure
324
INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
326
INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
327
# should return SQLSTATE 22007 <invalid datetime value>
329
# deactivated because of Bug#8294
330
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
332
#INSERT INTO t1 (col3) VALUES(CAST('2004-9-31 15:30' AS DATETIME));
334
#INSERT INTO t1 (col3) VALUES(CAST('2004-10-32 15:30' AS DATETIME));
336
#INSERT INTO t1 (col3) VALUES(CAST('2003-02-29 15:30' AS DATETIME));
338
#INSERT INTO t1 (col3) VALUES(CAST('2004-13-15 15:30' AS DATETIME));
340
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
342
INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));
347
#### Test INSERT with CONVERT to DATE/DATETIME into DATE/DATETIME/TIMESTAMP
349
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
351
INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));
352
INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
353
INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
356
## Test INSERT with CONVERT to DATE into DATE
357
# All test cases expected to fail should return
358
# SQLSTATE 22007 <invalid date value>
360
INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
363
INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
365
INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
367
# deactivated because of Bug#8294
368
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
370
#INSERT INTO t1 (col1) VALUES(CONVERT('2004-9-31' , DATE));
372
#INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-32' , DATE));
374
#INSERT INTO t1 (col1) VALUES(CONVERT('2003-02-29' , DATE));
376
#INSERT INTO t1 (col1) VALUES(CONVERT('2004-13-15',DATE));
378
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
380
INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
382
## Test INSERT with CONVERT to DATETIME into DATETIME
383
# All test cases expected to fail should return
384
# SQLSTATE 22007 <invalid datetime value>
386
INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
389
INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
391
INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
393
# deactivated because of Bug#8294
394
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
396
#INSERT INTO t1 (col2) VALUES(CONVERT('2004-9-31 15:30',DATETIME));
398
#INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-32 15:30',DATETIME));
400
#INSERT INTO t1 (col2) VALUES(CONVERT('2003-02-29 15:30',DATETIME));
402
#INSERT INTO t1 (col2) VALUES(CONVERT('2004-13-15 15:30',DATETIME));
404
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
406
INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));
408
## Test INSERT with CONVERT to DATETIME into DATETIME
409
# All test cases expected to fail should return
410
# SQLSTATE 22007 <invalid datetime value>
412
INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
414
# We accept this to be a failure
417
INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
419
INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
421
# deactivated because of Bug#8294
422
# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
424
#INSERT INTO t1 (col3) VALUES(CONVERT('2004-9-31 15:30',DATETIME));
426
#INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-32 15:30',DATETIME));
428
#INSERT INTO t1 (col3) VALUES(CONVERT('2003-02-29 15:30',DATETIME));
430
#INSERT INTO t1 (col3) VALUES(CONVERT('2004-13-15 15:30',DATETIME));
432
# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
434
INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));
439
# Zero dates using numbers was not checked properly (Bug #5933 & #6145)
442
create table t1 (col1 date, col2 datetime, col3 timestamp);
444
insert into t1 values (0,0,0);
446
insert into t1 values (0.0,0.0,0.0);
448
insert into t1 (col1) values (convert('0000-00-00',date));
450
insert into t1 (col1) values (cast('0000-00-00' as date));
452
set sql_mode='no_zero_date';
453
insert into t1 values (0,0,0);
454
insert into t1 values (0.0,0.0,0.0);
456
set sql_mode='traditional';
457
create table t1 (col1 date);
458
insert ignore into t1 values ('0000-00-00');
460
insert into t1 select * from t1;
461
insert ignore into t1 values ('0000-00-00');
462
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
464
insert into t1 select * from t1;
466
alter table t1 modify col1 datetime;
467
alter ignore table t1 modify col1 datetime;
469
insert into t1 select * from t1;
474
# Bug #5906: handle invalid date due to conversion
476
create table t1 (d date);
478
insert into t1 values ('2000-10-00');
480
insert into t1 values (1000);
481
insert into t1 values ('2000-10-01');
483
update t1 set d = 1100;
488
# Bug #22824: strict, datetime, NULL, wrong warning
490
set @@sql_mode='NO_ZERO_DATE';
491
create table t1(a datetime not null);
492
select count(*) from t1 where a is null;