post-review changes 1

include/my_time.h:
  remove duplicate defines.
  cast to ulonglong to avoid overflow
sql/field.cc:
  perform sign extension when reading packed TIME values
sql/item_cmpfunc.cc:
  when converting a string to a date for the purpose of comparing it with another date,
  we should ignore strict sql mode.
sql/item_timefunc.cc:
  better error message
sql/item_timefunc.h:
  limit decimals appropriately
sql/share/errmsg.txt:
  don't refer to an object as a "column" in error messages that are used not only for columns.

20 files changed, 308 insertions, 53 deletions

diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc
index 594b8b79ca2..05281814827 100644
--- a/mysql-test/include/type_hrtime.inc
+++ b/mysql-test/include/type_hrtime.inc
@@ -89,6 +89,7 @@ select * from v1;
 show columns from v1;
 create table t2 select * from v1;
 show create table t2;
+select * from t2;
 
 drop view v1;
 drop table t1, t2;
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index f7a388237e8..ca79f53ff5b 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -205,7 +205,7 @@ f8	date	YES		NULL
 f9	time(6)	YES		NULL	
 select * from t1;
 f1	f2	f3	f4	f5	f6	f7	f8	f9
-1997-01-01	1998-01-02 01:01:00.000003	49:01:01.000001	46:58:57.999999	8275:29:36.710655	10:11:12	2001-12-01 01:01:01.000000	1997-12-31	23:59:59.000001
+1997-01-01	1998-01-02 01:01:00.000003	49:01:01.000001	46:58:57.999999	-24:00:00.000001	10:11:12	2001-12-01 01:01:01.000000	1997-12-31	23:59:59.000001
 create table test(t1 datetime, t2 time, t3 time, t4 datetime);
 insert into test values 
 ('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 9d494619068..6a8805dfb3a 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -16,6 +16,11 @@ select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
 sec_to_time(time_to_sec("0:30:47")/6.21);
 sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
 02:30:01	23001	54742	00:04:57.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
@@ -1487,7 +1492,7 @@ select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as
 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');
+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
diff --git a/mysql-test/r/func_time_hires.result b/mysql-test/r/func_time_hires.result
index 4a3dc9b5d5c..a3f915bba27 100644
--- a/mysql-test/r/func_time_hires.result
+++ b/mysql-test/r/func_time_hires.result
@@ -23,9 +23,9 @@ select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222));
 time_to_sec(sec_to_time(11111))	11111
 time_to_sec(sec_to_time(11111.22222))	11111.22222
 select current_timestamp(7);
-ERROR HY000: Incorrect arguments to now
+ERROR 42000: Too big precision 7 specified for 'now'. Maximum is 6.
 select curtime(7);
-ERROR HY000: Incorrect arguments to curtime
+ERROR 42000: Too big precision 7 specified for 'curtime'. Maximum is 6.
 drop table if exists t1;
 create table t1 select sec_to_time(12345), sec_to_time(12345.6789),
 sec_to_time(1234567e-2), now(), curtime(0),
@@ -130,7 +130,7 @@ t5	12:13:14.12345
 t6	12:13:14.123456
 drop table t1;
 select CAST(@a AS DATETIME(7));
-ERROR 42000: Too big precision 7 specified for column '(@a)'. Maximum is 6.
+ERROR 42000: Too big precision 7 specified for '(@a)'. Maximum is 6.
 SELECT CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00');
 CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00')
 2011-01-02 15:00:00
@@ -154,24 +154,24 @@ insert into t1 values ('2002-07-15 21:00:00');
 select time(f1) from t1;
 time(f1)
 21:00:00.000000
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 time(f1)
 21:00:00.000000
-21:00:00.000000
+21:00:01.000000
 alter table t1 modify f1 timestamp;
 select time(f1) from t1;
 time(f1)
 21:00:00
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 time(f1)
 21:00:00
-21:00:00
+21:00:01
 alter table t1 modify f1 varchar(100);
 select time(f1) from t1;
 time(f1)
 21:00:00
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 time(f1)
 21:00:00.000000
-21:00:00.000000
+21:00:01.000000
 drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index efb549a1494..69733162818 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4105,6 +4105,11 @@ str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6'
 1
 Warnings:
 Warning	1292	Truncated incorrect date value: '2007/10/20 00:00:00 GMT-6'
+select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
+str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
+0
+Warnings:
+Warning	1292	Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
 select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
 str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
 1
@@ -4168,14 +4173,10 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
 set SQL_MODE=TRADITIONAL;
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
-0
-Warnings:
-Warning	1292	Truncated incorrect datetime value: '2007-10-00 12:34'
+1
 select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
 0
-Warnings:
-Warning	1292	Truncated incorrect datetime value: '2007-10-00 12:34'
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
 0
@@ -4219,14 +4220,23 @@ str_to_date('','%Y-%m-%d') = ''
 1
 Warnings:
 Warning	1292	Truncated incorrect date value: ''
-select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
-str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
+select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01';
+str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01'
+1
+select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL;
+str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL
 NULL
-select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
-str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01';
+str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01'
 NULL
-select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
-str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
+select str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL;
+str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL
+0
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01';
+str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01'
+0
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL;
+str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL
 NULL
 CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
 CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 5dcbf71ba88..44166d15015 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -890,9 +890,9 @@ DROP TABLE b15776;
 CREATE TABLE b15776 (a year(-2));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2))' at line 1
 CREATE TABLE b15776 (a timestamp(4294967294));
-ERROR 42000: Too big precision 4294967294 specified for column 'a'. Maximum is 6.
+ERROR 42000: Too big precision 4294967294 specified for 'a'. Maximum is 6.
 CREATE TABLE b15776 (a timestamp(4294967295));
-ERROR 42000: Too big precision 4294967295 specified for column 'a'. Maximum is 6.
+ERROR 42000: Too big precision 4294967295 specified for 'a'. Maximum is 6.
 CREATE TABLE b15776 (a timestamp(4294967296));
 ERROR 42000: Display width out of range for column 'a' (max = 4294967295)
 CREATE TABLE b15776 (a timestamp(-1));
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 846225be141..1e6f2de86db 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -208,14 +208,10 @@ SELECT * FROM t1 WHERE a = '0000-00-00';
 a
 0000-00-00
 0000-00-00
-Warnings:
-Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 0
 SELECT * FROM t2 WHERE a = '0000-00-00';
 a
 0000-00-00
 0000-00-00
-Warnings:
-Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 0
 INSERT INTO t1 VALUES ('0000-00-00');
 ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
 SET SQL_MODE=DEFAULT;
@@ -239,12 +235,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	i	i	4	const	1	Using where; Using index
 SELECT * FROM t1 WHERE a = '1000-00-00';
 a
-Warnings:
-Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 0
+1000-00-00
+1000-00-00
 SELECT * FROM t2 WHERE a = '1000-00-00';
 a
-Warnings:
-Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 0
+1000-00-00
+1000-00-00
 INSERT INTO t1 VALUES ('1000-00-00');
 ERROR 22007: Incorrect date value: '1000-00-00' for column 'a' at row 1
 SET SQL_MODE=DEFAULT;
diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result
index 095519cda94..3351eb54631 100644
--- a/mysql-test/r/type_datetime_hires.result
+++ b/mysql-test/r/type_datetime_hires.result
@@ -1,6 +1,6 @@
 drop table if exists t1, t2, t3;
 create table t1 (a datetime(7));
-ERROR 42000: Too big precision 7 specified for column 'a'. Maximum is 6.
+ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
 create table t1 (a datetime(3), key(a));
 insert t1 values ('2010-12-11 00:20:03.1234');
 insert t1 values ('2010-12-11 15:47:11.1234');
@@ -147,6 +147,10 @@ t2	CREATE TABLE `t2` (
   `a` datetime(6) DEFAULT NULL,
   `b` datetime(6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+a	b
+2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
+2011-01-02 03:04:06.234500	2011-01-02 03:04:06.234561
 drop view v1;
 drop table t1, t2;
 CREATE TABLE t1 (
@@ -214,3 +218,129 @@ 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;
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 76c5ea8f756..a0252676d65 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -721,7 +721,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (d decimal(66,0));
-ERROR 42000: Too big precision 66 specified for column 'd'. Maximum is 65.
+ERROR 42000: Too big precision 66 specified for 'd'. Maximum is 65.
 CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
 INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
 (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index a3a13bb0435..386bb4569d8 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -133,7 +133,7 @@ min(a)
 -0.010
 drop table t1;
 create table t1 (a float(200,100), b double(200,100));
-ERROR 42000: Too big scale 100 specified for column 'a'. Maximum is 30.
+ERROR 42000: Too big scale 100 specified for 'a'. Maximum is 30.
 create table t1 (c20 char);
 insert into t1 values (5000.0);
 Warnings:
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 70ee3a56cf3..5faa3444ce8 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -923,11 +923,11 @@ ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column
 select cast(ln(14000) as decimal(2,3)) c1;
 ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
 create table t1 (sl decimal(70,30));
-ERROR 42000: Too big precision 70 specified for column 'sl'. Maximum is 65.
+ERROR 42000: Too big precision 70 specified for 'sl'. Maximum is 65.
 create table t1 (sl decimal(32,31));
-ERROR 42000: Too big scale 31 specified for column 'sl'. Maximum is 30.
+ERROR 42000: Too big scale 31 specified for 'sl'. Maximum is 30.
 create table t1 (sl decimal(0,38));
-ERROR 42000: Too big scale 38 specified for column 'sl'. Maximum is 30.
+ERROR 42000: Too big scale 38 specified for 'sl'. Maximum is 30.
 create table t1 (sl decimal(0,30));
 ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'sl').
 create table t1 (sl decimal(5, 5));
@@ -1485,12 +1485,12 @@ SELECT CAST(1 AS decimal(65,10));
 CAST(1 AS decimal(65,10))
 1.0000000000
 SELECT CAST(1 AS decimal(66,10));
-ERROR 42000: Too big precision 66 specified for column '1'. Maximum is 65.
+ERROR 42000: Too big precision 66 specified for '1'. Maximum is 65.
 SELECT CAST(1 AS decimal(65,30));
 CAST(1 AS decimal(65,30))
 1.000000000000000000000000000000
 SELECT CAST(1 AS decimal(65,31));
-ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
+ERROR 42000: Too big scale 31 specified for '1'. Maximum is 30.
 CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
 INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
 SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
@@ -1499,7 +1499,7 @@ aa	SUM(b)
 3.000000000000000000000000000000	10
 4.000000000000000000000000000000	30
 SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
-ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
+ERROR 42000: Too big scale 31 specified for '1'. Maximum is 30.
 DROP TABLE t1;
 CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
 INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 797ee706fb9..86153cbb05d 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -167,3 +167,9 @@ f1	f2
 00:20:01	a
 00:20:03	b
 drop table t1;
+create table t1(f1 time);
+insert into t1 values ('23:38:57');
+select f1, f1 = '2010-10-11 23:38:57' from t1;
+f1	f1 = '2010-10-11 23:38:57'
+23:38:57	0
+drop table t1;
diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result
index 10ef9d3f3f3..eb21d1773ff 100644
--- a/mysql-test/r/type_time_hires.result
+++ b/mysql-test/r/type_time_hires.result
@@ -1,6 +1,6 @@
 drop table if exists t1, t2, t3;
 create table t1 (a time(7));
-ERROR 42000: Too big precision 7 specified for column 'a'. Maximum is 6.
+ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
 create table t1 (a time(3), key(a));
 insert t1 values ('2010-12-11 00:20:03.1234');
 Warnings:
@@ -163,5 +163,24 @@ t2	CREATE TABLE `t2` (
   `a` time(6) DEFAULT NULL,
   `b` time(6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+a	b
+03:04:06.234500	03:04:06.234561
+04:05:06.000000	04:05:06.789100
 drop view v1;
 drop table t1, t2;
+create table t1 (a time(4) not null);
+insert into t1 values ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
+select * from t1;
+a
+-00:00:00.6000
+-00:00:00.7000
+-00:00:00.8000
+-00:00:00.9000
+-00:00:01.0000
+-00:00:01.1000
+-00:00:01.0000
+-00:00:01.1000
+-00:00:01.0000
+-00:00:01.0900
+drop table t1;
diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result
index ade5354304b..75b6b60e4d5 100644
--- a/mysql-test/r/type_timestamp_hires.result
+++ b/mysql-test/r/type_timestamp_hires.result
@@ -1,6 +1,6 @@
 drop table if exists t1, t2, t3;
 create table t1 (a timestamp(7));
-ERROR 42000: Too big precision 7 specified for column 'a'. Maximum is 6.
+ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
 create table t1 (a timestamp(3), key(a));
 insert t1 values ('2010-12-11 00:20:03.1234');
 insert t1 values ('2010-12-11 15:47:11.1234');
@@ -147,6 +147,10 @@ t2	CREATE TABLE `t2` (
   `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
   `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+a	b
+2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
+2011-01-02 03:04:06.234500	2011-01-02 03:04:06.234561
 drop view v1;
 drop table t1, t2;
 set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456, time_zone='+03:00';
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index a320627d0bb..15c26b56634 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -15,6 +15,7 @@ select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_times
 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;
 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);
+select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899);
 select sec_to_time(time_to_sec('-838:59:59'));
 select now()-curdate()*1000000-curtime();
 select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
@@ -926,7 +927,7 @@ select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as
 # lp:736370 Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micr
 #
 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');
+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);
 drop table t1;
 
diff --git a/mysql-test/t/func_time_hires.test b/mysql-test/t/func_time_hires.test
index 8183f3435e2..940a081d155 100644
--- a/mysql-test/t/func_time_hires.test
+++ b/mysql-test/t/func_time_hires.test
@@ -9,9 +9,9 @@ select now(), curtime(0), utc_timestamp(1), utc_time(2), current_time(3),
 select sec_to_time(time_to_sec('1:2:3')), sec_to_time(time_to_sec('2:3:4.567890'));
 select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222));
 --horizontal_results
---error ER_WRONG_ARGUMENTS
+--error ER_TOO_BIG_PRECISION
 select current_timestamp(7);
---error ER_WRONG_ARGUMENTS
+--error ER_TOO_BIG_PRECISION
 select curtime(7);
 
 --disable_warnings
@@ -85,12 +85,12 @@ drop table t1;
 create table t1 (f1 timestamp(6));
 insert into t1 values ('2002-07-15 21:00:00');
 select time(f1) from t1;
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 alter table t1 modify f1 timestamp;
 select time(f1) from t1;
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 # but the effect cannot be eliminated completely:
 alter table t1 modify f1 varchar(100);
 select time(f1) from t1;
-select time(f1) from t1 union all select time(f1) from t1;
+select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 drop table t1;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index e655952288f..ebdc9610191 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3514,6 +3514,7 @@ select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
                                                 and '2007/10/20 00:00:00 GMT';
 select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
 select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6';
+select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
 
 # We have all we need -- and trailing garbage:
 # (leaving out a leading zero in first example to prove it's a
@@ -3562,10 +3563,12 @@ select str_to_date('1','%Y-%m-%d') = '1';
 select str_to_date('1','%Y-%m-%d') = '1';
 select str_to_date('','%Y-%m-%d') = '';
 
-# these three should work!
-select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
-select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
-select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
+select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01';
+select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL;
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01';
+select str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL;
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01';
+select str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL;
 
 #
 # Bug #30666: Incorrect order when using range conditions on 2 tables or more
diff --git a/mysql-test/t/type_datetime_hires.test b/mysql-test/t/type_datetime_hires.test
index 7473e01fc57..dfa0463b2f4 100644
--- a/mysql-test/t/type_datetime_hires.test
+++ b/mysql-test/t/type_datetime_hires.test
@@ -61,3 +61,71 @@ select table_name,partition_name,partition_method,partition_expression,partition
 
 drop table t1, t2;
 
+#
+# insert ... select with conversion
+#
+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');
+--query_vertical select * from t1;
+insert t2 select * from t1;
+--query_vertical select * from t2;
+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;
+--query_vertical select * from t1;
+
+drop table t1, t2;
+
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 864c2c5832d..a456f2b9eea 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -115,3 +115,11 @@ select * from t1 force key (f1) where f1 < curdate();
 select * from t1 ignore key (f1) where f1 < curdate();
 drop table t1;
 
+#
+# comparison of time and datetime:
+#
+create table t1(f1 time);
+insert into t1 values ('23:38:57');
+select f1, f1 = '2010-10-11 23:38:57' from t1;
+drop table t1;
+
diff --git a/mysql-test/t/type_time_hires.test b/mysql-test/t/type_time_hires.test
index c042a7e9bda..c9939289e43 100644
--- a/mysql-test/t/type_time_hires.test
+++ b/mysql-test/t/type_time_hires.test
@@ -2,3 +2,7 @@
 let type=time;
 --source include/type_hrtime.inc
 
+create table t1 (a time(4) not null);
+insert into t1 values ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
+select * from t1;
+drop table t1;