MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
part_exch_qa.inc
1 use test;
2 
3 --disable_result_log
4 --disable_query_log
5 --source suite/parts/inc/part_exch_tabs.inc
6 --enable_result_log
7 --enable_query_log
8 
9 --sorted_result
10 SELECT * FROM t_10;
11 --sorted_result
12 SELECT * FROM t_100;
13 --sorted_result
14 SELECT * FROM t_1000;
15 --sorted_result
16 SELECT * FROM tp;
17 --sorted_result
18 SELECT * FROM tsp;
19 --sorted_result
20 SELECT * FROM tsp_00;
21 --sorted_result
22 SELECT * FROM tsp_01;
23 --sorted_result
24 SELECT * FROM tsp_02;
25 --sorted_result
26 SELECT * FROM tsp_03;
27 --sorted_result
28 SELECT * FROM tsp_04;
29 
30 # 1) Valid exchange with partitions.
31 # exchange of values < 10 of tp to t and complete contents of t to p0 and back.
32 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
33 --sorted_result
34 SELECT * FROM t_10;
35 --sorted_result
36 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
37 # Back to the former contents.
38 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
39 --sorted_result
40 SELECT * FROM t_10;
41 --sorted_result
42 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
43 
44 # Exchange with empty table.
45 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
46 --sorted_result
47 SELECT * FROM t_empty;
48 --sorted_result
49 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
50 # Back to the former contents.
51 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
52 --sorted_result
53 SELECT * FROM t_empty;
54 --sorted_result
55 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
56 
57 # Exchange with null table.
58 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
59 --sorted_result
60 SELECT * FROM t_null;
61 --sorted_result
62 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
63 # Back to the former contents.
64 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
65 --sorted_result
66 SELECT * FROM t_null;
67 --sorted_result
68 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
69 
70 # exchange of values < 100 of tp to t and complete contents of t to p1 and back.
71 ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
72 --sorted_result
73 SELECT * FROM t_100;
74 --sorted_result
75 SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
76 # Back to the former contents.
77 ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
78 --sorted_result
79 SELECT * FROM t_100;
80 --sorted_result
81 SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
82 
83 # exchange of values < 1000 of tp to t and complete contents of t to p2 and back.
84 ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
85 --sorted_result
86 SELECT * FROM t_1000;
87 --sorted_result
88 SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
89 # Back to the former contents.
90 ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
91 --sorted_result
92 SELECT * FROM t_1000;
93 --sorted_result
94 SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
95 
96 # 2) Valid exchange of subpartitions.
97 # exchange of values < 10 of tsp to t and complete contents of t to p0 and back.
98 ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
99 ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
100 ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
101 ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
102 ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
103 --sorted_result
104 SELECT * FROM tsp_00;
105 --sorted_result
106 SELECT * FROM tsp_01;
107 --sorted_result
108 SELECT * FROM tsp_02;
109 --sorted_result
110 SELECT * FROM tsp_03;
111 --sorted_result
112 SELECT * FROM tsp_04;
113 --sorted_result
114 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
115 # Back to the former contents.
116 ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
117 ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
118 ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
119 ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
120 ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
121 --sorted_result
122 SELECT * FROM tsp_00;
123 --sorted_result
124 SELECT * FROM tsp_01;
125 --sorted_result
126 SELECT * FROM tsp_02;
127 --sorted_result
128 SELECT * FROM tsp_03;
129 --sorted_result
130 SELECT * FROM tsp_04;
131 --sorted_result
132 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
133 
134 # Exchange with null table.
135 ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
136 --sorted_result
137 SELECT * FROM t_null;
138 --sorted_result
139 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
140 # Back to the former contents.
141 ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
142 --sorted_result
143 SELECT * FROM t_null;
144 --sorted_result
145 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
146 
147 --source suite/parts/inc/part_exch_drop_tabs.inc
148