1#
2# time functions
3#
4--disable_warnings
5drop table if exists t1,t2,t3;
6--enable_warnings
7
8# Set timezone to GMT-3, to make it possible to use "interval 3 hour"
9set time_zone="+03:00";
10
11select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");
12select period_add("9602",-12),period_diff(199505,"9404") ;
13
14select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
15select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
16select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
17  sec_to_time(time_to_sec("0:30:47")/6.21);
18select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899);
19select sec_to_time(time_to_sec('-838:59:59'));
20select sec_to_time('9001.1'), sec_to_time('1234567890123.123');
21select sec_to_time(-9001.1), sec_to_time(-9001.1) / 1,
22       sec_to_time(-9001.1) / 1e0, sec_to_time(-9001) div 1;
23--replace_result e+042 e+42
24select sec_to_time(90011e-1), sec_to_time(1234567890123e30);
25select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999');
26select now()-curdate()*1000000-curtime();
27select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
28select strcmp(localtime(),concat(current_date()," ",current_time()));
29select strcmp(localtimestamp(),concat(current_date()," ",current_time()));
30select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
31select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
32select dayofmonth("1997-01-02"),dayofmonth(19970323);
33select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
34select month("2001-02-00"),year("2001-00-00");
35select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);
36select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
37select TIME(230322), TIME(230322.33), TIME("230322.33");
38
39# Test of week and yearweek
40select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
41select week(19981231),week(19971231),week(19981231,1),week(19971231,1);
42select week(19950101),week(19950101,1);
43select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);
44select yearweek('1987-01-01',1),yearweek('1987-01-01');
45select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006';
46select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006';
47select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006';
48select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006';
49select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006';
50select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006';
51select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006';
52select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006';
53select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
54select week(20001231,2),week(20001231,3);
55
56select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7';
57select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7';
58select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7';
59select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7';
60select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7';
61
62select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7);
63
64set default_week_format = 6;
65select week(20001231), week(20001231,6);
66set default_week_format = 0;
67
68set default_week_format = 2;
69select week(20001231),week(20001231,2),week(20001231,0);
70set default_week_format = 0;
71
72select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
73select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
74
75select dayname("1962-03-03"),dayname("1962-03-03")+0;
76select monthname("1972-03-04"),monthname("1972-03-04")+0;
77select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
78select time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
79select time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
80select time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
81select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
82select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
83select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
84select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
85select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
86select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
87select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
88select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
89select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
90select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
91select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
92select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
93select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
94select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
95select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);
96
97select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
98select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
99select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
100select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
101select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
102select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
103select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
104select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
105select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
106select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
107select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
108select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
109select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
110
111select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
112select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
113select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
114select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
115select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
116select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
117select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
118select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
119select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
120select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
121select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
122select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
123select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
124select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
125select INTERVAL 1 DAY + "1997-12-31";
126select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;
127
128select date_sub("1998-01-02",INTERVAL 31 DAY);
129select date_add("1997-12-31",INTERVAL 1 SECOND);
130select date_add("1997-12-31",INTERVAL 1 DAY);
131select date_add(NULL,INTERVAL 100000 SECOND);
132select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
133select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
134select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
135select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
136select date_add('1998-01-30',Interval 1 month);
137select date_add('1998-01-30',Interval '2:1' year_month);
138select date_add('1996-02-29',Interval '1' year);
139select extract(YEAR FROM "1999-01-02 10:11:12");
140select extract(YEAR_MONTH FROM "1999-01-02");
141select extract(DAY FROM "1999-01-02");
142select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
143select extract(DAY_MINUTE FROM "02 10:11:12");
144select extract(DAY_SECOND FROM "225 10:11:12");
145select extract(HOUR FROM "1999-01-02 10:11:12");
146select extract(HOUR_MINUTE FROM "10:11:12");
147select extract(HOUR_SECOND FROM "10:11:12");
148select extract(MINUTE FROM "10:11:12");
149select extract(MINUTE_SECOND FROM "10:11:12");
150select extract(SECOND FROM "1999-01-02 10:11:12");
151select extract(MONTH FROM "2001-02-00");
152
153#
154# test EXTRACT QUARTER (Bug #18100)
155#
156
157SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
158SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;
159SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;
160SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;
161SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;
162SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;
163SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;
164SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;
165SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;
166SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;
167SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;
168SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;
169#
170# MySQL Bugs: #12356: DATE_SUB or DATE_ADD incorrectly returns null
171#
172SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
173SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
174
175#
176# Test big intervals (Bug #3498)
177#
178SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;
179SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;
180SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
181SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
182SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR;
183
184SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
185SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
186SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
187SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
188
189#
190# Bug #614 (multiple extracts in where)
191#
192
193create table t1 (ctime varchar(20));
194insert into t1 values ('2001-01-12 12:23:40');
195select ctime, hour(ctime) from t1;
196select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
197drop table t1;
198
199#
200# Test bug with monthname() and NULL
201#
202
203create table t1 (id int);
204create table t2 (id int, date date);
205insert into t1 values (1);
206insert into t2 values (1, "0000-00-00");
207insert into t1 values (2);
208insert into t2 values (2, "2000-01-01");
209select monthname(date) from t1 inner join t2 on t1.id = t2.id;
210select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
211drop table t1,t2;
212
213#
214# Test bug with month() and year() on text fields with wrong information
215
216CREATE TABLE t1 (updated text) ENGINE=MyISAM;
217INSERT INTO t1 VALUES ('');
218SELECT month(updated) from t1;
219SELECT year(updated) from t1;
220drop table t1;
221
222#
223# Check that functions work identically on 0000-00-00 as a constant and on a
224# column
225#
226
227create table t1 (d date, dt datetime, t timestamp, c char(10));
228insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
229select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
230select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
231select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
232select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
233select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1;
234select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
235select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1;
236select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;
237select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
238drop table t1;
239
240
241#
242# Test problem with TIMESTAMP and BETWEEN
243#
244
245CREATE TABLE t1 ( start datetime default NULL);
246INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');
247CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);
248INSERT INTO t2 VALUES (20021029165106,20021105164731);
249CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);
250INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");
251
252# The following statement should be fixed to return a row in 4.1
253select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
254select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
255select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
256drop table t1,t2,t3;
257
258#
259# Test unix timestamp
260#
261select @a:=FROM_UNIXTIME(1);
262select unix_timestamp(@a);
263select unix_timestamp('1969-12-01 19:00:01');
264
265#
266# Tests for bug #6439 "unix_timestamp() function returns wrong datetime
267# values for too big argument", bug #7515 "from_unixtime(0) now
268# returns NULL instead of the epoch" and bug #9191
269# "TIMESTAMP/from_unixtime() no longer accepts 2^31-1."
270# unix_timestamp() should return error for too big or negative argument.
271# It should return Epoch value for zero argument since it seems that many
272# users rely on this fact, from_unixtime() should work with values
273# up to INT_MAX32 because of the same reason.
274#
275select from_unixtime(-1);
276# check for from_unixtime(2^31-1) and from_unixtime(2^31)
277select from_unixtime(2147483647);
278select from_unixtime(2147483648);
279select from_unixtime(0);
280
281#
282# Some more tests for bug #9191 "TIMESTAMP/from_unixtime() no
283# longer accepts 2^31-1". Here we test that from_unixtime and
284# unix_timestamp are consistent, when working with boundary dates.
285#
286select unix_timestamp(from_unixtime(2147483647));
287select unix_timestamp(from_unixtime(2147483648));
288
289# check for invalid dates
290
291# bad year
292select unix_timestamp('2039-01-20 01:00:00');
293select unix_timestamp('1968-01-20 01:00:00');
294# bad month
295select unix_timestamp('2038-02-10 01:00:00');
296select unix_timestamp('1969-11-20 01:00:00');
297# bad day
298select unix_timestamp('2038-01-20 01:00:00');
299select unix_timestamp('1969-12-30 01:00:00');
300
301#
302# Check negative shift (we subtract several days for boundary dates during
303# conversion).
304select unix_timestamp('2038-01-17 12:00:00');
305
306#
307# Check positive shift. (it happens only on
308# platfroms with unsigned time_t, such as QNX)
309#
310select unix_timestamp('1970-01-01 03:00:01');
311
312# check bad date, close to the boundary (we cut them off in the very end)
313select unix_timestamp('2038-01-19 07:14:07');
314
315#
316# Bug #28759: DAYNAME() and MONTHNAME() return binary string
317#
318
319SELECT CHARSET(DAYNAME(19700101));
320SELECT CHARSET(MONTHNAME(19700101));
321SELECT LOWER(DAYNAME(19700101));
322SELECT LOWER(MONTHNAME(19700101));
323SELECT COERCIBILITY(MONTHNAME('1970-01-01')),COERCIBILITY(DAYNAME('1970-01-01'));
324
325#
326# Test types from + INTERVAL
327#
328
329CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);
330INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");
331SELECT * from t1;
332select date_add("1997-12-31",INTERVAL 1 SECOND);
333select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);
334
335select date_add(datetime, INTERVAL 1 SECOND) from t1;
336select date_add(datetime, INTERVAL 1 YEAR) from t1;
337
338select date_add(date,INTERVAL 1 SECOND) from t1;
339select date_add(date,INTERVAL 1 MINUTE) from t1;
340select date_add(date,INTERVAL 1 HOUR) from t1;
341select date_add(date,INTERVAL 1 DAY) from t1;
342select date_add(date,INTERVAL 1 MONTH) from t1;
343select date_add(date,INTERVAL 1 YEAR) from t1;
344select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;
345select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;
346select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;
347select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;
348select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
349select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
350select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
351select date_add(date,INTERVAL "1" WEEK) from t1;
352select date_add(date,INTERVAL "1" QUARTER) from t1;
353select timestampadd(MINUTE, 1, date) from t1;
354select timestampadd(WEEK, 1, date) from t1;
355select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
356
357select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
358select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
359select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
360select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
361select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
362select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
363select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
364select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
365select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
366select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
367
368select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
369       timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
370       timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
371       timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
372
373# bug 16226
374SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
375SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');
376SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');
377SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');
378SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');
379SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');
380
381SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
382SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
383SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
384SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');
385SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');
386SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');
387
388SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
389SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');
390SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
391SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');
392SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');
393SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');
394
395SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');
396SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');
397SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');
398SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');
399SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');
400SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');
401
402# end of bug
403
404select date_add(time,INTERVAL 1 SECOND) from t1;
405drop table t1;
406
407# test for last_day
408select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,
409       last_day('2003-03-32') as f3, last_day('2003-04-01') as f4,
410       last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
411       last_day('2001-02-12');
412
413create table t1 select last_day('2000-02-05') as a,
414                from_days(to_days("960101")) as b;
415describe t1;
416select * from t1;
417drop table t1;
418select last_day('2000-02-05') as a,
419       from_days(to_days("960101")) as b;
420
421select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
422select length(last_day("1997-12-1"));
423select last_day("1997-12-1")+0;
424select last_day("1997-12-1")+0.0;
425
426# Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that
427# TZ variable set to GMT-3
428
429select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
430select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;
431select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;
432select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;
433select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
434select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
435
436explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
437
438SET @TMP='2007-08-01 12:22:49';
439CREATE TABLE t1 (d DATETIME);
440INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
441INSERT INTO t1 VALUES ('2007-08-01 12:23:01');
442INSERT INTO t1 VALUES ('2007-08-01 12:23:20');
443SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
444DROP TABLE t1;
445
446#
447# Bug #10568
448#
449
450select last_day('2005-00-00');
451select last_day('2005-00-01');
452select last_day('2005-01-00');
453
454#
455# Bug #18501: monthname and NULLs
456#
457
458select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
459       monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
460
461#
462# Bug #16327: problem with timestamp < 1970
463#
464
465set time_zone='-6:00';
466create table t1(a timestamp);
467insert into t1 values (19691231190001);
468select * from t1;
469drop table t1;
470
471#
472# Bug#16377 result of DATE/TIME functions were compared as strings which
473#           can lead to a wrong result.
474# Now wrong dates should be compared only with CAST()
475create table t1(f1 date, f2 time, f3 datetime);
476insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
477insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
478select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
479select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
480select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
481select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
482select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
483select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
484select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
485select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
486select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
487select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
488select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
489select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
490drop table t1;
491
492#
493# Bug #16546
494#
495
496create table t1 select now() - now(), curtime() - curtime(),
497                       sec_to_time(1) + 0, from_unixtime(1) + 0;
498show create table t1;
499drop table t1;
500
501#
502# Bug #11655: Wrong time is returning from nested selects - maximum time exists
503#
504# check if SEC_TO_TIME() handles out-of-range values correctly
505SELECT SEC_TO_TIME(3300000);
506SELECT SEC_TO_TIME(3300000)+0;
507SELECT SEC_TO_TIME(3600 * 4294967296);
508
509# check if TIME_TO_SEC() handles out-of-range values correctly
510SELECT TIME_TO_SEC('916:40:00');
511
512# check if ADDTIME() handles out-of-range values correctly
513SELECT ADDTIME('500:00:00', '416:40:00');
514SELECT ADDTIME('916:40:00', '416:40:00');
515
516# check if SUBTIME() handles out-of-range values correctly
517SELECT SUBTIME('916:40:00', '416:40:00');
518SELECT SUBTIME('-916:40:00', '416:40:00');
519
520# check if MAKETIME() handles out-of-range values correctly
521SELECT MAKETIME(916,0,0);
522SELECT MAKETIME(4294967296, 0, 0);
523SELECT MAKETIME(-4294967296, 0, 0);
524SELECT MAKETIME(0, 4294967296, 0);
525SELECT MAKETIME(0, 0, 4294967296);
526SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0);
527
528# check if EXTRACT() handles out-of-range values correctly
529SELECT EXTRACT(HOUR FROM '10000:02:03');
530
531# check if we get proper warnings if both input string truncation
532# and out-of-range value occur
533CREATE TABLE t1(f1 TIME);
534INSERT IGNORE INTO t1 VALUES('916:00:00 a');
535SELECT * FROM t1;
536DROP TABLE t1;
537
538#
539# Bug #20927: sec_to_time treats big unsigned as signed
540#
541# check if SEC_TO_TIME() handles BIGINT UNSIGNED values correctly
542--replace_regex /'1.8446.*e.*19'/'1.84467440737096e+19'/
543SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED));
544
545#
546# 21913: DATE_FORMAT() Crashes mysql server if I use it through
547#        mysql-connector-j driver.
548#
549
550SET NAMES latin1;
551SET character_set_results = NULL;
552SHOW VARIABLES LIKE 'character_set_results';
553
554CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);
555INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');
556
557SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;
558
559DROP TABLE testBug8868;
560
561SET NAMES DEFAULT;
562
563#
564# Bug #31160: MAKETIME() crashes server when returning NULL in ORDER BY using
565# filesort
566#
567SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-01 11:22:33');
568CREATE TABLE t1 (
569  a TIMESTAMP
570);
571INSERT INTO t1 VALUES (now()), (now());
572SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a);
573DROP TABLE t1;
574SET TIMESTAMP=DEFAULT;
575
576#
577# Bug #19844 time_format in Union truncates values
578#
579
580(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)
581union
582(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);
583(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H)
584union
585(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H);
586(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H)
587union
588(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H);
589
590(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H)
591union
592(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H);
593
594#
595# Bug #23653: crash if last_day('0000-00-00')
596#
597
598select last_day('0000-00-00');
599
600#
601# Bug 23616: datetime functions with double argumets
602#
603
604select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
605  week(20061108), week(20061108.01), week(20061108085411.000002);
606
607--echo End of 4.1 tests
608
609#
610# Bug #10590: %h, %I, and %l format specifies should all return results in
611# the 0-11 range
612#
613select time_format('100:00:00', '%H %k %h %I %l');
614
615#
616# Bug #12562: Make SYSDATE behave like it does in Oracle: always the current
617#             time, regardless of magic to make NOW() always the same for the
618#             entirety of a statement.
619SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
620SET GLOBAL log_bin_trust_function_creators = 1;
621
622create table t1 (a timestamp default '2005-05-05 01:01:01',
623                 b timestamp default '2005-05-05 01:01:01');
624delimiter //;
625drop function if exists t_slow_sysdate;
626create function t_slow_sysdate() returns timestamp
627begin
628  do sleep(2);
629  return sysdate();
630end;
631//
632
633insert into t1 set a = sysdate(), b = t_slow_sysdate();//
634
635create trigger t_before before insert on t1
636for each row begin
637  set new.b = t_slow_sysdate();
638end
639//
640
641delimiter ;//
642
643insert into t1 set a = sysdate();
644
645select a != b from t1;
646
647drop trigger t_before;
648drop function t_slow_sysdate;
649drop table t1;
650
651SET GLOBAL log_bin_trust_function_creators = 0;
652
653create table t1 (a datetime, i int, b datetime);
654insert into t1 select sysdate(), sleep(2), sysdate() from dual;
655select a != b from t1;
656drop table t1;
657
658delimiter //;
659create procedure t_sysdate()
660begin
661  select sysdate() into @a;
662  do sleep(2);
663  select sysdate() into @b;
664  select @a != @b;
665end;
666//
667delimiter ;//
668call t_sysdate();
669drop procedure t_sysdate;
670SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
671
672#
673# Bug #13534: timestampdiff() returned incorrect results across leap years
674#
675select timestampdiff(month,'2004-09-11','2004-09-11');
676select timestampdiff(month,'2004-09-11','2005-09-11');
677select timestampdiff(month,'2004-09-11','2006-09-11');
678select timestampdiff(month,'2004-09-11','2007-09-11');
679select timestampdiff(month,'2005-09-11','2004-09-11');
680select timestampdiff(month,'2005-09-11','2003-09-11');
681
682select timestampdiff(month,'2004-02-28','2005-02-28');
683select timestampdiff(month,'2004-02-29','2005-02-28');
684select timestampdiff(month,'2004-02-28','2005-02-28');
685select timestampdiff(month,'2004-03-29','2005-03-28');
686select timestampdiff(month,'2003-02-28','2004-02-29');
687select timestampdiff(month,'2003-02-28','2005-02-28');
688
689select timestampdiff(month,'1999-09-11','2001-10-10');
690select timestampdiff(month,'1999-09-11','2001-9-11');
691
692select timestampdiff(year,'1999-09-11','2001-9-11');
693select timestampdiff(year,'2004-02-28','2005-02-28');
694select timestampdiff(year,'2004-02-29','2005-02-28');
695
696#
697# Bug #18618: BETWEEN for dates with the second argument being a constant
698#             expression and the first and the third arguments being fields
699#
700
701CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);
702CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);
703
704INSERT INTO t1 VALUES
705  (1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');
706INSERT INTO t2 VALUES
707  (1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
708
709SELECT * FROM t1, t2
710  WHERE t1.day BETWEEN
711               '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
712SELECT * FROM t1, t2
713  WHERE CAST(t1.day AS DATE) BETWEEN
714                             '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
715
716DROP TABLE t1,t2;
717
718
719# Restore timezone to default
720set time_zone= @@global.time_zone;
721
722#
723# Bug #22229: bug in DATE_ADD()
724#
725
726select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
727select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute;
728
729#
730# Bug #21103: DATE column not compared as DATE
731#
732
733create table t1 (field DATE);
734insert into t1 values ('2006-11-06');
735select * from t1 where field < '2006-11-06 04:08:36.0';
736select * from t1 where field = '2006-11-06 04:08:36.0';
737select * from t1 where field = '2006-11-06';
738select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
739select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
740drop table t1;
741
742#
743# Bug #25643: SEC_TO_TIME function problem
744#
745CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));
746INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL),
747       (2, '11:00:00', '11:15:00', '1972-02-06');
748SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
749  FROM t1;
750SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
751  FROM t1 ORDER BY a DESC;
752DROP TABLE t1;
753
754#
755# Bug #20293: group by cuts off value from time_format
756#
757# Check if using GROUP BY with TIME_FORMAT() produces correct results
758
759SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1;
760
761#
762# Bug#28875 Conversion between ASCII and LATIN1 charsets does not function
763#
764set names latin1;
765create table t1 (a varchar(15) character set ascii not null);
766insert into t1 values ('070514-000000');
767# Conversion of date_format() result to ASCII
768# is safe with the default locale en_US
769--replace_column 1 #
770select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
771# Error for swe7: it is not ASCII compatible
772set names swe7;
773--error 1267
774select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
775set names latin1;
776# Conversion of date_format() result to ASCII
777# is not safe with the non-default locale fr_FR
778# because month and day names can have accented characters
779set lc_time_names=fr_FR;
780--error 1267
781select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
782set lc_time_names=en_US;
783drop table t1;
784
785#
786# Bug#32180: DATE_ADD treats datetime numeric argument as DATE
787#            instead of DATETIME
788#
789
790select DATE_ADD('20071108181000', INTERVAL 1 DAY);
791select DATE_ADD(20071108181000,   INTERVAL 1 DAY);
792select DATE_ADD('20071108',       INTERVAL 1 DAY);
793select DATE_ADD(20071108,         INTERVAL 1 DAY);
794
795#
796# Bug#32770: LAST_DAY() returns a DATE, but somehow internally keeps
797#            track of the TIME.
798#
799
800select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND;
801
802# Bug#33834: FRAC_SECOND: Applicability not clear in documentation
803#
804# Test case removed since FRAC_SECOND was deprecated and
805# removed as part of WL#5154
806#
807
808#
809# Bug #36466:
810#   Adding days to day_microsecond changes interpretation of microseconds
811#
812
813# show that we treat fractions of seconds correctly (zerofill from right to
814# six places) even if we left out fields on the left.
815select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond);
816select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond);
817
818
819--echo #
820--echo # Bug #52315 part 2 : utc_date() crashes when system time > year 2037
821--echo #
822
823--disable_result_log
824SET TIMESTAMP=-147490000; SELECT UTC_TIMESTAMP();
825--error 0,ER_WRONG_VALUE_FOR_VAR
826SET TIMESTAMP=2147483648; SELECT UTC_TIMESTAMP();
827SET TIMESTAMP=2147483646; SELECT UTC_TIMESTAMP();
828SET TIMESTAMP=2147483647; SELECT UTC_TIMESTAMP();
829SET TIMESTAMP=0; SELECT UTC_TIMESTAMP();
830SET TIMESTAMP=-1; SELECT UTC_TIMESTAMP();
831SET TIMESTAMP=1; SELECT UTC_TIMESTAMP();
832--enable_result_log
833
834#reset back the timestamp value
835SET TIMESTAMP=0;
836
837
838--echo End of 5.0 tests
839
840#
841# Bug #18997
842#
843
844select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
845select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
846select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
847select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
848select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
849select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
850select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
851select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
852select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
853select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
854select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
855
856
857#
858# Bug #55565: debug assertion when ordering by expressions with user
859# variable assignments
860#
861
862CREATE TABLE t1(a DOUBLE NOT NULL);
863INSERT INTO t1 VALUES (0),(9.216e-096);
864--echo # should not crash
865SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a);
866DROP TABLE t1;
867
868--echo #
869--echo # Bug #52160: crash and inconsistent results when grouping
870--echo #             by a function and column
871--echo #
872
873CREATE TABLE t1(a CHAR(10) NOT NULL);
874INSERT INTO t1 VALUES (''),('');
875SELECT COUNT(*) FROM t1 GROUP BY TIME_TO_SEC(a);
876DROP TABLE t1;
877
878--echo #
879--echo # Bug#11766112  59151:UNINITIALIZED VALUES IN EXTRACT_DATE_TIME WITH STR_TO_DATE(SPACE(..) ...
880--echo #
881
882SELECT STR_TO_DATE(SPACE(2),'1');
883
884--echo #
885--echo # Bug#11765216  58154: UNINITIALIZED VARIABLE FORMAT IN STR_TO_DATE FUNCTION
886--echo #
887
888SET GLOBAL SQL_MODE='';
889--disable_warnings
890DO  STR_TO_DATE((''), FROM_DAYS(@@GLOBAL.SQL_MODE));
891--enable_warnings
892SET GLOBAL SQL_MODE=DEFAULT;
893
894--echo #
895--echo # Bug#11766087  59125: VALGRIND UNINITIALISED VALUE WARNING IN ULL2DEC, LONGLONG2DECIMAL
896--echo #
897
898SELECT FORMAT(YEAR(STR_TO_DATE('',GET_FORMAT(TIME,''))),1);
899
900--echo #
901--echo # Bug#11766126  59166: ANOTHER DATETIME VALGRIND UNINITIALIZED WARNING
902--echo #
903
904--disable_result_log
905SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025));
906--enable_result_log
907
908--echo #
909--echo # Bug#11766124  59164: VALGRIND: UNINITIALIZED VALUE IN NUMBER_TO_DATETIME
910--echo #
911
912SELECT ADDDATE(MONTH(FROM_UNIXTIME(NULL)),INTERVAL 1 HOUR);
913
914--echo #
915--echo # Bug#11889186  60503: CRASH IN MAKE_DATE_TIME WITH DATE_FORMAT / STR_TO_DATE COMBINATION
916--echo #
917
918SELECT DATE_FORMAT('0000-00-11', '%W');
919SELECT DATE_FORMAT('0000-00-11', '%a');
920SELECT DATE_FORMAT('0000-00-11', '%w');
921
922--echo #
923--echo # Bug#12403504  AFTER FIX FOR #11889186 : ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0
924--echo #
925
926SELECT MAKEDATE(11111111,1);
927SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1);
928
929--echo #
930--echo # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0,
931--echo #
932
933DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5);
934
935--echo #
936--echo # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY
937--echo # SIMILAR TO '2009-10-00'
938--echo #
939
940query_vertical SELECT
941  DATE('20091000'),
942  STR_TO_DATE('200910','%Y%m'),
943  LAST_DAY('2009-10-00'),
944  LAST_DAY(DATE('2009-10-00')),
945  LAST_DAY(DATE'2009-10-00'),
946  LAST_DAY(STR_TO_DATE('200910','%Y%m')),
947  WEEK('2009-10-00'),
948  WEEK(DATE('2009-10-00')),
949  WEEK(DATE'2009-10-00'),
950  WEEK(STR_TO_DATE('200910','%Y%m')),
951  WEEKOFYEAR('2009-10-00'),
952  WEEKOFYEAR(DATE('2009-10-00')),
953  WEEKOFYEAR(DATE'2009-10-00'),
954  WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')),
955  DAYOFYEAR('2009-10-00'),
956  DAYOFYEAR(DATE('2009-10-00')),
957  DAYOFYEAR(DATE'2009-10-00'),
958  DAYOFYEAR(STR_TO_DATE('200910','%Y%m')),
959  WEEKDAY('2009-10-00'),
960  WEEKDAY(DATE('2009-10-00')),
961  WEEKDAY(DATE'2009-10-00'),
962  WEEKDAY(STR_TO_DATE('200910','%Y%m')),
963  TO_DAYs('2009-10-00'),
964  TO_DAYs(DATE('2009-10-00')),
965  TO_DAYs(DATE'2009-10-00'),
966  TO_DAYs(STR_TO_DATE('200910','%Y%m'));
967
968query_vertical SELECT
969  DATE('00000100'),
970  STR_TO_DATE('000001','%Y%m'),
971  LAST_DAY('0000-01-00'),
972  LAST_DAY(DATE('0000-01-00')),
973  LAST_DAY(DATE'0000-01-00'),
974  LAST_DAY(STR_TO_DATE('000001','%Y%m')),
975  WEEK('0000-01-00'),
976  WEEK(DATE('0000-01-00')),
977  WEEK(DATE'0000-01-00'),
978  WEEK(STR_TO_DATE('000001','%Y%m')),
979  WEEKOFYEAR('0000-01-00'),
980  WEEKOFYEAR(DATE('0000-01-00')),
981  WEEKOFYEAR(DATE'0000-01-00'),
982  WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')),
983  DAYOFYEAR('0000-01-00'),
984  DAYOFYEAR(DATE('0000-01-00')),
985  DAYOFYEAR(DATE'0000-01-00'),
986  DAYOFYEAR(STR_TO_DATE('000001','%Y%m')),
987  WEEKDAY('0000-01-00'),
988  WEEKDAY(DATE('0000-01-00')),
989  WEEKDAY(DATE'0000-01-00'),
990  WEEKDAY(STR_TO_DATE('000001','%Y%m')),
991  TO_DAYs('0000-01-00'),
992  TO_DAYs(DATE('0000-01-00')),
993  TO_DAYs(DATE'0000-01-00'),
994  TO_DAYs(STR_TO_DATE('000001','%Y%m'));
995
996--echo End of 5.1 tests
997
998--echo #
999--echo # Bug#57039: constant subtime expression returns incorrect result.
1000--echo #
1001CREATE TABLE t1 (`date_date` datetime NOT NULL);
1002INSERT INTO t1 VALUES ('2008-01-03 00:00:00'), ('2008-01-03 00:00:00');
1003SELECT * FROM t1 WHERE date_date >= subtime(now(), "00:30:00");
1004SELECT * FROM t1 WHERE date_date <= addtime(date_add("2000-1-1", INTERVAL "1:1:1" HOUR_SECOND), "00:20:00");
1005DROP TABLE t1;
1006
1007--echo #
1008--echo # Bug#57512 str_to_date crash...
1009--echo #
1010
1011SELECT WEEK(STR_TO_DATE(NULL,0));
1012SELECT SUBDATE(STR_TO_DATE(NULL,0), INTERVAL 1 HOUR);
1013
1014--echo #
1015--echo # BUG#59895 - setting storage engine to null segfaults mysqld
1016--echo #
1017SELECT MONTHNAME(0), MONTHNAME(0) IS NULL, MONTHNAME(0) + 1;
1018--error ER_WRONG_VALUE_FOR_VAR
1019SET default_storage_engine=NULL;
1020
1021
1022--echo #
1023--echo # BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP
1024--echo # Part1 (5.5)
1025SET time_zone='+03:00';
1026CREATE TABLE t1 (a DATETIME NOT NULL);
1027INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06');
1028INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00');
1029CREATE VIEW v1 AS SELECT * FROM t1;
1030SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1;
1031DROP VIEW v1;
1032DROP TABLE t1;
1033SET time_zone=DEFAULT;
1034
1035
1036--echo #
1037--echo # Bug #59686 crash in String::copy() with time data type
1038--echo #
1039
1040SELECT min(timestampadd(month, 1>'', from_days('%Z')));
1041
1042SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
1043create table t1(a time);
1044insert into t1 values ('00:00:00'),('00:01:00');
1045select 1 from t1 where 1 < some (select cast(a as datetime) from t1);
1046drop table t1;
1047SET timestamp=DEFAULT;
1048
1049--echo #
1050--echo # Bug #21564557: INCONSISTENT OUTPUT FROM 5.5 AND 5.6
1051--echo #                UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%M"
1052--echo #
1053
1054SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"));
1055SELECT UNIX_TIMESTAMP('2015-06-00');
1056SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'));
1057
1058set sql_mode= 'TRADITIONAL';
1059SELECT @@sql_mode;
1060
1061SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"));
1062SELECT UNIX_TIMESTAMP('2015-06-00');
1063SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'));
1064
1065set sql_mode= default;
1066
1067select time('10:10:10') > 10;
1068select time('10:10:10') > 1010;
1069select time('10:10:09') > 101010;
1070select time('10:10:10') > 101010;
1071select time('10:10:11') > 101010;
1072
1073select time(' 1 02:03:04') + interval 9 microsecond;
1074select time(' 1 02:03:04') - interval 9 microsecond;
1075select time('-1 02:03:04') + interval 9 microsecond;
1076select time('-1 02:03:04') - interval 9 microsecond;
1077select time(' 1 02:03:04') + interval '4:4:4' hour_second;
1078select time(' 1 02:03:04') - interval '4:4:4' hour_second;
1079select time('-1 02:03:04') + interval '4:4:4' hour_second;
1080select time('-1 02:03:04') - interval '4:4:4' hour_second;
1081select time(' 1 02:03:04') + interval 2 day;
1082select time(' 1 02:03:04') - interval 2 day;
1083select time('-1 02:03:04') + interval 2 day;
1084select time('-1 02:03:04') - interval 2 day;
1085
1086select time('10 02:03:04') + interval 30 day;
1087select time('10 02:03:04') + interval 1 year;
1088
1089# specially constructed queries to reach obscure places in the code
1090# not touched by the more "normal" queries (and to increase the coverage)
1091select cast('131415.123e0' as time);
1092select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04';
1093select least(time('1:2:3'), '01:02:04', null) div 1;
1094select truncate(least(time('1:2:3'), '01:02:04', null), 6);
1095select cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1));
1096select unix_timestamp(null);
1097select truncate(date('2010-40-10'), 6);
1098select extract(month from '2010-40-50');
1099select subtime('0000-00-10 10:10:10', '30 10:00:00');
1100
1101#
1102# lp:730637 Valgrind warnings in 5.1-micro
1103#
1104select cast(str_to_date(NULL, '%H:%i:%s') as time);
1105
1106create table t1 (f1 datetime, key (f1));
1107insert into t1 values ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
1108select * from t1 where f1 > time('-23:00:06');
1109drop table t1;
1110
1111#
1112# lp:730627 TIME_to_ulonglong: Assertion `0' failed in 5.1-micro on wrong argument to MAKETIME
1113#
1114select maketime(20,61,10)+0;
1115
1116#
1117# lp:731103 Assertion `maybe_null && item->null_value' failed with ORDER BY LAST_DAY()
1118#
1119create table t1 (f2 int not null) ;
1120insert into t1 values (0),(0);
1121select last_day(f2) from t1;
1122select last_day(f2) from t1 where last_day(f2) is null;
1123select * from t1 order by last_day (f2);
1124drop table t1;
1125
1126#
1127# lp:731815 Crash/valgrind warning Item::send with 5.1-micro
1128#
1129SET timestamp=unix_timestamp('2001-02-03 10:20:30');
1130select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow');
1131SET timestamp=DEFAULT;
1132
1133#
1134# lp:736370 Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micr
1135#
1136create table t1 (f1 integer, f2 date);
1137insert into t1 values (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05'),(6, '2011-05-06');
1138select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125);
1139drop table t1;
1140
1141#
1142# lp:736791 Crash in make_truncated_value_warning with LEAST()/GREATEST/COALESCE
1143#
1144create table t1 (f1 timestamp);
1145insert into t1 values ('0000-00-00 00:00:00');
1146select least(1, f1) from t1;
1147drop table t1;
1148
1149#
1150# lp:737092 Assertion `item->null_value' failed in get_datetime_value in 5.1-micro
1151#
1152SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10');
1153select now() > coalesce(time('21:43:24'), date('2010-05-03'));
1154SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22');
1155select now() > coalesce(time('21:43:24'), date('2010-05-03'));
1156SET timestamp=DEFAULT;
1157
1158#
1159# lp:737104 Crash in DTCollation::set in 5.1-micro
1160#
1161create table t1 (f1 timestamp);
1162select * from t1 where f1 > f1 and f1 <=> timestampadd(hour, 9 , '2010-01-01 16:55:35');
1163drop table t1;
1164
1165#
1166# lp:737111 Different behavior for TIMESTAMPADD with 0000-00-00 argument in 5.1-micro
1167#
1168create table t1 (f1 date);
1169insert into t1 values ('0000-00-00');
1170select timestampadd(week, 1, f1) from t1;
1171select timestampadd(week, 1, date("0000-00-00"));
1172drop table t1;
1173
1174#
1175# lp:737450 Second Assertion `item->null_value' failed in 5.1-micro
1176#
1177create table t1 (f2 time not null, f3 datetime, f4 int not null, f5 timestamp);
1178insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00');
1179select least(greatest(f3, f2, f4), f5) from t1;
1180drop table t1;
1181
1182#
1183# lp:737474 Wrong result with DAY(COALESCE(NULL)) in 5.1-micro
1184#
1185select day(coalesce(null));
1186
1187#
1188# lp:738067 Crash in get_datetime_value() in 5.1-micro
1189#
1190select timestamp(greatest('2002-08-20', '0000-00-00 00:00:00'));
1191
1192#
1193# lp:738091  cast(timestamp() AS time returns NULL for 0000-00-00 00:00:00 in 5.1-micro
1194#
1195create table t1 (f1 datetime);
1196insert into t1 values ('0000-00-00 00:00:00');
1197select cast(f1 AS time) from t1;
1198drop table t1;
1199
1200SET timestamp=UNIX_TIMESTAMP('2014-06-01 10:20:30');
1201select greatest(cast("0-0-0" as date), cast("10:20:05" as time));
1202select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00';
1203select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01';
1204select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05';
1205select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6));
1206SET timestamp=DEFAULT;
1207
1208select microsecond('12:00:00.123456'), microsecond('2009-12-31 23:59:59.000010');
1209
1210--error ER_TOO_BIG_PRECISION
1211select now(258);
1212
1213#
1214# MDEV-4293 Valgrind warnings (Conditional jump or move depends on uninitialised value) in remove_eq_conds on time functions with NULL argument in WHERE
1215#
1216SELECT 1 FROM DUAL WHERE YEAR(TIMEDIFF(NULL, '12:12:12'));
1217SELECT 1 FROM DUAL WHERE MONTH(TIMEDIFF(NULL, '12:12:12'));
1218SELECT 1 FROM DUAL WHERE DAYOFMONTH(TIMEDIFF(NULL, '12:12:12'));
1219SELECT 1 FROM DUAL WHERE HOUR(TIMEDIFF(NULL, '12:12:12'));
1220SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF(NULL, '12:12:12'));
1221SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12'));
1222
1223
1224--echo #
1225--echo # MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types
1226--echo #
1227CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1228INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1229SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1;
1230DROP TABLE t1;
1231
1232CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1233INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1234SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1;
1235DROP TABLE t1;
1236
1237CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1238INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1239SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1;
1240DROP TABLE t1;
1241
1242CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1243INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1244SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1;
1245DROP TABLE t1;
1246
1247CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1248INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1249SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1;
1250DROP TABLE t1;
1251
1252CREATE TABLE t1 AS SELECT
1253  STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only,
1254  STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only,
1255  STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond,
1256  STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time,
1257  STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond;
1258SHOW COLUMNS FROM t1;
1259DROP TABLE t1;
1260
1261CREATE TABLE t1 AS SELECT
1262  SEC_TO_TIME(1)+0.1,
1263  SEC_TO_TIME(1.1)+0.1,
1264  SEC_TO_TIME(1.12)+0.1,
1265  SEC_TO_TIME(1.123456)+0.1,
1266  SEC_TO_TIME(1.1234567)+0.1;
1267SHOW COLUMNS FROM t1;
1268DROP TABLE t1;
1269
1270CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1271INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1272SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))*1;
1273SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')))*1;
1274SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')));
1275SELECT * FROM t1 GROUP BY ABS(FROM_UNIXTIME(concat(a,'10')));
1276SELECT * FROM t1 GROUP BY @a:=(FROM_UNIXTIME(concat(a,'10'))*1);
1277
1278DROP TABLE t1;
1279
1280SET TIME_ZONE='+02:00';
1281
1282--echo #
1283--echo # MDEV-6302 Wrong result set when using GROUP BY FROM_UNIXTIME(...)+0
1284--echo #
1285CREATE TABLE t1 (a DATE);
1286INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1287SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 AS f2 FROM t1;
1288SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0;
1289DROP TABLE t1;
1290
1291CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
1292INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
1293SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1;
1294DROP TABLE t1;
1295
1296CREATE TABLE t1 (a DATE);
1297INSERT INTO t1 VALUES ('2005-05-04');
1298SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
1299SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1;
1300SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
1301CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
1302SHOW CREATE TABLE t2;
1303SELECT * FROM t2;
1304DROP TABLE t1,t2;
1305
1306--echo #
1307--echo # MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'))
1308--echo #
1309SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'));
1310
1311SET TIME_ZONE=DEFAULT;
1312
1313--echo #
1314--echo #  MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context
1315--echo #
1316CREATE TABLE t1 (a TIMESTAMP(3));
1317INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999');
1318SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2  FROM t1;
1319DROP TABLE t1;
1320CREATE TABLE t1 (a TIME(3));
1321INSERT INTO t1 VALUES ('10:20:30.999');
1322SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2  FROM t1;
1323DROP TABLE t1;
1324
1325SELECT
1326  CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1327  CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1328  COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1329  CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1330  TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1331  DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1332  TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1333
1334SELECT
1335  CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
1336  CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
1337  COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3,
1338  CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4,
1339  TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
1340  DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
1341  TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
1342
1343SELECT
1344  CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1345  CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1346  IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1347  CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1348  TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1349  DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1350  TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1351
1352SELECT
1353  CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
1354  CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
1355  IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3,
1356  CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4,
1357  TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
1358  DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
1359  TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
1360
1361SELECT
1362  CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1363  CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1364  IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1365  CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1366  TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1367  DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1368  TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1369
1370SELECT
1371  CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1372  CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1373  IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1374  CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1375  TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1376  DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1377  TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1378
1379SELECT
1380  CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
1381  CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
1382  CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3,
1383  CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4,
1384  TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5,
1385  DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
1386  TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
1387
1388SELECT
1389  CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
1390  CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
1391  CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3,
1392  CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4,
1393  TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5,
1394  DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
1395  TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
1396
1397CREATE TABLE t1 AS SELECT
1398  CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1,
1399  CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2,
1400  CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3,
1401  CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4;
1402SHOW CREATE TABLE t1;
1403DROP TABLE t1;
1404
1405
1406--echo #
1407--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
1408--echo #
1409SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
1410CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
1411INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
1412SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
1413CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
1414SELECT * FROM t2;
1415SHOW COLUMNS FROM t2;
1416DROP TABLE t2;
1417SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1;
1418SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1;
1419SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1;
1420SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1;
1421SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
1422SELECT COALESCE(d, t3) FROM t1;
1423SELECT CONCAT(COALESCE(d, t3)) FROM t1;
1424SELECT COALESCE(dt2, t3) FROM t1;
1425SELECT CONCAT(COALESCE(dt2, t3)) FROM t1;
1426SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
1427SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
1428DROP TABLE t1;
1429SET timestamp=DEFAULT;
1430
1431--echo #
1432--echo # MDEV-4724 Some temporal functions do not preserve microseconds
1433--echo #
1434SELECT MAKETIME(10,10,10.231);
1435SELECT MAKETIME(0, 0, 59.9);
1436CREATE TABLE t1 AS SELECT
1437  MAKETIME(10,00,00),
1438  MAKETIME(10,00,00.1),
1439  MAKETIME(10,00,00.12),
1440  MAKETIME(10,00,00.123),
1441  MAKETIME(10,00,00.1234),
1442  MAKETIME(10,00,00.12345),
1443  MAKETIME(10,00,00.123456);
1444SHOW COLUMNS FROM t1;
1445DROP TABLE t1;
1446
1447CREATE TABLE t1 AS SELECT
1448  TIME('10:00:00'),
1449  TIME('10:00:00.1'),
1450  TIME('10:00:00.12'),
1451  TIME('10:00:00.123'),
1452  TIME('10:00:00.1234'),
1453  TIME('10:00:00.12345'),
1454  TIME('10:00:00.12346');
1455SHOW COLUMNS FROM t1;
1456DROP TABLE t1;
1457
1458SET TIME_ZONE='+00:00';
1459SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17');
1460SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17');
1461SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456');
1462SELECT @@timestamp, FROM_UNIXTIME(@@timestamp);
1463SET TIME_ZONE=DEFAULT;
1464SET TIMESTAMP=DEFAULT;
1465
1466SELECT TIME('2012-10-16 15:54:16.12');
1467SELECT TIMESTAMP('2012-10-16 15:54:16.12');
1468SELECT TIMEDIFF('10:10:10.1','00:00:00');
1469SELECT TIME_TO_SEC('10:10:10');
1470SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12');
1471SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12');
1472SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
1473SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND);
1474SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND);
1475SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND);
1476SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND);
1477SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND);
1478SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND);
1479SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
1480SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);
1481SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND);
1482SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND);
1483SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND);
1484SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND);
1485SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND);
1486SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND);
1487SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND);
1488SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND);
1489SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND);
1490SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND);
1491SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND);
1492SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND);
1493SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND);
1494SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00');
1495
1496--echo #
1497--echo # MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision
1498--echo #
1499CREATE TABLE t1 (t0 TIME);
1500INSERT INTO t1 VALUES ('00:00:00');
1501SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1502CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1503SHOW COLUMNS FROM t2;
1504DROP TABLE t1,t2;
1505
1506CREATE TABLE t1 (t0 DATETIME);
1507INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
1508SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1509CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1510SHOW COLUMNS FROM t2;
1511DROP TABLE t1, t2;
1512
1513
1514--echo #
1515--echo # MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10')
1516--echo #
1517CREATE TABLE t1 AS SELECT
1518  TIMESTAMP('2001-01-01','10:10:10'),
1519  TIMESTAMP('2001-01-01','10:10:10.1'),
1520  TIMESTAMP('2001-01-01','10:10:10.12'),
1521  TIMESTAMP('2001-01-01','10:10:10.123'),
1522  TIMESTAMP('2001-01-01','10:10:10.1234'),
1523  TIMESTAMP('2001-01-01','10:10:10.12345'),
1524  TIMESTAMP('2001-01-01','10:10:10.123456'),
1525  TIMESTAMP('2001-01-01','10:10:10.1234567');
1526SHOW COLUMNS FROM t1;
1527SELECT * FROM t1;
1528DROP TABLE t1;
1529
1530CREATE TABLE t1 AS SELECT
1531  TIMESTAMP('2001-01-01 00:00:00','10:10:10'),
1532  TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'),
1533  TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'),
1534  TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'),
1535  TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'),
1536  TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'),
1537  TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'),
1538  TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10');
1539SHOW COLUMNS FROM t1;
1540SELECT * FROM t1;
1541DROP TABLE t1;
1542
1543SET timestamp=unix_timestamp('2001-02-03 10:20:30');
1544CREATE TABLE t1 AS SELECT
1545  TIMESTAMP('00:00:00','10:10:10'),
1546  TIMESTAMP(TIME('00:00:00'),'10:10:10');
1547SHOW COLUMNS FROM t1;
1548SELECT * FROM t1;
1549DROP TABLE t1;
1550SET timestamp=DEFAULT;
1551
1552--echo #
1553--echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
1554--echo #
1555SELECT MAKETIME(0, 0, -0.1);
1556
1557--echo #
1558--echo # MDEV-4857 Wrong result of HOUR('1 00:00:00')
1559--echo #
1560SET timestamp=unix_timestamp('2001-02-03 10:20:30');
1561SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
1562SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
1563SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
1564SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
1565
1566SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
1567SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
1568SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
1569SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
1570SET timestamp=DEFAULT;
1571
1572--echo #
1573--echo # MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.'
1574--echo #
1575SET TIMESTAMP=UNIX_TIMESTAMP('2014-01-22 18:19:20');
1576CREATE TABLE t1 (t TIME);
1577INSERT INTO t1 VALUES ('03:22:30'),('18:30:05');
1578SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1;
1579SELECT GREATEST(t, CURRENT_DATE()) FROM t1;
1580DROP TABLE t1;
1581SET TIMESTAMP=DEFAULT;
1582
1583--echo #
1584--echo # MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP
1585--echo #
1586CREATE TABLE t1 (i INT);
1587INSERT INTO t1 VALUES (1),(2);
1588SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP;
1589DROP TABLE t1;
1590
1591--echo #
1592--echo # MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
1593--echo #
1594SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
1595
1596--echo #
1597--echo # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL)
1598--echo #
1599--enable_metadata
1600SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE);
1601--disable_metadata
1602
1603--echo #
1604--echo # MDEV-5450 Assertion `cached_field_ type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' fails with IF, ISNULL, ADDDATE
1605--echo #
1606
1607CREATE TABLE t1 (a DATETIME, b DATE);
1608INSERT INTO t1 VALUES (NULL, '2012-12-21');
1609SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
1610SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
1611SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1;
1612SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
1613SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1;
1614SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1;
1615SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
1616SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
1617DROP TABLE t1;
1618
1619SET timestamp=unix_timestamp('2001-02-03 10:20:30');
1620CREATE TABLE t1 (a DATETIME, b TIME);
1621INSERT INTO t1 VALUES (NULL, '00:20:12');
1622SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
1623SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
1624SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1;
1625SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
1626SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1;
1627SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1;
1628SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
1629SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
1630DROP TABLE t1;
1631SET timestamp=DEFAULT;
1632
1633
1634--echo #
1635--echo # MDEV-5870 Assertion `ltime->neg == 0' fails with COALESCE, ADDDATE, MAKEDATE
1636--echo #
1637CREATE TABLE t1 (dt DATETIME);
1638INSERT INTO t1 VALUES ('2003-05-13 19:36:05'), ('2012-12-12 09:20:06');
1639SELECT COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) FROM t1;
1640DROP TABLE t1;
1641
1642--echo #
1643--echo # MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME
1644--echo #
1645SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03');
1646SELECT IF(1,TIME'10:20:30',DATE'2001-01-01');
1647SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01');
1648SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END;
1649SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01');
1650SET timestamp=DEFAULT;
1651
1652--echo #
1653--echo # MDEV-5750 Assertion `ltime->year == 0' fails on a query with EXTRACT DAY_MINUTE and TIME column
1654--echo #
1655SET timestamp=UNIX_TIMESTAMP('2010-01-01 00:00:00');
1656CREATE TABLE t1 ( d DATE, t TIME );
1657INSERT INTO t1 VALUES ('2008-12-05','22:34:09'),('2005-03-27','14:26:02');
1658SELECT EXTRACT(DAY_MINUTE FROM GREATEST(t,d)), GREATEST(t,d) FROM t1;
1659DROP TABLE t1;
1660SET timestamp=DEFAULT;
1661
1662--echo #
1663--echo # MDEV-7221 from_days fails after null value
1664--echo #
1665CREATE TABLE t1 (
1666 id INT(11) NOT NULL PRIMARY KEY,
1667 date1 DATE NULL DEFAULT NULL
1668);
1669INSERT INTO t1 VALUES (12, '2011-05-12');
1670INSERT INTO t1 VALUES (13, NULL);
1671INSERT INTO t1 VALUES (14, '2009-10-23');
1672INSERT INTO t1 VALUES (15, '2014-10-30');
1673INSERT INTO t1 VALUES (16, NULL);
1674INSERT INTO t1 VALUES (17, NULL);
1675INSERT INTO t1 VALUES (18, '2010-10-13');
1676SELECT a.id,a.date1,FROM_DAYS(TO_DAYS(a.date1)-10) as date2, DATE_ADD(a.date1,INTERVAL -10 DAY),TO_DAYS(a.date1)-10 FROM t1 a ORDER BY a.id;
1677DROP TABLE t1;
1678
1679--echo #
1680--echo # MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision()
1681--echo #
1682SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2;
1683
1684--echo #
1685--echo # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value
1686--echo #
1687
1688SET sql_mode='NO_ZERO_IN_DATE';
1689CREATE TABLE t1 (a TIME(6));
1690INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002');
1691SELECT * FROM t1;
1692DROP TABLE t1;
1693SET sql_mode=DEFAULT;
1694
1695
1696--echo #
1697--echo # MDEV-13972 crash in Item_func_sec_to_time::get_date
1698--echo #
1699
1700# The below query can return warning sporadically
1701--disable_warnings
1702DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID()))));
1703--enable_warnings
1704
1705DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~('')))));
1706SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0)));
1707
1708#
1709# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str
1710#
1711SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli'));
1712
1713
1714--echo #
1715--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result
1716--echo #
1717
1718--vertical_results
1719SELECT
1720  MAKETIME(1e10,0,0),
1721  MAKETIME(-1e10,0,0),
1722  MAKETIME(1e50,0,0),
1723  MAKETIME(-1e50,0,0),
1724  MAKETIME(COALESCE(1e50),0,0),
1725  MAKETIME(COALESCE(-1e50),0,0);
1726--horizontal_results
1727
1728CREATE TABLE t1 (a FLOAT);
1729INSERT INTO t1 VALUES (1e30),(-1e30);
1730SELECT MAKETIME(a,0,0) FROM t1;
1731DROP TABLE t1;
1732
1733--echo #
1734--echo # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result
1735--echo #
1736
1737SELECT MAKETIME(900,0,0);
1738SELECT MAKETIME(900,0,0.1);
1739SELECT MAKETIME(900,0,0.11);
1740SELECT MAKETIME(900,0,0.111);
1741SELECT MAKETIME(900,0,0.1111);
1742SELECT MAKETIME(900,0,0.11111);
1743SELECT MAKETIME(900,0,0.111111);
1744SELECT MAKETIME(900,0,0.1111111);
1745SELECT MAKETIME(900,0,0.11111111);
1746SELECT MAKETIME(900,0,0.111111111);
1747SELECT MAKETIME(900,0,EXP(1));
1748
1749SELECT MAKETIME(-900,0,0);
1750SELECT MAKETIME(-900,0,0.1);
1751SELECT MAKETIME(-900,0,0.11);
1752SELECT MAKETIME(-900,0,0.111);
1753SELECT MAKETIME(-900,0,0.1111);
1754SELECT MAKETIME(-900,0,0.11111);
1755SELECT MAKETIME(-900,0,0.111111);
1756SELECT MAKETIME(-900,0,0.1111111);
1757SELECT MAKETIME(-900,0,0.11111111);
1758SELECT MAKETIME(-900,0,0.111111111);
1759SELECT MAKETIME(-900,0,EXP(1));
1760
1761
1762--echo #
1763--echo # End of 5.5 tests
1764--echo #
1765
1766--echo #
1767--echo # MDEV-8205 timediff returns null when comparing decimal time to time string value
1768--echo #
1769
1770# 1h difference
1771SELECT
1772  TIMEDIFF('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ) AS str_str,
1773  TIMEDIFF('2014-01-01 00:00:00' , 20140101010000.000    ) AS str_dec,
1774  TIMEDIFF(20140101000000.000    , 20140101010000.000    ) AS dec_dec,
1775  TIMEDIFF(20140101000000.000    , '2014-01-01 01:00:00' ) AS dec_str;
1776
1777# 1D1h difference
1778SELECT
1779  TIMEDIFF('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ) AS str_str,
1780  TIMEDIFF('2014-01-01 00:00:00' , 20140102010000.000    ) AS str_dec,
1781  TIMEDIFF(20140101000000.000    , 20140102010000.000    ) AS dec_dec,
1782  TIMEDIFF(20140101000000.000    , '2014-01-02 01:00:00' ) AS dec_str;
1783
1784# 1M1D1h difference
1785SELECT
1786  TIMEDIFF('2014-01-01 00:00:00' , '2014-02-02 01:00:00' ) AS str_str,
1787  TIMEDIFF('2014-01-01 00:00:00' , 20140202010000.000    ) AS str_dec,
1788  TIMEDIFF(20140101000000.000    , 20140202010000.000    ) AS dec_dec,
1789  TIMEDIFF(20140101000000.000    , '2014-02-02 01:00:00' ) AS dec_str;
1790
1791# 2M1D1h difference
1792SELECT
1793  TIMEDIFF('2014-01-01 00:00:00' , '2014-03-02 01:00:00' ) AS str_str,
1794  TIMEDIFF('2014-01-01 00:00:00' , 20140302010000.000    ) AS str_dec,
1795  TIMEDIFF(20140101000000.000    , 20140302010000.000    ) AS dec_dec,
1796  TIMEDIFF(20140101000000.000    , '2014-03-02 01:00:00' ) AS dec_str;
1797
1798
1799--echo #
1800--echo # MDEV-10787 Assertion `ltime->neg == 0' failed in void date_to_datetime(MYSQL_TIME*)
1801--echo #
1802CREATE TABLE t1 (d DATE);
1803INSERT INTO t1 VALUES ('2005-07-20'),('2012-12-21');
1804SELECT REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) FROM t1;
1805SELECT REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) FROM t1;
1806SELECT CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) FROM t1;
1807SELECT CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) FROM t1;
1808SELECT CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) FROM t1;
1809DROP TABLE t1;
1810
1811# Maximum possible DAY_SECOND values in various formats
1812SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND);
1813SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND);
1814SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND);
1815SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND);
1816
1817# Out-of-range INTERVAL DAY_SECOND values
1818SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND);
1819SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND);
1820SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND);
1821
1822--echo #
1823--echo # MDEV-13202 Assertion `ltime->neg == 0' failed in date_to_datetime
1824--echo #
1825
1826CREATE TABLE t1 (i INT, d DATE);
1827INSERT INTO t1 VALUES (1, '1970-01-01');
1828SELECT MAX(NULLIF(i,1)) FROM t1 ORDER BY DATE_SUB(d,INTERVAL 17300000 HOUR);
1829DROP TABLE t1;
1830
1831CREATE TABLE t1 (i INT, d DATE);
1832INSERT INTO t1 VALUES (1, '1970-01-01');
1833SELECT CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) FROM t1;
1834DROP TABLE t1;
1835
1836SELECT CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR));
1837
1838
1839--echo #
1840--echo # End of 10.0 tests
1841--echo #
1842
1843--echo #
1844--echo # Start of 10.1 tests
1845--echo #
1846
1847--echo #
1848--echo # MDEV-10317 EXCTACT(MINUTE_MICROSECOND) truncates data
1849--echo #
1850
1851let $query=
1852SELECT
1853  a,
1854  EXTRACT(YEAR FROM a),
1855  EXTRACT(YEAR_MONTH FROM a),
1856  EXTRACT(QUARTER FROM a),
1857  EXTRACT(MONTH FROM a),
1858  EXTRACT(WEEK FROM a),
1859  EXTRACT(DAY FROM a),
1860  EXTRACT(DAY_HOUR FROM a),
1861  EXTRACT(DAY_MINUTE FROM a),
1862  EXTRACT(DAY_SECOND FROM a),
1863  EXTRACT(HOUR FROM a),
1864  EXTRACT(HOUR_MINUTE FROM a),
1865  EXTRACT(HOUR_SECOND FROM a),
1866  EXTRACT(MINUTE FROM a),
1867  EXTRACT(MINUTE_SECOND FROM a),
1868  EXTRACT(SECOND FROM a),
1869  EXTRACT(MICROSECOND FROM a),
1870  EXTRACT(DAY_MICROSECOND FROM a),
1871  EXTRACT(HOUR_MICROSECOND FROM a),
1872  EXTRACT(MINUTE_MICROSECOND FROM a),
1873  EXTRACT(SECOND_MICROSECOND FROM a)
1874FROM t1;
1875
1876
1877CREATE TABLE t1 (a DATETIME(6));
1878INSERT INTO t1 VALUES ('1999-12-31 23:59:59.999999');
1879
1880--vertical_results
1881--enable_metadata
1882--disable_ps_protocol
1883--eval $query
1884--enable_ps_protocol
1885--disable_metadata
1886--horizontal_results
1887
1888--eval CREATE TABLE t2 AS $query
1889--vertical_results
1890SELECT * FROM t2;
1891--horizontal_results
1892SHOW CREATE TABLE t2;
1893DROP TABLE t1,t2;
1894
1895CREATE TABLE t1 (a TIME(6));
1896INSERT INTO t1 VALUES ('-838:59:59.999999'),('838:59:59.999999');
1897
1898--vertical_results
1899--enable_metadata
1900--disable_ps_protocol
1901--eval $query
1902--enable_ps_protocol
1903--disable_metadata
1904--horizontal_results
1905
1906--eval CREATE TABLE t2 AS $query
1907--vertical_results
1908SELECT * FROM t2;
1909--horizontal_results
1910SHOW CREATE TABLE t2;
1911DROP TABLE t1,t2;
1912
1913
1914--echo #
1915--echo # MDEV-14926 AddressSanitizer: heap-use-after-free in make_date_time on weird combination of functions
1916--echo #
1917
1918DO INET_ATON( FROM_UNIXTIME( @@timestamp, ( TRIM( UNHEX(HEX('%m.%d.%Y') ) ) ) ) );
1919
1920CREATE TABLE t1 (d DATE);
1921INSERT INTO t1 VALUES ('1989-03-10');
1922SELECT TIME_FORMAT('23:59:43', BINARY d) AS f FROM t1 GROUP BY 'foo';
1923DROP TABLE t1;
1924
1925CREATE TABLE t1 (d DATE) ENGINE=MyISAM;
1926INSERT INTO t1 VALUES ('1900-01-01');
1927SELECT LENGTH( DATE_FORMAT( d, BINARY DATABASE() ) ) AS f FROM t1 GROUP BY d;
1928DROP TABLE t1;
1929
1930--echo #
1931--echo # MDEV-18667 ASAN heap-use-after-free in make_date_time / Arg_comparator::compare_string / Item_func_nullif::compare
1932--echo #
1933
1934SELECT NULLIF('foo', FROM_UNIXTIME('2012-12-12 12:12:12', TRIM(0)));
1935
1936--echo #
1937--echo # MDEV-18626 ASAN stack-buffer-overflow in int10_to_str / make_date_time upon DATE_FORMAT
1938--echo #
1939
1940SELECT DATE_FORMAT(100000000000, '%j');
1941
1942
1943--echo #
1944--echo # End of 10.1 tests
1945--echo #
1946
1947--echo #
1948--echo # MDEV-16217: Assertion `!table || (!table->read_set ||
1949--echo # bitmap_is_set(table->read_set, field_index))'
1950--echo # failed in Field_num::get_date
1951--echo #
1952CREATE TABLE t1 (pk int  default 0, a1 date);
1953INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
1954
1955CREATE VIEW v1 AS
1956SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
1957
1958SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
1959SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
1960
1961CREATE TABLE t2 (pk int  default 1, a1 date);
1962INSERT INTO t2 VALUES (4,NULL);
1963CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2;
1964select * from v2;
1965show create view v2;
1966CREATE view v3 as SELECT default(pk) from t2;
1967select * from v3;
1968explain extended select * from v3;
1969explain extended select default(pk) from t2;
1970show create view v3;
1971
1972DROP VIEW v1,v2,v3;
1973DROP TABLE t1,t2;
1974
1975--echo #
1976--echo # MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on
1977--echo #
1978
1979SELECT DAYNAME('2019-01-05')+0;
1980SELECT CAST(DAYNAME('2019-01-05') AS SIGNED);
1981
1982
1983--echo #
1984--echo # End of 10.2 tests
1985--echo #
1986
1987--echo #
1988--echo #  MDEV-12515 Wrong value when storing DATE_ADD() and ADDTIME() to a numeric field
1989--echo #
1990
1991SET sql_mode='';
1992
1993CREATE TABLE t1 AS SELECT
1994  DATE_ADD('2001-01-01',INTERVAL 1 DAY) AS c1,
1995  ADDTIME('10:20:30',1) AS c2;
1996SHOW CREATE TABLE t1;
1997SELECT * FROM t1;
1998DROP TABLE t1;
1999
2000CREATE TABLE t2 (c INT);
2001INSERT INTO t2 SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY);
2002INSERT INTO t2 VALUES ('2001-01-02');
2003SELECT * FROM t2;
2004DROP TABLE t2;
2005
2006CREATE TABLE t2 (a INT);
2007INSERT INTO t2 VALUES (ADDTIME('10:20:30',1));
2008INSERT INTO t2 VALUES ('10:20:31');
2009SELECT * FROM t2;
2010DROP TABLE t2;
2011
2012SET sql_mode=DEFAULT;
2013
2014
2015--echo #
2016--echo # MDEV-12860 Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..)
2017--echo #
2018
2019SET sql_mode=STRICT_ALL_TABLES;
2020CREATE TABLE t1 (
2021  id bigint(11) NOT NULL PRIMARY KEY,
2022  dt datetime(6)
2023);
2024INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');
2025CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;
2026DESCRIBE v1;
2027SELECT * FROM v1;
2028CREATE TABLE t2 AS SELECT MAX(dt2) FROM v1;
2029DESCRIBE t2;
2030SELECT * FROM t2;
2031DROP TABLE t2;
2032DROP VIEW v1;
2033DROP TABLE t1;
2034SET sql_mode=DEFAULT;
2035
2036--echo #
2037--echo # MDEV-12866 Out-of-range error with CREATE..SELECT..TO_SECONDS(NOW())
2038--echo #
2039SET sql_mode=STRICT_ALL_TABLES;
2040CREATE TABLE t1 AS SELECT TO_SECONDS('9999-12-31 23:59:59');
2041SHOW CREATE TABLE t1;
2042DROP TABLE t1;
2043SET sql_mode=DEFAULT;
2044
2045
2046--echo #
2047--echo # MDEV-13966 Parameter data type control for Item_temporal_func
2048--echo #
2049
2050--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2051SELECT FROM_DAYS(ROW(1,1));
2052
2053--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2054SELECT MAKEDATE(ROW(1,1),1);
2055--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2056SELECT MAKEDATE(1, ROW(1,1));
2057
2058--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2059SELECT LAST_DAY(ROW(1,1));
2060
2061--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2062SELECT SEC_TO_TIME(ROW(1,1));
2063
2064--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2065SELECT TIMEDIFF(ROW(1,1),1);
2066--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2067SELECT TIMEDIFF(1, ROW(1,1));
2068
2069--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2070SELECT MAKETIME(ROW(1,1),1,1);
2071--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2072SELECT MAKETIME(1, ROW(1,1), 1);
2073--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2074SELECT MAKETIME(1, 1, ROW(1,1));
2075
2076--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2077SELECT FROM_UNIXTIME(ROW(1,1));
2078
2079--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2080SELECT CONVERT_TZ(ROW(1,1),1,1);
2081--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2082SELECT CONVERT_TZ(1, ROW(1,1), 1);
2083--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2084SELECT CONVERT_TZ(1, 1, ROW(1,1));
2085
2086
2087--echo #
2088--echo # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
2089--echo #
2090
2091SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03');
2092SELECT
2093  COALESCE(TIME'800:00:00', NOW()) AS c,
2094  HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
2095
2096SELECT
2097  CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c,
2098  HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc;
2099
2100SELECT
2101  IFNULL(TIME'800:00:00', NOW()) AS c,
2102  HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc;
2103
2104SELECT
2105  IF(TRUE,TIME'800:00:00', NOW()) AS c,
2106  HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
2107
2108SELECT
2109  ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1,
2110  ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2,
2111  ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3,
2112  ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4;
2113
2114#
2115# Make sure that time functions that in 10.2 used get_arg0_time()
2116# do not mix days to hours for dates with zero YYYYMM and non-zero days.
2117#
2118
2119SELECT
2120  HOUR(TIMESTAMP'0000-00-01 10:00:00') AS h0,
2121  TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00') AS tts0,
2122  TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00.1') AS tts1,
2123  CAST(TIMESTAMP'0000-00-01 10:00:00' AS TIME) AS c0,
2124  CAST(TIMESTAMP'0000-00-01 10:00:00.1' AS TIME(1)) AS c2;
2125
2126SET TIMESTAMP=DEFAULT;
2127
2128--echo #
2129--echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)
2130--echo #
2131
2132SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03');
2133SELECT
2134  LAST_DAY(TIME'00:00:00') AS c1,
2135  CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2,
2136  CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3;
2137SET TIMESTAMP=DEFAULT;
2138
2139
2140--echo #
2141--echo # MDEV-15702 Remove the use of STRING_ITEM from Item_func_date_format::fix_length_and_dec()
2142--echo #
2143
2144--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2145SELECT DATE_FORMAT('2001-01-01',POINT(1,1));
2146--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2147SELECT DATE_FORMAT(POINT(1,1),'10');
2148--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2149SELECT DATE_FORMAT('2001-01-01',ROW(1,1));
2150--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2151SELECT DATE_FORMAT(ROW(1,1),'10');
2152
2153--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2154SELECT DATE_FORMAT('2001-01-01','%Y',POINT(1,1));
2155--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
2156SELECT DATE_FORMAT('2001-01-01','%Y',ROW(1,1));
2157
2158SELECT DATE_FORMAT('2001-01-01','%Y',@unknown_user_variable);
2159
2160CREATE TABLE t1 AS SELECT
2161  DATE_FORMAT('2001-01-01',NULL) AS c0,
2162  DATE_FORMAT('2001-01-01','10') AS c1,
2163  DATE_FORMAT('2001-01-01',10) AS c2,
2164  DATE_FORMAT('2001-01-01',10.0) AS c3,
2165  DATE_FORMAT('2001-01-01',10e0) AS c4,
2166  DATE_FORMAT('2001-01-01',TIME'10:20:30') AS c5;
2167SHOW CREATE TABLE t1;
2168DROP TABLE t1;
2169
2170EXECUTE IMMEDIATE
2171"CREATE TABLE t1 AS SELECT
2172   DATE_FORMAT('2001-01-01',?) AS c0,
2173   DATE_FORMAT('2001-01-01',?) AS c1,
2174   DATE_FORMAT('2001-01-01',?) AS c2,
2175   DATE_FORMAT('2001-01-01',?) AS c3,
2176   DATE_FORMAT('2001-01-01',?) AS c4,
2177   DATE_FORMAT('2001-01-01',?) AS c5"
2178USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30';
2179SHOW CREATE TABLE t1;
2180DROP TABLE t1;
2181
2182--echo #
2183--echo # MDEV-16152 Expressions with INTERVAL return bad results in some cases
2184--echo #
2185
2186SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
2187      -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
2188
2189SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
2190       INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
2191      +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
2192
2193EXPLAIN EXTENDED SELECT
2194  TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1,
2195  -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2;
2196
2197EXPLAIN EXTENDED SELECT
2198  TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1,
2199  INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2,
2200  +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3;
2201
2202############
2203# MDEV-13727
2204# Current timestamp functions inside stored functions must return the
2205# value of the top-level statement's timestamp (its start time).
2206# This must hold regardless of @@slow_query_log option.
2207#
2208
2209CREATE TABLE t_ts   (a timestamp(6));
2210CREATE TABLE t_trig (a timestamp(6));
2211delimiter //;
2212CREATE FUNCTION fn_sleep_before_now() returns int
2213BEGIN
2214  INSERT INTO t_ts SET a= current_timestamp(6);
2215  RETURN 0;
2216END//
2217CREATE TRIGGER trg_insert_t_ts after INSERT on t_ts for each row
2218BEGIN
2219  INSERT into t_trig set a= current_timestamp(6);
2220END//
2221delimiter ;//
2222
2223SET @sav_slow_query_log= @@session.slow_query_log;
2224
2225# @@slow_query_log ON check
2226SET @@session.slow_query_log= ON;
2227SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func;
2228
2229SELECT a FROM t_ts   LIMIT 1 into @ts_func;
2230SELECT a FROM t_trig LIMIT 1 into @ts_trig;
2231if (!`SELECT @ts_cur = @ts_func and @ts_func = @ts_trig`)
2232{
2233  SELECT @ts_cur, @ts_func, @ts_trig;
2234  --die Error: timestamps must be equal but they diverge
2235}
2236DELETE FROM t_ts;
2237DELETE FROM t_trig;
2238
2239# @@slow_query_log OFF check
2240SET @@session.slow_query_log= OFF;
2241SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts;
2242SELECT a FROM t_ts   LIMIT 1 into @ts_func;
2243SELECT a FROM t_trig LIMIT 1 into @ts_trig;
2244if (!`SELECT @ts_cur = @ts_func and @ts_func = @ts_trig`)
2245{
2246  SELECT @ts_cur, @ts_func, @ts_trig;
2247  --die Error: timestamps must be equal but they diverge
2248}
2249
2250# Cleanup
2251SET @@session.slow_query_log= @sav_slow_query_log;
2252DROP FUNCTION fn_sleep_before_now;
2253DROP TRIGGER trg_insert_t_ts;
2254DROP TABLE t_ts, t_trig;
2255
2256#
2257# End of MDEV-13727
2258###################
2259
2260
2261--echo #
2262--echo # MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer
2263--echo #
2264
2265CREATE TABLE t1 (a TIME);
2266INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
2267SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
2268SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09';
2269SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
2270SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
2271EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
2272DROP TABLE t1;
2273
2274
2275--echo #
2276--echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)
2277--echo #
2278
2279set timestamp=unix_timestamp('2018-08-02 10:10:10');
2280SELECT
2281  LAST_DAY(TIME'00:00:00') AS c1,
2282  CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2,
2283  CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3;
2284
2285--echo #
2286--echo # End of 10.3 tests
2287--echo #
2288
2289--echo #
2290--echo # MDEV-14032 SEC_TO_TIME executes side effect two times
2291--echo #
2292
2293SET @a=10000000;
2294SELECT SEC_TO_TIME(@a:=@a+1);
2295SELECT @a;
2296
2297CREATE TABLE t1 (a TEXT);
2298DELIMITER $$;
2299CREATE FUNCTION f1() RETURNS INT
2300BEGIN
2301  INSERT INTO t1 VALUES ('f1 was called');
2302  RETURN 10000000;
2303END;
2304$$
2305DELIMITER ;$$
2306SELECT SEC_TO_TIME(f1());
2307SELECT * FROM t1;
2308DROP TABLE t1;
2309DROP FUNCTION f1;
2310
2311--echo #
2312--echo # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999
2313--echo #
2314
2315--echo # Reject anything that's parsed as DATETIME or DATE
2316
2317CREATE TABLE t1 (a VARCHAR(64));
2318INSERT INTO t1 VALUES
2319('2001-01-01 10:20:30'),
2320('01-01-01 10:20:30'),
2321('2001-01-01       '),
2322('20010101102030'),
2323('010101102030');
2324SELECT ADDTIME(DATE'2001-01-01',a), a FROM t1;
2325DROP TABLE t1;
2326
2327
2328--vertical_results
2329
2330--echo # GREATEST(decimal, time)
2331
2332SELECT
2333  GREATEST(8395959, TIME'00:00:00') AS c0,
2334  GREATEST(8395959.0, TIME'00:00:00') AS c1,
2335  GREATEST(8395959.00, TIME'00:00:00') AS c2,
2336  GREATEST(8395959.000, TIME'00:00:00') AS c3,
2337  GREATEST(8395959.0000, TIME'00:00:00') AS c4,
2338  GREATEST(8395959.00000, TIME'00:00:00') AS c5,
2339  GREATEST(8395959.000000, TIME'00:00:00') AS c6,
2340  GREATEST(8395959.0000000, TIME'00:00:00') AS c7;
2341
2342SELECT
2343  MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c0,
2344  MICROSECOND(GREATEST(8395959.0, TIME'00:00:00')) AS c1,
2345  MICROSECOND(GREATEST(8395959.00, TIME'00:00:00')) AS c2,
2346  MICROSECOND(GREATEST(8395959.000, TIME'00:00:00')) AS c3,
2347  MICROSECOND(GREATEST(8395959.0000, TIME'00:00:00')) AS c4,
2348  MICROSECOND(GREATEST(8395959.00000, TIME'00:00:00')) AS c5,
2349  MICROSECOND(GREATEST(8395959.000000, TIME'00:00:00')) AS c6,
2350  MICROSECOND(GREATEST(8395959.0000000, TIME'00:00:00')) AS c7;
2351
2352SELECT
2353  CAST(GREATEST(8395959, TIME'00:00:00') AS SIGNED) AS ci,
2354  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,0)) AS c0,
2355  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,1)) AS c1,
2356  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,2)) AS c2,
2357  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,3)) AS c3,
2358  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,4)) AS c4,
2359  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,5)) AS c5,
2360  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,6)) AS c6,
2361  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,7)) AS c7;
2362
2363SELECT
2364  GREATEST(8395959, TIME'00:00:00') AS ci,
2365  GREATEST(8395959, TIME'00:00:00')+0 AS c0,
2366  GREATEST(8395959, TIME'00:00:00')+0.0 AS c1,
2367  GREATEST(8395959, TIME'00:00:00')+0.00 AS c2,
2368  GREATEST(8395959, TIME'00:00:00')+0.000 AS c3,
2369  GREATEST(8395959, TIME'00:00:00')+0.0000 AS c4,
2370  GREATEST(8395959, TIME'00:00:00')+0.00000 AS c5,
2371  GREATEST(8395959, TIME'00:00:00')+0.000000 AS c6,
2372  GREATEST(8395959, TIME'00:00:00')+0.0000000 AS c7;
2373
2374
2375
2376--echo # GREATEST(string, time)
2377
2378SELECT
2379  GREATEST('839:59:59', TIME'00:00:00') AS ci,
2380  GREATEST('839:59:59.0', TIME'00:00:00') AS c1,
2381  GREATEST('839:59:59.00', TIME'00:00:00') AS c2,
2382  GREATEST('839:59:59.000', TIME'00:00:00') AS c3,
2383  GREATEST('839:59:59.0000', TIME'00:00:00') AS c4,
2384  GREATEST('839:59:59.00000', TIME'00:00:00') AS c5,
2385  GREATEST('839:59:59.000000', TIME'00:00:00') AS c6,
2386  GREATEST('839:59:59.0000000', TIME'00:00:00') AS c7;
2387
2388SELECT
2389  MICROSECOND(GREATEST('839:59:59', TIME'00:00:00')) AS ci,
2390  MICROSECOND(GREATEST('839:59:59.0', TIME'00:00:00')) AS c1,
2391  MICROSECOND(GREATEST('839:59:59.00', TIME'00:00:00')) AS c2,
2392  MICROSECOND(GREATEST('839:59:59.000', TIME'00:00:00')) AS c3,
2393  MICROSECOND(GREATEST('839:59:59.0000', TIME'00:00:00')) AS c4,
2394  MICROSECOND(GREATEST('839:59:59.00000', TIME'00:00:00')) AS c5,
2395  MICROSECOND(GREATEST('839:59:59.000000', TIME'00:00:00')) AS c6,
2396  MICROSECOND(GREATEST('839:59:59.0000000', TIME'00:00:00')) AS c7;
2397
2398SELECT
2399  CAST(GREATEST('839:59:59', TIME'00:00:00') AS SIGNED) AS ci,
2400  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,0)) AS c0,
2401  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,1)) AS c1,
2402  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,2)) AS c2,
2403  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,3)) AS c3,
2404  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,4)) AS c4,
2405  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,5)) AS c5,
2406  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,6)) AS c6,
2407  CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,7)) AS c7;
2408
2409SELECT
2410  GREATEST('839:59:59', TIME'00:00:00') AS ci,
2411  GREATEST('839:59:59', TIME'00:00:00')+0 AS c0,
2412  GREATEST('839:59:59', TIME'00:00:00')+0.0 AS c1,
2413  GREATEST('839:59:59', TIME'00:00:00')+0.00 AS c2,
2414  GREATEST('839:59:59', TIME'00:00:00')+0.000 AS c3,
2415  GREATEST('839:59:59', TIME'00:00:00')+0.0000 AS c4,
2416  GREATEST('839:59:59', TIME'00:00:00')+0.00000 AS c5,
2417  GREATEST('839:59:59', TIME'00:00:00')+0.000000 AS c6,
2418  GREATEST('839:59:59', TIME'00:00:00')+0.0000000 AS c7;
2419
2420
2421--echo # ADDTIME(datetime, decimal)
2422
2423SELECT
2424  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS c0,
2425  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0) AS c1,
2426  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00) AS c2,
2427  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000) AS c3,
2428  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000) AS c4,
2429  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000) AS c5,
2430  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000) AS c6,
2431  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000) AS c7;
2432
2433SELECT
2434  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c0,
2435  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0)) AS c1,
2436  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00)) AS c2,
2437  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000)) AS c3,
2438  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000)) AS c4,
2439  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000)) AS c5,
2440  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000)) AS c6,
2441  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000)) AS c7;
2442
2443SELECT
2444  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS SIGNED) AS ci,
2445  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,0)) AS c0,
2446  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,1)) AS c1,
2447  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,2)) AS c2,
2448  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,3)) AS c3,
2449  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,4)) AS c4,
2450  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,5)) AS c5,
2451  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,6)) AS c6,
2452  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,7)) AS c7;
2453
2454SELECT
2455  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS ci,
2456  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0 AS c0,
2457  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0 AS c1,
2458  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00 AS c2,
2459  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000 AS c3,
2460  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000 AS c4,
2461  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00000 AS c5,
2462  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000000 AS c6,
2463  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000000 AS c7;
2464
2465--echo # ADDTIME(datetime, string)
2466
2467SELECT
2468  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS c0,
2469  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0') AS c1,
2470  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00') AS c2,
2471  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000') AS c3,
2472  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000') AS c4,
2473  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000') AS c5,
2474  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000') AS c6,
2475  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000') AS c7;
2476
2477SELECT
2478  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')) AS c0,
2479  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0')) AS c1,
2480  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00')) AS c2,
2481  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000')) AS c3,
2482  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000')) AS c4,
2483  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000')) AS c5,
2484  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000')) AS c6,
2485  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000')) AS c7;
2486
2487SELECT
2488  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS SIGNED) AS ci,
2489  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0,
2490  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1,
2491  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2,
2492  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3,
2493  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4,
2494  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5,
2495  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6,
2496  CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7;
2497
2498SELECT
2499  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS ci,
2500  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0 AS c0,
2501  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0 AS c1,
2502  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00 AS c2,
2503  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000 AS c3,
2504  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000 AS c4,
2505  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00000 AS c5,
2506  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000000 AS c6,
2507  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000000 AS c7;
2508
2509--echo # ADDTIME(time, decimal)
2510
2511SELECT
2512  ADDTIME(TIME'00:00:00', 8395959) AS c0,
2513  ADDTIME(TIME'00:00:00', 8395959.0) AS c1,
2514  ADDTIME(TIME'00:00:00', 8395959.00) AS c2,
2515  ADDTIME(TIME'00:00:00', 8395959.000) AS c3,
2516  ADDTIME(TIME'00:00:00', 8395959.0000) AS c4,
2517  ADDTIME(TIME'00:00:00', 8395959.00000) AS c5,
2518  ADDTIME(TIME'00:00:00', 8395959.000000) AS c6,
2519  ADDTIME(TIME'00:00:00', 8395959.0000000) AS c7;
2520
2521SELECT
2522  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959)) AS c0,
2523  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0)) AS c1,
2524  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00)) AS c2,
2525  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000)) AS c3,
2526  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000)) AS c4,
2527  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00000)) AS c5,
2528  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000000)) AS c6,
2529  MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000000)) AS c7;
2530
2531SELECT
2532  CAST(ADDTIME(TIME'00:00:00', 8395959) AS SIGNED) AS ci,
2533  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,0)) AS c0,
2534  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,1)) AS c1,
2535  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,2)) AS c2,
2536  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,3)) AS c3,
2537  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,4)) AS c4,
2538  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,5)) AS c5,
2539  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,6)) AS c6,
2540  CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,7)) AS c7;
2541
2542SELECT
2543  ADDTIME(TIME'00:00:00', 8395959) AS ci,
2544  ADDTIME(TIME'00:00:00', 8395959)+0 AS c0,
2545  ADDTIME(TIME'00:00:00', 8395959)+0.0 AS c1,
2546  ADDTIME(TIME'00:00:00', 8395959)+0.00 AS c2,
2547  ADDTIME(TIME'00:00:00', 8395959)+0.000 AS c3,
2548  ADDTIME(TIME'00:00:00', 8395959)+0.0000 AS c4,
2549  ADDTIME(TIME'00:00:00', 8395959)+0.00000 AS c5,
2550  ADDTIME(TIME'00:00:00', 8395959)+0.000000 AS c6,
2551  ADDTIME(TIME'00:00:00', 8395959)+0.0000000 AS c7;
2552
2553--echo # ADDTIME(time,string)
2554
2555SELECT
2556  ADDTIME(TIME'00:00:00', '839:59:59') AS c0,
2557  ADDTIME(TIME'00:00:00', '839:59:59.0') AS c1,
2558  ADDTIME(TIME'00:00:00', '839:59:59.00') AS c2,
2559  ADDTIME(TIME'00:00:00', '839:59:59.000') AS c3,
2560  ADDTIME(TIME'00:00:00', '839:59:59.0000') AS c4,
2561  ADDTIME(TIME'00:00:00', '839:59:59.00000') AS c5,
2562  ADDTIME(TIME'00:00:00', '839:59:59.000000') AS c6,
2563  ADDTIME(TIME'00:00:00', '839:59:59.0000000') AS c7;
2564
2565SELECT
2566  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59')) AS c0,
2567  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0')) AS c1,
2568  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00')) AS c2,
2569  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000')) AS c3,
2570  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000')) AS c4,
2571  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00000')) AS c5,
2572  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000000')) AS c6,
2573  MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000000')) AS c7;
2574
2575SELECT
2576  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS SIGNED) AS ci,
2577  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0,
2578  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1,
2579  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2,
2580  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3,
2581  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4,
2582  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5,
2583  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6,
2584  CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7;
2585
2586SELECT
2587  ADDTIME(TIME'00:00:00', '839:59:59') AS ci,
2588  ADDTIME(TIME'00:00:00', '839:59:59')+0 AS c0,
2589  ADDTIME(TIME'00:00:00', '839:59:59')+0.0 AS c1,
2590  ADDTIME(TIME'00:00:00', '839:59:59')+0.00 AS c2,
2591  ADDTIME(TIME'00:00:00', '839:59:59')+0.000 AS c3,
2592  ADDTIME(TIME'00:00:00', '839:59:59')+0.0000 AS c4,
2593  ADDTIME(TIME'00:00:00', '839:59:59')+0.00000 AS c5,
2594  ADDTIME(TIME'00:00:00', '839:59:59')+0.000000 AS c6,
2595  ADDTIME(TIME'00:00:00', '839:59:59')+0.0000000 AS c7;
2596
2597--echo # ADDTIME(int,int)
2598SELECT
2599  ADDTIME(0, 8395959) AS c,
2600  MICROSECOND(ADDTIME(0, 8395959)) AS cm,
2601  CAST(ADDTIME(0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384,
2602  CAST(ADDTIME(0, 8395959) AS DECIMAL(30,0)) AS cd300;
2603
2604SELECT
2605  ADDTIME(20010101000000, 8395959) AS c,
2606  MICROSECOND(ADDTIME(20010101000000, 8395959)) AS cm,
2607  CAST(ADDTIME(20010101000000, 8395959) AS SIGNED) AS cs_fixme_mdev_17384,
2608  CAST(ADDTIME(20010101000000, 8395959) AS DECIMAL(30,0)) AS cd300;
2609
2610--echo # ADDTIME(decimal,int)
2611--echo # 8385960 in cd300 is correct: addtime returns '838:59:59.9'
2612--echo # which is further *rounded* to a decimals(30,0)
2613SELECT
2614  ADDTIME(0.0, 8395959) AS c,
2615  MICROSECOND(ADDTIME(0.0, 8395959)) AS cm,
2616  CAST(ADDTIME(0.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384,
2617  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS cd300;
2618
2619SELECT
2620  ADDTIME(20010101000000.0, 8395959) AS c,
2621  MICROSECOND(ADDTIME(20010101000000.0, 8395959)) AS cm,
2622  CAST(ADDTIME(20010101000000.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384,
2623  CAST(ADDTIME(20010101000000.0, 8395959) AS DECIMAL(30,0)) AS cd300;
2624
2625
2626--echo # ADDTIME(decimal,decimal)
2627
2628SELECT
2629  ADDTIME(0.0, 8395959.0) AS c1,
2630  ADDTIME(0.0, 8395959.00) AS c2,
2631  ADDTIME(0.0, 8395959.000) AS c3,
2632  ADDTIME(0.0, 8395959.0000) AS c4,
2633  ADDTIME(0.0, 8395959.00000) AS c5,
2634  ADDTIME(0.0, 8395959.000000) AS c6,
2635  ADDTIME(0.0, 8395959.0000000) AS c7;
2636
2637SELECT
2638  MICROSECOND(ADDTIME(0.0, 8395959.0)) AS c1,
2639  MICROSECOND(ADDTIME(0.0, 8395959.00)) AS c2,
2640  MICROSECOND(ADDTIME(0.0, 8395959.000)) AS c3,
2641  MICROSECOND(ADDTIME(0.0, 8395959.0000)) AS c4,
2642  MICROSECOND(ADDTIME(0.0, 8395959.00000)) AS c5,
2643  MICROSECOND(ADDTIME(0.0, 8395959.000000)) AS c6,
2644  MICROSECOND(ADDTIME(0.0, 8395959.0000000)) AS c7;
2645
2646--echo # 8385960 in c1 is correct: addtime returns '838:59:59.9'
2647--echo # which is further *rounded* to a decimals(30,0)
2648SELECT
2649  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS c0,
2650  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,1)) AS c1,
2651  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,2)) AS c2,
2652  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,3)) AS c3,
2653  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,4)) AS c4,
2654  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,5)) AS c5,
2655  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,6)) AS c6,
2656  CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,7)) AS c7;
2657
2658SELECT
2659  ADDTIME(0.0, 8395959)+0 AS c0,
2660  ADDTIME(0.0, 8395959)+0.0 AS c1,
2661  ADDTIME(0.0, 8395959)+0.00 AS c2,
2662  ADDTIME(0.0, 8395959)+0.000 AS c3,
2663  ADDTIME(0.0, 8395959)+0.0000 AS c4,
2664  ADDTIME(0.0, 8395959)+0.00000 AS c5,
2665  ADDTIME(0.0, 8395959)+0.000000 AS c6,
2666  ADDTIME(0.0, 8395959)+0.0000000 AS c7;
2667
2668
2669--echo # TIMESTAMP(string,decimal)
2670
2671SELECT
2672  TIMESTAMP('2001-01-01', 8395959) AS ci,
2673  TIMESTAMP('2001-01-01', 8395959.0) AS c1,
2674  TIMESTAMP('2001-01-01', 8395959.00) AS c2,
2675  TIMESTAMP('2001-01-01', 8395959.000) AS c3,
2676  TIMESTAMP('2001-01-01', 8395959.0000) AS c4,
2677  TIMESTAMP('2001-01-01', 8395959.00000) AS c5,
2678  TIMESTAMP('2001-01-01', 8395959.000000) AS c6,
2679  TIMESTAMP('2001-01-01', 8395959.0000000) AS c7;
2680
2681SELECT
2682  MICROSECOND(TIMESTAMP('2001-01-01', 8395959)) AS ci,
2683  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0)) AS c1,
2684  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00)) AS c2,
2685  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000)) AS c3,
2686  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000)) AS c4,
2687  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00000)) AS c5,
2688  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000000)) AS c6,
2689  MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000000)) AS c7;
2690
2691SELECT
2692  CAST(TIMESTAMP('2001-01-01', 8395959) AS SIGNED) AS ci,
2693  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,0)) AS c0,
2694  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,1)) AS c1,
2695  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,2)) AS c2,
2696  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,3)) AS c3,
2697  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,4)) AS c4,
2698  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,5)) AS c5,
2699  CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,6)) AS c6;
2700
2701SELECT
2702  TIMESTAMP('2001-01-01', 8395959) AS ci,
2703  TIMESTAMP('2001-01-01', 8395959)+0 AS c0,
2704  TIMESTAMP('2001-01-01', 8395959)+0.0 AS c1,
2705  TIMESTAMP('2001-01-01', 8395959)+0.00 AS c2,
2706  TIMESTAMP('2001-01-01', 8395959)+0.000 AS c3,
2707  TIMESTAMP('2001-01-01', 8395959)+0.0000 AS c4,
2708  TIMESTAMP('2001-01-01', 8395959)+0.00000 AS c5,
2709  TIMESTAMP('2001-01-01', 8395959)+0.000000 AS c6,
2710  TIMESTAMP('2001-01-01', 8395959)+0.0000000 AS c7;
2711
2712--echo # TIMESTAMP(string,string)
2713
2714SELECT
2715  TIMESTAMP('2001-01-01', '839:59:59') AS ci,
2716  TIMESTAMP('2001-01-01', '839:59:59.0') AS c1,
2717  TIMESTAMP('2001-01-01', '839:59:59.00') AS c2,
2718  TIMESTAMP('2001-01-01', '839:59:59.000') AS c3,
2719  TIMESTAMP('2001-01-01', '839:59:59.0000') AS c4,
2720  TIMESTAMP('2001-01-01', '839:59:59.00000') AS c5,
2721  TIMESTAMP('2001-01-01', '839:59:59.000000') AS c6,
2722  TIMESTAMP('2001-01-01', '839:59:59.0000000') AS c7;
2723
2724SELECT
2725  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59')) AS ci,
2726  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0')) AS c1,
2727  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00')) AS c2,
2728  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000')) AS c3,
2729  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000')) AS c4,
2730  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00000')) AS c5,
2731  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000000')) AS c6,
2732  MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000000')) AS c7;
2733
2734SELECT
2735  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS SIGNED) AS ci,
2736  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,0)) AS c0,
2737  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,1)) AS c1,
2738  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,2)) AS c2,
2739  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,3)) AS c3,
2740  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,4)) AS c4,
2741  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,5)) AS c5,
2742  CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,6)) AS c6;
2743
2744SELECT
2745  TIMESTAMP('2001-01-01', '839:59:59') AS ci,
2746  TIMESTAMP('2001-01-01', '839:59:59')+0 AS c0,
2747  TIMESTAMP('2001-01-01', '839:59:59')+0.0 AS c1,
2748  TIMESTAMP('2001-01-01', '839:59:59')+0.00 AS c2,
2749  TIMESTAMP('2001-01-01', '839:59:59')+0.000 AS c3,
2750  TIMESTAMP('2001-01-01', '839:59:59')+0.0000 AS c4,
2751  TIMESTAMP('2001-01-01', '839:59:59')+0.00000 AS c5,
2752  TIMESTAMP('2001-01-01', '839:59:59')+0.000000 AS c6,
2753  TIMESTAMP('2001-01-01', '839:59:59')+0.0000000 AS c7;
2754
2755--horizontal_results
2756
2757--echo # Corner cases for TIMESTAMP(timestamp,xxx)
2758
2759--echo # HOUR is outside of supported INTERVAL DAYS TO SECONDS range
2760--echo # Expect NULL with INTERVAL warnings
2761CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64));
2762INSERT INTO t1 VALUES ('4294967296:00:00', '178956970 16:00:00');
2763INSERT INTO t1 VALUES ('4294967295:59:59', '178956970 15:59:59');
2764INSERT INTO t1 VALUES ('4294967294:59:59', '178956970 14:59:59');
2765INSERT INTO t1 VALUES ('87649416:00:00', '3652059 00:00:00');
2766SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1;
2767DROP TABLE t1;
2768
2769CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64));
2770INSERT INTO t1 VALUES ('-4294967296:00:00', '-178956970 16:00:00');
2771INSERT INTO t1 VALUES ('-4294967295:59:59', '-178956970 15:59:59');
2772INSERT INTO t1 VALUES ('-4294967294:59:59', '-178956970 14:59:59');
2773INSERT INTO t1 VALUES ('-87649416:00:00', '-3652059 00:00:00');
2774SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999', b) AS tb FROM t1;
2775DROP TABLE t1;
2776
2777--echo # HOUR is OK
2778--echo # Expect max or near-max DATETIME value + no INTERVAL warnings
2779CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64));
2780INSERT INTO t1 VALUES ('87649415:59:59.999999', '3652058 23:59:59.999999');
2781INSERT INTO t1 VALUES ('87649415:59:59', '3652058 23:59:59');
2782SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1;
2783DROP TABLE t1;
2784
2785--echo # HOUR is OK
2786--echo # Expect near '0001-01-01 00:00:00' DATETIME value + no INTERVAL warnings
2787CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64));
2788INSERT INTO t1 VALUES ('-87649415:59:59.999999', '-3652058 23:59:59.999999');
2789INSERT INTO t1 VALUES ('-87649415:59:59', '-3652058 23:59:59');
2790SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999', b) AS tb FROM t1;
2791DROP TABLE t1;
2792
2793--echo # HOUR is OK
2794--echo # Expect NULL on datetime arithmetic overflow + no INTERVAL warnings
2795CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64));
2796INSERT INTO t1 VALUES ('-00:00:00.000001', '-0 00:00:00.000001');
2797SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1;
2798DROP TABLE t1;
2799
2800
2801--echo # Corner cases for ADDTIME(timestamp,xxx)
2802--vertical_results
2803
2804--echo # HOUR is outside of UINT_MAX32 range
2805--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2806
2807SELECT
2808  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959) AS ci,
2809  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0 AS c0,
2810  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0 AS c1,
2811  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00 AS c2,
2812  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000 AS c3,
2813  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000 AS c4,
2814  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00000 AS c5,
2815  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000000 AS c6,
2816  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000000 AS c7;
2817
2818SELECT
2819  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59') AS ci,
2820  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0 AS c0,
2821  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0 AS c1,
2822  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00 AS c2,
2823  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000 AS c3,
2824  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000 AS c4,
2825  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00000 AS c5,
2826  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000000 AS c6,
2827  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000000 AS c7;
2828
2829## TODO: add '0001-01-01 00:00:00'
2830
2831--echo # HOUR UINT_MAX32
2832--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2833SELECT
2834  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959) AS ci,
2835  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0 AS c0,
2836  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0 AS c1,
2837  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00 AS c2,
2838  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000 AS c3,
2839  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000 AS c4,
2840  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00000 AS c5,
2841  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000000 AS c6,
2842  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000000 AS c7;
2843
2844SELECT
2845  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59') AS ci,
2846  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0 AS c0,
2847  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0 AS c1,
2848  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00 AS c2,
2849  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000 AS c3,
2850  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000 AS c4,
2851  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00000 AS c5,
2852  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000000 AS c6,
2853  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000000 AS c7;
2854
2855--echo # HOUR is max_useful_hour()+1
2856--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2857SELECT
2858  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959) AS ci,
2859  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0 AS c0,
2860  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0 AS c1,
2861  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00 AS c2,
2862  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000 AS c3,
2863  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000 AS c4,
2864  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00000 AS c5,
2865  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000000 AS c6,
2866  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000000 AS c7;
2867
2868SELECT
2869  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59') AS ci,
2870  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0 AS c0,
2871  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0 AS c1,
2872  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00 AS c2,
2873  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000 AS c3,
2874  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000 AS c4,
2875  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00000 AS c5,
2876  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000000 AS c6,
2877  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000000 AS c7;
2878
2879--echo # HOUR is max_useful_hour()
2880--echo # Expect NULL (calc_time_diff overflows ) + no INTERVAL warnings
2881SELECT
2882  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959) AS ci,
2883  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0 AS c0,
2884  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0 AS c1,
2885  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00 AS c2,
2886  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000 AS c3,
2887  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000 AS c4,
2888  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00000 AS c5,
2889  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000000 AS c6,
2890  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000000 AS c7;
2891
2892SELECT
2893  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59') AS ci,
2894  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0 AS c0,
2895  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1,
2896  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2,
2897  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3,
2898  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4,
2899  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5,
2900  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6,
2901  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7;
2902
2903--echo # HOUR is max_useful_hour()
2904--echo # Expect non-NULL + no warnings
2905SELECT
2906  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959) AS ci,
2907  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0 AS c0,
2908  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0 AS c1,
2909  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00 AS c2,
2910  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000 AS c3,
2911  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000 AS c4,
2912  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00000 AS c5,
2913  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000000 AS c6,
2914  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000000 AS c7;
2915
2916SELECT
2917  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59') AS ci,
2918  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0 AS c0,
2919  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1,
2920  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2,
2921  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3,
2922  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4,
2923  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5,
2924  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6,
2925  ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7;
2926--horizontal_results
2927
2928
2929--echo # Corner cases for ADDTIME(time,xxx)
2930--vertical_results
2931
2932--echo # HOUR outside of UINT32 range
2933--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2934SELECT
2935  ADDTIME(TIME'00:00:00', 42949672965959) AS ci,
2936  ADDTIME(TIME'00:00:00', 42949672965959)+0 AS c0,
2937  ADDTIME(TIME'00:00:00', 42949672965959)+0.0 AS c1,
2938  ADDTIME(TIME'00:00:00', 42949672965959)+0.00 AS c2,
2939  ADDTIME(TIME'00:00:00', 42949672965959)+0.000 AS c3,
2940  ADDTIME(TIME'00:00:00', 42949672965959)+0.0000 AS c4,
2941  ADDTIME(TIME'00:00:00', 42949672965959)+0.00000 AS c5,
2942  ADDTIME(TIME'00:00:00', 42949672965959)+0.000000 AS c6,
2943  ADDTIME(TIME'00:00:00', 42949672965959)+0.0000000 AS c7;
2944
2945SELECT
2946  ADDTIME(TIME'00:00:00', '4294967296:59:59') AS ci,
2947  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0 AS c0,
2948  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0 AS c1,
2949  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00 AS c2,
2950  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000 AS c3,
2951  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000 AS c4,
2952  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00000 AS c5,
2953  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000000 AS c6,
2954  ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000000 AS c7;
2955
2956--echo # HOUR is UINT_MAX32 (outside of INTERVAL DAY TO SECOND range)
2957--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2958SELECT
2959  ADDTIME(TIME'00:00:00', 42949672955959) AS ci,
2960  ADDTIME(TIME'00:00:00', 42949672955959)+0 AS c0,
2961  ADDTIME(TIME'00:00:00', 42949672955959)+0.0 AS c1,
2962  ADDTIME(TIME'00:00:00', 42949672955959)+0.00 AS c2,
2963  ADDTIME(TIME'00:00:00', 42949672955959)+0.000 AS c3,
2964  ADDTIME(TIME'00:00:00', 42949672955959)+0.0000 AS c4,
2965  ADDTIME(TIME'00:00:00', 42949672955959)+0.00000 AS c5,
2966  ADDTIME(TIME'00:00:00', 42949672955959)+0.000000 AS c6,
2967  ADDTIME(TIME'00:00:00', 42949672955959)+0.0000000 AS c7;
2968
2969SELECT
2970  ADDTIME(TIME'00:00:00', '4294967295:59:59') AS ci,
2971  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0 AS c0,
2972  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0 AS c1,
2973  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00 AS c2,
2974  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000 AS c3,
2975  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0000 AS c4,
2976  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00000 AS c5,
2977  ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000000 AS c6,
2978  ADDTIME(TIME'00:00:00', '4294967295;00:00')+0.0000000 AS c7;
2979
2980--echo # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range)
2981--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
2982
2983SELECT
2984  ADDTIME(TIME'00:00:00', 876494165959) AS ci,
2985  ADDTIME(TIME'00:00:00', 876494165959)+0 AS c0,
2986  ADDTIME(TIME'00:00:00', 876494165959)+0.0 AS c1,
2987  ADDTIME(TIME'00:00:00', 876494165959)+0.00 AS c2,
2988  ADDTIME(TIME'00:00:00', 876494165959)+0.000 AS c3,
2989  ADDTIME(TIME'00:00:00', 876494165959)+0.0000 AS c4,
2990  ADDTIME(TIME'00:00:00', 876494165959)+0.00000 AS c5,
2991  ADDTIME(TIME'00:00:00', 876494165959)+0.000000 AS c6,
2992  ADDTIME(TIME'00:00:00', 876494165959)+0.0000000 AS c7;
2993
2994SELECT
2995  ADDTIME(TIME'00:00:00', '87649416:59:59') AS ci,
2996  ADDTIME(TIME'00:00:00', '87649416:59:59')+0 AS c0,
2997  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0 AS c1,
2998  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00 AS c2,
2999  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000 AS c3,
3000  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000 AS c4,
3001  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00000 AS c5,
3002  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000000 AS c6,
3003  ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000000 AS c7;
3004
3005--echo # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range)
3006--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value"
3007
3008SELECT
3009  ADDTIME(TIME'-838:59:59', 876494165959) AS ci,
3010  ADDTIME(TIME'-838:59:59.9', 876494165959) AS c1,
3011  ADDTIME(TIME'-838:59:59.99', 876494165959) AS c2,
3012  ADDTIME(TIME'-838:59:59.999', 876494165959) AS c3,
3013  ADDTIME(TIME'-838:59:59.9999', 876494165959) AS c4,
3014  ADDTIME(TIME'-838:59:59.99999', 876494165959) AS c5,
3015  ADDTIME(TIME'-838:59:59.999999', 876494165959) AS c6;
3016
3017SELECT
3018  ADDTIME(TIME'-838:59:59', '87649416:59:59') AS ci,
3019  ADDTIME(TIME'-838:59:59.9', '87649416:59:59') AS c1,
3020  ADDTIME(TIME'-838:59:59.99', '87649416:59:59') AS c2,
3021  ADDTIME(TIME'-838:59:59.999', '87649416:59:59') AS c3,
3022  ADDTIME(TIME'-838:59:59.9999', '87649416:59:59') AS c4,
3023  ADDTIME(TIME'-838:59:59.99999', '87649416:59:59') AS c5,
3024  ADDTIME(TIME'-838:59:59.999999', '87649416:59:59') AS c6;
3025
3026# This does not give a warning about nanosecond truncation in --ps runs
3027# so disable warnings
3028--disable_warnings
3029SELECT
3030  ADDTIME(TIME'-838:59:59.9999999', '87649416:59:59') AS c7;
3031--enable_warnings
3032
3033--echo # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range)
3034--echo # Expect max TIME(0) + zero fraction + TIME warnings + no INTEVAL warnings
3035SELECT
3036  ADDTIME(TIME'00:00:00', 876494155959) AS ci,
3037  ADDTIME(TIME'00:00:00', 876494155959)+0 AS c0,
3038  ADDTIME(TIME'00:00:00', 876494155959)+0.0 AS c1,
3039  ADDTIME(TIME'00:00:00', 876494155959)+0.00 AS c2,
3040  ADDTIME(TIME'00:00:00', 876494155959)+0.000 AS c3,
3041  ADDTIME(TIME'00:00:00', 876494155959)+0.0000 AS c4,
3042  ADDTIME(TIME'00:00:00', 876494155959)+0.00000 AS c5,
3043  ADDTIME(TIME'00:00:00', 876494155959)+0.000000 AS c6,
3044  ADDTIME(TIME'00:00:00', 876494155959)+0.0000000 AS c7;
3045
3046SELECT
3047  ADDTIME(TIME'00:00:00', '87649415:59:59') AS ci,
3048  ADDTIME(TIME'00:00:00', '87649415:59:59')+0 AS c0,
3049  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0 AS c1,
3050  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00 AS c2,
3051  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000 AS c3,
3052  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000 AS c4,
3053  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00000 AS c5,
3054  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000000 AS c6,
3055  ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000000 AS c7;
3056
3057
3058--echo # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range)
3059--echo # Expect max TIME(N) + TIME warnings + no INTERVAL warnings
3060
3061SELECT
3062  ADDTIME(TIME'-838:59:59', 876494155959) AS ci,
3063  ADDTIME(TIME'-838:59:59.9', 876494155959) AS c1,
3064  ADDTIME(TIME'-838:59:59.99', 876494155959) AS c2,
3065  ADDTIME(TIME'-838:59:59.999', 876494155959) AS c3,
3066  ADDTIME(TIME'-838:59:59.9999', 876494155959) AS c4,
3067  ADDTIME(TIME'-838:59:59.99999', 876494155959) AS c5,
3068  ADDTIME(TIME'-838:59:59.999999', 876494155959) AS c6;
3069
3070SELECT
3071  ADDTIME(TIME'-838:59:59', '87649415:59:59') AS ci,
3072  ADDTIME(TIME'-838:59:59.9', '87649415:59:59') AS c1,
3073  ADDTIME(TIME'-838:59:59.99', '87649415:59:59') AS c2,
3074  ADDTIME(TIME'-838:59:59.999', '87649415:59:59') AS c3,
3075  ADDTIME(TIME'-838:59:59.9999', '87649415:59:59') AS c4,
3076  ADDTIME(TIME'-838:59:59.99999', '87649415:59:59') AS c5,
3077  ADDTIME(TIME'-838:59:59.999999', '87649415:59:59') AS c6;
3078
3079# This does not give a warning about nanosecond truncation in --ps runs
3080# so disable warnings
3081--disable_warnings
3082SELECT
3083  ADDTIME(TIME'-838:59:59.9999999', '87649415:59:59') AS c7;
3084--enable_warnings
3085
3086--horizontal_results
3087
3088
3089--echo #
3090--echo # MDEV-17400 The result of TIME('42949672965959-01') depends on architecture
3091--echo #
3092
3093SELECT TIME('42949672955959-01'), TIME('42949672965959-01');
3094SELECT TIME('18446744073709551615-01'),  TIME('18446744073709551616-01');
3095
3096--echo #
3097--echo # MDEV-17434 EXTRACT(DAY FROM negative_time) returns wrong result
3098--echo #
3099
3100CREATE TABLE t1 (a TIME(6));
3101INSERT INTO t1 VALUES ('-24:10:10.10');
3102SELECT
3103  EXTRACT(MINUTE FROM a),
3104  EXTRACT(SECOND FROM a),
3105  EXTRACT(MICROSECOND FROM a),
3106  EXTRACT(DAY FROM a),
3107  EXTRACT(DAY_HOUR FROM a),
3108  EXTRACT(DAY_MINUTE FROM a),
3109  EXTRACT(DAY_SECOND FROM a),
3110  EXTRACT(DAY_MICROSECOND FROM a)
3111FROM t1;
3112CREATE TABLE t2 AS
3113SELECT
3114  EXTRACT(MINUTE FROM a),
3115  EXTRACT(SECOND FROM a),
3116  EXTRACT(MICROSECOND FROM a),
3117  EXTRACT(DAY FROM a),
3118  EXTRACT(DAY_HOUR FROM a),
3119  EXTRACT(DAY_MINUTE FROM a),
3120  EXTRACT(DAY_SECOND FROM a),
3121  EXTRACT(DAY_MICROSECOND FROM a)
3122FROM t1;
3123SHOW CREATE TABLE t2;
3124DROP TABLE t2;
3125DROP TABLE t1;
3126
3127
3128--echo #
3129--echo # MDEV-17478 Wrong result for TIME('+100:20:30')
3130--echo #
3131
3132SELECT TIME('+100:20:30');
3133
3134--echo #
3135--echo # MDEV-17477 Wrong result for TIME('-2001-01-01 10:20:30')
3136--echo #
3137
3138SELECT TIME('-2001-01-01 10:20:30');
3139SELECT TIME('2001-01-01') AS c1, TIME('2001-01-01  ') AS c2;
3140SELECT TIME('0001:01:01 '), TIME('0001:01:01  ');
3141SELECT TIME('1 2'), TIME('1 2 ');
3142
3143SELECT TIME('2001-01-01T'), TIME('2001-01-01T ');
3144SELECT TIME('901-01-01T1'), TIME('901-01-01T10');
3145SELECT TIME('091-01-01T1'), TIME('091-01-01T10');
3146
3147SELECT TIME('0001:01:01x'), TIME('0001:01:01xx');
3148SELECT TIME('0001:01:01.'), TIME('0001:01:01..');
3149SELECT TIME('0001:01:01-'), TIME('0001:01:01--');
3150SELECT TIME('0001:01:01-'), TIME('0001:01:01--');
3151
3152SELECT TIME('-xxx'), TIME('-xxxxxxxxxxxxxxxxxxxx');
3153SELECT TIME('-      '), TIME('-                         ');
3154SELECT TIME('-'), TIME('-');
3155SELECT TIME('1-1-1 1:1:1'), TIME('1-1-1 1:1:1.0');
3156SELECT TIME('1-1-1 1:2:3'), TIME('1-1-1 1:2:3.0');
3157
3158SELECT
3159  CAST('20050326112233 garbage' as datetime),
3160  CAST('20050326 garbage' as date),
3161  CAST('50326 garbage' as time);
3162
3163SELECT TIME('- 01:00:00'), TIME('- 1 01:00:00');
3164
3165
3166--echo #
3167--echo # MDEV-17854 Assertion `decimals <= 6' failed in my_time_fraction_remainder on SELECT with NULLIF and FROM_UNIXTIME on incorrect time
3168--echo #
3169
3170SET time_zone='+00:00';
3171SELECT NULLIF(FROM_UNIXTIME('foo'), '2012-12-12 21:10:14');
3172SET time_zone=DEFAULT;
3173
3174
3175--echo #
3176--echo # MDEV-18402 Assertion `sec.sec() <= 59' failed in Item_func_maketime::get_date
3177--echo #
3178
3179SELECT MAKETIME('01', '01', LEAST( -100, NULL ));
3180SELECT CONCAT(MAKETIME('01', '01', LEAST( -100, NULL )));
3181
3182
3183--echo #
3184--echo # MDEV-19774 Assertion `sec.se c() <= 0x7FFFFFFFL' failed in Item_func_from_unixtime::get_date
3185--echo #
3186
3187SELECT FROM_UNIXTIME(LEAST(3696610869, NULL));
3188