MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
part_exch_qa_13.inc
1 # Author: Horst Hunger
2 # Created: 2010-07-13
3 
4 use test;
5 
6 --disable_result_log
7 --disable_query_log
8 --disable_warnings
9 DROP TABLE IF EXISTS t_10;
10 DROP TABLE IF EXISTS t_100;
11 DROP TABLE IF EXISTS t_1000;
12 DROP TABLE IF EXISTS tp;
13 DROP TABLE IF EXISTS tsp;
14 DROP TABLE IF EXISTS t_empty;
15 DROP TABLE IF EXISTS t_null;
16 --enable_warnings
17 
18 eval CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a))
19 CHECKSUM= 1,
20 ENGINE = $engine_table;
21 
22 eval CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a))
23 COMMENT= 'comment',
24 ENGINE = $engine_table;
25 
26 eval CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a))
27 MIN_ROWS= 1,
28 MAX_ROWS= 2000,
29 ENGINE = $engine_table;
30 
31 eval CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a))
32 ENGINE = $engine_table;
33 
34 eval CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a))
35 ENGINE = $engine_table;
36 
37 eval CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a))
38 CHECKSUM= 1,
39 ENGINE = $engine_part
40 PARTITION BY RANGE (a)
41 (PARTITION p0 VALUES LESS THAN (10),
42  PARTITION p1 VALUES LESS THAN (100),
43  PARTITION p2 VALUES LESS THAN (1000));
44 
45 eval CREATE TABLE tp1 (a INT, b VARCHAR(55), PRIMARY KEY (a))
46 ENGINE = $engine_part
47 PARTITION BY RANGE (a)
48 (PARTITION p0 VALUES LESS THAN (10) MAX_ROWS=2000 MIN_ROWS=1,
49  PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=2000 MIN_ROWS=1,
50  PARTITION p2 VALUES LESS THAN (1000) MAX_ROWS=2000 MIN_ROWS=1
51 );
52 
53 eval CREATE TABLE tsp (a INT,
54  b VARCHAR(55),
55  PRIMARY KEY (a))
56 COMMENT= 'comment',
57 ENGINE = $engine_subpart
58 PARTITION BY RANGE (a)
59 SUBPARTITION BY HASH(a)
60 (PARTITION p0 VALUES LESS THAN (10)
61  (SUBPARTITION sp00 MAX_ROWS=2000 MIN_ROWS=1,
62  SUBPARTITION sp01 MAX_ROWS=2000 MIN_ROWS=1,
63  SUBPARTITION sp02 MAX_ROWS=2000 MIN_ROWS=1,
64  SUBPARTITION sp03 MAX_ROWS=2000 MIN_ROWS=1,
65  SUBPARTITION sp04 MAX_ROWS=2000 MIN_ROWS=1),
66  PARTITION p1 VALUES LESS THAN (100)
67  (SUBPARTITION sp10,
68  SUBPARTITION sp11,
69  SUBPARTITION sp12,
70  SUBPARTITION sp13,
71  SUBPARTITION sp14),
72  PARTITION p2 VALUES LESS THAN (1000)
73  (SUBPARTITION sp20,
74  SUBPARTITION sp21,
75  SUBPARTITION sp22,
76  SUBPARTITION sp23,
77  SUBPARTITION sp24));
78 
79 # Values t_10 (not partitioned)
80 INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
81 
82 # Values t_100 (not partitioned)
83 INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
84 INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
85 
86 # Values t_1000 (not partitioned)
87 INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
88 INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
89 INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
90 INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
91 
92 # Values t_null (not partitioned)
93 INSERT INTO t_null VALUES (1, "NULL");
94 
95 # Values tp (partitions)
96 INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
97 INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
98 INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
99 INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
100 INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
101 INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
102 
103 # Values tp1 (partitions)
104 INSERT INTO tp1 VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
105 INSERT INTO tp1 VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
106 INSERT INTO tp1 VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
107 INSERT INTO tp1 VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
108 INSERT INTO tp1 VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
109 INSERT INTO tp1 VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
110 
111 # Values tps (subpartitions)
112 INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
113 INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
114 INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
115 INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
116 INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
117 INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
118 
119 eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
120  AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
121 eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
122  AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
123 eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table,
124  MAX_ROWS=2000, MIN_ROWS=1
125  AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
126 eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
127  AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
128 eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
129  AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
130 
131 SHOW CREATE TABLE t_100;
132 SHOW CREATE TABLE t_1000;
133 SHOW CREATE TABLE tp;
134 SHOW CREATE TABLE tsp;
135 
136 --enable_result_log
137 --enable_query_log
138 
139 --sorted_result
140 SELECT * FROM t_10;
141 --sorted_result
142 SELECT * FROM t_100;
143 --sorted_result
144 SELECT * FROM t_1000;
145 --sorted_result
146 SELECT * FROM tp;
147 --sorted_result
148 SELECT * FROM tsp;
149 --sorted_result
150 SELECT * FROM tsp_00;
151 --sorted_result
152 SELECT * FROM tsp_01;
153 --sorted_result
154 SELECT * FROM tsp_02;
155 --sorted_result
156 SELECT * FROM tsp_03;
157 --sorted_result
158 SELECT * FROM tsp_04;
159 
160 # 13) Exchanges with different table options.
161 # See bug#55944 to change the IGNORE
162 # IGNORE was removed in bug#57708.
163 INSERT INTO t_10 VALUES (10, "TEN");
164 --error ER_ROW_DOES_NOT_MATCH_PARTITION
165 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
166 --sorted_result
167 SELECT * FROM tp WHERE a < 11;
168 --sorted_result
169 SELECT * FROM t_10 WHERE a < 11;
170 #ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
171 #--sorted_result
172 #SELECT * FROM tp WHERE a < 11;
173 #--sorted_result
174 #SELECT * FROM t_10 WHERE a < 11;
175 INSERT INTO t_1000 VALUES (99, "Ninetynine");
176 --error ER_ROW_DOES_NOT_MATCH_PARTITION
177 ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000;
178 --sorted_result
179 SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
180 --sorted_result
181 SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
182 #ALTER TABLE tp1 EXCHANGE PARTITION p2 WITH TABLE t_1000 IGNORE;
183 #--sorted_result
184 #SELECT * FROM tp1 WHERE a < 1000 AND a > 98;
185 #--sorted_result
186 #SELECT * FROM t_1000 WHERE a < 1000 AND a > 98;
187 INSERT INTO tsp_03 VALUES (20, "Twenty");
188 --error ER_ROW_DOES_NOT_MATCH_PARTITION
189 ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
190 --sorted_result
191 SELECT * FROM tsp;
192 --sorted_result
193 SELECT * FROM tsp_03;
194 #ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03 IGNORE;
195 #--sorted_result
196 #SELECT * FROM tsp;
197 #--sorted_result
198 #SELECT * FROM tsp_03;
199 
200 DROP TABLE tp1;
201 --source suite/parts/inc/part_exch_drop_tabs.inc
202