Fix for bugs #1885, #2464, #2539. Proper handling of default

values for TIMESTAMP columns. The solution is not perfect since
we just silently ignoring default value for first TIMESTAMP 
column and properly reflecting this fact in SHOW CREATE TABLE.
We can't give a warning or simply support standard syntax 
(niladic functions as legal value for default) for first field 
since it is 4.0 tree.

2 files changed, 75 insertions, 5 deletions

diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index b513f958787..cd45bcf911d 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -122,5 +122,48 @@ t2	t4	t6	t8	t10	t12	t14
 0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00
 1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59
 drop table t1;
-create table t1 (a timestamp default 1);
-Invalid default value for 'a'
+create table t1 (t1 timestamp default '2003-01-01 00:00:00', 
+t2 timestamp default '2003-01-01 00:00:00');
+set TIMESTAMP=1000000000;
+insert into t1 values();
+select * from t1;
+t1	t2
+2001-09-09 04:46:40	2003-01-01 00:00:00
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t1` timestamp(14) NOT NULL,
+  `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+) TYPE=MyISAM
+show columns from t1;
+Field	Type	Null	Key	Default	Extra
+t1	timestamp(14)	YES		NULL	
+t2	timestamp(14)	YES		2003-01-01 00:00:00	
+show columns from t1 like 't2';
+Field	Type	Null	Key	Default	Extra
+t2	timestamp(14)	YES		2003-01-01 00:00:00	
+create table t2 (select * from t1);
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `t1` timestamp(14) NOT NULL,
+  `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+) TYPE=MyISAM
+alter table t1 add column t0 timestamp first;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t0` timestamp(14) NOT NULL,
+  `t1` timestamp(14) NOT NULL default '2003-01-01 00:00:00',
+  `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+) TYPE=MyISAM
+drop table t1,t2;
+create table t1 (ts1 timestamp, ts2 timestamp);
+set TIMESTAMP=1000000000;
+insert into t1 values ();
+insert into t1 values (DEFAULT, DEFAULT);
+select * from t1;
+ts1	ts2
+2001-09-09 04:46:40	0000-00-00 00:00:00
+2001-09-09 04:46:40	0000-00-00 00:00:00
+drop table t1;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 17f7b7c487f..3483227376e 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -73,8 +73,35 @@ select * from t1;
 drop table t1;
 
 #
-# Bug #1885
+# Bug #1885, bug #2539. 
+# Not perfect but still sensible attitude towards defaults for TIMESTAMP
+# We will ignore default value for first TIMESTAMP column.
 #
+create table t1 (t1 timestamp default '2003-01-01 00:00:00', 
+                 t2 timestamp default '2003-01-01 00:00:00');
+set TIMESTAMP=1000000000;
+insert into t1 values();
+select * from t1;
+show create table t1;
+show columns from t1;
+show columns from t1 like 't2';
+create table t2 (select * from t1);
+show create table t2;
+
+# Ugly, but we can't do anything about this in 4.0
+alter table t1 add column t0 timestamp first;
+show create table t1;
 
---error 1067
-create table t1 (a timestamp default 1);
+drop table t1,t2;
+
+#
+# Test for bug 2464, DEFAULT keyword in INSERT statement should return
+# default value for column.
+#
+
+create table t1 (ts1 timestamp, ts2 timestamp);
+set TIMESTAMP=1000000000;
+insert into t1 values ();
+insert into t1 values (DEFAULT, DEFAULT);
+select * from t1;
+drop table t1;