diff options
Diffstat (limited to 'mysql-test/main/function_defaults.result')
| -rw-r--r-- | mysql-test/main/function_defaults.result | 3095 |
1 files changed, 3095 insertions, 0 deletions
diff --git a/mysql-test/main/function_defaults.result b/mysql-test/main/function_defaults.result new file mode 100644 index 00000000000..62422752e17 --- /dev/null +++ b/mysql-test/main/function_defaults.result @@ -0,0 +1,3095 @@ +# +# Test of function defaults for any server, including embedded. +# +# +# 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 OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP ); +CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +drop table if exists t1; +# +# 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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 current_timestamp() ON UPDATE current_timestamp() +) ENGINE=MyISAM 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 current_timestamp() +) ENGINE=MyISAM 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' ON UPDATE current_timestamp() +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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 current_timestamp() ON UPDATE current_timestamp() +) ENGINE=MyISAM 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 current_timestamp() +) ENGINE=MyISAM 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 ON UPDATE current_timestamp() +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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 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', + `e` timestamp NULL DEFAULT NULL, + `f` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `g` datetime DEFAULT current_timestamp(), + `h` datetime DEFAULT NULL ON UPDATE current_timestamp(), + `i` datetime DEFAULT NULL, + `j` datetime DEFAULT '1986-09-27 03:00:00' +) ENGINE=MyISAM 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 current_timestamp() ON UPDATE current_timestamp() +) ENGINE=MyISAM 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 current_timestamp() +) ENGINE=MyISAM 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 ON UPDATE current_timestamp() +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM DEFAULT CHARSET=latin1 +SET TIMESTAMP = 2000.876543; +INSERT INTO t2( a ) VALUES ( 3 ); +SELECT * FROM t2; +b a +1970-01-01 00:16:40 1 +1970-01-01 00:16:40 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' IGNORE 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' IGNORE 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' IGNORE 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; +@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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file1.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file1.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file2.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file2.dat" IGNORE 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=MyISAM 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 44000: 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 44000: 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 OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) ); +CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) ); +ERROR HY000: Invalid ON UPDATE clause for 'a' column +drop table if exists t1; +# +# 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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 current_timestamp(6) ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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 current_timestamp(6) +) ENGINE=MyISAM 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' ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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 current_timestamp(6) ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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 current_timestamp(6) +) ENGINE=MyISAM 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 ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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 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=MyISAM 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 current_timestamp(6) ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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 current_timestamp(6) +) ENGINE=MyISAM 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 ON UPDATE current_timestamp(6) +) ENGINE=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM DEFAULT CHARSET=latin1 +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` 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' IGNORE 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' IGNORE 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' IGNORE 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(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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file1.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file1.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file2.dat" IGNORE 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=MyISAM DEFAULT CHARSET=latin1 +LOAD DATA INFILE "file2.dat" IGNORE 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=MyISAM 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 44000: 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 44000: 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; |