2
# testing different DATETIME ranges
6
drop table if exists t1;
9
create table t1 (t datetime, pk int auto_increment primary key);
10
insert into t1 (t) values (101);
11
insert into t1 (t) values (691231);
12
insert into t1 (t) values (700101);
13
insert into t1 (t) values (991231);
14
insert into t1 (t) values (10000101);
15
insert into t1 (t) values (99991231);
16
insert into t1 (t) values (101000000);
17
insert into t1 (t) values (691231000000);
18
insert into t1 (t) values (700101000000);
19
insert into t1 (t) values (991231235959);
20
insert into t1 (t) values (10000101000000);
21
insert into t1 (t) values (99991231235959);
22
--error ER_INVALID_DATETIME_VALUE # Bad datetime
23
insert into t1 (t) values (20030100000000);
24
--error ER_INVALID_DATETIME_VALUE # Bad datetime
25
insert into t1 (t) values (20030000000000);
27
--error ER_INVALID_DATETIME_VALUE # Bad datetime
28
delete from t1 where t > 0;
29
# try earliest datetime end point which is actually a friggin datetime, not a 0.
30
delete from t1 where t > "0001-01-01 00:00:00";
31
alter table t1 engine="default";
34
insert into t1 (t) values("000101");
35
insert into t1 (t) values("691231");
36
insert into t1 (t) values("700101");
37
insert into t1 (t) values("991231");
38
--error ER_INVALID_DATETIME_VALUE # Bad datetime
39
insert into t1 (t) values("00000101");
40
insert into t1 (t) values("00010101");
41
insert into t1 (t) values("99991231");
42
--error ER_INVALID_DATETIME_VALUE # Bad datetime
43
insert into t1 (t) values("00101000000");
44
--error ER_INVALID_DATETIME_VALUE # Bad datetime
45
insert into t1 (t) values("691231000000");
46
--error ER_INVALID_DATETIME_VALUE # Bad datetime
47
insert into t1 (t) values("700101000000");
48
--error ER_INVALID_DATETIME_VALUE # Bad datetime
49
insert into t1 (t) values("991231235959");
50
insert into t1 (t) values("10000101000000");
51
insert into t1 (t) values("99991231235959");
52
--error ER_INVALID_DATETIME_VALUE # Bad datetime
53
insert into t1 (t) values("20030100000000");
54
--error ER_INVALID_DATETIME_VALUE # Bad datetime
55
insert into t1 (t) values("20030000000000");
58
--error ER_INVALID_DATETIME_VALUE # Bad datetime
59
insert into t1 (t) values ("2003-003-03");
61
# Bug #7308: ISO-8601 date format not handled correctly
62
--error ER_INVALID_DATETIME_VALUE # Bad datetime
63
insert into t1 (t) values ("20030102T131415");
64
insert into t1 (t) values ("2001-01-01T01:01:01");
65
--error ER_INVALID_DATETIME_VALUE # Bad datetime
66
insert into t1 (t) values ("2001-1-1T1:01:01");
69
# Test some wrong dates
71
--error ER_INVALID_DATETIME_VALUE # Bad datetime
72
insert into t1 (t) values("2003-0303 12:13:14");
77
# Test of datetime and not null
80
CREATE TABLE t1 (a datetime not null primary key);
81
insert into t1 values ("2009-02-11 00:00:00");
82
select * from t1 where a is null;
86
# Test with bug when propagating DATETIME to integer and WHERE optimization
89
create table t1 (id int primary key, dt datetime);
90
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");
91
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');
92
create index dt on t1 (dt);
93
select * from t1 where dt > 20021020;
94
select * from t1 ignore index (dt) where dt > 20021020;
98
# Test of datetime optimization
101
CREATE TEMPORARY TABLE `t1` (
103
`numfacture` int NOT NULL default '0',
104
`expedition` datetime,
105
PRIMARY KEY (`numfacture`),
107
KEY `expedition` (`expedition`)
110
INSERT INTO t1 (expedition) VALUES ('0001-01-01 00:00:00');
111
SELECT * FROM t1 WHERE expedition='0001-01-01 00:00:00';
112
INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-01-01 00:00:00');
113
SELECT * FROM t1 WHERE expedition='0001-01-01 00:00:00';
115
EXPLAIN SELECT * FROM t1 WHERE expedition='0001-01-01 00:00:00';
117
create table t1 (a datetime not null, b datetime not null, pk int auto_increment primary key);
118
insert into t1 (a,b) values (now(), now());
119
insert into t1 (a,b) values (now(), now());
120
select a,b from t1 where a is null or b is null;
124
# Let us check if we properly treat wrong datetimes and produce proper
125
# warnings (for both strings and numbers)
127
create table t1 (t datetime primary key);
128
--error ER_INVALID_DATETIME_VALUE # Bad datetime
129
insert into t1 values (20030102030460),(20030102036301),(20030102240401),
130
(20030132030401),(20031302030401),(100001202030401);
133
--error ER_INVALID_DATETIME_VALUE # Bad datetime
134
insert into t1 values
135
("2003-01-02 03:04:60"),("2003-01-02 03:63:01"),("2003-01-02 24:04:01"),
136
("2003-01-32 03:04:01"),("2003-13-02 03:04:01"), ("10000-12-02 03:04:00");
139
--error ER_INVALID_DATETIME_VALUE # Bad datetime
140
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
141
select * from t1 order by t;
145
# Test for bug #7297 "Two digit year should be interpreted correctly even
146
# with zero month and day"
148
create table t1 (dt datetime primary key);
149
# These dates should be treated as dates in 21st century
150
insert into t1 values ("12-01-01"), ("01-01-01 01:00:00");
151
# Zero dates are still special :/
152
--error ER_INVALID_DATETIME_VALUE # Yeah special uh no its wrong
153
insert into t1 values ("00-00-00"), ("00-00-00 00:00:00");
158
# Bug #16546 DATETIME+0 not always coerced the same way
160
select cast('2006-12-05 22:10:10' as datetime) + 0;
166
# Bug#21475: Wrongly applied constant propagation leads to a false comparison.
168
CREATE TABLE t1(a DATETIME NOT NULL primary key);
169
INSERT INTO t1 VALUES ('20060606155555');
170
SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");
175
# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result
177
SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
178
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
179
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
183
# Test of storing datetime into date fields
186
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03', pk int primary key auto_increment);
187
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
188
show create table t1;
189
insert into t1 values ();
190
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
191
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
192
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
193
# Test error handling
194
--error ER_INVALID_DATE_VALUE # Bad date
195
insert into t1 (da, dt) values ('2007-03-32','2007-03-23 13:49:38');
196
select da,dt from t1;
198
--error ER_INVALID_DATETIME_VALUE # Bad datetime
199
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03', pk int auto_increment primary key);
203
# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
205
create table t1 (f1 date, f2 datetime, f3 timestamp, primary key (f1,f2));
206
insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
207
insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
208
insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
209
insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
210
insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
211
select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
212
select f1, f2, f3 from t1 where f1 between f2 and f3;
213
select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
215
select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
216
select f2, f3 from t1 where '01-03-10' between f2 and f3;
217
select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
221
# Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
223
create table t1 (f1 date primary key);
224
insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
225
select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
226
create table t2(f2 datetime primary key);
227
insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');
228
select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');
229
select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));
230
select * from t1,t2 where '01-01-01' in (f1, '01-02-03');
231
select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);
232
create table t3(f3 varchar(20) primary key);
233
insert into t3 select * from t2;
234
select * from t2,t3 where f2 in (f3,'03-04-05');
235
select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1'));
236
select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));
240
# Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.
242
select least(cast('01-01-01' as date), '01-01-02');
243
select greatest(cast('01-01-01' as date), '01-01-02');
244
select least(cast('01-01-01' as date), '01-01-02') + 0;
245
select greatest(cast('01-01-01' as date), '01-01-02') + 0;
246
select least(cast('01-01-01' as datetime), '01-01-02') + 0;
247
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
250
# Bug#28208: Wrong result of a non-const STRING function with a const
253
create table t1 (f1 date primary key);
254
insert into t1 values (curdate());
255
select left(f1,10) = curdate() from t1;
259
# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
262
create table t1(f1 date, pk int auto_increment primary key);
263
insert into t1 (f1) values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
265
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
266
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
267
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
271
# Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
274
create table t1(f1 datetime primary key);
275
insert into t1 values('2001-01-01'),('2002-02-02');
276
select * from t1 where f1 between 20020101 and 20070101000000;
277
--error ER_INVALID_DATETIME_VALUE # 2002010 is not a datetime.
278
select * from t1 where f1 between 2002010 and 20070101000000;
279
--error ER_INVALID_DATETIME_VALUE # 2007010100000 is not a datetime.
280
select * from t1 where f1 between 20020101 and 2007010100000;
284
# Bug #31249: problem with convert(..., datetime)
286
create table t1 (a int, pk int primary key auto_increment);
287
insert into t1 values (), (), ();
288
select sum(a) from t1 group by convert(a, datetime);
292
# Bug #32694: NOT NULL table field in a subquery produces invalid results
294
create table t1 (id int not null primary key, cur_date datetime not null);
295
create table t2 (id int not null primary key, cur_date date not null);
296
insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
297
insert into t2 (id, cur_date) values (1, '2007-04-25');
302
where id in (select id from t2 as x1 where (t2.cur_date is null));
304
where id in (select id from t2 as x1 where (t2.cur_date is null));
306
insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22');
307
insert into t2 (id, cur_date) values (2, '2007-04-26');
312
where id in (select id from t1 as x1 where (t1.cur_date is null));
314
where id in (select id from t1 as x1 where (t1.cur_date is null));
319
where id in (select id from t2 as x1 where (t2.cur_date is null));
321
where id in (select id from t2 as x1 where (t2.cur_date is null));
324
--echo End of 5.0 tests
326
# Test of storing datetime into date fields
329
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03', pk int auto_increment primary key);
330
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
331
show create table t1;
332
insert into t1 values ();
333
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
334
insert into t1 (da, dt) values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
335
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
336
# Test error handling
337
--error ER_INVALID_DATE_VALUE # Bad date
338
insert into t1 (da, dt) values ('2007-03-32','2007-03-23 13:49:38');
339
select da,dt from t1;
341
--error ER_INVALID_DATETIME_VALUE # Bad date
342
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03' primary key);