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
|
#--disable_abort_on_error
#
# Simple test for the partition storage engine
# Taken fromm the select test
#
-- source include/have_partition.inc
--disable_warnings
drop table if exists t1;
--enable_warnings
#
# Bug#30822: crash when COALESCE
#
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;
#
# More partition pruning tests, especially on interval walking
#
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;
select * from t1 where a is null or (a >= 5 and a <= 7);
select * from t1 where a is null;
select * from t1 where a is not null;
select * from t1 where a >= 1 and a < 3;
select * from t1 where a >= 3 and a <= 5;
select * from t1 where a > 2 and a < 4;
select * from t1 where a > 3 and a <= 6;
select * from t1 where a > 5;
select * from t1 where a >= 1 and a <= 5;
explain partitions select * from t1 where a < 0;
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
explain partitions select * from t1 where a is null;
explain partitions select * from t1 where a is not null;
explain partitions select * from t1 where a >= 1 and a < 3;
explain partitions select * from t1 where a >= 3 and a <= 5;
explain partitions select * from t1 where a > 2 and a < 4;
explain partitions select * from t1 where a > 3 and a <= 6;
explain partitions select * from t1 where a > 5;
explain partitions select * from t1 where a >= 1 and a <= 5;
drop table t1;
#
# Partition by hash, basic
#
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;
update t1 set c=3 where b=1;
select * from t1;
select b from t1 where a=3;
select b,c from t1 where a=1 AND b=1;
delete from t1 where a=1;
delete from t1 where c=3;
select * from t1;
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;
drop table t1;
#
# Partition by hash, only one partition
#
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;
#
# Partition by key, only one partition
#
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;
#
# Bug# 15968 - crash when INSERT with f1 = -1 into partition by hash(f1)
#
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;
drop table t1;
#
# BUG# 14524 Partitions: crash if blackhole
#
# Disable warnings to allow this test case to run without
# the Blackhole storage engine.
set sql_mode="";
--disable_warnings
CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
--enable_warnings
--error 0,ER_BINLOG_LOGGING_IMPOSSIBLE
INSERT INTO t1 VALUES (0);
DROP TABLE t1;
set sql_mode=default;
#
# BUG 18423 Hash partitioning can lose rows in some queries
#
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;
select * from t1 where c3 between '2002-01-01' and '2002-12-31';
drop table t1;
#
# Bug#31210 - INSERT DELAYED crashes server when used on partitioned table
#
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
# The test succeeds in an embedded server because normal insert is done.
# The test fails in a normal server with
# "DELAYED option not supported by table".
--error 0, ER_DELAYED_NOT_SUPPORTED
INSERT DELAYED INTO t1 VALUES (1);
DROP TABLE t1;
|
