#
# Test of function defaults for any server.
# We run the same tests with different settings for better coverage.
#
#
# Function defaults run 2. Six digits scale on seconds
# precision. InnoDB.
#
set default_storage_engine=innodb;
SET TIME_ZONE = "+00:00";
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
#
# 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);
Warnings:
Warning	1048	Column 'a' cannot be null
INSERT INTO t1 ( a, b, c ) VALUES (NULL, NULL, 3), (DEFAULT, DEFAULT, 4);
Warnings:
Warning	1048	Column 'a' cannot be null
SELECT * FROM t1;
a	b	c
0000-00-00 00:00:00.000000	NULL	1
2011-04-20 09:53:41.163578	2011-04-20 09:53:41.163578	2
0000-00-00 00:00:00.000000	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
0000-00-00 00:00:00.000000	NULL	1
2011-04-20 12:53:41.163578	2011-04-20 09:53:41.163578	2
0000-00-00 00:00:00.000000	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);
Warnings:
Warning	1048	Column 'a' cannot be null
INSERT INTO t1 ( a, b ) VALUES (NULL, NULL), (DEFAULT, DEFAULT);
Warnings:
Warning	1048	Column 'a' cannot be null
SELECT * FROM t1;
a	b
0000-00-00 00:00:00.000000	NULL
2010-10-11 12:34:56.000000	2010-10-11 12:34:56.000000
0000-00-00 00:00:00.000000	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;
Warnings:
Warning	1048	Column 'a' cannot be null
Warning	1048	Column 'b' cannot be null
Warning	1048	Column 'c' cannot be null
Warning	1048	Column 'd' cannot be 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
0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	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 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 = 2;
SELECT * FROM t1;
a	b	c
2011-04-20 09:53:41.794613	2011-04-20 09:53:41.794613	2
UPDATE t1 t11, t1 t12 SET t11.c = 3;
SELECT * FROM t1;
a	b	c
2011-04-20 15:06:13.534231	2011-04-20 15:06:13.534231	3
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 DEFAULT NULL,
  `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL,
  `c` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL,
  `c` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL,
  `c` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 DEFAULT NULL,
  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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 CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2;
a
1970-04-11 20:13:57.657898
CREATE TABLE t3  SELECT b FROM t1;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t3;
b
1970-04-11 20:13:57.657898
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' ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t7;
f
1970-04-11 20:13:57.657898
CREATE TABLE t8  SELECT g FROM t1;
SHOW CREATE TABLE t8;
Table	Create Table
t8	CREATE TABLE `t8` (
  `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t8;
g
1970-04-11 20:13:57.657898
CREATE TABLE t9  SELECT h FROM t1;
SHOW CREATE TABLE t9;
Table	Create Table
t9	CREATE TABLE `t9` (
  `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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.000000' ON UPDATE CURRENT_TIMESTAMP(6),
  `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765',
  `e` timestamp(6) NULL DEFAULT NULL,
  `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
  `i` datetime(6) DEFAULT NULL,
  `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# No function default for non-column expressions:
CREATE TABLE t13  SELECT COALESCE(a,a) FROM t1;
SHOW CREATE TABLE t13;
Table	Create Table
t13	CREATE TABLE `t13` (
  `COALESCE(a,a)` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t13;
COALESCE(a,a)
1970-04-11 20:13:57.657898
DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13;
# 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 CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t6;
e
1986-09-27 03:00:00.098765
# Test CREATE TABLE LIKE
CREATE TABLE t7 LIKE t1;
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `a` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `c` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
  `d` datetime(6) DEFAULT NULL,
  `e` datetime(6) DEFAULT '1986-09-27 03:00:00.098765'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1, t2, t3, t4, t5, t6, t7;
#
# Test of a CREATE TABLE SELECT that also declared columns. In this case
# the function default for them should be 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 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SET TIMESTAMP = 2000.876543;
INSERT INTO t2( a ) VALUES ( 3 );
SELECT * FROM t2;
b	a
1970-01-01 00:16:40.987654	1
1970-01-01 00:16:40.987654	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`	utf8mb4	utf8mb4_0900_ai_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`	utf8mb4	utf8mb4_0900_ai_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;
@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 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 DEFAULT NULL,
  `i` int NOT NULL DEFAULT '42'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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 DEFAULT NULL,
  `i` int NOT NULL DEFAULT '42'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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 DEFAULT NULL,
  `i` int NOT NULL DEFAULT '42'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 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 DEFAULT NULL,
  `i` int NOT NULL DEFAULT '42'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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.
# Test case provides coverage for Bug#29834066 "DEFAULT EXPRESSION CAN
# BREAK VIEW CHECK OPTION".
#
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 DEFAULT NULL,
  `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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),(2);
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	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
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	2	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
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;
SET sql_mode = default;