diff options
Diffstat (limited to 'mysql-test/include')
-rw-r--r-- | mysql-test/include/function_defaults.inc | 1166 | ||||
-rw-r--r-- | mysql-test/include/function_defaults_notembedded.inc | 94 | ||||
-rw-r--r-- | mysql-test/include/have_debug_sync.inc | 8 | ||||
-rw-r--r-- | mysql-test/include/have_innodb.combinations | 9 | ||||
-rw-r--r-- | mysql-test/include/wait_for_binlog_checkpoint.inc | 53 |
5 files changed, 1325 insertions, 5 deletions
diff --git a/mysql-test/include/function_defaults.inc b/mysql-test/include/function_defaults.inc new file mode 100644 index 00000000000..e588c82df1b --- /dev/null +++ b/mysql-test/include/function_defaults.inc @@ -0,0 +1,1166 @@ +SET TIME_ZONE = "+00:00"; + +--echo # +--echo # Test of errors for column data types that dont support function +--echo # defaults. +--echo # +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a BIT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a TINYINT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a SMALLINT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a MEDIUMINT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a INT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a BIGINT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a FLOAT DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a DECIMAL DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a DATE DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a TIME DEFAULT $current_timestamp ); +--error ER_INVALID_DEFAULT +eval CREATE TABLE t1( a YEAR DEFAULT $current_timestamp ); + +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a BIT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a TINYINT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a SMALLINT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a MEDIUMINT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a INT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a BIGINT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a FLOAT ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a DECIMAL ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a DATE ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a TIME ON UPDATE $current_timestamp ); +--error ER_INVALID_ON_UPDATE +eval CREATE TABLE t1( a YEAR ON UPDATE $current_timestamp ); + +--echo # +--echo # Test that the default clause behaves like NOW() regarding time zones. +--echo # +eval 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 +); + +--echo # 2011-09-27 14:11:08 UTC +SET TIMESTAMP = 1317132668.654321; +SET @old_time_zone = @@TIME_ZONE; +SET TIME_ZONE = "+05:00"; + +eval INSERT INTO t1( d, h ) VALUES ( $now, $now ); +SELECT * FROM t1; + +--echo # 1989-05-13 01:02:03 +SET TIMESTAMP = 611017323.543212; +eval UPDATE t1 SET d = $now, h = $now; +SELECT * FROM t1; + +SET TIME_ZONE = @old_time_zone; +DROP TABLE t1; + +--echo # +--echo # Test of several TIMESTAMP columns with different function defaults. +--echo # +eval 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 +); + +--echo # 2011-04-19 07:22:02 UTC +SET TIMESTAMP = 1303197722.534231; + +INSERT INTO t1 ( f ) VALUES (1); +SELECT * FROM t1; + +--echo # 2011-04-19 07:23:18 UTC +SET TIMESTAMP = 1303197798.132435; + +UPDATE t1 SET f = 2; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Test of inserted values out of order. +--echo # +eval 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 +); + +--echo # 2011-04-19 07:22:02 UTC +SET TIMESTAMP = 1303197722.534231; + +INSERT INTO t1 ( j, a ) VALUES ( 1, 1 ); +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Test of ON DUPLICATE KEY UPDATE +--echo # +eval 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' +); + +--echo # 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; + +--echo # 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; + +--echo # 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; + +DROP TABLE t1; + +eval CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); + +--echo # 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; + +UPDATE t1 SET b = 2, c = c WHERE a = 2; +SELECT * FROM t1; + +INSERT INTO t1 (a) VALUES (4); +SELECT * FROM t1; + +UPDATE t1 SET c = '2004-04-04 04:04:04.444444' WHERE a = 4; +SELECT * FROM t1; + +INSERT INTO t1 ( a ) VALUES ( 3 ), ( 5 ) ON DUPLICATE KEY UPDATE b = 3, c = c; +SELECT * FROM t1; + +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; + +DROP TABLE t1; + + +--echo # +--echo # Test of REPLACE INTO executed as UPDATE. +--echo # +eval 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 +); + +--echo # 1970-09-21 09:11:12 UTC +SET TIMESTAMP = 22756272.163584; + +REPLACE INTO t1 ( a ) VALUES ( 1 ); +SELECT * FROM t1; + +--echo # 1970-11-10 14:16:17 UTC +SET TIMESTAMP = 27094577.852954; + + +REPLACE INTO t1 ( a ) VALUES ( 1 ); +SELECT * FROM t1; + +DROP TABLE t1; + + +--echo # +--echo # Test of insertion of NULL, DEFAULT and an empty row for DEFAULT +--echo # CURRENT_TIMESTAMP. +--echo # +eval CREATE TABLE t1 ( + a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, + b $datetime DEFAULT $current_timestamp, + c INT +); + +--echo # 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; + +SET TIME_ZONE = "+03:00"; +SELECT * FROM t1; +SET TIME_ZONE = "+00:00"; + +DROP TABLE t1; + +--echo # 2011-04-20 07:05:39 UTC +SET TIMESTAMP = 1303283139.195624; +eval 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; + +DROP TABLE t1; + +--echo # 2011-04-20 09:53:41 UTC +SET TIMESTAMP = 1303293221.136952; + +eval 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; + +DROP TABLE t1; + +--echo # +--echo # Test of multiple-table UPDATE for DEFAULT CURRENT_TIMESTAMP +--echo # +eval 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; + +--echo # 2011-04-20 17:06:13 UTC +SET TIMESTAMP = 1303311973.163587; + +UPDATE t1 t11, t1 t12 SET t11.c = 1; +SELECT * FROM t1; + +UPDATE t1 t11, t1 t12 SET t11.c = 2; + +SELECT * FROM t1; + +DROP TABLE t1; + +eval 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 +); + +eval 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 +); + +--echo # 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; +SELECT * FROM t2; + +--echo # 1980-12-13 02:02:01 UTC +SET TIMESTAMP = 345520921.196755; + +UPDATE t1, t2 SET t1.e = 3, t2.f = 4; + +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # Test of multiple table update with temporary table and on the fly. +--echo # +eval 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 +); + +eval 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); + +--echo # Test of multiple table update done on the fly +--echo # 2011-04-20 15:06:13 UTC +SET TIMESTAMP = 1303311973.194685; +UPDATE t1 JOIN t2 USING ( c ) SET t2.d = 1; +SELECT * FROM t1; +SELECT * FROM t2; + +--echo # Test of multiple table update done with temporary table. +--echo # 1979-01-15 03:02:01 +SET TIMESTAMP = 285213721.134679; +UPDATE t1 JOIN t2 USING ( c ) SET t1.d = 1; +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t1, t2; + + +--echo # +--echo # Test of ON UPDATE CURRENT_TIMESTAMP. +--echo # +eval 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 +); + +--echo # 2011-04-20 09:53:41 UTC +SET TIMESTAMP = 1303293221.794613; + +INSERT INTO t1 ( c ) VALUES ( 1 ); +SELECT * FROM t1; + +UPDATE t1 SET c = 1; +SELECT * FROM t1; + +UPDATE t1 SET c = 2; +SELECT * FROM t1; + +--echo # +--echo # Test of multiple-table UPDATE for ON UPDATE CURRENT_TIMESTAMP +--echo # +--echo # 2011-04-20 15:06:13 UTC +SET TIMESTAMP = 1303311973.534231; + +UPDATE t1 t11, t1 t12 SET t11.c = 2; +SELECT * FROM t1; + +UPDATE t1 t11, t1 t12 SET t11.c = 3; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Test of a multiple-table update where only one table is updated and +--echo # the updated table has a primary key. +--echo # +eval CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a) ); +INSERT INTO t1 VALUES (1, 1),(2, 2),(3, 3),(4, 4); + +eval 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; +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # Test of ALTER TABLE, reordering columns. +--echo # +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b INT );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp AFTER b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, c $timestamp NULL );eval ALTER TABLE t1 MODIFY b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp FIRST; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a INT, b $timestamp NULL );eval ALTER TABLE t1 MODIFY b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp FIRST; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b $timestamp NULL );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b $timestamp NULL );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval 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;eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp AFTER b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now, b INT, c $timestamp NULL );eval ALTER TABLE t1 MODIFY c $timestamp NULL FIRST; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp, b INT, c $timestamp NULL ); +SHOW CREATE TABLE t1;eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp AFTER b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp, b INT, c $timestamp NULL );eval ALTER TABLE t1 MODIFY c $timestamp NULL FIRST; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Test of ALTER TABLE, adding columns. +--echo # +eval CREATE TABLE t1 ( a INT ); +eval ALTER TABLE t1 ADD COLUMN b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Test of INSERT SELECT. +--echo # +eval 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 +); + +eval 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 +); + +--echo # 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' +); + +--echo # 1986-09-27 01:00:00 UTC +SET TIMESTAMP = 528166800.132435; + +INSERT INTO t1 ( a, c ) SELECT a, c FROM t2; + +SELECT * FROM t1; + +DROP TABLE t1, t2; + +--echo # +--echo # Test of CREATE TABLE SELECT. +--echo # +--echo # We test that the columns of the source table are not used to determine +--echo # function defaults for the receiving table. +--echo # + +--echo # 1970-04-11 20:13:57 UTC +SET TIMESTAMP = 8712837.657898; +eval 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 (); + +--echo # 1971-01-31 21:13:57 UTC +SET TIMESTAMP = 34200837.164937; + +eval CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; SELECT * FROM t2; +eval CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; SELECT * FROM t3; +eval CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; SELECT * FROM t4; +eval CREATE TABLE t5 SELECT d FROM t1; SHOW CREATE TABLE t5; SELECT * FROM t5; +eval CREATE TABLE t6 SELECT e FROM t1; SHOW CREATE TABLE t6; SELECT * FROM t6; +eval CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; SELECT * FROM t7; +eval CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; SELECT * FROM t8; +eval CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; SELECT * FROM t9; +eval CREATE TABLE t10 SELECT i FROM t1; SHOW CREATE TABLE t10; SELECT * FROM t10; +eval CREATE TABLE t11 SELECT j FROM t1; SHOW CREATE TABLE t11; SELECT * FROM t11; + +eval 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; + +DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12; + +--echo # 1970-04-11 20:13:57 UTC +SET TIMESTAMP = 8712837.164953; +eval 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 (); + +--echo # 1971-01-31 20:13:57 UTC +SET TIMESTAMP = 34200837.915736; + +eval CREATE TABLE t2 SELECT a FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; + +eval CREATE TABLE t3 SELECT b FROM t1; +SHOW CREATE TABLE t3; +SELECT * FROM t3; + +eval CREATE TABLE t4 SELECT c FROM t1; +SHOW CREATE TABLE t4; +SELECT * FROM t4; + +eval CREATE TABLE t5 SELECT d FROM t1; +SHOW CREATE TABLE t5; +SELECT * FROM t5; + +eval CREATE TABLE t6 SELECT e FROM t1; +SHOW CREATE TABLE t6; +SELECT * FROM t6; + +DROP TABLE t1, t2, t3, t4, t5, t6; + +--echo # +--echo # Test of a CREATE TABLE SELECT that also declared columns. In this case +--echo # the function default should be de-activated during the execution of the +--echo # CREATE TABLE statement. +--echo # +--echo # 1970-01-01 03:16:40 +SET TIMESTAMP = 1000.987654; +eval CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES ( 1 ), ( 2 ); + +eval CREATE TABLE t2 ( b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp) SELECT a FROM t1; + +SHOW CREATE TABLE t2; +SET TIMESTAMP = 2000.876543; +INSERT INTO t2( a ) VALUES ( 3 ); +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # Test of updating a view. +--echo # +eval CREATE TABLE t1 ( a INT, b $datetime DEFAULT $current_timestamp ); +eval CREATE TABLE t2 ( a INT, b $datetime ON UPDATE $current_timestamp ); + +eval CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; + +eval CREATE VIEW v2 AS SELECT * FROM t2; +SHOW CREATE VIEW v2; + +--echo # 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; +SELECT * FROM v1; + +SELECT * FROM t2; +SELECT * FROM v2; + +--echo # 1970-04-11 20:13:57 UTC +SET TIMESTAMP = 8712837.567332; +UPDATE v1 SET a = 2; +UPDATE v2 SET a = 2; + +SELECT * FROM t1; +SELECT * FROM v1; + +SELECT * FROM t2; +SELECT * FROM v2; + +DROP VIEW v1, v2; +DROP TABLE t1, t2; + +--echo # +--echo # Test with stored procedures. +--echo # +eval 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; + +--echo # 1971-01-31 20:13:57 UTC +SET TIMESTAMP = 34200837.876544; +CALL p1(); +SELECT * FROM t1; + +--echo # 1970-04-11 21:13:57 UTC +SET TIMESTAMP = 8712837.143546; +CALL p2(); +SELECT * FROM t1; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +--echo # +--echo # Test with triggers. +--echo # +eval 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 +); + +eval CREATE TABLE t2 ( a INT ); + +DELIMITER |; +eval CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + INSERT INTO t1 ( a ) VALUES ( 1 ); +END| +DELIMITER ;| + +--echo # 1971-01-31 21:13:57 UTC +SET TIMESTAMP = 34200837.978675; + +INSERT INTO t2 ( a ) VALUES ( 1 ); +SELECT * FROM t1; + +DROP TRIGGER t2_trg; + +DELIMITER |; +eval CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + UPDATE t1 SET a = 2; +END| +DELIMITER ;| + +--echo # 1970-04-11 21:13:57 UTC +SET TIMESTAMP = 8712837.456789; + +INSERT INTO t2 ( a ) VALUES ( 1 ); +SELECT * FROM t1; + +DROP TABLE t1, t2; + +--echo # +--echo # Test where the assignment target is not a column. +--echo # +eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); +eval CREATE TABLE t2 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); +eval CREATE TABLE t3 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp ); +eval CREATE TABLE t4 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp ); + +eval CREATE VIEW v1 AS SELECT a COLLATE latin1_german1_ci AS b FROM t1; +eval CREATE VIEW v2 ( b ) AS SELECT a COLLATE latin1_german1_ci FROM t2; +eval CREATE VIEW v3 AS SELECT a COLLATE latin1_german1_ci AS b FROM t3; +eval 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; + +INSERT INTO v2 ( b ) VALUES ( '2007-10-24 00:03:34.010203' ); +SELECT a FROM t2; + +INSERT INTO t3 VALUES (); +UPDATE v3 SET b = '2007-10-24 00:03:34.010203'; +SELECT a FROM t3; + +INSERT INTO t4 VALUES (); +UPDATE v4 SET b = '2007-10-24 00:03:34.010203'; +SELECT a FROM t4; + +DROP VIEW v1, v2, v3, v4; +DROP TABLE t1, t2, t3, t4; + +--echo # +--echo # Test of LOAD DATA/XML INFILE +--echo # This tests behavior of function defaults for TIMESTAMP and DATETIME +--echo # columns. during LOAD ... INFILE. +--echo # As can be seen here, a TIMESTAMP column with only ON UPDATE +--echo # CURRENT_TIMESTAMP will still have CURRENT_TIMESTAMP inserted on LOAD +--echo # ... INFILE if the value is missing. For DATETIME columns a NULL value +--echo # is inserted instead. +--echo # + +eval 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 +); + +eval 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; + +--echo # Mon Aug 1 15:11:19 2011 UTC +SET TIMESTAMP = 1312211479.918273; + +LOAD DATA INFILE 't3.dat' INTO TABLE t1; +--query_vertical SELECT * FROM t1 + +LOAD DATA INFILE 't4.dat' INTO TABLE t2; +SELECT a FROM t2; +SELECT b FROM t2; +SELECT c FROM t2; +SELECT d FROM t2; +--echo # As shown here, supplying a NULL value to a non-nullable +--echo # column with no default value results in the zero date. +SELECT e FROM t2; +--echo # As shown here, supplying a NULL value to a non-nullable column with a +--echo # default value results in the zero date. +SELECT f FROM t2; +--echo # As shown here, supplying a NULL value to a non-nullable column with a +--echo # default function results in the zero date. +SELECT g FROM t2; +--echo # As shown here, supplying a NULL value to a non-nullable DATETIME ON +--echo # UPDATE CURRENT_TIMESTAMP column with no default value results in the +--echo # zero date. +SELECT h FROM t2; +SELECT i FROM t2; + +DELETE FROM t1; +DELETE FROM t2; + +--echo # Read t3 file into t1 +--echo # The syntax will cause a different code path to be taken +--echo # (read_fixed_length()) than under the LOAD ... INTO TABLE t1 command +--echo # above. The code in this path is copy-pasted code from the path taken +--echo # under the syntax used in the previous LOAD command. +LOAD DATA INFILE 't3.dat' INTO TABLE t1 +FIELDS TERMINATED BY '' ENCLOSED BY ''; + +SELECT b FROM t1; +SELECT c FROM t1; +SELECT d FROM t1; +SELECT e FROM t1; +--echo # Yes, a missing field cannot be NULL using this syntax, so it will +--echo # zero date instead. Says a comment in read_fixed_length() : "No fields +--echo # specified in fields_vars list can be NULL in this format." +--echo # It appears to be by design. This is inconsistent with LOAD DATA INFILE +--echo # syntax in previous test. +SELECT f FROM t1; +SELECT g FROM t1; +--echo # See comment above "SELECT f FROM f1". +SELECT h FROM t1; +SELECT i FROM t1; +DELETE FROM t1; + +LOAD DATA INFILE 't5.dat' INTO TABLE t1 ( a, @dummy ); +SELECT * FROM t1; +SELECT @dummy; +DELETE FROM t1; + +LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET c = '2005-06-06 08:09:10'; +SELECT * FROM t1; +DELETE FROM t1; + +LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET g = '2005-06-06 08:09:10'; +SELECT * FROM t1; +DELETE FROM t1; + +--echo # Load a static XML file +LOAD XML INFILE '../../std_data/onerow.xml' INTO TABLE t1 +ROWS IDENTIFIED BY '<row>'; + +--echo Missing tags are treated as NULL +--query_vertical SELECT * FROM t1 + +DROP TABLE t1, t2; + +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/t3.dat; +remove_file $MYSQLD_DATADIR/test/t4.dat; +remove_file $MYSQLD_DATADIR/test/t5.dat; + + +--echo # +--echo # Similar LOAD DATA tests in another form +--echo # +--echo # All of this test portion has been run on a pre-WL5874 trunk +--echo # (except that like_b and like_c didn't exist) and all result +--echo # differences are a bug. +--echo # Regarding like_b its definition is the same as b's except +--echo # that the constant default is replaced with a function +--echo # default. Our expectation is that like_b would behave +--echo # like b: if b is set to NULL, or set to 0000-00-00, or set to +--echo # its default, then the same should apply to like_b. Same for +--echo # like_c vs c. + +--echo # 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; + +--echo # Too short row + +eval +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 +); + +--echo # There is no promotion +SHOW CREATE TABLE t1; + +LOAD DATA INFILE "file1.dat" INTO table t1; + +--echo # It is strange that "like_b" gets NULL when "b" gets 0. But +--echo # this is consistent with how "a" gets NULL when "b" gets 0, +--echo # with how "g" gets NULL when "d" gets 0, and with how "h" gets +--echo # NULL when "i" gets 0. Looks like "DEFAULT +--echo # <non-NULL-constant>" is changed to 0, whereas DEFAULT NULL +--echo # and DEFAULT NOW are changed to NULL. +--query_vertical SELECT * FROM t1 +delete from t1; + +alter table t1 +modify f TIMESTAMP NULL default CURRENT_TIMESTAMP; + +--echo # There is no promotion +SHOW CREATE TABLE t1; + +LOAD DATA INFILE "file1.dat" INTO table t1; + +--query_vertical SELECT * FROM t1 +delete from t1; + +drop table t1; + +--echo # Conclusion derived from trunk's results: +--echo # DATETIME DEFAULT <non-NULL-constant> (b,c) gets 0000-00-00, +--echo # DATETIME DEFAULT NULL (a) gets NULL, +--echo # TIMESTAMP NULL DEFAULT <non-NULL-constant> (d) gets 0000-00-00, +--echo # TIMESTAMP NULL DEFAULT NULL (g) gets NULL, +--echo # TIMESTAMP NULL DEFAULT NOW (f after ALTER) gets NULL, +--echo # TIMESTAMP NOT NULL (f before ALTER, e) gets NOW. + +--echo ### Loading NULL ### + +eval +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 +); + +--echo # There is no promotion +SHOW CREATE TABLE t1; + +LOAD DATA INFILE "file2.dat" INTO table t1; + +--query_vertical SELECT * FROM t1 +delete from t1; + +alter table t1 +modify f TIMESTAMP NULL default CURRENT_TIMESTAMP; + +--echo # There is no promotion +SHOW CREATE TABLE t1; + +LOAD DATA INFILE "file2.dat" INTO table t1; + +--query_vertical SELECT * FROM t1 +delete from t1; + +--echo # Conclusion derived from trunk's results: +--echo # DATETIME NULL (a,b) gets NULL, +--echo # DATETIME NOT NULL (c) gets 0000-00-00, +--echo # TIMESTAMP NULL (d,f,g) gets NULL, +--echo # TIMESTAMP NOT NULL (e) gets NOW. + +drop table t1; +remove_file $MYSQLD_DATADIR/test/file1.dat; +remove_file $MYSQLD_DATADIR/test/file2.dat; + +--echo # +--echo # Test of updatable views with check options. The option can be violated +--echo # using ON UPDATE updates which is very strange as this offers a loophole +--echo # in this integrity check. +--echo # +SET TIME_ZONE = "+03:00"; +--echo # 1970-01-01 03:16:40 +SET TIMESTAMP = 1000.123456; + +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp) ENGINE = INNODB; + +SHOW CREATE TABLE t1; + +INSERT INTO t1 ( a ) VALUES ( 1 ); + +SELECT * FROM t1; + +eval CREATE VIEW v1 AS SELECT * FROM t1 WHERE b <= '1970-01-01 03:16:40.123456' +WITH CHECK OPTION; + +SELECT * FROM v1; + +--echo # 1970-01-01 03:33:20 +SET TIMESTAMP = 2000.000234; + +--error ER_VIEW_CHECK_FAILED +UPDATE v1 SET a = 2; +SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1; + +eval CREATE TABLE t1 ( + a $timestamp NOT NULL DEFAULT '1973-08-14 09:11:22.089786' ON UPDATE $current_timestamp, + c INT KEY +); +--echo # 1973-08-14 09:11:22 UTC +SET TIMESTAMP = 114167482.534231; +INSERT INTO t1 ( c ) VALUES ( 1 ); + +eval CREATE VIEW v1 AS +SELECT * +FROM t1 +WHERE a >= '1973-08-14 09:11:22' +WITH LOCAL CHECK OPTION; + +SELECT * FROM v1; + +SET TIMESTAMP = 1.126789; + +--error ER_VIEW_CHECK_FAILED +INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2; + +SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # Bug 13095459 - MULTI-TABLE UPDATE MODIFIES A ROW TWICE +--echo # +eval CREATE TABLE t1 ( + a INT, + b INT, + ts $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, + PRIMARY KEY ( a, ts ) +) ENGINE = INNODB; +INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' ); + +eval CREATE TABLE t2 ( a INT ) ENGINE = INNODB; +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; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT +--echo # CURRENT_TIMESTAMP INSERTS ZERO +--echo # +SET timestamp = 1000; + +CREATE TABLE t1 ( b INT ); +INSERT INTO t1 VALUES (1); + +eval ALTER TABLE t1 ADD COLUMN a6 $datetime DEFAULT $now ON UPDATE $now FIRST; +eval ALTER TABLE t1 ADD COLUMN a5 $datetime DEFAULT $now FIRST; +eval ALTER TABLE t1 ADD COLUMN a4 $datetime ON UPDATE $now FIRST; + +eval ALTER TABLE t1 ADD COLUMN a3 $timestamp NOT NULL DEFAULT $now ON UPDATE $now FIRST; +eval ALTER TABLE t1 ADD COLUMN a2 $timestamp NOT NULL DEFAULT $now FIRST; +eval ALTER TABLE t1 ADD COLUMN a1 $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now FIRST; + +eval ALTER TABLE t1 ADD COLUMN c1 $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now AFTER b; +eval ALTER TABLE t1 ADD COLUMN c2 $timestamp NOT NULL DEFAULT $now AFTER c1; +eval ALTER TABLE t1 ADD COLUMN c3 $timestamp NOT NULL DEFAULT $now ON UPDATE $now AFTER c2; + +eval ALTER TABLE t1 ADD COLUMN c4 $datetime ON UPDATE $now AFTER c3; +eval ALTER TABLE t1 ADD COLUMN c5 $datetime DEFAULT $now AFTER c4; +eval ALTER TABLE t1 ADD COLUMN c6 $datetime DEFAULT $now ON UPDATE $now AFTER c5; + +SELECT * FROM t1; + +DROP TABLE t1; + + +eval 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; + +DROP TABLE t1; + + +eval 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 (); + +eval ALTER TABLE t1 MODIFY COLUMN a $timestamp DEFAULT $now; +eval ALTER TABLE t1 MODIFY COLUMN b $datetime DEFAULT $now; +INSERT INTO t1 VALUES (); + +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/include/function_defaults_notembedded.inc b/mysql-test/include/function_defaults_notembedded.inc new file mode 100644 index 00000000000..d9708c13da5 --- /dev/null +++ b/mysql-test/include/function_defaults_notembedded.inc @@ -0,0 +1,94 @@ +SET TIME_ZONE = "+00:00"; + +--echo # +--echo # Test of INSERT DELAYED ... SET ... +--echo # + +--echo # 2011-04-19 08:02:40 UTC +SET TIMESTAMP = 1303200160.123456; + +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp); + +INSERT DELAYED INTO t1 SET a = 1; +FLUSH TABLE t1; + +SELECT * FROM t1; +SELECT * FROM t1 WHERE b = 0; + +INSERT DELAYED INTO t1 SET a = 2, b = '1980-01-02 10:20:30.405060'; +FLUSH TABLE t1; + +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Test of INSERT DELAYED ... VALUES ... +--echo # + +--echo # 2011-04-19 08:04:01 UTC +SET TIMESTAMP = 1303200241.234567; + +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp); + +INSERT DELAYED INTO t1 ( a ) VALUES (1); +FLUSH TABLE t1; +SELECT * FROM t1; + +INSERT DELAYED INTO t1 VALUES (2, '1977-12-19 12:34:56.789123'); +FLUSH TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Test of a delayed insert handler servicing two insert operations +--echo # with different sets of active defaults. +--echo # +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp); + +--connect(con1, localhost, root,,) +--echo # 2011-04-19 08:04:01 UTC +SET TIMESTAMP = 1303200241.345678; +SET debug_sync = 'before_write_delayed SIGNAL parked WAIT_FOR go'; +--send INSERT DELAYED INTO t1 ( a ) VALUES (1), (2), (3) + +--connection default +SET debug_sync = 'now WAIT_FOR parked'; + +--connect(con2, localhost, root,,) +--echo # 2011-04-19 08:04:01 UTC +SET TIME_ZONE="+03:00"; +SET TIMESTAMP = 1303200241.456789; +--send INSERT DELAYED INTO t1 ( a, b ) VALUES (4, '1977-12-19 12:34:56.789123'), (5, '1977-12-19 12:34:57.891234'), (6, '1977-12-19 12:34:58.912345') + +--connection default +SET debug_sync = 'now SIGNAL go'; + +--let $wait_condition= SELECT COUNT(*) = 6 FROM t1 +--source include/wait_condition.inc + +--sorted_result +SELECT * FROM t1; + +--disconnect con1 +--disconnect con2 + +DROP TABLE t1; + +--echo # +--echo # Test of early activation of function defaults. +--echo # + +eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp); + +SET TIMESTAMP = 1317235172.987654; # 2011-09-28 18:39:32 UTC +INSERT DELAYED INTO t1 ( a ) VALUES (1), (2), (3); + +SET TIMESTAMP = 385503754.876543; # 1982-03-20 20:22:34 UTC +INSERT DELAYED INTO t1 ( a ) VALUES (4), (5), (6); + +FLUSH TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/include/have_debug_sync.inc b/mysql-test/include/have_debug_sync.inc index 7aa5baf3342..ea2247a2746 100644 --- a/mysql-test/include/have_debug_sync.inc +++ b/mysql-test/include/have_debug_sync.inc @@ -1,5 +1,3 @@ ---require r/have_debug_sync.require -disable_query_log; -let $value= query_get_value(SHOW VARIABLES LIKE 'debug_sync', Value, 1); -eval SELECT ('$value' LIKE 'ON %') AS debug_sync; -enable_query_log; +if (`select @@debug_sync not like 'ON %'`) { + --skip Needs a debug_sync enabled +} diff --git a/mysql-test/include/have_innodb.combinations b/mysql-test/include/have_innodb.combinations index d47d2c81a71..55107204097 100644 --- a/mysql-test/include/have_innodb.combinations +++ b/mysql-test/include/have_innodb.combinations @@ -4,6 +4,9 @@ plugin-load-add=$HA_INNODB_SO innodb innodb-cmpmem innodb-trx +innodb-buffer-pool-stats +innodb-buffer-page +innodb-buffer-page-lru [xtradb_plugin] ignore-builtin-innodb @@ -11,9 +14,15 @@ plugin-load-add=$HA_XTRADB_SO innodb innodb-cmpmem innodb-trx +innodb-buffer-pool-stats +innodb-buffer-page +innodb-buffer-page-lru [xtradb] innodb innodb-cmpmem innodb-trx innodb-metrics +innodb-buffer-pool-stats +innodb-buffer-page +innodb-buffer-page-lru diff --git a/mysql-test/include/wait_for_binlog_checkpoint.inc b/mysql-test/include/wait_for_binlog_checkpoint.inc new file mode 100644 index 00000000000..960cf4e41b1 --- /dev/null +++ b/mysql-test/include/wait_for_binlog_checkpoint.inc @@ -0,0 +1,53 @@ +# include/wait_for_binlog_checkpoint.inc +# +# SUMMARY +# +# Wait until binlog checkpoint has been logged for current binlog file. +# This is useful to avoid races with output difference for binlog +# checkpoints, as these are logged asynchronously from the binlog +# background thread. +# +# USAGE: +# +# --source include/wait_for_binlog_checkpoint.inc + +let $_wait_count= 300; + +let $_found= 0; + +while ($_wait_count) +{ + dec $_wait_count; + let $_cur_binlog= query_get_value(SHOW MASTER STATUS, File, 1); + let $_more= 1; + let $_row= 1; + while ($_more) + { + let $_event= query_get_value(SHOW BINLOG EVENTS IN "$_cur_binlog", Event_type, $_row); + if ($_event == "No such row") + { + let $_more= 0; + } + if ($_event == "Binlog_checkpoint") + { + let $_info= query_get_value(SHOW BINLOG EVENTS IN "$_cur_binlog", Info, $_row); + if (`SELECT INSTR("$_info", "$_cur_binlog") != 0`) + { + let $_more= 0; + let $_wait_count= 0; + let $_found= 1; + } + } + inc $_row; + } + if ($_wait_count) + { + real_sleep 0.1; + } +} + +if (!$_found) +{ + eval SHOW BINLOG EVENTS IN "$_cur_binlog"; + --die ERROR: failed while waiting for binlog checkpoint $_cur_binlog +} |