Skip to content

Commit 2ac87b5

Browse files
author
Mikael Ronstrom
committed
WL#3352, Introducing Column list partitioning, makes it possible to partition on most data types, makes it possible to prune on multi-field partitioning
--BZR-- revision-id: [email protected] property-branch-nick: mysql-trunk-wl3352 testament3-sha1: 9bdf92480dc3a8b7697310a3d11569144e5ed403
1 parent db9c1f9 commit 2ac87b5

28 files changed

+2269
-794
lines changed

‎.bzrfileids‎

186 Bytes
Binary file not shown.

‎BUILD/build_mccge.sh‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -556,7 +556,7 @@ parse_package()
556556
package="pro"
557557
;;
558558
extended )
559-
package=""
559+
package="extended"
560560
;;
561561
cge )
562562
package="cge"

‎mysql-test/r/partition_mgm_err.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@ ERROR HY000: Reorganize of range partitions cannot change total ranges except fo
4141
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
4242
(PARTITION x01 VALUES LESS THAN (4),
4343
PARTITION x11 VALUES LESS THAN (2));
44-
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
44+
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
4545
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
4646
(PARTITION x01 VALUES LESS THAN (6),
4747
PARTITION x11 VALUES LESS THAN (4));

‎mysql-test/r/partition_range.result‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,100 @@
11
drop table if exists t1, t2;
22
create table t1 (a int)
33
partition by range (a)
4+
( partition p0 values less than (NULL),
5+
partition p1 values less than (MAXVALUE));
6+
ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '),
7+
partition p1 values less than (MAXVALUE))' at line 3
8+
create table t1 (a datetime not null)
9+
partition by range (TO_SECONDS(a))
10+
( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')),
11+
partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00')));
12+
INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00');
13+
INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00');
14+
explain partitions select * from t1 where a < '2007-03-08 00:00:00';
15+
id select_type table partitions type possible_keys key key_len ref rows Extra
16+
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
17+
explain partitions select * from t1 where a < '2007-03-08 00:00:01';
18+
id select_type table partitions type possible_keys key key_len ref rows Extra
19+
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
20+
explain partitions select * from t1 where a <= '2007-03-08 00:00:00';
21+
id select_type table partitions type possible_keys key key_len ref rows Extra
22+
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
23+
explain partitions select * from t1 where a <= '2007-03-07 23:59:59';
24+
id select_type table partitions type possible_keys key key_len ref rows Extra
25+
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
26+
explain partitions select * from t1 where a < '2007-03-07 23:59:59';
27+
id select_type table partitions type possible_keys key key_len ref rows Extra
28+
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
29+
drop table t1;
30+
create table t1 (a date)
31+
partition by range(to_seconds(a))
32+
(partition p0 values less than (to_seconds('2004-01-01')),
33+
partition p1 values less than (to_seconds('2005-01-01')));
34+
insert into t1 values ('2003-12-30'),('2004-12-31');
35+
select * from t1;
36+
a
37+
2003-12-30
38+
2004-12-31
39+
explain partitions select * from t1 where a <= '2003-12-31';
40+
id select_type table partitions type possible_keys key key_len ref rows Extra
41+
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1
42+
select * from t1 where a <= '2003-12-31';
43+
a
44+
2003-12-30
45+
explain partitions select * from t1 where a <= '2005-01-01';
46+
id select_type table partitions type possible_keys key key_len ref rows Extra
47+
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
48+
select * from t1 where a <= '2005-01-01';
49+
a
50+
2003-12-30
51+
2004-12-31
52+
drop table t1;
53+
create table t1 (a datetime)
54+
partition by range(to_seconds(a))
55+
(partition p0 values less than (to_seconds('2004-01-01 12:00:00')),
56+
partition p1 values less than (to_seconds('2005-01-01 12:00:00')));
57+
insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59');
58+
select * from t1;
59+
a
60+
2004-01-01 11:59:29
61+
2005-01-01 11:59:59
62+
explain partitions select * from t1 where a <= '2004-01-01 11:59.59';
63+
id select_type table partitions type possible_keys key key_len ref rows Extra
64+
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1
65+
select * from t1 where a <= '2004-01-01 11:59:59';
66+
a
67+
2004-01-01 11:59:29
68+
explain partitions select * from t1 where a <= '2005-01-01';
69+
id select_type table partitions type possible_keys key key_len ref rows Extra
70+
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
71+
select * from t1 where a <= '2005-01-01';
72+
a
73+
2004-01-01 11:59:29
74+
drop table t1;
75+
create table t1 (a int, b char(20))
76+
partition by range column_list(a,b)
77+
(partition p0 values less than (1));
78+
ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3
79+
create table t1 (a int, b char(20))
80+
partition by range(a)
81+
(partition p0 values less than (column_list(1,"b")));
82+
ERROR HY000: Inconsistency in usage of column lists for partitioning
83+
create table t1 (a int, b char(20))
84+
partition by range(a)
85+
(partition p0 values less than (column_list(1,"b")));
86+
ERROR HY000: Inconsistency in usage of column lists for partitioning
87+
create table t1 (a int, b char(20));
88+
create global index inx on t1 (a,b)
89+
partition by range (a)
90+
(partition p0 values less than (1));
91+
drop table t1;
92+
create table t1 (a int, b char(20))
93+
partition by range column_list(b)
94+
(partition p0 values less than (column_list("b")));
95+
drop table t1;
96+
create table t1 (a int)
97+
partition by range (a)
498
( partition p0 values less than (maxvalue));
599
alter table t1 add partition (partition p1 values less than (100000));
6100
ERROR HY000: MAXVALUE can only be used in last partition definition

‎mysql-test/suite/parts/inc/partition_key_32col.inc‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
--error ER_TOO_MANY_KEY_PARTS
1+
--error ER_TOO_MANY_PARTITION_FUNC_FIELDS_ERROR
22
eval create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3)) engine=$engine
33
partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3) (
44
partition pa1 max_rows=20 min_rows=2,

‎mysql-test/t/partition_column.test‎

Lines changed: 209 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,209 @@
1+
#
2+
# Tests for the new column list partitioning introduced in second
3+
# version for partitioning.
4+
#
5+
--source include/have_partition.inc
6+
7+
--disable_warnings
8+
drop table if exists t1;
9+
--enable_warnings
10+
11+
create table t1 (a int, b char(10), c varchar(25), d datetime)
12+
partition by range column_list(a,b,c,d)
13+
subpartition by hash (to_seconds(d))
14+
subpartitions 4
15+
( partition p0 values less than (column_list(1, NULL, MAXVALUE, NULL)),
16+
partition p1 values less than (column_list(1, 'a', MAXVALUE, TO_DAYS('1999-01-01'))),
17+
partition p2 values less than (column_list(1, 'a', MAXVALUE, MAXVALUE)),
18+
partition p3 values less than (column_list(1, MAXVALUE, MAXVALUE, MAXVALUE)));
19+
drop table t1;
20+
21+
create table t1 (a int, b char(10), c varchar(5), d int)
22+
partition by range column_list(a,b,c)
23+
subpartition by key (c,d)
24+
subpartitions 3
25+
( partition p0 values less than (column_list(1,'abc','abc')),
26+
partition p1 values less than (column_list(2,'abc','abc')),
27+
partition p2 values less than (column_list(3,'abc','abc')),
28+
partition p3 values less than (column_list(4,'abc','abc')));
29+
30+
insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3);
31+
insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3);
32+
insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3);
33+
insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3);
34+
select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR
35+
(a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2))));
36+
drop table t1;
37+
38+
create table t1 (a int, b varchar(2), c int)
39+
partition by range column_list (a, b, c)
40+
(partition p0 values less than (column_list(1, 'A', 1)),
41+
partition p1 values less than (column_list(1, 'B', 1)));
42+
insert into t1 values (1, 'A', 1);
43+
explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1;
44+
select * from t1 where a = 1 AND b <= 'A' and c = 1;
45+
drop table t1;
46+
47+
create table t1 (a char, b char, c char)
48+
partition by list column_list(a)
49+
( partition p0 values in (column_list('a')));
50+
insert into t1 (a) values ('a');
51+
select * from t1 where a = 'a';
52+
drop table t1;
53+
54+
--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
55+
create table t1 (d timestamp)
56+
partition by range column_list(d)
57+
( partition p0 values less than (column_list('2000-01-01')),
58+
partition p1 values less than (column_list('2040-01-01')));
59+
60+
create table t1 (a int, b int)
61+
partition by range column_list(a,b)
62+
(partition p0 values less than (column_list(null, 10)));
63+
drop table t1;
64+
65+
create table t1 (d date)
66+
partition by range column_list(d)
67+
( partition p0 values less than (column_list('2000-01-01')),
68+
partition p1 values less than (column_list('2009-01-01')));
69+
drop table t1;
70+
71+
create table t1 (d date)
72+
partition by range column_list(d)
73+
( partition p0 values less than (column_list('1999-01-01')),
74+
partition p1 values less than (column_list('2000-01-01')));
75+
drop table t1;
76+
77+
create table t1 (d date)
78+
partition by range column_list(d)
79+
( partition p0 values less than (column_list('2000-01-01')),
80+
partition p1 values less than (column_list('3000-01-01')));
81+
drop table t1;
82+
83+
create table t1 (a int, b int)
84+
partition by range column_list(a,b)
85+
(partition p2 values less than (column_list(99,99)),
86+
partition p1 values less than (column_list(99,999)));
87+
88+
insert into t1 values (99,998);
89+
select * from t1 where b = 998;
90+
drop table t1;
91+
92+
create table t1 as select to_seconds(null) as to_seconds;
93+
select data_type from information_schema.columns
94+
where column_name='to_seconds';
95+
drop table t1;
96+
97+
--error ER_PARSE_ERROR
98+
create table t1 (a int, b int)
99+
partition by list column_list(a,b)
100+
(partition p0 values in (column_list(maxvalue,maxvalue)));
101+
create table t1 (a int, b int)
102+
partition by range column_list(a,b)
103+
(partition p0 values less than (column_list(maxvalue,maxvalue)));
104+
drop table t1;
105+
106+
create table t1 (a int)
107+
partition by list column_list(a)
108+
(partition p0 values in (column_list(0)));
109+
select partition_method from information_schema.partitions where table_name='t1';
110+
drop table t1;
111+
112+
create table t1 (a char(6))
113+
partition by range column_list(a)
114+
(partition p0 values less than (column_list('H23456')),
115+
partition p1 values less than (column_list('M23456')));
116+
insert into t1 values ('F23456');
117+
select * from t1;
118+
drop table t1;
119+
120+
-- error 1054
121+
create table t1 (a char(6))
122+
partition by range column_list(a)
123+
(partition p0 values less than (column_list(H23456)),
124+
partition p1 values less than (column_list(M23456)));
125+
126+
-- error ER_RANGE_NOT_INCREASING_ERROR
127+
create table t1 (a char(6))
128+
partition by range column_list(a)
129+
(partition p0 values less than (column_list(23456)),
130+
partition p1 values less than (column_list(23456)));
131+
132+
-- error 1064
133+
create table t1 (a int, b int)
134+
partition by range column_list(a,b)
135+
(partition p0 values less than (10));
136+
137+
-- error ER_PARTITION_COLUMN_LIST_ERROR
138+
create table t1 (a int, b int)
139+
partition by range column_list(a,b)
140+
(partition p0 values less than (column_list(1,1,1));
141+
142+
create table t1 (a int, b int)
143+
partition by range column_list(a,b)
144+
(partition p0 values less than (column_list(1, NULL)),
145+
partition p1 values less than (column_list(2, maxvalue)),
146+
partition p2 values less than (column_list(3, 3)),
147+
partition p3 values less than (column_list(10, NULL)));
148+
149+
-- error ER_NO_PARTITION_FOR_GIVEN_VALUE
150+
insert into t1 values (10,0);
151+
insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1);
152+
select * from t1;
153+
154+
alter table t1
155+
partition by range column_list(b,a)
156+
(partition p0 values less than (column_list(1,2)),
157+
partition p1 values less than (column_list(3,3)),
158+
partition p2 values less than (column_list(9,5)));
159+
explain partitions select * from t1 where b < 2;
160+
select * from t1 where b < 2;
161+
explain partitions select * from t1 where b < 4;
162+
select * from t1 where b < 4;
163+
164+
alter table t1 reorganize partition p1 into
165+
(partition p11 values less than (column_list(2,2)),
166+
partition p12 values less than (column_list(3,3)));
167+
168+
-- error ER_REORG_OUTSIDE_RANGE
169+
alter table t1 reorganize partition p0 into
170+
(partition p01 values less than (column_list(0,3)),
171+
partition p02 values less than (column_list(1,1)));
172+
173+
-- error ER_PARTITION_COLUMN_LIST_ERROR
174+
alter table t1 reorganize partition p2 into
175+
(partition p2 values less than(column_list(9,6,1)));
176+
177+
-- error ER_PARTITION_COLUMN_LIST_ERROR
178+
alter table t1 reorganize partition p2 into
179+
(partition p2 values less than (10));
180+
181+
alter table t1 reorganize partition p2 into
182+
(partition p21 values less than (column_list(4,7)),
183+
partition p22 values less than (column_list(9,5)));
184+
explain partitions select * from t1 where b < 4;
185+
select * from t1 where b < 4;
186+
drop table t1;
187+
188+
#create table t1 (a int, b int)
189+
#partition by range column_list(a,b)
190+
#(partition p0 values less than (column_list(99,99)),
191+
# partition p1 values less than (column_list(99,maxvalue)));
192+
#drop table t1;
193+
194+
create table t1 (a int, b int)
195+
partition by list column_list(a,b)
196+
subpartition by hash (b)
197+
subpartitions 2
198+
(partition p0 values in (column_list(0,0), column_list(1,1)),
199+
partition p1 values in (column_list(1000,1000)));
200+
insert into t1 values (1000,1000);
201+
#select * from t1 where a = 0 and b = 0;
202+
drop table t1;
203+
204+
create table t1 (a char, b char, c char)
205+
partition by range column_list(a,b,c)
206+
( partition p0 values less than (column_list('a','b','c')));
207+
alter table t1 add partition
208+
(partition p1 values less than (column_list('b','c','d')));
209+
drop table t1;

0 commit comments

Comments
 (0)