EVOLUTION-MANAGER

Edit File: func_time.result

drop table if exists t1,t2,t3; set time_zone="+03:00"; select 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"); 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") 1996-01-01 31 1 5 select period_add("9602",-12),period_diff(199505,"9404") ; period_add("9602",-12) period_diff(199505,"9404") 199502 13 select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) 0 0 0 select 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; 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 1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) 02:30:01 23001 54742 00:04:57.4235 select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); sec_to_time(9001.1) time_to_sec('15:12:22.123456') time_to_sec(15.5566778899) 02:30:01.1 54742.123456 15.556677 select sec_to_time(time_to_sec('-838:59:59')); sec_to_time(time_to_sec('-838:59:59')) -838:59:59 select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); sec_to_time('9001.1') sec_to_time('1234567890123.123') 02:30:01.100000 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '1234567890123.123' select sec_to_time(-9001.1), sec_to_time(-9001.1) / 1, sec_to_time(-9001.1) / 1e0, sec_to_time(-9001) div 1; sec_to_time(-9001.1) sec_to_time(-9001.1) / 1 sec_to_time(-9001.1) / 1e0 sec_to_time(-9001) div 1 -02:30:01.1 -23001.10000 -23001.1 -23001 select sec_to_time(90011e-1), sec_to_time(1234567890123e30); sec_to_time(90011e-1) sec_to_time(1234567890123e30) 02:30:01.100000 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '1.234567890123e42' select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999'); sec_to_time(1234567890123) sec_to_time('99999999999999999999999999999') 838:59:59 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '1234567890123' Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' select now()-curdate()*1000000-curtime(); now()-curdate()*1000000-curtime() 0 select strcmp(current_timestamp(),concat(current_date()," ",current_time())); strcmp(current_timestamp(),concat(current_date()," ",current_time())) 0 select strcmp(localtime(),concat(current_date()," ",current_time())); strcmp(localtime(),concat(current_date()," ",current_time())) 0 select strcmp(localtimestamp(),concat(current_date()," ",current_time())); strcmp(localtimestamp(),concat(current_date()," ",current_time())) 0 select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"); date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w") January Thursday 2nd 1997 97 01 02 03 04 05 4 select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w")); date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w")) January Thursday 2nd 1997 97 01 02 12 00 00 4 select dayofmonth("1997-01-02"),dayofmonth(19970323); dayofmonth("1997-01-02") dayofmonth(19970323) 2 23 select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31"); month("1997-01-02") year("98-02-03") dayofyear("1997-12-31") 1 1998 365 select month("2001-02-00"),year("2001-00-00"); month("2001-02-00") year("2001-00-00") 2 2001 select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303); DAYOFYEAR("1997-03-03") WEEK("1998-03-03") QUARTER(980303) 62 9 1 select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322); HOUR("1997-03-03 23:03:22") MINUTE("23:03:22") SECOND(230322) 23 3 22 select TIME(230322), TIME(230322.33), TIME("230322.33"); TIME(230322) TIME(230322.33) TIME("230322.33") 23:03:22 23:03:22.33 23:03:22.33 select week(19980101),week(19970101),week(19980101,1),week(19970101,1); week(19980101) week(19970101) week(19980101,1) week(19970101,1) 0 0 1 1 select week(19981231),week(19971231),week(19981231,1),week(19971231,1); week(19981231) week(19971231) week(19981231,1) week(19971231,1) 52 52 53 53 select week(19950101),week(19950101,1); week(19950101) week(19950101,1) 1 0 select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1); yearweek('1981-12-31',1) yearweek('1982-01-01',1) yearweek('1982-12-31',1) yearweek('1983-01-01',1) 198153 198153 198252 198252 select yearweek('1987-01-01',1),yearweek('1987-01-01'); yearweek('1987-01-01',1) yearweek('1987-01-01') 198701 198652 select 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'; 2000 2001 2002 2003 2004 2005 2006 0 0 0 0 0 0 1 select 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'; 2000 2001 2002 2003 2004 2005 2006 1 0 1 1 1 1 1 select 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'; 2000 2001 2002 2003 2004 2005 2006 0 1 1 1 1 0 0 select 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'; 2000 2001 2002 2003 2004 2005 2006 1 1 1 2 2 1 1 select 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'; 2000 2001 2002 2003 2004 2005 2006 199952 200053 200152 200252 200352 200452 200601 select 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'; 2000 2001 2002 2003 2004 2005 2006 200001 200053 200201 200301 200401 200501 200601 select 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'; 2000 2001 2002 2003 2004 2005 2006 199952 200101 200201 200301 200401 200453 200552 select 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'; 2000 2001 2002 2003 2004 2005 2006 200001 200101 200201 200302 200402 200501 200601 select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3); week(19981231,2) week(19981231,3) week(20000101,2) week(20000101,3) 52 53 52 52 select week(20001231,2),week(20001231,3); week(20001231,2) week(20001231,3) 53 52 select 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'; 0 1 2 3 4 5 6 7 52 53 52 53 52 52 52 52 select 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'; 0 1 2 3 4 5 6 7 0 0 52 52 0 0 52 52 select 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'; 0 1 2 3 4 5 6 7 1 1 1 1 1 1 1 1 select 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'; 0 1 2 3 4 5 6 7 53 52 53 52 53 52 1 52 select 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'; 0 1 2 3 4 5 6 7 0 1 53 1 1 1 1 1 select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7); yearweek(20001231,0) yearweek(20001231,1) yearweek(20001231,2) yearweek(20001231,3) yearweek(20001231,4) yearweek(20001231,5) yearweek(20001231,6) yearweek(20001231,7) 200053 200052 200053 200052 200101 200052 200101 200052 set default_week_format = 6; select week(20001231), week(20001231,6); week(20001231) week(20001231,6) 1 1 set default_week_format = 0; set default_week_format = 2; select week(20001231),week(20001231,2),week(20001231,0); week(20001231) week(20001231,2) week(20001231,0) 53 53 53 set default_week_format = 0; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v') 1998-53 1998-53 select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); date_format('1999-12-31','%x-%v') date_format('2000-01-01','%x-%v') 1999-52 1999-52 select dayname("1962-03-03"),dayname("1962-03-03")+0; dayname("1962-03-03") dayname("1962-03-03")+0 Saturday 5 select monthname("1972-03-04"),monthname("1972-03-04")+0; monthname("1972-03-04") monthname("1972-03-04")+0 March 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'March' select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 select time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 select time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 select time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 select 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'); 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') 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6 select 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'); 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') NULL select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND) 1998-01-01 00:00:00 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE) 1998-01-01 00:00:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR) 1998-01-01 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); date_add("1997-12-31 23:59:59",INTERVAL 1 DAY) 1998-01-01 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH) 1998-01-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR) 1998-12-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND) 1998-01-01 00:01:00 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE) 1998-01-01 01:00:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR) 1998-01-02 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH) 1999-01-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND) 1998-01-01 01:01:00 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE) 1998-01-02 01:00:59 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND) 1998-01-02 01:01:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND) 1997-12-31 23:59:59 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE) 1997-12-31 23:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR) 1997-12-31 23:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY) 1997-12-31 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH) 1997-12-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR) 1997-01-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND) 1997-12-31 23:58:59 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE) 1997-12-31 22:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR) 1997-12-30 23:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH) 1996-12-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND) 1997-12-31 22:58:59 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE) 1997-12-30 22:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND) 1997-12-30 22:58:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND) 1998-01-02 03:46:39 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE) 1997-10-23 13:19:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR) 2009-05-29 15:59:59 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY) 1724-03-17 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND) 1998-01-07 22:40:00 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE) 1996-11-10 07:58:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR) 2025-05-19 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH) 1897-11-30 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND) 1999-02-21 17:40:38 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE) 1970-08-11 19:20:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND) 2025-05-23 04:40:38 select "1997-12-31 23:59:59" + INTERVAL 1 SECOND; "1997-12-31 23:59:59" + INTERVAL 1 SECOND 1998-01-01 00:00:00 select INTERVAL 1 DAY + "1997-12-31"; INTERVAL 1 DAY + "1997-12-31" 1998-01-01 select "1998-01-01 00:00:00" - INTERVAL 1 SECOND; "1998-01-01 00:00:00" - INTERVAL 1 SECOND 1997-12-31 23:59:59 select date_sub("1998-01-02",INTERVAL 31 DAY); date_sub("1998-01-02",INTERVAL 31 DAY) 1997-12-02 select date_add("1997-12-31",INTERVAL 1 SECOND); date_add("1997-12-31",INTERVAL 1 SECOND) 1997-12-31 00:00:01 select date_add("1997-12-31",INTERVAL 1 DAY); date_add("1997-12-31",INTERVAL 1 DAY) 1998-01-01 select date_add(NULL,INTERVAL 100000 SECOND); date_add(NULL,INTERVAL 100000 SECOND) NULL select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND) NULL select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND) NULL select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND) NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' select date_add('1998-01-30',Interval 1 month); date_add('1998-01-30',Interval 1 month) 1998-02-28 select date_add('1998-01-30',Interval '2:1' year_month); date_add('1998-01-30',Interval '2:1' year_month) 2000-02-29 select date_add('1996-02-29',Interval '1' year); date_add('1996-02-29',Interval '1' year) 1997-02-28 select extract(YEAR FROM "1999-01-02 10:11:12"); extract(YEAR FROM "1999-01-02 10:11:12") 1999 select extract(YEAR_MONTH FROM "1999-01-02"); extract(YEAR_MONTH FROM "1999-01-02") 199901 select extract(DAY FROM "1999-01-02"); extract(DAY FROM "1999-01-02") 2 select extract(DAY_HOUR FROM "1999-01-02 10:11:12"); extract(DAY_HOUR FROM "1999-01-02 10:11:12") 210 select extract(DAY_MINUTE FROM "02 10:11:12"); extract(DAY_MINUTE FROM "02 10:11:12") 21011 select extract(DAY_SECOND FROM "225 10:11:12"); extract(DAY_SECOND FROM "225 10:11:12") 34225959 Warnings: Warning 1292 Truncated incorrect time value: '225 10:11:12' select extract(HOUR FROM "1999-01-02 10:11:12"); extract(HOUR FROM "1999-01-02 10:11:12") 10 select extract(HOUR_MINUTE FROM "10:11:12"); extract(HOUR_MINUTE FROM "10:11:12") 1011 select extract(HOUR_SECOND FROM "10:11:12"); extract(HOUR_SECOND FROM "10:11:12") 101112 select extract(MINUTE FROM "10:11:12"); extract(MINUTE FROM "10:11:12") 11 select extract(MINUTE_SECOND FROM "10:11:12"); extract(MINUTE_SECOND FROM "10:11:12") 1112 select extract(SECOND FROM "1999-01-02 10:11:12"); extract(SECOND FROM "1999-01-02 10:11:12") 12 select extract(MONTH FROM "2001-02-00"); extract(MONTH FROM "2001-02-00") 2 SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter; quarter 4 SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter; quarter 4 SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter; quarter 4 SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 9999-12-31 00:00:00 SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 9999-12-31 00:00:00 SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND 1968-01-20 03:14:08 SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND 1968-01-20 03:15:07 SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND 8895-03-27 22:11:40 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND 6255-04-08 15:04:32 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE 5983-01-24 02:08:00 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR 2019-08-15 16:00:00 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND NULL Warnings: Warning 1441 Datetime function: datetime field overflow create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; ctime hour(ctime) 2001-01-12 12:23:40 12 select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; ctime 2001-01-12 12:23:40 drop table t1; create table t1 (id int); create table t2 (id int, date date); insert into t1 values (1); insert into t2 values (1, "0000-00-00"); insert into t1 values (2); insert into t2 values (2, "2000-01-01"); select monthname(date) from t1 inner join t2 on t1.id = t2.id; monthname(date) NULL January select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id; monthname(date) NULL January drop table t1,t2; CREATE TABLE t1 (updated text) ENGINE=MyISAM; INSERT INTO t1 VALUES (''); SELECT month(updated) from t1; month(updated) NULL Warnings: Warning 1292 Incorrect datetime value: '' SELECT year(updated) from t1; year(updated) NULL Warnings: Warning 1292 Incorrect datetime value: '' drop table t1; create table t1 (d date, dt datetime, t timestamp, c char(10)); insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1; dayofyear("0000-00-00") dayofyear(d) dayofyear(dt) dayofyear(t) dayofyear(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; dayofmonth("0000-00-00") dayofmonth(d) dayofmonth(dt) dayofmonth(t) dayofmonth(c) 0 0 0 0 0 select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1; month("0000-00-00") month(d) month(dt) month(t) month(c) 0 0 0 0 0 select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1; quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c) 0 0 0 0 0 select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; week("0000-00-00") week(d) week(dt) week(t) week(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; year("0000-00-00") year(d) year(dt) year(t) year(c) 0 0 0 0 0 select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 0 0 0 0 0 drop table t1; CREATE TABLE t1 ( start datetime default NULL); INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00'); CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL); INSERT INTO t2 VALUES (20021029165106,20021105164731); CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL); INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31"); select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 drop table t1,t2,t3; select @a:=FROM_UNIXTIME(1); @a:=FROM_UNIXTIME(1) 1970-01-01 03:00:01 select unix_timestamp(@a); unix_timestamp(@a) 1 select unix_timestamp('1969-12-01 19:00:01'); unix_timestamp('1969-12-01 19:00:01') NULL select from_unixtime(-1); from_unixtime(-1) NULL select from_unixtime(2147483647); from_unixtime(2147483647) 2038-01-19 06:14:07 select from_unixtime(2147483648); from_unixtime(2147483648) NULL select from_unixtime(0); from_unixtime(0) 1970-01-01 03:00:00 select unix_timestamp(from_unixtime(2147483647)); unix_timestamp(from_unixtime(2147483647)) 2147483647 select unix_timestamp(from_unixtime(2147483648)); unix_timestamp(from_unixtime(2147483648)) NULL select unix_timestamp('2039-01-20 01:00:00'); unix_timestamp('2039-01-20 01:00:00') NULL select unix_timestamp('1968-01-20 01:00:00'); unix_timestamp('1968-01-20 01:00:00') NULL select unix_timestamp('2038-02-10 01:00:00'); unix_timestamp('2038-02-10 01:00:00') NULL select unix_timestamp('1969-11-20 01:00:00'); unix_timestamp('1969-11-20 01:00:00') NULL select unix_timestamp('2038-01-20 01:00:00'); unix_timestamp('2038-01-20 01:00:00') NULL select unix_timestamp('1969-12-30 01:00:00'); unix_timestamp('1969-12-30 01:00:00') NULL select unix_timestamp('2038-01-17 12:00:00'); unix_timestamp('2038-01-17 12:00:00') 2147331600 select unix_timestamp('1970-01-01 03:00:01'); unix_timestamp('1970-01-01 03:00:01') 1 select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') NULL SELECT CHARSET(DAYNAME(19700101)); CHARSET(DAYNAME(19700101)) latin1 SELECT CHARSET(MONTHNAME(19700101)); CHARSET(MONTHNAME(19700101)) latin1 SELECT LOWER(DAYNAME(19700101)); LOWER(DAYNAME(19700101)) thursday SELECT LOWER(MONTHNAME(19700101)); LOWER(MONTHNAME(19700101)) january SELECT COERCIBILITY(MONTHNAME('1970-01-01')),COERCIBILITY(DAYNAME('1970-01-01')); COERCIBILITY(MONTHNAME('1970-01-01')) COERCIBILITY(DAYNAME('1970-01-01')) 4 4 CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time); INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08"); SELECT * from t1; datetime timestamp date time 2001-01-02 03:04:05 2002-01-02 03:04:05 2003-01-02 06:07:08 select date_add("1997-12-31",INTERVAL 1 SECOND); date_add("1997-12-31",INTERVAL 1 SECOND) 1997-12-31 00:00:01 select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH) 1999-01-31 select date_add(datetime, INTERVAL 1 SECOND) from t1; date_add(datetime, INTERVAL 1 SECOND) 2001-01-02 03:04:06 select date_add(datetime, INTERVAL 1 YEAR) from t1; date_add(datetime, INTERVAL 1 YEAR) 2002-01-02 03:04:05 select date_add(date,INTERVAL 1 SECOND) from t1; date_add(date,INTERVAL 1 SECOND) 2003-01-02 00:00:01 select date_add(date,INTERVAL 1 MINUTE) from t1; date_add(date,INTERVAL 1 MINUTE) 2003-01-02 00:01:00 select date_add(date,INTERVAL 1 HOUR) from t1; date_add(date,INTERVAL 1 HOUR) 2003-01-02 01:00:00 select date_add(date,INTERVAL 1 DAY) from t1; date_add(date,INTERVAL 1 DAY) 2003-01-03 select date_add(date,INTERVAL 1 MONTH) from t1; date_add(date,INTERVAL 1 MONTH) 2003-02-02 select date_add(date,INTERVAL 1 YEAR) from t1; date_add(date,INTERVAL 1 YEAR) 2004-01-02 select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; date_add(date,INTERVAL "1:1" MINUTE_SECOND) 2003-01-02 00:01:01 select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; date_add(date,INTERVAL "1:1" HOUR_MINUTE) 2003-01-02 01:01:00 select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; date_add(date,INTERVAL "1:1" DAY_HOUR) 2003-01-03 01:00:00 select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; date_add(date,INTERVAL "1 1" YEAR_MONTH) 2004-02-02 select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; date_add(date,INTERVAL "1:1:1" HOUR_SECOND) 2003-01-02 01:01:01 select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; date_add(date,INTERVAL "1 1:1" DAY_MINUTE) 2003-01-03 01:01:00 select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) 2003-01-03 01:01:01 select date_add(date,INTERVAL "1" WEEK) from t1; date_add(date,INTERVAL "1" WEEK) 2003-01-09 select date_add(date,INTERVAL "1" QUARTER) from t1; date_add(date,INTERVAL "1" QUARTER) 2003-04-02 select timestampadd(MINUTE, 1, date) from t1; timestampadd(MINUTE, 1, date) 2003-01-02 00:01:00 select timestampadd(WEEK, 1, date) from t1; timestampadd(WEEK, 1, date) 2003-01-09 select timestampadd(SQL_TSI_SECOND, 1, date) from t1; timestampadd(SQL_TSI_SECOND, 1, date) 2003-01-02 00:00:01 select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a; a 3 select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a; a -1 select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a; a -5 select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a; a 0 select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a; a 107 select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a; a 12 select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a; a 2136 select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a; a 89 select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a; a 128159 select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a; a 7689539 select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2, timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4; a1 a2 a3 a4 28 28 29 29 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27') 0 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28') 2 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29') 2 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27') 0 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28') 2 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29') 2 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27') 0 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28') 2 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29') 2 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27') 0 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28') 2 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29') 2 select date_add(time,INTERVAL 1 SECOND) from t1; date_add(time,INTERVAL 1 SECOND) 06:07:09 drop table t1; select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2, last_day('2003-03-32') as f3, last_day('2003-04-01') as f4, last_day('2001-01-01 01:01:01') as f5, last_day(NULL), last_day('2001-02-12'); f1 f2 f3 f4 f5 last_day(NULL) last_day('2001-02-12') 2000-02-29 2002-12-31 NULL 2003-04-30 2001-01-31 NULL 2001-02-28 Warnings: Warning 1292 Incorrect datetime value: '2003-03-32' create table t1 select last_day('2000-02-05') as a, from_days(to_days("960101")) as b; describe t1; Field Type Null Key Default Extra a date YES NULL b date YES NULL select * from t1; a b 2000-02-29 1996-01-01 drop table t1; select last_day('2000-02-05') as a, from_days(to_days("960101")) as b; a b 2000-02-29 1996-01-01 select date_add(last_day("1997-12-1"), INTERVAL 1 DAY); date_add(last_day("1997-12-1"), INTERVAL 1 DAY) 1998-01-01 select length(last_day("1997-12-1")); length(last_day("1997-12-1")) 10 select last_day("1997-12-1")+0; last_day("1997-12-1")+0 19971231 select last_day("1997-12-1")+0.0; last_day("1997-12-1")+0.0 19971231.0 select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0; strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0; strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0; strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0 1 select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0; strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0 1 select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0; strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0 1 select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0; strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0 1 explain 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); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select period_add('9602',-12) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03') AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,weekday(curdate()) - weekday(current_timestamp()) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time(time_to_sec('0:30:47') / 6.21) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `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')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,'1997-12-31 23:59:59' + interval 1 second AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,'1998-01-01 00:00:00' - interval 1 second AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,'1997-12-31' + interval 1 day AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,'1997-12-31 23:59:59' + interval 1 second AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` SET @TMP='2007-08-01 12:22:49'; CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ('2007-08-01 12:22:59'); INSERT INTO t1 VALUES ('2007-08-01 12:23:01'); INSERT INTO t1 VALUES ('2007-08-01 12:23:20'); SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1); count(*) 3 DROP TABLE t1; select last_day('2005-00-00'); last_day('2005-00-00') NULL select last_day('2005-00-01'); last_day('2005-00-01') NULL select last_day('2005-01-00'); last_day('2005-01-00') 2005-01-31 select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL set time_zone='-6:00'; create table t1(a timestamp); insert into t1 values (19691231190001); select * from t1; a 1969-12-31 19:00:01 drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); f1 2006-01-01 select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 select 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); f3 2006-01-01 12:01:01 select 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); f3 2006-01-01 12:01:01 select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; f1 2006-01-01 select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); f1 2006-01-01 select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); f1 Warnings: Warning 1292 Incorrect datetime value: 'zzz' select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); f1 2006-01-01 select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); f1 2006-01-02 drop table t1; create table t1 select now() - now(), curtime() - curtime(), sec_to_time(1) + 0, from_unixtime(1) + 0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `now() - now()` bigint(16) NOT NULL, `curtime() - curtime()` int(9) NOT NULL, `sec_to_time(1) + 0` int(9) DEFAULT NULL, `from_unixtime(1) + 0` bigint(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SELECT SEC_TO_TIME(3300000); SEC_TO_TIME(3300000) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3300000)+0; SEC_TO_TIME(3300000)+0 8385959 Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3600 * 4294967296); SEC_TO_TIME(3600 * 4294967296) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '15461882265600' SELECT TIME_TO_SEC('916:40:00'); TIME_TO_SEC('916:40:00') 3020399 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT ADDTIME('500:00:00', '416:40:00'); ADDTIME('500:00:00', '416:40:00') 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT ADDTIME('916:40:00', '416:40:00'); ADDTIME('916:40:00', '416:40:00') 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' Warning 1292 Truncated incorrect time value: '1255:39:59.999999' SELECT SUBTIME('916:40:00', '416:40:00'); SUBTIME('916:40:00', '416:40:00') 422:19:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT SUBTIME('-916:40:00', '416:40:00'); SUBTIME('-916:40:00', '416:40:00') -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-916:40:00' Warning 1292 Truncated incorrect time value: '-1255:39:59.999999' SELECT MAKETIME(916,0,0); MAKETIME(916,0,0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '916:00:00' SELECT MAKETIME(4294967296, 0, 0); MAKETIME(4294967296, 0, 0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '4294967296:00:00' SELECT MAKETIME(-4294967296, 0, 0); MAKETIME(-4294967296, 0, 0) -838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '-4294967296:00:00' SELECT MAKETIME(0, 4294967296, 0); MAKETIME(0, 4294967296, 0) NULL SELECT MAKETIME(0, 0, 4294967296); MAKETIME(0, 0, 4294967296) NULL SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0); MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) 838:59:59 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' SELECT EXTRACT(HOUR FROM '10000:02:03'); EXTRACT(HOUR FROM '10000:02:03') 22 Warnings: Warning 1292 Truncated incorrect time value: '10000:02:03' CREATE TABLE t1(f1 TIME); INSERT IGNORE INTO t1 VALUES('916:00:00 a'); Warnings: Warning 1265 Data truncated for column 'f1' at row 1 Warning 1264 Out of range value for column 'f1' at row 1 SELECT * FROM t1; f1 838:59:59 DROP TABLE t1; SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED)); SEC_TO_TIME(CAST(-1 AS UNSIGNED)) 838:59:59 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Note 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615' SET NAMES latin1; SET character_set_results = NULL; SHOW VARIABLES LIKE 'character_set_results'; Variable_name Value character_set_results CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY); INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd'); SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868; fmtddate field2 Sep-4 12:00AM abcd DROP TABLE testBug8868; SET NAMES DEFAULT; CREATE TABLE t1 ( a TIMESTAMP ); INSERT INTO t1 VALUES (now()), (now()); SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); 1 1 1 DROP TABLE t1; (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H); H 120 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H); H 120 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H); H 05 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H); H 5 select last_day('0000-00-00'); last_day('0000-00-00') NULL select isnull(week(now() + 0)), isnull(week(now() + 0.2)), week(20061108), week(20061108.01), week(20061108085411.000002); isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002) 0 0 45 45 45 Warnings: Warning 1292 Truncated incorrect datetime value: '20061108.01' End of 4.1 tests select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); drop function if exists t_slow_sysdate; create function t_slow_sysdate() returns timestamp begin do sleep(2); return sysdate(); end; // insert into t1 set a = sysdate(), b = t_slow_sysdate();// create trigger t_before before insert on t1 for each row begin set new.b = t_slow_sysdate(); end // insert into t1 set a = sysdate(); select a != b from t1; a != b 1 1 drop trigger t_before; drop function t_slow_sysdate; drop table t1; SET GLOBAL log_bin_trust_function_creators = 0; create table t1 (a datetime, i int, b datetime); insert into t1 select sysdate(), sleep(2), sysdate() from dual; select a != b from t1; a != b 1 drop table t1; create procedure t_sysdate() begin select sysdate() into @a; do sleep(2); select sysdate() into @b; select @a != @b; end; // call t_sysdate(); @a != @b 1 drop procedure t_sysdate; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; select timestampdiff(month,'2004-09-11','2004-09-11'); timestampdiff(month,'2004-09-11','2004-09-11') 0 select timestampdiff(month,'2004-09-11','2005-09-11'); timestampdiff(month,'2004-09-11','2005-09-11') 12 select timestampdiff(month,'2004-09-11','2006-09-11'); timestampdiff(month,'2004-09-11','2006-09-11') 24 select timestampdiff(month,'2004-09-11','2007-09-11'); timestampdiff(month,'2004-09-11','2007-09-11') 36 select timestampdiff(month,'2005-09-11','2004-09-11'); timestampdiff(month,'2005-09-11','2004-09-11') -12 select timestampdiff(month,'2005-09-11','2003-09-11'); timestampdiff(month,'2005-09-11','2003-09-11') -24 select timestampdiff(month,'2004-02-28','2005-02-28'); timestampdiff(month,'2004-02-28','2005-02-28') 12 select timestampdiff(month,'2004-02-29','2005-02-28'); timestampdiff(month,'2004-02-29','2005-02-28') 11 select timestampdiff(month,'2004-02-28','2005-02-28'); timestampdiff(month,'2004-02-28','2005-02-28') 12 select timestampdiff(month,'2004-03-29','2005-03-28'); timestampdiff(month,'2004-03-29','2005-03-28') 11 select timestampdiff(month,'2003-02-28','2004-02-29'); timestampdiff(month,'2003-02-28','2004-02-29') 12 select timestampdiff(month,'2003-02-28','2005-02-28'); timestampdiff(month,'2003-02-28','2005-02-28') 24 select timestampdiff(month,'1999-09-11','2001-10-10'); timestampdiff(month,'1999-09-11','2001-10-10') 24 select timestampdiff(month,'1999-09-11','2001-9-11'); timestampdiff(month,'1999-09-11','2001-9-11') 24 select timestampdiff(year,'1999-09-11','2001-9-11'); timestampdiff(year,'1999-09-11','2001-9-11') 2 select timestampdiff(year,'2004-02-28','2005-02-28'); timestampdiff(year,'2004-02-28','2005-02-28') 1 select timestampdiff(year,'2004-02-29','2005-02-28'); timestampdiff(year,'2004-02-29','2005-02-28') 0 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date); CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date); INSERT INTO t1 VALUES (1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01'); INSERT INTO t2 VALUES (1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15'); SELECT * FROM t1, t2 WHERE t1.day BETWEEN '2005.09.01' - INTERVAL 6 MONTH AND t2.day; id day id day 1 2005-06-01 1 2005-08-01 3 2005-07-01 1 2005-08-01 1 2005-06-01 2 2005-06-15 1 2005-06-01 3 2005-07-15 3 2005-07-01 3 2005-07-15 SELECT * FROM t1, t2 WHERE CAST(t1.day AS DATE) BETWEEN '2005.09.01' - INTERVAL 6 MONTH AND t2.day; id day id day 1 2005-06-01 1 2005-08-01 3 2005-07-01 1 2005-08-01 1 2005-06-01 2 2005-06-15 1 2005-06-01 3 2005-07-15 3 2005-07-01 3 2005-07-15 DROP TABLE t1,t2; set time_zone= @@global.time_zone; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE 22:10:00 select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute; str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute NULL Warnings: Warning 1292 Truncated incorrect date value: '1997-00-04 22:23:00' Warning 1292 Incorrect datetime value: '1997-00-04' create table t1 (field DATE); insert into t1 values ('2006-11-06'); select * from t1 where field < '2006-11-06 04:08:36.0'; field 2006-11-06 select * from t1 where field = '2006-11-06 04:08:36.0'; field select * from t1 where field = '2006-11-06'; field 2006-11-06 select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0'; field 2006-11-06 select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0'; field 2006-11-06 drop table t1; CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), (2, '11:00:00', '11:15:00', '1972-02-06'); SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) FROM t1; t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) 10:00:00 NULL NULL NULL 11:00:00 11:15:00 00:15:00 1 SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) FROM t1 ORDER BY a DESC; t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) 11:00:00 11:15:00 00:15:00 1 10:00:00 NULL NULL NULL DROP TABLE t1; SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1; TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") 838:59:58 838:59:59 set names latin1; create table t1 (a varchar(15) character set ascii not null); insert into t1 values ('070514-000000'); select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) # set names swe7; select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (swe7_swedish_ci,COERCIBLE) for operation 'concat' set names latin1; set lc_time_names=fr_FR; select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'concat' set lc_time_names=en_US; drop table t1; select DATE_ADD('20071108181000', INTERVAL 1 DAY); DATE_ADD('20071108181000', INTERVAL 1 DAY) 2007-11-09 18:10:00 select DATE_ADD(20071108181000, INTERVAL 1 DAY); DATE_ADD(20071108181000, INTERVAL 1 DAY) 2007-11-09 18:10:00 select DATE_ADD('20071108', INTERVAL 1 DAY); DATE_ADD('20071108', INTERVAL 1 DAY) 2007-11-09 select DATE_ADD(20071108, INTERVAL 1 DAY); DATE_ADD(20071108, INTERVAL 1 DAY) 2007-11-09 select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND 2007-12-30 23:59:59 select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond) 1000-01-02 03:02:01.050000 select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond) 1000-01-01 00:00:01.020000 # # Bug #52315 part 2 : utc_date() crashes when system time > year 2037 # SET TIMESTAMP=-147490000; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483648; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483646; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483647; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=0; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=-1; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=1; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=0; End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) 0049-12-31 23:59:59 select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND) 0198-12-31 23:59:59 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND) 0200-01-01 00:00:01 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 0199-12-31 23:59:59 select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND) 0200-01-01 00:00:00 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 0199-12-31 23:59:59 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR) 0001-01-01 23:59:59 select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND) 2049-12-31 23:59:59 select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND) 1989-12-31 23:59:59 select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND) 0068-12-31 23:59:59 select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND) 0168-12-31 23:59:59 CREATE TABLE t1(a DOUBLE NOT NULL); INSERT INTO t1 VALUES (0),(9.216e-096); # should not crash SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a); 1 1 1 DROP TABLE t1; # # Bug #52160: crash and inconsistent results when grouping # by a function and column # CREATE TABLE t1(a CHAR(10) NOT NULL); INSERT INTO t1 VALUES (''),(''); SELECT COUNT(*) FROM t1 GROUP BY TIME_TO_SEC(a); COUNT(*) 2 Warnings: Warning 1292 Truncated incorrect time value: '' Warning 1292 Truncated incorrect time value: '' Warning 1292 Truncated incorrect time value: '' DROP TABLE t1; # # Bug#11766112 59151:UNINITIALIZED VALUES IN EXTRACT_DATE_TIME WITH STR_TO_DATE(SPACE(..) ... # SELECT STR_TO_DATE(SPACE(2),'1'); STR_TO_DATE(SPACE(2),'1') 0000-00-00 # # Bug#11765216 58154: UNINITIALIZED VARIABLE FORMAT IN STR_TO_DATE FUNCTION # SET GLOBAL SQL_MODE=''; DO STR_TO_DATE((''), FROM_DAYS(@@GLOBAL.SQL_MODE)); SET GLOBAL SQL_MODE=DEFAULT; # # Bug#11766087 59125: VALGRIND UNINITIALISED VALUE WARNING IN ULL2DEC, LONGLONG2DECIMAL # SELECT FORMAT(YEAR(STR_TO_DATE('',GET_FORMAT(TIME,''))),1); FORMAT(YEAR(STR_TO_DATE('',GET_FORMAT(TIME,''))),1) NULL # # Bug#11766126 59166: ANOTHER DATETIME VALGRIND UNINITIALIZED WARNING # SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)); # # Bug#11766124 59164: VALGRIND: UNINITIALIZED VALUE IN NUMBER_TO_DATETIME # SELECT ADDDATE(MONTH(FROM_UNIXTIME(NULL)),INTERVAL 1 HOUR); ADDDATE(MONTH(FROM_UNIXTIME(NULL)),INTERVAL 1 HOUR) NULL # # Bug#11889186 60503: CRASH IN MAKE_DATE_TIME WITH DATE_FORMAT / STR_TO_DATE COMBINATION # SELECT DATE_FORMAT('0000-00-11', '%W'); DATE_FORMAT('0000-00-11', '%W') NULL SELECT DATE_FORMAT('0000-00-11', '%a'); DATE_FORMAT('0000-00-11', '%a') NULL SELECT DATE_FORMAT('0000-00-11', '%w'); DATE_FORMAT('0000-00-11', '%w') NULL # # Bug#12403504 AFTER FIX FOR #11889186 : ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0 # SELECT MAKEDATE(11111111,1); MAKEDATE(11111111,1) NULL SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1); WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1) NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' # # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0, # DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' # # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY # SIMILAR TO '2009-10-00' # SELECT DATE('20091000'), STR_TO_DATE('200910','%Y%m'), LAST_DAY('2009-10-00'), LAST_DAY(DATE('2009-10-00')), LAST_DAY(DATE'2009-10-00'), LAST_DAY(STR_TO_DATE('200910','%Y%m')), WEEK('2009-10-00'), WEEK(DATE('2009-10-00')), WEEK(DATE'2009-10-00'), WEEK(STR_TO_DATE('200910','%Y%m')), WEEKOFYEAR('2009-10-00'), WEEKOFYEAR(DATE('2009-10-00')), WEEKOFYEAR(DATE'2009-10-00'), WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')), DAYOFYEAR('2009-10-00'), DAYOFYEAR(DATE('2009-10-00')), DAYOFYEAR(DATE'2009-10-00'), DAYOFYEAR(STR_TO_DATE('200910','%Y%m')), WEEKDAY('2009-10-00'), WEEKDAY(DATE('2009-10-00')), WEEKDAY(DATE'2009-10-00'), WEEKDAY(STR_TO_DATE('200910','%Y%m')), TO_DAYs('2009-10-00'), TO_DAYs(DATE('2009-10-00')), TO_DAYs(DATE'2009-10-00'), TO_DAYs(STR_TO_DATE('200910','%Y%m')); DATE('20091000') 2009-10-00 STR_TO_DATE('200910','%Y%m') 2009-10-00 LAST_DAY('2009-10-00') 2009-10-31 LAST_DAY(DATE('2009-10-00')) 2009-10-31 LAST_DAY(DATE'2009-10-00') 2009-10-31 LAST_DAY(STR_TO_DATE('200910','%Y%m')) 2009-10-31 WEEK('2009-10-00') NULL WEEK(DATE('2009-10-00')) NULL WEEK(DATE'2009-10-00') NULL WEEK(STR_TO_DATE('200910','%Y%m')) NULL WEEKOFYEAR('2009-10-00') NULL WEEKOFYEAR(DATE('2009-10-00')) NULL WEEKOFYEAR(DATE'2009-10-00') NULL WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL DAYOFYEAR('2009-10-00') NULL DAYOFYEAR(DATE('2009-10-00')) NULL DAYOFYEAR(DATE'2009-10-00') NULL DAYOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL WEEKDAY('2009-10-00') NULL WEEKDAY(DATE('2009-10-00')) NULL WEEKDAY(DATE'2009-10-00') NULL WEEKDAY(STR_TO_DATE('200910','%Y%m')) NULL TO_DAYs('2009-10-00') NULL TO_DAYs(DATE('2009-10-00')) NULL TO_DAYs(DATE'2009-10-00') NULL TO_DAYs(STR_TO_DATE('200910','%Y%m')) NULL Warnings: Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date SELECT DATE('00000100'), STR_TO_DATE('000001','%Y%m'), LAST_DAY('0000-01-00'), LAST_DAY(DATE('0000-01-00')), LAST_DAY(DATE'0000-01-00'), LAST_DAY(STR_TO_DATE('000001','%Y%m')), WEEK('0000-01-00'), WEEK(DATE('0000-01-00')), WEEK(DATE'0000-01-00'), WEEK(STR_TO_DATE('000001','%Y%m')), WEEKOFYEAR('0000-01-00'), WEEKOFYEAR(DATE('0000-01-00')), WEEKOFYEAR(DATE'0000-01-00'), WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')), DAYOFYEAR('0000-01-00'), DAYOFYEAR(DATE('0000-01-00')), DAYOFYEAR(DATE'0000-01-00'), DAYOFYEAR(STR_TO_DATE('000001','%Y%m')), WEEKDAY('0000-01-00'), WEEKDAY(DATE('0000-01-00')), WEEKDAY(DATE'0000-01-00'), WEEKDAY(STR_TO_DATE('000001','%Y%m')), TO_DAYs('0000-01-00'), TO_DAYs(DATE('0000-01-00')), TO_DAYs(DATE'0000-01-00'), TO_DAYs(STR_TO_DATE('000001','%Y%m')); DATE('00000100') 0000-01-00 STR_TO_DATE('000001','%Y%m') 0000-01-00 LAST_DAY('0000-01-00') 0000-01-31 LAST_DAY(DATE('0000-01-00')) 0000-01-31 LAST_DAY(DATE'0000-01-00') 0000-01-31 LAST_DAY(STR_TO_DATE('000001','%Y%m')) 0000-01-31 WEEK('0000-01-00') NULL WEEK(DATE('0000-01-00')) NULL WEEK(DATE'0000-01-00') NULL WEEK(STR_TO_DATE('000001','%Y%m')) NULL WEEKOFYEAR('0000-01-00') NULL WEEKOFYEAR(DATE('0000-01-00')) NULL WEEKOFYEAR(DATE'0000-01-00') NULL WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL DAYOFYEAR('0000-01-00') NULL DAYOFYEAR(DATE('0000-01-00')) NULL DAYOFYEAR(DATE'0000-01-00') NULL DAYOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL WEEKDAY('0000-01-00') NULL WEEKDAY(DATE('0000-01-00')) NULL WEEKDAY(DATE'0000-01-00') NULL WEEKDAY(STR_TO_DATE('000001','%Y%m')) NULL TO_DAYs('0000-01-00') NULL TO_DAYs(DATE('0000-01-00')) NULL TO_DAYs(DATE'0000-01-00') NULL TO_DAYs(STR_TO_DATE('000001','%Y%m')) NULL Warnings: Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date End of 5.1 tests # # Bug#57039: constant subtime expression returns incorrect result. # CREATE TABLE t1 (`date_date` datetime NOT NULL); INSERT INTO t1 VALUES ('2008-01-03 00:00:00'), ('2008-01-03 00:00:00'); SELECT * FROM t1 WHERE date_date >= subtime(now(), "00:30:00"); date_date SELECT * FROM t1 WHERE date_date <= addtime(date_add("2000-1-1", INTERVAL "1:1:1" HOUR_SECOND), "00:20:00"); date_date DROP TABLE t1; # # Bug#57512 str_to_date crash... # SELECT WEEK(STR_TO_DATE(NULL,0)); WEEK(STR_TO_DATE(NULL,0)) NULL SELECT SUBDATE(STR_TO_DATE(NULL,0), INTERVAL 1 HOUR); SUBDATE(STR_TO_DATE(NULL,0), INTERVAL 1 HOUR) NULL # # BUG#59895 - setting storage engine to null segfaults mysqld # SELECT MONTHNAME(0), MONTHNAME(0) IS NULL, MONTHNAME(0) + 1; MONTHNAME(0) MONTHNAME(0) IS NULL MONTHNAME(0) + 1 NULL 1 NULL SET storage_engine=NULL; ERROR 42000: Variable 'storage_engine' can't be set to the value of 'NULL' # # BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP # Part1 (5.5) SET time_zone='+03:00'; CREATE TABLE t1 (a DATETIME NOT NULL); INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06'); INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00'); CREATE VIEW v1 AS SELECT * FROM t1; SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1; c1 NULL 1253421159.000 DROP VIEW v1; DROP TABLE t1; SET time_zone=DEFAULT; # # Bug #59686 crash in String::copy() with time data type # SELECT min(timestampadd(month, 1>'', from_days('%Z'))); min(timestampadd(month, 1>'', from_days('%Z'))) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '%Z' Warning 1292 Truncated incorrect DOUBLE value: '' Warning 1292 Incorrect datetime value: '0000-00-00' SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00'); create table t1(a time); insert into t1 values ('00:00:00'),('00:01:00'); select 1 from t1 where 1 < some (select cast(a as datetime) from t1); 1 1 1 Warnings: Warning 1292 Incorrect datetime value: '1' drop table t1; SET timestamp=DEFAULT; # # Bug #21564557: INCONSISTENT OUTPUT FROM 5.5 AND 5.6 # UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%M" # SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) NULL Warnings: Warning 1411 Incorrect datetime value: '201506' for function str_to_date SELECT UNIX_TIMESTAMP('2015-06-00'); UNIX_TIMESTAMP('2015-06-00') NULL Warnings: Warning 1292 Incorrect datetime value: '2015-06-00' SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) NULL Warnings: Warning 1411 Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date set sql_mode= 'TRADITIONAL'; SELECT @@sql_mode; @@sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) NULL Warnings: Warning 1411 Incorrect datetime value: '201506' for function str_to_date SELECT UNIX_TIMESTAMP('2015-06-00'); UNIX_TIMESTAMP('2015-06-00') NULL Warnings: Warning 1292 Incorrect datetime value: '2015-06-00' SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) NULL Warnings: Warning 1411 Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date set sql_mode= default; select time('10:10:10') > 10; time('10:10:10') > 10 1 select time('10:10:10') > 1010; time('10:10:10') > 1010 1 select time('10:10:09') > 101010; time('10:10:09') > 101010 0 select time('10:10:10') > 101010; time('10:10:10') > 101010 0 select time('10:10:11') > 101010; time('10:10:11') > 101010 1 select time(' 1 02:03:04') + interval 9 microsecond; time(' 1 02:03:04') + interval 9 microsecond 26:03:04.000009 select time(' 1 02:03:04') - interval 9 microsecond; time(' 1 02:03:04') - interval 9 microsecond 26:03:03.999991 select time('-1 02:03:04') + interval 9 microsecond; time('-1 02:03:04') + interval 9 microsecond -26:03:03.999991 select time('-1 02:03:04') - interval 9 microsecond; time('-1 02:03:04') - interval 9 microsecond -26:03:04.000009 select time(' 1 02:03:04') + interval '4:4:4' hour_second; time(' 1 02:03:04') + interval '4:4:4' hour_second 30:07:08 select time(' 1 02:03:04') - interval '4:4:4' hour_second; time(' 1 02:03:04') - interval '4:4:4' hour_second 21:59:00 select time('-1 02:03:04') + interval '4:4:4' hour_second; time('-1 02:03:04') + interval '4:4:4' hour_second -21:59:00 select time('-1 02:03:04') - interval '4:4:4' hour_second; time('-1 02:03:04') - interval '4:4:4' hour_second -30:07:08 select time(' 1 02:03:04') + interval 2 day; time(' 1 02:03:04') + interval 2 day 74:03:04 select time(' 1 02:03:04') - interval 2 day; time(' 1 02:03:04') - interval 2 day -21:56:56 select time('-1 02:03:04') + interval 2 day; time('-1 02:03:04') + interval 2 day 21:56:56 select time('-1 02:03:04') - interval 2 day; time('-1 02:03:04') - interval 2 day -74:03:04 select time('10 02:03:04') + interval 30 day; time('10 02:03:04') + interval 30 day NULL Warnings: Warning 1441 Datetime function: time field overflow select time('10 02:03:04') + interval 1 year; time('10 02:03:04') + interval 1 year NULL Warnings: Warning 1441 Datetime function: time field overflow select cast('131415.123e0' as time); cast('131415.123e0' as time) NULL Warnings: Warning 1292 Truncated incorrect time value: '131415.123e0' select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04'; cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04' 0 select least(time('1:2:3'), '01:02:04', null) div 1; least(time('1:2:3'), '01:02:04', null) div 1 NULL select truncate(least(time('1:2:3'), '01:02:04', null), 6); truncate(least(time('1:2:3'), '01:02:04', null), 6) NULL select cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1)); cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1)) NULL select unix_timestamp(null); unix_timestamp(null) NULL select truncate(date('2010-40-10'), 6); truncate(date('2010-40-10'), 6) NULL Warnings: Warning 1292 Incorrect datetime value: '2010-40-10' select extract(month from '2010-40-50'); extract(month from '2010-40-50') NULL Warnings: Warning 1292 Incorrect datetime value: '2010-40-50' select subtime('0000-00-10 10:10:10', '30 10:00:00'); subtime('0000-00-10 10:10:10', '30 10:00:00') NULL select cast(str_to_date(NULL, '%H:%i:%s') as time); cast(str_to_date(NULL, '%H:%i:%s') as time) NULL create table t1 (f1 datetime, key (f1)); insert into t1 values ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49'); select * from t1 where f1 > time('-23:00:06'); f1 drop table t1; select maketime(20,61,10)+0; maketime(20,61,10)+0 NULL create table t1 (f2 int not null) ; insert into t1 values (0),(0); select last_day(f2) from t1; last_day(f2) NULL NULL select last_day(f2) from t1 where last_day(f2) is null; last_day(f2) NULL NULL select * from t1 order by last_day (f2); f2 0 0 drop table t1; SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow') NULL SET timestamp=DEFAULT; create table t1 (f1 integer, f2 date); insert 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'); select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125); f1 f2 1 2011-05-05 2 2011-05-05 3 2011-05-05 4 2011-05-05 5 2011-05-05 drop table t1; create table t1 (f1 timestamp); insert into t1 values ('0000-00-00 00:00:00'); select least(1, f1) from t1; least(1, f1) 0000-00-00 00:00:00 Warnings: Warning 1292 Incorrect datetime value: '1' drop table t1; SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10'); select now() > coalesce(time('21:43:24'), date('2010-05-03')); now() > coalesce(time('21:43:24'), date('2010-05-03')) 0 SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22'); select now() > coalesce(time('21:43:24'), date('2010-05-03')); now() > coalesce(time('21:43:24'), date('2010-05-03')) 1 SET timestamp=DEFAULT; create table t1 (f1 timestamp); select * from t1 where f1 > f1 and f1 <=> timestampadd(hour, 9 , '2010-01-01 16:55:35'); f1 drop table t1; create table t1 (f1 date); insert into t1 values ('0000-00-00'); select timestampadd(week, 1, f1) from t1; timestampadd(week, 1, f1) NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' select timestampadd(week, 1, date("0000-00-00")); timestampadd(week, 1, date("0000-00-00")) NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' drop table t1; create table t1 (f2 time not null, f3 datetime, f4 int not null, f5 timestamp); insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00'); select least(greatest(f3, f2, f4), f5) from t1; least(greatest(f3, f2, f4), f5) 0000-00-00 00:00:00 drop table t1; select day(coalesce(null)); day(coalesce(null)) NULL select timestamp(greatest('2002-08-20', '0000-00-00 00:00:00')); timestamp(greatest('2002-08-20', '0000-00-00 00:00:00')) 2002-08-20 00:00:00 create table t1 (f1 datetime); insert into t1 values ('0000-00-00 00:00:00'); select cast(f1 AS time) from t1; cast(f1 AS time) 00:00:00 drop table t1; SET timestamp=UNIX_TIMESTAMP('2014-06-01 10:20:30'); select greatest(cast("0-0-0" as date), cast("10:20:05" as time)); greatest(cast("0-0-0" as date), cast("10:20:05" as time)) 2014-06-01 10:20:05 select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00'; greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00' 0 select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01'; greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01' 0 select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05'; greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05' 1 select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)); cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)) 2014-06-01 10:20:05.000000 SET timestamp=DEFAULT; select microsecond('12:00:00.123456'), microsecond('2009-12-31 23:59:59.000010'); microsecond('12:00:00.123456') microsecond('2009-12-31 23:59:59.000010') 123456 10 select now(258); ERROR 42000: Too big precision 258 specified for 'current_timestamp'. Maximum is 6 SELECT 1 FROM DUAL WHERE YEAR(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE MONTH(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE DAYOFMONTH(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE HOUR(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); 1 # # MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types # CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1; a 2005-05-04 Warnings: Warning 1292 Truncated incorrect time value: '2004-06-12' Warning 1292 Truncated incorrect time value: '2004-06-12' DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1; a 2000-02-23 2005-05-04 DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1; a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1; a 2005-05-04 2000-02-23 DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1; a 2000-02-23 2005-05-04 DROP TABLE t1; CREATE TABLE t1 AS SELECT STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only, STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only, STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond, STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time, STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond; SHOW COLUMNS FROM t1; Field Type Null Key Default Extra date_only date YES NULL time_only time YES NULL time_microsecond time(6) YES NULL date_time datetime YES NULL date_time_microsecond datetime(6) YES NULL DROP TABLE t1; CREATE TABLE t1 AS SELECT SEC_TO_TIME(1)+0.1, SEC_TO_TIME(1.1)+0.1, SEC_TO_TIME(1.12)+0.1, SEC_TO_TIME(1.123456)+0.1, SEC_TO_TIME(1.1234567)+0.1; SHOW COLUMNS FROM t1; Field Type Null Key Default Extra SEC_TO_TIME(1)+0.1 decimal(9,1) YES NULL SEC_TO_TIME(1.1)+0.1 decimal(9,1) YES NULL SEC_TO_TIME(1.12)+0.1 decimal(10,2) YES NULL SEC_TO_TIME(1.123456)+0.1 decimal(14,6) YES NULL SEC_TO_TIME(1.1234567)+0.1 decimal(14,6) YES NULL DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))*1; a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')))*1; a 2005-05-04 2000-02-23 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10'))); a 2005-05-04 2000-02-23 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' SELECT * FROM t1 GROUP BY ABS(FROM_UNIXTIME(concat(a,'10'))); a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' SELECT * FROM t1 GROUP BY @a:=(FROM_UNIXTIME(concat(a,'10'))*1); a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' DROP TABLE t1; SET TIME_ZONE='+02:00'; # # MDEV-6302 Wrong result set when using GROUP BY FROM_UNIXTIME(...)+0 # CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 AS f2 FROM t1; a f1 f2 2005-05-04 1970-01-01 02:33:25 19700101023325.000000 2000-02-23 1970-01-01 02:33:20 19700101023320.000000 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0; a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1; a 2000-02-23 2005-05-04 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2005-05-04'); SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; f2 0.000000 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1; f2 8 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f2` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; f2 0.000000 DROP TABLE t1,t2; # # MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) # SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) NULL Warnings: Warning 1411 Incorrect datetime value: '2020' for function str_to_date SET TIME_ZONE=DEFAULT; # # MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context # CREATE TABLE t1 (a TIMESTAMP(3)); INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); SELECT 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; c1 c2 c2 20010101102030 20010101102030.999 20010101102031 DROP TABLE t1; CREATE TABLE t1 (a TIME(3)); INSERT INTO t1 VALUES ('10:20:30.999'); SELECT 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; c1 c2 c2 102030 102030.999 102031 DROP TABLE t1; SELECT CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 SELECT CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 SELECT CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 SELECT CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 SELECT CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 SELECT CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; c1 c2 c3 c4 c5 c6 c7 20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 SELECT CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; c1 c2 c3 c4 c5 c6 c7 20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 SELECT CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; c1 c2 c3 c4 c5 c6 c7 20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 CREATE TABLE t1 AS SELECT CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1, CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2, CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3, CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` varchar(10) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, `c3` varchar(10) DEFAULT NULL, `c4` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types # SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE); INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01'); SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; CASE WHEN 0 THEN dt2 ELSE t3 END 2001-01-01 00:00:00.567 CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; SELECT * FROM t2; CASE WHEN 0 THEN dt2 ELSE t3 END 2001-01-01 00:00:00.567 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra CASE WHEN 0 THEN dt2 ELSE t3 END datetime(3) YES NULL DROP TABLE t2; SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1; CASE WHEN 1 THEN dt2 ELSE t3 END 2001-01-01 00:00:00.120 SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1; CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) 2001-01-01 00:00:00.120 SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1; CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) 2001-01-01 00:00:00.120 SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; CONCAT(CASE WHEN 1 THEN d ELSE t3 END) 2002-01-01 00:00:00.000 SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1; CASE WHEN 1 THEN t3 ELSE d END 2001-01-01 00:00:00.567 SELECT COALESCE(d, t3) FROM t1; COALESCE(d, t3) 2002-01-01 00:00:00.000 SELECT CONCAT(COALESCE(d, t3)) FROM t1; CONCAT(COALESCE(d, t3)) 2002-01-01 00:00:00.000 SELECT COALESCE(dt2, t3) FROM t1; COALESCE(dt2, t3) 2001-01-01 00:00:00.120 SELECT CONCAT(COALESCE(dt2, t3)) FROM t1; CONCAT(COALESCE(dt2, t3)) 2001-01-01 00:00:00.120 SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1; IFNULL(dt2, t3) CONCAT(IFNULL(dt2, t3)) 2001-01-01 00:00:00.120 2001-01-01 00:00:00.120 SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1; IFNULL(d, t3) CONCAT(IFNULL(d, t3)) 2002-01-01 00:00:00.000 2002-01-01 00:00:00.000 DROP TABLE t1; SET timestamp=DEFAULT; # # MDEV-4724 Some temporal functions do not preserve microseconds # SELECT MAKETIME(10,10,10.231); MAKETIME(10,10,10.231) 10:10:10.231 SELECT MAKETIME(0, 0, 59.9); MAKETIME(0, 0, 59.9) 00:00:59.9 CREATE TABLE t1 AS SELECT MAKETIME(10,00,00), MAKETIME(10,00,00.1), MAKETIME(10,00,00.12), MAKETIME(10,00,00.123), MAKETIME(10,00,00.1234), MAKETIME(10,00,00.12345), MAKETIME(10,00,00.123456); SHOW COLUMNS FROM t1; Field Type Null Key Default Extra MAKETIME(10,00,00) time YES NULL MAKETIME(10,00,00.1) time(1) YES NULL MAKETIME(10,00,00.12) time(2) YES NULL MAKETIME(10,00,00.123) time(3) YES NULL MAKETIME(10,00,00.1234) time(4) YES NULL MAKETIME(10,00,00.12345) time(5) YES NULL MAKETIME(10,00,00.123456) time(6) YES NULL DROP TABLE t1; CREATE TABLE t1 AS SELECT TIME('10:00:00'), TIME('10:00:00.1'), TIME('10:00:00.12'), TIME('10:00:00.123'), TIME('10:00:00.1234'), TIME('10:00:00.12345'), TIME('10:00:00.12346'); SHOW COLUMNS FROM t1; Field Type Null Key Default Extra TIME('10:00:00') time YES NULL TIME('10:00:00.1') time(1) YES NULL TIME('10:00:00.12') time(2) YES NULL TIME('10:00:00.123') time(3) YES NULL TIME('10:00:00.1234') time(4) YES NULL TIME('10:00:00.12345') time(5) YES NULL TIME('10:00:00.12346') time(5) YES NULL DROP TABLE t1; SET TIME_ZONE='+00:00'; SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17'); SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17'); NOW() UNIX_TIMESTAMP() UNIX_TIMESTAMP(NOW()) UNIX_TIMESTAMP('2012-10-16 22:46:17') 2012-10-16 22:46:17 1350427577 1350427577 1350427577 SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); SELECT @@timestamp, FROM_UNIXTIME(@@timestamp); @@timestamp FROM_UNIXTIME(@@timestamp) 97445.123456 1970-01-02 03:04:05.123456 SET TIME_ZONE=DEFAULT; SET TIMESTAMP=DEFAULT; SELECT TIME('2012-10-16 15:54:16.12'); TIME('2012-10-16 15:54:16.12') 15:54:16.12 SELECT TIMESTAMP('2012-10-16 15:54:16.12'); TIMESTAMP('2012-10-16 15:54:16.12') 2012-10-16 15:54:16.12 SELECT TIMEDIFF('10:10:10.1','00:00:00'); TIMEDIFF('10:10:10.1','00:00:00') 10:10:10.1 SELECT TIME_TO_SEC('10:10:10'); TIME_TO_SEC('10:10:10') 36610 SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12'); ADDTIME(TIME('10:10:10.1'),'10:10:10.12') 20:20:20.22 SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12'); ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12') 2001-01-01 20:20:20.22 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) 2001-01-01 00:00:01 SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND) 2001-01-01 00:00:01.1 SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND) 2001-01-01 00:00:01.12 SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND) 2001-01-01 00:00:01.123 SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND) 2001-01-01 00:00:01.1234 SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND) 2001-01-01 00:00:01.12345 SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND) 2001-01-01 00:00:01.123456 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) 2001-01-01 00:00:01 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND) 2001-01-01 00:00:01.1 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND) 2001-01-01 00:00:01.12 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND) 2001-01-01 00:00:01.123 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND) 2001-01-01 00:00:01.1234 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND) 2001-01-01 00:00:01.12345 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND) 2001-01-01 00:00:01.123456 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND) 2000-12-31 23:59:59 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND) 2000-12-31 23:59:58.9 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND) 2000-12-31 23:59:58.88 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND) 2000-12-31 23:59:58.877 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND) 2000-12-31 23:59:58.8766 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND) 2000-12-31 23:59:58.87655 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND) 2000-12-31 23:59:58.876544 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND); DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND) 2001-01-01 00:00:00.000000 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'xxx' SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00'); CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00') 2001-01-01 11:20:30.12 # # MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision # CREATE TABLE t1 (t0 TIME); INSERT INTO t1 VALUES ('00:00:00'); SELECT t0 + INTERVAL 1.1 SECOND FROM t1; t0 + INTERVAL 1.1 SECOND 00:00:01.1 CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; SHOW COLUMNS FROM t2; Field Type Null Key Default Extra t0 + INTERVAL 1.1 SECOND time(1) YES NULL DROP TABLE t1,t2; CREATE TABLE t1 (t0 DATETIME); INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); SELECT t0 + INTERVAL 1.1 SECOND FROM t1; t0 + INTERVAL 1.1 SECOND 2001-01-01 00:00:01.1 CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; SHOW COLUMNS FROM t2; Field Type Null Key Default Extra t0 + INTERVAL 1.1 SECOND datetime(1) YES NULL DROP TABLE t1, t2; # # MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10') # CREATE TABLE t1 AS SELECT TIMESTAMP('2001-01-01','10:10:10'), TIMESTAMP('2001-01-01','10:10:10.1'), TIMESTAMP('2001-01-01','10:10:10.12'), TIMESTAMP('2001-01-01','10:10:10.123'), TIMESTAMP('2001-01-01','10:10:10.1234'), TIMESTAMP('2001-01-01','10:10:10.12345'), TIMESTAMP('2001-01-01','10:10:10.123456'), TIMESTAMP('2001-01-01','10:10:10.1234567'); Warnings: Note 1292 Truncated incorrect time value: '10:10:10.1234567' SHOW COLUMNS FROM t1; Field Type Null Key Default Extra TIMESTAMP('2001-01-01','10:10:10') datetime YES NULL TIMESTAMP('2001-01-01','10:10:10.1') datetime(1) YES NULL TIMESTAMP('2001-01-01','10:10:10.12') datetime(2) YES NULL TIMESTAMP('2001-01-01','10:10:10.123') datetime(3) YES NULL TIMESTAMP('2001-01-01','10:10:10.1234') datetime(4) YES NULL TIMESTAMP('2001-01-01','10:10:10.12345') datetime(5) YES NULL TIMESTAMP('2001-01-01','10:10:10.123456') datetime(6) YES NULL TIMESTAMP('2001-01-01','10:10:10.1234567') datetime(6) YES NULL SELECT * FROM t1; TIMESTAMP('2001-01-01','10:10:10') TIMESTAMP('2001-01-01','10:10:10.1') TIMESTAMP('2001-01-01','10:10:10.12') TIMESTAMP('2001-01-01','10:10:10.123') TIMESTAMP('2001-01-01','10:10:10.1234') TIMESTAMP('2001-01-01','10:10:10.12345') TIMESTAMP('2001-01-01','10:10:10.123456') TIMESTAMP('2001-01-01','10:10:10.1234567') 2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 DROP TABLE t1; CREATE TABLE t1 AS SELECT TIMESTAMP('2001-01-01 00:00:00','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'), TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10'); Warnings: Note 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00.1234567' SHOW COLUMNS FROM t1; Field Type Null Key Default Extra TIMESTAMP('2001-01-01 00:00:00','10:10:10') datetime YES NULL TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') datetime(1) YES NULL TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') datetime(2) YES NULL TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') datetime(3) YES NULL TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') datetime(4) YES NULL TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') datetime(5) YES NULL TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') datetime(6) YES NULL TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') datetime(6) YES NULL SELECT * FROM t1; TIMESTAMP('2001-01-01 00:00:00','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') 2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 DROP TABLE t1; SET timestamp=unix_timestamp('2001-02-03 10:20:30'); CREATE TABLE t1 AS SELECT TIMESTAMP('00:00:00','10:10:10'), TIMESTAMP(TIME('00:00:00'),'10:10:10'); SHOW COLUMNS FROM t1; Field Type Null Key Default Extra TIMESTAMP('00:00:00','10:10:10') datetime YES NULL TIMESTAMP(TIME('00:00:00'),'10:10:10') datetime YES NULL SELECT * FROM t1; TIMESTAMP('00:00:00','10:10:10') TIMESTAMP(TIME('00:00:00'),'10:10:10') NULL 2001-02-03 10:10:10 DROP TABLE t1; SET timestamp=DEFAULT; # # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) # SELECT MAKETIME(0, 0, -0.1); MAKETIME(0, 0, -0.1) NULL # # MDEV-4857 Wrong result of HOUR('1 00:00:00') # SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT HOUR('1 02:00:00'), HOUR('26:00:00'); HOUR('1 02:00:00') HOUR('26:00:00') 26 26 SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00'); HOUR(TIME'1 02:00:00') HOUR(TIME'26:00:00') 26 26 SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00')); HOUR(TIME('1 02:00:00')) HOUR(TIME('26:00:00')) 26 26 SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00')); DAY(TIME('1 02:00:00')) DAY(TIME('26:00:00')) 4 4 SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00'); EXTRACT(HOUR FROM '1 02:00:00') EXTRACT(HOUR FROM '26:00:00') 2 2 SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00'); EXTRACT(HOUR FROM TIME'1 02:00:00') EXTRACT(HOUR FROM TIME'26:00:00') 2 2 SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00')); EXTRACT(HOUR FROM TIME('1 02:00:00')) EXTRACT(HOUR FROM TIME('26:00:00')) 2 2 SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00')); EXTRACT(DAY FROM TIME('1 02:00:00')) EXTRACT(DAY FROM TIME('26:00:00')) 1 1 SET timestamp=DEFAULT; # # 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.' # SET TIMESTAMP=UNIX_TIMESTAMP('2014-01-22 18:19:20'); CREATE TABLE t1 (t TIME); INSERT INTO t1 VALUES ('03:22:30'),('18:30:05'); SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1; CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') 2014-01-22 11:22:30 2014-01-23 02:30:05 SELECT GREATEST(t, CURRENT_DATE()) FROM t1; GREATEST(t, CURRENT_DATE()) 2014-01-22 03:22:30 2014-01-22 18:30:05 DROP TABLE t1; SET TIMESTAMP=DEFAULT; # # MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP; 1 1 1 DROP TABLE t1; # # MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND) # SELECT 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; c1 c2 9923-03-10 22:47:10.0 NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2000000000000000000.0' # # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) # SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE); Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE) 254 19 19 Y 0 0 8 DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE) 2011-01-02 12:14:14 # # 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 # CREATE TABLE t1 (a DATETIME, b DATE); INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; IF(1,ADDDATE(IFNULL(a,b),0),1) 2012-12-21 00:00:00 SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) 2012-12-21 00:00:00 SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) 2012-12-21 00:00:00 SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) 2012-12-21 00:00:00 SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; IF(1,ADDTIME(IFNULL(a,b),0),1) 2012-12-21 00:00:00 SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) 2012-12-21 00:00:00 SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) 2012-12-21 00:00:00 SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) 2012-12-21 00:00:00 DROP TABLE t1; SET timestamp=unix_timestamp('2001-02-03 10:20:30'); CREATE TABLE t1 (a DATETIME, b TIME); INSERT INTO t1 VALUES (NULL, '00:20:12'); SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; IF(1,ADDDATE(IFNULL(a,b),0),1) 2001-02-03 00:20:12 SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) 2001-02-03 00:20:12 SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) 2001-02-03 00:20:12 SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) 2001-02-03 00:20:12 SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; IF(1,ADDTIME(IFNULL(a,b),0),1) 2001-02-03 00:20:12 SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) 2001-02-03 00:20:12 SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) 2001-02-03 00:20:12 SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) 2001-02-03 00:20:12 DROP TABLE t1; SET timestamp=DEFAULT; # # MDEV-5870 Assertion `ltime->neg == 0' fails with COALESCE, ADDDATE, MAKEDATE # CREATE TABLE t1 (dt DATETIME); INSERT INTO t1 VALUES ('2003-05-13 19:36:05'), ('2012-12-12 09:20:06'); SELECT COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) FROM t1; COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) 2006-09-12 2006-09-12 Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow DROP TABLE t1; # # MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME # SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03'); SELECT IF(1,TIME'10:20:30',DATE'2001-01-01'); IF(1,TIME'10:20:30',DATE'2001-01-01') 2014-04-15 10:20:30 SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01'); IFNULL(TIME'10:20:30',DATE'2001-01-01') 2014-04-15 10:20:30 SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END; CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END 2014-04-15 10:20:30 SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01'); COALESCE(TIME'10:20:30',DATE'2001-01-01') 2014-04-15 10:20:30 SET timestamp=DEFAULT; # # MDEV-5750 Assertion `ltime->year == 0' fails on a query with EXTRACT DAY_MINUTE and TIME column # SET timestamp=UNIX_TIMESTAMP('2010-01-01 00:00:00'); CREATE TABLE t1 ( d DATE, t TIME ); INSERT INTO t1 VALUES ('2008-12-05','22:34:09'),('2005-03-27','14:26:02'); SELECT EXTRACT(DAY_MINUTE FROM GREATEST(t,d)), GREATEST(t,d) FROM t1; EXTRACT(DAY_MINUTE FROM GREATEST(t,d)) GREATEST(t,d) 12234 2010-01-01 22:34:09 11426 2010-01-01 14:26:02 DROP TABLE t1; SET timestamp=DEFAULT; # # MDEV-7221 from_days fails after null value # CREATE TABLE t1 ( id INT(11) NOT NULL PRIMARY KEY, date1 DATE NULL DEFAULT NULL ); INSERT INTO t1 VALUES (12, '2011-05-12'); INSERT INTO t1 VALUES (13, NULL); INSERT INTO t1 VALUES (14, '2009-10-23'); INSERT INTO t1 VALUES (15, '2014-10-30'); INSERT INTO t1 VALUES (16, NULL); INSERT INTO t1 VALUES (17, NULL); INSERT INTO t1 VALUES (18, '2010-10-13'); SELECT 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; id date1 date2 DATE_ADD(a.date1,INTERVAL -10 DAY) TO_DAYS(a.date1)-10 12 2011-05-12 2011-05-02 2011-05-02 734624 13 NULL NULL NULL NULL 14 2009-10-23 2009-10-13 2009-10-13 734058 15 2014-10-30 2014-10-20 2014-10-20 735891 16 NULL NULL NULL NULL 17 NULL NULL NULL NULL 18 2010-10-13 2010-10-03 2010-10-03 734413 DROP TABLE t1; # # MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() # SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 3 # # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value # SET sql_mode='NO_ZERO_IN_DATE'; CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002'); SELECT * FROM t1; a 46:58:57.999999 DROP TABLE t1; SET sql_mode=DEFAULT; # # MDEV-13972 crash in Item_func_sec_to_time::get_date # DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); Warnings: Warning 1292 Truncated incorrect INTEGER value: '' Warning 1292 Truncated incorrect INTEGER value: '' Warning 1292 Truncated incorrect INTEGER value: '' Warning 1292 Truncated incorrect time value: '20000101' SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); SEC_TO_TIME(MAKEDATE(0,RAND(~0))) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '20000101' SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) 24257 Warnings: Warning 1292 Truncated incorrect INTEGER value: '-3S\xFA\xDE?\x00\x00\xCA\xB3\xEEE\xA4\xD1\xC1\xA8' # # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result # SELECT MAKETIME(1e10,0,0), MAKETIME(-1e10,0,0), MAKETIME(1e50,0,0), MAKETIME(-1e50,0,0), MAKETIME(COALESCE(1e50),0,0), MAKETIME(COALESCE(-1e50),0,0); MAKETIME(1e10,0,0) 838:59:59 MAKETIME(-1e10,0,0) -838:59:59 MAKETIME(1e50,0,0) 838:59:59 MAKETIME(-1e50,0,0) -838:59:59 MAKETIME(COALESCE(1e50),0,0) 838:59:59 MAKETIME(COALESCE(-1e50),0,0) -838:59:59 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '10000000000:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '-10000000000:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '9223372036854775807:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '-9223372036854775808:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '9223372036854775807:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '-9223372036854775808:00:00' CREATE TABLE t1 (a FLOAT); INSERT INTO t1 VALUES (1e30),(-1e30); SELECT MAKETIME(a,0,0) FROM t1; MAKETIME(a,0,0) 838:59:59 -838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '9223372036854775807:00:00' Warning 1292 Truncated incorrect time value: '-9223372036854775808:00:00' DROP TABLE t1; # # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result # SELECT MAKETIME(900,0,0); MAKETIME(900,0,0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.1); MAKETIME(900,0,0.1) 838:59:59.9 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.11); MAKETIME(900,0,0.11) 838:59:59.99 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.111); MAKETIME(900,0,0.111) 838:59:59.999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.1111); MAKETIME(900,0,0.1111) 838:59:59.9999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.11111); MAKETIME(900,0,0.11111) 838:59:59.99999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.111111); MAKETIME(900,0,0.111111) 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.1111111); MAKETIME(900,0,0.1111111) 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.11111111); MAKETIME(900,0,0.11111111) 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,0.111111111); MAKETIME(900,0,0.111111111) 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:00' SELECT MAKETIME(900,0,EXP(1)); MAKETIME(900,0,EXP(1)) 838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '900:00:02' SELECT MAKETIME(-900,0,0); MAKETIME(-900,0,0) -838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.1); MAKETIME(-900,0,0.1) -838:59:59.9 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.11); MAKETIME(-900,0,0.11) -838:59:59.99 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.111); MAKETIME(-900,0,0.111) -838:59:59.999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.1111); MAKETIME(-900,0,0.1111) -838:59:59.9999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.11111); MAKETIME(-900,0,0.11111) -838:59:59.99999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.111111); MAKETIME(-900,0,0.111111) -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.1111111); MAKETIME(-900,0,0.1111111) -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.11111111); MAKETIME(-900,0,0.11111111) -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,0.111111111); MAKETIME(-900,0,0.111111111) -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:00' SELECT MAKETIME(-900,0,EXP(1)); MAKETIME(-900,0,EXP(1)) -838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-900:00:02' # # End of 5.5 tests # # # MDEV-8205 timediff returns null when comparing decimal time to time string value # SELECT TIMEDIFF('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ) AS str_str, TIMEDIFF('2014-01-01 00:00:00' , 20140101010000.000 ) AS str_dec, TIMEDIFF(20140101000000.000 , 20140101010000.000 ) AS dec_dec, TIMEDIFF(20140101000000.000 , '2014-01-01 01:00:00' ) AS dec_str; str_str str_dec dec_dec dec_str -01:00:00 -01:00:00.000 -01:00:00.000 -01:00:00.000 SELECT TIMEDIFF('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ) AS str_str, TIMEDIFF('2014-01-01 00:00:00' , 20140102010000.000 ) AS str_dec, TIMEDIFF(20140101000000.000 , 20140102010000.000 ) AS dec_dec, TIMEDIFF(20140101000000.000 , '2014-01-02 01:00:00' ) AS dec_str; str_str str_dec dec_dec dec_str -25:00:00 -25:00:00.000 -25:00:00.000 -25:00:00.000 SELECT TIMEDIFF('2014-01-01 00:00:00' , '2014-02-02 01:00:00' ) AS str_str, TIMEDIFF('2014-01-01 00:00:00' , 20140202010000.000 ) AS str_dec, TIMEDIFF(20140101000000.000 , 20140202010000.000 ) AS dec_dec, TIMEDIFF(20140101000000.000 , '2014-02-02 01:00:00' ) AS dec_str; str_str str_dec dec_dec dec_str -769:00:00 -769:00:00.000 -769:00:00.000 -769:00:00.000 SELECT TIMEDIFF('2014-01-01 00:00:00' , '2014-03-02 01:00:00' ) AS str_str, TIMEDIFF('2014-01-01 00:00:00' , 20140302010000.000 ) AS str_dec, TIMEDIFF(20140101000000.000 , 20140302010000.000 ) AS dec_dec, TIMEDIFF(20140101000000.000 , '2014-03-02 01:00:00' ) AS dec_str; str_str str_dec dec_dec dec_str -838:59:59 -838:59:59.999 -838:59:59.999 -838:59:59.999 Warnings: Warning 1292 Truncated incorrect time value: '-1441:00:00' Warning 1292 Truncated incorrect time value: '-1441:00:00' Warning 1292 Truncated incorrect time value: '-1441:00:00' Warning 1292 Truncated incorrect time value: '-1441:00:00' # # MDEV-10787 Assertion `ltime->neg == 0' failed in void date_to_datetime(MYSQL_TIME*) # CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('2005-07-20'),('2012-12-21'); SELECT REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) FROM t1; REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) NULL NULL Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow SELECT REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) FROM t1; REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) NULL NULL Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow SELECT CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) FROM t1; CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) NULL NULL Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow SELECT CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) FROM t1; CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) NULL NULL Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow SELECT CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) FROM t1; CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) NULL NULL Warnings: Warning 1441 Datetime function: datetime field overflow Warning 1441 Datetime function: datetime field overflow DROP TABLE t1; SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND) 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND) 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND) 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND) 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND) NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND) NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND); ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND) NULL Warnings: Warning 1441 Datetime function: datetime field overflow # # MDEV-13202 Assertion `ltime->neg == 0' failed in date_to_datetime # CREATE TABLE t1 (i INT, d DATE); INSERT INTO t1 VALUES (1, '1970-01-01'); SELECT MAX(NULLIF(i,1)) FROM t1 ORDER BY DATE_SUB(d,INTERVAL 17300000 HOUR); MAX(NULLIF(i,1)) NULL Warnings: Warning 1441 Datetime function: datetime field overflow DROP TABLE t1; CREATE TABLE t1 (i INT, d DATE); INSERT INTO t1 VALUES (1, '1970-01-01'); SELECT CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) FROM t1; CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) NULL Warnings: Warning 1441 Datetime function: datetime field overflow DROP TABLE t1; SELECT CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR)); CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR)) NULL Warnings: Warning 1441 Datetime function: datetime field overflow # # End of 10.0 tests # # # Start of 10.1 tests # # # MDEV-10317 EXCTACT(MINUTE_MICROSECOND) truncates data # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('1999-12-31 23:59:59.999999'); SELECT a, EXTRACT(YEAR FROM a), EXTRACT(YEAR_MONTH FROM a), EXTRACT(QUARTER FROM a), EXTRACT(MONTH FROM a), EXTRACT(WEEK FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(HOUR FROM a), EXTRACT(HOUR_MINUTE FROM a), EXTRACT(HOUR_SECOND FROM a), EXTRACT(MINUTE FROM a), EXTRACT(MINUTE_SECOND FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a), EXTRACT(HOUR_MICROSECOND FROM a), EXTRACT(MINUTE_MICROSECOND FROM a), EXTRACT(SECOND_MICROSECOND FROM a) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 12 26 26 Y 128 6 63 def EXTRACT(YEAR FROM a) 3 4 4 Y 32896 0 63 def EXTRACT(YEAR_MONTH FROM a) 3 6 6 Y 32896 0 63 def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 def EXTRACT(MONTH FROM a) 3 2 2 Y 32896 0 63 def EXTRACT(WEEK FROM a) 3 2 2 Y 32896 0 63 def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 def EXTRACT(DAY_HOUR FROM a) 3 5 4 Y 32896 0 63 def EXTRACT(DAY_MINUTE FROM a) 3 7 6 Y 32896 0 63 def EXTRACT(DAY_SECOND FROM a) 3 9 8 Y 32896 0 63 def EXTRACT(HOUR FROM a) 3 3 2 Y 32896 0 63 def EXTRACT(HOUR_MINUTE FROM a) 3 5 4 Y 32896 0 63 def EXTRACT(HOUR_SECOND FROM a) 3 7 6 Y 32896 0 63 def EXTRACT(MINUTE FROM a) 3 3 2 Y 32896 0 63 def EXTRACT(MINUTE_SECOND FROM a) 3 5 4 Y 32896 0 63 def EXTRACT(SECOND FROM a) 3 3 2 Y 32896 0 63 def EXTRACT(MICROSECOND FROM a) 3 7 6 Y 32896 0 63 def EXTRACT(DAY_MICROSECOND FROM a) 8 15 14 Y 32896 0 63 def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 12 Y 32896 0 63 def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 10 Y 32896 0 63 def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 8 Y 32896 0 63 a 1999-12-31 23:59:59.999999 EXTRACT(YEAR FROM a) 1999 EXTRACT(YEAR_MONTH FROM a) 199912 EXTRACT(QUARTER FROM a) 4 EXTRACT(MONTH FROM a) 12 EXTRACT(WEEK FROM a) 52 EXTRACT(DAY FROM a) 31 EXTRACT(DAY_HOUR FROM a) 3123 EXTRACT(DAY_MINUTE FROM a) 312359 EXTRACT(DAY_SECOND FROM a) 31235959 EXTRACT(HOUR FROM a) 23 EXTRACT(HOUR_MINUTE FROM a) 2359 EXTRACT(HOUR_SECOND FROM a) 235959 EXTRACT(MINUTE FROM a) 59 EXTRACT(MINUTE_SECOND FROM a) 5959 EXTRACT(SECOND FROM a) 59 EXTRACT(MICROSECOND FROM a) 999999 EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 EXTRACT(SECOND_MICROSECOND FROM a) 59999999 CREATE TABLE t2 AS SELECT a, EXTRACT(YEAR FROM a), EXTRACT(YEAR_MONTH FROM a), EXTRACT(QUARTER FROM a), EXTRACT(MONTH FROM a), EXTRACT(WEEK FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(HOUR FROM a), EXTRACT(HOUR_MINUTE FROM a), EXTRACT(HOUR_SECOND FROM a), EXTRACT(MINUTE FROM a), EXTRACT(MINUTE_SECOND FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a), EXTRACT(HOUR_MICROSECOND FROM a), EXTRACT(MINUTE_MICROSECOND FROM a), EXTRACT(SECOND_MICROSECOND FROM a) FROM t1; SELECT * FROM t2; a 1999-12-31 23:59:59.999999 EXTRACT(YEAR FROM a) 1999 EXTRACT(YEAR_MONTH FROM a) 199912 EXTRACT(QUARTER FROM a) 4 EXTRACT(MONTH FROM a) 12 EXTRACT(WEEK FROM a) 52 EXTRACT(DAY FROM a) 31 EXTRACT(DAY_HOUR FROM a) 3123 EXTRACT(DAY_MINUTE FROM a) 312359 EXTRACT(DAY_SECOND FROM a) 31235959 EXTRACT(HOUR FROM a) 23 EXTRACT(HOUR_MINUTE FROM a) 2359 EXTRACT(HOUR_SECOND FROM a) 235959 EXTRACT(MINUTE FROM a) 59 EXTRACT(MINUTE_SECOND FROM a) 5959 EXTRACT(SECOND FROM a) 59 EXTRACT(MICROSECOND FROM a) 999999 EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 EXTRACT(SECOND_MICROSECOND FROM a) 59999999 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime(6) DEFAULT NULL, `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2; CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('-838:59:59.999999'),('838:59:59.999999'); SELECT a, EXTRACT(YEAR FROM a), EXTRACT(YEAR_MONTH FROM a), EXTRACT(QUARTER FROM a), EXTRACT(MONTH FROM a), EXTRACT(WEEK FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(HOUR FROM a), EXTRACT(HOUR_MINUTE FROM a), EXTRACT(HOUR_SECOND FROM a), EXTRACT(MINUTE FROM a), EXTRACT(MINUTE_SECOND FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a), EXTRACT(HOUR_MICROSECOND FROM a), EXTRACT(MINUTE_MICROSECOND FROM a), EXTRACT(SECOND_MICROSECOND FROM a) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 11 17 17 Y 128 6 63 def EXTRACT(YEAR FROM a) 3 4 1 Y 32896 0 63 def EXTRACT(YEAR_MONTH FROM a) 3 6 1 Y 32896 0 63 def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 def EXTRACT(MONTH FROM a) 3 2 1 Y 32896 0 63 def EXTRACT(WEEK FROM a) 3 2 9 Y 32896 0 63 def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 def EXTRACT(DAY_HOUR FROM a) 3 5 5 Y 32896 0 63 def EXTRACT(DAY_MINUTE FROM a) 3 7 7 Y 32896 0 63 def EXTRACT(DAY_SECOND FROM a) 3 9 9 Y 32896 0 63 def EXTRACT(HOUR FROM a) 3 3 3 Y 32896 0 63 def EXTRACT(HOUR_MINUTE FROM a) 3 5 5 Y 32896 0 63 def EXTRACT(HOUR_SECOND FROM a) 3 7 7 Y 32896 0 63 def EXTRACT(MINUTE FROM a) 3 3 3 Y 32896 0 63 def EXTRACT(MINUTE_SECOND FROM a) 3 5 5 Y 32896 0 63 def EXTRACT(SECOND FROM a) 3 3 3 Y 32896 0 63 def EXTRACT(MICROSECOND FROM a) 3 7 7 Y 32896 0 63 def EXTRACT(DAY_MICROSECOND FROM a) 8 15 15 Y 32896 0 63 def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 13 Y 32896 0 63 def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 11 Y 32896 0 63 def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 9 Y 32896 0 63 a -838:59:59.999999 EXTRACT(YEAR FROM a) 0 EXTRACT(YEAR_MONTH FROM a) 0 EXTRACT(QUARTER FROM a) 0 EXTRACT(MONTH FROM a) 0 EXTRACT(WEEK FROM a) 613566757 EXTRACT(DAY FROM a) 34 EXTRACT(DAY_HOUR FROM a) -3422 EXTRACT(DAY_MINUTE FROM a) -342259 EXTRACT(DAY_SECOND FROM a) -34225959 EXTRACT(HOUR FROM a) -22 EXTRACT(HOUR_MINUTE FROM a) -2259 EXTRACT(HOUR_SECOND FROM a) -225959 EXTRACT(MINUTE FROM a) -59 EXTRACT(MINUTE_SECOND FROM a) -5959 EXTRACT(SECOND FROM a) -59 EXTRACT(MICROSECOND FROM a) -999999 EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 EXTRACT(SECOND_MICROSECOND FROM a) -59999999 a 838:59:59.999999 EXTRACT(YEAR FROM a) 0 EXTRACT(YEAR_MONTH FROM a) 0 EXTRACT(QUARTER FROM a) 0 EXTRACT(MONTH FROM a) 0 EXTRACT(WEEK FROM a) 613566757 EXTRACT(DAY FROM a) 34 EXTRACT(DAY_HOUR FROM a) 3422 EXTRACT(DAY_MINUTE FROM a) 342259 EXTRACT(DAY_SECOND FROM a) 34225959 EXTRACT(HOUR FROM a) 22 EXTRACT(HOUR_MINUTE FROM a) 2259 EXTRACT(HOUR_SECOND FROM a) 225959 EXTRACT(MINUTE FROM a) 59 EXTRACT(MINUTE_SECOND FROM a) 5959 EXTRACT(SECOND FROM a) 59 EXTRACT(MICROSECOND FROM a) 999999 EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 EXTRACT(SECOND_MICROSECOND FROM a) 59999999 CREATE TABLE t2 AS SELECT a, EXTRACT(YEAR FROM a), EXTRACT(YEAR_MONTH FROM a), EXTRACT(QUARTER FROM a), EXTRACT(MONTH FROM a), EXTRACT(WEEK FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(HOUR FROM a), EXTRACT(HOUR_MINUTE FROM a), EXTRACT(HOUR_SECOND FROM a), EXTRACT(MINUTE FROM a), EXTRACT(MINUTE_SECOND FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a), EXTRACT(HOUR_MICROSECOND FROM a), EXTRACT(MINUTE_MICROSECOND FROM a), EXTRACT(SECOND_MICROSECOND FROM a) FROM t1; SELECT * FROM t2; a -838:59:59.999999 EXTRACT(YEAR FROM a) 0 EXTRACT(YEAR_MONTH FROM a) 0 EXTRACT(QUARTER FROM a) 0 EXTRACT(MONTH FROM a) 0 EXTRACT(WEEK FROM a) 613566757 EXTRACT(DAY FROM a) 34 EXTRACT(DAY_HOUR FROM a) -3422 EXTRACT(DAY_MINUTE FROM a) -342259 EXTRACT(DAY_SECOND FROM a) -34225959 EXTRACT(HOUR FROM a) -22 EXTRACT(HOUR_MINUTE FROM a) -2259 EXTRACT(HOUR_SECOND FROM a) -225959 EXTRACT(MINUTE FROM a) -59 EXTRACT(MINUTE_SECOND FROM a) -5959 EXTRACT(SECOND FROM a) -59 EXTRACT(MICROSECOND FROM a) -999999 EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 EXTRACT(SECOND_MICROSECOND FROM a) -59999999 a 838:59:59.999999 EXTRACT(YEAR FROM a) 0 EXTRACT(YEAR_MONTH FROM a) 0 EXTRACT(QUARTER FROM a) 0 EXTRACT(MONTH FROM a) 0 EXTRACT(WEEK FROM a) 613566757 EXTRACT(DAY FROM a) 34 EXTRACT(DAY_HOUR FROM a) 3422 EXTRACT(DAY_MINUTE FROM a) 342259 EXTRACT(DAY_SECOND FROM a) 34225959 EXTRACT(HOUR FROM a) 22 EXTRACT(HOUR_MINUTE FROM a) 2259 EXTRACT(HOUR_SECOND FROM a) 225959 EXTRACT(MINUTE FROM a) 59 EXTRACT(MINUTE_SECOND FROM a) 5959 EXTRACT(SECOND FROM a) 59 EXTRACT(MICROSECOND FROM a) 999999 EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 EXTRACT(SECOND_MICROSECOND FROM a) 59999999 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time(6) DEFAULT NULL, `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2; # # MDEV-14926 AddressSanitizer: heap-use-after-free in make_date_time on weird combination of functions # DO INET_ATON( FROM_UNIXTIME( @@timestamp, ( TRIM( UNHEX(HEX('%m.%d.%Y') ) ) ) ) ); CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1989-03-10'); SELECT TIME_FORMAT('23:59:43', BINARY d) AS f FROM t1 GROUP BY 'foo'; f 1989-03-10 DROP TABLE t1; CREATE TABLE t1 (d DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('1900-01-01'); SELECT LENGTH( DATE_FORMAT( d, BINARY DATABASE() ) ) AS f FROM t1 GROUP BY d; f 4 DROP TABLE t1; # # MDEV-18667 ASAN heap-use-after-free in make_date_time / Arg_comparator::compare_string / Item_func_nullif::compare # SELECT NULLIF('foo', FROM_UNIXTIME('2012-12-12 12:12:12', TRIM(0))); NULLIF('foo', FROM_UNIXTIME('2012-12-12 12:12:12', TRIM(0))) foo Warnings: Warning 1292 Truncated incorrect DECIMAL value: '2012-12-12 12:12:12' # # MDEV-18626 ASAN stack-buffer-overflow in int10_to_str / make_date_time upon DATE_FORMAT # SELECT DATE_FORMAT(100000000000, '%j'); DATE_FORMAT(100000000000, '%j') NULL # # End of 10.1 tests # # # MDEV-16217: Assertion `!table || (!table->read_set || # bitmap_is_set(table->read_set, field_index))' # failed in Field_num::get_date # CREATE TABLE t1 (pk int default 0, a1 date); INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL); CREATE VIEW v1 AS SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1; SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1; a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) 0 NULL NULL NULL SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1; a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) 0 NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '18446744073709551615' CREATE TABLE t2 (pk int default 1, a1 date); INSERT INTO t2 VALUES (4,NULL); CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2; select * from v2; default(t1.pk) default(t2.pk) pk 0 1 1 0 1 2 0 1 3 0 1 4 show create view v2; View Create View character_set_client collation_connection v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select default(`t1`.`pk`) AS `default(t1.pk)`,default(`t2`.`pk`) AS `default(t2.pk)`,`t1`.`pk` AS `pk` from (`t1` join `t2`) latin1 latin1_swedish_ci CREATE view v3 as SELECT default(pk) from t2; select * from v3; default(pk) 1 explain extended select * from v3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 Warnings: Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual explain extended select default(pk) from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 Warnings: Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual show create view v3; View Create View character_set_client collation_connection v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select default(`t2`.`pk`) AS `default(pk)` from `t2` latin1 latin1_swedish_ci DROP VIEW v1,v2,v3; DROP TABLE t1,t2; # # End of 10.2 tests # # # MDEV-12515 Wrong value when storing DATE_ADD() and ADDTIME() to a numeric field # SET sql_mode=''; CREATE TABLE t1 AS SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY) AS c1, ADDTIME('10:20:30',1) AS c2; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` varchar(19) DEFAULT NULL, `c2` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 2001-01-02 10:20:31 DROP TABLE t1; CREATE TABLE t2 (c INT); INSERT INTO t2 SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY); Warnings: Warning 1265 Data truncated for column 'c' at row 1 INSERT INTO t2 VALUES ('2001-01-02'); Warnings: Warning 1265 Data truncated for column 'c' at row 1 SELECT * FROM t2; c 2001 2001 DROP TABLE t2; CREATE TABLE t2 (a INT); INSERT INTO t2 VALUES (ADDTIME('10:20:30',1)); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT INTO t2 VALUES ('10:20:31'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 SELECT * FROM t2; a 10 10 DROP TABLE t2; SET sql_mode=DEFAULT; # # MDEV-12860 Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..) # SET sql_mode=STRICT_ALL_TABLES; CREATE TABLE t1 ( id bigint(11) NOT NULL PRIMARY KEY, dt datetime(6) ); INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456'); CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1; DESCRIBE v1; Field Type Null Key Default Extra dt2 bigint(11) YES NULL SELECT * FROM v1; dt2 2233123456 CREATE TABLE t2 AS SELECT MAX(dt2) FROM v1; DESCRIBE t2; Field Type Null Key Default Extra MAX(dt2) bigint(11) YES NULL SELECT * FROM t2; MAX(dt2) 2233123456 DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; SET sql_mode=DEFAULT; # # MDEV-12866 Out-of-range error with CREATE..SELECT..TO_SECONDS(NOW()) # SET sql_mode=STRICT_ALL_TABLES; CREATE TABLE t1 AS SELECT TO_SECONDS('9999-12-31 23:59:59'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `TO_SECONDS('9999-12-31 23:59:59')` bigint(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET sql_mode=DEFAULT; # # MDEV-13966 Parameter data type control for Item_temporal_func # SELECT FROM_DAYS(ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'from_days' SELECT MAKEDATE(ROW(1,1),1); ERROR HY000: Illegal parameter data type row for operation 'makedate' SELECT MAKEDATE(1, ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'makedate' SELECT LAST_DAY(ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'last_day' SELECT SEC_TO_TIME(ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'sec_to_time' SELECT TIMEDIFF(ROW(1,1),1); ERROR HY000: Illegal parameter data type row for operation 'timediff' SELECT TIMEDIFF(1, ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'timediff' SELECT MAKETIME(ROW(1,1),1,1); ERROR HY000: Illegal parameter data type row for operation 'maketime' SELECT MAKETIME(1, ROW(1,1), 1); ERROR HY000: Illegal parameter data type row for operation 'maketime' SELECT MAKETIME(1, 1, ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'maketime' SELECT FROM_UNIXTIME(ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'from_unixtime' SELECT CONVERT_TZ(ROW(1,1),1,1); ERROR HY000: Illegal parameter data type row for operation 'convert_tz' SELECT CONVERT_TZ(1, ROW(1,1), 1); ERROR HY000: Illegal parameter data type row for operation 'convert_tz' SELECT CONVERT_TZ(1, 1, ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'convert_tz' # # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime) # SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03'); SELECT COALESCE(TIME'800:00:00', NOW()) AS c, HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc; c hc 2018-03-22 08:00:00 8 SELECT CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c, HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc; c hc 2018-03-22 08:00:00 8 SELECT IFNULL(TIME'800:00:00', NOW()) AS c, HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc; c hc 2018-03-22 08:00:00 8 SELECT IF(TRUE,TIME'800:00:00', NOW()) AS c, HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc; c hc 2018-03-22 08:00:00 8 SELECT ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1, ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2, ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3, ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4; c1 c2 c3 c4 NULL NULL NULL NULL SELECT HOUR(TIMESTAMP'0000-00-01 10:00:00') AS h0, TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00') AS tts0, TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00.1') AS tts1, CAST(TIMESTAMP'0000-00-01 10:00:00' AS TIME) AS c0, CAST(TIMESTAMP'0000-00-01 10:00:00.1' AS TIME(1)) AS c2; h0 tts0 tts1 c0 c2 10 36000 36000.1 10:00:00 10:00:00.1 SET TIMESTAMP=DEFAULT; # # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) # SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03'); SELECT LAST_DAY(TIME'00:00:00') AS c1, CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; c1 c2 c3 2018-02-28 00:00:00 00:00:00 SET TIMESTAMP=DEFAULT; # # MDEV-15702 Remove the use of STRING_ITEM from Item_func_date_format::fix_length_and_dec() # SELECT DATE_FORMAT('2001-01-01',POINT(1,1)); ERROR HY000: Illegal parameter data type geometry for operation 'date_format' SELECT DATE_FORMAT(POINT(1,1),'10'); ERROR HY000: Illegal parameter data type geometry for operation 'date_format' SELECT DATE_FORMAT('2001-01-01',ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'date_format' SELECT DATE_FORMAT(ROW(1,1),'10'); ERROR HY000: Illegal parameter data type row for operation 'date_format' SELECT DATE_FORMAT('2001-01-01','%Y',POINT(1,1)); ERROR HY000: Illegal parameter data type geometry for operation 'date_format' SELECT DATE_FORMAT('2001-01-01','%Y',ROW(1,1)); ERROR HY000: Illegal parameter data type row for operation 'date_format' SELECT DATE_FORMAT('2001-01-01','%Y',@unknown_user_variable); DATE_FORMAT('2001-01-01','%Y',@unknown_user_variable) 2001 Warnings: Warning 1649 Unknown locale: 'NULL' CREATE TABLE t1 AS SELECT DATE_FORMAT('2001-01-01',NULL) AS c0, DATE_FORMAT('2001-01-01','10') AS c1, DATE_FORMAT('2001-01-01',10) AS c2, DATE_FORMAT('2001-01-01',10.0) AS c3, DATE_FORMAT('2001-01-01',10e0) AS c4, DATE_FORMAT('2001-01-01',TIME'10:20:30') AS c5; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c0` char(0) DEFAULT NULL, `c1` varchar(2) DEFAULT NULL, `c2` varchar(2) DEFAULT NULL, `c3` varchar(4) DEFAULT NULL, `c4` varchar(2) DEFAULT NULL, `c5` varchar(8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXECUTE IMMEDIATE "CREATE TABLE t1 AS SELECT DATE_FORMAT('2001-01-01',?) AS c0, DATE_FORMAT('2001-01-01',?) AS c1, DATE_FORMAT('2001-01-01',?) AS c2, DATE_FORMAT('2001-01-01',?) AS c3, DATE_FORMAT('2001-01-01',?) AS c4, DATE_FORMAT('2001-01-01',?) AS c5" USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c0` char(0) DEFAULT NULL, `c1` varchar(2) DEFAULT NULL, `c2` varchar(2) DEFAULT NULL, `c3` varchar(4) DEFAULT NULL, `c4` varchar(2) DEFAULT NULL, `c5` varchar(100) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # MDEV-16152 Expressions with INTERVAL return bad results in some cases # SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; c1 c2 1991-01-01 10:20:30 1991-01-01 10:20:30 SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; c1 c2 c3 2011-01-01 10:20:30 2011-01-01 10:20:30 2011-01-01 10:20:30 EXPLAIN EXTENDED SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS `c1`,TIMESTAMP'2001-01-01 10:20:30' - interval '10' year AS `c2` EXPLAIN EXTENDED SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c1`,TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c2`,TIMESTAMP'2001-01-01 10:20:30' + interval '10' year AS `c3` CREATE TABLE t_ts (a timestamp(6)); CREATE TABLE t_trig (a timestamp(6)); CREATE FUNCTION fn_sleep_before_now() returns int BEGIN INSERT INTO t_ts SET a= current_timestamp(6); RETURN 0; END// CREATE TRIGGER trg_insert_t_ts after INSERT on t_ts for each row BEGIN INSERT into t_trig set a= current_timestamp(6); END// SET @sav_slow_query_log= @@session.slow_query_log; SET @@session.slow_query_log= ON; SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func; SELECT a FROM t_ts LIMIT 1 into @ts_func; SELECT a FROM t_trig LIMIT 1 into @ts_trig; DELETE FROM t_ts; DELETE FROM t_trig; SET @@session.slow_query_log= OFF; SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts; SELECT a FROM t_ts LIMIT 1 into @ts_func; SELECT a FROM t_trig LIMIT 1 into @ts_trig; SET @@session.slow_query_log= @sav_slow_query_log; DROP FUNCTION fn_sleep_before_now; DROP TRIGGER trg_insert_t_ts; DROP TABLE t_ts, t_trig; # # MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer # CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('00:00:10'),('00:00:20'); SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1; a SUBTIME(a,TIME'00:00:01') ADDTIME(a,TIME'00:00:01') 00:00:10 00:00:09 00:00:11 00:00:20 00:00:19 00:00:21 SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'; a 00:00:10 SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; a 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'; a EXPLAIN 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'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' and addtime(`test`.`t1`.`a`,TIME'00:00:01') <= TIME'00:00:09' DROP TABLE t1; # # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) # set timestamp=unix_timestamp('2018-08-02 10:10:10'); SELECT LAST_DAY(TIME'00:00:00') AS c1, CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; c1 c2 c3 2018-08-31 00:00:00 00:00:00 # # End of 10.3 tests #