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 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