revert a suggested "optimization" that introduced a bug

compilation error in mysys/my_getsystime.c fixed
some redundant code removed
sec_to_time, time_to_sec, from_unixtime, unix_timestamp, @@timestamp now
  use decimal, not double for numbers with a fractional part.
purge_master_logs_before_date() fixed
many bugs in corner cases fixed

mysys/my_getsystime.c:
  compilation failure fixed
sql/sql_parse.cc:
  don't cut corners. it backfires.

1 files changed, 106 insertions, 0 deletions

diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result
index 75b6b60e4d5..d1702325633 100644
--- a/mysql-test/r/type_timestamp_hires.result
+++ b/mysql-test/r/type_timestamp_hires.result
@@ -105,7 +105,113 @@ Table	Create Table
 t3	CREATE TABLE `t3` (
   `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t2, t3;
+insert t1 values ('2010-12-13 14:15:16.222222');
+select a, a+0, a-1, a*1, a/2 from t1;
+a	a+0	a-1	a*1	a/2
+2012-12-11 01:02:13.3332	20121211010213.3332	20121211010212.3332	20121211010213.3332	10060605505106.66660000
+2010-12-13 14:15:16.2222	20101213141516.2222	20101213141515.2222	20101213141516.2222	10050606570758.11110000
+select max(a), min(a), sum(a), avg(a) from t1;
+max(a)	min(a)	sum(a)	avg(a)
+2012-12-11 01:02:13.3332	2010-12-13 14:15:16.2222	40222424151729.5554	20111212075864.77770000
+create table t2 select a, a+0, a-1, a*1, a/2 from t1;
+create table t3 select max(a), min(a), sum(a), avg(a) from t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000',
+  `a+0` decimal(25,4) NOT NULL DEFAULT '0.0000',
+  `a-1` decimal(25,4) NOT NULL DEFAULT '0.0000',
+  `a*1` decimal(25,4) NOT NULL DEFAULT '0.0000',
+  `a/2` decimal(28,8) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `max(a)` timestamp(4) NULL DEFAULT NULL,
+  `min(a)` timestamp(4) NULL DEFAULT NULL,
+  `sum(a)` decimal(46,4) DEFAULT NULL,
+  `avg(a)` decimal(28,8) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2, t3;
+create table t1 (f0_timestamp timestamp(0), f1_timestamp timestamp(1), f2_timestamp timestamp(2), f3_timestamp timestamp(3), f4_timestamp timestamp(4), f5_timestamp timestamp(5), f6_timestamp timestamp(6));
+insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
+select * from t1;
+f0_timestamp	2010-11-12 11:14:17
+f1_timestamp	2010-11-12 11:14:17.7
+f2_timestamp	2010-11-12 11:14:17.76
+f3_timestamp	2010-11-12 11:14:17.765
+f4_timestamp	2010-11-12 11:14:17.7654
+f5_timestamp	2010-11-12 11:14:17.76543
+f6_timestamp	2010-11-12 11:14:17.765432
+select cast(f0_timestamp as time(4)) time4_f0_timestamp, cast(f1_timestamp as datetime(3)) datetime3_f1_timestamp, cast(f2_timestamp as date) date_f2_timestamp, cast(f4_timestamp as decimal(40,5)) decimal5_f4_timestamp, cast(f5_timestamp as signed) bigint_f5_timestamp, cast(f6_timestamp as char(255)) varchar_f6_timestamp from t1;
+time4_f0_timestamp	11:14:17.0000
+datetime3_f1_timestamp	2010-11-12 11:14:17.700
+date_f2_timestamp	2010-11-12
+decimal5_f4_timestamp	20101112111417.76540
+bigint_f5_timestamp	20101112111417
+varchar_f6_timestamp	2010-11-12 11:14:17.765432
+create table t2 (time4_f0_timestamp time(4), datetime3_f1_timestamp datetime(3), date_f2_timestamp date, double_f3_timestamp double, decimal5_f4_timestamp decimal(40,5), bigint_f5_timestamp bigint, varchar_f6_timestamp varchar(255));
+insert t2 select * from t1;
+Warnings:
+Level	Note
+Code	1265
+Message	Data truncated for column 'time4_f0_timestamp' at row 1
+Level	Note
+Code	1265
+Message	Data truncated for column 'date_f2_timestamp' at row 1
+select * from t2;
+time4_f0_timestamp	11:14:17.0000
+datetime3_f1_timestamp	2010-11-12 11:14:17.700
+date_f2_timestamp	2010-11-12
+double_f3_timestamp	20101112111417.8
+decimal5_f4_timestamp	20101112111417.76540
+bigint_f5_timestamp	20101112111417
+varchar_f6_timestamp	2010-11-12 11:14:17.765432
+alter table t1 change f0_timestamp time4_f0_timestamp time(4), change f1_timestamp datetime3_f1_timestamp datetime(3), change f2_timestamp date_f2_timestamp date, change f3_timestamp double_f3_timestamp double, change f4_timestamp decimal5_f4_timestamp decimal(40,5), change f5_timestamp bigint_f5_timestamp bigint, change f6_timestamp varchar_f6_timestamp varchar(255);
+Warnings:
+Level	Note
+Code	1265
+Message	Data truncated for column 'time4_f0_timestamp' at row 1
+Level	Note
+Code	1265
+Message	Data truncated for column 'date_f2_timestamp' at row 1
+select * from t1;
+time4_f0_timestamp	11:14:17.0000
+datetime3_f1_timestamp	2010-11-12 11:14:17.700
+date_f2_timestamp	2010-11-12
+double_f3_timestamp	20101112111417.8
+decimal5_f4_timestamp	20101112111417.76540
+bigint_f5_timestamp	20101112111417
+varchar_f6_timestamp	2010-11-12 11:14:17.765432
+alter table t1 modify time4_f0_timestamp timestamp(0), modify datetime3_f1_timestamp timestamp(1), modify date_f2_timestamp timestamp(2), modify double_f3_timestamp timestamp(3), modify decimal5_f4_timestamp timestamp(4), modify bigint_f5_timestamp timestamp(5), modify varchar_f6_timestamp timestamp(6);
+Warnings:
+Level	Warning
+Code	1265
+Message	Data truncated for column 'time4_f0_timestamp' at row 1
+select * from t1;
+time4_f0_timestamp	0000-00-00 00:00:00
+datetime3_f1_timestamp	2010-11-12 11:14:17.7
+date_f2_timestamp	2010-11-12 00:00:00.00
+double_f3_timestamp	2010-11-12 11:14:17.765
+decimal5_f4_timestamp	2010-11-12 11:14:17.7654
+bigint_f5_timestamp	2010-11-12 11:14:17.00000
+varchar_f6_timestamp	2010-11-12 11:14:17.765432
+delete from t1;
+insert t1 select * from t2;
+Warnings:
+Level	Warning
+Code	1265
+Message	Data truncated for column 'time4_f0_timestamp' at row 1
+select * from t1;
+time4_f0_timestamp	0000-00-00 00:00:00
+datetime3_f1_timestamp	2010-11-12 11:14:17.7
+date_f2_timestamp	2010-11-12 00:00:00.00
+double_f3_timestamp	2010-11-12 11:14:17.765
+decimal5_f4_timestamp	2010-11-12 11:14:17.7654
+bigint_f5_timestamp	2010-11-12 11:14:17.00000
+varchar_f6_timestamp	2010-11-12 11:14:17.765432
+drop table t1, t2;
 create table t1 (a timestamp(6), b timestamp(6));
 create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y);
 call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');