[tomato.git] / release / src / router / mysql / mysql-test / suite / parts / r / partition_mgm_lc1_memory.result
| blob | 6f34054428c88361cb42ab2228b55a240439ef48 |
1 # Creating database MySQL_TEST_DB
2 CREATE DATABASE MySQL_Test_DB;
3 USE MySQL_Test_DB;
4 # 1.0 KEY partitioning mgm
5 # Creating KEY partitioned table
6 CREATE TABLE TableA (a INT)
7 ENGINE = 'Memory'
8 PARTITION BY KEY (a)
9 (PARTITION parta ,
10 PARTITION partB ,
11 PARTITION Partc ,
12 PARTITION PartD );
13 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
14 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
15 SELECT * FROM TableA;
16 a
17 1
18 10
19 11
20 12
21 2
22 3
23 4
24 5
25 6
26 7
27 8
28 9
29 # Test of ADD/COALESCE PARTITIONS
30 # expecting duplicate partition name
31 ALTER TABLE TableA ADD PARTITION
32 (PARTITION partA,
33 PARTITION Parta,
34 PARTITION PartA);
35 ERROR HY000: Duplicate partition name parta
36 ALTER TABLE TableA ADD PARTITION
37 (PARTITION partE,
38 PARTITION Partf,
39 PARTITION PartG);
40 SELECT * FROM TableA;
41 a
42 1
43 10
44 11
45 12
46 2
47 3
48 4
49 5
50 6
51 7
52 8
53 9
54 SHOW CREATE TABLE TableA;
55 Table Create Table
56 TableA CREATE TABLE `tablea` (
57 `a` int(11) DEFAULT NULL
58 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
59 /*!50100 PARTITION BY KEY (a)
60 (PARTITION parta ENGINE = MEMORY,
61 PARTITION partB ENGINE = MEMORY,
62 PARTITION Partc ENGINE = MEMORY,
63 PARTITION PartD ENGINE = MEMORY,
64 PARTITION partE ENGINE = MEMORY,
65 PARTITION Partf ENGINE = MEMORY,
66 PARTITION PartG ENGINE = MEMORY) */
67 ALTER TABLE TableA COALESCE PARTITION 4;
68 SELECT * FROM TableA;
69 a
70 1
71 10
72 11
73 12
74 2
75 3
76 4
77 5
78 6
79 7
80 8
81 9
82 SHOW CREATE TABLE TableA;
83 Table Create Table
84 TableA CREATE TABLE `tablea` (
85 `a` int(11) DEFAULT NULL
86 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
87 /*!50100 PARTITION BY KEY (a)
88 (PARTITION parta ENGINE = MEMORY,
89 PARTITION partB ENGINE = MEMORY,
90 PARTITION Partc ENGINE = MEMORY) */
91 # Test of REORGANIZE PARTITIONS
92 # Should not work on HASH/KEY
93 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
94 (PARTITION PARTA ,
95 PARTITION partc );
96 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
97 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
98 (PARTITION partB ,
99 PARTITION parta );
100 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
101 ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
102 (PARTITION partB COMMENT="Previusly named parta",
103 PARTITION parta COMMENT="Previusly named partB");
104 SELECT * FROM TableA;
105 a
106 1
107 10
108 11
109 12
110 2
111 3
112 4
113 5
114 6
115 7
116 8
117 9
118 SHOW CREATE TABLE TableA;
119 Table Create Table
120 TableA CREATE TABLE `tablea` (
121 `a` int(11) DEFAULT NULL
122 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
123 /*!50100 PARTITION BY KEY (a)
124 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY,
125 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY,
126 PARTITION Partc ENGINE = MEMORY) */
127 # Test of RENAME TABLE
128 RENAME TABLE TableA to TableB;
129 SELECT * FROM TableB;
130 a
131 1
132 10
133 11
134 12
135 2
136 3
137 4
138 5
139 6
140 7
141 8
142 9
143 RENAME TABLE TableB to TableA;
144 SELECT * FROM TableA;
145 a
146 1
147 10
148 11
149 12
150 2
151 3
152 4
153 5
154 6
155 7
156 8
157 9
158 # Checking name comparision Upper vs Lower case
159 # Error if lower_case_table_names != 0
160 # lower_case_table_names: 1
161 CREATE TABLE tablea (a INT)
162 ENGINE = 'Memory'
163 PARTITION BY KEY (a)
164 (PARTITION parta ,
165 PARTITION partB ,
166 PARTITION Partc ,
167 PARTITION PartD );
168 ERROR 42S01: Table 'tablea' already exists
169 SHOW TABLES;
170 Tables_in_mysql_test_db
171 tablea
172 RENAME TABLE TableA to tablea;
173 ERROR 42S01: Table 'tablea' already exists
174 RENAME TABLE tablea to TableA;
175 ERROR 42S01: Table 'tablea' already exists
176 SELECT * FROM tablea;
177 a
178 1
179 10
180 11
181 12
182 2
183 3
184 4
185 5
186 6
187 7
188 8
189 9
190 SHOW CREATE TABLE tablea;
191 Table Create Table
192 tablea CREATE TABLE `tablea` (
193 `a` int(11) DEFAULT NULL
194 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
195 /*!50100 PARTITION BY KEY (a)
196 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY,
197 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY,
198 PARTITION Partc ENGINE = MEMORY) */
199 # Test of REMOVE PARTITIONING
200 ALTER TABLE TableA REMOVE PARTITIONING;
201 SELECT * FROM TableA;
202 a
203 1
204 10
205 11
206 12
207 2
208 3
209 4
210 5
211 6
212 7
213 8
214 9
215 SHOW CREATE TABLE TableA;
216 Table Create Table
217 TableA CREATE TABLE `tablea` (
218 `a` int(11) DEFAULT NULL
219 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
220 # Cleaning up after KEY PARTITIONING test
221 DROP TABLE TableA;
222 # 2.0 HASH partitioning mgm
223 # expecting duplicate partition name
224 CREATE TABLE TableA (a INT)
225 ENGINE = 'Memory'
226 PARTITION BY HASH (a)
227 (PARTITION parta ,
228 PARTITION partA ,
229 PARTITION Parta ,
230 PARTITION PartA );
231 ERROR HY000: Duplicate partition name parta
232 # Creating Hash partitioned table
233 CREATE TABLE TableA (a INT)
234 ENGINE = 'Memory'
235 PARTITION BY HASH (a)
236 (PARTITION parta ,
237 PARTITION partB ,
238 PARTITION Partc ,
239 PARTITION PartD );
240 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
241 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
242 SELECT * FROM TableA;
243 a
244 1
245 10
246 11
247 12
248 2
249 3
250 4
251 5
252 6
253 7
254 8
255 9
256 # Test of ADD/COALESCE PARTITIONS
257 # expecting duplicate partition name
258 ALTER TABLE TableA ADD PARTITION
259 (PARTITION partA,
260 PARTITION Parta,
261 PARTITION PartA);
262 ERROR HY000: Duplicate partition name parta
263 ALTER TABLE TableA ADD PARTITION
264 (PARTITION partE,
265 PARTITION Partf,
266 PARTITION PartG);
267 SELECT * FROM TableA;
268 a
269 1
270 10
271 11
272 12
273 2
274 3
275 4
276 5
277 6
278 7
279 8
280 9
281 SHOW CREATE TABLE TableA;
282 Table Create Table
283 TableA CREATE TABLE `tablea` (
284 `a` int(11) DEFAULT NULL
285 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
286 /*!50100 PARTITION BY HASH (a)
287 (PARTITION parta ENGINE = MEMORY,
288 PARTITION partB ENGINE = MEMORY,
289 PARTITION Partc ENGINE = MEMORY,
290 PARTITION PartD ENGINE = MEMORY,
291 PARTITION partE ENGINE = MEMORY,
292 PARTITION Partf ENGINE = MEMORY,
293 PARTITION PartG ENGINE = MEMORY) */
294 ALTER TABLE TableA COALESCE PARTITION 4;
295 SELECT * FROM TableA;
296 a
297 1
298 10
299 11
300 12
301 2
302 3
303 4
304 5
305 6
306 7
307 8
308 9
309 SHOW CREATE TABLE TableA;
310 Table Create Table
311 TableA CREATE TABLE `tablea` (
312 `a` int(11) DEFAULT NULL
313 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
314 /*!50100 PARTITION BY HASH (a)
315 (PARTITION parta ENGINE = MEMORY,
316 PARTITION partB ENGINE = MEMORY,
317 PARTITION Partc ENGINE = MEMORY) */
318 # Test of REORGANIZE PARTITIONS
319 # Should not work on HASH/KEY
320 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
321 (PARTITION PARTA ,
322 PARTITION partc );
323 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
324 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
325 (PARTITION partB ,
326 PARTITION parta );
327 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
328 ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
329 (PARTITION partB COMMENT="Previusly named parta",
330 PARTITION parta COMMENT="Previusly named partB");
331 SELECT * FROM TableA;
332 a
333 1
334 10
335 11
336 12
337 2
338 3
339 4
340 5
341 6
342 7
343 8
344 9
345 SHOW CREATE TABLE TableA;
346 Table Create Table
347 TableA CREATE TABLE `tablea` (
348 `a` int(11) DEFAULT NULL
349 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
350 /*!50100 PARTITION BY HASH (a)
351 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY,
352 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY,
353 PARTITION Partc ENGINE = MEMORY) */
354 # Test of RENAME TABLE
355 RENAME TABLE TableA to TableB;
356 SELECT * FROM TableB;
357 a
358 1
359 10
360 11
361 12
362 2
363 3
364 4
365 5
366 6
367 7
368 8
369 9
370 RENAME TABLE TableB to TableA;
371 SELECT * FROM TableA;
372 a
373 1
374 10
375 11
376 12
377 2
378 3
379 4
380 5
381 6
382 7
383 8
384 9
385 # Checking name comparision Upper vs Lower case
386 # Error if lower_case_table_names != 0
387 # lower_case_table_names: 1
388 CREATE TABLE tablea (a INT)
389 ENGINE = 'Memory'
390 PARTITION BY HASH (a)
391 (PARTITION parta ,
392 PARTITION partB ,
393 PARTITION Partc ,
394 PARTITION PartD );
395 ERROR 42S01: Table 'tablea' already exists
396 SHOW TABLES;
397 Tables_in_mysql_test_db
398 tablea
399 RENAME TABLE TableA to tablea;
400 ERROR 42S01: Table 'tablea' already exists
401 RENAME TABLE tablea to TableA;
402 ERROR 42S01: Table 'tablea' already exists
403 SELECT * FROM tablea;
404 a
405 1
406 10
407 11
408 12
409 2
410 3
411 4
412 5
413 6
414 7
415 8
416 9
417 SHOW CREATE TABLE tablea;
418 Table Create Table
419 tablea CREATE TABLE `tablea` (
420 `a` int(11) DEFAULT NULL
421 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
422 /*!50100 PARTITION BY HASH (a)
423 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY,
424 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY,
425 PARTITION Partc ENGINE = MEMORY) */
426 # Test of REMOVE PARTITIONING
427 ALTER TABLE TableA REMOVE PARTITIONING;
428 SELECT * FROM TableA;
429 a
430 1
431 10
432 11
433 12
434 2
435 3
436 4
437 5
438 6
439 7
440 8
441 9
442 SHOW CREATE TABLE TableA;
443 Table Create Table
444 TableA CREATE TABLE `tablea` (
445 `a` int(11) DEFAULT NULL
446 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
447 # Cleaning up after HASH PARTITIONING test
448 DROP TABLE TableA;
449 # 3.0 RANGE partitioning mgm
450 # Creating RANGE partitioned table
451 CREATE TABLE TableA (a INT)
452 ENGINE = 'Memory'
453 PARTITION BY RANGE (a)
454 (PARTITION parta VALUES LESS THAN (4) ,
455 PARTITION partB VALUES LESS THAN (7) ,
456 PARTITION Partc VALUES LESS THAN (10) ,
457 PARTITION PartD VALUES LESS THAN (13) );
458 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
459 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
460 SELECT * FROM TableA;
461 a
462 1
463 10
464 11
465 12
466 2
467 3
468 4
469 5
470 6
471 7
472 8
473 9
474 # Test of ADD/DROP PARTITIONS
475 # expecting duplicate partition name
476 ALTER TABLE TableA ADD PARTITION
477 (PARTITION partA VALUES LESS THAN (MAXVALUE));
478 ERROR HY000: Duplicate partition name parta
479 ALTER TABLE TableA ADD PARTITION
480 (PARTITION partE VALUES LESS THAN (16),
481 PARTITION Partf VALUES LESS THAN (19),
482 PARTITION PartG VALUES LESS THAN (22));
483 SELECT * FROM TableA;
484 a
485 1
486 10
487 11
488 12
489 2
490 3
491 4
492 5
493 6
494 7
495 8
496 9
497 SHOW CREATE TABLE TableA;
498 Table Create Table
499 TableA CREATE TABLE `tablea` (
500 `a` int(11) DEFAULT NULL
501 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
502 /*!50100 PARTITION BY RANGE (a)
503 (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY,
504 PARTITION partB VALUES LESS THAN (7) ENGINE = MEMORY,
505 PARTITION Partc VALUES LESS THAN (10) ENGINE = MEMORY,
506 PARTITION PartD VALUES LESS THAN (13) ENGINE = MEMORY,
507 PARTITION partE VALUES LESS THAN (16) ENGINE = MEMORY,
508 PARTITION Partf VALUES LESS THAN (19) ENGINE = MEMORY,
509 PARTITION PartG VALUES LESS THAN (22) ENGINE = MEMORY) */
510 ALTER TABLE TableA DROP PARTITION partE, PartG;
511 ALTER TABLE TableA DROP PARTITION Partf;
512 ALTER TABLE TableA ADD PARTITION
513 (PARTITION PartE VALUES LESS THAN (MAXVALUE));
514 SELECT * FROM TableA;
515 a
516 1
517 10
518 11
519 12
520 2
521 3
522 4
523 5
524 6
525 7
526 8
527 9
528 SHOW CREATE TABLE TableA;
529 Table Create Table
530 TableA CREATE TABLE `tablea` (
531 `a` int(11) DEFAULT NULL
532 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
533 /*!50100 PARTITION BY RANGE (a)
534 (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY,
535 PARTITION partB VALUES LESS THAN (7) ENGINE = MEMORY,
536 PARTITION Partc VALUES LESS THAN (10) ENGINE = MEMORY,
537 PARTITION PartD VALUES LESS THAN (13) ENGINE = MEMORY,
538 PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */
539 # Test of REORGANIZE PARTITIONS
540 # Error since it must reorganize a consecutive range
541 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
542 (PARTITION partB VALUES LESS THAN (3) ,
543 PARTITION parta VALUES LESS THAN (11) );
544 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
545 ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
546 (PARTITION partD VALUES LESS THAN (8)
547 COMMENT="Previously partB and partly Partc",
548 PARTITION partB VALUES LESS THAN (11)
549 COMMENT="Previously partly Partc and partly PartD",
550 PARTITION partC VALUES LESS THAN (MAXVALUE)
551 COMMENT="Previously partly PartD");
552 SELECT * FROM TableA;
553 a
554 1
555 10
556 11
557 12
558 2
559 3
560 4
561 5
562 6
563 7
564 8
565 9
566 SHOW CREATE TABLE TableA;
567 Table Create Table
568 TableA CREATE TABLE `tablea` (
569 `a` int(11) DEFAULT NULL
570 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
571 /*!50100 PARTITION BY RANGE (a)
572 (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY,
573 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MEMORY,
574 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MEMORY,
575 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MEMORY) */
576 # Test of RENAME TABLE
577 RENAME TABLE TableA to TableB;
578 SELECT * FROM TableB;
579 a
580 1
581 10
582 11
583 12
584 2
585 3
586 4
587 5
588 6
589 7
590 8
591 9
592 RENAME TABLE TableB to TableA;
593 SELECT * FROM TableA;
594 a
595 1
596 10
597 11
598 12
599 2
600 3
601 4
602 5
603 6
604 7
605 8
606 9
607 # Checking name comparision Upper vs Lower case
608 # Error if lower_case_table_names != 0
609 # lower_case_table_names: 1
610 CREATE TABLE tablea (a INT)
611 ENGINE = 'Memory'
612 PARTITION BY RANGE (a)
613 (PARTITION parta VALUES LESS THAN (4) ,
614 PARTITION partB VALUES LESS THAN (7) ,
615 PARTITION Partc VALUES LESS THAN (10) ,
616 PARTITION PartD VALUES LESS THAN (13) );
617 ERROR 42S01: Table 'tablea' already exists
618 SHOW TABLES;
619 Tables_in_mysql_test_db
620 tablea
621 RENAME TABLE TableA to tablea;
622 ERROR 42S01: Table 'tablea' already exists
623 RENAME TABLE tablea to TableA;
624 ERROR 42S01: Table 'tablea' already exists
625 SELECT * FROM tablea;
626 a
627 1
628 10
629 11
630 12
631 2
632 3
633 4
634 5
635 6
636 7
637 8
638 9
639 SHOW CREATE TABLE tablea;
640 Table Create Table
641 tablea CREATE TABLE `tablea` (
642 `a` int(11) DEFAULT NULL
643 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
644 /*!50100 PARTITION BY RANGE (a)
645 (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY,
646 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MEMORY,
647 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MEMORY,
648 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MEMORY) */
649 # Test of REMOVE PARTITIONING
650 ALTER TABLE TableA REMOVE PARTITIONING;
651 SELECT * FROM TableA;
652 a
653 1
654 10
655 11
656 12
657 2
658 3
659 4
660 5
661 6
662 7
663 8
664 9
665 SHOW CREATE TABLE TableA;
666 Table Create Table
667 TableA CREATE TABLE `tablea` (
668 `a` int(11) DEFAULT NULL
669 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
670 # Cleaning up after RANGE PARTITIONING test
671 DROP TABLE TableA;
672 # 4.0 LIST partitioning mgm
673 # Creating LIST partitioned table
674 CREATE TABLE TableA (a INT)
675 ENGINE = 'Memory'
676 PARTITION BY LIST (a)
677 (PARTITION parta VALUES IN (1,8,9) ,
678 PARTITION partB VALUES IN (2,10,11) ,
679 PARTITION Partc VALUES IN (3,4,7) ,
680 PARTITION PartD VALUES IN (5,6,12) );
681 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
682 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
683 SELECT * FROM TableA;
684 a
685 1
686 10
687 11
688 12
689 2
690 3
691 4
692 5
693 6
694 7
695 8
696 9
697 # Test of ADD/DROP PARTITIONS
698 # expecting duplicate partition name
699 ALTER TABLE TableA ADD PARTITION
700 (PARTITION partA VALUES IN (0));
701 ERROR HY000: Duplicate partition name parta
702 ALTER TABLE TableA ADD PARTITION
703 (PARTITION partE VALUES IN (16),
704 PARTITION Partf VALUES IN (19),
705 PARTITION PartG VALUES IN (22));
706 SELECT * FROM TableA;
707 a
708 1
709 10
710 11
711 12
712 2
713 3
714 4
715 5
716 6
717 7
718 8
719 9
720 SHOW CREATE TABLE TableA;
721 Table Create Table
722 TableA CREATE TABLE `tablea` (
723 `a` int(11) DEFAULT NULL
724 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
725 /*!50100 PARTITION BY LIST (a)
726 (PARTITION parta VALUES IN (1,8,9) ENGINE = MEMORY,
727 PARTITION partB VALUES IN (2,10,11) ENGINE = MEMORY,
728 PARTITION Partc VALUES IN (3,4,7) ENGINE = MEMORY,
729 PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY,
730 PARTITION partE VALUES IN (16) ENGINE = MEMORY,
731 PARTITION Partf VALUES IN (19) ENGINE = MEMORY,
732 PARTITION PartG VALUES IN (22) ENGINE = MEMORY) */
733 ALTER TABLE TableA DROP PARTITION partE, PartG;
734 ALTER TABLE TableA DROP PARTITION Partf;
735 ALTER TABLE TableA ADD PARTITION
736 (PARTITION PartE VALUES IN (13));
737 SELECT * FROM TableA;
738 a
739 1
740 10
741 11
742 12
743 2
744 3
745 4
746 5
747 6
748 7
749 8
750 9
751 SHOW CREATE TABLE TableA;
752 Table Create Table
753 TableA CREATE TABLE `tablea` (
754 `a` int(11) DEFAULT NULL
755 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
756 /*!50100 PARTITION BY LIST (a)
757 (PARTITION parta VALUES IN (1,8,9) ENGINE = MEMORY,
758 PARTITION partB VALUES IN (2,10,11) ENGINE = MEMORY,
759 PARTITION Partc VALUES IN (3,4,7) ENGINE = MEMORY,
760 PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY,
761 PARTITION PartE VALUES IN (13) ENGINE = MEMORY) */
762 # Test of REORGANIZE PARTITIONS
763 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
764 (PARTITION Partc VALUES IN (1,7)
765 COMMENT = "Mix 1 of old parta and Partc",
766 PARTITION partF VALUES IN (3,9)
767 COMMENT = "Mix 2 of old parta and Partc",
768 PARTITION parta VALUES IN (4,8)
769 COMMENT = "Mix 3 of old parta and Partc");
770 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
771 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
772 (PARTITION Partc VALUES IN (1,7)
773 COMMENT = "Mix 1 of old parta and Partc",
774 PARTITION parta VALUES IN (3,9)
775 COMMENT = "Mix 2 of old parta and Partc",
776 PARTITION partB VALUES IN (4,8)
777 COMMENT = "Mix 3 of old parta and Partc");
778 SELECT * FROM TableA;
779 a
780 1
781 12
782 3
783 4
784 5
785 6
786 7
787 8
788 9
789 SHOW CREATE TABLE TableA;
790 Table Create Table
791 TableA CREATE TABLE `tablea` (
792 `a` int(11) DEFAULT NULL
793 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
794 /*!50100 PARTITION BY LIST (a)
795 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = MEMORY,
796 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MEMORY,
797 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MEMORY,
798 PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY,
799 PARTITION PartE VALUES IN (13) ENGINE = MEMORY) */
800 # Test of RENAME TABLE
801 RENAME TABLE TableA to TableB;
802 SELECT * FROM TableB;
803 a
804 1
805 12
806 3
807 4
808 5
809 6
810 7
811 8
812 9
813 RENAME TABLE TableB to TableA;
814 SELECT * FROM TableA;
815 a
816 1
817 12
818 3
819 4
820 5
821 6
822 7
823 8
824 9
825 # Checking name comparision Upper vs Lower case
826 # Error if lower_case_table_names != 0
827 # lower_case_table_names: 1
828 CREATE TABLE tablea (a INT)
829 ENGINE = 'Memory'
830 PARTITION BY LIST (a)
831 (PARTITION parta VALUES IN (1,8,9) ,
832 PARTITION partB VALUES IN (2,10,11) ,
833 PARTITION Partc VALUES IN (3,4,7) ,
834 PARTITION PartD VALUES IN (5,6,12) );
835 ERROR 42S01: Table 'tablea' already exists
836 SHOW TABLES;
837 Tables_in_mysql_test_db
838 tablea
839 RENAME TABLE TableA to tablea;
840 ERROR 42S01: Table 'tablea' already exists
841 RENAME TABLE tablea to TableA;
842 ERROR 42S01: Table 'tablea' already exists
843 SELECT * FROM tablea;
844 a
845 1
846 12
847 3
848 4
849 5
850 6
851 7
852 8
853 9
854 SHOW CREATE TABLE tablea;
855 Table Create Table
856 tablea CREATE TABLE `tablea` (
857 `a` int(11) DEFAULT NULL
858 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
859 /*!50100 PARTITION BY LIST (a)
860 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = MEMORY,
861 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MEMORY,
862 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MEMORY,
863 PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY,
864 PARTITION PartE VALUES IN (13) ENGINE = MEMORY) */
865 # Test of REMOVE PARTITIONING
866 ALTER TABLE TableA REMOVE PARTITIONING;
867 SELECT * FROM TableA;
868 a
869 1
870 12
871 3
872 4
873 5
874 6
875 7
876 8
877 9
878 SHOW CREATE TABLE TableA;
879 Table Create Table
880 TableA CREATE TABLE `tablea` (
881 `a` int(11) DEFAULT NULL
882 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
883 # Cleaning up after LIST PARTITIONING test
884 DROP TABLE TableA;
885 # Cleaning up before exit
886 USE test;
887 DROP DATABASE MySQL_Test_DB;