Debian Codegraph @

mysql-test/suite/parts/r/partition_bit_innodb.result

raw
1 SET @max_row = 20;
2 drop table if exists t1;
3 create table t1 (a bit(65), primary key (a)) engine='INNODB' partition by key (a);
4 ERROR 42000: Display width out of range for column 'a' (max = 64)
5 create table t1 (a bit(0), primary key (a)) engine='INNODB' partition by key (a);
6 ERROR HY000: Invalid size for column 'a'.
7 create table t1 (a bit(0), primary key (a)) engine='INNODB'
8 partition by key (a) (
9 partition pa1,
10 partition pa2);
11 ERROR HY000: Invalid size for column 'a'.
12 create table t1 (a bit(64), primary key (a)) engine='INNODB'
13 partition by key (a) partitions 2;
14 show create table t1;
15 Table Create Table
16 t1 CREATE TABLE `t1` (
17 `a` bit(64) NOT NULL,
18 PRIMARY KEY (`a`)
19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
20 /*!50100 PARTITION BY KEY (a)
21 PARTITIONS 2 */
22 insert into t1 values
23 (b'1111111111111111111111111111111111111111111111111111111111111111'),
24 (b'1000000000000000000000000000000000000000000000000000000000000000'),
25 (b'0000000000000000000000000000000000000000000000000000000000000001'),
26 (b'1010101010101010101010101010101010101010101010101010101010101010'),
27 (b'0101010101010101010101010101010101010101010101010101010101010101');
28 select hex(a) from t1;
29 hex(a)
30 1
31 5555555555555555
32 8000000000000000
33 AAAAAAAAAAAAAAAA
34 FFFFFFFFFFFFFFFF
35 drop table t1;
36 create table t1 (a bit(64), primary key (a)) engine='INNODB'
37 partition by key (a) (
38 partition pa1 max_rows=20 min_rows=2,
39 partition pa2 max_rows=30 min_rows=3,
40 partition pa3 max_rows=30 min_rows=4,
41 partition pa4 max_rows=40 min_rows=2);
42 show create table t1;
43 Table Create Table
44 t1 CREATE TABLE `t1` (
45 `a` bit(64) NOT NULL,
46 PRIMARY KEY (`a`)
47 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
48 /*!50100 PARTITION BY KEY (a)
49 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
50 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
51 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
52 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
53 insert into t1 values
54 (b'1111111111111111111111111111111111111111111111111111111111111111'),
55 (b'1000000000000000000000000000000000000000000000000000000000000000'),
56 (b'0000000000000000000000000000000000000000000000000000000000000001'),
57 (b'1010101010101010101010101010101010101010101010101010101010101010'),
58 (b'0101010101010101010101010101010101010101010101010101010101010101');
59 select hex(a) from t1 where a=b'0101010101010101010101010101010101010101010101010101010101010101';
60 hex(a)
61 5555555555555555
62 delete from t1 where a=b'0101010101010101010101010101010101010101010101010101010101010101';
63 select hex(a) from t1;
64 hex(a)
65 1
66 8000000000000000
67 AAAAAAAAAAAAAAAA
68 FFFFFFFFFFFFFFFF
69 drop table t1;
70 create table t2 (a bit, primary key (a)) engine='INNODB'
71 partition by key (a) partitions 4;
72 show create table t2;
73 Table Create Table
74 t2 CREATE TABLE `t2` (
75 `a` bit(1) NOT NULL,
76 PRIMARY KEY (`a`)
77 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
78 /*!50100 PARTITION BY KEY (a)
79 PARTITIONS 4 */
80 insert into t2 values (b'0'), (b'1');
81 select hex(a) from t2;
82 hex(a)
83 0
84 1
85 alter table t2 drop primary key;
86 show create table t2;
87 Table Create Table
88 t2 CREATE TABLE `t2` (
89 `a` bit(1) NOT NULL
90 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
91 /*!50100 PARTITION BY KEY (a)
92 PARTITIONS 4 */
93 select hex(a) from t2;
94 hex(a)
95 0
96 1
97 alter table t2 add primary key (a);
98 show create table t2;
99 Table Create Table
100 t2 CREATE TABLE `t2` (
101 `a` bit(1) NOT NULL,
102 PRIMARY KEY (`a`)
103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
104 /*!50100 PARTITION BY KEY (a)
105 PARTITIONS 4 */
106 select hex(a) from t2;
107 hex(a)
108 0
109 1
110 drop table t2;
111 create table t3 (a bit(8), primary key (a)) engine='INNODB'
112 partition by range (a) subpartition by key (a) subpartitions 2 (
113 partition pa1 values less than (3),
114 partition pa2 values less than (16),
115 partition pa3 values less than (64),
116 partition pa4 values less than (256));
117 show create table t3;
118 Table Create Table
119 t3 CREATE TABLE `t3` (
120 `a` bit(8) NOT NULL,
121 PRIMARY KEY (`a`)
122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
123 /*!50100 PARTITION BY RANGE (`a`)
124 SUBPARTITION BY KEY (a)
125 SUBPARTITIONS 2
126 (PARTITION pa1 VALUES LESS THAN (3) ENGINE = InnoDB,
127 PARTITION pa2 VALUES LESS THAN (16) ENGINE = InnoDB,
128 PARTITION pa3 VALUES LESS THAN (64) ENGINE = InnoDB,
129 PARTITION pa4 VALUES LESS THAN (256) ENGINE = InnoDB) */
130 255 inserts;
131 select hex(a) from t3 where a=b'01010101';
132 hex(a)
133 55
134 delete from t3 where a=b'01010101';
135 select count(*) from t3;
136 count(*)
137 254
138 select hex(a) from t3;
139 hex(a)
140 1
141 10
142 11
143 12
144 13
145 14
146 15
147 16
148 17
149 18
150 19
151 1A
152 1B
153 1C
154 1D
155 1E
156 1F
157 2
158 20
159 21
160 22
161 23
162 24
163 25
164 26
165 27
166 28
167 29
168 2A
169 2B
170 2C
171 2D
172 2E
173 2F
174 3
175 30
176 31
177 32
178 33
179 34
180 35
181 36
182 37
183 38
184 39
185 3A
186 3B
187 3C
188 3D
189 3E
190 3F
191 4
192 40
193 41
194 42
195 43
196 44
197 45
198 46
199 47
200 48
201 49
202 4A
203 4B
204 4C
205 4D
206 4E
207 4F
208 5
209 50
210 51
211 52
212 53
213 54
214 56
215 57
216 58
217 59
218 5A
219 5B
220 5C
221 5D
222 5E
223 5F
224 6
225 60
226 61
227 62
228 63
229 64
230 65
231 66
232 67
233 68
234 69
235 6A
236 6B
237 6C
238 6D
239 6E
240 6F
241 7
242 70
243 71
244 72
245 73
246 74
247 75
248 76
249 77
250 78
251 79
252 7A
253 7B
254 7C
255 7D
256 7E
257 7F
258 8
259 80
260 81
261 82
262 83
263 84
264 85
265 86
266 87
267 88
268 89
269 8A
270 8B
271 8C
272 8D
273 8E
274 8F
275 9
276 90
277 91
278 92
279 93
280 94
281 95
282 96
283 97
284 98
285 99
286 9A
287 9B
288 9C
289 9D
290 9E
291 9F
292 A
293 A0
294 A1
295 A2
296 A3
297 A4
298 A5
299 A6
300 A7
301 A8
302 A9
303 AA
304 AB
305 AC
306 AD
307 AE
308 AF
309 B
310 B0
311 B1
312 B2
313 B3
314 B4
315 B5
316 B6
317 B7
318 B8
319 B9
320 BA
321 BB
322 BC
323 BD
324 BE
325 BF
326 C
327 C0
328 C1
329 C2
330 C3
331 C4
332 C5
333 C6
334 C7
335 C8
336 C9
337 CA
338 CB
339 CC
340 CD
341 CE
342 CF
343 D
344 D0
345 D1
346 D2
347 D3
348 D4
349 D5
350 D6
351 D7
352 D8
353 D9
354 DA
355 DB
356 DC
357 DD
358 DE
359 DF
360 E
361 E0
362 E1
363 E2
364 E3
365 E4
366 E5
367 E6
368 E7
369 E8
370 E9
371 EA
372 EB
373 EC
374 ED
375 EE
376 EF
377 F
378 F0
379 F1
380 F2
381 F3
382 F4
383 F5
384 F6
385 F7
386 F8
387 F9
388 FA
389 FB
390 FC
391 FD
392 FE
393 FF
394 drop table t3;
395 create table t4 (a bit(8), primary key (a)) engine='INNODB'
396 partition by list (a) subpartition by key (a) subpartitions 2 (
397 partition pa1 values in (0,1,2,3),
398 partition pa2 values in (4,5,6,7,8,9,10,11,12,13,14,15,16),
399 partition pa3 values in (17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32));
400 show create table t4;
401 Table Create Table
402 t4 CREATE TABLE `t4` (
403 `a` bit(8) NOT NULL,
404 PRIMARY KEY (`a`)
405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
406 /*!50100 PARTITION BY LIST (`a`)
407 SUBPARTITION BY KEY (a)
408 SUBPARTITIONS 2
409 (PARTITION pa1 VALUES IN (0,1,2,3) ENGINE = InnoDB,
410 PARTITION pa2 VALUES IN (4,5,6,7,8,9,10,11,12,13,14,15,16) ENGINE = InnoDB,
411 PARTITION pa3 VALUES IN (17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32) ENGINE = InnoDB) */
412 32 inserts;
413 select hex(a) from t4 where a=b'00000001';
414 hex(a)
415 1
416 delete from t4 where a=b'00000001';
417 select count(*) from t4;
418 count(*)
419 31
420 select hex(a) from t4;
421 hex(a)
422 10
423 11
424 12
425 13
426 14
427 15
428 16
429 17
430 18
431 19
432 1A
433 1B
434 1C
435 1D
436 1E
437 1F
438 2
439 20
440 3
441 4
442 5
443 6
444 7
445 8
446 9
447 A
448 B
449 C
450 D
451 E
452 F
453 drop table t4;