1
1
drop table if exists t1;
2
create table t1 (t datetime);
3
insert into t1 values (101);
4
insert into t1 values (691231);
5
insert into t1 values (700101);
6
insert into t1 values (991231);
7
insert into t1 values (10000101);
8
insert into t1 values (99991231);
9
insert into t1 values (101000000);
10
insert into t1 values (691231000000);
11
insert into t1 values (700101000000);
12
insert into t1 values (991231235959);
13
insert into t1 values (10000101000000);
14
insert into t1 values (99991231235959);
15
insert into t1 values (20030100000000);
2
create table t1 (t datetime, pk int auto_increment primary key);
3
insert into t1 (t) values (101);
4
insert into t1 (t) values (691231);
5
insert into t1 (t) values (700101);
6
insert into t1 (t) values (991231);
7
insert into t1 (t) values (10000101);
8
insert into t1 (t) values (99991231);
9
insert into t1 (t) values (101000000);
10
insert into t1 (t) values (691231000000);
11
insert into t1 (t) values (700101000000);
12
insert into t1 (t) values (991231235959);
13
insert into t1 (t) values (10000101000000);
14
insert into t1 (t) values (99991231235959);
15
insert into t1 (t) values (20030100000000);
16
16
ERROR HY000: Received an invalid datetime value '20030100000000'.
17
insert into t1 values (20030000000000);
17
insert into t1 (t) values (20030000000000);
18
18
ERROR HY000: Received an invalid datetime value '20030000000000'.
21
21
2000-01-01 00:00:00
22
22
2069-12-31 00:00:00
38
38
Table Op Msg_type Msg_text
39
39
test.t1 check status OK
41
insert into t1 values("000101");
42
insert into t1 values("691231");
43
insert into t1 values("700101");
44
insert into t1 values("991231");
45
insert into t1 values("00000101");
41
insert into t1 (t) values("000101");
42
insert into t1 (t) values("691231");
43
insert into t1 (t) values("700101");
44
insert into t1 (t) values("991231");
45
insert into t1 (t) values("00000101");
46
46
ERROR HY000: Received an invalid datetime value '00000101'.
47
insert into t1 values("00010101");
48
insert into t1 values("99991231");
49
insert into t1 values("00101000000");
47
insert into t1 (t) values("00010101");
48
insert into t1 (t) values("99991231");
49
insert into t1 (t) values("00101000000");
50
50
ERROR HY000: Received an invalid datetime value '00101000000'.
51
insert into t1 values("691231000000");
51
insert into t1 (t) values("691231000000");
52
52
ERROR HY000: Received an invalid datetime value '691231000000'.
53
insert into t1 values("700101000000");
53
insert into t1 (t) values("700101000000");
54
54
ERROR HY000: Received an invalid datetime value '700101000000'.
55
insert into t1 values("991231235959");
55
insert into t1 (t) values("991231235959");
56
56
ERROR HY000: Received an invalid datetime value '991231235959'.
57
insert into t1 values("10000101000000");
58
insert into t1 values("99991231235959");
59
insert into t1 values("20030100000000");
57
insert into t1 (t) values("10000101000000");
58
insert into t1 (t) values("99991231235959");
59
insert into t1 (t) values("20030100000000");
60
60
ERROR HY000: Received an invalid datetime value '20030100000000'.
61
insert into t1 values("20030000000000");
61
insert into t1 (t) values("20030000000000");
62
62
ERROR HY000: Received an invalid datetime value '20030000000000'.
63
insert into t1 values ("2003-003-03");
63
insert into t1 (t) values ("2003-003-03");
64
64
ERROR HY000: Received an invalid datetime value '2003-003-03'.
65
insert into t1 values ("20030102T131415");
65
insert into t1 (t) values ("20030102T131415");
66
66
ERROR HY000: Received an invalid datetime value '20030102T131415'.
67
insert into t1 values ("2001-01-01T01:01:01");
68
insert into t1 values ("2001-1-1T1:01:01");
67
insert into t1 (t) values ("2001-01-01T01:01:01");
68
insert into t1 (t) values ("2001-1-1T1:01:01");
69
69
ERROR HY000: Received an invalid datetime value '2001-1-1T1:01:01'.
72
72
2000-01-01 00:00:00
73
73
2069-12-31 00:00:00
79
79
9999-12-31 23:59:59
80
80
2001-01-01 01:01:01
82
insert into t1 values("2003-0303 12:13:14");
82
insert into t1 (t) values("2003-0303 12:13:14");
83
83
ERROR HY000: Received an invalid datetime value '2003-0303 12:13:14'.
87
CREATE TABLE t1 (a datetime not null);
87
CREATE TABLE t1 (a datetime not null primary key);
88
88
insert into t1 values ("2009-02-11 00:00:00");
89
89
select * from t1 where a is null;
92
create table t1 (id int, dt datetime);
92
create table t1 (id int primary key, dt datetime);
93
93
insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30");
94
94
select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15');
123
123
id select_type table type possible_keys key key_len ref rows Extra
124
124
1 SIMPLE t1 ref expedition expedition 9 const 1 Using where
126
create table t1 (a datetime not null, b datetime not null);
127
insert into t1 values (now(), now());
128
insert into t1 values (now(), now());
129
select * from t1 where a is null or b is null;
126
create table t1 (a datetime not null, b datetime not null, pk int auto_increment primary key);
127
insert into t1 (a,b) values (now(), now());
128
insert into t1 (a,b) values (now(), now());
129
select a,b from t1 where a is null or b is null;
132
create table t1 (t datetime);
132
create table t1 (t datetime primary key);
133
133
insert into t1 values (20030102030460),(20030102036301),(20030102240401),
134
134
(20030132030401),(20031302030401),(100001202030401);
135
135
ERROR HY000: Received an invalid datetime value '20030102036301'.
148
148
select * from t1 order by t;
151
create table t1 (dt datetime);
151
create table t1 (dt datetime primary key);
152
152
insert into t1 values ("12-01-01"), ("01-01-01 01:00:00");
153
153
insert into t1 values ("00-00-00"), ("00-00-00 00:00:00");
154
154
ERROR HY000: Received an invalid datetime value '00-00-00'.
155
155
select * from t1;
157
158
2012-01-01 00:00:00
160
160
select cast('2006-12-05 22:10:10' as datetime) + 0;
161
161
cast('2006-12-05 22:10:10' as datetime) + 0
162
162
20061205221010.000000
163
CREATE TABLE t1(a DATETIME NOT NULL);
163
CREATE TABLE t1(a DATETIME NOT NULL primary key);
164
164
INSERT INTO t1 VALUES ('20060606155555');
165
165
SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");
175
175
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
176
176
CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6))
177
177
20060810101112.000014
178
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
178
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03', pk int primary key auto_increment);
179
179
show create table t1;
180
180
Table Create Table
181
181
t1 CREATE TABLE `t1` (
182
182
`da` date DEFAULT '1962-03-03',
183
`dt` datetime DEFAULT '1962-03-03 00:00:00'
183
`dt` datetime DEFAULT '1962-03-03 00:00:00',
184
`pk` int NOT NULL AUTO_INCREMENT,
185
PRIMARY KEY (`pk`) USING BTREE
185
187
insert into t1 values ();
186
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
187
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
188
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
189
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
188
190
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
189
insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
191
insert into t1 (da, dt) values ('2007-03-32','2007-03-23 13:49:38');
190
192
ERROR HY000: Received an invalid datetime value '2007-03-32'.
193
select da,dt from t1;
193
195
1962-03-03 1962-03-03 00:00:00
194
196
2007-03-23 2007-03-23 13:49:38
195
197
2007-03-23 2007-03-23 13:49:38
196
198
2007-03-23 2007-03-23 13:49:38
198
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
200
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03', pk int auto_increment primary key);
199
201
ERROR HY000: Received an invalid datetime value '1962-03-32 23:33:34'.
200
create table t1 (f1 date, f2 datetime, f3 timestamp);
202
create table t1 (f1 date, f2 datetime, f3 timestamp, primary key (f1,f2));
201
203
insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
202
204
insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
203
205
insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
231
233
2001-04-15 00:00:00
233
create table t1 (f1 date);
235
create table t1 (f1 date primary key);
234
236
insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
235
237
select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
240
create table t2(f2 datetime);
242
create table t2(f2 datetime primary key);
241
243
insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');
242
244
select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');
294
296
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
295
297
cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2))
296
298
20010101000000.00
297
create table t1 (f1 date);
299
create table t1 (f1 date primary key);
298
300
insert into t1 values (curdate());
299
301
select left(f1,10) = curdate() from t1;
300
302
left(f1,10) = curdate()
303
create table t1(f1 date);
304
insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
305
create table t1(f1 date, pk int auto_increment primary key);
306
insert into t1 (f1) values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
305
307
set @bug28261='';
306
308
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
307
309
if(@bug28261 = f1, '', @bug28261:= f1)
334
336
select * from t1 where f1 between 20020101 and 2007010100000;
335
337
ERROR HY000: Received an invalid datetime value '2007010100000'.
338
# Bug#27216: functions with parameters of different date types may
339
# return wrong type of the result.
341
create table t1 (f1 date, f2 datetime, f3 varchar(20));
342
create table t2 as select coalesce(f1,f1) as f4 from t1;
344
Field Type Null Default Default_is_NULL On_Update
346
create table t3 as select coalesce(f1,f2) as f4 from t1;
348
Field Type Null Default Default_is_NULL On_Update
349
f4 DATETIME TRUE TRUE
350
create table t4 as select coalesce(f2,f2) as f4 from t1;
352
Field Type Null Default Default_is_NULL On_Update
353
f4 DATETIME TRUE TRUE
354
create table t5 as select coalesce(f1,f3) as f4 from t1;
356
Field Type Null Default Default_is_NULL On_Update
358
create table t6 as select coalesce(f2,f3) as f4 from t1;
360
Field Type Null Default Default_is_NULL On_Update
362
create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
364
Field Type Null Default Default_is_NULL On_Update
365
f4 DATETIME TRUE TRUE
366
create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
369
Field Type Null Default Default_is_NULL On_Update
370
f4 DATETIME TRUE TRUE
371
create table t9 as select case when 1 then cast('01-01-01' as date)
372
when 0 then cast('01-01-01' as date) end as f4 from t1;
374
Field Type Null Default Default_is_NULL On_Update
376
create table t10 as select case when 1 then cast('01-01-01' as datetime)
377
when 0 then cast('01-01-01' as datetime) end as f4 from t1;
379
Field Type Null Default Default_is_NULL On_Update
380
f4 DATETIME TRUE TRUE
381
create table t11 as select if(1, cast('01-01-01' as datetime),
382
cast('01-01-01' as date)) as f4 from t1;
384
Field Type Null Default Default_is_NULL On_Update
386
create table t12 as select least(cast('01-01-01' as datetime),
387
cast('01-01-01' as date)) as f4 from t1;
389
Field Type Null Default Default_is_NULL On_Update
391
create table t13 as select ifnull(cast('01-01-01' as datetime),
392
cast('01-01-01' as date)) as f4 from t1;
394
Field Type Null Default Default_is_NULL On_Update
396
drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
397
###################################################################
398
create table t1 (a int);
339
create table t1 (a int, pk int primary key auto_increment);
399
340
insert into t1 values (), (), ();
400
341
select sum(a) from t1 group by convert(a, datetime);
404
create table t1 (id int not null, cur_date datetime not null);
405
create table t2 (id int not null, cur_date date not null);
345
create table t1 (id int not null primary key, cur_date datetime not null);
346
create table t2 (id int not null primary key, cur_date date not null);
406
347
insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
407
348
insert into t2 (id, cur_date) values (1, '2007-04-25');
410
351
where id in (select id from t2 as x1 where (t2.cur_date is null));
411
352
id select_type table type possible_keys key key_len ref rows filtered Extra
412
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where
353
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
413
354
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
415
356
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
446
387
drop table t1,t2;
448
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
389
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03', pk int auto_increment primary key);
449
390
show create table t1;
450
391
Table Create Table
451
392
t1 CREATE TABLE `t1` (
452
393
`da` date DEFAULT '1962-03-03',
453
`dt` datetime DEFAULT '1962-03-03 00:00:00'
394
`dt` datetime DEFAULT '1962-03-03 00:00:00',
395
`pk` int NOT NULL AUTO_INCREMENT,
396
PRIMARY KEY (`pk`) USING BTREE
455
398
insert into t1 values ();
456
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
457
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
399
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
400
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
458
401
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
459
insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
402
insert into t1 (da, dt) values ('2007-03-32','2007-03-23 13:49:38');
460
403
ERROR HY000: Received an invalid datetime value '2007-03-32'.
404
select da,dt from t1;
463
406
1962-03-03 1962-03-03 00:00:00
464
407
2007-03-23 2007-03-23 13:49:38
465
408
2007-03-23 2007-03-23 13:49:38
466
409
2007-03-23 2007-03-23 13:49:38
468
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
411
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03' primary key);
469
412
ERROR HY000: Received an invalid datetime value '1962-03-32 23:33:34'.