Avoid copying on REORGANIZE PARTITION where possible

[Hartmut Holzgraefe]

When modifying a range partition using ALTER TABLE...REORGANIZE PARTITION it may happen that all rows from the original partition end up in the same new partition. In this case the underlying table could simply be reused intead of being copied over as it happens now.

E.g.

CREATE TABLE `t1` (
`id` int auto_increment primary key,
) ENGINE=InnoDB
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (1500000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
;

INSERT INTO t1 SELECT NULL;
INSERT INTO t1 SELECT NULL FROM t1; -- repeat until > 1 million affected rows are reported

SELECT max(id) FROM t1; -- verify that number of rows is somehere between 1.6 and 2.9 million

ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (
  PARTITION p2 VALUES LESS THAN (3000000),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

All rows from the original p2 will end up in the new p2 anyway, so the REORGANIZE could just create the new empty p3 partition and modify the top level partition meta information, but the operation is actually taking quite some time as it simply seems to copy over all data from the old p2 partition to the new one ...

[Mclyn (mclyn-dimaculangan)]

ALTER TABLE table_name
 REORGANIZE PARTITION current_partition INTO
 (PARTITION current_partition VALUES LESS THAN (18000000),
 PARTITION new_partition VALUES LESS THAN MAXVALUE);

Executing this query took us 2-3 minutes and INSERT or UPDATE on the table cannot be performed during the partitioning operation. Fixing this bug as soon as possible would help us decrease our application's downtime. Thanks!