path: root/mysql-test/suite/parts/t/alter_table.test

# 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;