Debian Codegraph @

mysql-test/suite/ndb/r/ndb_partition_key.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b,c))
3 ENGINE = NDB
4 PARTITION BY KEY (a,b);
5 insert into t1 values (1,1,1,1);
6 select * from t1;
7 a b c d
8 1 1 1 1
9 update t1 set d = 2 where a = 1 and b = 1 and c = 1;
10 select * from t1;
11 a b c d
12 1 1 1 2
13 delete from t1;
14 select * from t1;
15 a b c d
16 drop table t1;
17 CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
18 ENGINE = NDB
19 PARTITION BY KEY (c);
20 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
21 CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a,b))
22 ENGINE = NDB
23 PARTITION BY KEY (a);
24 ALTER TABLE t1 ALGORITHM=INPLACE, REORGANIZE PARTITION;
25 insert into t1 values
26 (1,1,3),(1,2,3),(1,3,3),(1,4,3),(1,5,3),(1,6,3),
27 (1,7,3),(1,8,3),(1,9,3),(1,10,3),(1,11,3),(1,12,3);
28 select * from t1 order by b;
29 a b c
30 1 1 3
31 1 2 3
32 1 3 3
33 1 4 3
34 1 5 3
35 1 6 3
36 1 7 3
37 1 8 3
38 1 9 3
39 1 10 3
40 1 11 3
41 1 12 3
42 select max(b) from t1 where a = 1;
43 max(b)
44 12
45 select b from t1 where a = 1 order by b desc;
46 b
47 12
48 11
49 10
50 9
51 8
52 7
53 6
54 5
55 4
56 3
57 2
58 1
59 DROP TABLE t1;
60 CREATE TABLE t1 (a INT, b CHAR(10) COLLATE latin1_bin, c INT, d INT,
61 PRIMARY KEY (a,b,c) USING HASH)
62 ENGINE=NDB
63 DEFAULT CHARSET=latin1
64 PARTITION BY KEY (b);
65 insert into t1 values (1,"a",1,1),(2,"a",1,1),(3,"a",1,1);
66 Attributes:
67 a Int PRIMARY KEY AT=FIXED ST=MEMORY
68 b Char(10;latin1_bin) PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
69 c Int PRIMARY KEY AT=FIXED ST=MEMORY
70 d Int NULL AT=FIXED ST=MEMORY
71 show create table t1;
72 Table Create Table
73 t1 CREATE TABLE `t1` (
74 `a` int NOT NULL,
75 `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
76 `c` int NOT NULL,
77 `d` int DEFAULT NULL,
78 PRIMARY KEY (`a`,`b`,`c`) USING HASH
79 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
80 /*!50100 PARTITION BY KEY (b) */
81 DROP TABLE t1;
82 CREATE TABLE t1 (a int not null primary key)
83 PARTITION BY KEY(a)
84 (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
85 drop table t1;
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 drop table t1;
92 CREATE TABLE t1 (a int not null primary key) ENGINE = NDB;
93 ALTER TABLE t1
94 PARTITION BY KEY(a)
95 (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
96 drop table t1;
97 create table t1 (a int primary key)
98 engine=ndb
99 partition by key(a)
100 (partition p0, partition p1);
101 show create table t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `a` int NOT NULL,
105 PRIMARY KEY (`a`)
106 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
107 /*!50100 PARTITION BY KEY (a)
108 (PARTITION p0 ENGINE = ndbcluster,
109 PARTITION p1 ENGINE = ndbcluster) */
110 alter table t1 engine=innodb;
111 show create table t1;
112 Table Create Table
113 t1 CREATE TABLE `t1` (
114 `a` int NOT NULL,
115 PRIMARY KEY (`a`)
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
117 /*!50100 PARTITION BY KEY (a)
118 (PARTITION p0 ENGINE = InnoDB,
119 PARTITION p1 ENGINE = InnoDB) */
120 alter table t1 engine=ndb;
121 show create table t1;
122 Table Create Table
123 t1 CREATE TABLE `t1` (
124 `a` int NOT NULL,
125 PRIMARY KEY (`a`)
126 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
127 /*!50100 PARTITION BY KEY (a)
128 (PARTITION p0 ENGINE = ndbcluster,
129 PARTITION p1 ENGINE = ndbcluster) */
130 alter table t1 engine=innodb remove partitioning;
131 show create table t1;
132 Table Create Table
133 t1 CREATE TABLE `t1` (
134 `a` int NOT NULL,
135 PRIMARY KEY (`a`)
136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
137 alter table t1 engine=ndb
138 partition by key(a)
139 (partition p0, partition p1 engine = ndb);
140 show create table t1;
141 Table Create Table
142 t1 CREATE TABLE `t1` (
143 `a` int NOT NULL,
144 PRIMARY KEY (`a`)
145 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
146 /*!50100 PARTITION BY KEY (a)
147 (PARTITION p0 ENGINE = ndbcluster,
148 PARTITION p1 ENGINE = ndbcluster) */
149 alter table t1
150 partition by key (a)
151 (partition p0 engine=ndb, partition p1 engine=ndb);
152 show create table t1;
153 Table Create Table
154 t1 CREATE TABLE `t1` (
155 `a` int NOT NULL,
156 PRIMARY KEY (`a`)
157 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
158 /*!50100 PARTITION BY KEY (a)
159 (PARTITION p0 ENGINE = ndbcluster,
160 PARTITION p1 ENGINE = ndbcluster) */
161 alter table t1 remove partitioning;
162 show create table t1;
163 Table Create Table
164 t1 CREATE TABLE `t1` (
165 `a` int NOT NULL,
166 PRIMARY KEY (`a`)
167 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
168 alter table t1
169 partition by key(a)
170 (partition p0 engine=ndb, partition p1);
171 alter table t1
172 engine=ndb
173 partition by key(a)
174 (partition p0 engine=ndb, partition p1 engine = ndb);
175 show create table t1;
176 Table Create Table
177 t1 CREATE TABLE `t1` (
178 `a` int NOT NULL,
179 PRIMARY KEY (`a`)
180 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
181 /*!50100 PARTITION BY KEY (a)
182 (PARTITION p0 ENGINE = ndbcluster,
183 PARTITION p1 ENGINE = ndbcluster) */
184 drop table t1;
185 CREATE TABLE t1 (
186 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
187 c2 TEXT NOT NULL,
188 c3 INT NOT NULL,
189 c4 BIT NOT NULL,
190 c5 FLOAT,
191 c6 VARCHAR(255),
192 c7 TIMESTAMP,
193 PRIMARY KEY(c1,c3))
194 ENGINE=NDB
195 PARTITION BY KEY(c3) PARTITIONS 5;
196 ALTER TABLE t1 ALGORITHM=COPY, COALESCE PARTITION 4;
197 DROP TABLE t1;
198 CREATE TABLE t1 (a int primary key)
199 ENGINE=NDB
200 PARTITION BY KEY(a);
201 ANALYZE TABLE t1;
202 Table Op Msg_type Msg_text
203 test.t1 analyze status OK
204 CHECK TABLE t1;
205 Table Op Msg_type Msg_text
206 test.t1 check note The storage engine for the table doesn't support check
207 OPTIMIZE TABLE t1;
208 Table Op Msg_type Msg_text
209 test.t1 optimize status OK
210 REPAIR TABLE t1;
211 Table Op Msg_type Msg_text
212 test.t1 repair note The storage engine for the table doesn't support repair
213 ALTER TABLE t1 ALGORITHM=INPLACE, OPTIMIZE PARTITION p0;
214 Table Op Msg_type Msg_text
215 test.t1 optimize status OK
216 ALTER TABLE t1 ALGORITHM=INPLACE, CHECK PARTITION p0;
217 Table Op Msg_type Msg_text
218 test.t1 check note The storage engine for the table doesn't support check
219 ALTER TABLE t1 ALGORITHM=INPLACE, REPAIR PARTITION p0;
220 Table Op Msg_type Msg_text
221 test.t1 repair note The storage engine for the table doesn't support repair
222 ALTER TABLE t1 ALGORITHM=INPLACE, ANALYZE PARTITION p0;
223 Table Op Msg_type Msg_text
224 test.t1 analyze status OK
225 ALTER TABLE t1 ALGORITHM=INPLACE, REBUILD PARTITION p0;
226 ERROR HY000: Table storage engine for 't1' doesn't have this option
227 DROP TABLE t1;
228 CREATE TABLE t1 (
229 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
230 c2 TEXT NOT NULL,
231 c3 INT NOT NULL,
232 PRIMARY KEY(c1,c3))
233 ENGINE=NDB
234 PARTITION BY KEY(c3) PARTITIONS 5;
235 ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
236 DROP TABLE t1;
237 CREATE TABLE t1 (
238 c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
239 c2 TEXT NOT NULL,
240 c3 INT NOT NULL,
241 PRIMARY KEY(c1,c3))
242 ENGINE=NDB
243 PARTITION BY KEY(c3)
244 (PARTITION p0 NODEGROUP 0, PARTITION p1 NODEGROUP 0);
245 ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
246 SELECT NODEGROUP,PARTITION_NAME FROM information_schema.partitions WHERE
247 table_name = "t1";
248 NODEGROUP PARTITION_NAME
249 0 p0
250 0 p1
251 DROP TABLE t1;
252 CREATE TABLE t1 (
253 a tinyint unsigned NOT NULL,
254 b bigint(20) unsigned NOT NULL,
255 c char(12),
256 PRIMARY KEY (a,b)
257 ) ENGINE ndb DEFAULT CHARSET=latin1 PARTITION BY KEY (a);
258 Warnings:
259 Warning 1681 Integer display width is deprecated and will be removed in a future release.
260 insert into t1 values(1,1,'1'), (2,2,'2'), (3,3,'3'), (4,4,'4'), (5,5,'5');
261 select * from t1 where a = 1;
262 a b c
263 1 1 1
264 select * from t1 where a = 2;
265 a b c
266 2 2 2
267 select * from t1 where a = 3;
268 a b c
269 3 3 3
270 select * from t1 where a = 4;
271 a b c
272 4 4 4
273 select * from t1 where a = 5;
274 a b c
275 5 5 5
276 delete from t1 where a = 1;
277 select * from t1 order by 1;
278 a b c
279 2 2 2
280 3 3 3
281 4 4 4
282 5 5 5
283 delete from t1 where a = 2;
284 select * from t1 order by 1;
285 a b c
286 3 3 3
287 4 4 4
288 5 5 5
289 delete from t1 where a = 3;
290 select * from t1 order by 1;
291 a b c
292 4 4 4
293 5 5 5
294 delete from t1 where a = 4;
295 select * from t1 order by 1;
296 a b c
297 5 5 5
298 delete from t1 where a = 5;
299 select * from t1 order by 1;
300 a b c
301 drop table t1;
302 create table nott1 (a int,
303 b int,
304 c int,
305 primary key(a,b))
306 engine=ndb partition by key(a);
307 insert into nott1 values (0,0,0);
308 insert into nott1 values (0,1,1);
309 insert into nott1 values (0,2,2);
310 insert into nott1 values (0,3,3);
311 insert into nott1 values (1,0,4);
312 insert into nott1 values (1,1,5);
313 insert into nott1 values (1,2,6);
314 insert into nott1 values (2,22,7);
315 All partitions scan
316 select * from nott1 order by c;
317 a b c
318 0 0 0
319 0 1 1
320 0 2 2
321 0 3 3
322 1 0 4
323 1 1 5
324 1 2 6
325 2 22 7
326 ndb_scan_count
327 1
328 ndb_pruned_scan_count
329 0
330 Single partition scan
331 select * from nott1 where a=0 order by c;
332 a b c
333 0 0 0
334 0 1 1
335 0 2 2
336 0 3 3
337 ndb_scan_count
338 1
339 ndb_pruned_scan_count
340 1
341 Single partition scan
342 select * from nott1 where a=4 order by c;
343 a b c
344 ndb_scan_count
345 1
346 ndb_pruned_scan_count
347 1
348 MRR scan on one partition
349 select * from nott1 where a=0 and (( b > 0 and b < 3) or
350 ( b > 5 and b < 10) or
351 ( b > 22 and b < 50)) order by c;
352 a b c
353 0 1 1
354 0 2 2
355 ndb_scan_count
356 1
357 ndb_pruned_scan_count
358 1
359 drop table nott1;
360 CREATE TABLE t1 (
361 a char(10) NOT NULL,
362 b char(1) NOT NULL,
363 PRIMARY KEY (a,b)
364 ) ENGINE ndb DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
365 PARTITION BY KEY (a);
366 insert into t1 values ('a', 'a'), ('b', 'b');
367 select * from t1 where a = 'a';
368 a b
369 a a
370 drop table t1;
371 CREATE TABLE t1 (
372 a varchar(3000) NOT NULL,
373 b char(1) NOT NULL,
374 PRIMARY KEY (a,b)
375 ) ENGINE ndb DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
376 PARTITION BY KEY (a);
377 insert into t1 values ('a', 'a'), ('b', 'b');
378 select * from t1 where a = 'a';
379 a b
380 a a
381 drop table t1;