1 files changed, 321 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/t/alter_table.test b/mysql-test/suite/parts/t/alter_table.test
new file mode 100644
index 00000000..9b75add3
--- /dev/null
+++ b/mysql-test/suite/parts/t/alter_table.test
@@ -0,0 +1,321 @@
+# Permissions don't work with embedded
+--source include/not_embedded.inc
+--source include/have_partition.inc
+--source include/lcase_names.inc
+--source suite/parts/inc/engines.inc
+
+#
+# MDEV-22649 SIGSEGV in ha_partition::create_partitioning_metadata on ALTER
+#
+
+set @save_alter_algorithm= @@session.alter_algorithm;
+SET SESSION alter_algorithm=4;
+CREATE TABLE t1(a INT) engine=myisam PARTITION BY RANGE(a) SUBPARTITION BY KEY(a) (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION s0,SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (20) (SUBPARTITION s2,SUBPARTITION s3));
+show create table t1;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t1 ADD COLUMN c INT;
+DROP table if exists t1;
+set @@session.alter_algorithm= @save_alter_algorithm;
+
+
+#
+# MDEV-22804 SIGSEGV in ha_partition::create_partitioning_metadata |
+# ERROR 1507 (HY000): Error in list of partitions to DROP
+#
+
+CREATE TABLE t1 (a INT) PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (PARTITION p VALUES LESS THAN (5) (SUBPARTITION sp, SUBPARTITION sp1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION sp2, SUBPARTITION sp3));
+ALTER TABLE t1 DROP PARTITION p;
+DROP TABLE if exists t1;
+
+#
+# MDEV-23357 Server crashes in Sql_cmd_alter_table_exchange_partition::exchange_partition
+#
+CREATE TABLE t1 (i INT);
+CREATE VIEW v1 as SELECT * FROM t1;
+CREATE TABLE t2 (i INT);
+--error ER_CHECK_NO_SUCH_TABLE
+ALTER TABLE v1 EXCHANGE PARTITION p2 WITH TABLE t2 ;
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-22165 CONVERT PARTITION: move in partition from existing table
+--echo #
+create or replace table tp1 (a int);
+create or replace table t1 (a int)
+partition by hash (a) partitions 2;
+--error ER_ONLY_ON_RANGE_LIST_PARTITION
+alter table t1 convert table tp1 to partition p2;
+
+create or replace table t1 (a int)
+partition by range (a)
+(partition p0 values less than (0));
+
+--error ER_NO_SUCH_TABLE
+alter table t1 convert table non_existent to partition p1 values less than (10);
+alter table t1 convert table tp1 to partition p1 values less than (10);
+--error ER_NO_SUCH_TABLE
+show create table tp1;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+
+create table tp2 (x int);
+--error ER_TABLES_DIFFERENT_METADATA
+alter table t1 convert table tp2 to partition p2 values less than (20);
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table tp2;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+
+create or replace table tp2 (a int);
+insert tp2 values (1), (15), (17);
+--error ER_ROW_DOES_NOT_MATCH_PARTITION
+alter table t1 convert table tp2 to partition p2 values less than (20);
+delete from tp2;
+insert tp2 values (15), (1), (17);
+--error ER_ROW_DOES_NOT_MATCH_PARTITION
+alter table t1 convert table tp2 to partition p2 values less than (20);
+delete from tp2;
+insert tp2 values (15), (17), (1);
+--error ER_ROW_DOES_NOT_MATCH_PARTITION
+alter table t1 convert table tp2 to partition p2 values less than (20);
+delete from tp2;
+insert tp2 values (15), (17);
+alter table t1 convert table tp2 to partition p2 values less than (20);
+--error ER_NO_SUCH_TABLE
+show create table tp2;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+select * from t1 partition (p2);
+
+create or replace table t1 (a int)
+partition by range (a) (
+p0 values less than (0),
+pn values less than (30));
+insert into t1 values (1);
+create or replace table tp1 (a int);
+insert into tp1 values (2);
+# TODO: would be good to automatically detect order of partitions,
+# as well as move the data from succeeding partitions (ADD PARTITION FR).
+--error ER_RANGE_NOT_INCREASING_ERROR
+alter table t1 convert table tp1 to partition p1 values less than (10);
+
+drop tables t1, tp1;
+
+
+--echo #
+--echo # MDEV-22166 CONVERT PARTITION: move out partition into a table
+--echo #
+
+create or replace table t1 (x int);
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+alter table t1 convert partition p1 to table tp1;
+
+create or replace table t1 (x int)
+partition by hash(x) partitions 2;
+--error ER_ONLY_ON_RANGE_LIST_PARTITION
+alter table t1 convert partition p1 to table tp1;
+
+create or replace table t1 (x int)
+partition by key(x) partitions 2;
+--error ER_ONLY_ON_RANGE_LIST_PARTITION
+alter table t1 convert partition p1 to table tp1;
+
+create or replace table t1 (x int)
+partition by range(x)
+subpartition by hash(x) subpartitions 3 (
+  partition p1 values less than (10),
+  partition pn values less than maxvalue);
+--error ER_PARTITION_CONVERT_SUBPARTITIONED
+alter table t1 convert partition p1 to table p1;
+--error ER_PARTITION_DOES_NOT_EXIST
+alter table t1 convert partition p1sp0 to table p1;
+
+create or replace table t1 (x int)
+partition by range(x) (
+  partition p1 values less than (10));
+--error ER_DROP_LAST_PARTITION
+alter table t1 convert partition p1 to table tp1;
+
+--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
+create or replace temporary table t1 (x int)
+partition by range(x) (
+  partition p0 values less than (10),
+  partition pn values less than maxvalue);
+
+if ($MTR_COMBINATION_RANGE)
+{
+  create or replace table t1 (x int)
+  partition by range(x) (
+    partition p1 values less than (10),
+    partition p2 values less than (20),
+    partition p3 values less than (30),
+    partition p4 values less than (40),
+    partition p5 values less than (50),
+    partition pn values less than maxvalue);
+}
+
+if ($MTR_COMBINATION_LIST)
+{
+  create or replace table t1 (x int)
+  partition by list(x) (
+    partition p1 values in (2, 3, 4),
+    partition p2 values in (12, 13, 14),
+    partition p3 values in (22, 23, 24),
+    partition p4 values in (32, 33, 34),
+    partition p5 values in (42, 43, 44),
+    partition pn values in (52, 53, 54));
+}
+
+insert into t1 values (2), (12), (22), (32), (42), (52);
+
+create or replace table tp2 (y int);
+insert tp2 values (88);
+# Multiple ALTER PARTITION statements are not possible
+--error ER_PARSE_ERROR
+alter table t1 convert partition p2 to table tp2, drop partition p3;
+# TODO: probably no need in such specific codes, should be ER_PARTITION_NON_EXISTENT
+--error ER_PARTITION_DOES_NOT_EXIST
+alter table t1 convert partition p00 to table tp00;
+# Better error here is ER_PARTITION_DOES_NOT_EXIST,
+# but mysql_alter_table() works checks new table before anything else.
+# So, looks like no big reason to change anything here.
+--error ER_TABLE_EXISTS_ERROR
+alter table t1 convert partition p00 to table tp2;
+--error ER_TABLE_EXISTS_ERROR
+alter table t1 convert partition p2 to table tp2;
+--delimiter $
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger tr1 before update on t1 for each row
+begin
+  alter table t1 convert partition p2 to table tp2;
+end$
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+create function f1() returns int
+begin
+  alter table t1 convert partition p2 to table tp2;
+end$
+--delimiter ;
+select * from tp2;
+drop table tp2;
+alter table t1 convert partition p2 to table tp2;
+
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table tp2;
+select * from tp2;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+select * from t1 order by x;
+
+--error ER_BAD_DB_ERROR
+alter table t1 convert partition p3 to table inexistent.tp3;
+create database EXISTENT;
+alter table t1 convert partition p3 to table EXISTENT.TP3;
+
+# The only way to put `` into var...
+--let $tp3=`select '`TP3`'`
+if ($MTR_COMBINATION_LCASE1)
+{
+  --let $tp3= `select '`tp3`'`
+}
+--replace_result $engine X ' PAGE_CHECKSUM=1' '' $tp3 `TP3`-ok
+show create table EXISTENT.TP3;
+select * from EXISTENT.TP3 order by x;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+select * from t1 order by x;
+
+--echo # LOCK TABLES
+lock tables t1 write;
+alter table t1 convert partition p4 to table tp4;
+# TODO: lock table tp4 in ALTER TABLE, otherwise there is no
+# guarantee in data consistency between t1 and tp4
+--error ER_TABLE_NOT_LOCKED
+show create table tp4;
+
+unlock tables;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table tp4;
+select * from tp4;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+select * from t1 order by x;
+
+--echo # PS
+prepare stmt from 'alter table t1 convert partition p5 to table tp5';
+execute stmt;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table tp5;
+select * from tp5;
+--replace_result $engine X ' PAGE_CHECKSUM=1' ''
+show create table t1;
+select * from t1 order by x;
+drop table tp5;
+--error ER_PARTITION_DOES_NOT_EXIST
+execute stmt;
+--error ER_PARTITION_DOES_NOT_EXIST
+execute stmt;
+drop prepare stmt;
+
+--echo # Privileges
+create user alan;
+grant usage on *.* to alan;
+grant create, insert, drop on test.* to alan;
+--connect alan,localhost,alan,,test
+show grants for current_user;
+--error ER_TABLEACCESS_DENIED_ERROR
+alter table t1 convert partition p1 to table tp1;
+--connection default
+revoke all on test.* from alan;
+grant create, insert, alter on test.* to alan;
+--connection alan
+use test;
+show grants for current_user;
+--error ER_TABLEACCESS_DENIED_ERROR
+alter table t1 convert partition p1 to table tp1;
+--connection default
+revoke all on test.* from alan;
+grant create, drop, alter on test.* to alan;
+--connection alan
+use test;
+show grants for current_user;
+--error ER_TABLEACCESS_DENIED_ERROR
+alter table t1 convert partition p1 to table tp1;
+--connection default
+revoke all on test.* from alan;
+grant insert, drop, alter on test.* to alan;
+--connection alan
+use test;
+show grants for current_user;
+--error ER_TABLEACCESS_DENIED_ERROR
+alter table t1 convert partition p1 to table tp1;
+--connection default
+grant create, insert, drop, alter on test.* to alan;
+--connection alan
+use test;
+show grants for current_user;
+alter table t1 convert partition p1 to table tp1;
+--disconnect alan
+--connection default
+
+drop database EXISTENT;
+drop user alan;
+drop tables t1, tp1, tp2, tp4;
+
+--echo #
+--echo # MDEV-31014 Database privileges are insufficient for CONVERT TABLE TO PARTITION
+--echo #
+create database db;
+create user u@localhost;
+grant all on db.* to u@localhost;
+
+--connect (con1,localhost,u,,db)
+create table t1 (a int) partition by range(a) (p1 values less than (100), p2 values less than (1000));
+alter table t1 convert partition p2 to table tp;
+alter table t1 convert table tp to partition p2 values less than (1000);
+
+# Cleanup
+--disconnect con1
+--connection default
+drop user u@localhost;
+drop database db;