Merge with 5.1-microseconds

A lot of small fixes and new test cases.

client/mysqlbinlog.cc:
  Cast removed
client/mysqltest.cc:
  Added missing DBUG_RETURN
include/my_pthread.h:
  set_timespec_time_nsec() now only takes one argument
mysql-test/t/date_formats.test:
  Remove --disable_ps_protocl as now also ps supports microseconds
mysys/my_uuid.c:
  Changed to use my_interval_timer() instead of my_getsystime()
mysys/waiting_threads.c:
  Changed to use my_hrtime()
sql/field.h:
  Added bool special_const_compare() for fields that may convert values before compare (like year)
sql/field_conv.cc:
  Added test to get optimal copying of identical temporal values.
sql/item.cc:
  Return that item_int is equal if it's positive, even if unsigned flag is different.
  Fixed Item_cache_str::save_in_field() to have identical null check as other similar functions
  Added proper NULL check to Item_cache_int::save_in_field()
sql/item_cmpfunc.cc:
  Don't call convert_constant_item() if there is nothing that is worth converting.
  Simplified test when years should be converted
sql/item_sum.cc:
  Mark cache values in Item_sum_hybrid as not constants to ensure they are not replaced by other cache values in compare_datetime()
sql/item_timefunc.cc:
  Changed sec_to_time() to take a my_decimal argument to ensure we don't loose any sub seconds.
  Added Item_temporal_func::get_time() (This simplifies some things)
sql/mysql_priv.h:
  Added Lazy_string_decimal()
sql/mysqld.cc:
  Added my_decimal constants max_seconds_for_time_type, time_second_part_factor
sql/table.cc:
  Changed expr_arena to be of type CONVENTIONAL_EXECUTION to ensure that we don't loose any items that are created by fix_fields()
sql/tztime.cc:
  TIME_to_gmt_sec() now sets *in_dst_time_gap in case of errors
  This is needed to be able to detect if timestamp is 0
storage/maria/lockman.c:
  Changed from my_getsystime() to set_timespec_time_nsec()
storage/maria/ma_loghandler.c:
  Changed from my_getsystime() to my_hrtime()
storage/maria/ma_recovery.c:
  Changed from my_getsystime() to mmicrosecond_interval_timer()
storage/maria/unittest/trnman-t.c:
  Changed from my_getsystime() to mmicrosecond_interval_timer()
storage/xtradb/handler/ha_innodb.cc:
  Added support for new time,datetime and timestamp
unittest/mysys/thr_template.c:
  my_getsystime() -> my_interval_timer()
unittest/mysys/waiting_threads-t.c:
  my_getsystime() -> my_interval_timer()

1 files changed, 232 insertions, 30 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index f99f2a09fc9..b2a47377b05 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -8,20 +8,25 @@ period_add("9602",-12)	period_diff(199505,"9404")
 199502	13
 select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
 now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
-0.000000	0	0
+0	0	0
 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
 from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
-1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112.000000
+1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112
 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
 sec_to_time(time_to_sec("0:30:47")/6.21);
 sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
-02:30:01	23001.000000	54742	00:04:57
+02:30:01	23001	54742	00:04:57.423510
+select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899);
+sec_to_time(9001.1)	time_to_sec('15:12:22.123456')	time_to_sec(15.5566778899)
+02:30:01.1	54742.123456	15.556677
+Warnings:
+Warning	1292	Truncated incorrect time value: '15.5566778899'
 select sec_to_time(time_to_sec('-838:59:59'));
 sec_to_time(time_to_sec('-838:59:59'))
 -838:59:59
 select now()-curdate()*1000000-curtime();
 now()-curdate()*1000000-curtime()
-0.000000
+0
 select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
 strcmp(current_timestamp(),concat(current_date()," ",current_time()))
 0
@@ -52,6 +57,9 @@ DAYOFYEAR("1997-03-03")	WEEK("1998-03-03")	QUARTER(980303)
 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.330000
 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
@@ -546,7 +554,7 @@ unix_timestamp(@a)
 1
 select unix_timestamp('1969-12-01 19:00:01');
 unix_timestamp('1969-12-01 19:00:01')
-0
+NULL
 select from_unixtime(-1);
 from_unixtime(-1)
 NULL
@@ -567,22 +575,22 @@ unix_timestamp(from_unixtime(2147483648))
 NULL
 select unix_timestamp('2039-01-20 01:00:00');
 unix_timestamp('2039-01-20 01:00:00')
-0
+NULL
 select unix_timestamp('1968-01-20 01:00:00');
 unix_timestamp('1968-01-20 01:00:00')
-0
+NULL
 select unix_timestamp('2038-02-10 01:00:00');
 unix_timestamp('2038-02-10 01:00:00')
-0
+NULL
 select unix_timestamp('1969-11-20 01:00:00');
 unix_timestamp('1969-11-20 01:00:00')
-0
+NULL
 select unix_timestamp('2038-01-20 01:00:00');
 unix_timestamp('2038-01-20 01:00:00')
-0
+NULL
 select unix_timestamp('1969-12-30 01:00:00');
 unix_timestamp('1969-12-30 01:00:00')
-0
+NULL
 select unix_timestamp('2038-01-17 12:00:00');
 unix_timestamp('2038-01-17 12:00:00')
 2147331600
@@ -591,7 +599,7 @@ unix_timestamp('1970-01-01 03:00:01')
 1
 select unix_timestamp('2038-01-19 07:14:07');
 unix_timestamp('2038-01-19 07:14:07')
-0
+NULL
 SELECT CHARSET(DAYNAME(19700101));
 CHARSET(DAYNAME(19700101))
 latin1
@@ -798,9 +806,7 @@ 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)
-NULL
-Warnings:
-Warning	1264	Out of range value for column 'time' at row 1
+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,
@@ -814,7 +820,7 @@ 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	NO		0000-00-00	
+a	date	YES		NULL	
 b	date	YES		NULL	
 select * from t1;
 a	b
@@ -941,10 +947,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `now() - now()` double(23,6) NOT NULL DEFAULT '0.000000',
-  `curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000',
-  `sec_to_time(1) + 0` double(23,6) DEFAULT NULL,
-  `from_unixtime(1) + 0` double(23,6) DEFAULT NULL
+  `now() - now()` double(17,0) NOT NULL DEFAULT '0',
+  `curtime() - curtime()` double(17,0) NOT NULL DEFAULT '0',
+  `sec_to_time(1) + 0` double(17,0) DEFAULT NULL,
+  `from_unixtime(1) + 0` double(17,0) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 SELECT SEC_TO_TIME(3300000);
@@ -954,7 +960,7 @@ Warnings:
 Warning	1292	Truncated incorrect time value: '3300000'
 SELECT SEC_TO_TIME(3300000)+0;
 SEC_TO_TIME(3300000)+0
-8385959.000000
+8385959
 Warnings:
 Warning	1292	Truncated incorrect time value: '3300000'
 SELECT SEC_TO_TIME(3600 * 4294967296);
@@ -964,31 +970,31 @@ Warnings:
 Warning	1292	Truncated incorrect time value: '15461882265600'
 SELECT TIME_TO_SEC('916:40:00');
 TIME_TO_SEC('916:40:00')
-3020399
+3020399.999999
 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
+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
+838:59:59.999999
 Warnings:
 Warning	1292	Truncated incorrect time value: '916:40:00'
-Warning	1292	Truncated incorrect time value: '1255:39:59'
+Warning	1292	Truncated incorrect time value: '1255:39:59.999999'
 SELECT SUBTIME('916:40:00', '416:40:00');
 SUBTIME('916:40:00', '416:40:00')
-422:19:59
+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
+-838:59:59.999999
 Warnings:
 Warning	1292	Truncated incorrect time value: '-916:40:00'
-Warning	1292	Truncated incorrect time value: '-1255:39:59'
+Warning	1292	Truncated incorrect time value: '-1255:39:59.999999'
 SELECT MAKETIME(916,0,0);
 MAKETIME(916,0,0)
 838:59:59
@@ -1220,6 +1226,13 @@ 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
 NULL
+Warnings:
+Error	1411	Incorrect datetime value: '10:00 PM' for function str_to_date
+select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute;
+str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute
+NULL
+Warnings:
+Error	1411	Incorrect datetime value: '1997-00-04 22:23:00' for function str_to_date
 create table t1 (field DATE);
 insert into t1 values ('2006-11-06');
 select * from t1 where field < '2006-11-06 04:08:36.0';
@@ -1399,8 +1412,6 @@ NULL
 # Bug#11766126  59166: ANOTHER DATETIME VALGRIND UNINITIALIZED WARNING
 #
 SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025));
-CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025))
-NULL
 #
 # Bug#11766124  59164: VALGRIND: UNINITIALIZED VALUE IN NUMBER_TO_DATETIME
 #
@@ -1420,3 +1431,194 @@ SELECT DATE_FORMAT('0000-00-11', '%w');
 DATE_FORMAT('0000-00-11', '%w')
 NULL
 End of 5.1 tests
+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 cast('131415.123e0' as time);
+cast('131415.123e0' as time)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect time value: '131415.123e0'
+select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04';
+cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04'
+0
+select least(time('1:2:3'), '01:02:04', null) div 1;
+least(time('1:2:3'), '01:02:04', null) div 1
+NULL
+select truncate(least(time('1:2:3'), '01:02:04', null), 6);
+truncate(least(time('1:2:3'), '01:02:04', null), 6)
+NULL
+select cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1));
+cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1))
+NULL
+select unix_timestamp(null);
+unix_timestamp(null)
+NULL
+select truncate(date('2010-40-10'), 6);
+truncate(date('2010-40-10'), 6)
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '2010-40-10'
+select extract(month from '2010-40-50');
+extract(month from '2010-40-50')
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '2010-40-50'
+select subtime('0000-00-10 10:10:10', '30 10:00:00');
+subtime('0000-00-10 10:10:10', '30 10:00:00')
+NULL
+select cast(str_to_date(NULL, '%H:%i:%s') as time);
+cast(str_to_date(NULL, '%H:%i:%s') as time)
+NULL
+create table t1 (f1 datetime, key (f1));
+insert into t1 values ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
+select * from t1 where f1 > time('-23:00:06');
+f1
+2000-09-12 00:00:00
+2007-04-25 05:08:49
+Warnings:
+Warning	1292	Incorrect datetime value: '-23:00:06' for column 'f1' at row 1
+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
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+select last_day(f2) from t1 where last_day(f2) is null;
+last_day(f2)
+NULL
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+select * from t1 order by last_day (f2);
+f2
+0
+0
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+drop table t1;
+select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow');
+convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow')
+NULL
+create table t1 (f1 integer, f2 date);
+insert into t1 values (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05'),(6, '2011-05-06');
+select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125);
+f1	f2
+1	2011-05-05
+2	2011-05-05
+3	2011-05-05
+4	2011-05-05
+5	2011-05-05
+drop table t1;
+create table t1 (f1 timestamp);
+insert into t1 values ('0000-00-00 00:00:00');
+select least(1, f1) from t1;
+least(1, f1)
+0000-00-00 00:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '1' for column 'f1' at row 1
+drop table t1;
+select now() > coalesce(time('21:43:24'), date('2010-05-03'));
+now() > coalesce(time('21:43:24'), date('2010-05-03'))
+1
+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
+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;
+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))
+0000-00-00
+select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00';
+greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00'
+1
+select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6));
+cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6))
+0000-00-00 00:00:00.000000