MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values

When a partition with engine-defined partition options is added by
ALTER, it shows the correct definition in SHOW CREATE, but the actual
values get inherited from the previous partition.

The cause of the bug is that the server did not parse the options
associated with the newly added partition.

In the case of ALTER, the complete part_info, which represents the
partition structure after ALTER, is built by prep_alter_part_table().
So, we need to parse engine-defined partition options after the call
of the function.

2 files changed, 117 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/r/engine_defined_part_attributes.result b/mysql-test/suite/parts/r/engine_defined_part_attributes.result
index 81063d82c6f..f31d5e901fb 100644
--- a/mysql-test/suite/parts/r/engine_defined_part_attributes.result
+++ b/mysql-test/suite/parts/r/engine_defined_part_attributes.result
@@ -216,3 +216,73 @@ SUBPARTITION BY HASH (`id`)
  (SUBPARTITION `spt3` ENGINE = InnoDB,
   SUBPARTITION `spt4` ENGINE = InnoDB))
 DROP TABLE `t11`;
+#
+# MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values
+#
+# restart: --innodb-sys-tablespaces
+CREATE TABLE `t12` (
+id INT
+) ENGINE=InnoDB PARTITION BY HASH(id)
+(
+pt1 PAGE_COMPRESSED=0
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+name	flag
+test/t12#P#pt1	21
+ALTER TABLE `t12` ADD PARTITION (
+PARTITION pt2 PAGE_COMPRESSED=1
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+name	flag
+test/t12#P#pt1	21
+test/t12#P#pt2	1610612789
+ALTER TABLE `t12` ADD PARTITION (
+PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+name	flag
+test/t12#P#pt1	21
+test/t12#P#pt2	1610612789
+test/t12#P#pt3	805306421
+DROP TABLE `t12`;
+CREATE TABLE `t13` (
+`id` INT
+) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) (
+PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0,
+PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
+PARTITION pt3 VALUES LESS THAN MAXVALUE
+);
+SHOW CREATE TABLE `t13`;
+Table	Create Table
+t13	CREATE TABLE `t13` (
+  `id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`=1
+ PARTITION BY RANGE (`id`)
+(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 0,
+ PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
+ PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
+name	flag
+test/t13#P#pt1	21
+test/t13#P#pt2	805306421
+test/t13#P#pt3	1610612789
+ALTER TABLE `t13` PARTITION BY RANGE(id) (
+PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
+PARTITION pt2 VALUES LESS THAN (200),
+PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0
+);
+SHOW CREATE TABLE `t13`;
+Table	Create Table
+t13	CREATE TABLE `t13` (
+  `id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`=1
+ PARTITION BY RANGE (`id`)
+(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
+ PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB,
+ PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB PAGE_COMPRESSED = 0)
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
+name	flag
+test/t13#P#pt1	805306421
+test/t13#P#pt2	1610612789
+test/t13#P#pt3	21
+DROP TABLE `t13`;
diff --git a/mysql-test/suite/parts/t/engine_defined_part_attributes.test b/mysql-test/suite/parts/t/engine_defined_part_attributes.test
index 8d08f4654cc..22aec9286e9 100644
--- a/mysql-test/suite/parts/t/engine_defined_part_attributes.test
+++ b/mysql-test/suite/parts/t/engine_defined_part_attributes.test
@@ -167,3 +167,50 @@ SUBPARTITION BY HASH(id) (
 SHOW CREATE TABLE `t11`;
 
 DROP TABLE `t11`;
+
+--echo #
+--echo # MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values
+--echo #
+
+--let $restart_parameters= --innodb-sys-tablespaces
+--source include/restart_mysqld.inc
+
+CREATE TABLE `t12` (
+  id INT
+) ENGINE=InnoDB PARTITION BY HASH(id)
+(
+  pt1 PAGE_COMPRESSED=0
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+
+ALTER TABLE `t12` ADD PARTITION (
+  PARTITION pt2 PAGE_COMPRESSED=1
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+
+ALTER TABLE `t12` ADD PARTITION (
+  PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
+);
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
+
+DROP TABLE `t12`;
+
+CREATE TABLE `t13` (
+  `id` INT
+) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) (
+    PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0,
+    PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
+    PARTITION pt3 VALUES LESS THAN MAXVALUE
+);
+SHOW CREATE TABLE `t13`;
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
+
+ALTER TABLE `t13` PARTITION BY RANGE(id) (
+    PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
+    PARTITION pt2 VALUES LESS THAN (200),
+    PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0
+);
+SHOW CREATE TABLE `t13`;
+SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
+
+DROP TABLE `t13`;