diff options
Diffstat (limited to 'mysql-test/r/function_defaults.result')
| -rw-r--r-- | mysql-test/r/function_defaults.result | 3095 |
1 files changed, 0 insertions, 3095 deletions
diff --git a/mysql-test/r/function_defaults.result b/mysql-test/r/function_defaults.result deleted file mode 100644 index 62422752e17..00000000000 --- a/mysql-test/r/function_defaults.result +++ /dev/null @@ -1,3095 +0,0 @@ -# -# 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; |