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