#At file:///home/horst/bzr/mysql-next-mr-bugfixing/ based on revid:mats.kindahl@stripped
3341 Horst.Hunger 2010-10-28
Updated tests for WL#4445 (ALTER TABLE ... EXCHANGE ...) including tests for IGNORE.
added:
mysql-test/suite/parts/inc/part_exch_drop_tabs.inc
mysql-test/suite/parts/inc/part_exch_qa.inc
mysql-test/suite/parts/inc/part_exch_qa_1.inc
mysql-test/suite/parts/inc/part_exch_qa_13.inc
mysql-test/suite/parts/inc/part_exch_qa_4.inc
mysql-test/suite/parts/inc/part_exch_qa_5.inc
mysql-test/suite/parts/inc/part_exch_qa_7.inc
mysql-test/suite/parts/inc/part_exch_qa_8.inc
mysql-test/suite/parts/inc/part_exch_tabs.inc
mysql-test/suite/parts/r/partition_exch_innodb.result
mysql-test/suite/parts/r/partition_exch_myisam.result
mysql-test/suite/parts/r/partition_exch_myisam_innodb.result
mysql-test/suite/parts/r/partition_exch_qa.result
mysql-test/suite/parts/r/partition_exch_qa_10.result
mysql-test/suite/parts/r/partition_exch_qa_11.result
mysql-test/suite/parts/r/partition_exch_qa_12.result
mysql-test/suite/parts/r/partition_exch_qa_13.result
mysql-test/suite/parts/r/partition_exch_qa_14.result
mysql-test/suite/parts/r/partition_exch_qa_15.result
mysql-test/suite/parts/r/partition_exch_qa_1_innodb.result
mysql-test/suite/parts/r/partition_exch_qa_1_myisam.result
mysql-test/suite/parts/r/partition_exch_qa_2.result
mysql-test/suite/parts/r/partition_exch_qa_3.result
mysql-test/suite/parts/r/partition_exch_qa_4_innodb.result
mysql-test/suite/parts/r/partition_exch_qa_4_myisam.result
mysql-test/suite/parts/r/partition_exch_qa_5_innodb.result
mysql-test/suite/parts/r/partition_exch_qa_5_myisam.result
mysql-test/suite/parts/r/partition_exch_qa_6.result
mysql-test/suite/parts/r/partition_exch_qa_7_innodb.result
mysql-test/suite/parts/r/partition_exch_qa_7_myisam.result
mysql-test/suite/parts/r/partition_exch_qa_8_innodb.result
mysql-test/suite/parts/r/partition_exch_qa_8_myisam.result
mysql-test/suite/parts/t/partition_exch_innodb.test
mysql-test/suite/parts/t/partition_exch_myisam.test
mysql-test/suite/parts/t/partition_exch_myisam_innodb.test
mysql-test/suite/parts/t/partition_exch_qa.test
mysql-test/suite/parts/t/partition_exch_qa_10.test
mysql-test/suite/parts/t/partition_exch_qa_11.test
mysql-test/suite/parts/t/partition_exch_qa_12.test
mysql-test/suite/parts/t/partition_exch_qa_13.test
mysql-test/suite/parts/t/partition_exch_qa_14.test
mysql-test/suite/parts/t/partition_exch_qa_15.test
mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test
mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test
mysql-test/suite/parts/t/partition_exch_qa_2.test
mysql-test/suite/parts/t/partition_exch_qa_3.test
mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test
mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test
mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test
mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test
mysql-test/suite/parts/t/partition_exch_qa_6.test
mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test
mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test
mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test
mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test
=== added file 'mysql-test/suite/parts/inc/part_exch_drop_tabs.inc'
--- a/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,15 @@
+--disable_warnings
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+--enable_warnings
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,148 @@
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+
+# 1) Valid exchange with partitions.
+# exchange of values < 10 of tp to t and complete contents of t to p0 and back.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to the former contents.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+
+# Exchange with empty table.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+--sorted_result
+SELECT * FROM t_empty;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to the former contents.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+--sorted_result
+SELECT * FROM t_empty;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+
+# Exchange with null table.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+--sorted_result
+SELECT * FROM t_null;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to the former contents.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+--sorted_result
+SELECT * FROM t_null;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+
+# exchange of values < 100 of tp to t and complete contents of t to p1 and back.
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+# Back to the former contents.
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+
+# exchange of values < 1000 of tp to t and complete contents of t to p2 and back.
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+# Back to the former contents.
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+
+# 2) Valid exchange of subpartitions.
+# exchange of values < 10 of tsp to t and complete contents of t to p0 and back.
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+# Back to the former contents.
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+
+# Exchange with null table.
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+--sorted_result
+SELECT * FROM t_null;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+# Back to the former contents.
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+--sorted_result
+SELECT * FROM t_null;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_1.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_1.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_1.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,61 @@
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+
+# 13) Exchanges with indexes.
+CREATE INDEX id_t_10_b USING BTREE ON t_10 (b);
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+CREATE INDEX id_tp_b USING BTREE ON tp (b);
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+DROP INDEX id_t_10_b ON t_10;
+DROP INDEX id_tp_b ON tp;
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a);
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+show create table tp ;
+ALTER TABLE tp DROP INDEX a;
+ALTER TABLE t_10 DROP INDEX a;
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+show create table tp ;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_13.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_13.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_13.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,201 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--disable_warnings
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+--enable_warnings
+
+eval CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a))
+CHECKSUM= 1,
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a))
+COMMENT= 'comment',
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a))
+MIN_ROWS= 1,
+MAX_ROWS= 2000,
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a))
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a))
+ENGINE = $engine_table;
+
+eval CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a))
+CHECKSUM= 1,
+ENGINE = $engine_part
+PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10),
+ PARTITION p1 VALUES LESS THAN (100),
+ PARTITION p2 VALUES LESS THAN (1000));
+
+eval CREATE TABLE tp1 (a INT, b VARCHAR(55), PRIMARY KEY (a))
+ENGINE = $engine_part
+PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) MAX_ROWS=2000 MIN_ROWS=1,
+ PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=2000 MIN_ROWS=1,
+ PARTITION p2 VALUES LESS THAN (1000) MAX_ROWS=2000 MIN_ROWS=1
+);
+
+eval CREATE TABLE tsp (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+COMMENT= 'comment',
+ENGINE = $engine_subpart
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH(a)
+(PARTITION p0 VALUES LESS THAN (10)
+ (SUBPARTITION sp00 MAX_ROWS=2000 MIN_ROWS=1,
+ SUBPARTITION sp01 MAX_ROWS=2000 MIN_ROWS=1,
+ SUBPARTITION sp02 MAX_ROWS=2000 MIN_ROWS=1,
+ SUBPARTITION sp03 MAX_ROWS=2000 MIN_ROWS=1,
+ SUBPARTITION sp04 MAX_ROWS=2000 MIN_ROWS=1),
+ PARTITION p1 VALUES LESS THAN (100)
+ (SUBPARTITION sp10,
+ SUBPARTITION sp11,
+ SUBPARTITION sp12,
+ SUBPARTITION sp13,
+ SUBPARTITION sp14),
+ PARTITION p2 VALUES LESS THAN (1000)
+ (SUBPARTITION sp20,
+ SUBPARTITION sp21,
+ SUBPARTITION sp22,
+ SUBPARTITION sp23,
+ SUBPARTITION sp24));
+
+# Values t_10 (not partitioned)
+INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
+
+# Values t_100 (not partitioned)
+INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
+INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
+
+# Values t_1000 (not partitioned)
+INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
+INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
+INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
+INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
+
+# Values t_null (not partitioned)
+INSERT INTO t_null VALUES (1, "NULL");
+
+# Values tp (partitions)
+INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+# Values tp1 (partitions)
+INSERT INTO tp1 VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tp1 VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tp1 VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tp1 VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tp1 VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tp1 VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+# Values tps (subpartitions)
+INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
+eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
+eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table,
+ MAX_ROWS=2000, MIN_ROWS=1
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
+eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
+eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
+
+SHOW CREATE TABLE t_100;
+SHOW CREATE TABLE t_1000;
+SHOW CREATE TABLE tp;
+SHOW CREATE TABLE tsp;
+
+--enable_result_log
+--enable_query_log
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+
+# 13) Exchanges with different table options.
+# See bug#55944 to change the IGNORE
+INSERT INTO t_10 VALUES (10, "TEN");
+--error 1711
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM tp WHERE a < 11;
+--sorted_result
+SELECT * FROM t_10 WHERE a < 11;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+--sorted_result
+SELECT * FROM tp WHERE a < 11;
+--sorted_result
+SELECT * FROM t_10 WHERE a < 11;
+INSERT INTO t_1000 VALUES (99, "Ninetynine");
+--error 1711
+ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000;
+--sorted_result
+SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
+--sorted_result
+SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
+ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000 IGNORE;
+--sorted_result
+SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
+--sorted_result
+SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
+INSERT INTO tsp_03 VALUES (20, "Twenty");
+--error 1711
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03 IGNORE;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_03;
+
+DROP TABLE tp1;
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_4.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_4.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_4.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,45 @@
+CREATE DATABASE test_2;
+
+USE test;
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+
+USE test_2;
+--source suite/parts/inc/part_exch_tabs.inc
+
+--enable_result_log
+--enable_query_log
+
+USE test;
+# 10) Exchanges with different databases.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+--sorted_result
+SELECT * FROM test_2.t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+--sorted_result
+SELECT * FROM test_2.t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+--sorted_result
+SELECT * FROM test_2.tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+--sorted_result
+SELECT * FROM test_2.tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+USE test_2;
+--source suite/parts/inc/part_exch_drop_tabs.inc
+USE test;
+
+DROP DATABASE test_2;
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_5.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_5.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_5.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,89 @@
+--source include/not_embedded.inc
+
+CREATE USER test1@localhost;
+CREATE USER test2@localhost;
+GRANT USAGE ON *.* TO test1@localhost;
+GRANT USAGE ON *.* TO test2@localhost;
+GRANT CREATE, DROP, INSERT, SELECT ON test.* TO test1@localhost;
+GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
+
+--echo connect (test1,localhost,test1,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test1,localhost,test1,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+SELECT current_user();
+SHOW GRANTS FOR CURRENT_USER;
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--echo disconnect test1;
+disconnect test1;
+
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+SHOW GRANTS FOR CURRENT_USER;
+
+# 9) Exchanges with different owner.
+# Privilege for ALTER and SELECT
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+REVOKE ALTER ON test.* FROM test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+SHOW GRANTS FOR CURRENT_USER;
+# Privilege for ALTER and SELECT
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+# Back to former values.
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+
+--echo connection default;
+connection default;
+--echo disconnect test2;
+disconnect test2;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
+DROP USER test1@localhost;
+DROP USER test2@localhost;
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_7.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_7.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_7.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,63 @@
+CREATE USER test_2@localhost;
+
+--source include/not_embedded.inc
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+# 8) Exchanges partition and table and back in 2 sessions with an insert.
+# Parallel INSERT and SELECT
+# LOCK behaviour when exchanging different partitons.
+--echo send
+--send
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--echo connect (test_2,localhost,test_2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test_2,localhost,test_2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+# Wait until exchange has been done.
+let $wait_condition= SELECT count(a)>0 FROM tp WHERE a=1;
+--source include/wait_condition.inc
+# Expect 1,3,5,9 in tp and 2,4,6,8 in t_10
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Exchange back.
+INSERT INTO tp VALUES (7,"Seven");
+# Expect 2,4,6,8 in tp
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--echo connection default;
+connection default;
+--echo reap;
+reap;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+
+--echo connection test_2;
+connection test_2;
+let $wait_condition= SELECT count(a)>0 FROM tp WHERE a=2;
+--source include/wait_condition.inc
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+--echo disconnect test_2;
+disconnect test_2;
+
+--echo connection default;
+connection default;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
+DROP USER test_2@localhost;
+
=== added file 'mysql-test/suite/parts/inc/part_exch_qa_8.inc'
--- a/mysql-test/suite/parts/inc/part_exch_qa_8.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_qa_8.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,71 @@
+CREATE USER test2@localhost;
+
+--source include/not_embedded.inc
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+CREATE DATABASE testdb;
+USE testdb;
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+USE test;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on test.* TO test2@localhost;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on testdb.* TO test2@localhost;
+
+# 8) Exchanges partition and table and back in 2 sessions with 2 databases.
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+# Privileges on both DB's
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+--sorted_result
+SELECT * FROM testdb.t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Exchange back.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+--sorted_result
+SELECT * FROM testdb.t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+# Exchange back.
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+REVOKE INSERT ON testdb.* FROM test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
+DROP USER test2@localhost;
+DROP DATABASE testdb;
+
=== added file 'mysql-test/suite/parts/inc/part_exch_tabs.inc'
--- a/mysql-test/suite/parts/inc/part_exch_tabs.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/part_exch_tabs.inc 2010-10-28 12:56:41 +0000
@@ -0,0 +1,139 @@
+--disable_warnings
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+--enable_warnings
+
+eval CREATE TABLE t_10 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_100 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_1000 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_empty (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_null (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_table;
+
+eval CREATE TABLE tp (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_part
+PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory,
+ PARTITION p1 VALUES LESS THAN (100) $p_data_directory $p_index_directory,
+ PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory);
+
+eval CREATE TABLE tsp (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a)) $data_directory $index_directory
+ENGINE = $engine_subpart
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH(a)
+(PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory
+ (SUBPARTITION sp00,
+ SUBPARTITION sp01,
+ SUBPARTITION sp02,
+ SUBPARTITION sp03,
+ SUBPARTITION sp04),
+ PARTITION p1 VALUES LESS THAN (100)
+ (SUBPARTITION sp10 $p_data_directory $p_index_directory,
+ SUBPARTITION sp11 $p_data_directory $p_index_directory,
+ SUBPARTITION sp12 $p_data_directory $p_index_directory,
+ SUBPARTITION sp13 $p_data_directory $p_index_directory,
+ SUBPARTITION sp14 $p_data_directory $p_index_directory),
+ PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory
+ (SUBPARTITION sp20,
+ SUBPARTITION sp21,
+ SUBPARTITION sp22,
+ SUBPARTITION sp23,
+ SUBPARTITION sp24));
+
+# Values t_10 (not partitioned)
+INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
+
+# Values t_100 (not partitioned)
+INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
+INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
+
+# Values t_1000 (not partitioned)
+INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
+INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
+INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
+INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
+
+# Values t_null (not partitioned)
+INSERT INTO t_null VALUES (1, "NULL");
+
+# Values tp (partitions)
+INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+# Values tps (subpartitions)
+INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a))
+ ENGINE = $engine_table $data_directory $index_directory
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
+eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a))
+ ENGINE = $engine_table $data_directory $index_directory
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
+eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a))
+ ENGINE = $engine_table $data_directory $index_directory
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
+eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a))
+ ENGINE = $engine_table $data_directory $index_directory
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
+eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a))
+ ENGINE = $engine_table $data_directory $index_directory
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
+
+SHOW CREATE TABLE t_10;
+SHOW CREATE TABLE t_100;
+SHOW CREATE TABLE t_1000;
+SHOW CREATE TABLE tp;
+SHOW CREATE TABLE tsp;
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tp WHERE a< 10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 11 AND 100;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 101 AND 200;
+--sorted_result
+SELECT * FROM tsp;
=== added file 'mysql-test/suite/parts/r/partition_exch_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,355 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp_01;
+a b
+6 Six
+SELECT * FROM tsp_02;
+a b
+2 Two
+SELECT * FROM tsp_03;
+a b
+8 Eight
+SELECT * FROM tsp_04;
+a b
+4 Four
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+6 Six
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+2 Two
+4 Four
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,355 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp_01;
+a b
+6 Six
+SELECT * FROM tsp_02;
+a b
+2 Two
+SELECT * FROM tsp_03;
+a b
+8 Eight
+SELECT * FROM tsp_04;
+a b
+4 Four
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+6 Six
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+2 Two
+4 Four
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_myisam_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,14 @@
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,355 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
+SELECT * FROM t_empty;
+a b
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
+a b
+12 twelve
+14 Fourteen
+16 Sixteen
+18 Eightteen
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp_01;
+a b
+6 Six
+SELECT * FROM tsp_02;
+a b
+2 Two
+SELECT * FROM tsp_03;
+a b
+8 Eight
+SELECT * FROM tsp_04;
+a b
+4 Four
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
+ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+6 Six
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+1 NULL
+2 Two
+4 Four
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
+SELECT * FROM t_null;
+a b
+1 NULL
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_10.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_10.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_10.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,70 @@
+use test;
+CREATE PROCEDURE test_p1 ()
+BEGIN
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ALTER TABLE tp DROP INDEX a;
+ALTER TABLE t_10 DROP INDEX a;
+END|
+CALL test_p1;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 9;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+DROP PROCEDURE test_p1;
+SET @save_autocommit= @@autocommit;
+SET @@autocommit= OFF;
+SHOW VARIABLES LIKE '%autocommit%';
+Variable_name Value
+autocommit OFF
+CREATE TRIGGER test_trg_1 BEFORE UPDATE ON tp FOR EACH ROW
+BEGIN
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+END|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION test_f_1() RETURNS int
+BEGIN
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+END|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+SET @@autocommit= @save_autocommit;
+SET @save_event_scheduler= @@global.event_scheduler;
+SET @@global.event_scheduler= ON;
+CREATE EVENT test_ev_1
+ON SCHEDULE AT CURRENT_TIMESTAMP
+DO
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 9;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SET @@global.event_scheduler= @save_event_scheduler;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_11.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_11.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_11.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,56 @@
+use test;
+SET @part= 'p0';
+SET @part_tab= 'tp';
+SET @table= 't_10';
+SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ',@part,' WITH TABLE ',@table);
+PREPARE test_stmt1 FROM @s;
+EXECUTE test_stmt1;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp;
+a b
+1 One
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+3 Three
+5 Five
+9 Nine
+DEALLOCATE PREPARE test_stmt1;
+SET @part_tab= 'tp';
+SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ? WITH TABLE ?');
+PREPARE test_stmt2 FROM @s;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WITH TABLE ?' at line 1
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_12.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_12.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_12.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,125 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100 IGNORE;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000;
+ERROR HY000: Non matching attribute 'MAX_ROWS' between partition and table
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000 IGNORE;
+ERROR HY000: Non matching attribute 'MAX_ROWS' between partition and table
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_13.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_13.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_13.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,286 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+INSERT INTO t_10 VALUES (10, "TEN");
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Found row that does not match the partition
+SELECT * FROM tp WHERE a < 11;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM t_10 WHERE a < 11;
+a b
+1 One
+10 TEN
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+SELECT * FROM tp WHERE a < 11;
+a b
+1 One
+10 TEN
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_10 WHERE a < 11;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+INSERT INTO t_1000 VALUES (99, "Ninetynine");
+ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000;
+ERROR HY000: Found row that does not match the partition
+SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+99 Ninetynine
+ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000 IGNORE;
+SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+99 Ninetynine
+SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+INSERT INTO tsp_03 VALUES (20, "Twenty");
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
+ERROR HY000: Found row that does not match the partition
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_03;
+a b
+20 Twenty
+3 Three
+ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03 IGNORE;
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+20 Twenty
+3 Three
+4 Four
+6 Six
+SELECT * FROM tsp_03;
+a b
+8 Eight
+DROP TABLE tp1;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_14.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_14.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_14.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,45 @@
+use test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+use test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+use test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_15.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_15.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_15.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,19 @@
+use test;
+CREATE TABLE t_11 (a INT, b VARCHAR(55),
+FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE)
+ENGINE= InnoDB;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+ERROR HY000: Table to exchange with partition has foreign key references: 't_11'
+DROP TABLE IF EXISTS t_11;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_1_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_1_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_1_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,177 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+CREATE INDEX id_t_10_b USING BTREE ON t_10 (b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+CREATE INDEX id_tp_b USING BTREE ON tp (b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+DROP INDEX id_t_10_b ON t_10;
+DROP INDEX id_tp_b ON tp;
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+Table Create Table
+t_10 CREATE TABLE `t_10` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table tp ;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
+ PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB) */
+ALTER TABLE tp DROP INDEX a;
+ALTER TABLE t_10 DROP INDEX a;
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+Table Create Table
+t_10 CREATE TABLE `t_10` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`,`b`) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table tp ;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`,`b`) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
+ PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB) */
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_1_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_1_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_1_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,177 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+CREATE INDEX id_t_10_b USING BTREE ON t_10 (b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+CREATE INDEX id_tp_b USING BTREE ON tp (b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+DROP INDEX id_t_10_b ON t_10;
+DROP INDEX id_tp_b ON tp;
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+ERROR HY000: Tables have different definitions
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+Table Create Table
+t_10 CREATE TABLE `t_10` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`) USING BTREE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table tp ;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`) USING BTREE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
+ PARTITION p1 VALUES LESS THAN (100) ENGINE = MyISAM,
+ PARTITION p2 VALUES LESS THAN (1000) ENGINE = MyISAM) */
+ALTER TABLE tp DROP INDEX a;
+ALTER TABLE t_10 DROP INDEX a;
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a,b);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+show create table t_10;
+Table Create Table
+t_10 CREATE TABLE `t_10` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`,`b`) USING BTREE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table tp ;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`,`b`) USING BTREE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
+ PARTITION p1 VALUES LESS THAN (100) ENGINE = MyISAM,
+ PARTITION p2 VALUES LESS THAN (1000) ENGINE = MyISAM) */
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_2.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_2.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_2.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,144 @@
+use test;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM t_100;
+a b
+11 Eleven
+13 Thirdteen
+15 Fifeteen
+19 Nineteen
+91 Ninety-one
+93 Ninety-three
+95 Ninety-five
+99 Ninety-nine
+SELECT * FROM t_1000;
+a b
+111 Hundred elven
+113 Hundred thirdteen
+115 Hundred fiveteen
+119 Hundred nineteen
+131 Hundred thirty-one
+133 Hundred thirty-three
+135 Hundred thirty-five
+139 Hundred thirty-nine
+151 Hundred fifty-one
+153 Hundred fifty-three
+155 Hundred fity-five
+159 Hundred fifty-nine
+191 Hundred ninety-one
+193 Hundred ninety-three
+195 Hundred ninety-five
+199 Hundred ninety-nine
+SELECT * FROM tp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eighty-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp;
+a b
+112 Hundred twelve
+114 Hundred fourteen
+116 Hundred sixteen
+118 Hundred eightteen
+12 twelve
+122 Hundred twenty-two
+124 Hundred twenty-four
+126 Hundred twenty-six
+128 Hundred twenty-eight
+14 Fourteen
+16 Sixteen
+162 Hundred sixty-two
+164 Hundred sixty-four
+166 Hundred sixty-six
+168 Hundred sixty-eight
+18 Eightteen
+182 Hundred eight-two
+184 Hundred eighty-four
+186 Hundred eighty-six
+188 Hundred eighty-eight
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp_01;
+a b
+1 One
+SELECT * FROM tsp_02;
+a b
+SELECT * FROM tsp_03;
+a b
+3 Three
+SELECT * FROM tsp_04;
+a b
+9 Nine
+CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+ERROR HY000: Tables have different definitions
+DROP TABLE IF EXISTS t_11;
+CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table SELECT * FROM t_10;
+Warnings:
+Warning 1286 Unknown storage engine '$engine_table'
+Warning 1266 Using storage engine MyISAM for table 't_11'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+ERROR HY000: Tables have different definitions
+DROP TABLE IF EXISTS t_11;
+CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+DROP TABLE IF EXISTS t_11;
+CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table
+PARTITION BY KEY() AS SELECT * FROM t_10;
+Warnings:
+Warning 1286 Unknown storage engine '$engine_table'
+Warning 1266 Using storage engine MyISAM for table 't_11'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+ERROR HY000: Table to exchange with partition is partitioned: 't_11'
+DROP TABLE IF EXISTS t_11;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp;
+ERROR HY000: Table to exchange with partition is partitioned: 'tsp'
+ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: Subpartitioned table, use subpartition instead of partition
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_100;
+ERROR HY000: Found row that does not match the partition
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_10;
+ERROR HY000: Found row that does not match the partition
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_3.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_3.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_3.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,17 @@
+use test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_4_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_4_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_4_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,76 @@
+CREATE DATABASE test_2;
+USE test;
+USE test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+SELECT * FROM test_2.t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+SELECT * FROM test_2.t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+SELECT * FROM test_2.tsp_00;
+a b
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+5 Five
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+SELECT * FROM test_2.tsp_00;
+a b
+5 Five
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+USE test_2;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+USE test;
+DROP DATABASE test_2;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_4_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_4_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_4_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,76 @@
+CREATE DATABASE test_2;
+USE test;
+USE test;
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+SELECT * FROM test_2.t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE test_2.t_10;
+SELECT * FROM test_2.t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+SELECT * FROM test_2.tsp_00;
+a b
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+5 Five
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE test_2.tsp_00;
+SELECT * FROM test_2.tsp_00;
+a b
+5 Five
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+USE test_2;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+USE test;
+DROP DATABASE test_2;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_5_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_5_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_5_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,114 @@
+CREATE USER test1@localhost;
+CREATE USER test2@localhost;
+GRANT USAGE ON *.* TO test1@localhost;
+GRANT USAGE ON *.* TO test2@localhost;
+GRANT CREATE, DROP, INSERT, SELECT ON test.* TO test1@localhost;
+GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
+connect (test1,localhost,test1,,test,MASTER_MYPORT,MASTER_MYSOCK);
+SELECT current_user();
+current_user()
+test1@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test1@localhost
+GRANT USAGE ON *.* TO 'test1'@'localhost'
+GRANT SELECT, INSERT, CREATE, DROP ON `test`.* TO 'test1'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test1'@'localhost' for table 'tp'
+disconnect test1;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+current_user()
+test2@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+5 Five
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test2;
+connection default;
+REVOKE ALTER ON test.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+current_user()
+test2@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tp'
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tp'
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tsp'
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tsp'
+connection default;
+disconnect test2;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test1@localhost;
+DROP USER test2@localhost;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_5_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_5_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_5_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,114 @@
+CREATE USER test1@localhost;
+CREATE USER test2@localhost;
+GRANT USAGE ON *.* TO test1@localhost;
+GRANT USAGE ON *.* TO test2@localhost;
+GRANT CREATE, DROP, INSERT, SELECT ON test.* TO test1@localhost;
+GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
+connect (test1,localhost,test1,,test,MASTER_MYPORT,MASTER_MYSOCK);
+SELECT current_user();
+current_user()
+test1@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test1@localhost
+GRANT USAGE ON *.* TO 'test1'@'localhost'
+GRANT SELECT, INSERT, CREATE, DROP ON `test`.* TO 'test1'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test1'@'localhost' for table 'tp'
+disconnect test1;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+current_user()
+test2@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+5 Five
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test2;
+connection default;
+REVOKE ALTER ON test.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SELECT current_user();
+current_user()
+test2@localhost
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tp'
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tp'
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tsp'
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR 42000: ALTER command denied to user 'test2'@'localhost' for table 'tsp'
+connection default;
+disconnect test2;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test1@localhost;
+DROP USER test2@localhost;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_6.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_6.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_6.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,105 @@
+CREATE USER test2@localhost;
+GRANT USAGE ON *.* TO test2@localhost;
+GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+5 Five
+6 Six
+8 Eight
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+SELECT * FROM tsp_00;
+a b
+5 Five
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test2;
+connection default;
+REVOKE INSERT ON test.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 'tp'
+disconnect test2;
+connection default;
+GRANT INSERT ON test.* TO test2@localhost;
+REVOKE CREATE ON test.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, DROP, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+ERROR 42000: CREATE command denied to user 'test2'@'localhost' for table 'tsp'
+disconnect test2;
+connection default;
+GRANT CREATE ON test.* TO test2@localhost;
+REVOKE DROP ON test.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+Grants for test2@localhost
+GRANT USAGE ON *.* TO 'test2'@'localhost'
+GRANT SELECT, INSERT, UPDATE, CREATE, ALTER ON `test`.* TO 'test2'@'localhost'
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: DROP command denied to user 'test2'@'localhost' for table 'tp'
+disconnect test2;
+connection default;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test2@localhost;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_7_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_7_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_7_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,82 @@
+CREATE USER test_2@localhost;
+send
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+connect (test_2,localhost,test_2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+INSERT INTO tp VALUES (7,"Seven");
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+connection default;
+reap;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+connection test_2;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test_2;
+connection default;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test_2@localhost;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_7_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_7_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_7_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,82 @@
+CREATE USER test_2@localhost;
+send
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+connect (test_2,localhost,test_2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+INSERT INTO tp VALUES (7,"Seven");
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+connection default;
+reap;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+connection test_2;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test_2;
+connection default;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+7 Seven
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test_2@localhost;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_8_innodb.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_8_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_8_innodb.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,83 @@
+CREATE USER test2@localhost;
+CREATE DATABASE testdb;
+USE testdb;
+USE test;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on test.* TO test2@localhost;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on testdb.* TO test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+SELECT * FROM testdb.t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+SELECT * FROM testdb.t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test2;
+connection default;
+REVOKE INSERT ON testdb.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 't_10'
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 'tp'
+disconnect test2;
+connection default;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test2@localhost;
+DROP DATABASE testdb;
=== added file 'mysql-test/suite/parts/r/partition_exch_qa_8_myisam.result'
--- a/mysql-test/suite/parts/r/partition_exch_qa_8_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_exch_qa_8_myisam.result 2010-10-28 12:56:41 +0000
@@ -0,0 +1,83 @@
+CREATE USER test2@localhost;
+CREATE DATABASE testdb;
+USE testdb;
+USE test;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on test.* TO test2@localhost;
+GRANT CREATE, ALTER, DROP, INSERT, SELECT on testdb.* TO test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+SELECT * FROM testdb.t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+SELECT * FROM testdb.t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+SELECT * FROM t_10;
+a b
+1 One
+3 Three
+5 Five
+9 Nine
+SELECT * FROM testdb.tp WHERE a BETWEEN 0 AND 10;
+a b
+2 Two
+4 Four
+6 Six
+8 Eight
+disconnect test2;
+connection default;
+REVOKE INSERT ON testdb.* FROM test2@localhost;
+connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE testdb.t_10;
+ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 't_10'
+ALTER TABLE testdb.tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 'tp'
+disconnect test2;
+connection default;
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS tsp_00;
+DROP TABLE IF EXISTS tsp_01;
+DROP TABLE IF EXISTS tsp_02;
+DROP TABLE IF EXISTS tsp_03;
+DROP TABLE IF EXISTS tsp_04;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+DROP USER test2@localhost;
+DROP DATABASE testdb;
=== added file 'mysql-test/suite/parts/t/partition_exch_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_myisam_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,21 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--error 1497
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_10.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_10.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_10.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,69 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/not_embedded.inc
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+# 17) Exchanges in Procedure, triggers and events.
+DELIMITER |;
+CREATE PROCEDURE test_p1 ()
+BEGIN
+ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a);
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+ALTER TABLE tp DROP INDEX a;
+ALTER TABLE t_10 DROP INDEX a;
+END|
+DELIMITER ;|
+CALL test_p1;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 9;
+DROP PROCEDURE test_p1;
+
+SET @save_autocommit= @@autocommit;
+SET @@autocommit= OFF;
+SHOW VARIABLES LIKE '%autocommit%';
+DELIMITER |;
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER test_trg_1 BEFORE UPDATE ON tp FOR EACH ROW
+BEGIN
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+END|
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION test_f_1() RETURNS int
+BEGIN
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+END|
+DELIMITER ;|
+SET @@autocommit= @save_autocommit;
+
+SET @save_event_scheduler= @@global.event_scheduler;
+SET @@global.event_scheduler= ON;
+CREATE EVENT test_ev_1
+ ON SCHEDULE AT CURRENT_TIMESTAMP
+ DO
+ ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+sleep 1;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 9;
+SET @@global.event_scheduler= @save_event_scheduler;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_11.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_11.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_11.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,37 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+# 13) Exchange with dynamic variables.
+SET @part= 'p0';
+SET @part_tab= 'tp';
+SET @table= 't_10';
+SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ',@part,' WITH TABLE ',@table);
+PREPARE test_stmt1 FROM @s;
+EXECUTE test_stmt1;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp;
+DEALLOCATE PREPARE test_stmt1;
+
+SET @part_tab= 'tp';
+SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ? WITH TABLE ?');
+--error ER_PARSE_ERROR
+PREPARE test_stmt2 FROM @s;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_12.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_12.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_12.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,176 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--disable_warnings
+DROP TABLE IF EXISTS t_10;
+DROP TABLE IF EXISTS t_100;
+DROP TABLE IF EXISTS t_1000;
+DROP TABLE IF EXISTS tp;
+DROP TABLE IF EXISTS tsp;
+DROP TABLE IF EXISTS t_empty;
+DROP TABLE IF EXISTS t_null;
+--enable_warnings
+
+eval CREATE TABLE t_10 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+CHECKSUM= 1,
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_100 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+COMMENT= 'comment',
+ROW_FORMAT= COMPRESSED,
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_1000 (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+MIN_ROWS= 1,
+MAX_ROWS= 200,
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_empty (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+ENGINE = $engine_table;
+
+eval CREATE TABLE t_null (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+ENGINE = $engine_table;
+
+eval CREATE TABLE tp (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+ENGINE = $engine_part
+PARTITION BY RANGE (a)
+(PARTITION p0 VALUES LESS THAN (10),
+ PARTITION p1 VALUES LESS THAN (100),
+ PARTITION p2 VALUES LESS THAN (1000));
+
+eval CREATE TABLE tsp (a INT,
+ b VARCHAR(55),
+ PRIMARY KEY (a))
+ENGINE = $engine_subpart
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH(a)
+(PARTITION p0 VALUES LESS THAN (10)
+ (SUBPARTITION sp00,
+ SUBPARTITION sp01,
+ SUBPARTITION sp02,
+ SUBPARTITION sp03,
+ SUBPARTITION sp04),
+ PARTITION p1 VALUES LESS THAN (100)
+ (SUBPARTITION sp10,
+ SUBPARTITION sp11,
+ SUBPARTITION sp12,
+ SUBPARTITION sp13,
+ SUBPARTITION sp14),
+ PARTITION p2 VALUES LESS THAN (1000)
+ (SUBPARTITION sp20,
+ SUBPARTITION sp21,
+ SUBPARTITION sp22,
+ SUBPARTITION sp23,
+ SUBPARTITION sp24));
+
+# Values t_10 (not partitioned)
+INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
+
+# Values t_100 (not partitioned)
+INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
+INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
+
+# Values t_1000 (not partitioned)
+INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
+INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
+INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
+INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
+
+# Values t_null (not partitioned)
+INSERT INTO t_null VALUES (1, "NULL");
+
+# Values tp (partitions)
+INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+# Values tps (subpartitions)
+INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
+INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
+INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
+INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
+INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
+INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
+
+eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
+eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
+eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
+eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
+eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
+ AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
+
+SHOW CREATE TABLE t_10;
+SHOW CREATE TABLE t_100;
+SHOW CREATE TABLE t_1000;
+SHOW CREATE TABLE tp;
+SHOW CREATE TABLE tsp;
+
+--enable_result_log
+--enable_query_log
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+
+# 13) Exchanges with different table options.
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100 IGNORE;
+--error 1705
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000;
+--error 1705
+ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000 IGNORE;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_13.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_13.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_13.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_13.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_14.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_14.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_14.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,92 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+# DATA DIRECTORY
+# Make directory for partition data
+let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
+--mkdir $data_dir_path
+let $p_data_directory= DATA DIRECTORY = '$data_dir_path';
+let $data_directory= DATA DIRECTORY = '$data_dir_path';
+
+# INDEX DIRECTORY
+# Make directory for partition index
+let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
+--mkdir $idx_dir_path
+let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path';
+let $index_directory= INDEX DIRECTORY = '$idx_dir_path';
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+--rmdir $data_dir_path
+--rmdir $idx_dir_path
+
+# DATA DIRECTORY
+# Make directory for partition data
+let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
+--mkdir $data_dir_path
+let $p_data_directory= DATA DIRECTORY = '$data_dir_path';
+
+# INDEX DIRECTORY
+# Make directory for partition index
+let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
+--mkdir $idx_dir_path
+let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path';
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+--rmdir $data_dir_path
+--rmdir $idx_dir_path
+
+# DATA DIRECTORY
+# Make directory for partition data
+let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
+--mkdir $data_dir_path
+let $data_directory= DATA DIRECTORY = '$data_dir_path';
+
+# INDEX DIRECTORY
+# Make directory for partition index
+let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
+--mkdir $idx_dir_path
+let $index_directory= INDEX DIRECTORY = '$idx_dir_path';
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--error 1710
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+#--rmdir $data_dir_path
+#--rmdir $idx_dir_path
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_15.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_15.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_15.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,31 @@
+# Author: Horst Hunger
+# Created: 2010-07-15
+
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+# 21) Foreign Key.
+# Exchange of partition with table differing in structure.
+CREATE TABLE t_11 (a INT, b VARCHAR(55),
+ FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE)
+ ENGINE= InnoDB;
+#--error ER_TABLES_DIFFERENT_METADATA
+--error 1718
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+--disable_warnings
+DROP TABLE IF EXISTS t_11;
+--enable_warnings
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa_1.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-13
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_1.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_2.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_2.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_2.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,80 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+
+# 3) Invalid exchanges.
+# Exchange of partition with table differing in structure.
+CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10;
+--error ER_TABLES_DIFFERENT_METADATA
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+--disable_warnings
+DROP TABLE IF EXISTS t_11;
+--enable_warnings
+CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table SELECT * FROM t_10;
+--error ER_TABLES_DIFFERENT_METADATA
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+--disable_warnings
+DROP TABLE IF EXISTS t_11;
+CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10;
+--error ER_MIX_HANDLER_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+--disable_warnings
+DROP TABLE IF EXISTS t_11;
+--enable_warnings
+# Exchange of partition with partitioned table.
+CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table
+ PARTITION BY KEY() AS SELECT * FROM t_10;
+--error ER_PARTITION_EXCHANGE_PART_TABLE
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11;
+--disable_warnings
+DROP TABLE IF EXISTS t_11;
+--enable_warnings
+# Exchange of subpartition with partitioned table.
+--error ER_PARTITION_EXCHANGE_PART_TABLE
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp;
+# Exchange of subpartitioned partition with table.
+--error ER_PARTITION_INSTEAD_OF_SUBPARTITION
+ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10;
+# Exchange of values in partition not fitting the hash.
+--error ER_ROW_DOES_NOT_MATCH_PARTITION
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_100;
+# Exchange of values in subpartition not fitting the hash.
+--error ER_ROW_DOES_NOT_MATCH_PARTITION
+ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_10;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_3.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_3.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_3.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,46 @@
+# Author: Horst Hunger
+# Created: 2010-07-05
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+use test;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM t_100;
+--sorted_result
+SELECT * FROM t_1000;
+--sorted_result
+SELECT * FROM tp;
+--sorted_result
+SELECT * FROM tsp;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp_01;
+--sorted_result
+SELECT * FROM tsp_02;
+--sorted_result
+SELECT * FROM tsp_03;
+--sorted_result
+SELECT * FROM tsp_04;
+--enable_result_log
+--enable_query_log
+
+# 5) Exchanges with different engines.
+--error ER_MIX_HANDLER_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--error ER_MIX_HANDLER_ERROR
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa_4.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_4.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa_5.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_5.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_6.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_6.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_6.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,102 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/not_embedded.inc
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+CREATE USER test2@localhost;
+
+--disable_result_log
+--disable_query_log
+--source suite/parts/inc/part_exch_tabs.inc
+--enable_result_log
+--enable_query_log
+
+GRANT USAGE ON *.* TO test2@localhost;
+GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+# 9) Exchanges with different owner.
+# Privilege for ALTER and SELECT
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--sorted_result
+SELECT * FROM t_10;
+--sorted_result
+SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+# Back to former values.
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+--sorted_result
+SELECT * FROM tsp_00;
+--sorted_result
+SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+REVOKE INSERT ON test.* FROM test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+# Privilege for ALTER and SELECT
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+GRANT INSERT ON test.* TO test2@localhost;
+REVOKE CREATE ON test.* FROM test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+GRANT CREATE ON test.* TO test2@localhost;
+REVOKE DROP ON test.* FROM test2@localhost;
+
+--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK);
+connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+USE test;
+SHOW GRANTS FOR CURRENT_USER;
+# Privilege for ALTER and SELECT
+--error ER_TABLEACCESS_DENIED_ERROR
+ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
+--echo disconnect test2;
+disconnect test2;
+
+--echo connection default;
+connection default;
+
+--source suite/parts/inc/part_exch_drop_tabs.inc
+
+DROP USER test2@localhost;
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa_7.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-06
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_7.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,12 @@
+# Author: Horst Hunger
+# Created: 2010-07-07
+
+--source include/have_partition.inc
+--source include/have_innodb.inc
+
+let $engine_table= InnoDB;
+let $engine_part= InnoDB;
+let $engine_subpart= InnoDB;
+
+--source suite/parts/inc/part_exch_qa_8.inc
+
=== added file 'mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test'
--- a/mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test 2010-10-28 12:56:41 +0000
@@ -0,0 +1,11 @@
+# Author: Horst Hunger
+# Created: 2010-07-07
+
+--source include/have_partition.inc
+
+let $engine_table= MYISAM;
+let $engine_part= MYISAM;
+let $engine_subpart= MYISAM;
+
+--source suite/parts/inc/part_exch_qa_8.inc
+
Attachment: [text/bzr-bundle] bzr/horst.hunger@sun.com-20101028125641-fjo11s04guxc5tdm.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (horst.hunger:3341) WL#4445 | Horst.Hunger | 28 Oct |
