summaryrefslogtreecommitdiff
path: root/mysql-test/r/partition_hash.result
blob: 9a63db3f9749b6e1972e86b4f8b168e2a737e472 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
drop table if exists t1;
CREATE TABLE t1 (c1 INT)
PARTITION BY HASH (c1)
PARTITIONS 15;
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 COALESCE PARTITION 13;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT)
PARTITION BY LINEAR HASH (c1)
PARTITIONS 5;
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 COALESCE PARTITION 3;
DROP TABLE t1;
create table t1 (a int unsigned)
partition by hash(a div 2)
partitions 4;
insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
select * from t1 where a < 0;
a
select * from t1 where a is null or (a >= 5 and a <= 7);
a
NULL
5
6
7
select * from t1 where a is null;
a
NULL
select * from t1 where a is not null;
a
0
1
2
3
4
5
6
7
select * from t1 where a >= 1 and a < 3;
a
1
2
select * from t1 where a >= 3 and a <= 5;
a
3
4
5
select * from t1 where a > 2 and a < 4;
a
3
select * from t1 where a > 3 and a <= 6;
a
4
5
6
select * from t1 where a > 5;
a
6
7
select * from t1 where a >= 1 and a <= 5;
a
1
2
3
4
5
explain partitions select * from t1 where a < 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0,p2,p3	ALL	NULL	NULL	NULL	NULL	7	Using where
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	3	Using where
explain partitions select * from t1 where a is not null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
explain partitions select * from t1 where a >= 1 and a < 3;
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
explain partitions select * from t1 where a >= 3 and a <= 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	4	Using where
explain partitions select * from t1 where a > 2 and a < 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
explain partitions select * from t1 where a > 3 and a <= 6;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	4	Using where
explain partitions select * from t1 where a > 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
explain partitions select * from t1 where a >= 1 and a <= 5;
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
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by hash (a + 2)
partitions 3
(partition x1 tablespace ts1,
partition x2 tablespace ts2,
partition x3 tablespace ts3);
insert into t1 values (1,1,1);
insert into t1 values (2,1,1);
insert into t1 values (3,1,1);
insert into t1 values (4,1,1);
insert into t1 values (5,1,1);
select * from t1;
a	b	c
1	1	1
4	1	1
2	1	1
5	1	1
3	1	1
update t1 set c=3 where b=1;
select * from t1;
a	b	c
1	1	3
4	1	3
2	1	3
5	1	3
3	1	3
select b from t1 where a=3;
b
1
select b,c from t1 where a=1 AND b=1;
b	c
1	3
delete from t1 where a=1;
delete from t1 where c=3;
select * from t1;
a	b	c
ALTER TABLE t1
partition by hash (a + 3)
partitions 3
(partition x1 tablespace ts1,
partition x2 tablespace ts2,
partition x3 tablespace ts3);
select * from t1;
a	b	c
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by hash (a)
(partition x1);
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by key (a)
(partition x1);
drop table t1;
CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2;
INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######';
select * from t1;
f1	f2
-1	#######
drop table t1;
set sql_mode="";
CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
INSERT INTO t1 VALUES (0);
DROP TABLE t1;
set sql_mode=default;
create table t1 (c1 int DEFAULT NULL,
c2 varchar (30) DEFAULT NULL,
c3 date DEFAULT NULL)
engine = myisam
partition by hash (to_days(c3))
partitions 12;
insert into t1 values
(136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'),
(182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22');
select * from t1;
c1	c2	c3
136	abc	2002-01-05
158	abc	2002-06-01
142	abc	2002-02-14
162	abc	2002-06-28
182	abc	2002-11-09
184	abc	2002-11-22
select * from t1 where c3 between '2002-01-01' and '2002-12-31';
c1	c2	c3
136	abc	2002-01-05
158	abc	2002-06-01
142	abc	2002-02-14
162	abc	2002-06-28
182	abc	2002-11-09
184	abc	2002-11-22
drop table t1;
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
INSERT DELAYED INTO t1 VALUES (1);
DROP TABLE t1;