[Commits] Rev 4477: MDEV-6880 Can't define CURRENT_TIMESTAMP as default value for added column in lp:~maria-captains/maria/10.0

Sergei Golubchik serg at mariadb.org
Wed Nov 12 20:43:37 EET 2014


At lp:~maria-captains/maria/10.0

------------------------------------------------------------
revno: 4477
revision-id: sergii at pisem.net-20141112184336-4ge700t4xww1e1au
parent: sergii at pisem.net-20141112153406-mwgnh363eq8z6eod
committer: Sergei Golubchik <sergii at pisem.net>
branch nick: 10.0
timestamp: Wed 2014-11-12 19:43:36 +0100
message:
  MDEV-6880 Can't define CURRENT_TIMESTAMP as default value for added column
  
  ALTER TABLE: don't fill default values per row, do it once.
  And do it in two places - for copy_data_between_tables() and for online ALTER.
  
  Also, run function_defaults test both for MyISAM and for InnoDB.
=== modified file 'mysql-test/include/function_defaults.inc'
--- a/mysql-test/include/function_defaults.inc	2014-11-11 09:40:46 +0000
+++ b/mysql-test/include/function_defaults.inc	2014-11-12 18:43:36 +0000
@@ -1050,7 +1050,7 @@ SET TIME_ZONE = "+03:00";
 --echo # 1970-01-01 03:16:40
 SET TIMESTAMP = 1000.123456;
 
-eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp) ENGINE = INNODB;
+eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp);
 
 SHOW CREATE TABLE t1;
 
@@ -1107,10 +1107,10 @@ eval CREATE TABLE t1 (
   b INT,
   ts $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp,
   PRIMARY KEY ( a, ts )
-) ENGINE = INNODB;
+);
 INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' );
 
-eval CREATE TABLE t2 ( a INT ) ENGINE = INNODB;
+eval CREATE TABLE t2 ( a INT );
 INSERT INTO t2 VALUES ( 1 );
 
 UPDATE t1 STRAIGHT_JOIN t2
@@ -1146,8 +1146,7 @@ eval ALTER TABLE t1 ADD COLUMN c4 $datet
 eval ALTER TABLE t1 ADD COLUMN c5 $datetime DEFAULT $now AFTER c4;
 eval ALTER TABLE t1 ADD COLUMN c6 $datetime DEFAULT $now ON UPDATE $now AFTER c5;
 
-SELECT * FROM t1;
-
+query_vertical SELECT * FROM t1;
 DROP TABLE t1;
 
 

=== modified file 'mysql-test/r/function_defaults.result'
--- a/mysql-test/r/function_defaults.result	2014-11-11 09:40:46 +0000
+++ b/mysql-test/r/function_defaults.result	2014-11-12 18:43:36 +0000
@@ -1434,13 +1434,13 @@ drop table t1;
 SET TIME_ZONE = "+03:00";
 # 1970-01-01 03:16:40
 SET TIMESTAMP = 1000.123456;
-CREATE TABLE t1 ( a INT, b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE = INNODB;
+CREATE TABLE t1 ( a INT, b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL,
   `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 INSERT INTO t1 ( a ) VALUES ( 1 );
 SELECT * FROM t1;
 a	b
@@ -1490,9 +1490,9 @@ a INT,
 b INT,
 ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY ( a, ts )
-) ENGINE = INNODB;
+);
 INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' );
-CREATE TABLE t2 ( a INT ) ENGINE = INNODB;
+CREATE TABLE t2 ( a INT );
 INSERT INTO t2 VALUES ( 1 );
 UPDATE t1 STRAIGHT_JOIN t2
 SET t1.b = t1.b + 1
@@ -1521,8 +1521,19 @@ ALTER TABLE t1 ADD COLUMN c4 DATETIME ON
 ALTER TABLE t1 ADD COLUMN c5 DATETIME DEFAULT NOW() AFTER c4;
 ALTER TABLE t1 ADD COLUMN c6 DATETIME DEFAULT NOW() ON UPDATE NOW() AFTER c5;
 SELECT * FROM t1;
-a1	a2	a3	a4	a5	a6	b	c1	c2	c3	c4	c5	c6
-0000-00-00 00:00:00	1970-01-01 03:16:40	1970-01-01 03:16:40	NULL	1970-01-01 03:16:40	1970-01-01 03:16:40	1	0000-00-00 00:00:00	1970-01-01 03:16:40	1970-01-01 03:16:40	NULL	1970-01-01 03:16:40	1970-01-01 03:16:40
+a1	0000-00-00 00:00:00
+a2	1970-01-01 03:16:40
+a3	1970-01-01 03:16:40
+a4	NULL
+a5	1970-01-01 03:16:40
+a6	1970-01-01 03:16:40
+b	1
+c1	0000-00-00 00:00:00
+c2	1970-01-01 03:16:40
+c3	1970-01-01 03:16:40
+c4	NULL
+c5	1970-01-01 03:16:40
+c6	1970-01-01 03:16:40
 DROP TABLE t1;
 CREATE TABLE t1 ( a TIMESTAMP  NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP, b DATETIME DEFAULT NOW() );
 INSERT INTO t1 VALUES ();
@@ -2979,13 +2990,13 @@ drop table t1;
 SET TIME_ZONE = "+03:00";
 # 1970-01-01 03:16:40
 SET TIMESTAMP = 1000.123456;
-CREATE TABLE t1 ( a INT, b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)) ENGINE = INNODB;
+CREATE TABLE t1 ( a INT, b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL,
   `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 INSERT INTO t1 ( a ) VALUES ( 1 );
 SELECT * FROM t1;
 a	b
@@ -3035,9 +3046,9 @@ a INT,
 b INT,
 ts TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
 PRIMARY KEY ( a, ts )
-) ENGINE = INNODB;
+);
 INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' );
-CREATE TABLE t2 ( a INT ) ENGINE = INNODB;
+CREATE TABLE t2 ( a INT );
 INSERT INTO t2 VALUES ( 1 );
 UPDATE t1 STRAIGHT_JOIN t2
 SET t1.b = t1.b + 1
@@ -3066,8 +3077,19 @@ ALTER TABLE t1 ADD COLUMN c4 DATETIME(6)
 ALTER TABLE t1 ADD COLUMN c5 DATETIME(6) DEFAULT NOW(6) AFTER c4;
 ALTER TABLE t1 ADD COLUMN c6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c5;
 SELECT * FROM t1;
-a1	a2	a3	a4	a5	a6	b	c1	c2	c3	c4	c5	c6
-0000-00-00 00:00:00.000000	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	NULL	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	1	0000-00-00 00:00:00.000000	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	NULL	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000
+a1	0000-00-00 00:00:00.000000
+a2	1970-01-01 03:16:40.000000
+a3	1970-01-01 03:16:40.000000
+a4	NULL
+a5	1970-01-01 03:16:40.000000
+a6	1970-01-01 03:16:40.000000
+b	1
+c1	0000-00-00 00:00:00.000000
+c2	1970-01-01 03:16:40.000000
+c3	1970-01-01 03:16:40.000000
+c4	NULL
+c5	1970-01-01 03:16:40.000000
+c6	1970-01-01 03:16:40.000000
 DROP TABLE t1;
 CREATE TABLE t1 ( a TIMESTAMP(6)  NOT NULL DEFAULT NOW(6) ON UPDATE CURRENT_TIMESTAMP(6), b DATETIME(6) DEFAULT NOW(6) );
 INSERT INTO t1 VALUES ();

=== added file 'mysql-test/r/function_defaults_innodb.result'
--- a/mysql-test/r/function_defaults_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/function_defaults_innodb.result	2014-11-12 18:43:36 +0000
@@ -0,0 +1,3116 @@
+#
+# Test of function defaults for any server, including embedded.
+#
+set default_storage_engine=innodb;
+#
+# Function defaults run 1. No microsecond precision.
+#
+SET TIME_ZONE = "+00:00";
+#
+# Test of errors for column data types that dont support function
+# defaults.
+#
+CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+#
+# Test that the default clause behaves like NOW() regarding time zones.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NULL,
+e DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+f DATETIME DEFAULT CURRENT_TIMESTAMP,
+g DATETIME ON UPDATE CURRENT_TIMESTAMP,
+h DATETIME
+);
+# 2011-09-27 14:11:08 UTC
+SET TIMESTAMP = 1317132668.654321;
+SET @old_time_zone = @@TIME_ZONE;
+SET TIME_ZONE = "+05:00";
+INSERT INTO t1( d, h ) VALUES ( NOW(), NOW() );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h
+2011-09-27 19:11:08	2011-09-27 19:11:08	0000-00-00 00:00:00	2011-09-27 19:11:08	2011-09-27 19:11:08	2011-09-27 19:11:08	NULL	2011-09-27 19:11:08
+# 1989-05-13 01:02:03
+SET TIMESTAMP = 611017323.543212;
+UPDATE t1 SET d = NOW(), h = NOW();
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h
+1989-05-13 04:02:03	2011-09-27 19:11:08	1989-05-13 04:02:03	1989-05-13 04:02:03	1989-05-13 04:02:03	2011-09-27 19:11:08	1989-05-13 04:02:03	1989-05-13 04:02:03
+SET TIME_ZONE = @old_time_zone;
+DROP TABLE t1;
+#
+# Test of several TIMESTAMP columns with different function defaults.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+f INT
+);
+# 2011-04-19 07:22:02 UTC
+SET TIMESTAMP = 1303197722.534231;
+INSERT INTO t1 ( f ) VALUES (1);
+SELECT * FROM t1;
+a	b	c	d	e	f
+2011-04-19 07:22:02	2011-04-19 07:22:02	2011-04-19 07:22:02	0000-00-00 00:00:00	0000-00-00 00:00:00	1
+# 2011-04-19 07:23:18 UTC
+SET TIMESTAMP = 1303197798.132435;
+UPDATE t1 SET f = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f
+2011-04-19 07:23:18	2011-04-19 07:23:18	2011-04-19 07:22:02	2011-04-19 07:23:18	2011-04-19 07:23:18	2
+DROP TABLE t1;
+#
+# Test of inserted values out of order.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NULL,
+f DATETIME,
+g DATETIME DEFAULT CURRENT_TIMESTAMP,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+j INT
+);
+# 2011-04-19 07:22:02 UTC
+SET TIMESTAMP = 1303197722.534231;
+INSERT INTO t1 ( j, a ) VALUES ( 1, 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j
+1	2011-04-19 07:22:02	2011-04-19 07:22:02	0000-00-00 00:00:00	NULL	NULL	2011-04-19 07:22:02	NULL	2011-04-19 07:22:02	1
+DROP TABLE t1;
+#
+# Test of ON DUPLICATE KEY UPDATE
+#
+CREATE TABLE t1 (
+a INT PRIMARY KEY,
+b INT,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+e TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+f TIMESTAMP NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+g TIMESTAMP NULL,
+h DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME DEFAULT CURRENT_TIMESTAMP,
+j DATETIME ON UPDATE CURRENT_TIMESTAMP,
+k DATETIME NULL,
+l DATETIME DEFAULT '1986-09-27 03:00:00.098765'
+);
+# 1977-12-21 23:00:00 UTC
+SET TIMESTAMP = 251593200.192837;
+INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	NULL	1977-12-21 23:00:00	1977-12-21 23:00:00	0000-00-00 00:00:00	1986-09-27 03:00:00	NULL	1977-12-21 23:00:00	1977-12-21 23:00:00	NULL	NULL	1986-09-27 03:00:00
+# 1975-05-21 23:00:00 UTC
+SET TIMESTAMP = 169945200.918273;
+INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	2	1975-05-21 23:00:00	1977-12-21 23:00:00	1975-05-21 23:00:00	1986-09-27 03:00:00	NULL	1975-05-21 23:00:00	1977-12-21 23:00:00	1975-05-21 23:00:00	NULL	1986-09-27 03:00:00
+# 1973-08-14 09:11:22 UTC
+SET TIMESTAMP = 114167482.534231;
+INSERT INTO t1(a) VALUES (2) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	2	1975-05-21 23:00:00	1977-12-21 23:00:00	1975-05-21 23:00:00	1986-09-27 03:00:00	NULL	1975-05-21 23:00:00	1977-12-21 23:00:00	1975-05-21 23:00:00	NULL	1986-09-27 03:00:00
+2	NULL	1973-08-14 09:11:22	1973-08-14 09:11:22	0000-00-00 00:00:00	1986-09-27 03:00:00	NULL	1973-08-14 09:11:22	1973-08-14 09:11:22	NULL	NULL	1986-09-27 03:00:00
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
+# 2011-04-19 07:23:18 UTC
+SET TIMESTAMP = 1303197798.945156;
+INSERT INTO t1 VALUES
+(1, 0, '2001-01-01 01:01:01.111111'),
+(2, 0, '2002-02-02 02:02:02.222222'),
+(3, 0, '2003-03-03 03:03:03.333333');
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	0	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+UPDATE t1 SET b = 2, c = c WHERE a = 2;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+INSERT INTO t1 (a) VALUES (4);
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+4	NULL	2011-04-19 07:23:18
+UPDATE t1 SET c = '2004-04-04 04:04:04.444444' WHERE a = 4;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+4	NULL	2004-04-04 04:04:04
+INSERT INTO t1 ( a ) VALUES ( 3 ), ( 5 ) ON DUPLICATE KEY UPDATE b = 3, c = c;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	3	2003-03-03 03:03:03
+4	NULL	2004-04-04 04:04:04
+5	NULL	2011-04-19 07:23:18
+INSERT INTO t1 (a, c) VALUES
+(4, '2004-04-04 00:00:00.444444'),
+(6, '2006-06-06 06:06:06.666666')
+ON DUPLICATE KEY UPDATE b = 4;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	3	2003-03-03 03:03:03
+4	4	2011-04-19 07:23:18
+5	NULL	2011-04-19 07:23:18
+6	NULL	2006-06-06 06:06:06
+DROP TABLE t1;
+#
+# Test of REPLACE INTO executed as UPDATE.
+#
+CREATE TABLE t1 (
+a INT PRIMARY KEY,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c DATETIME  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+e DATETIME  DEFAULT CURRENT_TIMESTAMP,
+f TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+g DATETIME  ON UPDATE CURRENT_TIMESTAMP,
+h TIMESTAMP NULL,
+i DATETIME
+);
+# 1970-09-21 09:11:12 UTC
+SET TIMESTAMP = 22756272.163584;
+REPLACE INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1970-09-21 09:11:12	1970-09-21 09:11:12	1970-09-21 09:11:12	1970-09-21 09:11:12	0000-00-00 00:00:00	NULL	NULL	NULL
+# 1970-11-10 14:16:17 UTC
+SET TIMESTAMP = 27094577.852954;
+REPLACE INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1970-11-10 14:16:17	1970-11-10 14:16:17	1970-11-10 14:16:17	1970-11-10 14:16:17	0000-00-00 00:00:00	NULL	NULL	NULL
+DROP TABLE t1;
+#
+# Test of insertion of NULL, DEFAULT and an empty row for DEFAULT
+# CURRENT_TIMESTAMP.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME DEFAULT CURRENT_TIMESTAMP,
+c INT
+);
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.163578;
+INSERT INTO t1 VALUES (NULL, NULL, 1), (DEFAULT, DEFAULT, 2);
+INSERT INTO t1 ( a, b, c ) VALUES (NULL, NULL, 3), (DEFAULT, DEFAULT, 4);
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41	NULL	1
+2011-04-20 09:53:41	2011-04-20 09:53:41	2
+2011-04-20 09:53:41	NULL	3
+2011-04-20 09:53:41	2011-04-20 09:53:41	4
+SET TIME_ZONE = "+03:00";
+SELECT * FROM t1;
+a	b	c
+2011-04-20 12:53:41	NULL	1
+2011-04-20 12:53:41	2011-04-20 09:53:41	2
+2011-04-20 12:53:41	NULL	3
+2011-04-20 12:53:41	2011-04-20 09:53:41	4
+SET TIME_ZONE = "+00:00";
+DROP TABLE t1;
+# 2011-04-20 07:05:39 UTC
+SET TIMESTAMP = 1303283139.195624;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT '2010-10-11 12:34:56' ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME DEFAULT '2010-10-11 12:34:56'
+);
+INSERT INTO t1          VALUES (NULL, NULL), (DEFAULT, DEFAULT);
+INSERT INTO t1 ( a, b ) VALUES (NULL, NULL), (DEFAULT, DEFAULT);
+SELECT * FROM t1;
+a	b
+2011-04-20 07:05:39	NULL
+2010-10-11 12:34:56	2010-10-11 12:34:56
+2011-04-20 07:05:39	NULL
+2010-10-11 12:34:56	2010-10-11 12:34:56
+DROP TABLE t1;
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.136952;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+e TIMESTAMP NULL,
+f DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+g DATETIME DEFAULT CURRENT_TIMESTAMP,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME NULL,
+j DATETIME DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+INSERT INTO t1 SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j
+2011-04-20 09:53:41	2011-04-20 09:53:41	0000-00-00 00:00:00	1986-09-27 03:00:00	NULL	2011-04-20 09:53:41	2011-04-20 09:53:41	NULL	NULL	1986-09-27 03:00:00
+2011-04-20 09:53:41	2011-04-20 09:53:41	2011-04-20 09:53:41	2011-04-20 09:53:41	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t1;
+#
+# Test of multiple-table UPDATE for DEFAULT CURRENT_TIMESTAMP
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME DEFAULT CURRENT_TIMESTAMP,
+c INT
+);
+INSERT INTO t1 ( c ) VALUES (1);
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41	2011-04-20 09:53:41	1
+# 2011-04-20 17:06:13 UTC
+SET TIMESTAMP = 1303311973.163587;
+UPDATE t1 t11, t1 t12 SET t11.c = 1;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41	2011-04-20 09:53:41	1
+UPDATE t1 t11, t1 t12 SET t11.c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 15:06:13	2011-04-20 09:53:41	2
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+c DATETIME  DEFAULT   CURRENT_TIMESTAMP,
+d DATETIME  ON UPDATE CURRENT_TIMESTAMP,
+e INT
+);
+CREATE TABLE t2 (
+f INT,
+g DATETIME  ON UPDATE CURRENT_TIMESTAMP,
+h DATETIME  DEFAULT   CURRENT_TIMESTAMP,
+i TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+j TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+# 1995-03-11 00:02:03 UTC
+SET TIMESTAMP = 794880123.195676;
+INSERT INTO t1 ( e ) VALUES ( 1 ), ( 2 );
+INSERT INTO t2 ( f ) VALUES ( 1 ), ( 2 );
+SELECT * FROM t1;
+a	b	c	d	e
+1995-03-11 00:02:03	0000-00-00 00:00:00	1995-03-11 00:02:03	NULL	1
+1995-03-11 00:02:03	0000-00-00 00:00:00	1995-03-11 00:02:03	NULL	2
+SELECT * FROM t2;
+f	g	h	i	j
+1	NULL	1995-03-11 00:02:03	0000-00-00 00:00:00	1995-03-11 00:02:03
+2	NULL	1995-03-11 00:02:03	0000-00-00 00:00:00	1995-03-11 00:02:03
+# 1980-12-13 02:02:01 UTC
+SET TIMESTAMP = 345520921.196755;
+UPDATE t1, t2 SET t1.e = 3, t2.f = 4;
+SELECT * FROM t1;
+a	b	c	d	e
+1995-03-11 00:02:03	1980-12-13 02:02:01	1995-03-11 00:02:03	1980-12-13 02:02:01	3
+1995-03-11 00:02:03	1980-12-13 02:02:01	1995-03-11 00:02:03	1980-12-13 02:02:01	3
+SELECT * FROM t2;
+f	g	h	i	j
+4	1980-12-13 02:02:01	1995-03-11 00:02:03	1980-12-13 02:02:01	1995-03-11 00:02:03
+4	1980-12-13 02:02:01	1995-03-11 00:02:03	1980-12-13 02:02:01	1995-03-11 00:02:03
+DROP TABLE t1, t2;
+#
+# Test of multiple table update with temporary table and on the fly.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME ON UPDATE CURRENT_TIMESTAMP,
+c INT,
+d INT
+);
+CREATE TABLE t2 (
+a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME ON UPDATE CURRENT_TIMESTAMP,
+c INT KEY,
+d INT
+);
+INSERT INTO t1 ( c ) VALUES (1), (2);
+INSERT INTO t2 ( c ) VALUES (1), (2);
+# Test of multiple table update done on the fly
+# 2011-04-20 15:06:13 UTC
+SET TIMESTAMP = 1303311973.194685;
+UPDATE t1 JOIN t2 USING ( c ) SET t2.d = 1;
+SELECT * FROM t1;
+a	b	c	d
+0000-00-00 00:00:00	NULL	1	NULL
+0000-00-00 00:00:00	NULL	2	NULL
+SELECT * FROM t2;
+a	b	c	d
+2011-04-20 15:06:13	2011-04-20 15:06:13	1	1
+2011-04-20 15:06:13	2011-04-20 15:06:13	2	1
+# Test of multiple table update done with temporary table.
+# 1979-01-15 03:02:01
+SET TIMESTAMP = 285213721.134679;
+UPDATE t1 JOIN t2 USING ( c ) SET t1.d = 1;
+SELECT * FROM t1;
+a	b	c	d
+1979-01-15 02:02:01	1979-01-15 02:02:01	1	1
+1979-01-15 02:02:01	1979-01-15 02:02:01	2	1
+SELECT * FROM t2;
+a	b	c	d
+2011-04-20 15:06:13	2011-04-20 15:06:13	1	1
+2011-04-20 15:06:13	2011-04-20 15:06:13	2	1
+DROP TABLE t1, t2;
+#
+# Test of ON UPDATE CURRENT_TIMESTAMP.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME ON UPDATE CURRENT_TIMESTAMP,
+c INT
+);
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.794613;
+INSERT INTO t1 ( c ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c
+0000-00-00 00:00:00	NULL	1
+UPDATE t1 SET c = 1;
+SELECT * FROM t1;
+a	b	c
+0000-00-00 00:00:00	NULL	1
+UPDATE t1 SET c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41	2011-04-20 09:53:41	2
+#
+# Test that ON UPDATE CURRENT_TIMESTAMP works after non-changing UPDATE.
+#
+# 2011-04-20 09:54:13 UTC
+SET TIMESTAMP = 1303293253.794613;
+UPDATE t1 SET c = 2, b = '2011-04-20 09:53:41.794613';
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41	2011-04-20 09:53:41	2
+UPDATE t1 SET c = 3;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:54:13	2011-04-20 09:54:13	3
+#
+# Test of multiple-table UPDATE for ON UPDATE CURRENT_TIMESTAMP
+#
+# 2011-04-20 15:06:13 UTC
+SET TIMESTAMP = 1303311973.534231;
+UPDATE t1 t11, t1 t12 SET t11.c = 3;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:54:13	2011-04-20 09:54:13	3
+UPDATE t1 t11, t1 t12 SET t11.c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 15:06:13	2011-04-20 15:06:13	2
+DROP TABLE t1;
+#
+# Test of a multiple-table update where only one table is updated and
+# the updated table has a primary key.
+#
+CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a) );
+INSERT INTO t1 VALUES (1, 1),(2, 2),(3, 3),(4, 4);
+CREATE TABLE t2 ( a INT, b INT );
+INSERT INTO t2 VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5);
+UPDATE t1, t2 SET t1.b = 100 WHERE t1.a = t2.a;
+SELECT * FROM t1;
+a	b
+1	100
+2	100
+3	100
+4	100
+SELECT * FROM t2;
+a	b
+1	1
+2	2
+3	3
+4	4
+5	5
+DROP TABLE t1, t2;
+#
+# Test of ALTER TABLE, reordering columns.
+#
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b INT );
+ALTER TABLE t1 MODIFY a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `a` int(11) DEFAULT NULL,
+  `c` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp NULL DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp NULL DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(), b INT, c TIMESTAMP NULL );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `b` int(11) DEFAULT NULL,
+  `c` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 MODIFY a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `c` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(), b INT, c TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY c TIMESTAMP NULL FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` timestamp NULL DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP, b INT, c TIMESTAMP NULL );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `b` int(11) DEFAULT NULL,
+  `c` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 MODIFY a TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `c` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP, b INT, c TIMESTAMP NULL );
+ALTER TABLE t1 MODIFY c TIMESTAMP NULL FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` timestamp NULL DEFAULT NULL,
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Test of ALTER TABLE, adding columns.
+#
+CREATE TABLE t1 ( a INT );
+ALTER TABLE t1 ADD COLUMN b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Test of INSERT SELECT.
+#
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+d DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+CREATE TABLE t2 (
+placeholder1 INT,
+placeholder2 INT,
+placeholder3 INT,
+placeholder4 INT,
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
+c DATETIME,
+d DATETIME
+);
+# 1977-08-16 15:30:01 UTC
+SET TIMESTAMP = 240589801.654312;
+INSERT INTO t2 (a, b, c, d) VALUES (
+'1977-08-16 15:30:01.123456',
+'1977-08-16 15:30:01.234567',
+'1977-08-16 15:30:01.345678',
+'1977-08-16 15:30:01.456789'
+);
+# 1986-09-27 01:00:00 UTC
+SET TIMESTAMP = 528166800.132435;
+INSERT INTO t1 ( a, c ) SELECT a, c FROM t2;
+SELECT * FROM t1;
+a	b	c	d
+1977-08-16 15:30:01	1986-09-27 01:00:00	1977-08-16 15:30:01	1986-09-27 01:00:00
+DROP TABLE t1, t2;
+#
+# Test of CREATE TABLE SELECT.
+#
+# We test that the columns of the source table are not used to determine
+# function defaults for the receiving table.
+#
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.657898;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+e TIMESTAMP NULL,
+f DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+g DATETIME DEFAULT CURRENT_TIMESTAMP,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME NULL,
+j DATETIME DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.164937;
+CREATE TABLE t2  SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+a
+1970-04-11 20:13:57
+CREATE TABLE t3  SELECT b FROM t1;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t3;
+b
+1970-04-11 20:13:57
+CREATE TABLE t4  SELECT c FROM t1;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t4;
+c
+0000-00-00 00:00:00
+CREATE TABLE t5  SELECT d FROM t1;
+SHOW CREATE TABLE t5;
+Table	Create Table
+t5	CREATE TABLE `t5` (
+  `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t5;
+d
+1986-09-27 03:00:00
+CREATE TABLE t6  SELECT e FROM t1;
+SHOW CREATE TABLE t6;
+Table	Create Table
+t6	CREATE TABLE `t6` (
+  `e` timestamp NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t6;
+e
+NULL
+CREATE TABLE t7  SELECT f FROM t1;
+SHOW CREATE TABLE t7;
+Table	Create Table
+t7	CREATE TABLE `t7` (
+  `f` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t7;
+f
+1970-04-11 20:13:57
+CREATE TABLE t8  SELECT g FROM t1;
+SHOW CREATE TABLE t8;
+Table	Create Table
+t8	CREATE TABLE `t8` (
+  `g` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t8;
+g
+1970-04-11 20:13:57
+CREATE TABLE t9  SELECT h FROM t1;
+SHOW CREATE TABLE t9;
+Table	Create Table
+t9	CREATE TABLE `t9` (
+  `h` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t9;
+h
+NULL
+CREATE TABLE t10 SELECT i FROM t1;
+SHOW CREATE TABLE t10;
+Table	Create Table
+t10	CREATE TABLE `t10` (
+  `i` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t10;
+i
+NULL
+CREATE TABLE t11 SELECT j FROM t1;
+SHOW CREATE TABLE t11;
+Table	Create Table
+t11	CREATE TABLE `t11` (
+  `j` datetime DEFAULT '1986-09-27 03:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t11;
+j
+1986-09-27 03:00:00
+CREATE TABLE t12 (
+k TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+l TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+m TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+n TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+o TIMESTAMP NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+p TIMESTAMP NULL,
+q DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+r DATETIME DEFAULT CURRENT_TIMESTAMP,
+s DATETIME ON UPDATE CURRENT_TIMESTAMP,
+t DATETIME NULL,
+u DATETIME DEFAULT '1986-09-27 03:00:00.098765'
+)
+SELECT * FROM t1;
+SHOW CREATE TABLE t12;
+Table	Create Table
+t12	CREATE TABLE `t12` (
+  `k` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `l` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `m` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `n` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `o` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00',
+  `p` timestamp NULL DEFAULT NULL,
+  `q` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `r` datetime DEFAULT CURRENT_TIMESTAMP,
+  `s` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  `t` datetime DEFAULT NULL,
+  `u` datetime DEFAULT '1986-09-27 03:00:00',
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00',
+  `e` timestamp NULL DEFAULT NULL,
+  `f` datetime DEFAULT NULL,
+  `g` datetime DEFAULT NULL,
+  `h` datetime DEFAULT NULL,
+  `i` datetime DEFAULT NULL,
+  `j` datetime DEFAULT '1986-09-27 03:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12;
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.164953;
+CREATE TABLE t1 (
+a DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME DEFAULT CURRENT_TIMESTAMP,
+c DATETIME ON UPDATE CURRENT_TIMESTAMP,
+d DATETIME NULL,
+e DATETIME DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+# 1971-01-31 20:13:57 UTC
+SET TIMESTAMP = 34200837.915736;
+CREATE TABLE t2 SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+a
+1970-04-11 20:13:57
+CREATE TABLE t3 SELECT b FROM t1;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `b` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t3;
+b
+1970-04-11 20:13:57
+CREATE TABLE t4 SELECT c FROM t1;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t4;
+c
+NULL
+CREATE TABLE t5 SELECT d FROM t1;
+SHOW CREATE TABLE t5;
+Table	Create Table
+t5	CREATE TABLE `t5` (
+  `d` datetime DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t5;
+d
+NULL
+CREATE TABLE t6 SELECT e FROM t1;
+SHOW CREATE TABLE t6;
+Table	Create Table
+t6	CREATE TABLE `t6` (
+  `e` datetime DEFAULT '1986-09-27 03:00:00'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t6;
+e
+1986-09-27 03:00:00
+DROP TABLE t1, t2, t3, t4, t5, t6;
+#
+# Test of a CREATE TABLE SELECT that also declared columns. In this case
+# the function default should be de-activated during the execution of the
+# CREATE TABLE statement.
+#
+# 1970-01-01 03:16:40
+SET TIMESTAMP = 1000.987654;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES ( 1 ), ( 2 );
+CREATE TABLE t2 ( b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SET TIMESTAMP = 2000.876543;
+INSERT INTO t2( a ) VALUES ( 3 );
+SELECT * FROM t2;
+b	a
+0000-00-00 00:00:00	1
+0000-00-00 00:00:00	2
+1970-01-01 00:33:20	3
+DROP TABLE t1, t2;
+#
+# Test of updating a view.
+#
+CREATE TABLE t1 ( a INT, b DATETIME DEFAULT CURRENT_TIMESTAMP );
+CREATE TABLE t2 ( a INT, b DATETIME ON UPDATE CURRENT_TIMESTAMP );
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`	latin1	latin1_swedish_ci
+CREATE VIEW v2 AS SELECT * FROM t2;
+SHOW CREATE VIEW v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2`	latin1	latin1_swedish_ci
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.348564;
+INSERT INTO v1 ( a ) VALUES ( 1 );
+INSERT INTO v2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b
+1	1971-01-31 20:13:57
+SELECT * FROM v1;
+a	b
+1	1971-01-31 20:13:57
+SELECT * FROM t2;
+a	b
+1	NULL
+SELECT * FROM v2;
+a	b
+1	NULL
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.567332;
+UPDATE v1 SET a = 2;
+UPDATE v2 SET a = 2;
+SELECT * FROM t1;
+a	b
+2	1971-01-31 20:13:57
+SELECT * FROM v1;
+a	b
+2	1971-01-31 20:13:57
+SELECT * FROM t2;
+a	b
+2	1970-04-11 20:13:57
+SELECT * FROM v2;
+a	b
+2	1970-04-11 20:13:57
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
+#
+# Test with stored procedures.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NULL,
+f DATETIME DEFAULT CURRENT_TIMESTAMP,
+g DATETIME ON UPDATE CURRENT_TIMESTAMP
+);
+CREATE PROCEDURE p1() INSERT INTO test.t1( a ) VALUES ( 1 );
+CREATE PROCEDURE p2() UPDATE t1 SET a = 2 WHERE a = 1;
+# 1971-01-31 20:13:57 UTC
+SET TIMESTAMP = 34200837.876544;
+CALL p1();
+SELECT * FROM t1;
+a	b	c	d	e	f	g
+1	1971-01-31 20:13:57	1971-01-31 20:13:57	0000-00-00 00:00:00	NULL	1971-01-31 20:13:57	NULL
+# 1970-04-11 21:13:57 UTC
+SET TIMESTAMP = 8712837.143546;
+CALL p2();
+SELECT * FROM t1;
+a	b	c	d	e	f	g
+2	1970-04-11 20:13:57	1971-01-31 20:13:57	1970-04-11 20:13:57	NULL	1971-01-31 20:13:57	1970-04-11 20:13:57
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP TABLE t1;
+#
+# Test with triggers.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NULL,
+f DATETIME,
+g DATETIME DEFAULT CURRENT_TIMESTAMP,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+CREATE TABLE t2 ( a INT );
+CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t1 ( a ) VALUES ( 1 );
+END|
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.978675;
+INSERT INTO t2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1971-01-31 20:13:57	1971-01-31 20:13:57	0000-00-00 00:00:00	NULL	NULL	1971-01-31 20:13:57	NULL	1971-01-31 20:13:57
+DROP TRIGGER t2_trg;
+CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW
+BEGIN
+UPDATE t1 SET a = 2;
+END|
+# 1970-04-11 21:13:57 UTC
+SET TIMESTAMP = 8712837.456789;
+INSERT INTO t2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+2	1970-04-11 20:13:57	1971-01-31 20:13:57	1970-04-11 20:13:57	NULL	NULL	1971-01-31 20:13:57	1970-04-11 20:13:57	1970-04-11 20:13:57
+DROP TABLE t1, t2;
+#
+# Test where the assignment target is not a column.
+#
+CREATE TABLE t1 ( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
+CREATE TABLE t2 ( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
+CREATE TABLE t3 ( a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP );
+CREATE TABLE t4 ( a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP );
+CREATE VIEW v1       AS SELECT a COLLATE latin1_german1_ci AS b FROM t1;
+CREATE VIEW v2 ( b ) AS SELECT a COLLATE latin1_german1_ci      FROM t2;
+CREATE VIEW v3       AS SELECT a COLLATE latin1_german1_ci AS b FROM t3;
+CREATE VIEW v4 ( b ) AS SELECT a COLLATE latin1_german1_ci      FROM t4;
+INSERT INTO v1 ( b ) VALUES ( '2007-10-24 00:03:34.010203' );
+SELECT a FROM t1;
+a
+2007-10-24 00:03:34
+INSERT INTO v2 ( b ) VALUES ( '2007-10-24 00:03:34.010203' );
+SELECT a FROM t2;
+a
+2007-10-24 00:03:34
+INSERT INTO t3 VALUES ();
+UPDATE v3 SET b = '2007-10-24 00:03:34.010203';
+SELECT a FROM t3;
+a
+2007-10-24 00:03:34
+INSERT INTO t4 VALUES ();
+UPDATE v4 SET b = '2007-10-24 00:03:34.010203';
+SELECT a FROM t4;
+a
+2007-10-24 00:03:34
+DROP VIEW  v1, v2, v3, v4;
+DROP TABLE t1, t2, t3, t4;
+#
+# Test of LOAD DATA/XML INFILE
+# This tests behavior of function defaults for TIMESTAMP and DATETIME
+# columns. during LOAD ... INFILE.
+# As can be seen here, a TIMESTAMP column with only ON UPDATE
+# CURRENT_TIMESTAMP will still have CURRENT_TIMESTAMP inserted on LOAD
+# ... INFILE if the value is missing. For DATETIME columns a NULL value
+# is inserted instead.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+f DATETIME,
+g DATETIME DEFAULT CURRENT_TIMESTAMP,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP,
+i DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+CREATE TABLE t2 (
+a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+c TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+e DATETIME NOT NULL,
+f DATETIME NOT NULL DEFAULT '1977-01-02 12:13:14',
+g DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
+h DATETIME ON UPDATE CURRENT_TIMESTAMP NOT NULL,
+i DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
+);
+SELECT 1 INTO OUTFILE 't3.dat' FROM dual;
+SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+INTO OUTFILE 't4.dat'
+FROM dual;
+SELECT 1, 2 INTO OUTFILE 't5.dat' FROM dual;
+# Mon Aug 1 15:11:19 2011 UTC
+SET TIMESTAMP = 1312211479.918273;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT * FROM t1;
+a	1
+b	2011-08-01 15:11:19
+c	2011-08-01 15:11:19
+d	2011-08-01 15:11:19
+e	2011-08-01 15:11:19
+f	NULL
+g	NULL
+h	NULL
+i	NULL
+LOAD DATA INFILE 't4.dat' INTO TABLE t2;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'e' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'f' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'g' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'h' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT a FROM t2;
+a
+2011-08-01 15:11:19
+SELECT b FROM t2;
+b
+2011-08-01 15:11:19
+SELECT c FROM t2;
+c
+2011-08-01 15:11:19
+SELECT d FROM t2;
+d
+2011-08-01 15:11:19
+# As shown here, supplying a NULL value to a non-nullable
+# column with no default value results in the zero date.
+SELECT e FROM t2;
+e
+0000-00-00 00:00:00
+# As shown here, supplying a NULL value to a non-nullable column with a
+# default value results in the zero date.
+SELECT f FROM t2;
+f
+0000-00-00 00:00:00
+# As shown here, supplying a NULL value to a non-nullable column with a
+# default function results in the zero date.
+SELECT g FROM t2;
+g
+0000-00-00 00:00:00
+# As shown here, supplying a NULL value to a non-nullable DATETIME ON
+# UPDATE CURRENT_TIMESTAMP column with no default value results in the
+# zero date.
+SELECT h FROM t2;
+h
+0000-00-00 00:00:00
+SELECT i FROM t2;
+i
+0000-00-00 00:00:00
+DELETE FROM t1;
+DELETE FROM t2;
+# Read t3 file into t1
+# The syntax will cause a different code path to be taken
+# (read_fixed_length()) than under the LOAD ... INTO TABLE t1 command
+# above. The code in this path is copy-pasted code from the path taken
+# under the syntax used in the previous LOAD command.
+LOAD DATA INFILE 't3.dat' INTO TABLE t1
+FIELDS TERMINATED BY '' ENCLOSED BY '';
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT b FROM t1;
+b
+2011-08-01 15:11:19
+SELECT c FROM t1;
+c
+2011-08-01 15:11:19
+SELECT d FROM t1;
+d
+2011-08-01 15:11:19
+SELECT e FROM t1;
+e
+2011-08-01 15:11:19
+# Yes, a missing field cannot be NULL using this syntax, so it will
+# zero date instead. Says a comment in read_fixed_length() : "No fields
+# specified in fields_vars list can be NULL in this format."
+# It appears to be by design. This is inconsistent with LOAD DATA INFILE
+# syntax in previous test.
+SELECT f FROM t1;
+f
+0000-00-00 00:00:00
+SELECT g FROM t1;
+g
+0000-00-00 00:00:00
+# See comment above "SELECT f FROM f1".
+SELECT h FROM t1;
+h
+0000-00-00 00:00:00
+SELECT i FROM t1;
+i
+0000-00-00 00:00:00
+DELETE FROM t1;
+LOAD DATA INFILE 't5.dat' INTO TABLE t1 ( a, @dummy );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19	2011-08-01 15:11:19	0000-00-00 00:00:00	2011-08-01 15:11:19	NULL	2011-08-01 15:11:19	NULL	2011-08-01 15:11:19
+SELECT @dummy;
+ at dummy
+2
+DELETE FROM t1;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET c = '2005-06-06 08:09:10';
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19	2005-06-06 08:09:10	0000-00-00 00:00:00	2011-08-01 15:11:19	NULL	2011-08-01 15:11:19	NULL	2011-08-01 15:11:19
+DELETE FROM t1;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET g = '2005-06-06 08:09:10';
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19	2011-08-01 15:11:19	0000-00-00 00:00:00	2011-08-01 15:11:19	NULL	2005-06-06 08:09:10	NULL	2011-08-01 15:11:19
+DELETE FROM t1;
+# Load a static XML file
+LOAD XML INFILE '../../std_data/onerow.xml' INTO TABLE t1
+ROWS IDENTIFIED BY '<row>';
+Missing tags are treated as NULL
+SELECT * FROM t1;
+a	1
+b	2011-08-01 15:11:19
+c	2011-08-01 15:11:19
+d	2011-08-01 15:11:19
+e	2011-08-01 15:11:19
+f	NULL
+g	NULL
+h	NULL
+i	NULL
+DROP TABLE t1, t2;
+#
+# Similar LOAD DATA tests in another form
+#
+# All of this test portion has been run on a pre-WL5874 trunk
+# (except that like_b and like_c didn't exist) and all result
+# differences are a bug.
+# Regarding like_b its definition is the same as b's except
+# that the constant default is replaced with a function
+# default. Our expectation is that like_b would behave
+# like b: if b is set to NULL, or set to 0000-00-00, or set to
+# its default, then the same should apply to like_b. Same for
+# like_c vs c.
+# Mon Aug 1 15:11:19 2011 UTC
+SET TIMESTAMP = 1312211479.089786;
+SELECT 1 INTO OUTFILE "file1.dat" FROM dual;
+SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+INTO OUTFILE "file2.dat" FROM dual;
+# Too short row
+CREATE TABLE t1 (
+dummy INT,
+a DATETIME NULL DEFAULT NULL,
+b DATETIME NULL DEFAULT "2011-11-18",
+like_b DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
+c DATETIME NOT NULL DEFAULT "2011-11-18",
+like_c DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NULL DEFAULT "2011-05-03" ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NOT NULL DEFAULT "2011-05-03",
+f TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+g TIMESTAMP NULL DEFAULT NULL,
+h INT NULL,
+i INT NOT NULL DEFAULT 42
+);
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime DEFAULT NULL,
+  `b` datetime DEFAULT '2011-11-18 00:00:00',
+  `like_b` datetime DEFAULT CURRENT_TIMESTAMP,
+  `c` datetime NOT NULL DEFAULT '2011-11-18 00:00:00',
+  `like_c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `d` timestamp NULL DEFAULT '2011-05-03 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `e` timestamp NOT NULL DEFAULT '2011-05-03 00:00:00',
+  `f` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file1.dat" INTO table t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+# It is strange that "like_b" gets NULL when "b" gets 0. But
+# this is consistent with how "a" gets NULL when "b" gets 0,
+# with how "g" gets NULL when "d" gets 0, and with how "h" gets
+# NULL when "i" gets 0. Looks like "DEFAULT
+# <non-NULL-constant>" is changed to 0, whereas DEFAULT NULL
+# and DEFAULT NOW are changed to NULL.
+SELECT * FROM t1;
+dummy	1
+a	NULL
+b	0000-00-00 00:00:00
+like_b	NULL
+c	0000-00-00 00:00:00
+like_c	0000-00-00 00:00:00
+d	0000-00-00 00:00:00
+e	2011-08-01 15:11:19
+f	2011-08-01 15:11:19
+g	NULL
+h	NULL
+i	0
+delete from t1;
+alter table t1
+modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime DEFAULT NULL,
+  `b` datetime DEFAULT '2011-11-18 00:00:00',
+  `like_b` datetime DEFAULT CURRENT_TIMESTAMP,
+  `c` datetime NOT NULL DEFAULT '2011-11-18 00:00:00',
+  `like_c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `d` timestamp NULL DEFAULT '2011-05-03 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `e` timestamp NOT NULL DEFAULT '2011-05-03 00:00:00',
+  `f` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file1.dat" INTO table t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT * FROM t1;
+dummy	1
+a	NULL
+b	0000-00-00 00:00:00
+like_b	NULL
+c	0000-00-00 00:00:00
+like_c	0000-00-00 00:00:00
+d	0000-00-00 00:00:00
+e	2011-08-01 15:11:19
+f	NULL
+g	NULL
+h	NULL
+i	0
+delete from t1;
+drop table t1;
+# Conclusion derived from trunk's results:
+# DATETIME DEFAULT <non-NULL-constant> (b,c) gets 0000-00-00,
+# DATETIME DEFAULT NULL (a) gets NULL,
+# TIMESTAMP NULL DEFAULT <non-NULL-constant> (d) gets 0000-00-00,
+# TIMESTAMP NULL DEFAULT NULL (g) gets NULL,
+# TIMESTAMP NULL DEFAULT NOW (f after ALTER) gets NULL,
+# TIMESTAMP NOT NULL (f before ALTER, e) gets NOW.
+### Loading NULL ###
+CREATE TABLE t1 (
+dummy INT,
+a DATETIME NULL DEFAULT NULL,
+b DATETIME NULL DEFAULT "2011-11-18",
+like_b DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
+c DATETIME NOT NULL DEFAULT "2011-11-18",
+like_c DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+d TIMESTAMP NULL DEFAULT "2011-05-03" ON UPDATE CURRENT_TIMESTAMP,
+e TIMESTAMP NOT NULL DEFAULT "2011-05-03",
+f TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+g TIMESTAMP NULL DEFAULT NULL,
+h INT NULL,
+i INT NOT NULL DEFAULT 42
+);
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime DEFAULT NULL,
+  `b` datetime DEFAULT '2011-11-18 00:00:00',
+  `like_b` datetime DEFAULT CURRENT_TIMESTAMP,
+  `c` datetime NOT NULL DEFAULT '2011-11-18 00:00:00',
+  `like_c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `d` timestamp NULL DEFAULT '2011-05-03 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `e` timestamp NOT NULL DEFAULT '2011-05-03 00:00:00',
+  `f` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file2.dat" INTO table t1;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'like_c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT * FROM t1;
+dummy	NULL
+a	NULL
+b	NULL
+like_b	NULL
+c	0000-00-00 00:00:00
+like_c	0000-00-00 00:00:00
+d	NULL
+e	2011-08-01 15:11:19
+f	2011-08-01 15:11:19
+g	NULL
+h	NULL
+i	0
+delete from t1;
+alter table t1
+modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime DEFAULT NULL,
+  `b` datetime DEFAULT '2011-11-18 00:00:00',
+  `like_b` datetime DEFAULT CURRENT_TIMESTAMP,
+  `c` datetime NOT NULL DEFAULT '2011-11-18 00:00:00',
+  `like_c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `d` timestamp NULL DEFAULT '2011-05-03 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `e` timestamp NOT NULL DEFAULT '2011-05-03 00:00:00',
+  `f` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file2.dat" INTO table t1;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'like_c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT * FROM t1;
+dummy	NULL
+a	NULL
+b	NULL
+like_b	NULL
+c	0000-00-00 00:00:00
+like_c	0000-00-00 00:00:00
+d	NULL
+e	2011-08-01 15:11:19
+f	NULL
+g	NULL
+h	NULL
+i	0
+delete from t1;
+# Conclusion derived from trunk's results:
+# DATETIME NULL (a,b) gets NULL,
+# DATETIME NOT NULL (c) gets 0000-00-00,
+# TIMESTAMP NULL (d,f,g) gets NULL,
+# TIMESTAMP NOT NULL (e) gets NOW.
+drop table t1;
+#
+# Test of updatable views with check options. The option can be violated
+# using ON UPDATE updates which is very strange as this offers a loophole
+# in this integrity check.
+#
+SET TIME_ZONE = "+03:00";
+# 1970-01-01 03:16:40
+SET TIMESTAMP = 1000.123456;
+CREATE TABLE t1 ( a INT, b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b
+1	1970-01-01 03:16:40
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE b <= '1970-01-01 03:16:40.123456'
+WITH CHECK OPTION;
+SELECT * FROM v1;
+a	b
+1	1970-01-01 03:16:40
+# 1970-01-01 03:33:20
+SET TIMESTAMP = 2000.000234;
+UPDATE v1 SET a = 2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM t1;
+a	b
+1	1970-01-01 03:16:40
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT '1973-08-14 09:11:22.089786' ON UPDATE CURRENT_TIMESTAMP,
+c INT KEY
+);
+# 1973-08-14 09:11:22 UTC
+SET TIMESTAMP = 114167482.534231;
+INSERT INTO t1 ( c ) VALUES ( 1 );
+CREATE VIEW v1 AS
+SELECT *
+FROM t1
+WHERE a >= '1973-08-14 09:11:22'
+WITH LOCAL CHECK OPTION;
+SELECT * FROM v1;
+a	c
+1973-08-14 09:11:22	1
+SET TIMESTAMP = 1.126789;
+INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM v1;
+a	c
+1973-08-14 09:11:22	1
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# Bug 13095459 - MULTI-TABLE UPDATE MODIFIES A ROW TWICE
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+PRIMARY KEY ( a, ts )
+);
+INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' );
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES ( 1 );
+UPDATE t1 STRAIGHT_JOIN t2
+SET t1.b = t1.b + 1
+WHERE t1.a = 1 AND t1.ts >= '2000-09-28 00:00:00';
+SELECT b FROM t1;
+b
+1
+DROP TABLE t1, t2;
+#
+# Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT
+# CURRENT_TIMESTAMP INSERTS ZERO
+#
+SET timestamp = 1000;
+CREATE TABLE t1 ( b INT );
+INSERT INTO t1 VALUES (1);
+ALTER TABLE t1 ADD COLUMN a6 DATETIME DEFAULT NOW() ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a5 DATETIME DEFAULT NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a4 DATETIME ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a3 TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a2 TIMESTAMP NOT NULL DEFAULT NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a1 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW() AFTER b;
+ALTER TABLE t1 ADD COLUMN c2 TIMESTAMP NOT NULL DEFAULT NOW() AFTER c1;
+ALTER TABLE t1 ADD COLUMN c3 TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW() AFTER c2;
+ALTER TABLE t1 ADD COLUMN c4 DATETIME ON UPDATE NOW() AFTER c3;
+ALTER TABLE t1 ADD COLUMN c5 DATETIME DEFAULT NOW() AFTER c4;
+ALTER TABLE t1 ADD COLUMN c6 DATETIME DEFAULT NOW() ON UPDATE NOW() AFTER c5;
+SELECT * FROM t1;
+a1	0000-00-00 00:00:00
+a2	1970-01-01 03:16:40
+a3	1970-01-01 03:16:40
+a4	NULL
+a5	1970-01-01 03:16:40
+a6	1970-01-01 03:16:40
+b	1
+c1	0000-00-00 00:00:00
+c2	1970-01-01 03:16:40
+c3	1970-01-01 03:16:40
+c4	NULL
+c5	1970-01-01 03:16:40
+c6	1970-01-01 03:16:40
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP  NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP, b DATETIME DEFAULT NOW() );
+INSERT INTO t1 VALUES ();
+SET timestamp = 1000000000;
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3);
+SELECT * FROM t1;
+a	b
+1970-01-01 03:16:40.000	1970-01-01 03:16:40.000
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP NOT NULL DEFAULT '1999-12-01 11:22:33' ON UPDATE CURRENT_TIMESTAMP,
+b DATETIME DEFAULT '1999-12-01 11:22:33'
+);
+INSERT INTO t1 VALUES ();
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP DEFAULT NOW();
+ALTER TABLE t1 MODIFY COLUMN b DATETIME DEFAULT NOW();
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a	b
+1999-12-01 11:22:33	1999-12-01 11:22:33
+2001-09-09 04:46:40	2001-09-09 04:46:40
+DROP TABLE t1;
+#
+# Function defaults run 2. Six digits scale on seconds precision.
+#
+SET TIME_ZONE = "+00:00";
+#
+# Test of errors for column data types that dont support function
+# defaults.
+#
+CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) );
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) );
+ERROR HY000: Invalid ON UPDATE clause for 'a' column
+#
+# Test that the default clause behaves like NOW() regarding time zones.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NULL,
+e DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+f DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+g DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+h DATETIME(6)
+);
+# 2011-09-27 14:11:08 UTC
+SET TIMESTAMP = 1317132668.654321;
+SET @old_time_zone = @@TIME_ZONE;
+SET TIME_ZONE = "+05:00";
+INSERT INTO t1( d, h ) VALUES ( NOW(6), NOW(6) );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h
+2011-09-27 19:11:08.654321	2011-09-27 19:11:08.654321	0000-00-00 00:00:00.000000	2011-09-27 19:11:08.654321	2011-09-27 19:11:08.654321	2011-09-27 19:11:08.654321	NULL	2011-09-27 19:11:08.654321
+# 1989-05-13 01:02:03
+SET TIMESTAMP = 611017323.543212;
+UPDATE t1 SET d = NOW(6), h = NOW(6);
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h
+1989-05-13 04:02:03.543212	2011-09-27 19:11:08.654321	1989-05-13 04:02:03.543212	1989-05-13 04:02:03.543212	1989-05-13 04:02:03.543212	2011-09-27 19:11:08.654321	1989-05-13 04:02:03.543212	1989-05-13 04:02:03.543212
+SET TIME_ZONE = @old_time_zone;
+DROP TABLE t1;
+#
+# Test of several TIMESTAMP columns with different function defaults.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+f INT
+);
+# 2011-04-19 07:22:02 UTC
+SET TIMESTAMP = 1303197722.534231;
+INSERT INTO t1 ( f ) VALUES (1);
+SELECT * FROM t1;
+a	b	c	d	e	f
+2011-04-19 07:22:02.534231	2011-04-19 07:22:02.534231	2011-04-19 07:22:02.534231	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	1
+# 2011-04-19 07:23:18 UTC
+SET TIMESTAMP = 1303197798.132435;
+UPDATE t1 SET f = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f
+2011-04-19 07:23:18.132435	2011-04-19 07:23:18.132435	2011-04-19 07:22:02.534231	2011-04-19 07:23:18.132435	2011-04-19 07:23:18.132435	2
+DROP TABLE t1;
+#
+# Test of inserted values out of order.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NULL,
+f DATETIME(6),
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+j INT
+);
+# 2011-04-19 07:22:02 UTC
+SET TIMESTAMP = 1303197722.534231;
+INSERT INTO t1 ( j, a ) VALUES ( 1, 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j
+1	2011-04-19 07:22:02.534231	2011-04-19 07:22:02.534231	0000-00-00 00:00:00.000000	NULL	NULL	2011-04-19 07:22:02.534231	NULL	2011-04-19 07:22:02.534231	1
+DROP TABLE t1;
+#
+# Test of ON DUPLICATE KEY UPDATE
+#
+CREATE TABLE t1 (
+a INT PRIMARY KEY,
+b INT,
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+f TIMESTAMP(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+g TIMESTAMP(6) NULL,
+h DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+j DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+k DATETIME(6) NULL,
+l DATETIME(6) DEFAULT '1986-09-27 03:00:00.098765'
+);
+# 1977-12-21 23:00:00 UTC
+SET TIMESTAMP = 251593200.192837;
+INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	NULL	1977-12-21 23:00:00.192837	1977-12-21 23:00:00.192837	0000-00-00 00:00:00.000000	1986-09-27 03:00:00.098765	NULL	1977-12-21 23:00:00.192837	1977-12-21 23:00:00.192837	NULL	NULL	1986-09-27 03:00:00.098765
+# 1975-05-21 23:00:00 UTC
+SET TIMESTAMP = 169945200.918273;
+INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	2	1975-05-21 23:00:00.918273	1977-12-21 23:00:00.192837	1975-05-21 23:00:00.918273	1986-09-27 03:00:00.098765	NULL	1975-05-21 23:00:00.918273	1977-12-21 23:00:00.192837	1975-05-21 23:00:00.918273	NULL	1986-09-27 03:00:00.098765
+# 1973-08-14 09:11:22 UTC
+SET TIMESTAMP = 114167482.534231;
+INSERT INTO t1(a) VALUES (2) ON DUPLICATE KEY UPDATE b = 2;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j	k	l
+1	2	1975-05-21 23:00:00.918273	1977-12-21 23:00:00.192837	1975-05-21 23:00:00.918273	1986-09-27 03:00:00.098765	NULL	1975-05-21 23:00:00.918273	1977-12-21 23:00:00.192837	1975-05-21 23:00:00.918273	NULL	1986-09-27 03:00:00.098765
+2	NULL	1973-08-14 09:11:22.534231	1973-08-14 09:11:22.534231	0000-00-00 00:00:00.000000	1986-09-27 03:00:00.098765	NULL	1973-08-14 09:11:22.534231	1973-08-14 09:11:22.534231	NULL	NULL	1986-09-27 03:00:00.098765
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) );
+# 2011-04-19 07:23:18 UTC
+SET TIMESTAMP = 1303197798.945156;
+INSERT INTO t1 VALUES
+(1, 0, '2001-01-01 01:01:01.111111'),
+(2, 0, '2002-02-02 02:02:02.222222'),
+(3, 0, '2003-03-03 03:03:03.333333');
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	0	2002-02-02 02:02:02.222222
+3	0	2003-03-03 03:03:03.333333
+UPDATE t1 SET b = 2, c = c WHERE a = 2;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	2	2002-02-02 02:02:02.222222
+3	0	2003-03-03 03:03:03.333333
+INSERT INTO t1 (a) VALUES (4);
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	2	2002-02-02 02:02:02.222222
+3	0	2003-03-03 03:03:03.333333
+4	NULL	2011-04-19 07:23:18.945156
+UPDATE t1 SET c = '2004-04-04 04:04:04.444444' WHERE a = 4;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	2	2002-02-02 02:02:02.222222
+3	0	2003-03-03 03:03:03.333333
+4	NULL	2004-04-04 04:04:04.444444
+INSERT INTO t1 ( a ) VALUES ( 3 ), ( 5 ) ON DUPLICATE KEY UPDATE b = 3, c = c;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	2	2002-02-02 02:02:02.222222
+3	3	2003-03-03 03:03:03.333333
+4	NULL	2004-04-04 04:04:04.444444
+5	NULL	2011-04-19 07:23:18.945156
+INSERT INTO t1 (a, c) VALUES
+(4, '2004-04-04 00:00:00.444444'),
+(6, '2006-06-06 06:06:06.666666')
+ON DUPLICATE KEY UPDATE b = 4;
+SELECT * FROM t1;
+a	b	c
+1	0	2001-01-01 01:01:01.111111
+2	2	2002-02-02 02:02:02.222222
+3	3	2003-03-03 03:03:03.333333
+4	4	2011-04-19 07:23:18.945156
+5	NULL	2011-04-19 07:23:18.945156
+6	NULL	2006-06-06 06:06:06.666666
+DROP TABLE t1;
+#
+# Test of REPLACE INTO executed as UPDATE.
+#
+CREATE TABLE t1 (
+a INT PRIMARY KEY,
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c DATETIME(6)  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+e DATETIME(6)  DEFAULT CURRENT_TIMESTAMP(6),
+f TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+g DATETIME(6)  ON UPDATE CURRENT_TIMESTAMP(6),
+h TIMESTAMP(6) NULL,
+i DATETIME(6)
+);
+# 1970-09-21 09:11:12 UTC
+SET TIMESTAMP = 22756272.163584;
+REPLACE INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1970-09-21 09:11:12.163584	1970-09-21 09:11:12.163584	1970-09-21 09:11:12.163584	1970-09-21 09:11:12.163584	0000-00-00 00:00:00.000000	NULL	NULL	NULL
+# 1970-11-10 14:16:17 UTC
+SET TIMESTAMP = 27094577.852954;
+REPLACE INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1970-11-10 14:16:17.852954	1970-11-10 14:16:17.852954	1970-11-10 14:16:17.852954	1970-11-10 14:16:17.852954	0000-00-00 00:00:00.000000	NULL	NULL	NULL
+DROP TABLE t1;
+#
+# Test of insertion of NULL, DEFAULT and an empty row for DEFAULT
+# CURRENT_TIMESTAMP.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+c INT
+);
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.163578;
+INSERT INTO t1 VALUES (NULL, NULL, 1), (DEFAULT, DEFAULT, 2);
+INSERT INTO t1 ( a, b, c ) VALUES (NULL, NULL, 3), (DEFAULT, DEFAULT, 4);
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41.163578	NULL	1
+2011-04-20 09:53:41.163578	2011-04-20 09:53:41.163578	2
+2011-04-20 09:53:41.163578	NULL	3
+2011-04-20 09:53:41.163578	2011-04-20 09:53:41.163578	4
+SET TIME_ZONE = "+03:00";
+SELECT * FROM t1;
+a	b	c
+2011-04-20 12:53:41.163578	NULL	1
+2011-04-20 12:53:41.163578	2011-04-20 09:53:41.163578	2
+2011-04-20 12:53:41.163578	NULL	3
+2011-04-20 12:53:41.163578	2011-04-20 09:53:41.163578	4
+SET TIME_ZONE = "+00:00";
+DROP TABLE t1;
+# 2011-04-20 07:05:39 UTC
+SET TIMESTAMP = 1303283139.195624;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT '2010-10-11 12:34:56' ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) DEFAULT '2010-10-11 12:34:56'
+);
+INSERT INTO t1          VALUES (NULL, NULL), (DEFAULT, DEFAULT);
+INSERT INTO t1 ( a, b ) VALUES (NULL, NULL), (DEFAULT, DEFAULT);
+SELECT * FROM t1;
+a	b
+2011-04-20 07:05:39.195624	NULL
+2010-10-11 12:34:56.000000	2010-10-11 12:34:56.000000
+2011-04-20 07:05:39.195624	NULL
+2010-10-11 12:34:56.000000	2010-10-11 12:34:56.000000
+DROP TABLE t1;
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.136952;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+e TIMESTAMP(6) NULL,
+f DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) NULL,
+j DATETIME(6) DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+INSERT INTO t1 SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL;
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i	j
+2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	0000-00-00 00:00:00.000000	1986-09-27 03:00:00.098765	NULL	2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	NULL	NULL	1986-09-27 03:00:00.098765
+2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t1;
+#
+# Test of multiple-table UPDATE for DEFAULT CURRENT_TIMESTAMP
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+c INT
+);
+INSERT INTO t1 ( c ) VALUES (1);
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	1
+# 2011-04-20 17:06:13 UTC
+SET TIMESTAMP = 1303311973.163587;
+UPDATE t1 t11, t1 t12 SET t11.c = 1;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41.136952	2011-04-20 09:53:41.136952	1
+UPDATE t1 t11, t1 t12 SET t11.c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 15:06:13.163587	2011-04-20 09:53:41.136952	2
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+c DATETIME(6)  DEFAULT   CURRENT_TIMESTAMP(6),
+d DATETIME(6)  ON UPDATE CURRENT_TIMESTAMP(6),
+e INT
+);
+CREATE TABLE t2 (
+f INT,
+g DATETIME(6)  ON UPDATE CURRENT_TIMESTAMP(6),
+h DATETIME(6)  DEFAULT   CURRENT_TIMESTAMP(6),
+i TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+j TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
+);
+# 1995-03-11 00:02:03 UTC
+SET TIMESTAMP = 794880123.195676;
+INSERT INTO t1 ( e ) VALUES ( 1 ), ( 2 );
+INSERT INTO t2 ( f ) VALUES ( 1 ), ( 2 );
+SELECT * FROM t1;
+a	b	c	d	e
+1995-03-11 00:02:03.195676	0000-00-00 00:00:00.000000	1995-03-11 00:02:03.195676	NULL	1
+1995-03-11 00:02:03.195676	0000-00-00 00:00:00.000000	1995-03-11 00:02:03.195676	NULL	2
+SELECT * FROM t2;
+f	g	h	i	j
+1	NULL	1995-03-11 00:02:03.195676	0000-00-00 00:00:00.000000	1995-03-11 00:02:03.195676
+2	NULL	1995-03-11 00:02:03.195676	0000-00-00 00:00:00.000000	1995-03-11 00:02:03.195676
+# 1980-12-13 02:02:01 UTC
+SET TIMESTAMP = 345520921.196755;
+UPDATE t1, t2 SET t1.e = 3, t2.f = 4;
+SELECT * FROM t1;
+a	b	c	d	e
+1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	3
+1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	3
+SELECT * FROM t2;
+f	g	h	i	j
+4	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676
+4	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676	1980-12-13 02:02:01.196755	1995-03-11 00:02:03.195676
+DROP TABLE t1, t2;
+#
+# Test of multiple table update with temporary table and on the fly.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c INT,
+d INT
+);
+CREATE TABLE t2 (
+a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c INT KEY,
+d INT
+);
+INSERT INTO t1 ( c ) VALUES (1), (2);
+INSERT INTO t2 ( c ) VALUES (1), (2);
+# Test of multiple table update done on the fly
+# 2011-04-20 15:06:13 UTC
+SET TIMESTAMP = 1303311973.194685;
+UPDATE t1 JOIN t2 USING ( c ) SET t2.d = 1;
+SELECT * FROM t1;
+a	b	c	d
+0000-00-00 00:00:00.000000	NULL	1	NULL
+0000-00-00 00:00:00.000000	NULL	2	NULL
+SELECT * FROM t2;
+a	b	c	d
+2011-04-20 15:06:13.194685	2011-04-20 15:06:13.194685	1	1
+2011-04-20 15:06:13.194685	2011-04-20 15:06:13.194685	2	1
+# Test of multiple table update done with temporary table.
+# 1979-01-15 03:02:01
+SET TIMESTAMP = 285213721.134679;
+UPDATE t1 JOIN t2 USING ( c ) SET t1.d = 1;
+SELECT * FROM t1;
+a	b	c	d
+1979-01-15 02:02:01.134679	1979-01-15 02:02:01.134679	1	1
+1979-01-15 02:02:01.134679	1979-01-15 02:02:01.134679	2	1
+SELECT * FROM t2;
+a	b	c	d
+2011-04-20 15:06:13.194685	2011-04-20 15:06:13.194685	1	1
+2011-04-20 15:06:13.194685	2011-04-20 15:06:13.194685	2	1
+DROP TABLE t1, t2;
+#
+# Test of ON UPDATE CURRENT_TIMESTAMP.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c INT
+);
+# 2011-04-20 09:53:41 UTC
+SET TIMESTAMP = 1303293221.794613;
+INSERT INTO t1 ( c ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c
+0000-00-00 00:00:00.000000	NULL	1
+UPDATE t1 SET c = 1;
+SELECT * FROM t1;
+a	b	c
+0000-00-00 00:00:00.000000	NULL	1
+UPDATE t1 SET c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41.794613	2011-04-20 09:53:41.794613	2
+#
+# Test that ON UPDATE CURRENT_TIMESTAMP works after non-changing UPDATE.
+#
+# 2011-04-20 09:54:13 UTC
+SET TIMESTAMP = 1303293253.794613;
+UPDATE t1 SET c = 2, b = '2011-04-20 09:53:41.794613';
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:53:41.794613	2011-04-20 09:53:41.794613	2
+UPDATE t1 SET c = 3;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:54:13.794613	2011-04-20 09:54:13.794613	3
+#
+# Test of multiple-table UPDATE for ON UPDATE CURRENT_TIMESTAMP
+#
+# 2011-04-20 15:06:13 UTC
+SET TIMESTAMP = 1303311973.534231;
+UPDATE t1 t11, t1 t12 SET t11.c = 3;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 09:54:13.794613	2011-04-20 09:54:13.794613	3
+UPDATE t1 t11, t1 t12 SET t11.c = 2;
+SELECT * FROM t1;
+a	b	c
+2011-04-20 15:06:13.534231	2011-04-20 15:06:13.534231	2
+DROP TABLE t1;
+#
+# Test of a multiple-table update where only one table is updated and
+# the updated table has a primary key.
+#
+CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a) );
+INSERT INTO t1 VALUES (1, 1),(2, 2),(3, 3),(4, 4);
+CREATE TABLE t2 ( a INT, b INT );
+INSERT INTO t2 VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5);
+UPDATE t1, t2 SET t1.b = 100 WHERE t1.a = t2.a;
+SELECT * FROM t1;
+a	b
+1	100
+2	100
+3	100
+4	100
+SELECT * FROM t2;
+a	b
+1	1
+2	2
+3	3
+4	4
+5	5
+DROP TABLE t1, t2;
+#
+# Test of ALTER TABLE, reordering columns.
+#
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), b INT );
+ALTER TABLE t1 MODIFY a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), c TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `a` int(11) DEFAULT NULL,
+  `c` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), b TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp(6) NULL DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), b TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` timestamp(6) NULL DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(6), b INT, c TIMESTAMP(6) NULL );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `b` int(11) DEFAULT NULL,
+  `c` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 MODIFY a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `c` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(6), b INT, c TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY c TIMESTAMP(6) NULL FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` timestamp(6) NULL DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE CURRENT_TIMESTAMP(6), b INT, c TIMESTAMP(6) NULL );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `b` int(11) DEFAULT NULL,
+  `c` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 MODIFY a TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE CURRENT_TIMESTAMP(6) AFTER b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `c` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE CURRENT_TIMESTAMP(6), b INT, c TIMESTAMP(6) NULL );
+ALTER TABLE t1 MODIFY c TIMESTAMP(6) NULL FIRST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` timestamp(6) NULL DEFAULT NULL,
+  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Test of ALTER TABLE, adding columns.
+#
+CREATE TABLE t1 ( a INT );
+ALTER TABLE t1 ADD COLUMN b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Test of INSERT SELECT.
+#
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+d DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+);
+CREATE TABLE t2 (
+placeholder1 INT,
+placeholder2 INT,
+placeholder3 INT,
+placeholder4 INT,
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00',
+c DATETIME(6),
+d DATETIME(6)
+);
+# 1977-08-16 15:30:01 UTC
+SET TIMESTAMP = 240589801.654312;
+INSERT INTO t2 (a, b, c, d) VALUES (
+'1977-08-16 15:30:01.123456',
+'1977-08-16 15:30:01.234567',
+'1977-08-16 15:30:01.345678',
+'1977-08-16 15:30:01.456789'
+);
+# 1986-09-27 01:00:00 UTC
+SET TIMESTAMP = 528166800.132435;
+INSERT INTO t1 ( a, c ) SELECT a, c FROM t2;
+SELECT * FROM t1;
+a	b	c	d
+1977-08-16 15:30:01.123456	1986-09-27 01:00:00.132435	1977-08-16 15:30:01.345678	1986-09-27 01:00:00.132435
+DROP TABLE t1, t2;
+#
+# Test of CREATE TABLE SELECT.
+#
+# We test that the columns of the source table are not used to determine
+# function defaults for the receiving table.
+#
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.657898;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+e TIMESTAMP(6) NULL,
+f DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) NULL,
+j DATETIME(6) DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.164937;
+CREATE TABLE t2  SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+a
+1970-04-11 20:13:57.657897
+CREATE TABLE t3  SELECT b FROM t1;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t3;
+b
+1970-04-11 20:13:57.657897
+CREATE TABLE t4  SELECT c FROM t1;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t4;
+c
+0000-00-00 00:00:00.000000
+CREATE TABLE t5  SELECT d FROM t1;
+SHOW CREATE TABLE t5;
+Table	Create Table
+t5	CREATE TABLE `t5` (
+  `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t5;
+d
+1986-09-27 03:00:00.098765
+CREATE TABLE t6  SELECT e FROM t1;
+SHOW CREATE TABLE t6;
+Table	Create Table
+t6	CREATE TABLE `t6` (
+  `e` timestamp(6) NULL DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t6;
+e
+NULL
+CREATE TABLE t7  SELECT f FROM t1;
+SHOW CREATE TABLE t7;
+Table	Create Table
+t7	CREATE TABLE `t7` (
+  `f` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t7;
+f
+1970-04-11 20:13:57.657897
+CREATE TABLE t8  SELECT g FROM t1;
+SHOW CREATE TABLE t8;
+Table	Create Table
+t8	CREATE TABLE `t8` (
+  `g` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t8;
+g
+1970-04-11 20:13:57.657897
+CREATE TABLE t9  SELECT h FROM t1;
+SHOW CREATE TABLE t9;
+Table	Create Table
+t9	CREATE TABLE `t9` (
+  `h` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t9;
+h
+NULL
+CREATE TABLE t10 SELECT i FROM t1;
+SHOW CREATE TABLE t10;
+Table	Create Table
+t10	CREATE TABLE `t10` (
+  `i` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t10;
+i
+NULL
+CREATE TABLE t11 SELECT j FROM t1;
+SHOW CREATE TABLE t11;
+Table	Create Table
+t11	CREATE TABLE `t11` (
+  `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t11;
+j
+1986-09-27 03:00:00.098765
+CREATE TABLE t12 (
+k TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+l TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+m TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+n TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+o TIMESTAMP(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+p TIMESTAMP(6) NULL,
+q DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+r DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+s DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+t DATETIME(6) NULL,
+u DATETIME(6) DEFAULT '1986-09-27 03:00:00.098765'
+)
+SELECT * FROM t1;
+SHOW CREATE TABLE t12;
+Table	Create Table
+t12	CREATE TABLE `t12` (
+  `k` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `l` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `m` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `n` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `o` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+  `p` timestamp(6) NULL DEFAULT NULL,
+  `q` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `r` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
+  `s` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
+  `t` datetime(6) DEFAULT NULL,
+  `u` datetime(6) DEFAULT '1986-09-27 03:00:00.098765',
+  `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',
+  `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
+  `e` timestamp(6) NULL DEFAULT NULL,
+  `f` datetime(6) DEFAULT NULL,
+  `g` datetime(6) DEFAULT NULL,
+  `h` datetime(6) DEFAULT NULL,
+  `i` datetime(6) DEFAULT NULL,
+  `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12;
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.164953;
+CREATE TABLE t1 (
+a DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+c DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+d DATETIME(6) NULL,
+e DATETIME(6) DEFAULT '1986-09-27 03:00:00.098765'
+);
+INSERT INTO t1 VALUES ();
+# 1971-01-31 20:13:57 UTC
+SET TIMESTAMP = 34200837.915736;
+CREATE TABLE t2 SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+a
+1970-04-11 20:13:57.164953
+CREATE TABLE t3 SELECT b FROM t1;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `b` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t3;
+b
+1970-04-11 20:13:57.164953
+CREATE TABLE t4 SELECT c FROM t1;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t4;
+c
+NULL
+CREATE TABLE t5 SELECT d FROM t1;
+SHOW CREATE TABLE t5;
+Table	Create Table
+t5	CREATE TABLE `t5` (
+  `d` datetime(6) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t5;
+d
+NULL
+CREATE TABLE t6 SELECT e FROM t1;
+SHOW CREATE TABLE t6;
+Table	Create Table
+t6	CREATE TABLE `t6` (
+  `e` datetime(6) DEFAULT '1986-09-27 03:00:00.098765'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM t6;
+e
+1986-09-27 03:00:00.098765
+DROP TABLE t1, t2, t3, t4, t5, t6;
+#
+# Test of a CREATE TABLE SELECT that also declared columns. In this case
+# the function default should be de-activated during the execution of the
+# CREATE TABLE statement.
+#
+# 1970-01-01 03:16:40
+SET TIMESTAMP = 1000.987654;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES ( 1 ), ( 2 );
+CREATE TABLE t2 ( b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)) SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+  `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SET TIMESTAMP = 2000.876543;
+INSERT INTO t2( a ) VALUES ( 3 );
+SELECT * FROM t2;
+b	a
+0000-00-00 00:00:00.000000	1
+0000-00-00 00:00:00.000000	2
+1970-01-01 00:33:20.876543	3
+DROP TABLE t1, t2;
+#
+# Test of updating a view.
+#
+CREATE TABLE t1 ( a INT, b DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE TABLE t2 ( a INT, b DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`	latin1	latin1_swedish_ci
+CREATE VIEW v2 AS SELECT * FROM t2;
+SHOW CREATE VIEW v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2`	latin1	latin1_swedish_ci
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.348564;
+INSERT INTO v1 ( a ) VALUES ( 1 );
+INSERT INTO v2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b
+1	1971-01-31 20:13:57.348564
+SELECT * FROM v1;
+a	b
+1	1971-01-31 20:13:57.348564
+SELECT * FROM t2;
+a	b
+1	NULL
+SELECT * FROM v2;
+a	b
+1	NULL
+# 1970-04-11 20:13:57 UTC
+SET TIMESTAMP = 8712837.567332;
+UPDATE v1 SET a = 2;
+UPDATE v2 SET a = 2;
+SELECT * FROM t1;
+a	b
+2	1971-01-31 20:13:57.348564
+SELECT * FROM v1;
+a	b
+2	1971-01-31 20:13:57.348564
+SELECT * FROM t2;
+a	b
+2	1970-04-11 20:13:57.567332
+SELECT * FROM v2;
+a	b
+2	1970-04-11 20:13:57.567332
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
+#
+# Test with stored procedures.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NULL,
+f DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+g DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6)
+);
+CREATE PROCEDURE p1() INSERT INTO test.t1( a ) VALUES ( 1 );
+CREATE PROCEDURE p2() UPDATE t1 SET a = 2 WHERE a = 1;
+# 1971-01-31 20:13:57 UTC
+SET TIMESTAMP = 34200837.876544;
+CALL p1();
+SELECT * FROM t1;
+a	b	c	d	e	f	g
+1	1971-01-31 20:13:57.876544	1971-01-31 20:13:57.876544	0000-00-00 00:00:00.000000	NULL	1971-01-31 20:13:57.876544	NULL
+# 1970-04-11 21:13:57 UTC
+SET TIMESTAMP = 8712837.143546;
+CALL p2();
+SELECT * FROM t1;
+a	b	c	d	e	f	g
+2	1970-04-11 20:13:57.143546	1971-01-31 20:13:57.876544	1970-04-11 20:13:57.143546	NULL	1971-01-31 20:13:57.876544	1970-04-11 20:13:57.143546
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP TABLE t1;
+#
+# Test with triggers.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NULL,
+f DATETIME(6),
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+);
+CREATE TABLE t2 ( a INT );
+CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t1 ( a ) VALUES ( 1 );
+END|
+# 1971-01-31 21:13:57 UTC
+SET TIMESTAMP = 34200837.978675;
+INSERT INTO t2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	1971-01-31 20:13:57.978675	1971-01-31 20:13:57.978675	0000-00-00 00:00:00.000000	NULL	NULL	1971-01-31 20:13:57.978675	NULL	1971-01-31 20:13:57.978675
+DROP TRIGGER t2_trg;
+CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW
+BEGIN
+UPDATE t1 SET a = 2;
+END|
+# 1970-04-11 21:13:57 UTC
+SET TIMESTAMP = 8712837.456789;
+INSERT INTO t2 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+2	1970-04-11 20:13:57.456789	1971-01-31 20:13:57.978675	1970-04-11 20:13:57.456789	NULL	NULL	1971-01-31 20:13:57.978675	1970-04-11 20:13:57.456789	1970-04-11 20:13:57.456789
+DROP TABLE t1, t2;
+#
+# Test where the assignment target is not a column.
+#
+CREATE TABLE t1 ( a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE TABLE t2 ( a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE TABLE t3 ( a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE TABLE t4 ( a TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE VIEW v1       AS SELECT a COLLATE latin1_german1_ci AS b FROM t1;
+CREATE VIEW v2 ( b ) AS SELECT a COLLATE latin1_german1_ci      FROM t2;
+CREATE VIEW v3       AS SELECT a COLLATE latin1_german1_ci AS b FROM t3;
+CREATE VIEW v4 ( b ) AS SELECT a COLLATE latin1_german1_ci      FROM t4;
+INSERT INTO v1 ( b ) VALUES ( '2007-10-24 00:03:34.010203' );
+SELECT a FROM t1;
+a
+2007-10-24 00:03:34.010203
+INSERT INTO v2 ( b ) VALUES ( '2007-10-24 00:03:34.010203' );
+SELECT a FROM t2;
+a
+2007-10-24 00:03:34.010203
+INSERT INTO t3 VALUES ();
+UPDATE v3 SET b = '2007-10-24 00:03:34.010203';
+SELECT a FROM t3;
+a
+2007-10-24 00:03:34.010203
+INSERT INTO t4 VALUES ();
+UPDATE v4 SET b = '2007-10-24 00:03:34.010203';
+SELECT a FROM t4;
+a
+2007-10-24 00:03:34.010203
+DROP VIEW  v1, v2, v3, v4;
+DROP TABLE t1, t2, t3, t4;
+#
+# Test of LOAD DATA/XML INFILE
+# This tests behavior of function defaults for TIMESTAMP and DATETIME
+# columns. during LOAD ... INFILE.
+# As can be seen here, a TIMESTAMP column with only ON UPDATE
+# CURRENT_TIMESTAMP will still have CURRENT_TIMESTAMP inserted on LOAD
+# ... INFILE if the value is missing. For DATETIME columns a NULL value
+# is inserted instead.
+#
+CREATE TABLE t1 (
+a INT,
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+f DATETIME(6),
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
+i DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+);
+CREATE TABLE t2 (
+a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+c TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+e DATETIME(6) NOT NULL,
+f DATETIME(6) NOT NULL DEFAULT '1977-01-02 12:13:14',
+g DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
+h DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL,
+i DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL
+);
+SELECT 1 INTO OUTFILE 't3.dat' FROM dual;
+SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+INTO OUTFILE 't4.dat'
+FROM dual;
+SELECT 1, 2 INTO OUTFILE 't5.dat' FROM dual;
+# Mon Aug 1 15:11:19 2011 UTC
+SET TIMESTAMP = 1312211479.918273;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT * FROM t1;
+a	1
+b	2011-08-01 15:11:19.918273
+c	2011-08-01 15:11:19.918273
+d	2011-08-01 15:11:19.918273
+e	2011-08-01 15:11:19.918273
+f	NULL
+g	NULL
+h	NULL
+i	NULL
+LOAD DATA INFILE 't4.dat' INTO TABLE t2;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'e' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'f' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'g' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'h' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT a FROM t2;
+a
+2011-08-01 15:11:19.918273
+SELECT b FROM t2;
+b
+2011-08-01 15:11:19.918273
+SELECT c FROM t2;
+c
+2011-08-01 15:11:19.918273
+SELECT d FROM t2;
+d
+2011-08-01 15:11:19.918273
+# As shown here, supplying a NULL value to a non-nullable
+# column with no default value results in the zero date.
+SELECT e FROM t2;
+e
+0000-00-00 00:00:00.000000
+# As shown here, supplying a NULL value to a non-nullable column with a
+# default value results in the zero date.
+SELECT f FROM t2;
+f
+0000-00-00 00:00:00.000000
+# As shown here, supplying a NULL value to a non-nullable column with a
+# default function results in the zero date.
+SELECT g FROM t2;
+g
+0000-00-00 00:00:00.000000
+# As shown here, supplying a NULL value to a non-nullable DATETIME ON
+# UPDATE CURRENT_TIMESTAMP column with no default value results in the
+# zero date.
+SELECT h FROM t2;
+h
+0000-00-00 00:00:00.000000
+SELECT i FROM t2;
+i
+0000-00-00 00:00:00.000000
+DELETE FROM t1;
+DELETE FROM t2;
+# Read t3 file into t1
+# The syntax will cause a different code path to be taken
+# (read_fixed_length()) than under the LOAD ... INTO TABLE t1 command
+# above. The code in this path is copy-pasted code from the path taken
+# under the syntax used in the previous LOAD command.
+LOAD DATA INFILE 't3.dat' INTO TABLE t1
+FIELDS TERMINATED BY '' ENCLOSED BY '';
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT b FROM t1;
+b
+2011-08-01 15:11:19.918273
+SELECT c FROM t1;
+c
+2011-08-01 15:11:19.918273
+SELECT d FROM t1;
+d
+2011-08-01 15:11:19.918273
+SELECT e FROM t1;
+e
+2011-08-01 15:11:19.918273
+# Yes, a missing field cannot be NULL using this syntax, so it will
+# zero date instead. Says a comment in read_fixed_length() : "No fields
+# specified in fields_vars list can be NULL in this format."
+# It appears to be by design. This is inconsistent with LOAD DATA INFILE
+# syntax in previous test.
+SELECT f FROM t1;
+f
+0000-00-00 00:00:00.000000
+SELECT g FROM t1;
+g
+0000-00-00 00:00:00.000000
+# See comment above "SELECT f FROM f1".
+SELECT h FROM t1;
+h
+0000-00-00 00:00:00.000000
+SELECT i FROM t1;
+i
+0000-00-00 00:00:00.000000
+DELETE FROM t1;
+LOAD DATA INFILE 't5.dat' INTO TABLE t1 ( a, @dummy );
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19.918273	2011-08-01 15:11:19.918273	0000-00-00 00:00:00.000000	2011-08-01 15:11:19.918273	NULL	2011-08-01 15:11:19.918273	NULL	2011-08-01 15:11:19.918273
+SELECT @dummy;
+ at dummy
+2
+DELETE FROM t1;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET c = '2005-06-06 08:09:10';
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19.918273	2005-06-06 08:09:10.000000	0000-00-00 00:00:00.000000	2011-08-01 15:11:19.918273	NULL	2011-08-01 15:11:19.918273	NULL	2011-08-01 15:11:19.918273
+DELETE FROM t1;
+LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET g = '2005-06-06 08:09:10';
+SELECT * FROM t1;
+a	b	c	d	e	f	g	h	i
+1	2011-08-01 15:11:19.918273	2011-08-01 15:11:19.918273	0000-00-00 00:00:00.000000	2011-08-01 15:11:19.918273	NULL	2005-06-06 08:09:10.000000	NULL	2011-08-01 15:11:19.918273
+DELETE FROM t1;
+# Load a static XML file
+LOAD XML INFILE '../../std_data/onerow.xml' INTO TABLE t1
+ROWS IDENTIFIED BY '<row>';
+Missing tags are treated as NULL
+SELECT * FROM t1;
+a	1
+b	2011-08-01 15:11:19.918273
+c	2011-08-01 15:11:19.918273
+d	2011-08-01 15:11:19.918273
+e	2011-08-01 15:11:19.918273
+f	NULL
+g	NULL
+h	NULL
+i	NULL
+DROP TABLE t1, t2;
+#
+# Similar LOAD DATA tests in another form
+#
+# All of this test portion has been run on a pre-WL5874 trunk
+# (except that like_b and like_c didn't exist) and all result
+# differences are a bug.
+# Regarding like_b its definition is the same as b's except
+# that the constant default is replaced with a function
+# default. Our expectation is that like_b would behave
+# like b: if b is set to NULL, or set to 0000-00-00, or set to
+# its default, then the same should apply to like_b. Same for
+# like_c vs c.
+# Mon Aug 1 15:11:19 2011 UTC
+SET TIMESTAMP = 1312211479.089786;
+SELECT 1 INTO OUTFILE "file1.dat" FROM dual;
+SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+INTO OUTFILE "file2.dat" FROM dual;
+# Too short row
+CREATE TABLE t1 (
+dummy INT,
+a DATETIME(6) NULL DEFAULT NULL,
+b DATETIME(6) NULL DEFAULT "2011-11-18",
+like_b DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
+c DATETIME(6) NOT NULL DEFAULT "2011-11-18",
+like_c DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NULL DEFAULT "2011-05-03" ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NOT NULL DEFAULT "2011-05-03",
+f TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+g TIMESTAMP(6) NULL DEFAULT NULL,
+h INT NULL,
+i INT NOT NULL DEFAULT 42
+);
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime(6) DEFAULT NULL,
+  `b` datetime(6) DEFAULT '2011-11-18 00:00:00.000000',
+  `like_b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
+  `c` datetime(6) NOT NULL DEFAULT '2011-11-18 00:00:00.000000',
+  `like_c` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `d` timestamp(6) NULL DEFAULT '2011-05-03 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `e` timestamp(6) NOT NULL DEFAULT '2011-05-03 00:00:00.000000',
+  `f` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `g` timestamp(6) NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file1.dat" INTO table t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+# It is strange that "like_b" gets NULL when "b" gets 0. But
+# this is consistent with how "a" gets NULL when "b" gets 0,
+# with how "g" gets NULL when "d" gets 0, and with how "h" gets
+# NULL when "i" gets 0. Looks like "DEFAULT
+# <non-NULL-constant>" is changed to 0, whereas DEFAULT NULL
+# and DEFAULT NOW are changed to NULL.
+SELECT * FROM t1;
+dummy	1
+a	NULL
+b	0000-00-00 00:00:00.000000
+like_b	NULL
+c	0000-00-00 00:00:00.000000
+like_c	0000-00-00 00:00:00.000000
+d	0000-00-00 00:00:00.000000
+e	2011-08-01 15:11:19.089786
+f	2011-08-01 15:11:19.089786
+g	NULL
+h	NULL
+i	0
+delete from t1;
+alter table t1
+modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime(6) DEFAULT NULL,
+  `b` datetime(6) DEFAULT '2011-11-18 00:00:00.000000',
+  `like_b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
+  `c` datetime(6) NOT NULL DEFAULT '2011-11-18 00:00:00.000000',
+  `like_c` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `d` timestamp(6) NULL DEFAULT '2011-05-03 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `e` timestamp(6) NOT NULL DEFAULT '2011-05-03 00:00:00.000000',
+  `f` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp(6) NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file1.dat" INTO table t1;
+Warnings:
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+Warning	1261	Row 1 doesn't contain data for all columns
+SELECT * FROM t1;
+dummy	1
+a	NULL
+b	0000-00-00 00:00:00.000000
+like_b	NULL
+c	0000-00-00 00:00:00.000000
+like_c	0000-00-00 00:00:00.000000
+d	0000-00-00 00:00:00.000000
+e	2011-08-01 15:11:19.089786
+f	NULL
+g	NULL
+h	NULL
+i	0
+delete from t1;
+drop table t1;
+# Conclusion derived from trunk's results:
+# DATETIME DEFAULT <non-NULL-constant> (b,c) gets 0000-00-00,
+# DATETIME DEFAULT NULL (a) gets NULL,
+# TIMESTAMP NULL DEFAULT <non-NULL-constant> (d) gets 0000-00-00,
+# TIMESTAMP NULL DEFAULT NULL (g) gets NULL,
+# TIMESTAMP NULL DEFAULT NOW (f after ALTER) gets NULL,
+# TIMESTAMP NOT NULL (f before ALTER, e) gets NOW.
+### Loading NULL ###
+CREATE TABLE t1 (
+dummy INT,
+a DATETIME(6) NULL DEFAULT NULL,
+b DATETIME(6) NULL DEFAULT "2011-11-18",
+like_b DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
+c DATETIME(6) NOT NULL DEFAULT "2011-11-18",
+like_c DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+d TIMESTAMP(6) NULL DEFAULT "2011-05-03" ON UPDATE CURRENT_TIMESTAMP(6),
+e TIMESTAMP(6) NOT NULL DEFAULT "2011-05-03",
+f TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+g TIMESTAMP(6) NULL DEFAULT NULL,
+h INT NULL,
+i INT NOT NULL DEFAULT 42
+);
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime(6) DEFAULT NULL,
+  `b` datetime(6) DEFAULT '2011-11-18 00:00:00.000000',
+  `like_b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
+  `c` datetime(6) NOT NULL DEFAULT '2011-11-18 00:00:00.000000',
+  `like_c` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `d` timestamp(6) NULL DEFAULT '2011-05-03 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `e` timestamp(6) NOT NULL DEFAULT '2011-05-03 00:00:00.000000',
+  `f` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `g` timestamp(6) NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file2.dat" INTO table t1;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'like_c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT * FROM t1;
+dummy	NULL
+a	NULL
+b	NULL
+like_b	NULL
+c	0000-00-00 00:00:00.000000
+like_c	0000-00-00 00:00:00.000000
+d	NULL
+e	2011-08-01 15:11:19.089786
+f	2011-08-01 15:11:19.089786
+g	NULL
+h	NULL
+i	0
+delete from t1;
+alter table t1
+modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
+# There is no promotion
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `dummy` int(11) DEFAULT NULL,
+  `a` datetime(6) DEFAULT NULL,
+  `b` datetime(6) DEFAULT '2011-11-18 00:00:00.000000',
+  `like_b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
+  `c` datetime(6) NOT NULL DEFAULT '2011-11-18 00:00:00.000000',
+  `like_c` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+  `d` timestamp(6) NULL DEFAULT '2011-05-03 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6),
+  `e` timestamp(6) NOT NULL DEFAULT '2011-05-03 00:00:00.000000',
+  `f` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `g` timestamp(6) NULL DEFAULT NULL,
+  `h` int(11) DEFAULT NULL,
+  `i` int(11) NOT NULL DEFAULT '42'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+LOAD DATA INFILE "file2.dat" INTO table t1;
+Warnings:
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'like_c' at row 1
+Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'i' at row 1
+SELECT * FROM t1;
+dummy	NULL
+a	NULL
+b	NULL
+like_b	NULL
+c	0000-00-00 00:00:00.000000
+like_c	0000-00-00 00:00:00.000000
+d	NULL
+e	2011-08-01 15:11:19.089786
+f	NULL
+g	NULL
+h	NULL
+i	0
+delete from t1;
+# Conclusion derived from trunk's results:
+# DATETIME NULL (a,b) gets NULL,
+# DATETIME NOT NULL (c) gets 0000-00-00,
+# TIMESTAMP NULL (d,f,g) gets NULL,
+# TIMESTAMP NOT NULL (e) gets NOW.
+drop table t1;
+#
+# Test of updatable views with check options. The option can be violated
+# using ON UPDATE updates which is very strange as this offers a loophole
+# in this integrity check.
+#
+SET TIME_ZONE = "+03:00";
+# 1970-01-01 03:16:40
+SET TIMESTAMP = 1000.123456;
+CREATE TABLE t1 ( a INT, b TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 ( a ) VALUES ( 1 );
+SELECT * FROM t1;
+a	b
+1	1970-01-01 03:16:40.123456
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE b <= '1970-01-01 03:16:40.123456'
+WITH CHECK OPTION;
+SELECT * FROM v1;
+a	b
+1	1970-01-01 03:16:40.123456
+# 1970-01-01 03:33:20
+SET TIMESTAMP = 2000.000234;
+UPDATE v1 SET a = 2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM t1;
+a	b
+1	1970-01-01 03:16:40.123456
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT '1973-08-14 09:11:22.089786' ON UPDATE CURRENT_TIMESTAMP(6),
+c INT KEY
+);
+# 1973-08-14 09:11:22 UTC
+SET TIMESTAMP = 114167482.534231;
+INSERT INTO t1 ( c ) VALUES ( 1 );
+CREATE VIEW v1 AS
+SELECT *
+FROM t1
+WHERE a >= '1973-08-14 09:11:22'
+WITH LOCAL CHECK OPTION;
+SELECT * FROM v1;
+a	c
+1973-08-14 09:11:22.089786	1
+SET TIMESTAMP = 1.126789;
+INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM v1;
+a	c
+1973-08-14 09:11:22.089786	1
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# Bug 13095459 - MULTI-TABLE UPDATE MODIFIES A ROW TWICE
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+ts TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
+PRIMARY KEY ( a, ts )
+);
+INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' );
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES ( 1 );
+UPDATE t1 STRAIGHT_JOIN t2
+SET t1.b = t1.b + 1
+WHERE t1.a = 1 AND t1.ts >= '2000-09-28 00:00:00';
+SELECT b FROM t1;
+b
+1
+DROP TABLE t1, t2;
+#
+# Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT
+# CURRENT_TIMESTAMP INSERTS ZERO
+#
+SET timestamp = 1000;
+CREATE TABLE t1 ( b INT );
+INSERT INTO t1 VALUES (1);
+ALTER TABLE t1 ADD COLUMN a6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a5 DATETIME(6) DEFAULT NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a4 DATETIME(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a3 TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a2 TIMESTAMP(6) NOT NULL DEFAULT NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a1 TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW(6) AFTER b;
+ALTER TABLE t1 ADD COLUMN c2 TIMESTAMP(6) NOT NULL DEFAULT NOW(6) AFTER c1;
+ALTER TABLE t1 ADD COLUMN c3 TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c2;
+ALTER TABLE t1 ADD COLUMN c4 DATETIME(6) ON UPDATE NOW(6) AFTER c3;
+ALTER TABLE t1 ADD COLUMN c5 DATETIME(6) DEFAULT NOW(6) AFTER c4;
+ALTER TABLE t1 ADD COLUMN c6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c5;
+SELECT * FROM t1;
+a1	0000-00-00 00:00:00.000000
+a2	1970-01-01 03:16:40.000000
+a3	1970-01-01 03:16:40.000000
+a4	NULL
+a5	1970-01-01 03:16:40.000000
+a6	1970-01-01 03:16:40.000000
+b	1
+c1	0000-00-00 00:00:00.000000
+c2	1970-01-01 03:16:40.000000
+c3	1970-01-01 03:16:40.000000
+c4	NULL
+c5	1970-01-01 03:16:40.000000
+c6	1970-01-01 03:16:40.000000
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6)  NOT NULL DEFAULT NOW(6) ON UPDATE CURRENT_TIMESTAMP(6), b DATETIME(6) DEFAULT NOW(6) );
+INSERT INTO t1 VALUES ();
+SET timestamp = 1000000000;
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3);
+SELECT * FROM t1;
+a	b
+1970-01-01 03:16:40.000	1970-01-01 03:16:40.000
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP(6) NOT NULL DEFAULT '1999-12-01 11:22:33' ON UPDATE CURRENT_TIMESTAMP(6),
+b DATETIME(6) DEFAULT '1999-12-01 11:22:33'
+);
+INSERT INTO t1 VALUES ();
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(6) DEFAULT NOW(6);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(6) DEFAULT NOW(6);
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a	b
+1999-12-01 11:22:33.000000	1999-12-01 11:22:33.000000
+2001-09-09 04:46:40.000000	2001-09-09 04:46:40.000000
+DROP TABLE t1;

=== modified file 'mysql-test/t/function_defaults.test'
--- a/mysql-test/t/function_defaults.test	2012-10-17 12:43:56 +0000
+++ b/mysql-test/t/function_defaults.test	2014-11-12 18:43:36 +0000
@@ -2,8 +2,6 @@
 --echo # Test of function defaults for any server, including embedded.
 --echo #
 
---source include/have_innodb.inc
-
 --echo #
 --echo # Function defaults run 1. No microsecond precision.
 --echo #

=== added file 'mysql-test/t/function_defaults_innodb.test'
--- a/mysql-test/t/function_defaults_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/function_defaults_innodb.test	2014-11-12 18:43:36 +0000
@@ -0,0 +1,24 @@
+--echo #
+--echo # Test of function defaults for any server, including embedded.
+--echo #
+
+--source include/have_innodb.inc
+set default_storage_engine=innodb;
+
+--echo #
+--echo # Function defaults run 1. No microsecond precision.
+--echo #
+let $current_timestamp=CURRENT_TIMESTAMP;
+let $now=NOW();
+let $timestamp=TIMESTAMP;
+let $datetime=DATETIME;
+source 'include/function_defaults.inc';
+
+--echo #
+--echo # Function defaults run 2. Six digits scale on seconds precision.
+--echo #
+let $current_timestamp=CURRENT_TIMESTAMP(6);
+let $now=NOW(6);
+let $timestamp=TIMESTAMP(6);
+let $datetime=DATETIME(6);
+source 'include/function_defaults.inc';

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2014-11-03 16:47:37 +0000
+++ b/sql/sql_table.cc	2014-11-12 18:43:36 +0000
@@ -8723,6 +8723,9 @@ bool mysql_alter_table(THD *thd,char *ne
     */
     altered_table->column_bitmaps_set_no_signal(&altered_table->s->all_set,
                                                 &altered_table->s->all_set);
+    restore_record(altered_table, s->default_values); // Create empty record
+    if (altered_table->default_field && altered_table->update_default_fields())
+      goto err_new_table_cleanup;
 
     // Ask storage engine whether to use copy or in-place
     enum_alter_inplace_result inplace_supported=
@@ -9365,14 +9368,14 @@ copy_data_between_tables(THD *thd, TABLE
   to->use_all_columns();
   to->mark_virtual_columns_for_write(TRUE);
   if (init_read_record(&info, thd, from, (SQL_SELECT *) 0, 1, 1, FALSE))
-  {
-    error= 1;
     goto err;
-  }
+
   if (ignore && !alter_ctx->fk_error_if_delete_row)
     to->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
   thd->get_stmt_da()->reset_current_row_for_warning();
   restore_record(to, s->default_values);        // Create empty record
+  if (to->default_field && to->update_default_fields())
+    goto err;
 
   thd->progress.max_counter= from->file->records();
   time_to_report_progress= MY_HOW_OFTEN_TO_WRITE/10;
@@ -9415,11 +9418,6 @@ copy_data_between_tables(THD *thd, TABLE
     prev_insert_id= to->file->next_insert_id;
     if (to->vfield)
       update_virtual_fields(thd, to, VCOL_UPDATE_FOR_WRITE);
-    if (to->default_field && to->update_default_fields())
-    {
-      error= 1;
-      break;
-    }
     if (thd->is_error())
     {
       error= 1;

=== modified file 'storage/innobase/handler/handler0alter.cc'
--- a/storage/innobase/handler/handler0alter.cc	2014-10-04 13:26:04 +0000
+++ b/storage/innobase/handler/handler0alter.cc	2014-11-12 18:43:36 +0000
@@ -2460,7 +2460,7 @@ innobase_build_col_map(
 
 		innobase_build_col_map_add(
 			heap, dtuple_get_nth_field(add_cols, i),
-			altered_table->s->field[sql_idx],
+			altered_table->field[sql_idx],
 			dict_table_is_comp(new_table));
 found_col:
 		i++;

=== modified file 'storage/xtradb/handler/handler0alter.cc'
--- a/storage/xtradb/handler/handler0alter.cc	2014-10-04 13:26:04 +0000
+++ b/storage/xtradb/handler/handler0alter.cc	2014-11-12 18:43:36 +0000
@@ -2461,7 +2461,7 @@ innobase_build_col_map(
 
 		innobase_build_col_map_add(
 			heap, dtuple_get_nth_field(add_cols, i),
-			altered_table->s->field[sql_idx],
+			altered_table->field[sql_idx],
 			dict_table_is_comp(new_table));
 found_col:
 		i++;



More information about the commits mailing list