drop table if exists t1; create table t1 (a int, b int) partition by list column_list(a,b) ( partition p0 values in (column_list(1, NULL), column_list(2, NULL), column_list(NULL, NULL)), partition p1 values in (column_list(1,1), column_list(2,2)), partition p2 values in (column_list(3, NULL), column_list(NULL, 1))); insert into t1 values (3, NULL); insert into t1 values (NULL, 1); insert into t1 values (NULL, NULL); insert into t1 values (1, NULL); insert into t1 values (2, NULL); insert into t1 values (1,1); insert into t1 values (2,2); select * from t1 where a = 1; a b 1 NULL 1 1 select * from t1 where a = 2; a b 2 NULL 2 2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST COLUMN_LIST(a,b) (PARTITION p0 VALUES IN ( COLUMN_LIST(1,NULL), COLUMN_LIST(2,NULL), COLUMN_LIST(NULL,NULL)) ENGINE = MyISAM, PARTITION p1 VALUES IN ( COLUMN_LIST(1,1), COLUMN_LIST(2,2)) ENGINE = MyISAM, PARTITION p2 VALUES IN ( COLUMN_LIST(3,NULL), COLUMN_LIST(NULL,1)) ENGINE = MyISAM) */ drop table t1; create table t1 (a int) partition by list (a) ( partition p0 values in (1), partition p1 values in (1)); ERROR HY000: Multiple definition of same constant in list partitioning create table t1 (a int) partition by list (a) ( partition p0 values in (2, 1), partition p1 values in (4, NULL, 3)); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); insert into t1 values (4); insert into t1 values (NULL); insert into t1 values (5); ERROR HY000: Table has no partition for value 5 drop table t1; create table t1 (a int) partition by list column_list(a) ( partition p0 values in (column_list(2), column_list(1)), partition p1 values in (column_list(4), column_list(NULL), column_list(3))); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); insert into t1 values (4); insert into t1 values (NULL); insert into t1 values (5); ERROR HY000: Table has no partition for value from column_list show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST COLUMN_LIST(a) (PARTITION p0 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(1)) ENGINE = MyISAM, PARTITION p1 VALUES IN ( COLUMN_LIST(4), COLUMN_LIST(NULL), COLUMN_LIST(3)) ENGINE = MyISAM) */ drop table t1; create table t1 (a int, b char(10), c varchar(25), d datetime) partition by range column_list(a,b,c,d) subpartition by hash (to_seconds(d)) subpartitions 4 ( partition p0 values less than (column_list(1, NULL, MAXVALUE, NULL)), partition p1 values less than (column_list(1, 'a', MAXVALUE, TO_DAYS('1999-01-01'))), partition p2 values less than (column_list(1, 'a', MAXVALUE, MAXVALUE)), partition p3 values less than (column_list(1, MAXVALUE, MAXVALUE, MAXVALUE))); drop table t1; create table t1 (a int, b char(10), c varchar(5), d int) partition by range column_list(a,b,c) subpartition by key (c,d) subpartitions 3 ( partition p0 values less than (column_list(1,'abc','abc')), partition p1 values less than (column_list(2,'abc','abc')), partition p2 values less than (column_list(3,'abc','abc')), partition p3 values less than (column_list(4,'abc','abc'))); insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3); insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3); insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3); select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); a b c d 1 a b 1 1 b c 1 drop table t1; create table t1 (a int, b varchar(2), c int) partition by range column_list (a, b, c) (partition p0 values less than (column_list(1, 'A', 1)), partition p1 values less than (column_list(1, 'B', 1))); insert into t1 values (1, 'A', 1); explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 system NULL NULL NULL NULL 1 select * from t1 where a = 1 AND b <= 'A' and c = 1; a b c 1 A 1 drop table t1; create table t1 (a char, b char, c char) partition by list column_list(a) ( partition p0 values in (column_list('a'))); insert into t1 (a) values ('a'); select * from t1 where a = 'a'; a b c a NULL NULL drop table t1; create table t1 (d timestamp) partition by range column_list(d) ( partition p0 values less than (column_list('2000-01-01')), partition p1 values less than (column_list('2040-01-01'))); ERROR HY000: Partition column values of incorrect type create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(null, 10))); drop table t1; create table t1 (d date) partition by range column_list(d) ( partition p0 values less than (column_list('2000-01-01')), partition p1 values less than (column_list('2009-01-01'))); drop table t1; create table t1 (d date) partition by range column_list(d) ( partition p0 values less than (column_list('1999-01-01')), partition p1 values less than (column_list('2000-01-01'))); drop table t1; create table t1 (d date) partition by range column_list(d) ( partition p0 values less than (column_list('2000-01-01')), partition p1 values less than (column_list('3000-01-01'))); drop table t1; create table t1 (a int, b int) partition by range column_list(a,b) (partition p2 values less than (column_list(99,99)), partition p1 values less than (column_list(99,999))); insert into t1 values (99,998); select * from t1 where b = 998; a b 99 998 drop table t1; create table t1 as select to_seconds(null) as to_seconds; select data_type from information_schema.columns where column_name='to_seconds'; data_type int drop table t1; create table t1 (a int, b int) partition by list column_list(a,b) (partition p0 values in (column_list(maxvalue,maxvalue))); ERROR 42000: Cannot use MAXVALUE as value in List partitioning near 'maxvalue,maxvalue)))' at line 3 create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(maxvalue,maxvalue))); drop table t1; create table t1 (a int) partition by list column_list(a) (partition p0 values in (column_list(0))); select partition_method from information_schema.partitions where table_name='t1'; partition_method LIST COLUMN_LIST drop table t1; create table t1 (a char(6)) partition by range column_list(a) (partition p0 values less than (column_list('H23456')), partition p1 values less than (column_list('M23456'))); insert into t1 values ('F23456'); select * from t1; a F23456 drop table t1; create table t1 (a char(6)) partition by range column_list(a) (partition p0 values less than (column_list(H23456)), partition p1 values less than (column_list(M23456))); ERROR 42S22: Unknown column 'H23456' in 'field list' create table t1 (a char(6)) partition by range column_list(a) (partition p0 values less than (column_list(23456)), partition p1 values less than (column_list(23456))); ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (10)); ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3 create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(1,1,1)); ERROR HY000: Inconsistency in usage of column lists for partitioning create table t1 (a int, b int) partition by range column_list(a,b) (partition p0 values less than (column_list(1, NULL)), partition p1 values less than (column_list(2, maxvalue)), partition p2 values less than (column_list(3, 3)), partition p3 values less than (column_list(10, NULL))); insert into t1 values (10,0); ERROR HY000: Table has no partition for value from column_list insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); select * from t1; a b 0 1 1 1 2 1 3 1 3 4 4 9 9 1 alter table t1 partition by range column_list(b,a) (partition p0 values less than (column_list(1,2)), partition p1 values less than (column_list(3,3)), partition p2 values less than (column_list(9,5))); explain partitions select * from t1 where b < 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 7 Using where select * from t1 where b < 2; a b 0 1 1 1 2 1 3 1 9 1 explain partitions select * from t1 where b < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where select * from t1 where b < 4; a b 0 1 1 1 2 1 3 1 9 1 alter table t1 reorganize partition p1 into (partition p11 values less than (column_list(2,2)), partition p12 values less than (column_list(3,3))); alter table t1 reorganize partition p0 into (partition p01 values less than (column_list(0,3)), partition p02 values less than (column_list(1,1))); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range alter table t1 reorganize partition p2 into (partition p2 values less than(column_list(9,6,1))); ERROR HY000: Inconsistency in usage of column lists for partitioning alter table t1 reorganize partition p2 into (partition p2 values less than (10)); ERROR HY000: Inconsistency in usage of column lists for partitioning alter table t1 reorganize partition p2 into (partition p21 values less than (column_list(4,7)), partition p22 values less than (column_list(9,5))); explain partitions select * from t1 where b < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p11,p12,p21 ALL NULL NULL NULL NULL 7 Using where select * from t1 where b < 4; a b 0 1 1 1 2 1 3 1 9 1 drop table t1; create table t1 (a int, b int) partition by list column_list(a,b) subpartition by hash (b) subpartitions 2 (partition p0 values in (column_list(0,0), column_list(1,1)), partition p1 values in (column_list(1000,1000))); insert into t1 values (1000,1000); drop table t1; create table t1 (a char, b char, c char) partition by range column_list(a,b,c) ( partition p0 values less than (column_list('a','b','c'))); alter table t1 add partition (partition p1 values less than (column_list('b','c','d'))); drop table t1;