Description:
When REORGANIZE partition statement is used with no TABLESPACE option, the partition is moved to table level tablespace option.
This is wrong as table level tablespace option works more as default option for partitions on a partitioned table and it makes sense to use it only while adding a new partition to the table and during create table.
How to repeat:
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd';
CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY (a))
ENGINE=InnoDB TABLESPACE ts2
PARTITION BY RANGE(a)
PARTITIONS 4
( PARTITION P1 VALUES LESS THAN (2) TABLESPACE ts1,
PARTITION P2 VALUES LESS THAN (4) TABLESPACE innodb_system,
PARTITION P3 VALUES LESS THAN (6),
PARTITION P4 VALUES LESS THAN (8) TABLESPACE innodb_file_per_table);
SHOW CREATE TABLE t1;
ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (PARTITION p4 VALUES LESS THAN (8));
SHOW CREATE TABLE t1;
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
FROM information_schema.innodb_sys_tables A
LEFT JOIN
information_schema.innodb_sys_tablespaces B
ON A.SPACE = B.SPACE
WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (6) TABLESPACE innodb_system);
SHOW CREATE TABLE t1;
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
FROM information_schema.innodb_sys_tables A
LEFT JOIN
information_schema.innodb_sys_tablespaces B
ON A.SPACE = B.SPACE
WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
DROP TABLE t1;
