#
# MDEV-5271 Support engine-defined attributes per partition
#
# partitioned tables
CREATE TABLE `t1` (
`id` INT
) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1,
PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1
);
SHOW CREATE TABLE `t1`;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='YES'
 PARTITION BY RANGE (`id`)
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO' ENCRYPTION_KEY_ID = 1,
 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT' ENCRYPTION_KEY_ID = 1)
INSERT INTO t1 VALUES (1), (2), (3);
DELETE FROM t1 WHERE id = 1;
UPDATE t1 SET id = 4 WHERE id = 3;
SELECT * FROM t1 WHERE id IN (2, 3);
id
2
DROP TABLE `t1`;
CREATE TABLE `t2` (
`id` INT
) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100),
PARTITION pt2 VALUES LESS THAN MAXVALUE
);
ERROR HY000: Can't create table `test`.`t2` (errno: 140 "Wrong create options")
CREATE TABLE `t3` (
`id` INT
) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
PARTITION pt2 VALUES LESS THAN MAXVALUE
);
ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options")
CREATE TABLE `t4` (
`id` INT
) ENGINE=InnoDB ENCRYPTED="NO";
SHOW CREATE TABLE `t4`;
Table	Create Table
t4	CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='NO'
ALTER TABLE `t4` PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO",
PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
);
SHOW CREATE TABLE `t4`;
Table	Create Table
t4	CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='NO'
 PARTITION BY RANGE (`id`)
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO',
 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT')
ALTER TABLE `t4` PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100),
PARTITION pt2 VALUES LESS THAN MAXVALUE
);
SHOW CREATE TABLE `t4`;
Table	Create Table
t4	CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='NO'
 PARTITION BY RANGE (`id`)
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
ALTER TABLE `t4` PARTITION BY RANGE(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
);
ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options")
DROP TABLE `t4`;
# subpartitioned tables
CREATE TABLE `t5` (
`id` INT
) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id)
SUBPARTITIONS 2 (
PARTITION pt1 VALUES LESS THAN (100),
PARTITION pt2 VALUES LESS THAN MAXVALUE
);
SHOW CREATE TABLE `t5`;
Table	Create Table
t5	CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='NO'
 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
SUBPARTITIONS 2
(PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
DROP TABLE `t5`;
CREATE TABLE `t6` (
`id` INT
) ENGINE=InnoDB PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id)
SUBPARTITIONS 2 (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES",
PARTITION pt2 VALUES LESS THAN MAXVALUE
);
ERROR HY000: Can't create table `test`.`t6` (errno: 140 "Wrong create options")
CREATE TABLE `t7` (
id INT
) ENGINE=InnoDB PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id) (
PARTITION pt1 VALUES LESS THAN (100)(
SUBPARTITION spt1 ENCRYPTED="NO",
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE (
SUBPARTITION spt3,
SUBPARTITION spt4
)
);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENCRYPTED="NO",
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE ...' at line 6
CREATE TABLE `t8` (
id INT
) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id) (
PARTITION pt1 VALUES LESS THAN (100) (
SUBPARTITION spt1,
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE (
SUBPARTITION spt3,
SUBPARTITION spt4
)
);
SHOW CREATE TABLE `t8`;
Table	Create Table
t8	CREATE TABLE `t8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='NO'
 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
(PARTITION `pt1` VALUES LESS THAN (100)
 (SUBPARTITION `spt1` ENGINE = InnoDB,
  SUBPARTITION `spt2` ENGINE = InnoDB),
 PARTITION `pt2` VALUES LESS THAN MAXVALUE
 (SUBPARTITION `spt3` ENGINE = InnoDB,
  SUBPARTITION `spt4` ENGINE = InnoDB))
DROP TABLE `t8`;
CREATE TABLE `t9` (
id INT
) ENGINE=InnoDB PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
SUBPARTITION spt1,
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE (
SUBPARTITION spt3,
SUBPARTITION spt4
)
);
SHOW CREATE TABLE `t9`;
Table	Create Table
t9	CREATE TABLE `t9` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
(PARTITION `pt1` VALUES LESS THAN (100)
 (SUBPARTITION `spt1` ENGINE = InnoDB,
  SUBPARTITION `spt2` ENGINE = InnoDB),
 PARTITION `pt2` VALUES LESS THAN MAXVALUE
 (SUBPARTITION `spt3` ENGINE = InnoDB,
  SUBPARTITION `spt4` ENGINE = InnoDB))
DROP TABLE `t9`;
CREATE TABLE `t10` (
id INT
) ENGINE=InnoDB PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" (
SUBPARTITION spt1,
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE (
SUBPARTITION spt3,
SUBPARTITION spt4
)
);
ERROR HY000: Can't create table `test`.`t10` (errno: 140 "Wrong create options")
CREATE TABLE `t11` (
id INT
) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id)
SUBPARTITION BY HASH(id) (
PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
SUBPARTITION spt1,
SUBPARTITION spt2
),
PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" (
SUBPARTITION spt3,
SUBPARTITION spt4
)
);
SHOW CREATE TABLE `t11`;
Table	Create Table
t11	CREATE TABLE `t11` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTED`='YES'
 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
(PARTITION `pt1` VALUES LESS THAN (100)
 (SUBPARTITION `spt1` ENGINE = InnoDB,
  SUBPARTITION `spt2` ENGINE = InnoDB),
 PARTITION `pt2` VALUES LESS THAN MAXVALUE
 (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 COLLATE=latin1_swedish_ci `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 COLLATE=latin1_swedish_ci `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`;