| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/MariaDB/mysql-test/main/ (MariaDB Server Version 8.1-8.4©) Datei vom 1.9.2026 mit Größe 229 kB |
|
Impressum func_time.result Sprache: Lisp |
|||||||||||||||
|
set time_zone="+03:00"; select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(d from_days(to_days("960101")) to_days(960201)-to_days("19960101") to_days(date_add( 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_timest 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")) as e1,from_unixtime(unix e1 e2 e3 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_s 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.55667788 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 seconds value: '1234567890123.123000' 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(-9 -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 seconds 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 seconds value: '1234567890123' Warning 1292 Truncated incorrect seconds value: '99999999999999999999999999999' select now()-curdate()*1000000-curtime(); now()-curdate()*1000000-curtime() 0 select strcmp(current_timestamp(),concat(current_date()," ",current_time())) as exp; exp 0 select strcmp(localtime(),current_time()) as exp; exp 0 select strcmp(localtimestamp(),concat(current_date()," ",current_time())) as exp; exp 0 select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w") as exp; exp 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")) as exp; exp 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('1981-12-31',1) yearweek('1982-01-01',1) yearweek('1982-12-31',1) yearweek 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 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 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 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 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 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 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 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 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(199 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(200 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(200 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(200 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(200 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(20 yearweek(20001231,0) yearweek(20001231,1) yearweek(20001231,2) yearweek(20001231,3) 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 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'Saturday' 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(1998013100000 time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131000000,'%H|% 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(1998013101020 time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010203,'%H|% 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(1998013113141 time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131131415,'%H|% 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(1998013101001 time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010015,'%H|% 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 exp 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|03 select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|% exp 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) as exp; exp 1999-02-21 17:40:38 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE) as exp; exp 1970-08-11 19:20:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND) as exp; exp 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") 225101112 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 MIN exp 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 MIN exp 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 as exp; exp 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 as exp; exp 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: '' for column `test`.`t1`.`updated` at row 1 SELECT year(updated) from t1; year(updated) NULL Warnings: Warning 1292 Incorrect datetime value: '' for column `test`.`t1`.`updated` at row 1 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( 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' for column `test`.`t1`.`c` at row 1 select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofm 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' for column `test`.`t1`.`c` at row 1 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) fro 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' for column `test`.`t1`.`c` at row 1 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' for column `test`.`t1`.`c` at row 1 select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),ex extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MON 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:0 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(0); from_unixtime(0) NULL Warnings: Warning 1292 Truncated incorrect unixtime value: '0.0' select unix_timestamp(from_unixtime(2147483647)); unix_timestamp(from_unixtime(2147483647)) 2147483647 select unix_timestamp('1968-01-20 01:00:00'); unix_timestamp('1968-01-20 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('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 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')) 6 6 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 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 as exp; exp 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time exp 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), ut exp 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 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(19950 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_ 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: 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(" 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 Truncated 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci drop table t1; SELECT SEC_TO_TIME(3300000); SEC_TO_TIME(3300000) 838:59:59 Warnings: Warning 1292 Truncated incorrect seconds value: '3300000' SELECT SEC_TO_TIME(3300000)+0; SEC_TO_TIME(3300000)+0 8385959 Warnings: Warning 1292 Truncated incorrect seconds value: '3300000' SELECT SEC_TO_TIME(3600 * 4294967296); SEC_TO_TIME(3600 * 4294967296) 838:59:59 Warnings: Warning 1292 Truncated incorrect seconds 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 ADDTIME(20010101,1e0), ADDTIME(20010101,1.1e0); ADDTIME(20010101,1e0) ADDTIME(20010101,1.1e0) 2001-01-01 00:00:01.000000 2001-01-01 00:00:01.100000 SELECT ADDTIME(ADDTIME(20010101,1e0), 0); ADDTIME(ADDTIME(20010101,1e0), 0) 2001-01-01 00:00:01.000000 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 Warnings: Warning 1292 Truncated incorrect seconds value: '4294967296' 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') 16 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 Warning 1292 Truncated incorrect seconds 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; SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-01 11:22:33'); CREATE TABLE t1 ( a TIMESTAMP ); INSERT INTO t1 VALUES (now()), (now()); SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); 1 1 1 Warnings: Warning 1292 Truncated incorrect seconds value: '20010101112233' Warning 1292 Truncated incorrect seconds value: '20010101112233' DROP TABLE t1; SET TIMESTAMP=DEFAULT; (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( 0 0 45 45 45 Warnings: Note 1292 Truncated incorrect date 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 as exp; exp NULL Warnings: Warning 1292 Truncated incorrect date value: '1997-00-04 22:23:00' Warning 1292 Incorrect datetime value: '1997-00-04 00:00:00' 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 3020 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, 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_c 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) as exp exp 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; # # MDEV-11341 STR_TO_DATE does not return NULL for invalid dates # SELECT STR_TO_DATE('1949-02-30','%Y-%m-%d'); STR_TO_DATE('1949-02-30','%Y-%m-%d') NULL Warnings: Warning 1411 Incorrect datetime value: '1949-02-30' for function str_to_date SELECT STR_TO_DATE('1949-02-28','%Y-%m-%d'); STR_TO_DATE('1949-02-28','%Y-%m-%d') 1949-02-28 SELECT STR_TO_DATE('1949-06-31','%Y-%m-%d'); STR_TO_DATE('1949-06-31','%Y-%m-%d') NULL Warnings: Warning 1411 Incorrect datetime value: '1949-06-31' for function str_to_date SELECT STR_TO_DATE('2000-02-29','%Y-%m-%d'); STR_TO_DATE('2000-02-29','%Y-%m-%d') 2000-02-29 SELECT STR_TO_DATE('1900-02-29','%Y-%m-%d'); STR_TO_DATE('1900-02-29','%Y-%m-%d') NULL Warnings: Warning 1411 Incorrect datetime value: '1900-02-29' for function str_to_date SET sql_mode=ALLOW_INVALID_DATES; SELECT STR_TO_DATE('1949-02-30','%Y-%m-%d'); STR_TO_DATE('1949-02-30','%Y-%m-%d') 1949-02-30 SELECT STR_TO_DATE('1949-02-28','%Y-%m-%d'); STR_TO_DATE('1949-02-28','%Y-%m-%d') 1949-02-28 SELECT STR_TO_DATE('1949-06-31','%Y-%m-%d'); STR_TO_DATE('1949-06-31','%Y-%m-%d') 1949-06-31 SELECT STR_TO_DATE('2000-02-29','%Y-%m-%d'); STR_TO_DATE('2000-02-29','%Y-%m-%d') 2000-02-29 SELECT STR_TO_DATE('1900-02-29','%Y-%m-%d'); STR_TO_DATE('1900-02-29','%Y-%m-%d') 1900-02-29 SET sql_mode=DEFAULT; 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 Incorrect time value: '' for column `test`.`t1`.`a` at row 1 Warning 1292 Incorrect time value: '' for column `test`.`t1`.`a` at row 1 Warning 1292 Incorrect time value: '' for column `test`.`t1`.`a` at row 2 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_SECO 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 default_storage_engine=NULL; ERROR 42000: Variable 'default_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 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 Truncated 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')) as exp; exp 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_DIV 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')) as exp; exp 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 Incorrect time value: '131415.123e0' select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04' as ex exp 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') as exp; exp 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 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 Truncated 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' as exp; exp 0 select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01' as exp; exp 0 select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05' a exp 1 select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)) as e exp 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 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)&style='color: green'>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_microseco 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 FRO 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 F 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, 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 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(22) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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, ROU c1 c2 c2 20010101102030 20010101102030.999 2001-01-01 10:20:31 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, ROU c1 c2 c2 102030 102030.999 10:20:31 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) 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 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) 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 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)) A 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 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)) A 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 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 c 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 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 c 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 SELECT CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGN CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECI 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 c 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) c1 c2 c3 c4 c5 c6 c7 20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01 SELECT CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGN CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECI 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 c 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) c1 c2 c3 c4 c5 c6 c7 20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01 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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c2` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c3` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c4` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; # # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal type # 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: 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 INTERVAL DAY TO SECOND 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 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.12 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:1 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.12 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 # 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(' c1 c2 9923-03-10 22:47:10.0 NULL Warnings: Warning 1292 Truncated incorrect seconds 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 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 # 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 colum # 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,INTERV 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-1 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(SYS_GUID())))); DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); Warnings: Warning 1292 Truncated incorrect DECIMAL value: '' Warning 1292 Truncated incorrect INTEGER value: '' Warning 1292 Truncated incorrect seconds 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 seconds 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 # # 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' ) as exp FROM exp 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' ) as exp FR exp 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) as exp FROM t1; exp 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) as exp FROM t1; exp 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) as exp FROM t1; exp 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) as exp; exp 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND) as exp; exp 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND) as exp; exp 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND) as exp; exp 9999-12-31 23:59:59 SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND) as ex exp NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND) as e exp NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND) a exp 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)) as exp; exp 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 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 1 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 3 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(5) DEFAULT NULL, `EXTRACT(YEAR_MONTH FROM a)` int(7) DEFAULT NULL, `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, `EXTRACT(MONTH FROM a)` int(3) DEFAULT NULL, `EXTRACT(WEEK FROM a)` int(3) DEFAULT NULL, `EXTRACT(DAY FROM a)` int(3) 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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 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 1 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 3 3 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(5) DEFAULT NULL, `EXTRACT(YEAR_MONTH FROM a)` int(7) DEFAULT NULL, `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, `EXTRACT(MONTH FROM a)` int(3) DEFAULT NULL, `EXTRACT(WEEK FROM a)` int(3) DEFAULT NULL, `EXTRACT(DAY FROM a)` int(3) 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1,t2; # # MDEV-14926 AddressSanitizer: heap-use-after-free in make_date_time on weird combinatio # 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_strin # 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 Truncated 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` A 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` A DROP VIEW v1,v2,v3; DROP TABLE t1,t2; # # MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_der # SELECT DAYNAME('2019-01-05')+0; DAYNAME('2019-01-05')+0 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'Saturday' SELECT CAST(DAYNAME('2019-01-05') AS SIGNED); CAST(DAYNAME('2019-01-05') AS SIGNED) 0 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'Saturday' # # 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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c2` varchar(26) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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_M # 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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- 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')) c1 c2 c3 c4 NULL NULL NULL NULL Warnings: Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 00:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 00:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 00:00:00' 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_de # SELECT DATE_FORMAT('2001-01-01',POINT(1,1)); ERROR HY000: Illegal parameter data type point for operation 'date_format' SELECT DATE_FORMAT(POINT(1,1),'10'); ERROR HY000: Illegal parameter data type point 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 point 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) 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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c1` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c2` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c3` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c4` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c5` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c1` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c2` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c3` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c4` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `c5` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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'200 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'200 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 express # 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:0 a EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDT 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`,TI 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 # # # MDEV-14032 SEC_TO_TIME executes side effect two times # SET @a=10000000; SELECT SEC_TO_TIME(@a:=@a+1); SEC_TO_TIME(@a:=@a+1) 838:59:59 Warnings: Warning 1292 Truncated incorrect seconds value: '10000001' SELECT @a; @a 10000001 CREATE TABLE t1 (a TEXT); CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO t1 VALUES ('f1 was called'); RETURN 10000000; END; $$ SELECT SEC_TO_TIME(f1()); SEC_TO_TIME(f1()) 838:59:59 Warnings: Warning 1292 Truncated incorrect seconds value: '10000000' SELECT * FROM t1; a f1 was called DROP TABLE t1; DROP FUNCTION f1; # # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999 # # Reject anything that's parsed as DATETIME or DATE CREATE TABLE t1 (a VARCHAR(64)); INSERT INTO t1 VALUES ('2001-01-01 10:20:30'), ('01-01-01 10:20:30'), ('2001-01-01 '), ('20010101102030'), ('010101102030'); SELECT ADDTIME(DATE'2001-01-01',a), a FROM t1; ADDTIME(DATE'2001-01-01',a) a NULL 2001-01-01 10:20:30 NULL 01-01-01 10:20:30 NULL 2001-01-01 NULL 20010101102030 NULL 010101102030 Warnings: Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 10:20:30' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '01-01-01 10:20:30' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 ' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '20010101102030' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '010101102030' DROP TABLE t1; # GREATEST(decimal, time) SELECT GREATEST(8395959, TIME'00:00:00') AS c0, GREATEST(8395959.0, TIME'00:00:00') AS c1, GREATEST(8395959.00, TIME'00:00:00') AS c2, GREATEST(8395959.000, TIME'00:00:00') AS c3, GREATEST(8395959.0000, TIME'00:00:00') AS c4, GREATEST(8395959.00000, TIME'00:00:00') AS c5, GREATEST(8395959.000000, TIME'00:00:00') AS c6, GREATEST(8395959.0000000, TIME'00:00:00') AS c7; c0 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 c7 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.00' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.00000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.000000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0000000' SELECT MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c0, MICROSECOND(GREATEST(8395959.0, TIME'00:00:00')) AS c1, MICROSECOND(GREATEST(8395959.00, TIME'00:00:00')) AS c2, MICROSECOND(GREATEST(8395959.000, TIME'00:00:00')) AS c3, MICROSECOND(GREATEST(8395959.0000, TIME'00:00:00')) AS c4, MICROSECOND(GREATEST(8395959.00000, TIME'00:00:00')) AS c5, MICROSECOND(GREATEST(8395959.000000, TIME'00:00:00')) AS c6, MICROSECOND(GREATEST(8395959.0000000, TIME'00:00:00')) AS c7; c0 0 c1 900000 c2 990000 c3 999000 c4 999900 c5 999990 c6 999999 c7 999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.00' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.00000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.000000' Level Warning Code 1292 Message Truncated incorrect time value: '8395959.0000000' SELECT CAST(GREATEST(8395959, TIME'00:00:00') AS SIGNED) AS ci, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,0)) AS c0, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,1)) AS c1, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,2)) AS c2, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,3)) AS c3, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,4)) AS c4, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,5)) AS c5, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,6)) AS c6, CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,7)) AS c7; ci 8385959 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' SELECT GREATEST(8395959, TIME'00:00:00') AS ci, GREATEST(8395959, TIME'00:00:00')+0 AS c0, GREATEST(8395959, TIME'00:00:00')+0.0 AS c1, GREATEST(8395959, TIME'00:00:00')+0.00 AS c2, GREATEST(8395959, TIME'00:00:00')+0.000 AS c3, GREATEST(8395959, TIME'00:00:00')+0.0000 AS c4, GREATEST(8395959, TIME'00:00:00')+0.00000 AS c5, GREATEST(8395959, TIME'00:00:00')+0.000000 AS c6, GREATEST(8395959, TIME'00:00:00')+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' Level Warning Code 1292 Message Truncated incorrect time value: '8395959' # GREATEST(string, time) SELECT GREATEST('839:59:59', TIME'00:00:00') AS ci, GREATEST('839:59:59.0', TIME'00:00:00') AS c1, GREATEST('839:59:59.00', TIME'00:00:00') AS c2, GREATEST('839:59:59.000', TIME'00:00:00') AS c3, GREATEST('839:59:59.0000', TIME'00:00:00') AS c4, GREATEST('839:59:59.00000', TIME'00:00:00') AS c5, GREATEST('839:59:59.000000', TIME'00:00:00') AS c6, GREATEST('839:59:59.0000000', TIME'00:00:00') AS c7; ci 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 c7 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.00' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.00000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0000000' SELECT MICROSECOND(GREATEST('839:59:59', TIME'00:00:00')) AS ci, MICROSECOND(GREATEST('839:59:59.0', TIME'00:00:00')) AS c1, MICROSECOND(GREATEST('839:59:59.00', TIME'00:00:00')) AS c2, MICROSECOND(GREATEST('839:59:59.000', TIME'00:00:00')) AS c3, MICROSECOND(GREATEST('839:59:59.0000', TIME'00:00:00')) AS c4, MICROSECOND(GREATEST('839:59:59.00000', TIME'00:00:00')) AS c5, MICROSECOND(GREATEST('839:59:59.000000', TIME'00:00:00')) AS c6, MICROSECOND(GREATEST('839:59:59.0000000', TIME'00:00:00')) AS c7; ci 0 c1 900000 c2 990000 c3 999000 c4 999900 c5 999990 c6 999999 c7 999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.00' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.00000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59.0000000' SELECT CAST(GREATEST('839:59:59', TIME'00:00:00') AS SIGNED) AS ci, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,0)) AS c0, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,1)) AS c1, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,2)) AS c2, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,3)) AS c3, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,4)) AS c4, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,5)) AS c5, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,6)) AS c6, CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,7)) AS c7; ci 8385959 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT GREATEST('839:59:59', TIME'00:00:00') AS ci, GREATEST('839:59:59', TIME'00:00:00')+0 AS c0, GREATEST('839:59:59', TIME'00:00:00')+0.0 AS c1, GREATEST('839:59:59', TIME'00:00:00')+0.00 AS c2, GREATEST('839:59:59', TIME'00:00:00')+0.000 AS c3, GREATEST('839:59:59', TIME'00:00:00')+0.0000 AS c4, GREATEST('839:59:59', TIME'00:00:00')+0.00000 AS c5, GREATEST('839:59:59', TIME'00:00:00')+0.000000 AS c6, GREATEST('839:59:59', TIME'00:00:00')+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' # ADDTIME(datetime, decimal) SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0) AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00) AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000) AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000) AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000) AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000) AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000) AS c7; c0 2001-02-04 23:59:59 c1 2001-02-04 23:59:59.0 c2 2001-02-04 23:59:59.00 c3 2001-02-04 23:59:59.000 c4 2001-02-04 23:59:59.0000 c5 2001-02-04 23:59:59.00000 c6 2001-02-04 23:59:59.000000 c7 2001-02-04 23:59:59.000000 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' SELECT MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c0, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0)) AS c1, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00)) AS c2, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000)) AS c3, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000)) AS c4, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000)) AS c5, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000)) AS c6, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000)) AS c7; c0 0 c1 0 c2 0 c3 0 c4 0 c5 0 c6 0 c7 0 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' SELECT CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS SIGNED) AS ci, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,0)) AS c0, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,1)) AS c1, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,2)) AS c2, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,3)) AS c3, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,4)) AS c4, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,5)) AS c5, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,6)) AS c6, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,7)) AS c7; ci 20010204235959 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000000 AS c7; ci 2001-02-04 23:59:59 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 # ADDTIME(datetime, string) SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0') AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00') AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000') AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000') AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000') AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000') AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000') AS c7; c0 2001-02-04 23:59:59 c1 2001-02-04 23:59:59.0 c2 2001-02-04 23:59:59.00 c3 2001-02-04 23:59:59.000 c4 2001-02-04 23:59:59.0000 c5 2001-02-04 23:59:59.00000 c6 2001-02-04 23:59:59.000000 c7 2001-02-04 23:59:59.000000 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' SELECT MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')) AS c0, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0')) AS c1, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00')) AS c2, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000')) AS c3, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000')) AS c4, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000')) AS c5, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000')) AS c6, MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000')) AS c7; c0 0 c1 0 c2 0 c3 0 c4 0 c5 0 c6 0 c7 0 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' SELECT CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS SIGNED) AS ci, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6, CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7; ci 20010204235959 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000000 AS c7; ci 2001-02-04 23:59:59 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 # ADDTIME(time, decimal) SELECT ADDTIME(TIME'00:00:00', 8395959) AS c0, ADDTIME(TIME'00:00:00', 8395959.0) AS c1, ADDTIME(TIME'00:00:00', 8395959.00) AS c2, ADDTIME(TIME'00:00:00', 8395959.000) AS c3, ADDTIME(TIME'00:00:00', 8395959.0000) AS c4, ADDTIME(TIME'00:00:00', 8395959.00000) AS c5, ADDTIME(TIME'00:00:00', 8395959.000000) AS c6, ADDTIME(TIME'00:00:00', 8395959.0000000) AS c7; c0 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 c7 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT MICROSECOND(ADDTIME(TIME'00:00:00', 8395959)) AS c0, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0)) AS c1, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00)) AS c2, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000)) AS c3, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000)) AS c4, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00000)) AS c5, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000000)) AS c6, MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000000)) AS c7; c0 0 c1 900000 c2 990000 c3 999000 c4 999900 c5 999990 c6 999999 c7 999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT CAST(ADDTIME(TIME'00:00:00', 8395959) AS SIGNED) AS ci, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,0)) AS c0, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,1)) AS c1, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,2)) AS c2, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,3)) AS c3, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,4)) AS c4, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,5)) AS c5, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,6)) AS c6, CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,7)) AS c7; ci 8385959 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT ADDTIME(TIME'00:00:00', 8395959) AS ci, ADDTIME(TIME'00:00:00', 8395959)+0 AS c0, ADDTIME(TIME'00:00:00', 8395959)+0.0 AS c1, ADDTIME(TIME'00:00:00', 8395959)+0.00 AS c2, ADDTIME(TIME'00:00:00', 8395959)+0.000 AS c3, ADDTIME(TIME'00:00:00', 8395959)+0.0000 AS c4, ADDTIME(TIME'00:00:00', 8395959)+0.00000 AS c5, ADDTIME(TIME'00:00:00', 8395959)+0.000000 AS c6, ADDTIME(TIME'00:00:00', 8395959)+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' # ADDTIME(time,string) SELECT ADDTIME(TIME'00:00:00', '839:59:59') AS c0, ADDTIME(TIME'00:00:00', '839:59:59.0') AS c1, ADDTIME(TIME'00:00:00', '839:59:59.00') AS c2, ADDTIME(TIME'00:00:00', '839:59:59.000') AS c3, ADDTIME(TIME'00:00:00', '839:59:59.0000') AS c4, ADDTIME(TIME'00:00:00', '839:59:59.00000') AS c5, ADDTIME(TIME'00:00:00', '839:59:59.000000') AS c6, ADDTIME(TIME'00:00:00', '839:59:59.0000000') AS c7; c0 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 c7 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59')) AS c0, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0')) AS c1, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00')) AS c2, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000')) AS c3, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000')) AS c4, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00000')) AS c5, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000000')) AS c6, MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000000')) AS c7; c0 0 c1 900000 c2 990000 c3 999000 c4 999900 c5 999990 c6 999999 c7 999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS SIGNED) AS ci, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6, CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7; ci 8385959 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT ADDTIME(TIME'00:00:00', '839:59:59') AS ci, ADDTIME(TIME'00:00:00', '839:59:59')+0 AS c0, ADDTIME(TIME'00:00:00', '839:59:59')+0.0 AS c1, ADDTIME(TIME'00:00:00', '839:59:59')+0.00 AS c2, ADDTIME(TIME'00:00:00', '839:59:59')+0.000 AS c3, ADDTIME(TIME'00:00:00', '839:59:59')+0.0000 AS c4, ADDTIME(TIME'00:00:00', '839:59:59')+0.00000 AS c5, ADDTIME(TIME'00:00:00', '839:59:59')+0.000000 AS c6, ADDTIME(TIME'00:00:00', '839:59:59')+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' # ADDTIME(int,int) SELECT ADDTIME(0, 8395959) AS c, MICROSECOND(ADDTIME(0, 8395959)) AS cm, CAST(ADDTIME(0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, CAST(ADDTIME(0, 8395959) AS DECIMAL(30,0)) AS cd300; c 838:59:59 cm 0 cs_fixme_mdev_17384 838 cd300 8385959 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect INTEGER value: '838:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT ADDTIME(20010101000000, 8395959) AS c, MICROSECOND(ADDTIME(20010101000000, 8395959)) AS cm, CAST(ADDTIME(20010101000000, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, CAST(ADDTIME(20010101000000, 8395959) AS DECIMAL(30,0)) AS cd300; c 2001-02-04 23:59:59 cm 0 cs_fixme_mdev_17384 2001 cd300 20010204235959 Warnings: Level Warning Code 1292 Message Truncated incorrect INTEGER value: '2001-02-04 23:59:59' # ADDTIME(decimal,int) # 8385960 in cd300 is correct: addtime returns '838:59:59.9' # which is further *rounded* to a decimals(30,0) SELECT ADDTIME(0.0, 8395959) AS c, MICROSECOND(ADDTIME(0.0, 8395959)) AS cm, CAST(ADDTIME(0.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS cd300; c 838:59:59.9 cm 900000 cs_fixme_mdev_17384 838 cd300 8385960 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect INTEGER value: '838:59:59.9' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT ADDTIME(20010101000000.0, 8395959) AS c, MICROSECOND(ADDTIME(20010101000000.0, 8395959)) AS cm, CAST(ADDTIME(20010101000000.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, CAST(ADDTIME(20010101000000.0, 8395959) AS DECIMAL(30,0)) AS cd300; c 2001-02-04 23:59:59.0 cm 0 cs_fixme_mdev_17384 2001 cd300 20010204235959 Warnings: Level Warning Code 1292 Message Truncated incorrect INTEGER value: '2001-02-04 23:59:59.0' # ADDTIME(decimal,decimal) SELECT ADDTIME(0.0, 8395959.0) AS c1, ADDTIME(0.0, 8395959.00) AS c2, ADDTIME(0.0, 8395959.000) AS c3, ADDTIME(0.0, 8395959.0000) AS c4, ADDTIME(0.0, 8395959.00000) AS c5, ADDTIME(0.0, 8395959.000000) AS c6, ADDTIME(0.0, 8395959.0000000) AS c7; c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 c7 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT MICROSECOND(ADDTIME(0.0, 8395959.0)) AS c1, MICROSECOND(ADDTIME(0.0, 8395959.00)) AS c2, MICROSECOND(ADDTIME(0.0, 8395959.000)) AS c3, MICROSECOND(ADDTIME(0.0, 8395959.0000)) AS c4, MICROSECOND(ADDTIME(0.0, 8395959.00000)) AS c5, MICROSECOND(ADDTIME(0.0, 8395959.000000)) AS c6, MICROSECOND(ADDTIME(0.0, 8395959.0000000)) AS c7; c1 900000 c2 990000 c3 999000 c4 999900 c5 999990 c6 999999 c7 999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' # 8385960 in c1 is correct: addtime returns '838:59:59.9' # which is further *rounded* to a decimals(30,0) SELECT CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS c0, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,1)) AS c1, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,2)) AS c2, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,3)) AS c3, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,4)) AS c4, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,5)) AS c5, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,6)) AS c6, CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,7)) AS c7; c0 8385960 c1 8385959.9 c2 8385959.90 c3 8385959.900 c4 8385959.9000 c5 8385959.90000 c6 8385959.900000 c7 8385959.9000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' SELECT ADDTIME(0.0, 8395959)+0 AS c0, ADDTIME(0.0, 8395959)+0.0 AS c1, ADDTIME(0.0, 8395959)+0.00 AS c2, ADDTIME(0.0, 8395959)+0.000 AS c3, ADDTIME(0.0, 8395959)+0.0000 AS c4, ADDTIME(0.0, 8395959)+0.00000 AS c5, ADDTIME(0.0, 8395959)+0.000000 AS c6, ADDTIME(0.0, 8395959)+0.0000000 AS c7; c0 8385959.9 c1 8385959.9 c2 8385959.90 c3 8385959.900 c4 8385959.9000 c5 8385959.90000 c6 8385959.900000 c7 8385959.9000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '839:59:59' # TIMESTAMP(string,decimal) SELECT TIMESTAMP('2001-01-01', 8395959) AS ci, TIMESTAMP('2001-01-01', 8395959.0) AS c1, TIMESTAMP('2001-01-01', 8395959.00) AS c2, TIMESTAMP('2001-01-01', 8395959.000) AS c3, TIMESTAMP('2001-01-01', 8395959.0000) AS c4, TIMESTAMP('2001-01-01', 8395959.00000) AS c5, TIMESTAMP('2001-01-01', 8395959.000000) AS c6, TIMESTAMP('2001-01-01', 8395959.0000000) AS c7; ci 2001-02-04 23:59:59 c1 2001-02-04 23:59:59.0 c2 2001-02-04 23:59:59.00 c3 2001-02-04 23:59:59.000 c4 2001-02-04 23:59:59.0000 c5 2001-02-04 23:59:59.00000 c6 2001-02-04 23:59:59.000000 c7 2001-02-04 23:59:59.000000 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' SELECT MICROSECOND(TIMESTAMP('2001-01-01', 8395959)) AS ci, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0)) AS c1, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00)) AS c2, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000)) AS c3, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000)) AS c4, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00000)) AS c5, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000000)) AS c6, MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000000)) AS c7; ci 0 c1 0 c2 0 c3 0 c4 0 c5 0 c6 0 c7 0 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.0000000' SELECT CAST(TIMESTAMP('2001-01-01', 8395959) AS SIGNED) AS ci, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,0)) AS c0, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,1)) AS c1, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,2)) AS c2, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,3)) AS c3, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,4)) AS c4, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,5)) AS c5, CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,6)) AS c6; ci 20010204235959 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 SELECT TIMESTAMP('2001-01-01', 8395959) AS ci, TIMESTAMP('2001-01-01', 8395959)+0 AS c0, TIMESTAMP('2001-01-01', 8395959)+0.0 AS c1, TIMESTAMP('2001-01-01', 8395959)+0.00 AS c2, TIMESTAMP('2001-01-01', 8395959)+0.000 AS c3, TIMESTAMP('2001-01-01', 8395959)+0.0000 AS c4, TIMESTAMP('2001-01-01', 8395959)+0.00000 AS c5, TIMESTAMP('2001-01-01', 8395959)+0.000000 AS c6, TIMESTAMP('2001-01-01', 8395959)+0.0000000 AS c7; ci 2001-02-04 23:59:59 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 # TIMESTAMP(string,string) SELECT TIMESTAMP('2001-01-01', '839:59:59') AS ci, TIMESTAMP('2001-01-01', '839:59:59.0') AS c1, TIMESTAMP('2001-01-01', '839:59:59.00') AS c2, TIMESTAMP('2001-01-01', '839:59:59.000') AS c3, TIMESTAMP('2001-01-01', '839:59:59.0000') AS c4, TIMESTAMP('2001-01-01', '839:59:59.00000') AS c5, TIMESTAMP('2001-01-01', '839:59:59.000000') AS c6, TIMESTAMP('2001-01-01', '839:59:59.0000000') AS c7; ci 2001-02-04 23:59:59 c1 2001-02-04 23:59:59.0 c2 2001-02-04 23:59:59.00 c3 2001-02-04 23:59:59.000 c4 2001-02-04 23:59:59.0000 c5 2001-02-04 23:59:59.00000 c6 2001-02-04 23:59:59.000000 c7 2001-02-04 23:59:59.000000 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' SELECT MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59')) AS ci, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0')) AS c1, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00')) AS c2, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000')) AS c3, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000')) AS c4, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00000')) AS c5, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000000')) AS c6, MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000000')) AS c7; ci 0 c1 0 c2 0 c3 0 c4 0 c5 0 c6 0 c7 0 Warnings: Level Note Code 1292 Message Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.0000000' SELECT CAST(TIMESTAMP('2001-01-01', '839:59:59') AS SIGNED) AS ci, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,0)) AS c0, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,1)) AS c1, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,2)) AS c2, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,3)) AS c3, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,4)) AS c4, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,5)) AS c5, CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,6)) AS c6; ci 20010204235959 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 SELECT TIMESTAMP('2001-01-01', '839:59:59') AS ci, TIMESTAMP('2001-01-01', '839:59:59')+0 AS c0, TIMESTAMP('2001-01-01', '839:59:59')+0.0 AS c1, TIMESTAMP('2001-01-01', '839:59:59')+0.00 AS c2, TIMESTAMP('2001-01-01', '839:59:59')+0.000 AS c3, TIMESTAMP('2001-01-01', '839:59:59')+0.0000 AS c4, TIMESTAMP('2001-01-01', '839:59:59')+0.00000 AS c5, TIMESTAMP('2001-01-01', '839:59:59')+0.000000 AS c6, TIMESTAMP('2001-01-01', '839:59:59')+0.0000000 AS c7; ci 2001-02-04 23:59:59 c0 20010204235959 c1 20010204235959.0 c2 20010204235959.00 c3 20010204235959.000 c4 20010204235959.0000 c5 20010204235959.00000 c6 20010204235959.000000 c7 20010204235959.0000000 # Corner cases for TIMESTAMP(timestamp,xxx) # HOUR is outside of supported INTERVAL DAYS TO SECONDS range # Expect NULL with INTERVAL warnings CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); INSERT INTO t1 VALUES ('4294967296:00:00', '178956970 16:00:00'); INSERT INTO t1 VALUES ('4294967295:59:59', '178956970 15:59:59'); INSERT INTO t1 VALUES ('4294967294:59:59', '178956970 14:59:59'); INSERT INTO t1 VALUES ('87649416:00:00', '3652059 00:00:00'); SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS t ta tb NULL NULL NULL NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967296:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '178956970 16:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '178956970 15:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967294:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '178956970 14:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '87649416:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '3652059 00:00:00' DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); INSERT INTO t1 VALUES ('-4294967296:00:00', '-178956970 16:00:00'); INSERT INTO t1 VALUES ('-4294967295:59:59', '-178956970 15:59:59'); INSERT INTO t1 VALUES ('-4294967294:59:59', '-178956970 14:59:59'); INSERT INTO t1 VALUES ('-87649416:00:00', '-3652059 00:00:00'); SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999 ta tb NULL NULL NULL NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-4294967296:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-178956970 16:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-4294967295:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-178956970 15:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-4294967294:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-178956970 14:59:59' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-87649416:00:00' Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '-3652059 00:00:00' DROP TABLE t1; # HOUR is OK # Expect max or near-max DATETIME value + no INTERVAL warnings CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); INSERT INTO t1 VALUES ('87649415:59:59.999999', '3652058 23:59:59.999999'); INSERT INTO t1 VALUES ('87649415:59:59', '3652058 23:59:59'); SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS t ta tb 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.000000 9999-12-31 23:59:59.000000 DROP TABLE t1; # HOUR is OK # Expect near '0001-01-01 00:00:00' DATETIME value + no INTERVAL warnings CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); INSERT INTO t1 VALUES ('-87649415:59:59.999999', '-3652058 23:59:59.999999'); INSERT INTO t1 VALUES ('-87649415:59:59', '-3652058 23:59:59'); SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999 ta tb NULL 0001-01-01 00:00:00.000000 0001-01-01 00:00:00.000000 0001-01-01 00:00:00.999999 DROP TABLE t1; # HOUR is OK # Expect NULL on datetime arithmetic overflow + no INTERVAL warnings CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); INSERT INTO t1 VALUES ('-00:00:00.000001', '-0 00:00:00.000001'); SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS t ta tb NULL NULL DROP TABLE t1; # Corner cases for ADDTIME(timestamp,xxx) # HOUR is outside of UINT_MAX32 range # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959) AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59') AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' # HOUR UINT_MAX32 # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959) AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59') AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' # HOUR is max_useful_hour()+1 # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959) AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59') AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' # HOUR is max_useful_hour() # Expect NULL (calc_time_diff overflows ) + no INTERVAL warnings SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959) AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59') AS ci, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0 AS c0, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL # HOUR is max_useful_hour() # Expect non-NULL + no warnings SELECT ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959) AS ci, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0 AS c0, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0 AS c1, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00 AS c2, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000 AS c3, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000 AS c4, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00000 AS c5, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000000 AS c6, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000000 AS c7; ci 9999-12-31 23:59:59 c0 99991231235959 c1 99991231235959.0 c2 99991231235959.00 c3 99991231235959.000 c4 99991231235959.0000 c5 99991231235959.00000 c6 99991231235959.000000 c7 99991231235959.0000000 SELECT ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59') AS ci, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0 AS c0, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6, ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7; ci 9999-12-31 23:59:59 c0 99991231235959 c1 99991231235959.0 c2 99991231235959.00 c3 99991231235959.000 c4 99991231235959.0000 c5 99991231235959.00000 c6 99991231235959.000000 c7 99991231235959.0000000 # Corner cases for ADDTIME(time,xxx) # HOUR outside of UINT32 range # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIME'00:00:00', 42949672965959) AS ci, ADDTIME(TIME'00:00:00', 42949672965959)+0 AS c0, ADDTIME(TIME'00:00:00', 42949672965959)+0.0 AS c1, ADDTIME(TIME'00:00:00', 42949672965959)+0.00 AS c2, ADDTIME(TIME'00:00:00', 42949672965959)+0.000 AS c3, ADDTIME(TIME'00:00:00', 42949672965959)+0.0000 AS c4, ADDTIME(TIME'00:00:00', 42949672965959)+0.00000 AS c5, ADDTIME(TIME'00:00:00', 42949672965959)+0.000000 AS c6, ADDTIME(TIME'00:00:00', 42949672965959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672965959' SELECT ADDTIME(TIME'00:00:00', '4294967296:59:59') AS ci, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0 AS c0, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0 AS c1, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00 AS c2, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000 AS c3, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000 AS c4, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00000 AS c5, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000000 AS c6, ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59' # HOUR is UINT_MAX32 (outside of INTERVAL DAY TO SECOND range) # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIME'00:00:00', 42949672955959) AS ci, ADDTIME(TIME'00:00:00', 42949672955959)+0 AS c0, ADDTIME(TIME'00:00:00', 42949672955959)+0.0 AS c1, ADDTIME(TIME'00:00:00', 42949672955959)+0.00 AS c2, ADDTIME(TIME'00:00:00', 42949672955959)+0.000 AS c3, ADDTIME(TIME'00:00:00', 42949672955959)+0.0000 AS c4, ADDTIME(TIME'00:00:00', 42949672955959)+0.00000 AS c5, ADDTIME(TIME'00:00:00', 42949672955959)+0.000000 AS c6, ADDTIME(TIME'00:00:00', 42949672955959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '42949672955959' SELECT ADDTIME(TIME'00:00:00', '4294967295:59:59') AS ci, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0 AS c0, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0 AS c1, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00 AS c2, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000 AS c3, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0000 AS c4, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00000 AS c5, ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000000 AS c6, ADDTIME(TIME'00:00:00', '4294967295;00:00')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '4294967295;00:00' # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range) # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIME'00:00:00', 876494165959) AS ci, ADDTIME(TIME'00:00:00', 876494165959)+0 AS c0, ADDTIME(TIME'00:00:00', 876494165959)+0.0 AS c1, ADDTIME(TIME'00:00:00', 876494165959)+0.00 AS c2, ADDTIME(TIME'00:00:00', 876494165959)+0.000 AS c3, ADDTIME(TIME'00:00:00', 876494165959)+0.0000 AS c4, ADDTIME(TIME'00:00:00', 876494165959)+0.00000 AS c5, ADDTIME(TIME'00:00:00', 876494165959)+0.000000 AS c6, ADDTIME(TIME'00:00:00', 876494165959)+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' SELECT ADDTIME(TIME'00:00:00', '87649416:59:59') AS ci, ADDTIME(TIME'00:00:00', '87649416:59:59')+0 AS c0, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0 AS c1, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00 AS c2, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000 AS c3, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000 AS c4, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00000 AS c5, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000000 AS c6, ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000000 AS c7; ci NULL c0 NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL c7 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range) # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" SELECT ADDTIME(TIME'-838:59:59', 876494165959) AS ci, ADDTIME(TIME'-838:59:59.9', 876494165959) AS c1, ADDTIME(TIME'-838:59:59.99', 876494165959) AS c2, ADDTIME(TIME'-838:59:59.999', 876494165959) AS c3, ADDTIME(TIME'-838:59:59.9999', 876494165959) AS c4, ADDTIME(TIME'-838:59:59.99999', 876494165959) AS c5, ADDTIME(TIME'-838:59:59.999999', 876494165959) AS c6; ci NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '876494165959' SELECT ADDTIME(TIME'-838:59:59', '87649416:59:59') AS ci, ADDTIME(TIME'-838:59:59.9', '87649416:59:59') AS c1, ADDTIME(TIME'-838:59:59.99', '87649416:59:59') AS c2, ADDTIME(TIME'-838:59:59.999', '87649416:59:59') AS c3, ADDTIME(TIME'-838:59:59.9999', '87649416:59:59') AS c4, ADDTIME(TIME'-838:59:59.99999', '87649416:59:59') AS c5, ADDTIME(TIME'-838:59:59.999999', '87649416:59:59') AS c6; ci NULL c1 NULL c2 NULL c3 NULL c4 NULL c5 NULL c6 NULL Warnings: Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' Level Warning Code 1292 Message Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59' SELECT ADDTIME(TIME'-838:59:59.9999999', '87649416:59:59') AS c7; c7 NULL # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range) # Expect max TIME(0) + zero fraction + TIME warnings + no INTERVAL warnings SELECT ADDTIME(TIME'00:00:00', 876494155959) AS ci, ADDTIME(TIME'00:00:00', 876494155959)+0 AS c0, ADDTIME(TIME'00:00:00', 876494155959)+0.0 AS c1, ADDTIME(TIME'00:00:00', 876494155959)+0.00 AS c2, ADDTIME(TIME'00:00:00', 876494155959)+0.000 AS c3, ADDTIME(TIME'00:00:00', 876494155959)+0.0000 AS c4, ADDTIME(TIME'00:00:00', 876494155959)+0.00000 AS c5, ADDTIME(TIME'00:00:00', 876494155959)+0.000000 AS c6, ADDTIME(TIME'00:00:00', 876494155959)+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' SELECT ADDTIME(TIME'00:00:00', '87649415:59:59') AS ci, ADDTIME(TIME'00:00:00', '87649415:59:59')+0 AS c0, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0 AS c1, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00 AS c2, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000 AS c3, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000 AS c4, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00000 AS c5, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000000 AS c6, ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000000 AS c7; ci 838:59:59 c0 8385959 c1 8385959.0 c2 8385959.00 c3 8385959.000 c4 8385959.0000 c5 8385959.00000 c6 8385959.000000 c7 8385959.0000000 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' Level Warning Code 1292 Message Truncated incorrect time value: '87649415:59:59' # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range) # Expect max TIME(N) + TIME warnings + no INTERVAL warnings SELECT ADDTIME(TIME'-838:59:59', 876494155959) AS ci, ADDTIME(TIME'-838:59:59.9', 876494155959) AS c1, ADDTIME(TIME'-838:59:59.99', 876494155959) AS c2, ADDTIME(TIME'-838:59:59.999', 876494155959) AS c3, ADDTIME(TIME'-838:59:59.9999', 876494155959) AS c4, ADDTIME(TIME'-838:59:59.99999', 876494155959) AS c5, ADDTIME(TIME'-838:59:59.999999', 876494155959) AS c6; ci 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '87648577:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.100000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.010000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.001000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000100' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000010' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000001' SELECT ADDTIME(TIME'-838:59:59', '87649415:59:59') AS ci, ADDTIME(TIME'-838:59:59.9', '87649415:59:59') AS c1, ADDTIME(TIME'-838:59:59.99', '87649415:59:59') AS c2, ADDTIME(TIME'-838:59:59.999', '87649415:59:59') AS c3, ADDTIME(TIME'-838:59:59.9999', '87649415:59:59') AS c4, ADDTIME(TIME'-838:59:59.99999', '87649415:59:59') AS c5, ADDTIME(TIME'-838:59:59.999999', '87649415:59:59') AS c6; ci 838:59:59 c1 838:59:59.9 c2 838:59:59.99 c3 838:59:59.999 c4 838:59:59.9999 c5 838:59:59.99999 c6 838:59:59.999999 Warnings: Level Warning Code 1292 Message Truncated incorrect time value: '87648577:00:00' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.100000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.010000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.001000' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000100' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000010' Level Warning Code 1292 Message Truncated incorrect time value: '87648576:59:59.000001' SELECT ADDTIME(TIME'-838:59:59.9999999', '87649415:59:59') AS c7; c7 838:59:59.999999 # # MDEV-17400 The result of TIME('42949672965959-01') depends on architecture # SELECT TIME('42949672955959-01'), TIME('42949672965959-01'); TIME('42949672955959-01') TIME('42949672965959-01') NULL NULL Warnings: Warning 1292 Incorrect time value: '42949672955959-01' Warning 1292 Incorrect time value: '42949672965959-01' SELECT TIME('18446744073709551615-01'), TIME('18446744073709551616-01'); TIME('18446744073709551615-01') TIME('18446744073709551616-01') NULL NULL Warnings: Warning 1292 Incorrect time value: '18446744073709551615-01' Warning 1292 Incorrect time value: '18446744073709551616-01' # # MDEV-17434 EXTRACT(DAY FROM negative_time) returns wrong result # CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('-24:10:10.10'); SELECT EXTRACT(MINUTE FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a) FROM t1; EXTRACT(MINUTE FROM a) EXTRACT(SECOND FROM a) EXTRACT(MICROSECOND FROM a) EXTRACT(DAY FROM -10 -10 -100000 -1 -100 -10010 -1001010 -1001010100000 CREATE TABLE t2 AS SELECT EXTRACT(MINUTE FROM a), EXTRACT(SECOND FROM a), EXTRACT(MICROSECOND FROM a), EXTRACT(DAY FROM a), EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_MINUTE FROM a), EXTRACT(DAY_SECOND FROM a), EXTRACT(DAY_MICROSECOND FROM a) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, `EXTRACT(DAY FROM a)` int(3) 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(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t2; DROP TABLE t1; # # MDEV-17478 Wrong result for TIME('+100:20:30') # SELECT TIME('+100:20:30'); TIME('+100:20:30') 100:20:30 # # MDEV-17477 Wrong result for TIME('-2001-01-01 10:20:30') # SELECT TIME('-2001-01-01 10:20:30'); TIME('-2001-01-01 10:20:30') NULL Warnings: Warning 1292 Incorrect time value: '-2001-01-01 10:20:30' SELECT TIME('2001-01-01') AS c1, TIME('2001-01-01 ') AS c2; c1 c2 00:20:01 00:20:01 Warnings: Warning 1292 Truncated incorrect time value: '2001-01-01' Warning 1292 Truncated incorrect time value: '2001-01-01 ' SELECT TIME('0001:01:01 '), TIME('0001:01:01 '); TIME('0001:01:01 ') TIME('0001:01:01 ') 01:01:01 01:01:01 SELECT TIME('1 2'), TIME('1 2 '); TIME('1 2') TIME('1 2 ') 00:00:01 00:00:01 Warnings: Warning 1292 Truncated incorrect time value: '1 2' Warning 1292 Truncated incorrect time value: '1 2 ' SELECT TIME('2001-01-01T'), TIME('2001-01-01T '); TIME('2001-01-01T') TIME('2001-01-01T ') 00:00:00 00:00:00 Warnings: Warning 1292 Truncated incorrect date value: '2001-01-01T' Warning 1292 Truncated incorrect date value: '2001-01-01T ' SELECT TIME('901-01-01T1'), TIME('901-01-01T10'); TIME('901-01-01T1') TIME('901-01-01T10') 01:00:00 10:00:00 SELECT TIME('091-01-01T1'), TIME('091-01-01T10'); TIME('091-01-01T1') TIME('091-01-01T10') 01:00:00 10:00:00 SELECT TIME('0001:01:01x'), TIME('0001:01:01xx'); TIME('0001:01:01x') TIME('0001:01:01xx') 01:01:01 01:01:01 Warnings: Warning 1292 Truncated incorrect time value: '0001:01:01x' Warning 1292 Truncated incorrect time value: '0001:01:01xx' SELECT TIME('0001:01:01.'), TIME('0001:01:01..'); TIME('0001:01:01.') TIME('0001:01:01..') 01:01:01 01:01:01 Warnings: Warning 1292 Truncated incorrect time value: '0001:01:01..' SELECT TIME('0001:01:01-'), TIME('0001:01:01--'); TIME('0001:01:01-') TIME('0001:01:01--') 01:01:01 01:01:01 Warnings: Warning 1292 Truncated incorrect time value: '0001:01:01-' Warning 1292 Truncated incorrect time value: '0001:01:01--' SELECT TIME('0001:01:01-'), TIME('0001:01:01--'); TIME('0001:01:01-') TIME('0001:01:01--') 01:01:01 01:01:01 Warnings: Warning 1292 Truncated incorrect time value: '0001:01:01-' Warning 1292 Truncated incorrect time value: '0001:01:01--' SELECT TIME('-xxx'), TIME('-xxxxxxxxxxxxxxxxxxxx'); TIME('-xxx') TIME('-xxxxxxxxxxxxxxxxxxxx') NULL NULL Warnings: Warning 1292 Incorrect time value: '-xxx' Warning 1292 Incorrect time value: '-xxxxxxxxxxxxxxxxxxxx' SELECT TIME('- '), TIME('- '); TIME('- ') TIME('- ') NULL NULL Warnings: Warning 1292 Incorrect time value: '- ' Warning 1292 Incorrect time value: '- ' SELECT TIME('-'), TIME('-'); TIME('-') TIME('-') NULL NULL Warnings: Warning 1292 Incorrect time value: '-' Warning 1292 Incorrect time value: '-' SELECT TIME('1-1-1 1:1:1'), TIME('1-1-1 1:1:1.0'); TIME('1-1-1 1:1:1') TIME('1-1-1 1:1:1.0') 01:01:01 01:01:01.0 SELECT TIME('1-1-1 1:2:3'), TIME('1-1-1 1:2:3.0'); TIME('1-1-1 1:2:3') TIME('1-1-1 1:2:3.0') 01:02:03 01:02:03.0 SELECT CAST('20050326112233 garbage' as datetime), CAST('20050326 garbage' as date), CAST('50326 garbage' as time); CAST('20050326112233 garbage' as datetime) CAST('20050326 garbage' as date) CAST('50326 g 2005-03-26 11:22:33 2005-03-26 05:03:26 Warnings: Warning 1292 Truncated incorrect datetime value: '20050326112233 garbage' Warning 1292 Truncated incorrect date value: '20050326 garbage' Warning 1292 Truncated incorrect time value: '50326 garbage' SELECT TIME('- 01:00:00'), TIME('- 1 01:00:00'); TIME('- 01:00:00') TIME('- 1 01:00:00') -01:00:00 -25:00:00 # # MDEV-17854 Assertion `decimals <= 6' failed in my_time_fraction_remainder on SELECT with NU # SET time_zone='+00:00'; SELECT NULLIF(FROM_UNIXTIME('foo'), '2012-12-12 21:10:14'); NULLIF(FROM_UNIXTIME('foo'), '2012-12-12 21:10:14') NULL Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'foo' Warning 1292 Truncated incorrect unixtime value: '0.0' Warning 1292 Truncated incorrect DECIMAL value: 'foo' Warning 1292 Truncated incorrect unixtime value: '0.0' SET time_zone=DEFAULT; # # MDEV-18402 Assertion `sec.sec() <= 59' failed in Item_func_maketime::get_date # SELECT MAKETIME('01', '01', LEAST( -100, NULL )); MAKETIME('01', '01', LEAST( -100, NULL )) NULL SELECT CONCAT(MAKETIME('01', '01', LEAST( -100, NULL ))); CONCAT(MAKETIME('01', '01', LEAST( -100, NULL ))) NULL # # MDEV-19774 Assertion `sec.se c() <= 0x7FFFFFFFL' failed in Item_func_from_unixtime::get_ # SELECT FROM_UNIXTIME(LEAST(3696610869, NULL)); FROM_UNIXTIME(LEAST(3696610869, NULL)) NULL # # Start of 10.5 tests # # # MDEV-29149 Assertion `!is_valid_datetime() || fraction_remainder(((item->decimals) < (6 # SET @@timestamp= UNIX_TIMESTAMP('2022-07-21 23:00:00'); SELECT DATE_SUB('2022-07-21 00:00:00', INTERVAL 800 HOUR) AS expected_result; expected_result 2022-06-17 16:00:00 SELECT IF(1,TIMEDIFF('38:59:59','839:00:00'),CAST('2022-12-12' AS DATE)) AS c1, IF(1,TIMEDIFF('-839:00:00','-38:59:59'),CAST('2022-12-12' AS DATE)) AS c2; c1 c2 2022-06-17 16:00:00 2022-06-17 16:00:00 Warnings: Warning 1292 Truncated incorrect time value: '839:00:00' Warning 1292 Truncated incorrect time value: '-839:00:00' SELECT IF(1,TIMEDIFF(385959,8390000),CAST('2022-12-12' AS DATE)) AS c1, IF(1,TIMEDIFF(-8390000,-385959),CAST('2022-12-12' AS DATE)) AS c2; c1 c2 2022-06-17 16:00:00 2022-06-17 16:00:00 Warnings: Warning 1292 Truncated incorrect time value: '8390000' Warning 1292 Truncated incorrect time value: '-8390000' SELECT TIMEDIFF('38:59:59','839:00:00') AS c1, CAST(TIMEDIFF('38:59:59','839:00:00') AS TIME(6)) AS c2, TIMEDIFF('839:00:00','38:59:59') AS c3, CAST(TIMEDIFF('839:00:00','38:59:59') AS TIME(6)) AS c4; c1 c2 c3 c4 -800:00:00 -800:00:00.000000 800:00:00 800:00:00.000000 Warnings: Warning 1292 Truncated incorrect time value: '839:00:00' Warning 1292 Truncated incorrect time value: '839:00:00' Warning 1292 Truncated incorrect time value: '839:00:00' Warning 1292 Truncated incorrect time value: '839:00:00' SELECT TIMEDIFF(385959,8390000) AS c1, CAST(TIMEDIFF(385959,8390000) AS TIME(6)) AS c2, TIMEDIFF(8390000,385959) AS c3, CAST(TIMEDIFF(8390000,385959) AS TIME(6)) AS c4; c1 c2 c3 c4 -800:00:00 -800:00:00.000000 800:00:00 800:00:00.000000 Warnings: Warning 1292 Truncated incorrect time value: '8390000' Warning 1292 Truncated incorrect time value: '8390000' Warning 1292 Truncated incorrect time value: '8390000' Warning 1292 Truncated incorrect time value: '8390000' SET @@timestamp= DEFAULT; # # MDEV-31302 Assertion `mon > 0 && mon < 13' failed in my_time_t sec_since_epoch(int, int, int, int, # CREATE TABLE t1 (a DATE); SET @@time_zone='+1:00'; INSERT INTO t1 VALUES ('2024-00-01'); SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1; a NULL SET @@time_zone=DEFAULT; DROP TABLE t1; # End of 10.5 tests # # MDEV-38006 Inconsistent behaviors when casting into time # create table t1 (c1 char(20), c2 mediumint unsigned); insert into t1 values (9430886, 9430886), (9430816, 9430816); select cast(c1 as time), cast(c2 as time) from t1; cast(c1 as time) cast(c2 as time) NULL NULL 838:59:59 838:59:59 Warnings: Warning 1292 Incorrect time value: '9430886' for column `test`.`t1`.`c1` at row 1 Warning 1292 Incorrect time value: '9430886' for column `test`.`t1`.`c2` at row 1 Warning 1292 Incorrect time value: '9430816' for column `test`.`t1`.`c1` at row 2 Warning 1292 Incorrect time value: '9430816' for column `test`.`t1`.`c2` at row 2 drop table t1; # End of 10.11 tests # # MDEV-34829 LOCALTIME returns a wrong data type # SET timestamp=UNIX_TIMESTAMP('2024-08-28 14:47:00'); SELECT LOCALTIME; LOCALTIME 14:47:00 EXPLAIN EXTENDED SELECT LOCALTIME; 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 curtime() AS `LOCALTIME` CREATE TABLE t1 AS SELECT LOCALTIME; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `LOCALTIME` time NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci SELECT * FROM t1; LOCALTIME 14:47:00 DROP TABLE t1; SET timestamp=DEFAULT; # End of 11.6 tests # # MDEV-15751 CURRENT_TIMESTAMP should return a TIMESTAMP [WITH TIME ZONE?] # CREATE TABLE t1 (localtimestamp TIMESTAMP); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Mari CREATE TABLE t1 (current_timestamp TIMESTAMP); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Mari CREATE TABLE t1 AS SELECT localtimestamp, localtimestamp(), localtimestamp(0), localtimestamp(1), localtimestamp(2), localtimestamp(3), localtimestamp(4), localtimestamp(5), localtimestamp(6); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `localtimestamp` datetime NOT NULL, `localtimestamp()` datetime NOT NULL, `localtimestamp(0)` datetime NOT NULL, `localtimestamp(1)` datetime(1) NOT NULL, `localtimestamp(2)` datetime(2) NOT NULL, `localtimestamp(3)` datetime(3) NOT NULL, `localtimestamp(4)` datetime(4) NOT NULL, `localtimestamp(5)` datetime(5) NOT NULL, `localtimestamp(6)` datetime(6) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; CREATE TABLE t1 AS SELECT current_timestamp(), current_timestamp(0), current_timestamp(1), current_timestamp(2), current_timestamp(3), current_timestamp(4), current_timestamp(5), current_timestamp(6); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `current_timestamp()` timestamp NOT NULL, `current_timestamp(0)` timestamp NOT NULL, `current_timestamp(1)` timestamp(1) NOT NULL, `current_timestamp(2)` timestamp(2) NOT NULL, `current_timestamp(3)` timestamp(3) NOT NULL, `current_timestamp(4)` timestamp(4) NOT NULL, `current_timestamp(5)` timestamp(5) NOT NULL, `current_timestamp(6)` timestamp(6) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; CREATE TABLE t1 AS SELECT sysdate(), sysdate(0), sysdate(1), sysdate(2), sysdate(3), sysdate(4), sysdate(5), sysdate(6); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `sysdate()` timestamp NOT NULL, `sysdate(0)` timestamp NOT NULL, `sysdate(1)` timestamp(1) NOT NULL, `sysdate(2)` timestamp(2) NOT NULL, `sysdate(3)` timestamp(3) NOT NULL, `sysdate(4)` timestamp(4) NOT NULL, `sysdate(5)` timestamp(5) NOT NULL, `sysdate(6)` timestamp(6) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; CREATE TABLE t1 AS SELECT from_unixtime(1000000e0), from_unixtime(1000000), from_unixtime(1000000.1), from_unixtime(1000000.12), from_unixtime(1000000.123), from_unixtime(1000000.1234), from_unixtime(1000000.12345), from_unixtime(1000000.123456), from_unixtime(1000000.1234567), from_unixtime(1000000.12345678), from_unixtime(1000000.123456789), from_unixtime(1000000.1234567891), from_unixtime(1000000.12345678912); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `from_unixtime(1000000e0)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000)` timestamp NULL DEFAULT NULL, `from_unixtime(1000000.1)` timestamp(1) NULL DEFAULT NULL, `from_unixtime(1000000.12)` timestamp(2) NULL DEFAULT NULL, `from_unixtime(1000000.123)` timestamp(3) NULL DEFAULT NULL, `from_unixtime(1000000.1234)` timestamp(4) NULL DEFAULT NULL, `from_unixtime(1000000.12345)` timestamp(5) NULL DEFAULT NULL, `from_unixtime(1000000.123456)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000.1234567)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000.12345678)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000.123456789)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000.1234567891)` timestamp(6) NULL DEFAULT NULL, `from_unixtime(1000000.12345678912)` timestamp(6) NULL DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; # End of 11.7 tests
¤ Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.0.237Bemerkung: (vorverarbeitet am 2026-10-08) ¤*Bot Zugriff |
|
||||||||||||||
2026-10-10