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.

32 files changed, 378 insertions, 107 deletions

diff --git a/include/my_sys.h b/include/my_sys.h
index ff4083b9fda..96754d2e85d 100644
--- a/include/my_sys.h
+++ b/include/my_sys.h
@@ -894,6 +894,7 @@ extern ulong crc32(ulong crc, const uchar *buf, uint len);
 extern uint my_set_max_open_files(uint files);
 void my_free_open_file_info(void);
 
+#define HRTIME_RESOLUTION               1000000
 typedef struct {ulonglong val;} my_hrtime_t;
 typedef struct {ulonglong val;} my_timediff_t;
 void my_time_init();
@@ -904,7 +905,7 @@ extern ulonglong my_getsystime(void);
 #define my_micro_time()                 (my_getsystime()/10)
 #define hrtime_to_time(X)               ((X).val/1000000)
 #define hrtime_from_time(X)             ((ulonglong)((X)*1000000ULL))
-#define hrtime_to_double(X)             ((X).val/1e6)
+#define hrtime_to_double(X)             ((X).val/(double)HRTIME_RESOLUTION)
 #define hrtime_sec_part(X)              ((ulong)((X).val%1000000))
 #define my_time(X)                      hrtime_to_time(my_hrtime())
 #define my_micro_and_hrtime(X,Y)        my_diff_and_hrtime(X,Y)
diff --git a/include/my_time.h b/include/my_time.h
index 5ec51685489..9fcff24918b 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -70,8 +70,10 @@ typedef long my_time_t;
 #define TIME_MAX_MINUTE 59
 #define TIME_MAX_SECOND 59
 #define TIME_MAX_SECOND_PART 999999
+#define TIME_SECOND_PART_FACTOR (TIME_MAX_SECOND_PART+1)
+#define TIME_SECOND_PART_DIGITS 6
 #define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + \
-                        TIME_MAX_SECOND + TIME_MAX_SECOND_PART/1e6)
+                        TIME_MAX_SECOND + TIME_MAX_SECOND_PART/(double)TIME_SECOND_PART_FACTOR)
 #define TIME_MAX_VALUE_SECONDS (TIME_MAX_HOUR * 3600L + \
                                 TIME_MAX_MINUTE * 60L + TIME_MAX_SECOND)
 
@@ -139,8 +141,6 @@ void set_zero_time(MYSQL_TIME *tm, enum enum_mysql_timestamp_type time_type);
   sent using binary protocol fit in this buffer.
 */
 #define MAX_DATE_STRING_REP_LENGTH 30
-#define MAX_SEC_PART_VALUE  999999
-#define MAX_SEC_PART_DIGITS 6
 #define AUTO_SEC_PART_DIGITS 31 /* same as NOT_FIXED_DEC */
 
 int my_time_to_str(const MYSQL_TIME *l_time, char *to, int digits);
@@ -150,16 +150,16 @@ int my_TIME_to_str(const MYSQL_TIME *l_time, char *to, int digits);
 
 static inline longlong sec_part_shift(longlong second_part, int digits)
 {
-  return second_part / log_10_int[MAX_SEC_PART_DIGITS - digits];
+  return second_part / (longlong)log_10_int[TIME_SECOND_PART_DIGITS - digits];
 }
 static inline longlong sec_part_unshift(longlong second_part, int digits)
 {
-  return second_part * log_10_int[MAX_SEC_PART_DIGITS - digits];
+  return second_part * (longlong)log_10_int[TIME_SECOND_PART_DIGITS - digits];
 }
 static inline ulong sec_part_truncate(ulong second_part, int digits)
 {
   /* the cast here should be unnecessary! */
-  return second_part - second_part % (ulong)log_10_int[MAX_SEC_PART_DIGITS - digits];
+  return second_part - second_part % (ulong)log_10_int[TIME_SECOND_PART_DIGITS - digits];
 }
 
 #define hrtime_to_my_time(X) ((my_time_t)hrtime_to_time(X))
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index cca2ef65d8a..1da3a087226 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -4420,7 +4420,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field)
     field->max_length= MAX_DOUBLE_STRING_REP_LENGTH;
     break;
   case MYSQL_TYPE_TIME:
-    field->max_length= 15;                    /* 19:23:48.123456 */
+    field->max_length= 17;                    /* -123:23:48.123456 */
     param->skip_result= skip_result_with_length;
     break;
   case MYSQL_TYPE_DATE:
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;
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index 9bae4dec120..788edc88ff0 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -695,7 +695,7 @@ fractional:
 int check_time_range(struct st_mysql_time *my_time, uint dec, int *warning) 
 {
   longlong hour;
-  static ulong max_sec_part[MAX_SEC_PART_DIGITS+1]= {000000, 900000, 990000,
+  static ulong max_sec_part[TIME_SECOND_PART_DIGITS+1]= {000000, 900000, 990000,
                                              999000, 999900, 999990, 999999};
 
   if (my_time->minute >= 60 || my_time->second >= 60)
@@ -704,7 +704,7 @@ int check_time_range(struct st_mysql_time *my_time, uint dec, int *warning)
   hour= my_time->hour + (24*my_time->day);
 
   if (dec == AUTO_SEC_PART_DIGITS)
-    dec= MAX_SEC_PART_DIGITS;
+    dec= TIME_SECOND_PART_DIGITS;
 
   if (hour <= TIME_MAX_HOUR &&
       (hour != TIME_MAX_HOUR || my_time->minute != TIME_MAX_MINUTE ||
@@ -1042,9 +1042,9 @@ int my_time_to_str(const MYSQL_TIME *l_time, char *to, int digits)
   ulong day= (l_time->year || l_time->month) ? 0 : l_time->day;
 
   if (digits == AUTO_SEC_PART_DIGITS)
-    digits= l_time->second_part ? MAX_SEC_PART_DIGITS : 0;
+    digits= l_time->second_part ? TIME_SECOND_PART_DIGITS : 0;
 
-  DBUG_ASSERT(digits >= 0 && digits <= MAX_SEC_PART_DIGITS);
+  DBUG_ASSERT(digits >= 0 && digits <= TIME_SECOND_PART_DIGITS);
 
   return sprintf(to,
                  digits ? "%s%02lu:%02u:%02u.%0*lu"
@@ -1063,9 +1063,9 @@ int my_date_to_str(const MYSQL_TIME *l_time, char *to)
 int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, int digits)
 {
   if (digits == AUTO_SEC_PART_DIGITS)
-    digits= l_time->second_part ? MAX_SEC_PART_DIGITS : 0;
+    digits= l_time->second_part ? TIME_SECOND_PART_DIGITS : 0;
 
-  DBUG_ASSERT(digits >= 0 && digits <= MAX_SEC_PART_DIGITS);
+  DBUG_ASSERT(digits >= 0 && digits <= TIME_SECOND_PART_DIGITS);
 
   return sprintf(to,
                  digits ? "%04u-%02u-%02u %02u:%02u:%02u.%0*lu"
diff --git a/sql/field.cc b/sql/field.cc
index 914376b91e0..731aaea4659 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5056,7 +5056,7 @@ static uint sec_part_bytes[MAX_DATETIME_PRECISION+1]= { 0, 1, 1, 2, 2, 3, 3 };
 static uint datetime_hires_bytes[MAX_DATETIME_PRECISION+1]=
 { 5, 6, 6, 7, 7, 7, 8 };
 static uint time_hires_bytes[MAX_DATETIME_PRECISION+1]=
-{ 3, 4, 4, 4, 5, 5, 6 };
+{ 3, 4, 4, 5, 5, 5, 6 };
 
 void Field_timestamp_hires::store_TIME(my_time_t timestamp, ulong sec_part)
 {
@@ -5520,7 +5520,14 @@ String *Field_time_hires::val_str(String *str,
 
 bool Field_time_hires::get_date(MYSQL_TIME *ltime, uint fuzzydate)
 {
-  ulonglong packed= read_bigendian(ptr, Field_time_hires::pack_length());
+  uint32 len= pack_length();
+  longlong packed= read_bigendian(ptr, len);
+
+  /* sign extension */
+  longlong mask= 1LL << (len*8 - 1);
+  if (packed & mask)
+    packed|= ~(mask-1);
+
   unpack_time(sec_part_unshift(packed, dec), ltime);
   /*
     unpack_time() returns MYSQL_TIMESTAMP_DATETIME.
diff --git a/sql/field.h b/sql/field.h
index d5d5ad32744..75cf3858e6f 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1234,7 +1234,7 @@ public:
   dec(dec_arg)
   {
     DBUG_ASSERT(dec);
-    DBUG_ASSERT(dec <= MAX_SEC_PART_DIGITS);
+    DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
   }
   void sql_type(String &str) const;
   long get_timestamp(ulong *sec_part) const;
@@ -1407,7 +1407,7 @@ public:
      dec(dec_arg)
   {
     DBUG_ASSERT(dec);
-    DBUG_ASSERT(dec <= MAX_SEC_PART_DIGITS);
+    DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
   }
   enum ha_base_keytype key_type() const { return HA_KEYTYPE_BINARY; }
   uint decimals() const { return dec; }
@@ -1476,7 +1476,7 @@ public:
                     field_name_arg, cs), dec(dec_arg)
   {
     DBUG_ASSERT(dec);
-    DBUG_ASSERT(dec <= MAX_SEC_PART_DIGITS);
+    DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
   }
   enum ha_base_keytype key_type() const { return HA_KEYTYPE_BINARY; }
   int store_decimal(const my_decimal *d);
diff --git a/sql/item.cc b/sql/item.cc
index cce985fe80d..997ce821f9a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2748,13 +2748,13 @@ void Item_param::set_time(MYSQL_TIME *tm, timestamp_type time_type,
   value.time= *tm;
   value.time.time_type= time_type;
 
-  decimals= value.time.second_part > 0 ? MAX_SEC_PART_DIGITS : 0;
+  decimals= value.time.second_part > 0 ? TIME_SECOND_PART_DIGITS : 0;
 
   if (value.time.year > 9999 || value.time.month > 12 ||
       value.time.day > 31 ||
       (time_type != MYSQL_TIMESTAMP_TIME && value.time.hour > 23) ||
       value.time.minute > 59 || value.time.second > 59 ||
-      value.time.second_part > MAX_SEC_PART_VALUE)
+      value.time.second_part > TIME_MAX_SECOND_PART)
   {
     Lazy_string_time str(&value.time);
     make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 1573dfddf98..e7418c2a53a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -629,12 +629,14 @@ int Arg_comparator::set_compare_func(Item_result_field *item, Item_result type)
   @param[in]   warn_name  Field name for issuing the warning
   @param[out]  l_time     The MYSQL_TIME objects is initialized.
 
-  Parses a date provided in the string str into a MYSQL_TIME object. If the
-  string contains an incorrect date or doesn't correspond to a date at all
-  then a warning is issued. The warn_type and the warn_name arguments are used
-  as the name and the type of the field when issuing the warning. If any input
-  was discarded (trailing or non-timestamp-y characters), return value will be
-  TRUE.
+  Parses a date provided in the string str into a MYSQL_TIME object.
+  The date is used for comparison, that is fuzzy dates are allowed
+  independently of sql_mode.
+  If the string contains an incorrect date or doesn't correspond to a date at
+  all then a warning is issued. The warn_type and the warn_name arguments are
+  used as the name and the type of the field when issuing the warning. If any
+  input was discarded (trailing or non-timestamp-y characters), return value
+  will be TRUE.
 
   @return Status flag
   @retval FALSE Success.
@@ -649,8 +651,6 @@ bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type,
   enum_mysql_timestamp_type timestamp_type;
   int flags= TIME_FUZZY_DATE | MODE_INVALID_DATES;
 
-  flags|= thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE);
-
   if (warn_type == MYSQL_TIMESTAMP_TIME)
     flags|= TIME_TIME_ONLY;
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 51da306af48..a173f50e605 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1479,9 +1479,10 @@ bool Item_func_curdate::get_date(MYSQL_TIME *res,
 
 bool Item_func_curtime::fix_fields(THD *thd, Item **items)
 {
-  if (decimals > MAX_SEC_PART_DIGITS)
+  if (decimals > TIME_SECOND_PART_DIGITS)
   {
-    my_error(ER_WRONG_ARGUMENTS, MYF(0), func_name());
+    my_error(ER_TOO_BIG_PRECISION, MYF(0), decimals, func_name(),
+             TIME_SECOND_PART_DIGITS);
     return 1;
   }
   return Item_timefunc::fix_fields(thd, items);
@@ -1492,7 +1493,7 @@ void Item_func_curtime::fix_length_and_dec()
   collation.set(&my_charset_bin);
   store_now_in_TIME(&ltime);
   max_length= MAX_TIME_WIDTH +
-    (decimals ? min(decimals, MAX_SEC_PART_DIGITS)+1 : 0);
+    (decimals ? min(decimals, TIME_SECOND_PART_DIGITS)+1 : 0);
 }
 
 bool Item_func_curtime::get_date(MYSQL_TIME *res,
@@ -1505,11 +1506,11 @@ bool Item_func_curtime::get_date(MYSQL_TIME *res,
 static void set_sec_part(ulong sec_part, MYSQL_TIME *ltime, Item *item)
 {
   DBUG_ASSERT(item->decimals == AUTO_SEC_PART_DIGITS ||
-              item->decimals <= MAX_SEC_PART_DIGITS);
+              item->decimals <= TIME_SECOND_PART_DIGITS);
   if (item->decimals)
   {
     ltime->second_part= sec_part;
-    if (item->decimals < MAX_SEC_PART_DIGITS)
+    if (item->decimals < TIME_SECOND_PART_DIGITS)
       ltime->second_part= sec_part_truncate(ltime->second_part, item->decimals);
   }
 }
@@ -1548,9 +1549,10 @@ void Item_func_curtime_utc::store_now_in_TIME(MYSQL_TIME *now_time)
 
 bool Item_func_now::fix_fields(THD *thd, Item **items)
 {
-  if (decimals > MAX_SEC_PART_DIGITS)
+  if (decimals > TIME_SECOND_PART_DIGITS)
   {
-    my_error(ER_WRONG_ARGUMENTS, MYF(0), func_name());
+    my_error(ER_TOO_BIG_PRECISION, MYF(0), decimals, func_name(),
+             TIME_SECOND_PART_DIGITS);
     return 1;
   }
   return Item_temporal_func::fix_fields(thd, items);
@@ -1561,7 +1563,7 @@ void Item_func_now::fix_length_and_dec()
   collation.set(&my_charset_bin);
   store_now_in_TIME(&ltime);
   max_length= MAX_DATETIME_WIDTH +
-                 (decimals ? min(decimals, MAX_SEC_PART_DIGITS)+1 : 0);
+                 (decimals ? min(decimals, TIME_SECOND_PART_DIGITS)+1 : 0);
 }
 
 
@@ -1848,7 +1850,7 @@ void Item_func_convert_tz::fix_length_and_dec()
   max_length= MAX_DATETIME_WIDTH;
   decimals= args[0]->decimals;
   if (decimals && decimals != NOT_FIXED_DEC)
-    max_length+= min(decimals, MAX_SEC_PART_DIGITS) + 1;
+    max_length+= min(decimals, TIME_SECOND_PART_DIGITS) + 1;
   maybe_null= 1;
 }
 
@@ -1949,7 +1951,7 @@ void Item_date_add_interval::fix_length_and_dec()
   else
     decimals= args[0]->decimals;
   if (decimals)
-    max_length+= min(decimals, MAX_SEC_PART_DIGITS) + 1;
+    max_length+= min(decimals, TIME_SECOND_PART_DIGITS) + 1;
   value.alloc(max_length);
 }
 
@@ -2402,7 +2404,7 @@ void Item_func_add_time::fix_length_and_dec()
   else if (arg0_field_type == MYSQL_TYPE_TIME)
     cached_field_type= MYSQL_TYPE_TIME;
   if (decimals)
-    max_length+= min(decimals, MAX_SEC_PART_DIGITS) + 1;
+    max_length+= min(decimals, TIME_SECOND_PART_DIGITS) + 1;
 }
 
 /**
@@ -2467,7 +2469,7 @@ bool Item_func_add_time::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 
   if (cached_field_type == MYSQL_TYPE_STRING &&
       (l_time1.second_part || l_time2.second_part))
-    dec= MAX_SEC_PART_DIGITS;
+    dec= TIME_SECOND_PART_DIGITS;
 
   if (!is_time)
   {
@@ -2931,7 +2933,7 @@ void Item_func_str_to_date::fix_length_and_dec()
 {
   maybe_null= 1;
   cached_field_type= MYSQL_TYPE_DATETIME;
-  max_length= MAX_DATETIME_WIDTH+MAX_SEC_PART_DIGITS;
+  max_length= MAX_DATETIME_WIDTH + TIME_SECOND_PART_DIGITS;
   cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
   decimals= AUTO_SEC_PART_DIGITS;
   if ((const_item= args[1]->const_item()))
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 68fd34aa29e..9f45655add6 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -332,7 +332,9 @@ public:
   void fix_length_and_dec()
   {
     maybe_null= TRUE;
-    decimals=args[0]->decimals;
+    decimals= args[0]->decimals;
+    if (decimals != NOT_FIXED_DEC)
+      set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
     max_length=17;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
@@ -389,7 +391,7 @@ public:
   void fix_length_and_dec()
   {
     max_length= MAX_TIME_WIDTH +
-                   (decimals ? min(decimals, MAX_SEC_PART_DIGITS)+1 : 0);
+                   (decimals ? min(decimals, TIME_SECOND_PART_DIGITS)+1 : 0);
   }
 };
 
@@ -597,8 +599,8 @@ public:
     collation.set(&my_charset_bin);
     maybe_null=1;
     decimals= args[0]->decimals;
-    if (decimals != NOT_FIXED_DEC && decimals > MAX_SEC_PART_DIGITS)
-      decimals= MAX_SEC_PART_DIGITS;
+    if (decimals != NOT_FIXED_DEC)
+      set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
     Item_timefunc::fix_length_and_dec();
   }
   const char *func_name() const { return "sec_to_time"; }
@@ -700,9 +702,13 @@ public:
     maybe_null= 1;
     max_length= MIN_TIME_WIDTH;
     if (decimals == NOT_FIXED_DEC)
+    {
       decimals= args[0]->decimals;
+      if (decimals != NOT_FIXED_DEC)
+        set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
+    }
     if (decimals && decimals != NOT_FIXED_DEC)
-      max_length+= min(decimals, MAX_SEC_PART_DIGITS) + 1;
+      max_length+= min(decimals, TIME_SECOND_PART_DIGITS) + 1;
   }
 };
 
@@ -722,7 +728,7 @@ public:
     maybe_null= 1;
     max_length= MAX_DATETIME_WIDTH;
     if (decimals && decimals != NOT_FIXED_DEC)
-      max_length+= min(decimals, MAX_SEC_PART_DIGITS) + 1;
+      max_length+= min(decimals, TIME_SECOND_PART_DIGITS) + 1;
   }
 };
 
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 273e3b1fd28..9242b348f0b 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -1200,7 +1200,7 @@ bool Protocol_binary::store(MYSQL_TIME *tm, int decimals)
   pos[5]= (uchar) tm->minute;
   pos[6]= (uchar) tm->second;
   DBUG_ASSERT(decimals == AUTO_SEC_PART_DIGITS ||
-              (decimals >= 0 && decimals <= MAX_SEC_PART_DIGITS));
+              (decimals >= 0 && decimals <= TIME_SECOND_PART_DIGITS));
   if (decimals != AUTO_SEC_PART_DIGITS)
     tm->second_part= sec_part_truncate(tm->second_part, decimals);
   int4store(pos+7, tm->second_part);
@@ -1242,7 +1242,7 @@ bool Protocol_binary::store_time(MYSQL_TIME *tm, int decimals)
   pos[6]= (uchar) tm->minute;
   pos[7]= (uchar) tm->second;
   DBUG_ASSERT(decimals == AUTO_SEC_PART_DIGITS ||
-              (decimals >= 0 && decimals <= MAX_SEC_PART_DIGITS));
+              (decimals >= 0 && decimals <= TIME_SECOND_PART_DIGITS));
   if (decimals != AUTO_SEC_PART_DIGITS)
     tm->second_part= sec_part_truncate(tm->second_part, decimals);
   int4store(pos+8, tm->second_part);
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index c2fa3d12829..a9880913c5e 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5513,11 +5513,11 @@ ER_SP_NO_RECURSION
         eng "Recursive stored functions and triggers are not allowed."
         ger "Rekursive gespeicherte Routinen und Triggers sind nicht erlaubt"
 ER_TOO_BIG_SCALE 42000 S1009
-        eng "Too big scale %u specified for column '%-.192s'. Maximum is %lu."
-        ger "Zu großer Skalierungsfaktor %u für Feld '%-.192s' angegeben. Maximum ist %lu"
+        eng "Too big scale %u specified for '%-.192s'. Maximum is %lu."
+        ger "Zu großer Skalierungsfaktor %u für '%-.192s' angegeben. Maximum ist %lu"
 ER_TOO_BIG_PRECISION 42000 S1009
-        eng "Too big precision %u specified for column '%-.192s'. Maximum is %lu."
-        ger "Zu große Genauigkeit %u für Feld '%-.192s' angegeben. Maximum ist %lu"
+        eng "Too big precision %u specified for '%-.192s'. Maximum is %lu."
+        ger "Zu große Genauigkeit %u für '%-.192s' angegeben. Maximum ist %lu"
 ER_M_BIGGER_THAN_D 42000 S1009
         eng "For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '%-.192s')."
         ger "Für FLOAT(M,D), DOUBLE(M,D) oder DECIMAL(M,D) muss M >= D sein (Feld '%-.192s')"