drop table if exists t1;
#
# Bug#62505: ALTER TABLE ADD PARTITION fails for LIST partitions with
#            more than 16 items
#
CREATE TABLE t1 (a INT);
# SUCCESS with 20 items because this is initial partitioning action
# (The parser already knows that it is only on column)
ALTER TABLE t1
PARTITION BY LIST(a)
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,
11,12,13,14,15,16,17,18,19,20));
# BUG: FAILED, because number of items > 16 during partition add
# (The parser do not know how many columns the table is partitioned on)
ALTER TABLE t1 ADD PARTITION
(PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`a`)
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB) */
# Test with single column LIST COLUMNS too
ALTER TABLE t1
PARTITION BY LIST COLUMNS (a)
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
ALTER TABLE t1 ADD PARTITION
(PARTITION p2 VALUES IN ((71),(72),(73),(74),(75),(76),(77),(78),(79),(80),
(81),(82),(83),(84),(85),(86),(87),(88),(89),(90)));
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 3
ALTER TABLE t1 ADD PARTITION
(PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(a)
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB) */
DROP TABLE t1;
# Test with two columns in LIST COLUMNS partitioning
CREATE TABLE t1
(a INT,
b CHAR(2))
PARTITION BY LIST COLUMNS (a, b)
(PARTITION p0_a VALUES IN
((0, "a0"), (0, "a1"), (0, "a2"), (0, "a3"), (0, "a4"), (0, "a5"), (0, "a6"),
(0, "a7"), (0, "a8"), (0, "a9"), (0, "aa"), (0, "ab"), (0, "ac"), (0, "ad"),
(0, "ae"), (0, "af"), (0, "ag"), (0, "ah"), (0, "ai"), (0, "aj"), (0, "ak"),
(0, "al")));
ALTER TABLE t1 ADD PARTITION
(PARTITION p1_a VALUES IN
((1, "a0"), (1, "a1"), (1, "a2"), (1, "a3"), (1, "a4"), (1, "a5"), (1, "a6"),
(1, "a7"), (1, "a8"), (1, "a9"), (1, "aa"), (1, "ab"), (1, "ac"), (1, "ad"),
(1, "ae"), (1, "af"), (1, "ag"), (1, "ah"), (1, "ai"), (1, "aj"), (1, "ak"),
(1, "al")));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(a,b)
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
 PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB) */
# Test of the parser for '('
ALTER TABLE t1 ADD PARTITION
(PARTITION p2_a VALUES IN
(((1 + 1), "a0"), (2, "a1"), (2, "a2"), (2, "a3"), (2, "a4"), (2, "a5"),
(2, "a6"), (2, "a7"), (2, "a8"), (2, "a9"), (2, "aa"), (2, "ab"), (2, "ac"),
(2, "ad"), (2, "ae"), (2, "af"), (2, "ag"), (2, "ah"), (2, "ai"), (2, "aj"),
(2, "ak"), (2, "al")));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(a,b)
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
 PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
 PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB) */
ALTER TABLE t1 ADD PARTITION
(PARTITION p3_a VALUES IN ((1 + 1 + 1), "a0"));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"a0"))' at line 2
ALTER TABLE t1 ADD PARTITION
(PARTITION p3_a VALUES IN (1 + 1 + 1, "a0"));
ERROR HY000: Inconsistency in usage of column lists for partitioning
# Test with 3 columns when it only has 2.
ALTER TABLE t1 ADD PARTITION
(PARTITION p3_a VALUES IN ((3, "a1", 0), (3, "a2", 0)));
ERROR HY000: Inconsistency in usage of column lists for partitioning
ALTER TABLE t1 ADD PARTITION
(PARTITION p3_a VALUES IN ((1 + 1 + 1, "a0")));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(a,b)
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
 PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
 PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB,
 PARTITION p3_a VALUES IN ((3,'a0')) ENGINE = InnoDB) */
# Test with more than 16 columns (cause of regression)
ALTER TABLE t1 ADD PARTITION
(PARTITION part_2 VALUES IN ((21 ,22, 23, 24, 25, 26, 27, 28, 29, 30,
31 ,32, 33, 34, 35, 36, 37, 38, 39, 40),
(41 ,42, 43, 44, 45, 46, 47, 48, 49, 50,
51 ,52, 53, 54, 55, 56, 57, 58, 59, 60)));
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 5
ALTER TABLE t1 ADD PARTITION
(PARTITION part_2 VALUES IN (21 ,22, 23, 24, 25, 26, 27, 28, 29, 30,
31 ,32, 33, 34, 35, 36, 37, 38, 39, 40));
ERROR HY000: Inconsistency in usage of column lists for partitioning
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(a,b)
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
 PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
 PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB,
 PARTITION p3_a VALUES IN ((3,'a0')) ENGINE = InnoDB) */
DROP TABLE t1;
create table t1 (a int unsigned)
partition by list (a)
(partition p0 values in (0),
partition p1 values in (1),
partition pnull values in (null),
partition p2 values in (2));
insert into t1 values (null),(0),(1),(2);
select * from t1 where a < 2;
a
0
1
select * from t1 where a <= 0;
a
0
select * from t1 where a < 1;
a
0
select * from t1 where a > 0;
a
1
2
select * from t1 where a > 1;
a
2
select * from t1 where a >= 0;
a
0
1
2
select * from t1 where a >= 1;
a
1
2
select * from t1 where a is null;
a
NULL
select * from t1 where a is not null;
a
0
1
2
select * from t1 where a is null or a > 0;
a
1
NULL
2
drop table t1;
create table t1 (a int unsigned, b int)
partition by list (a)
subpartition by hash (b)
subpartitions 2
(partition p0 values in (0),
partition p1 values in (1),
partition pnull values in (null, 2),
partition p3 values in (3));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
PARTITION_METHOD	PARTITION_EXPRESSION	PARTITION_DESCRIPTION
LIST	`a`	0
LIST	`a`	0
LIST	`a`	1
LIST	`a`	1
LIST	`a`	3
LIST	`a`	3
LIST	`a`	NULL,2
LIST	`a`	NULL,2
insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
insert into t1 values (2,0),(2,1),(3,0),(3,1);
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain select * from t1 where a is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` is null)
select * from t1 where a is null;
a	b
NULL	0
NULL	1
explain select * from t1 where a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 2)
select * from t1 where a = 2;
a	b
2	0
2	1
select * from t1 where a <= 0;
a	b
0	0
0	1
select * from t1 where a < 3;
a	b
0	0
0	1
1	0
1	1
2	0
2	1
select * from t1 where a >= 1 or a is null;
a	b
1	0
1	1
NULL	0
2	0
NULL	1
2	1
3	0
3	1
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null)
partition by list(a)
partitions 2
(partition x123 values in (1,5,6),
partition x234 values in (4,7,8));
INSERT into t1 VALUES (1,1,1);
INSERT into t1 VALUES (2,1,1);
ERROR HY000: Table has no partition for value 2
INSERT into t1 VALUES (3,1,1);
ERROR HY000: Table has no partition for value 3
INSERT into t1 VALUES (4,1,1);
INSERT into t1 VALUES (5,1,1);
INSERT into t1 VALUES (6,1,1);
INSERT into t1 VALUES (7,1,1);
INSERT into t1 VALUES (8,1,1);
INSERT into t1 VALUES (9,1,1);
ERROR HY000: Table has no partition for value 9
INSERT into t1 VALUES (1,2,1);
INSERT into t1 VALUES (1,3,1);
INSERT into t1 VALUES (1,4,1);
INSERT into t1 VALUES (7,2,1);
INSERT into t1 VALUES (7,3,1);
INSERT into t1 VALUES (7,4,1);
SELECT * from t1;
a	b	c
1	1	1
5	1	1
6	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
8	1	1
7	2	1
7	3	1
7	4	1
SELECT * from t1 WHERE a=1;
a	b	c
1	1	1
1	2	1
1	3	1
1	4	1
SELECT * from t1 WHERE a=7;
a	b	c
7	1	1
7	2	1
7	3	1
7	4	1
SELECT * from t1 WHERE b=2;
a	b	c
1	2	1
7	2	1
UPDATE t1 SET a=8 WHERE a=7 AND b=3;
SELECT * from t1;
a	b	c
1	1	1
5	1	1
6	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
8	1	1
7	2	1
8	3	1
7	4	1
UPDATE t1 SET a=8 WHERE a=5 AND b=1;
SELECT * from t1;
a	b	c
1	1	1
6	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
8	1	1
7	2	1
8	3	1
7	4	1
8	1	1
DELETE from t1 WHERE a=8;
SELECT * from t1;
a	b	c
1	1	1
6	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
7	2	1
7	4	1
DELETE from t1 WHERE a=2;
SELECT * from t1;
a	b	c
1	1	1
6	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
7	2	1
7	4	1
DELETE from t1 WHERE a=5 OR a=6;
SELECT * from t1;
a	b	c
1	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
7	2	1
7	4	1
ALTER TABLE t1
partition by list(a)
partitions 2
(partition x123 values in (1,5,6),
partition x234 values in (4,7,8));
SELECT * from t1;
a	b	c
1	1	1
1	2	1
1	3	1
1	4	1
4	1	1
7	1	1
7	2	1
7	4	1
INSERT into t1 VALUES (6,2,1);
INSERT into t1 VALUES (2,2,1);
ERROR HY000: Table has no partition for value 2
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key (a,b))
partition by list (a)
subpartition by hash (a+b)
( partition x1 values in (1,2,3)
( subpartition x11 nodegroup 0,
subpartition x12 nodegroup 1),
partition x2 values in (4,5,6)
( subpartition x21 nodegroup 0,
subpartition x22 nodegroup 1)
);
INSERT into t1 VALUES (1,1,1);
INSERT into t1 VALUES (4,1,1);
INSERT into t1 VALUES (7,1,1);
ERROR HY000: Table has no partition for value 7
UPDATE t1 SET a=5 WHERE a=1;
SELECT * from t1;
a	b	c
5	1	1
4	1	1
UPDATE t1 SET a=6 WHERE a=4;
SELECT * from t1;
a	b	c
5	1	1
6	1	1
DELETE from t1 WHERE a=6;
SELECT * from t1;
a	b	c
5	1	1
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by list (a)
(partition x1 values in (1,2,9,4));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`a`)
(PARTITION x1 VALUES IN (1,2,9,4) ENGINE = InnoDB) */
drop table t1;
CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1)
(PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (3),
PARTITION p4 VALUES IN (4),
PARTITION p5 VALUES IN (5));
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
SELECT COUNT(*) FROM t1 WHERE s1 < 3;
COUNT(*)
2
DROP TABLE t1;
create table t1 (a int auto_increment primary key)
auto_increment=100
partition by list (a)
(partition p0 values in (1, 100));
create index inx on t1 (a);
insert into t1 values (null);
select * from t1;
a
100
drop table t1;
create table t1 (a char(1))
partition by list (ascii(ucase(a)))
(partition p1 values in (2));
ERROR HY000: This partition function is not allowed
# partition list directory
SET innodb_strict_mode=OFF;
CREATE TABLE t1 (id INTEGER NOT NULL, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
PARTITION p1999 VALUES IN (1995, 1999, 2003)
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p1999/data'
    INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p1999/idx',
PARTITION p2000 VALUES IN (1996, 2000, 2004)
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2000/data'
    INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2000/idx',
PARTITION p2001 VALUES IN (1997, 2001, 2005)
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2001/data'
    INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2001/idx'
);
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
ERROR HY000: Table has no partition for value 1994
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
ERROR HY000: Table has no partition for value 1998
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
ERROR HY000: Table has no partition for value 2002
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
ERROR HY000: Table has no partition for value 2006
DROP TABLE t1;
#
# Bug#28387488: DATA DICTINARY CRASH ON DEBUG BUILD 
# 
# Create table with partition value which is not legal utf-8. 
CREATE TABLE t1 (c1 varbinary(64) NOT NULL) PARTITION BY LIST COLUMNS (c1) (PARTITION custom_p1 VALUES IN (0x98000));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(c1)
(PARTITION custom_p1 VALUES IN (_binary 0x098000) ENGINE = InnoDB) */
SELECT TABLE_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_name = 't1';
TABLE_NAME	PARTITION_DESCRIPTION
t1	_binary 0x098000
# Create table with partition value which IS legal utf-8
CREATE TABLE t2 (c1 varbinary(64) NOT NULL) PARTITION BY LIST COLUMNS (c1) (PARTITION custom_p1 VALUES IN (0x24212b2b));
# Verify that the partition value is still shown as a hex string
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c1` varbinary(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(c1)
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */
SELECT TABLE_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_name = 't2';
TABLE_NAME	PARTITION_DESCRIPTION
t2	_binary 0x24212B2B
# Creating t3 using the SHOW CREATE output for t2
CREATE TABLE `t3` (
`c1` varbinary(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(c1)
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `c1` varbinary(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY LIST  COLUMNS(c1)
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */
# Verify that we can insert into the tables
INSERT INTO t1 VALUES (0x98000);
INSERT INTO t2 VALUES (0x24212b2b);
# also when the table is created from SHOW CREATE output
INSERT INTO t3 VALUES (0x24212b2b);
# Cleanup
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
#
# Bug#31867653: ASSERT WITH IN CREATE TABLE LIST PARITITION
#
# Verify that it is possible to create a table with a
# partition list with more than 256 elements.
CREATE TABLE t1 (c1 INT) PARTITION BY LIST
(c1)( PARTITION p0 VALUES IN
(3089,86,2283,283,1872,3255,1376,2558,289,3098,1522,2829,337,968,3938,190,743,
1141,3257,3461,3496,312,2757,2646,2284,765,662,2088,1880,3616,1388,1910,915,
3390,2387,3357,1264,578,3666,2168,3640,1876,1042,890,459,1771,787,1930,2003,1346
,2917,34,850,2027,1010,2702,3407,235,1672,647,2485,2438,954,1295,425,3561,2068
,3169,1920,885,629,818,2511,2732,2188,642,2630,2047,597,3958,3013,599,909,206,
2084,3597,3150,2871,97,1262,1318,1584,3491,342,3382,1427,2170,4,3470,2521,3811
,821,3308,2522,3418,1306,412,203,2620,2899,2825,3044,2455,1634,3206,2827,150,
2359,3090,3890,2929,1233,1058,1274,1047,927,2546,2699,1399,3124,3193,2051,1795,
2475,3140,194,3967,1793,1220,3881,591,1458,3607,2224,2175,1652,3908,3870,2561,
829,3979,3974,2192,2644,2106,3245,556,1837,1437,3548,116,2137,659,2324,2982,
3677,132,1271,1481,2906,3447,3226,2778,1923,2771,1951,766,3368,1768,2230,2341,
1093,3962,1947,830,1119,1004,914,1064,2598,3002,3690,1831,810,243,3519,2031,1866
,862,2902,3200,1227,1205,1958,122,827,2392,371,3378,679,1537,2012,3003,3159,
2044,3620,2173,893,3843,2953,3223,1045,140,2266,2844,539,1861,1500,3794,349,901,
2021,1087,2788,344,1830,1722,1460,451,3278,2553,195,3222,1560,2799,1839,3074,
2945,1377,3646,3392,1127,1723,3284,3304,2633,2520,43,586,2942,329,2425,80,2726,
282,2353,1836,2306,469,1457,123,3842,3029,3950,1586,1555,2990,3352,1812,3933,
1802,2361,1916,2692,1902,364,3224,3970,1610,3123,3624,3981,162,2622,3102,165,
516,2878,2484,2755,40,2444,2153,444,12,1138,3812,3112,3626,3559,3188,2038,3665,
2862,604,331,651,2469,762,3356,2501,2789,3627,2220,3961,3445,2846) );
# Cleanup
DROP TABLE t1;