1CREATE TABLE t1 (a TIMESTAMP); 2SHOW CREATE TABLE t1; 3Table Create Table 4t1 CREATE TABLE `t1` ( 5 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() 6) ENGINE=MyISAM DEFAULT CHARSET=latin1 7DROP TABLE t1; 8CREATE TABLE t1 (a TIMESTAMP NULL); 9SHOW CREATE TABLE t1; 10Table Create Table 11t1 CREATE TABLE `t1` ( 12 `a` timestamp NULL DEFAULT NULL 13) ENGINE=MyISAM DEFAULT CHARSET=latin1 14DROP TABLE t1; 15CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL); 16ERROR 42000: Invalid default value for 'a' 17CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00'); 18SHOW CREATE TABLE t1; 19Table Create Table 20t1 CREATE TABLE `t1` ( 21 `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 22) ENGINE=MyISAM DEFAULT CHARSET=latin1 23DROP TABLE t1; 24CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30'); 25SHOW CREATE TABLE t1; 26Table Create Table 27t1 CREATE TABLE `t1` ( 28 `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30' 29) ENGINE=MyISAM DEFAULT CHARSET=latin1 30DROP TABLE t1; 31CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 32SHOW CREATE TABLE t1; 33Table Create Table 34t1 CREATE TABLE `t1` ( 35 `a` timestamp NOT NULL DEFAULT current_timestamp() 36) ENGINE=MyISAM DEFAULT CHARSET=latin1 37DROP TABLE t1; 38CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL); 39SHOW CREATE TABLE t1; 40Table Create Table 41t1 CREATE TABLE `t1` ( 42 `a` timestamp NULL DEFAULT NULL 43) ENGINE=MyISAM DEFAULT CHARSET=latin1 44DROP TABLE t1; 45CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); 46SHOW CREATE TABLE t1; 47Table Create Table 48t1 CREATE TABLE `t1` ( 49 `a` timestamp NULL DEFAULT '0000-00-00 00:00:00' 50) ENGINE=MyISAM DEFAULT CHARSET=latin1 51DROP TABLE t1; 52CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30'); 53SHOW CREATE TABLE t1; 54Table Create Table 55t1 CREATE TABLE `t1` ( 56 `a` timestamp NULL DEFAULT '2001-01-01 10:20:30' 57) ENGINE=MyISAM DEFAULT CHARSET=latin1 58DROP TABLE t1; 59CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); 60SHOW CREATE TABLE t1; 61Table Create Table 62t1 CREATE TABLE `t1` ( 63 `a` timestamp NULL DEFAULT current_timestamp() 64) ENGINE=MyISAM DEFAULT CHARSET=latin1 65DROP TABLE t1; 66CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); 67SHOW CREATE TABLE t1; 68Table Create Table 69t1 CREATE TABLE `t1` ( 70 `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 71) ENGINE=MyISAM DEFAULT CHARSET=latin1 72DROP TABLE t1; 73CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'); 74SHOW CREATE TABLE t1; 75Table Create Table 76t1 CREATE TABLE `t1` ( 77 `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30' 78) ENGINE=MyISAM DEFAULT CHARSET=latin1 79DROP TABLE t1; 80CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); 81SHOW CREATE TABLE t1; 82Table Create Table 83t1 CREATE TABLE `t1` ( 84 `a` timestamp NOT NULL DEFAULT current_timestamp() 85) ENGINE=MyISAM DEFAULT CHARSET=latin1 86DROP TABLE t1; 87CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL); 88SHOW CREATE TABLE t1; 89Table Create Table 90t1 CREATE TABLE `t1` ( 91 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 92 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 93) ENGINE=MyISAM DEFAULT CHARSET=latin1 94DROP TABLE t1; 95CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL); 96SHOW CREATE TABLE t1; 97Table Create Table 98t1 CREATE TABLE `t1` ( 99 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 100 `b` timestamp NULL DEFAULT NULL 101) ENGINE=MyISAM DEFAULT CHARSET=latin1 102DROP TABLE t1; 103CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00'); 104SHOW CREATE TABLE t1; 105Table Create Table 106t1 CREATE TABLE `t1` ( 107 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 108 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 109) ENGINE=MyISAM DEFAULT CHARSET=latin1 110DROP TABLE t1; 111CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); 112SHOW CREATE TABLE t1; 113Table Create Table 114t1 CREATE TABLE `t1` ( 115 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 116 `b` timestamp NULL DEFAULT '0000-00-00 00:00:00' 117) ENGINE=MyISAM DEFAULT CHARSET=latin1 118DROP TABLE t1; 119CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); 120SHOW CREATE TABLE t1; 121Table Create Table 122t1 CREATE TABLE `t1` ( 123 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 124 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 125) ENGINE=MyISAM DEFAULT CHARSET=latin1 126DROP TABLE t1; 127CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 128SHOW CREATE TABLE t1; 129Table Create Table 130t1 CREATE TABLE `t1` ( 131 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 132 `b` timestamp NOT NULL DEFAULT current_timestamp() 133) ENGINE=MyISAM DEFAULT CHARSET=latin1 134DROP TABLE t1; 135CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); 136SHOW CREATE TABLE t1; 137Table Create Table 138t1 CREATE TABLE `t1` ( 139 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 140 `b` timestamp NULL DEFAULT current_timestamp() 141) ENGINE=MyISAM DEFAULT CHARSET=latin1 142DROP TABLE t1; 143CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); 144SHOW CREATE TABLE t1; 145Table Create Table 146t1 CREATE TABLE `t1` ( 147 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 148 `b` timestamp NOT NULL DEFAULT current_timestamp() 149) ENGINE=MyISAM DEFAULT CHARSET=latin1 150DROP TABLE t1; 151CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i; 152SHOW CREATE TABLE t1; 153Table Create Table 154t1 CREATE TABLE `t1` ( 155 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 156 `i` int(1) NOT NULL 157) ENGINE=MyISAM DEFAULT CHARSET=latin1 158CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1; 159SHOW CREATE TABLE t2; 160Table Create Table 161t2 CREATE TABLE `t2` ( 162 `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 163 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() 164) ENGINE=MyISAM DEFAULT CHARSET=latin1 165DROP TABLE t2; 166DROP TABLE t1; 167CREATE TABLE t1 (a INT); 168ALTER TABLE t1 ADD b TIMESTAMP; 169SHOW CREATE TABLE t1; 170Table Create Table 171t1 CREATE TABLE `t1` ( 172 `a` int(11) DEFAULT NULL, 173 `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() 174) ENGINE=MyISAM DEFAULT CHARSET=latin1 175DROP TABLE t1; 176# 177# MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error 178# 179SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 180SET sql_mode='ANSI,NO_ZERO_DATE'; 181CREATE TABLE t1 (a TIMESTAMP NOT NULL); 182INSERT INTO t1 VALUES (); 183SELECT * FROM t1; 184a 1852001-01-01 10:20:30 186DROP TABLE t1; 187SET sql_mode=DEFAULT; 188SET timestamp=DEFAULT; 189