diff options
Diffstat (limited to 'mysql-test/suite/parts')
29 files changed, 1658 insertions, 13 deletions
diff --git a/mysql-test/suite/parts/inc/partition_key_32col.inc b/mysql-test/suite/parts/inc/partition_key_32col.inc index 74016d9b556..b0635ca0e9c 100644 --- a/mysql-test/suite/parts/inc/partition_key_32col.inc +++ b/mysql-test/suite/parts/inc/partition_key_32col.inc @@ -1,4 +1,4 @@ ---error ER_TOO_MANY_KEY_PARTS +--error ER_TOO_MANY_PARTITION_FUNC_FIELDS_ERROR eval create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3)) engine=$engine partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3) ( partition pa1 max_rows=20 min_rows=2, diff --git a/mysql-test/suite/parts/inc/partition_mgm.inc b/mysql-test/suite/parts/inc/partition_mgm.inc index 1ab548222a8..9dfa2b2ffb3 100644 --- a/mysql-test/suite/parts/inc/partition_mgm.inc +++ b/mysql-test/suite/parts/inc/partition_mgm.inc @@ -13,6 +13,7 @@ # part_optA-D Extra partitioning options (E.g. INDEX/DATA DIR) # # # # have_bug33158 NDB case insensitive create, but case sensitive rename # +# no_truncate No support for truncate partition # #------------------------------------------------------------------------------# # Original Author: mattiasj # # Original Date: 2008-06-27 # @@ -518,6 +519,95 @@ DROP TABLE TableA; } # End of $can_only_key +if ($no_truncate) +{ +--echo # Verify that TRUNCATE PARTITION gives error +eval CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, + b VARCHAR(255)) +ENGINE = $engine +PARTITION BY KEY (a) +(PARTITION LT1000, + PARTITION LT2000, + PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +--error ER_PARTITION_MGMT_ON_NONPARTITIONED, ER_ILLEGAL_HA +ALTER TABLE t1 TRUNCATE PARTITION MAX; +} +if (!$no_truncate) +{ +--echo # Testing TRUNCATE PARTITION +eval CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, + b VARCHAR(255)) +ENGINE = $engine +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), + PARTITION LT2000 VALUES LESS THAN (2000), + PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +SELECT * FROM t1 ORDER BY a; +ALTER TABLE t1 ANALYZE PARTITION MAX; +--echo # Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +--echo # Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +--echo # Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +--echo # Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +--echo # Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +--echo # Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; +} --echo # Cleaning up before exit eval USE $old_db; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/inc/partition_syntax.inc b/mysql-test/suite/parts/inc/partition_syntax.inc index e72aad80f0a..e7eea73325a 100644 --- a/mysql-test/suite/parts/inc/partition_syntax.inc +++ b/mysql-test/suite/parts/inc/partition_syntax.inc @@ -312,7 +312,7 @@ PARTITION BY RANGE(f_int1) --echo #------------------------------------------------------------------------ --echo # 3.5.1 NULL in RANGE partitioning clause --echo # 3.5.1.1 VALUE LESS THAN (NULL) is not allowed ---error ER_PARSE_ERROR +--error ER_NULL_IN_VALUES_LESS_THAN eval CREATE TABLE t1 ( $column_list ) @@ -320,7 +320,7 @@ PARTITION BY RANGE(f_int1) ( PARTITION part1 VALUES LESS THAN (NULL), PARTITION part2 VALUES LESS THAN (1000)); --echo # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed ---error ER_PARSE_ERROR +--error ER_NULL_IN_VALUES_LESS_THAN eval CREATE TABLE t1 ( $column_list ) diff --git a/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result b/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result index 30ff27df298..4f623813386 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result @@ -915,6 +915,18 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Archive' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result b/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result index cd55ffbad03..19f16780d13 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result @@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'InnoDB' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +MySQL_Test_DB.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result b/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result index faf776e03a3..69a43b64d87 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result @@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Memory' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +MySQL_Test_DB.t1 analyze note The storage engine for the table doesn't support analyze +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result b/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result index 827f7a15c24..9b4e85be9d0 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result @@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'MyISAM' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +MySQL_Test_DB.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result b/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result index 45b674638e7..15b3f424527 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result @@ -181,6 +181,18 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 # Cleaning up after KEY PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'NDBCluster' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result b/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result index 443453a2d70..4cd8cafa3ee 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result @@ -882,6 +882,18 @@ TableA CREATE TABLE `tablea` ( ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Archive' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result b/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result index 49ccc7b1808..952f4136cb6 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'InnoDB' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result b/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result index 6f34054428c..435a0d8313e 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` ( ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Memory' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze note The storage engine for the table doesn't support analyze +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result b/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result index ac230e29c66..3a90ce4d73c 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'MyISAM' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result b/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result index 0a53e1b4a9b..1d221caa163 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result @@ -219,6 +219,18 @@ TableA CREATE TABLE `tablea` ( ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 # Cleaning up after KEY PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'NDBCluster' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result b/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result index fc0390c238d..6e8abfef06d 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result @@ -882,6 +882,18 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Archive' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result b/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result index da111137068..8e42bc9eb62 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'InnoDB' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result b/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result index a1716ea36c8..24047912ab1 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'Memory' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze note The storage engine for the table doesn't support analyze +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result b/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result index 6bdfa149de0..7a61a811ea3 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result @@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; +# Testing TRUNCATE PARTITION +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'MyISAM' +PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000), +PARTITION LT2000 VALUES LESS THAN (2000), +PARTITION MAX VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL AUTO_INCREMENT, + `b` varchar(255) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY RANGE (a) +(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM, + PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM, + PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +SELECT * FROM t1 ORDER BY a; +a b +1 First +2 Second +999 Last in LT1000 +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First in MAX +2001 Second in MAX +ALTER TABLE t1 ANALYZE PARTITION MAX; +Table Op Msg_type Msg_text +mysql_test_db.t1 analyze status OK +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION MAX; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION MAX; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); +SELECT * FROM t1 WHERE a >= 2000; +a b +2000 First after TRUNCATE MAX (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT1000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +1000 First in LT2000 +1001 Second in LT2000 +1999 Last in LT2000 +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +# Truncate without FLUSH +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +# Truncate with FLUSH after +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +FLUSH TABLES; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +# Truncate with FLUSH before +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +# Truncate with FLUSH after INSERT +FLUSH TABLES; +ALTER TABLE t1 TRUNCATE PARTITION LT2000; +INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); +SELECT * FROM t1 ORDER BY a; +a b +2000 First after TRUNCATE MAX (4) +2001 First after TRUNCATE LT1000 (1) +2002 First after TRUNCATE LT1000 (2) +2003 First after TRUNCATE LT1000 (3) +2004 First after TRUNCATE LT1000 (4) +2005 First after TRUNCATE LT2000 (1) +2006 First after TRUNCATE LT2000 (2) +2007 First after TRUNCATE LT2000 (3) +2008 First after TRUNCATE LT2000 (4) +DROP TABLE t1; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result b/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result index 8b9c5be1fb6..2f5dfe5e08e 100644 --- a/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result +++ b/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result @@ -219,6 +219,18 @@ TableA CREATE TABLE `TableA` ( ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 # Cleaning up after KEY PARTITIONING test DROP TABLE TableA; +# Verify that TRUNCATE PARTITION gives error +CREATE TABLE t1 +(a BIGINT AUTO_INCREMENT PRIMARY KEY, +b VARCHAR(255)) +ENGINE = 'NDBCluster' +PARTITION BY KEY (a) +(PARTITION LT1000, +PARTITION LT2000, +PARTITION MAX); +INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX"); +ALTER TABLE t1 TRUNCATE PARTITION MAX; +Got one of the listed errors # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB; diff --git a/mysql-test/suite/parts/r/partition_special_innodb.result b/mysql-test/suite/parts/r/partition_special_innodb.result index 8869f6d450c..26c1ac9356c 100644 --- a/mysql-test/suite/parts/r/partition_special_innodb.result +++ b/mysql-test/suite/parts/r/partition_special_innodb.result @@ -133,7 +133,7 @@ partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); -ERROR 42000: Too many key parts specified; max 16 parts allowed +ERROR HY000: Too many fields in 'list of partition fields' create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='InnoDB' partition by key(a,b,c,d,e,f,g,h) ( partition pa1 max_rows=20 min_rows=2, diff --git a/mysql-test/suite/parts/r/partition_special_myisam.result b/mysql-test/suite/parts/r/partition_special_myisam.result index 7167d6af39e..60c9f9666cd 100644 --- a/mysql-test/suite/parts/r/partition_special_myisam.result +++ b/mysql-test/suite/parts/r/partition_special_myisam.result @@ -133,7 +133,7 @@ partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); -ERROR 42000: Too many key parts specified; max 16 parts allowed +ERROR HY000: Too many fields in 'list of partition fields' create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='MyISAM' partition by key(a,b,c,d,e,f,g,h) ( partition pa1 max_rows=20 min_rows=2, diff --git a/mysql-test/suite/parts/r/partition_syntax_innodb.result b/mysql-test/suite/parts/r/partition_syntax_innodb.result index c27a1386409..848a30a13df 100644 --- a/mysql-test/suite/parts/r/partition_syntax_innodb.result +++ b/mysql-test/suite/parts/r/partition_syntax_innodb.result @@ -610,8 +610,7 @@ f_charbig VARCHAR(1000) PARTITION BY RANGE(f_int1) ( PARTITION part1 VALUES LESS THAN (NULL), PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 +ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed CREATE TABLE t1 ( f_int1 INTEGER, @@ -623,8 +622,7 @@ f_charbig VARCHAR(1000) PARTITION BY RANGE(f_int1) ( PARTITION part1 VALUES LESS THAN (NULL), PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 +ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN # 3.5.2 NULL in LIST partitioning clause # 3.5.2.1 VALUE IN (NULL) CREATE TABLE t1 ( diff --git a/mysql-test/suite/parts/r/partition_syntax_myisam.result b/mysql-test/suite/parts/r/partition_syntax_myisam.result index 0cf98765797..f3b7f5b3025 100644 --- a/mysql-test/suite/parts/r/partition_syntax_myisam.result +++ b/mysql-test/suite/parts/r/partition_syntax_myisam.result @@ -610,8 +610,7 @@ f_charbig VARCHAR(1000) PARTITION BY RANGE(f_int1) ( PARTITION part1 VALUES LESS THAN (NULL), PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 +ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed CREATE TABLE t1 ( f_int1 INTEGER, @@ -623,8 +622,7 @@ f_charbig VARCHAR(1000) PARTITION BY RANGE(f_int1) ( PARTITION part1 VALUES LESS THAN (NULL), PARTITION part2 VALUES LESS THAN (1000)); -ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), -PARTITION part2 VALUES LESS THAN (1000))' at line 9 +ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN # 3.5.2 NULL in LIST partitioning clause # 3.5.2.1 VALUE IN (NULL) CREATE TABLE t1 ( diff --git a/mysql-test/suite/parts/r/rpl_partition.result b/mysql-test/suite/parts/r/rpl_partition.result index c2537815631..dfce1c3cdfa 100644 --- a/mysql-test/suite/parts/r/rpl_partition.result +++ b/mysql-test/suite/parts/r/rpl_partition.result @@ -177,6 +177,8 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 0 Last_SQL_Error +Replicate_Ignore_Server_Ids +Master_Server_Id 1 SELECT count(*) "Slave norm" FROM t1; Slave norm 500 SELECT count(*) "Slave bykey" FROM t2; diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test index 5b4f2568d46..313da329a9f 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test @@ -35,6 +35,7 @@ ##### Storage engine to be tested --source include/have_archive.inc let $engine= 'Archive'; +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test b/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test index 686c69cca25..736e45067bc 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test @@ -41,6 +41,8 @@ let $can_only_key= 1; # Allow hash/list/range partitioning with ndb #SET new=on; let $engine= 'NDBCluster'; +# NDB does not yet support TRUNCATE PARTITION +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test index 2bc643db75f..58eef828f06 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test @@ -32,6 +32,7 @@ ##### Storage engine to be tested --source include/have_archive.inc let $engine= 'Archive'; +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test b/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test index a70b9b5c41c..ac425eb84ff 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test @@ -38,6 +38,8 @@ let $can_only_key= 1; # Allow hash/list/range partitioning with ndb #SET new=on; let $engine= 'NDBCluster'; +# NDB does not yet support TRUNCATE PARTITION +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test index d0e2591804d..92036178e59 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test @@ -32,6 +32,7 @@ ##### Storage engine to be tested --source include/have_archive.inc let $engine= 'Archive'; +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test b/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test index 67fdfdde70b..725ba3b5e74 100644 --- a/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test @@ -37,6 +37,8 @@ let $can_only_key= 1; # Allow hash/list/range partitioning with ndb #SET new=on; let $engine= 'NDBCluster'; +# NDB does not yet support TRUNCATE PARTITION +let $no_truncate= 1; #------------------------------------------------------------------------------# # Execute the tests to be applied to all storage engines |