MDEV-3929 Add system variable explicit_defaults_for_timestamp for compatibility with MySQL

18 files changed, 55 insertions, 54 deletions

diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 30c93f58e2f..0470e8f3d9d 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -401,7 +401,7 @@ explain t2;
 select * from t2;
 drop table t1, t2;
 
-create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
+create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
 create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
 show create table t2;
 drop table t1,t2;
@@ -1436,7 +1436,7 @@ DROP TABLE IF EXISTS t3;
 
 --echo
 
-CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
+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');
 
 --echo
 
@@ -1448,7 +1448,7 @@ CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
 
 --echo
 --error ER_INVALID_DEFAULT
-CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
+CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL);
 
 --echo
 --echo # -- Check that NULL column still can be created.
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index ff8396fd7fd..2d95dffa17a 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -30,7 +30,7 @@ drop table t1;
 # Test insert syntax
 #
 
-create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int);
+create table t1 (a int not null auto_increment, primary key (a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c char(10) default "hello", i int);
 insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default);
 select a,t>0,c,i from t1;
 truncate table t1;
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 355c5ef53a4..e2697effeb6 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -395,7 +395,7 @@ create table t1 (
     c1 int,
     c2 char(12),
     c3 varchar(123),
-    c4 timestamp,
+    c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     index (c1),
     index i1 (c1),
     index i2 (c2),
diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test
index a859f39e51d..f5ed65cf7e2 100644
--- a/mysql-test/t/metadata.test
+++ b/mysql-test/t/metadata.test
@@ -15,7 +15,7 @@ drop table if exists t1,t2;
 
 select 1, 1.0, -1, "hello", NULL;
 
-create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
+create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
 select * from t1;
 select a b, b c from t1 as t2;
 drop table t1;
@@ -176,7 +176,7 @@ dcol_uns double unsigned,
 # date/time types
 date_col date,
 time_col time,
-timestamp_col timestamp,
+timestamp_col timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 year_col year,
 datetime_col datetime,
 # string types
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 8c620ed2ce4..d54c2ab7c55 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -195,8 +195,8 @@ UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
 set sql_safe_updates=0;
 drop table t1,t2;
 set timestamp=1038401397;
-create table t1 (n int(10) not null primary key, d int(10), t timestamp);
-create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+create table t1 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values(1,1,NULL);
 insert into t2 values(1,10,NULL),(2,20,NULL);
 set timestamp=1038000000;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index c3267ad7a40..11652b3bedf 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1080,7 +1080,7 @@ drop table t1;
 drop table if exists t1;
 --enable_warnings
 
-create table t1 (`d` timestamp, unique (`d`));
+create table t1 (`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, unique (`d`));
 set time_zone='+00:00';
 insert into t1 values ('2003-10-25 22:00:00'),('2003-10-25 23:00:00');
 # results should show two different time values
diff --git a/mysql-test/t/ps_4heap.test b/mysql-test/t/ps_4heap.test
index f16d4599a74..2c0b64b1503 100644
--- a/mysql-test/t/ps_4heap.test
+++ b/mysql-test/t/ps_4heap.test
@@ -31,7 +31,8 @@ eval create table t9
   c1  tinyint, c2  smallint, c3  mediumint, c4  int,
   c5  integer, c6  bigint, c7  float, c8  double,
   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
-  c13 date, c14 datetime, c15 timestamp, c16 time,
+  c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
+  ON UPDATE CURRENT_TIMESTAMP, c16 time,
   c17 year, c18 tinyint, c19 bool, c20 char,
   c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100),
   c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100),
diff --git a/mysql-test/t/ps_5merge.test b/mysql-test/t/ps_5merge.test
index e6ce9bf42d3..adef6b51f0e 100644
--- a/mysql-test/t/ps_5merge.test
+++ b/mysql-test/t/ps_5merge.test
@@ -31,7 +31,7 @@ create table t9
   c1  tinyint, c2  smallint, c3  mediumint, c4  int,
   c5  integer, c6  bigint, c7  float, c8  double,
   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
-  c13 date, c14 datetime, c15 timestamp, c16 time,
+  c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time,
   c17 year, c18 tinyint, c19 bool, c20 char,
   c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
   c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
@@ -62,7 +62,7 @@ create table t9
   c1  tinyint, c2  smallint, c3  mediumint, c4  int,
   c5  integer, c6  bigint, c7  float, c8  double,
   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
-  c13 date, c14 datetime, c15 timestamp, c16 time,
+  c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time,
   c17 year, c18 tinyint, c19 bool, c20 char,
   c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
   c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 2d75dc48f73..b207db042e6 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1814,7 +1814,7 @@ DROP TABLE t1;
 # Test of bug with SUM(CASE...)
 #
 
-CREATE TABLE t1 (gvid int(10) unsigned default NULL,  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  mmid int(10) unsigned default NULL,  hdid int(10) unsigned default NULL,  fsid int(10) unsigned default NULL,  ctid int(10) unsigned default NULL,  dtid int(10) unsigned default NULL,  cost int(10) unsigned default NULL,  performance int(10) unsigned default NULL,  serialnumber bigint(20) unsigned default NULL,  monitored tinyint(3) unsigned default '1',  removed tinyint(3) unsigned default '0',  target tinyint(3) unsigned default '0',  dt_modified timestamp NOT NULL,  name varchar(255) binary default NULL,  description varchar(255) default NULL,  UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
+CREATE TABLE t1 (gvid int(10) unsigned default NULL,  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  mmid int(10) unsigned default NULL,  hdid int(10) unsigned default NULL,  fsid int(10) unsigned default NULL,  ctid int(10) unsigned default NULL,  dtid int(10) unsigned default NULL,  cost int(10) unsigned default NULL,  performance int(10) unsigned default NULL,  serialnumber bigint(20) unsigned default NULL,  monitored tinyint(3) unsigned default '1',  removed tinyint(3) unsigned default '0',  target tinyint(3) unsigned default '0',  dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  name varchar(255) binary default NULL,  description varchar(255) default NULL,  UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);
 CREATE TABLE t2 (  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  sampletid smallint(5) unsigned default NULL,  sampletime datetime default NULL,  samplevalue bigint(20) unsigned default NULL,  KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
 INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
@@ -3260,7 +3260,7 @@ DROP TABLE t1;
 #
 # Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
 #
-CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
 ANALYZE TABLE t1;
 
@@ -3832,7 +3832,7 @@ SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
 DROP TABLE t1;
 
-CREATE TABLE t1(a TIMESTAMP NOT NULL);
+CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 INSERT INTO t1 VALUES('2001-01-01');
 SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index d7bfb3a973e..d9aed952fce 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -170,7 +170,7 @@ type_numeric numeric(5,2),
 empty_char char(0),
 type_char char(2),
 type_varchar varchar(10),
-type_timestamp timestamp not null,
+type_timestamp timestamp not null default current_timestamp on update current_timestamp,
 type_date date not null default '0000-00-00',
 type_time time not null default '00:00:00',
 type_datetime datetime not null default '0000-00-00 00:00:00',
diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test
index b46da77554a..869bf45dbf3 100644
--- a/mysql-test/t/sql_mode.test
+++ b/mysql-test/t/sql_mode.test
@@ -93,7 +93,7 @@ set @@SQL_MODE=NULL;
 set session sql_mode=ansi;
 create table t1
 (f1 integer auto_increment primary key,
- f2 timestamp default current_timestamp on update current_timestamp);
+ f2 timestamp not null default current_timestamp on update current_timestamp);
 show create table t1;
 set session sql_mode=no_field_options;
 show create table t1;
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index b1d36447e00..93b31499bef 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1132,11 +1132,11 @@ drop table t1;
 #
 
 set @@sql_mode='traditional';
-create table t1(a int, b timestamp);
+create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 alter table t1 add primary key(a);
 show create table t1;
 drop table t1;
-create table t1(a int, b timestamp default 20050102030405);
+create table t1(a int, b timestamp not null default 20050102030405);
 alter table t1 add primary key(a);
 show create table t1;
 drop table t1;
diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test
index 2b01934edea..dcc765ae132 100644
--- a/mysql-test/t/system_mysql_db_fix50117.test
+++ b/mysql-test/t/system_mysql_db_fix50117.test
@@ -45,9 +45,9 @@ CREATE TABLE IF NOT EXISTS plugin ( name char(64) binary DEFAULT '' NOT NULL, dl
 CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table';
 
 
-CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Table privileges';
+CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Table privileges';
 
-CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Column privileges';
+CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Column privileges';
 
 
 CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example  text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8   comment='help topics';
@@ -77,12 +77,12 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type (   Time_zone_id int unsign
 CREATE TABLE IF NOT EXISTS time_zone_leap_second (   Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8   comment='Leap seconds information for time zones';
 
 
-CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name  char(64) DEFAULT '' NOT NULL, type  enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language  enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list  blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body  longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures';
+CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name  char(64) DEFAULT '' NOT NULL, type  enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language  enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list  blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body  longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures';
 
 
-CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Procedure privileges';
+CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Procedure privileges';
 
-CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
+CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
 
 # Run the mysql_fix_privilege_tables.sql using "mysql --force"
 --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index b417c298837..9d049d0c6e7 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -32,7 +32,7 @@ drop table t1;
 # Test insert of now() and curtime()
 #
 
-CREATE TABLE t1 (a timestamp, b date, c time, d datetime);
+CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime);
 insert into t1 (b,c,d) values(now(),curtime(),now());
 select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
 drop table t1;
@@ -182,7 +182,7 @@ if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`)
    # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'.
    --real_sleep 5
 }
-create table t1 (f1 date, f2 datetime, f3 timestamp);
+create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1(f1) values(curdate());
 select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
 delete from t1;
@@ -201,7 +201,7 @@ drop table t1;
 #
 # Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
 #
-create table t1 (f1 date, f2 datetime, f3 timestamp);
+create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
 insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
 insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 4a897c1e440..44b0835fcae 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -22,7 +22,7 @@ CREATE TABLE t1 (
   umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
   ulong int(11) unsigned DEFAULT '0' NOT NULL,
   ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
-  time_stamp timestamp,
+  time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   date_field date,	
   time_field time,	
   date_time datetime,
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 97b95d66077..6188ed6b4d6 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -9,7 +9,7 @@ drop table if exists t1,t2;
 # Set timezone to GMT-3, to make it possible to use "interval 3 hour"
 set time_zone="+03:00";
 
-CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 CREATE TABLE t2 (a int, t datetime);
 SET TIMESTAMP=1234;
 insert into t1 values(1,NULL);
@@ -27,7 +27,7 @@ select * from t1;
 drop table t1,t2;
 
 SET TIMESTAMP=1234;
-CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
+CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";
 UPDATE t1 SET value="my value" WHERE id="myKey";
@@ -36,12 +36,12 @@ UPDATE t1 SET id="myKey" WHERE value="my value";
 SELECT stamp FROM t1 WHERE id="myKey";
 drop table t1;
 
-create table t1 (a timestamp);
+create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values (now());
 select date_format(a,"%Y %y"),year(a),year(now()) from t1;
 drop table t1;
 
-create table t1 (ix timestamp);
+create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
 select ix+0 from t1;
 truncate table t1;
@@ -49,7 +49,7 @@ insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),(
 select ix+0 from t1;
 drop table t1;
 
-CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
+CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
 INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
 INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
@@ -71,7 +71,7 @@ drop table t1;
 # Let us check if we properly treat wrong datetimes and produce proper warnings
 # (for both strings and numbers)
 #
-create table t1 (ix timestamp);
+create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
 select ix+0 from t1;
 truncate table t1;
@@ -100,7 +100,7 @@ drop table t1;
 # Let us test TIMESTAMP auto-update behaviour
 # Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and
 # behaviour of DEFAULT literal for such fields
-create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp);
+create table t1 (t1 timestamp not null default '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00');
 SET TIMESTAMP=1000000000;
 insert into t1 values ();
 SET TIMESTAMP=1000000001;
@@ -112,7 +112,7 @@ show create table t1;
 show columns from t1;
 drop table t1;
 
-create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp);
+create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00');
 SET TIMESTAMP=1000000002;
 insert into t1 values ();
 SET TIMESTAMP=1000000003;
@@ -124,7 +124,7 @@ show create table t1;
 show columns from t1;
 drop table t1;
 
-create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime);
+create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime);
 SET TIMESTAMP=1000000004;
 insert into t1 values ();
 select * from t1;
@@ -137,7 +137,7 @@ show create table t1;
 show columns from t1;
 drop table t1;
 
-create table t1 (t1 timestamp default now() on update now(), t2 datetime);
+create table t1 (t1 timestamp not null default now() on update now(), t2 datetime);
 SET TIMESTAMP=1000000006;
 insert into t1 values ();
 select * from t1;
@@ -150,7 +150,7 @@ show create table t1;
 show columns from t1;
 drop table t1;
 
-create table t1 (t1 timestamp, t2 datetime, t3 timestamp);
+create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00');
 SET TIMESTAMP=1000000007;
 insert into t1 values ();
 select * from t1;
@@ -166,7 +166,7 @@ drop table t1;
 # Let us test if CURRENT_TIMESTAMP also works well as default value
 # (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just
 # for demonstartion.)
-create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
+create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime);
 SET TIMESTAMP=1000000009;
 insert into t1 values ();
 select * from t1;
@@ -196,21 +196,21 @@ drop table t1;
 
 # Now let us test replace it should behave exactly like delete+insert
 # Case where optimization is possible DEFAULT = ON UPDATE
-create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
+create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 SET TIMESTAMP=1000000013;
 replace into t1 set pk = 1, bulk= 20; 
 select * from t1;
 drop table t1;
 # Case in which there should not be optimisation
-create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
+create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 SET TIMESTAMP=1000000014;
 replace into t1 set pk = 1, bulk= 20; 
 select * from t1;
 drop table t1;
 # Other similar case
-create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
+create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 SET TIMESTAMP=1000000015;
 replace into t1 set pk = 1, bulk= 20; 
@@ -218,7 +218,7 @@ select * from t1;
 drop table t1;
 
 # Let us test alter now
-create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
+create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp);
 insert into t1 values ('2004-04-01 00:00:00');
 SET TIMESTAMP=1000000016;
 alter table t1 add i int default 10;
@@ -266,7 +266,7 @@ drop table t1;
 create table t1 (a bigint, b bigint);
 insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
 set timestamp=1000000019;
-alter table t1 modify a timestamp, modify b timestamp;
+alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT '0000-00-00 00:00:0';
 select * from t1;
 drop table t1;
 
@@ -274,7 +274,7 @@ drop table t1;
 # Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
 # using GROUP BY in @@new=1 mode.
 #
-create table t1 (a char(2), t timestamp);
+create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
                       ('b', '2004-02-01 00:00:00');
 select max(t) from t1 group by a;
@@ -295,7 +295,7 @@ drop table t1;
 #
 # Bug#7806 - insert on duplicate key and auto-update of timestamp
 #
-create table t1 (a int auto_increment primary key, b int, c timestamp);
+create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
   (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
 select * from t1;
@@ -333,8 +333,8 @@ drop table t1;
 #
 
 CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-                  f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-                  f3 TIMESTAMP);
+                  f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+                  f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00');
 INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
 INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
 INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
@@ -351,7 +351,7 @@ DROP TABLE t1;
 --echo #             Version "5.1.42 SUSE MySQL RPM"
 --echo #
 
-CREATE TABLE t1 (a TIMESTAMP, KEY (a));
+CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a));
 
 INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'),
                       ('2000-01-01 00:00:01'), ('2000-01-01 00:00:01');
@@ -421,7 +421,7 @@ SELECT UNIX_TIMESTAMP('abc');
 --echo
 
 SET TIMESTAMP=0;
-CREATE TABLE t1(a timestamp);
+CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02');
 FLUSH TABLES t1;
 SELECT MAX(a) FROM t1;
diff --git a/mysql-test/t/type_timestamp_hires.test b/mysql-test/t/type_timestamp_hires.test
index 17a2c3e1f1f..0a49056294f 100644
--- a/mysql-test/t/type_timestamp_hires.test
+++ b/mysql-test/t/type_timestamp_hires.test
@@ -5,7 +5,7 @@ let type=timestamp;
 set time_zone='+03:00';
 set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456;
 
-create table t1 (a timestamp(5));
+create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP);
 #
 # CREATE ... DEFAULT NOW(X)
 #
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index b8b040b0d0d..87f88570c3b 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -847,7 +847,7 @@ drop table t1;
 #
 # Bug#12185: Data type aggregation may produce wrong result
 #
-create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);
+create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);
 create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
 show create table t2;
 drop table t1, t2;