merge with 5.1-micro

1 files changed, 99 insertions, 126 deletions

diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result
index b0ea2e871fb..d80d4365ee8 100644
--- a/mysql-test/r/type_datetime_hires.result
+++ b/mysql-test/r/type_datetime_hires.result
@@ -115,7 +115,106 @@ Table	Create Table
 t3	CREATE TABLE `t3` (
   `a` datetime(4) DEFAULT NULL
 ) 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` datetime(4) DEFAULT NULL,
+  `a+0` decimal(25,4) DEFAULT NULL,
+  `a-1` decimal(25,4) DEFAULT NULL,
+  `a*1` decimal(25,4) DEFAULT NULL,
+  `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)` datetime(4) DEFAULT NULL,
+  `min(a)` datetime(4) 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_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(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_datetime	2010-11-12 11:14:17
+f1_datetime	2010-11-12 11:14:17.7
+f2_datetime	2010-11-12 11:14:17.76
+f3_datetime	2010-11-12 11:14:17.765
+f4_datetime	2010-11-12 11:14:17.7654
+f5_datetime	2010-11-12 11:14:17.76543
+f6_datetime	2010-11-12 11:14:17.765432
+select cast(f0_datetime as time(4)) time4_f0_datetime, cast(f1_datetime as datetime(3)) datetime3_f1_datetime, cast(f2_datetime as date) date_f2_datetime, cast(f4_datetime as double) double_f3_datetime, cast(f4_datetime as decimal(40,5)) decimal5_f4_datetime, cast(f5_datetime as signed) bigint_f5_datetime, cast(f6_datetime as char(255)) varchar_f6_datetime from t1;
+time4_f0_datetime	11:14:17.0000
+datetime3_f1_datetime	2010-11-12 11:14:17.700
+date_f2_datetime	2010-11-12
+double_f3_datetime	20101112111417.8
+decimal5_f4_datetime	20101112111417.76540
+bigint_f5_datetime	20101112111417
+varchar_f6_datetime	2010-11-12 11:14:17.765432
+create table t2 (time4_f0_datetime time(4), datetime3_f1_datetime datetime(3), date_f2_datetime date, double_f3_datetime double, decimal5_f4_datetime decimal(40,5), bigint_f5_datetime bigint, varchar_f6_datetime varchar(255));
+insert t2 select * from t1;
+Warnings:
+Level	Note
+Code	1265
+Message	Data truncated for column 'time4_f0_datetime' at row 1
+Level	Note
+Code	1265
+Message	Data truncated for column 'date_f2_datetime' at row 1
+select * from t2;
+time4_f0_datetime	11:14:17.0000
+datetime3_f1_datetime	2010-11-12 11:14:17.700
+date_f2_datetime	2010-11-12
+double_f3_datetime	20101112111417.8
+decimal5_f4_datetime	20101112111417.76540
+bigint_f5_datetime	20101112111417
+varchar_f6_datetime	2010-11-12 11:14:17.765432
+alter table t1 change f0_datetime time4_f0_datetime time(4), change f1_datetime datetime3_f1_datetime datetime(3), change f2_datetime date_f2_datetime date, change f3_datetime double_f3_datetime double, change f4_datetime decimal5_f4_datetime decimal(40,5), change f5_datetime bigint_f5_datetime bigint, change f6_datetime varchar_f6_datetime varchar(255);
+Warnings:
+Level	Note
+Code	1265
+Message	Data truncated for column 'time4_f0_datetime' at row 1
+Level	Note
+Code	1265
+Message	Data truncated for column 'date_f2_datetime' at row 1
+select * from t1;
+time4_f0_datetime	11:14:17.0000
+datetime3_f1_datetime	2010-11-12 11:14:17.700
+date_f2_datetime	2010-11-12
+double_f3_datetime	20101112111417.8
+decimal5_f4_datetime	20101112111417.76540
+bigint_f5_datetime	20101112111417
+varchar_f6_datetime	2010-11-12 11:14:17.765432
+alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6);
+select * from t1;
+time4_f0_datetime	0000-00-00 11:14:17
+datetime3_f1_datetime	2010-11-12 11:14:17.7
+date_f2_datetime	2010-11-12 00:00:00.00
+double_f3_datetime	2010-11-12 11:14:17.800
+decimal5_f4_datetime	2010-11-12 11:14:17.7654
+bigint_f5_datetime	2010-11-12 11:14:17.00000
+varchar_f6_datetime	2010-11-12 11:14:17.765432
+delete from t1;
+insert t1 select * from t2;
+select * from t1;
+time4_f0_datetime	0000-00-00 11:14:17
+datetime3_f1_datetime	2010-11-12 11:14:17.7
+date_f2_datetime	2010-11-12 00:00:00.00
+double_f3_datetime	2010-11-12 11:14:17.765
+decimal5_f4_datetime	2010-11-12 11:14:17.7654
+bigint_f5_datetime	2010-11-12 11:14:17.00000
+varchar_f6_datetime	2010-11-12 11:14:17.765432
+drop table t1, t2;
 create table t1 (a datetime(6), b datetime(6));
 create procedure foo(x datetime, y datetime(4)) insert into t1 values (x, y);
 call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
@@ -228,132 +327,6 @@ t2	p01	RANGE	extract(microsecond from taken)	123000	3
 t2	p02	RANGE	extract(microsecond from taken)	500000	4
 t2	p03	RANGE	extract(microsecond from taken)	MAXVALUE	3
 drop table t1, t2;
-create table t1 (
-q_date date,
-q_time time,
-q_time5 time(5),
-q_datetime datetime,
-q_datetime1 datetime(1),
-q_datetime3 datetime(3),
-q_datetime5 datetime(5),
-q_timestamp timestamp,
-q_timestamp2 timestamp(2),
-q_timestamp4 timestamp(4),
-q_timestamp6 timestamp(6),
-q_varchar50 varchar(50),
-q_varchar60 varchar(60),
-q_varchar70 varchar(70),
-q_varchar80 varchar(80));
-create table t2 (
-date_datetime datetime,
-time_datetime datetime,
-time5_varchar100 varchar(100),
-datetime_time time,
-datetime1_date  date,
-datetime3_timestamp timestamp,
-datetime5_varchar100 varchar(100),
-timestamp_datetime datetime,
-timestamp2_date date,
-timestamp4_time time,
-timestamp6_varchar100 varchar(100),
-varchar50_date date,
-varchar60_datetime datetime,
-varchar70_time time,
-varchar80_timestamp timestamp);
-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',
-'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', '2010-11-12 11:14:17.765432');
-Warnings:
-Note	1265	Data truncated for column 'q_date' at row 1
-Note	1265	Data truncated for column 'q_time' at row 1
-Note	1265	Data truncated for column 'q_time5' at row 1
-select * from t1;;
-q_date	2010-11-12
-q_time	11:14:17
-q_time5	11:14:17.76543
-q_datetime	2010-11-12 11:14:17
-q_datetime1	2010-11-12 11:14:17.7
-q_datetime3	2010-11-12 11:14:17.765
-q_datetime5	2010-11-12 11:14:17.76543
-q_timestamp	2010-11-12 11:14:17
-q_timestamp2	2010-11-12 11:14:17.76
-q_timestamp4	2010-11-12 11:14:17.7654
-q_timestamp6	2010-11-12 11:14:17.765432
-q_varchar50	2010-11-12 11:14:17.765432
-q_varchar60	2010-11-12 11:14:17.765432
-q_varchar70	2010-11-12 11:14:17.765432
-q_varchar80	2010-11-12 11:14:17.765432
-insert t2 select * from t1;
-Warnings:
-Warning	1265	Data truncated for column 'time_datetime' at row 1
-Note	1265	Data truncated for column 'datetime_time' at row 1
-Note	1265	Data truncated for column 'datetime1_date' at row 1
-Note	1265	Data truncated for column 'timestamp2_date' at row 1
-Note	1265	Data truncated for column 'timestamp4_time' at row 1
-Note	1265	Data truncated for column 'varchar50_date' at row 1
-Note	1265	Data truncated for column 'varchar70_time' at row 1
-select * from t2;;
-date_datetime	2010-11-12 00:00:00
-time_datetime	0000-00-00 00:00:00
-time5_varchar100	11:14:17.76543
-datetime_time	11:14:17
-datetime1_date	2010-11-12
-datetime3_timestamp	2010-11-12 11:14:17
-datetime5_varchar100	2010-11-12 11:14:17.76543
-timestamp_datetime	2010-11-12 11:14:17
-timestamp2_date	2010-11-12
-timestamp4_time	11:14:17
-timestamp6_varchar100	2010-11-12 11:14:17.765432
-varchar50_date	2010-11-12
-varchar60_datetime	2010-11-12 11:14:17
-varchar70_time	11:14:17
-varchar80_timestamp	2010-11-12 11:14:17
-alter table t1
-change q_date date_datetime datetime,
-change q_time time_datetime datetime,
-change q_time5 time5_varchar100 varchar(100),
-change q_datetime datetime_time time,
-change q_datetime1 datetime1_date  date,
-change q_datetime3 datetime3_timestamp timestamp,
-change q_datetime5 datetime5_varchar100 varchar(100),
-change q_timestamp timestamp_datetime datetime,
-change q_timestamp2 timestamp2_date date,
-change q_timestamp4 timestamp4_time time,
-change q_timestamp6 timestamp6_varchar100 varchar(100),
-change q_varchar50 varchar50_date date,
-change q_varchar60 varchar60_datetime datetime,
-change q_varchar70 varchar70_time time,
-change q_varchar80 varchar80_timestamp timestamp;
-Warnings:
-Warning	1265	Data truncated for column 'time_datetime' at row 1
-Note	1265	Data truncated for column 'datetime_time' at row 1
-Note	1265	Data truncated for column 'datetime1_date' at row 1
-Note	1265	Data truncated for column 'timestamp2_date' at row 1
-Note	1265	Data truncated for column 'timestamp4_time' at row 1
-Note	1265	Data truncated for column 'varchar50_date' at row 1
-Note	1265	Data truncated for column 'varchar70_time' at row 1
-select * from t1;;
-date_datetime	2010-11-12 00:00:00
-time_datetime	0000-00-00 00:00:00
-time5_varchar100	11:14:17.76543
-datetime_time	11:14:17
-datetime1_date	2010-11-12
-datetime3_timestamp	2010-11-12 11:14:17
-datetime5_varchar100	2010-11-12 11:14:17.76543
-timestamp_datetime	2010-11-12 11:14:17
-timestamp2_date	2010-11-12
-timestamp4_time	11:14:17
-timestamp6_varchar100	2010-11-12 11:14:17.765432
-varchar50_date	2010-11-12
-varchar60_datetime	2010-11-12 11:14:17
-varchar70_time	11:14:17
-varchar80_timestamp	2010-11-12 11:14:17
-drop table t1, t2;
 create table t1 (a datetime, b datetime(6));
 insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912');
 update t1 set b=a;