Debian Codegraph @

mysql-test/suite/ndb/t/ndb_partition_key.test

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1;
5 --enable_warnings
6
7 #
8 # Basic syntax test
9 #
10
11 # Support for partition key verified
12 CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b,c))
13 ENGINE = NDB
14 PARTITION BY KEY (a,b);
15
16 insert into t1 values (1,1,1,1);
17 select * from t1;
18 update t1 set d = 2 where a = 1 and b = 1 and c = 1;
19 select * from t1;
20 delete from t1;
21 select * from t1;
22
23 drop table t1;
24
25 # only support for partition key on primary key
26 --error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
27 CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
28 ENGINE = NDB
29 PARTITION BY KEY (c);
30
31 CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a,b))
32 ENGINE = NDB
33 PARTITION BY KEY (a);
34
35 # Trigger assert from bug#19856162 with an alter table
36 # not actually doing any changes to partitioniong
37 ALTER TABLE t1 ALGORITHM=INPLACE, REORGANIZE PARTITION;
38
39 insert into t1 values
40 (1,1,3),(1,2,3),(1,3,3),(1,4,3),(1,5,3),(1,6,3),
41 (1,7,3),(1,8,3),(1,9,3),(1,10,3),(1,11,3),(1,12,3);
42
43 select * from t1 order by b;
44
45 # BUG#33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition
46 select max(b) from t1 where a = 1;
47 select b from t1 where a = 1 order by b desc;
48
49 DROP TABLE t1;
50
51 #
52 # Test partition and char support
53 #
54
55 CREATE TABLE t1 (a INT, b CHAR(10) COLLATE latin1_bin, c INT, d INT,
56 PRIMARY KEY (a,b,c) USING HASH)
57 ENGINE=NDB
58 DEFAULT CHARSET=latin1
59 PARTITION BY KEY (b);
60
61 insert into t1 values (1,"a",1,1),(2,"a",1,1),(3,"a",1,1);
62
63 # should show only one attribute with DISTRIBUTION KEY
64 source show_attributes.inc;
65
66 #
67 # Test that explicit partition info is not shown in show create table
68 # result should not contain (PARTITION P0 ... etc) since this is what shows up in
69 # mysqldump, and we don't want that info there
70 #
71 --disable_warnings
72 show create table t1;
73 --enable_warnings
74
75 DROP TABLE t1;
76
77 #
78 # Bug #13155: Problem in Create Table using SHOW CREATE TABLE syntax
79 #
80 CREATE TABLE t1 (a int not null primary key)
81 PARTITION BY KEY(a)
82 (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
83
84 drop table t1;
85
86 CREATE TABLE t1 (a int not null primary key);
87 ALTER TABLE t1 ALGORITHM=COPY,
88 ENGINE = NDB
89 PARTITION BY KEY(a)
90 (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
91
92 drop table t1;
93
94 CREATE TABLE t1 (a int not null primary key) ENGINE = NDB;
95 ALTER TABLE t1
96 PARTITION BY KEY(a)
97 (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
98
99 drop table t1;
100
101 #
102 # Bug #17754 Improper handling of removal of partitioning in ALTER TABLE
103 # Also added a number of general test cases in the same area
104 #
105 create table t1 (a int primary key)
106 engine=ndb
107 partition by key(a)
108 (partition p0, partition p1);
109 --disable_warnings
110 show create table t1;
111 --enable_warnings
112
113 alter table t1 engine=innodb;
114 show create table t1;
115
116 alter table t1 engine=ndb;
117 --disable_warnings
118 show create table t1;
119 --enable_warnings
120
121 alter table t1 engine=innodb remove partitioning;
122 show create table t1;
123
124 alter table t1 engine=ndb
125 partition by key(a)
126 (partition p0, partition p1 engine = ndb);
127 --disable_warnings
128 show create table t1;
129 --enable_warnings
130
131 alter table t1
132 partition by key (a)
133 (partition p0 engine=ndb, partition p1 engine=ndb);
134 --disable_warnings
135 show create table t1;
136 --enable_warnings
137
138 alter table t1 remove partitioning;
139 --disable_warnings
140 show create table t1;
141 --enable_warnings
142
143 # after bug#31931 was fixed
144 # this is OK, since the storage engine is specified
145 # on table level before.
146 #--error ER_MIX_HANDLER_ERROR
147 alter table t1
148 partition by key(a)
149 (partition p0 engine=ndb, partition p1);
150
151 alter table t1
152 engine=ndb
153 partition by key(a)
154 (partition p0 engine=ndb, partition p1 engine = ndb);
155 --disable_warnings
156 show create table t1;
157 --enable_warnings
158
159 drop table t1;
160
161 #
162 # BUG 16810 Out of memory when coalesce partition
163 #
164 CREATE TABLE t1 (
165 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
166 c2 TEXT NOT NULL,
167 c3 INT NOT NULL,
168 c4 BIT NOT NULL,
169 c5 FLOAT,
170 c6 VARCHAR(255),
171 c7 TIMESTAMP,
172 PRIMARY KEY(c1,c3))
173 ENGINE=NDB
174 PARTITION BY KEY(c3) PARTITIONS 5;
175
176 let $j= 11;
177 --disable_query_log
178 while ($j)
179 {
180 eval INSERT INTO t1 VALUES (NULL, "Tested Remotely from Texas, USA", $j,
181 b'0',
182 $j.00,"By JBM $j","2006-01-26");
183 dec $j;
184 }
185 --enable_query_log
186 ALTER TABLE t1 ALGORITHM=COPY, COALESCE PARTITION 4;
187
188 DROP TABLE t1;
189
190 #
191 # Bug 16822: OPTIMIZE TABLE hangs test
192 #
193 CREATE TABLE t1 (a int primary key)
194 ENGINE=NDB
195 PARTITION BY KEY(a);
196 ANALYZE TABLE t1;
197 CHECK TABLE t1;
198 OPTIMIZE TABLE t1;
199 REPAIR TABLE t1;
200 ALTER TABLE t1 ALGORITHM=INPLACE, OPTIMIZE PARTITION p0;
201 ALTER TABLE t1 ALGORITHM=INPLACE, CHECK PARTITION p0;
202 ALTER TABLE t1 ALGORITHM=INPLACE, REPAIR PARTITION p0;
203 ALTER TABLE t1 ALGORITHM=INPLACE, ANALYZE PARTITION p0;
204 --error ER_ILLEGAL_HA
205 ALTER TABLE t1 ALGORITHM=INPLACE, REBUILD PARTITION p0;
206 DROP TABLE t1;
207
208 #
209 # BUG 16806: ALTER TABLE fails
210 #
211 CREATE TABLE t1 (
212 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
213 c2 TEXT NOT NULL,
214 c3 INT NOT NULL,
215 PRIMARY KEY(c1,c3))
216 ENGINE=NDB
217 PARTITION BY KEY(c3) PARTITIONS 5;
218
219 ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
220 DROP TABLE t1;
221
222 CREATE TABLE t1 (
223 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
224 c2 TEXT NOT NULL,
225 c3 INT NOT NULL,
226 PRIMARY KEY(c1,c3))
227 ENGINE=NDB
228 PARTITION BY KEY(c3)
229 (PARTITION p0 NODEGROUP 0, PARTITION p1 NODEGROUP 0);
230
231 ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
232 --sorted_result
233 SELECT NODEGROUP,PARTITION_NAME FROM information_schema.partitions WHERE
234 table_name = "t1";
235 DROP TABLE t1;
236
237 # bug#25587
238
239 CREATE TABLE t1 (
240 a tinyint unsigned NOT NULL,
241 b bigint(20) unsigned NOT NULL,
242 c char(12),
243 PRIMARY KEY (a,b)
244 ) ENGINE ndb DEFAULT CHARSET=latin1 PARTITION BY KEY (a);
245
246 insert into t1 values(1,1,'1'), (2,2,'2'), (3,3,'3'), (4,4,'4'), (5,5,'5');
247 select * from t1 where a = 1;
248 select * from t1 where a = 2;
249 select * from t1 where a = 3;
250 select * from t1 where a = 4;
251 select * from t1 where a = 5;
252 delete from t1 where a = 1;
253 select * from t1 order by 1;
254 delete from t1 where a = 2;
255 select * from t1 order by 1;
256 delete from t1 where a = 3;
257 select * from t1 order by 1;
258 delete from t1 where a = 4;
259 select * from t1 order by 1;
260 delete from t1 where a = 5;
261 select * from t1 order by 1;
262
263 drop table t1;
264
265 # Create table with distribution key != partition key to
266 # test some partition pruning stuff
267 #
268 create table nott1 (a int,
269 b int,
270 c int,
271 primary key(a,b))
272 engine=ndb partition by key(a);
273
274 insert into nott1 values (0,0,0);
275 insert into nott1 values (0,1,1);
276 insert into nott1 values (0,2,2);
277 insert into nott1 values (0,3,3);
278 insert into nott1 values (1,0,4);
279 insert into nott1 values (1,1,5);
280 insert into nott1 values (1,2,6);
281 insert into nott1 values (2,22,7);
282
283 --echo All partitions scan
284 --source suite/ndb/include/ndb_init_scan_counts.inc
285 select * from nott1 order by c;
286 --source suite/ndb/include/ndb_scan_counts.inc
287
288 --echo Single partition scan
289 --source suite/ndb/include/ndb_init_scan_counts.inc
290 select * from nott1 where a=0 order by c;
291 --source suite/ndb/include/ndb_scan_counts.inc
292
293 --echo Single partition scan
294 --source suite/ndb/include/ndb_init_scan_counts.inc
295 select * from nott1 where a=4 order by c;
296 --source suite/ndb/include/ndb_scan_counts.inc
297
298 --echo MRR scan on one partition
299 --source suite/ndb/include/ndb_init_scan_counts.inc
300 select * from nott1 where a=0 and (( b > 0 and b < 3) or
301 ( b > 5 and b < 10) or
302 ( b > 22 and b < 50)) order by c;
303 --source suite/ndb/include/ndb_scan_counts.inc
304
305 drop table nott1;
306
307 #
308 #
309 #
310 CREATE TABLE t1 (
311 a char(10) NOT NULL,
312 b char(1) NOT NULL,
313 PRIMARY KEY (a,b)
314 ) ENGINE ndb DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
315 PARTITION BY KEY (a);
316
317 insert into t1 values ('a', 'a'), ('b', 'b');
318 select * from t1 where a = 'a';
319 drop table t1;
320
321 CREATE TABLE t1 (
322 a varchar(3000) NOT NULL,
323 b char(1) NOT NULL,
324 PRIMARY KEY (a,b)
325 ) ENGINE ndb DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
326 PARTITION BY KEY (a);
327
328 insert into t1 values ('a', 'a'), ('b', 'b');
329 select * from t1 where a = 'a';
330 drop table t1;