From: Alexander Barkov
Date: February 23 2012 4:33pm
Subject: bzr push into mysql-trunk branch (alexander.barkov:3951 to 3952) Bug#13596893
List-Archive: http://lists.mysql.com/commits/143048
X-Bug: 13596893
Message-Id: <201202231636.q1NGanko030745@acsmt358.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3952 Alexander Barkov	2012-02-23
           Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
            
            Problem:
            
            Prior to WL#946 expressions like "timestamp_column1-timestamp_column2"
            had REAL type. WL#946 changed behaviour  of temporal data types in numeric
            context started to work as DECIMAL numbers, or as INT number in case of
            no fractional digits. This change in combination with the fact that
            TIMESTAMP fields had UNSIGNED_FLAG made the expression
            "timestamp_column1-timestamp_column2" create a "BINGINT UNSIGNED"
            result and therefore fail with the error 
            "BIGINT UNSIGNED value is out of range".
            
            Fix:
            
            - Removing UNSIGNED_FLAG from Field_timestampf.
            - For consistency, removing "unsigned_flag" from
              all DATETIME and DATE items. Note, we want to have
              negative years in the future anyway.
            - Additionally, adjusting Item::decimal_precision() to
              return more precise result, so smaller numeric types
              are created when temporal types appear in numeric context.
            - Additionally, removing ZEROFILL_FLAG from Field_timestampf,
              as it's a remainder from MySQL-4.0 times.
            
            
            Per file comments:
            
            
              @ sql/field.cc
                Removing ZEROFILL_FLAG and UNSIGNED_FLAG from Field_timestampf.
                Note, not removing these flags from the old Field_timestamp,
                for better compatibility purposes. InnoDB might need these
                flags in the old tables.
                Also, removing redundant setting of BINARY_FLAG.
                It's set by Field_temporal::Field_temporal().
            
              @ sql/item.cc
                Adding better decimal precision calculation for the  temporal data types,
                to create smaller data types in numeric context.
            
              @ sql/item_func.cc
              @ sql/item_timefunc.cc
              @ sql/item_timefunc.h
                Removing unsigned flag from date and datetime items.
            
              @ sql/sql_const.h
                Introducing new constants representing number of integer 
                digits in the temporal data types.
            
              @ mysql-test/r/ctype_binary.result
              @ mysql-test/r/ctype_cp1251.result
              @ mysql-test/r/ctype_latin1.result
              @ mysql-test/r/ctype_ucs.result
              @ mysql-test/r/ctype_utf8.result
              @ mysql-test/r/func_time.result
              @ mysql-test/r/metadata.result
              @ mysql-test/r/ps_2myisam.result
              @ mysql-test/r/ps_3innodb.result
              @ mysql-test/r/ps_4heap.result
              @ mysql-test/r/ps_5merge.result
              @ mysql-test/r/type_temporal_fractional.result
                Recording test results

    modified:
      mysql-test/r/ctype_binary.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_ucs.result
      mysql-test/r/ctype_utf8.result
      mysql-test/r/func_time.result
      mysql-test/r/metadata.result
      mysql-test/r/ps_2myisam.result
      mysql-test/r/ps_3innodb.result
      mysql-test/r/ps_4heap.result
      mysql-test/r/ps_5merge.result
      mysql-test/r/type_temporal_fractional.result
      mysql-test/r/type_timestamp.result
      mysql-test/t/type_timestamp.test
      sql/field.cc
      sql/item.cc
      sql/item_func.cc
      sql/item_timefunc.cc
      sql/item_timefunc.h
      sql/sql_const.h
 3951 Tor Didriksen	2012-02-23
      WL#6158 post-push fix: correct size specifier for shift constant.

    modified:
      unittest/gunit/join_tab_sort-t.cc
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_binary.result	2012-02-23 16:32:32 +0000
@@ -2818,8 +2818,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	128	31	63
 def					field1_str2	254	29	19	Y	128	31	63
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_cp1251.result	2012-02-23 16:32:32 +0000
@@ -3210,8 +3210,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	51
 def					field1_str2	254	29	19	Y	0	31	51
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_latin1.result	2012-02-23 16:32:32 +0000
@@ -3237,8 +3237,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	8
 def					field1_str2	254	29	19	Y	0	31	8
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_ucs.result	2012-02-23 16:32:32 +0000
@@ -4137,8 +4137,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	8
 def					field1_str2	254	29	19	Y	0	31	8
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result	2012-01-23 10:02:54 +0000
+++ b/mysql-test/r/ctype_utf8.result	2012-02-23 16:32:32 +0000
@@ -5064,8 +5064,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	87	10	Y	0	31	33
 def					field1_str2	254	87	19	Y	0	31	33
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result	2012-01-24 11:57:25 +0000
+++ b/mysql-test/r/func_time.result	2012-02-23 16:32:32 +0000
@@ -929,10 +929,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `now() - now()` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `curtime() - curtime()` int(10) NOT NULL DEFAULT '0',
-  `sec_to_time(1) + 0` bigint(12) DEFAULT NULL,
-  `from_unixtime(1) + 0` bigint(20) unsigned DEFAULT NULL
+  `now() - now()` bigint(16) NOT NULL DEFAULT '0',
+  `curtime() - curtime()` int(9) NOT NULL DEFAULT '0',
+  `sec_to_time(1) + 0` int(9) DEFAULT NULL,
+  `from_unixtime(1) + 0` bigint(16) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 SELECT SEC_TO_TIME(3300000);

=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2011-11-21 11:58:25 +0000
+++ b/mysql-test/r/metadata.result	2012-02-23 16:32:32 +0000
@@ -21,7 +21,7 @@ def	test	t1	t1	g	g	5	4	0	Y	32768	3	63
 def	test	t1	t1	h	h	246	7	0	Y	32768	4	63
 def	test	t1	t1	i	i	13	4	0	Y	32864	0	63
 def	test	t1	t1	j	j	10	10	0	Y	128	0	63
-def	test	t1	t1	k	k	7	19	0	N	9441	0	63
+def	test	t1	t1	k	k	7	19	0	N	9345	0	63
 def	test	t1	t1	l	l	12	19	0	Y	128	0	63
 def	test	t1	t1	m	m	254	1	0	Y	256	0	8
 def	test	t1	t1	n	n	254	3	0	Y	2048	0	8
@@ -284,7 +284,7 @@ def	test	t1	t1	double_precision_col	doub
 def	test	t1	t1	dcol_uns	dcol_uns	5	22	0	Y	32800	31	63
 def	test	t1	t1	date_col	date_col	10	10	0	Y	128	0	63
 def	test	t1	t1	time_col	time_col	11	10	0	Y	128	0	63
-def	test	t1	t1	timestamp_col	timestamp_col	7	19	0	N	9441	0	63
+def	test	t1	t1	timestamp_col	timestamp_col	7	19	0	N	9345	0	63
 def	test	t1	t1	year_col	year_col	13	4	0	Y	32864	0	63
 def	test	t1	t1	datetime_col	datetime_col	12	19	0	Y	128	0	63
 def	test	t1	t1	char_col	char_col	254	5	0	Y	0	0	8
@@ -332,12 +332,12 @@ def	test	t1	t1	dt3	dt3	12	23	0	Y	128	3	6
 def	test	t1	t1	dt2	dt2	12	22	0	Y	128	2	63
 def	test	t1	t1	dt1	dt1	12	21	0	Y	128	1	63
 def	test	t1	t1	dt0	dt0	12	19	0	Y	128	0	63
-def	test	t1	t1	ts6	ts6	7	26	0	N	9441	6	63
-def	test	t1	t1	ts5	ts5	7	25	0	N	225	5	63
-def	test	t1	t1	ts4	ts4	7	24	0	N	225	4	63
-def	test	t1	t1	ts3	ts3	7	23	0	N	225	3	63
-def	test	t1	t1	ts2	ts2	7	22	0	N	225	2	63
-def	test	t1	t1	ts1	ts1	7	21	0	N	225	1	63
-def	test	t1	t1	ts0	ts0	7	19	0	N	225	0	63
+def	test	t1	t1	ts6	ts6	7	26	0	N	9345	6	63
+def	test	t1	t1	ts5	ts5	7	25	0	N	129	5	63
+def	test	t1	t1	ts4	ts4	7	24	0	N	129	4	63
+def	test	t1	t1	ts3	ts3	7	23	0	N	129	3	63
+def	test	t1	t1	ts2	ts2	7	22	0	N	129	2	63
+def	test	t1	t1	ts1	ts1	7	21	0	N	129	1	63
+def	test	t1	t1	ts0	ts0	7	19	0	N	129	0	63
 t6	t5	t4	t3	t2	t1	t0	dt6	dt5	dt4	dt3	dt2	dt1	dt0	ts6	ts5	ts4	ts3	ts2	ts1	ts0
 DROP TABLE t1;

=== modified file 'mysql-test/r/ps_2myisam.result'
--- a/mysql-test/r/ps_2myisam.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_2myisam.result	2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_3innodb.result'
--- a/mysql-test/r/ps_3innodb.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_3innodb.result	2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_4heap.result'
--- a/mysql-test/r/ps_4heap.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_4heap.result	2012-02-23 16:32:32 +0000
@@ -64,7 +64,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_5merge.result'
--- a/mysql-test/r/ps_5merge.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_5merge.result	2012-02-23 16:32:32 +0000
@@ -106,7 +106,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63
@@ -3132,7 +3132,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result	2012-02-17 13:55:18 +0000
+++ b/mysql-test/r/type_temporal_fractional.result	2012-02-23 16:32:32 +0000
@@ -3104,13 +3104,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1	bigint(12)	YES		NULL	
-MAX(t1) + 1	decimal(13,1)	YES		NULL	
-MAX(t2) + 1	decimal(14,2)	YES		NULL	
-MAX(t3) + 1	decimal(15,3)	YES		NULL	
-MAX(t4) + 1	decimal(16,4)	YES		NULL	
-MAX(t5) + 1	decimal(17,5)	YES		NULL	
-MAX(t6) + 1	decimal(18,6)	YES		NULL	
+MAX(t0) + 1	int(9)	YES		NULL	
+MAX(t1) + 1	decimal(9,1)	YES		NULL	
+MAX(t2) + 1	decimal(10,2)	YES		NULL	
+MAX(t3) + 1	decimal(11,3)	YES		NULL	
+MAX(t4) + 1	decimal(12,4)	YES		NULL	
+MAX(t5) + 1	decimal(13,5)	YES		NULL	
+MAX(t6) + 1	decimal(14,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1	MAX(t1) + 1	MAX(t2) + 1	MAX(t3) + 1	MAX(t4) + 1	MAX(t5) + 1	MAX(t6) + 1
 101011	101011.9	101011.99	101011.999	101011.9999	101011.99999	101011.999999
@@ -3123,13 +3123,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) +
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1.1	decimal(12,1)	YES		NULL	
-MAX(t1) + 1.1	decimal(13,1)	YES		NULL	
-MAX(t2) + 1.1	decimal(14,2)	YES		NULL	
-MAX(t3) + 1.1	decimal(15,3)	YES		NULL	
-MAX(t4) + 1.1	decimal(16,4)	YES		NULL	
-MAX(t5) + 1.1	decimal(17,5)	YES		NULL	
-MAX(t6) + 1.1	decimal(18,6)	YES		NULL	
+MAX(t0) + 1.1	decimal(9,1)	YES		NULL	
+MAX(t1) + 1.1	decimal(9,1)	YES		NULL	
+MAX(t2) + 1.1	decimal(10,2)	YES		NULL	
+MAX(t3) + 1.1	decimal(11,3)	YES		NULL	
+MAX(t4) + 1.1	decimal(12,4)	YES		NULL	
+MAX(t5) + 1.1	decimal(13,5)	YES		NULL	
+MAX(t6) + 1.1	decimal(14,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1.1	MAX(t1) + 1.1	MAX(t2) + 1.1	MAX(t3) + 1.1	MAX(t4) + 1.1	MAX(t5) + 1.1	MAX(t6) + 1.1
 101011.1	101012.0	101012.09	101012.099	101012.0999	101012.09999	101012.099999
@@ -3597,8 +3597,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(18,6) DEFAULT NULL,
-  `d` decimal(18,6) DEFAULT NULL,
+  `i` decimal(14,6) DEFAULT NULL,
+  `d` decimal(14,6) DEFAULT NULL,
   `f` double DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -3627,30 +3627,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(12) DEFAULT NULL,
-  `t3 + 1` decimal(15,3) DEFAULT NULL,
-  `t6 + 1` decimal(18,6) DEFAULT NULL,
-  `t0 - 1` bigint(12) DEFAULT NULL,
-  `t3 - 1` decimal(15,3) DEFAULT NULL,
-  `t6 - 1` decimal(18,6) DEFAULT NULL,
-  `t0 * 1` bigint(12) DEFAULT NULL,
-  `t3 * 1` decimal(15,3) DEFAULT NULL,
-  `t6 * 1` decimal(18,6) DEFAULT NULL,
-  `t0 / 1` decimal(14,4) DEFAULT NULL,
-  `t3 / 1` decimal(18,7) DEFAULT NULL,
-  `t6 / 1` decimal(21,10) DEFAULT NULL,
-  `TIME'10:10:10' + 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' + 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' + 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' - 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' - 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' - 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' * 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' * 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' * 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' / 1` decimal(14,4) DEFAULT NULL,
-  `TIME'10:10:10.123' / 1` decimal(18,7) DEFAULT NULL,
-  `TIME'10:10:10.123456' / 1` decimal(21,10) DEFAULT NULL
+  `t0 + 1` int(9) DEFAULT NULL,
+  `t3 + 1` decimal(11,3) DEFAULT NULL,
+  `t6 + 1` decimal(14,6) DEFAULT NULL,
+  `t0 - 1` int(9) DEFAULT NULL,
+  `t3 - 1` decimal(11,3) DEFAULT NULL,
+  `t6 - 1` decimal(14,6) DEFAULT NULL,
+  `t0 * 1` int(9) DEFAULT NULL,
+  `t3 * 1` decimal(11,3) DEFAULT NULL,
+  `t6 * 1` decimal(14,6) DEFAULT NULL,
+  `t0 / 1` decimal(11,4) DEFAULT NULL,
+  `t3 / 1` decimal(14,7) DEFAULT NULL,
+  `t6 / 1` decimal(17,10) DEFAULT NULL,
+  `TIME'10:10:10' + 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' + 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' + 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' - 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' - 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' - 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' * 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' * 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' * 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' / 1` decimal(11,4) DEFAULT NULL,
+  `TIME'10:10:10.123' / 1` decimal(14,7) DEFAULT NULL,
+  `TIME'10:10:10.123456' / 1` decimal(17,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -3702,16 +3702,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` time DEFAULT NULL,
   `MAX(t0)` time DEFAULT NULL,
-  `AVG(t0)` decimal(14,4) DEFAULT NULL,
-  `SUM(t0)` decimal(32,0) DEFAULT NULL,
+  `AVG(t0)` decimal(11,4) DEFAULT NULL,
+  `SUM(t0)` decimal(29,0) DEFAULT NULL,
   `MIN(t3)` time(3) DEFAULT NULL,
   `MAX(t3)` time(3) DEFAULT NULL,
-  `AVG(t3)` decimal(18,7) DEFAULT NULL,
-  `SUM(t3)` decimal(36,3) DEFAULT NULL,
+  `AVG(t3)` decimal(14,7) DEFAULT NULL,
+  `SUM(t3)` decimal(32,3) DEFAULT NULL,
   `MIN(t6)` time(6) DEFAULT NULL,
   `MAX(t6)` time(6) DEFAULT NULL,
-  `AVG(t6)` decimal(21,10) DEFAULT NULL,
-  `SUM(t6)` decimal(39,6) DEFAULT NULL
+  `AVG(t6)` decimal(17,10) DEFAULT NULL,
+  `SUM(t6)` decimal(35,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -8041,13 +8041,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1	bigint(21)	YES		NULL	
-MAX(t1) + 1	decimal(22,1)	YES		NULL	
-MAX(t2) + 1	decimal(23,2)	YES		NULL	
-MAX(t3) + 1	decimal(24,3)	YES		NULL	
-MAX(t4) + 1	decimal(25,4)	YES		NULL	
-MAX(t5) + 1	decimal(26,5)	YES		NULL	
-MAX(t6) + 1	decimal(27,6)	YES		NULL	
+MAX(t0) + 1	bigint(16)	YES		NULL	
+MAX(t1) + 1	decimal(16,1)	YES		NULL	
+MAX(t2) + 1	decimal(17,2)	YES		NULL	
+MAX(t3) + 1	decimal(18,3)	YES		NULL	
+MAX(t4) + 1	decimal(19,4)	YES		NULL	
+MAX(t5) + 1	decimal(20,5)	YES		NULL	
+MAX(t6) + 1	decimal(21,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1	MAX(t1) + 1	MAX(t2) + 1	MAX(t3) + 1	MAX(t4) + 1	MAX(t5) + 1	MAX(t6) + 1
 20010101101011	20010101101011.9	20010101101011.99	20010101101011.999	20010101101011.9999	20010101101011.99999	20010101101011.999999
@@ -8060,13 +8060,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) +
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1.1	decimal(21,1)	YES		NULL	
-MAX(t1) + 1.1	decimal(22,1)	YES		NULL	
-MAX(t2) + 1.1	decimal(23,2)	YES		NULL	
-MAX(t3) + 1.1	decimal(24,3)	YES		NULL	
-MAX(t4) + 1.1	decimal(25,4)	YES		NULL	
-MAX(t5) + 1.1	decimal(26,5)	YES		NULL	
-MAX(t6) + 1.1	decimal(27,6)	YES		NULL	
+MAX(t0) + 1.1	decimal(16,1)	YES		NULL	
+MAX(t1) + 1.1	decimal(16,1)	YES		NULL	
+MAX(t2) + 1.1	decimal(17,2)	YES		NULL	
+MAX(t3) + 1.1	decimal(18,3)	YES		NULL	
+MAX(t4) + 1.1	decimal(19,4)	YES		NULL	
+MAX(t5) + 1.1	decimal(20,5)	YES		NULL	
+MAX(t6) + 1.1	decimal(21,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1.1	MAX(t1) + 1.1	MAX(t2) + 1.1	MAX(t3) + 1.1	MAX(t4) + 1.1	MAX(t5) + 1.1	MAX(t6) + 1.1
 20010101101011.1	20010101101012.0	20010101101012.09	20010101101012.099	20010101101012.0999	20010101101012.09999	20010101101012.099999
@@ -8470,8 +8470,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(27,6) DEFAULT NULL,
-  `d` decimal(27,6) DEFAULT NULL,
+  `i` decimal(21,6) DEFAULT NULL,
+  `d` decimal(21,6) DEFAULT NULL,
   `f` double DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -8500,30 +8500,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(21) DEFAULT NULL,
-  `t3 + 1` decimal(24,3) DEFAULT NULL,
-  `t6 + 1` decimal(27,6) DEFAULT NULL,
-  `t0 - 1` bigint(21) DEFAULT NULL,
-  `t3 - 1` decimal(24,3) DEFAULT NULL,
-  `t6 - 1` decimal(27,6) DEFAULT NULL,
-  `t0 * 1` bigint(21) DEFAULT NULL,
-  `t3 * 1` decimal(24,3) DEFAULT NULL,
-  `t6 * 1` decimal(27,6) DEFAULT NULL,
-  `t0 / 1` decimal(23,4) DEFAULT NULL,
-  `t3 / 1` decimal(27,7) DEFAULT NULL,
-  `t6 / 1` decimal(30,10) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+  `t0 + 1` bigint(16) DEFAULT NULL,
+  `t3 + 1` decimal(18,3) DEFAULT NULL,
+  `t6 + 1` decimal(21,6) DEFAULT NULL,
+  `t0 - 1` bigint(16) DEFAULT NULL,
+  `t3 - 1` decimal(18,3) DEFAULT NULL,
+  `t6 - 1` decimal(21,6) DEFAULT NULL,
+  `t0 * 1` bigint(16) DEFAULT NULL,
+  `t3 * 1` decimal(18,3) DEFAULT NULL,
+  `t6 * 1` decimal(21,6) DEFAULT NULL,
+  `t0 / 1` decimal(18,4) DEFAULT NULL,
+  `t3 / 1` decimal(21,7) DEFAULT NULL,
+  `t6 / 1` decimal(24,10) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -8575,16 +8575,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` datetime DEFAULT NULL,
   `MAX(t0)` datetime DEFAULT NULL,
-  `AVG(t0)` decimal(23,4) DEFAULT NULL,
-  `SUM(t0)` decimal(41,0) DEFAULT NULL,
+  `AVG(t0)` decimal(18,4) DEFAULT NULL,
+  `SUM(t0)` decimal(36,0) DEFAULT NULL,
   `MIN(t3)` datetime(3) DEFAULT NULL,
   `MAX(t3)` datetime(3) DEFAULT NULL,
-  `AVG(t3)` decimal(27,7) DEFAULT NULL,
-  `SUM(t3)` decimal(45,3) DEFAULT NULL,
+  `AVG(t3)` decimal(21,7) DEFAULT NULL,
+  `SUM(t3)` decimal(39,3) DEFAULT NULL,
   `MIN(t6)` datetime(6) DEFAULT NULL,
   `MAX(t6)` datetime(6) DEFAULT NULL,
-  `AVG(t6)` decimal(30,10) DEFAULT NULL,
-  `SUM(t6)` decimal(48,6) DEFAULT NULL
+  `AVG(t6)` decimal(24,10) DEFAULT NULL,
+  `SUM(t6)` decimal(42,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -13161,8 +13161,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `d` decimal(27,6) NOT NULL DEFAULT '0.000000',
+  `i` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `d` decimal(21,6) NOT NULL DEFAULT '0.000000',
   `f` double NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -13191,30 +13191,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 / 1` decimal(23,4) DEFAULT NULL,
-  `t3 / 1` decimal(27,7) DEFAULT NULL,
-  `t6 / 1` decimal(30,10) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+  `t0 + 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 - 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 * 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 / 1` decimal(18,4) DEFAULT NULL,
+  `t3 / 1` decimal(21,7) DEFAULT NULL,
+  `t6 / 1` decimal(24,10) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -13266,16 +13266,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` datetime DEFAULT NULL,
   `MAX(t0)` datetime DEFAULT NULL,
-  `AVG(t0)` decimal(23,4) DEFAULT NULL,
-  `SUM(t0)` decimal(41,0) DEFAULT NULL,
+  `AVG(t0)` decimal(18,4) DEFAULT NULL,
+  `SUM(t0)` decimal(36,0) DEFAULT NULL,
   `MIN(t3)` datetime(3) DEFAULT NULL,
   `MAX(t3)` datetime(3) DEFAULT NULL,
-  `AVG(t3)` decimal(27,7) DEFAULT NULL,
-  `SUM(t3)` decimal(45,3) DEFAULT NULL,
+  `AVG(t3)` decimal(21,7) DEFAULT NULL,
+  `SUM(t3)` decimal(39,3) DEFAULT NULL,
   `MIN(t6)` datetime(6) DEFAULT NULL,
   `MAX(t6)` datetime(6) DEFAULT NULL,
-  `AVG(t6)` decimal(30,10) DEFAULT NULL,
-  `SUM(t6)` decimal(48,6) DEFAULT NULL
+  `AVG(t6)` decimal(24,10) DEFAULT NULL,
+  `SUM(t6)` decimal(42,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;

=== modified file 'mysql-test/r/type_timestamp.result'
--- a/mysql-test/r/type_timestamp.result	2012-01-31 15:16:16 +0000
+++ b/mysql-test/r/type_timestamp.result	2012-02-23 16:32:32 +0000
@@ -622,3 +622,30 @@ MAX(dt) = '2011-01-06 12:34:30'
 1
 DROP TABLE t1;
 End of 5.5 tests
+#
+# Start of 5.6 tests
+#
+#
+# Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+#
+CREATE TABLE t1 (
+`c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+`c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+c2-c1
+-30389003988
+SELECT * FROM t1;
+c1	c2
+2003-05-16 23:53:29	2000-01-27 23:13:41
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
+TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'
+-30389003988
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
+TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29')
+-30389003988
+DROP TABLE t1;
+#
+# End of 5.6 tests
+#

=== modified file 'mysql-test/t/type_timestamp.test'
--- a/mysql-test/t/type_timestamp.test	2012-01-31 15:16:16 +0000
+++ b/mysql-test/t/type_timestamp.test	2012-02-23 16:32:32 +0000
@@ -426,3 +426,26 @@ DROP TABLE t1;
 
 --echo End of 5.5 tests
 
+
+--echo #
+--echo # Start of 5.6 tests
+--echo #
+
+--echo #
+--echo # Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+--echo #
+CREATE TABLE t1 (
+  `c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+SELECT * FROM t1;
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'; 
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29'); 
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 5.6 tests
+--echo #

=== modified file 'sql/field.cc'
--- a/sql/field.cc	2012-02-13 06:26:52 +0000
+++ b/sql/field.cc	2012-02-23 16:32:32 +0000
@@ -5144,8 +5144,6 @@ Field_temporal_with_date_and_time::conve
 
 void Field_temporal_with_date_and_time::init_timestamp_flags()
 {
-  /* For 4.0 MYD and 4.0 InnoDB compatibility */
-  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
   if (unireg_check != NONE)
   {
     /*
@@ -5243,6 +5241,8 @@ Field_timestamp::Field_timestamp(uchar *
                                      unireg_check_arg, field_name_arg, 0)
 {
   init_timestamp_flags();
+   /* For 4.0 MYD and 4.0 InnoDB compatibility */
+  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
 }
 
 
@@ -5253,6 +5253,8 @@ Field_timestamp::Field_timestamp(bool ma
                                      NONE, field_name_arg, 0)
 {
   init_timestamp_flags();
+  /* For 4.0 MYD and 4.0 InnoDB compatibility */
+  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
 }
 
 
@@ -5429,8 +5431,6 @@ Field_timestampf::Field_timestampf(bool
                                       maybe_null_arg ? (uchar*) "": 0, 0,
                                       NONE, field_name_arg, dec_arg)
 {
-  /* For 4.0 MYD and 4.0 InnoDB compatibility */
-  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
   if (unireg_check != TIMESTAMP_DN_FIELD)
     flags|= ON_UPDATE_NOW_FLAG;
 }
@@ -9742,6 +9742,9 @@ bool Create_field::init(THD *thd, char *
     }
     break;
   case MYSQL_TYPE_TIMESTAMP:
+    /* Add flags for TIMESTAMP for 4.0 MYD and 4.0 InnoDB compatibility */
+    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    /* Fall through */
   case MYSQL_TYPE_TIMESTAMP2:
     if (fld_length == NULL)
     {
@@ -9759,7 +9762,7 @@ bool Create_field::init(THD *thd, char *
         length= ((length+1)/2)*2;
       length= min<ulong>(length, MAX_DATETIME_COMPRESSED_WIDTH);
     }
-    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    
     /*
       Since we silently rewrite down to MAX_DATETIME_COMPRESSED_WIDTH bytes,
       the parser should not raise errors unless bizzarely large. 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-02-16 11:31:37 +0000
+++ b/sql/item.cc	2012-02-23 16:32:32 +0000
@@ -617,6 +617,18 @@ uint Item::decimal_precision() const
                                      unsigned_flag);
     return min<uint>(prec, DECIMAL_MAX_PRECISION);
   }
+  switch (field_type())
+  {
+    case MYSQL_TYPE_TIME:
+      return decimals + TIME_INT_DIGITS;
+    case MYSQL_TYPE_DATETIME:
+    case MYSQL_TYPE_TIMESTAMP:
+      return decimals + DATETIME_INT_DIGITS;
+    case MYSQL_TYPE_DATE:
+      return decimals + DATE_INT_DIGITS;
+    default:
+      break;
+  }
   return min<uint>(max_char_length(), DECIMAL_MAX_PRECISION);
 }
 

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-02-17 13:55:18 +0000
+++ b/sql/item_func.cc	2012-02-23 16:32:32 +0000
@@ -596,6 +596,7 @@ void Item_func_numhybrid::fix_num_length
 */
 void Item_func::count_datetime_length(Item **item, uint nitems)
 {
+  unsigned_flag= 0;
   decimals= 0;
   if (field_type() != MYSQL_TYPE_DATE)
   {
@@ -611,16 +612,13 @@ void Item_func::count_datetime_length(It
     case MYSQL_TYPE_DATETIME:
     case MYSQL_TYPE_TIMESTAMP:
       len+= MAX_DATETIME_WIDTH;
-      unsigned_flag= 1;
       break;
     case MYSQL_TYPE_DATE:
     case MYSQL_TYPE_NEWDATE:
       len+= MAX_DATE_WIDTH;
-      unsigned_flag= 1;
       break;
     case MYSQL_TYPE_TIME:
       len+= MAX_TIME_WIDTH;
-      unsigned_flag= 0;
       break;
     default:
       DBUG_ASSERT(0);

=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc	2012-02-16 09:51:14 +0000
+++ b/sql/item_timefunc.cc	2012-02-23 16:32:32 +0000
@@ -2235,11 +2235,9 @@ void Item_date_add_interval::fix_length_
     uint8 dec= MY_MAX(args[0]->datetime_precision(), interval_dec);
     fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
     cached_field_type= MYSQL_TYPE_DATETIME;
-    unsigned_flag= 1;
   }
   else if (arg0_field_type == MYSQL_TYPE_DATE)
   {
-    unsigned_flag= 1;
     if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
     {
       cached_field_type= MYSQL_TYPE_DATE;
@@ -2808,7 +2806,6 @@ void Item_func_add_time::fix_length_and_
   else if (args[0]->is_temporal_with_date_and_time() || is_date)
   {
     cached_field_type= MYSQL_TYPE_DATETIME;
-    unsigned_flag= 1;
     uint8 dec= MY_MAX(args[0]->datetime_precision(), args[1]->time_precision());
     fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
   }
@@ -3327,7 +3324,6 @@ void Item_func_str_to_date::fix_from_for
         */
         cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
         cached_field_type= MYSQL_TYPE_DATETIME; 
-        unsigned_flag= 1;
         fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH,
                                                 DATETIME_MAX_DECIMALS);
         return;
@@ -3345,7 +3341,6 @@ void Item_func_str_to_date::fix_from_for
   }
   else if (time_part_used)
   {
-    unsigned_flag= 1;
     if (date_part_used) /* DATETIME, no microseconds */
     {
       cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;

=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h	2012-01-31 15:16:16 +0000
+++ b/sql/item_timefunc.h	2012-02-23 16:32:32 +0000
@@ -588,11 +588,11 @@ class Item_date_func :public Item_tempor
 {
 public:
   Item_date_func() :Item_temporal_func()
-  { unsigned_flag= 1; }
+  { }
   Item_date_func(Item *a) :Item_temporal_func(a)
-  { unsigned_flag= 1; }
+  { }
   Item_date_func(Item *a, Item *b) :Item_temporal_func(a, b)
-  { unsigned_flag= 1; }
+  { }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   bool get_time(MYSQL_TIME *ltime)
   {
@@ -636,13 +636,13 @@ class Item_datetime_func :public Item_te
 {
 public:
   Item_datetime_func() :Item_temporal_func()
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a) :Item_temporal_func(a)
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a,Item *b) :Item_temporal_func(a,b)
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a,Item *b, Item *c) :Item_temporal_func(a,b,c)
-  { unsigned_flag= 1; }
+  { }
   enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
   double val_real() { return val_real_from_decimal(); }
   String *val_str(String *str)

=== modified file 'sql/sql_const.h'
--- a/sql/sql_const.h	2011-11-24 08:02:23 +0000
+++ b/sql/sql_const.h	2012-02-23 16:32:32 +0000
@@ -57,6 +57,10 @@
 #define MAX_DATETIME_WIDTH	19	/* YYYY-MM-DD HH:MM:SS */
 #define MAX_DATETIME_COMPRESSED_WIDTH 14  /* YYYYMMDDHHMMSS */
 
+#define DATE_INT_DIGITS       8         /* YYYYMMDD       */
+#define TIME_INT_DIGITS       7         /* hhhmmss        */
+#define DATETIME_INT_DIGITS  14         /* YYYYMMDDhhmmss */
+
 #define MAX_TABLES	(sizeof(table_map)*8-3)	/* Max tables in join */
 #define PARAM_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-3))
 #define OUTER_REF_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-2))

No bundle (reason: useless for push emails).