Edit File: partition_column.result
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 /*!50500 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 /*!50500 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 /*!50500 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 /*!50500 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 /*!50500 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 /*!50100 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 /*!50500 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 /*!50500 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 /*!50500 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 /*!50500 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 column_name='to_seconds'; data_type int 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;
Back to File Manager