drop table if exists t1;
#
# Bug#52815: LIST COLUMNS doesn't insert rows in correct partition
#            if muliple columns used
CREATE TABLE t1 (
id INT NOT NULL,
name VARCHAR(255),
department VARCHAR(10),
country VARCHAR(255)
) PARTITION BY LIST COLUMNS (department, country) (
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION second_office VALUES IN (('dep2', 'Russia'))
);
INSERT INTO t1 VALUES(1, 'Ann', 'dep1', 'Russia');
INSERT INTO t1 VALUES(2, 'Bob', 'dep1', 'Croatia');
INSERT INTO t1 VALUES(3, 'Cecil', 'dep2', 'Russia');
INSERT INTO t1 VALUES(3, 'Dan', 'dep2', 'Croatia');
ERROR HY000: Table has no partition for value from column_list
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't1';
PARTITION_NAME	TABLE_ROWS
first_office	2
second_office	1
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY LIST  COLUMNS(`department`,`country`)
(PARTITION `first_office` VALUES IN (('dep1','Russia'),('dep1','Croatia')) ENGINE = MyISAM,
 PARTITION `second_office` VALUES IN (('dep2','Russia')) ENGINE = MyISAM)
SELECT * FROM t1 WHERE department = 'dep2' and country = 'Croatia';
id	name	department	country
SELECT * FROM t1 WHERE department = 'dep1' and country = 'Croatia';
id	name	department	country
2	Bob	dep1	Croatia
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0));
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (a BLOB)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN ("X"));
ERROR HY000: A BLOB field is not allowed in partition function
CREATE TABLE t1 (a TEXT)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN ("X"));
ERROR HY000: A BLOB field is not allowed in partition function
CREATE TABLE t1 (a FLOAT)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0.0));
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (a DOUBLE)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0.0));
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (d TIMESTAMP)
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ('2000-01-01'),
PARTITION p1 VALUES LESS THAN ('2040-01-01'));
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (d BIT(1))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN (0),
PARTITION p1 VALUES LESS THAN (1));
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (d ENUM("YES","NO"))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ("NO"),
PARTITION p1 VALUES LESS THAN (MAXVALUE));
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
CREATE TABLE t1 (d SET("Car","MC"))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ("MC"),
PARTITION p1 VALUES LESS THAN (MAXVALUE));
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
create table t1 (a int, b int)
partition by range columns (a,b)
( partition p0 values less than (maxvalue, 10),
partition p1 values less than (maxvalue, maxvalue));
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
create table t1 (a int, b int, c int)
partition by range columns (a,b,c)
( partition p0 values less than (1, maxvalue, 10),
partition p1 values less than (1, maxvalue, maxvalue));
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
create table t1 (a varchar(5) character set ucs2 collate ucs2_bin)
partition by range columns (a)
(partition p0 values less than (0x0041));
insert into t1 values (0x00410000);
select hex(a) from t1 where a like 'A_';
hex(a)
00410000
explain partitions select hex(a) from t1 where a like 'A_';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
alter table t1 remove partitioning;
select hex(a) from t1 where a like 'A_';
hex(a)
00410000
create index a on t1 (a);
select hex(a) from t1 where a like 'A_';
hex(a)
00410000
insert into t1 values ('A_');
select hex(a) from t1;
hex(a)
00410000
0041005F
drop table t1;
create table t1 (a varchar(1) character set latin1 collate latin1_general_ci)
partition by range columns(a)
( partition p0 values less than ('a'),
partition p1 values less than ('b'),
partition p2 values less than ('c'),
partition p3 values less than ('d'));
insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
select * from t1 where a > 'B' collate latin1_bin;
a
a
b
C
c
select * from t1 where a <> 'B' collate latin1_bin;
a
A
a
b
C
c
alter table t1 remove partitioning;
select * from t1 where a > 'B' collate latin1_bin;
a
a
b
C
c
select * from t1 where a <> 'B' collate latin1_bin;
a
A
a
b
C
c
drop table t1;
create table t1 (a varchar(2) character set latin1,
b varchar(2) character set latin1)
partition by list columns(a,b)
(partition p0 values in (('a','a')));
insert into t1 values ('A','A');
select * from t1 where b <> 'a' collate latin1_bin AND
a = 'A' collate latin1_bin;
a	b
A	A
alter table t1 remove partitioning;
select * from t1 where b <> 'a' collate latin1_bin AND
a = 'A' collate latin1_bin;
a	b
A	A
drop table t1;
create table t1 (a varchar(5))
partition by list columns(a)
( partition p0 values in ('\''),
  partition p1 values in ('\\'),
  partition p2 values in ('\0'));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY LIST  COLUMNS(`a`)
(PARTITION `p0` VALUES IN ('''') ENGINE = MyISAM,
 PARTITION `p1` VALUES IN ('\\') ENGINE = MyISAM,
 PARTITION `p2` VALUES IN ('\0') ENGINE = MyISAM)
drop table t1;
set @@sql_mode=allow_invalid_dates;
create table t1 (a char, b char, c date)
partition by range columns (a,b,c)
( partition p0 values less than (0,0,to_days('3000-11-31')));
ERROR HY000: Partition column values of incorrect type
create table t1 (a char, b char, c date)
partition by range columns (a,b,c)
( partition p0 values less than (0,0,'3000-11-31'));
ERROR HY000: Partition column values of incorrect type
set @@sql_mode='';
create table t1 (a int, b char(10), c varchar(25), d datetime)
partition by range columns(a,b,c,d)
subpartition by hash (to_seconds(d))
subpartitions 4
( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'),
partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE),
partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));
ERROR HY000: Partition column values of incorrect type
create table t1 (a int, b char(10), c varchar(25), d datetime)
partition by range columns(a,b,c,d)
subpartition by hash (to_seconds(d))
subpartitions 4
( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'),
partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE),
partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'0',MAXVALUE,'1900-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'0',MAXVALUE,'1900-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'0',MAXVALUE,'1900-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'0',MAXVALUE,'1900-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'a',MAXVALUE,'1999-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'a',MAXVALUE,'1999-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'a',MAXVALUE,'1999-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'a',MAXVALUE,'1999-01-01'
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'b',MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'b',MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'b',MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,'b',MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,MAXVALUE,MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,MAXVALUE,MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,MAXVALUE,MAXVALUE,MAXVALUE
RANGE COLUMNS	`a`,`b`,`c`,`d`	1,MAXVALUE,MAXVALUE,MAXVALUE
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` char(10) DEFAULT NULL,
  `c` varchar(25) DEFAULT NULL,
  `d` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY RANGE  COLUMNS(`a`,`b`,`c`,`d`)
SUBPARTITION BY HASH (to_seconds(`d`))
SUBPARTITIONS 4
(PARTITION `p0` VALUES LESS THAN (1,'0',MAXVALUE,'1900-01-01') ENGINE = MyISAM,
 PARTITION `p1` VALUES LESS THAN (1,'a',MAXVALUE,'1999-01-01') ENGINE = MyISAM,
 PARTITION `p2` VALUES LESS THAN (1,'b',MAXVALUE,MAXVALUE) ENGINE = MyISAM,
 PARTITION `p3` VALUES LESS THAN (1,MAXVALUE,MAXVALUE,MAXVALUE) ENGINE = MyISAM)
drop table t1;
create table t1 (a int, b int)
partition by range columns (a,b)
(partition p0 values less than (NULL, maxvalue));
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
create table t1 (a int, b int)
partition by list columns(a,b)
( partition p0 values in ((maxvalue, 0)));
Got one of the listed errors
create table t1 (a int, b int)
partition by list columns (a,b)
( partition p0 values in ((0,0)));
alter table t1 add partition
(partition p1 values in (maxvalue, maxvalue));
Got one of the listed errors
drop table t1;
create table t1 (a int, b int)
partition by key (a,a);
ERROR HY000: Duplicate partition field name 'a'
create table t1 (a int, b int)
partition by list columns(a,a)
( partition p values in ((1,1)));
ERROR HY000: Duplicate partition field name 'a'
create table t1 (a int signed)
partition by list (a)
( partition p0 values in (1, 3, 5, 7, 9, NULL),
partition p1 values in (2, 4, 6, 8, 0));
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
select * from t1 where NULL <= a;
a
select * from t1 where a is null;
a
NULL
explain partitions select * from t1 where a is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
select * from t1 where a <= 1;
a
1
0
drop table t1;
create table t1 (a int signed)
partition by list columns(a)
( partition p0 values in (1, 3, 5, 7, 9, NULL),
partition p1 values in (2, 4, 6, 8, 0));
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
select * from t1 where a <= NULL;
a
select * from t1 where a is null;
a
NULL
explain partitions select * from t1 where a is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
select * from t1 where a <= 1;
a
1
0
drop table t1;
create table t1 (a int, b int)
partition by list columns(a,b)
( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)),
partition p1 values in ((1,1), (2,2)),
partition p2 values in ((3, NULL), (NULL, 1)));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
LIST COLUMNS	`a`,`b`	(1,NULL),(2,NULL),(NULL,NULL)
LIST COLUMNS	`a`,`b`	(1,1),(2,2)
LIST COLUMNS	`a`,`b`	(3,NULL),(NULL,1)
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
 PARTITION BY LIST  COLUMNS(`a`,`b`)
(PARTITION `p0` VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN ((1,1),(2,2)) ENGINE = MyISAM,
 PARTITION `p2` VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM)
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
select * from t1 where a > 8;
a	b
select * from t1 where a not between 8 and 8;
a	b
1	NULL
2	NULL
1	1
2	2
3	NULL
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
 PARTITION BY LIST  COLUMNS(`a`,`b`)
(PARTITION `p0` VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN ((1,1),(2,2)) ENGINE = MyISAM,
 PARTITION `p2` VALUES IN ((3,NULL),(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));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
LIST	`a`	2,1
LIST	`a`	NULL,4,3
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY LIST (`a`)
(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (NULL,4,3) ENGINE = MyISAM)
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 columns(a)
( partition p0 values in (2, 1),
partition p1 values in ((4), (NULL), (3)));
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 4
create table t1 (a int)
partition by list columns(a)
( partition p0 values in (2, 1),
partition p1 values in (4, NULL, 3));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
LIST COLUMNS	`a`	2,1
LIST COLUMNS	`a`	4,NULL,3
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY LIST  COLUMNS(`a`)
(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (4,NULL,3) ENGINE = MyISAM)
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
 PARTITION BY LIST  COLUMNS(`a`)
(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (4,NULL,3) ENGINE = MyISAM)
drop table t1;
create table t1 (a int, b char(10), c varchar(5), d int)
partition by range columns(a,b,c)
subpartition by key (c,d)
subpartitions 3
( partition p0 values less than (1,'abc','abc'),
partition p1 values less than (2,'abc','abc'),
partition p2 values less than (3,'abc','abc'),
partition p3 values less than (4,'abc','abc'));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
RANGE COLUMNS	`a`,`b`,`c`	1,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	1,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	1,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	2,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	2,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	2,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	3,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	3,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	3,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	4,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	4,'abc','abc'
RANGE COLUMNS	`a`,`b`,`c`	4,'abc','abc'
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` char(10) DEFAULT NULL,
  `c` varchar(5) DEFAULT NULL,
  `d` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY RANGE  COLUMNS(`a`,`b`,`c`)
SUBPARTITION BY KEY (`c`,`d`)
SUBPARTITIONS 3
(PARTITION `p0` VALUES LESS THAN (1,'abc','abc') ENGINE = MyISAM,
 PARTITION `p1` VALUES LESS THAN (2,'abc','abc') ENGINE = MyISAM,
 PARTITION `p2` VALUES LESS THAN (3,'abc','abc') ENGINE = MyISAM,
 PARTITION `p3` VALUES LESS THAN (4,'abc','abc') ENGINE = MyISAM)
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 columns (a, b, c)
(partition p0 values less than (1, 'A', 1),
partition p1 values less than (1, 'B', 1));
select partition_method, partition_expression, partition_description
from information_schema.partitions where table_name = "t1";
partition_method	partition_expression	partition_description
RANGE COLUMNS	`a`,`b`,`c`	1,'A',1
RANGE COLUMNS	`a`,`b`,`c`	1,'B',1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(2) DEFAULT NULL,
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY RANGE  COLUMNS(`a`,`b`,`c`)
(PARTITION `p0` VALUES LESS THAN (1,'A',1) ENGINE = MyISAM,
 PARTITION `p1` VALUES LESS THAN (1,'B',1) ENGINE = MyISAM)
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 columns(a)
( partition p0 values in ('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 time)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
partition p1 values less than ('2040-01-01'));
ERROR HY000: Partition column values of incorrect type
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (maxvalue, 10));
drop table t1;
create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
partition p1 values less than ('2009-01-01'));
drop table t1;
create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('1999-01-01'),
partition p1 values less than ('2000-01-01'));
drop table t1;
create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
partition p1 values less than ('3000-01-01'));
drop table t1;
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p2 values less than (99,99),
partition p1 values less than (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 table_schema='test' and column_name='to_seconds';
data_type
bigint
drop table t1;
create table t1 (a int, b int)
partition by list columns(a,b)
(partition p0 values in ((maxvalue,maxvalue)));
ERROR 42000: Cannot use MAXVALUE as value in VALUES IN near 'maxvalue,maxvalue)))' at line 3
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (maxvalue,maxvalue));
drop table t1;
create table t1 (a int)
partition by list columns(a)
(partition p0 values in (0));
select partition_method from information_schema.partitions where table_name='t1';
partition_method
LIST COLUMNS
drop table t1;
create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than ('H23456'),
partition p1 values less than ('M23456'));
insert into t1 values ('F23456');
select * from t1;
a
F23456
drop table t1;
create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than (H23456),
partition p1 values less than (M23456));
ERROR 42S22: Unknown column 'H23456' in 'field list'
create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than (23456),
partition p1 values less than (23456));
ERROR HY000: Partition column values of incorrect type
create table t1 (a int, b int)
partition by range columns(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 columns(a,b)
(partition p0 values less than (1,1,1);
ERROR HY000: Inconsistency in usage of column lists for partitioning
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (1, 0),
partition p1 values less than (2, maxvalue),
partition p2 values less than (3, 3),
partition p3 values less than (10, maxvalue));
insert into t1 values (11,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 columns(b,a)
(partition p0 values less than (1,2),
partition p1 values less than (3,3),
partition p2 values less than (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	5	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 (2,2),
partition p12 values less than (3,3));
alter table t1 reorganize partition p0 into
(partition p01 values less than (0,3),
partition p02 values less than (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(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 (4,7),
partition p22 values less than (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	6	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 columns(a,b)
subpartition by hash (b)
subpartitions 2
(partition p0 values in ((0,0), (1,1)),
partition p1 values in ((1000,1000)));
insert into t1 values (1000,1000);
drop table t1;
create table t1 (a char, b char, c char)
partition by range columns(a,b,c)
( partition p0 values less than ('a','b','c'));
alter table t1 add partition
(partition p1 values less than ('b','c','d'));
drop table t1;