Create 'main' test directory and move 't' and 'r' there

1 files changed, 672 insertions, 0 deletions

diff --git a/mysql-test/main/partition_column.result b/mysql-test/main/partition_column.result
new file mode 100644
index 00000000000..041c485c1a8
--- /dev/null
+++ b/mysql-test/main/partition_column.result
@@ -0,0 +1,672 @@
+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;