diff options
Diffstat (limited to 'mysql-test/main/partition_column.result')
| -rw-r--r-- | mysql-test/main/partition_column.result | 672 |
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; |