Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-1-innodb.result

raw
1 # Additional tests for WL#5217 by QA, testplan 1.1
2 CREATE TABLE t1
3 (a INT NOT NULL,
4 b varchar (64),
5 INDEX ind_t1 (b,a),
6 PRIMARY KEY (a))
7 ENGINE = InnoDB
8 PARTITION BY RANGE (a)
9 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
10 (PARTITION pNeg VALUES LESS THAN (0)
11 (SUBPARTITION subp0 ,
12 SUBPARTITION subp1 ,
13 SUBPARTITION subp2 ),
14 PARTITION `p0-29` VALUES LESS THAN (30)
15 (SUBPARTITION subp3 ,
16 SUBPARTITION subp4 ,
17 SUBPARTITION subp5 ),
18 PARTITION `p30-299` VALUES LESS THAN (300)
19 (SUBPARTITION subp6 ,
20 SUBPARTITION subp7 ,
21 SUBPARTITION subp8 ),
22 PARTITION `p300-2999` VALUES LESS THAN (3000)
23 (SUBPARTITION subp9 ,
24 SUBPARTITION subp10 ,
25 SUBPARTITION subp11 ),
26 PARTITION `p3000-299999` VALUES LESS THAN (300000)
27 (SUBPARTITION subp12 ,
28 SUBPARTITION subp13 ,
29 SUBPARTITION subp14 ));
30 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
31 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
32 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
33 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
34 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
35 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
36 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
37 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
38 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
39 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
40 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
41 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
42 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
43 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
44 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
45 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
46 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
47 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
48 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
49 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
50 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
51 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
52 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
53 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
54 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
55 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
56 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
57 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
58 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
59 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
60 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
61 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
62 CREATE USER test_user_1 IDENTIFIED BY 'testpw';
63 CREATE USER test_user_2 IDENTIFIED BY 'testpw';
64 GRANT ALL PRIVILEGES ON test.* TO test_user_1;
65 GRANT ALL PRIVILEGES ON test.* TO test_user_2;
66 connect session1, localhost, test_user_1,'testpw',test;
67 SELECT * FROM t1 PARTITION (`p0-29`);
68 a b
69 1 (p0-29-)subp3
70 2 (p0-29-)subp3
71 21 (p0-29-)subp5
72 22 (p0-29-)subp5
73 23 (p0-29-)subp5
74 24 (p0-29-)subp5
75 3 (p0-29-)subp3
76 4 (p0-29-)subp3
77 send
78 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
79 connect session2, localhost, test_user_2,'testpw',test;
80 SELECT * FROM t1 PARTITION (`p30-299`);
81 a b
82 231 (p30-299-)subp8
83 232 (p30-299-)subp8
84 233 (p30-299-)subp8
85 234 (p30-299-)subp8
86 31 (p30-299-)subp6
87 32 (p30-299-)subp6
88 33 (p30-299-)subp6
89 34 (p30-299-)subp6
90 UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
91 SELECT * FROM t1 PARTITION (`p30-299`);
92 a b
93 231 (p30-299-)subp8
94 232 (p30-299-)subp8
95 233 (p30-299-)subp8
96 234 (p30-299-)subp8
97 31 p30-299-upd
98 32 p30-299-upd
99 33 p30-299-upd
100 34 p30-299-upd
101 SELECT * FROM t1 PARTITION (`p300-2999`);
102 a b
103 301 (p300-2999-)subp8
104 302 (p300-2999-)subp8
105 303 (p300-2999-)subp8
106 304 (p300-2999-)subp8
107 UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
108 SELECT * FROM t1 PARTITION (`p300-2999`);
109 a b
110 301 p300-2999-upd
111 302 p300-2999-upd
112 303 p300-2999-upd
113 304 p300-2999-upd
114 SELECT * FROM t1 PARTITION (`p3000-299999`);
115 a b
116 299996 (p3000-299999-)subp14
117 299997 (p3000-299999-)subp14
118 299998 (p3000-299999-)subp14
119 299999 (p3000-299999-)subp14
120 3001 (p3000-299999-)subp12
121 3002 (p3000-299999-)subp12
122 3003 (p3000-299999-)subp12
123 3004 (p3000-299999-)subp12
124 UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
125 SELECT * FROM t1 PARTITION (`p3000-299999`);
126 a b
127 299996 (p3000-299999-)subp14
128 299997 (p3000-299999-)subp14
129 299998 (p3000-299999-)subp14
130 299999 (p3000-299999-)subp14
131 3001 p3000-299999-upd
132 3002 p3000-299999-upd
133 3003 p3000-299999-upd
134 3004 p3000-299999-upd
135 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
136 SELECT * FROM t1 PARTITION (`p0-29`);
137 a b
138 1 p0-29-upd-2
139 2 p0-29-upd-2
140 21 (p0-29-)subp5
141 22 (p0-29-)subp5
142 23 (p0-29-)subp5
143 24 (p0-29-)subp5
144 3 p0-29-upd-2
145 4 p0-29-upd-2
146 UPDATE t1 PARTITION (subp3) SET b='subp3-upd';
147 UPDATE t1 PARTITION (subp4) SET b='subp4-upd';
148 UPDATE t1 PARTITION (subp5) SET b='subp5-upd';
149 SELECT * FROM t1 PARTITION (subp3);
150 a b
151 21 subp3-upd
152 24 subp3-upd
153 3 subp3-upd
154 SELECT * FROM t1 PARTITION (subp4);
155 a b
156 1 subp4-upd
157 22 subp4-upd
158 4 subp4-upd
159 SELECT * FROM t1 PARTITION (subp5);
160 a b
161 2 subp5-upd
162 23 subp5-upd
163 SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a;
164 a b
165 3 subp3-upd
166 21 subp3-upd
167 24 subp3-upd
168 1 subp4-upd
169 4 subp4-upd
170 22 subp4-upd
171 2 subp5-upd
172 23 subp5-upd
173 connection session1;
174 reap
175 SELECT * FROM t1 PARTITION (`p0-29`);
176 a b
177 1 subp4-upd
178 2 subp5-upd
179 21 subp3-upd
180 22 subp4-upd
181 23 subp5-upd
182 24 subp3-upd
183 3 subp3-upd
184 4 subp4-upd
185 connection session2;
186 SELECT * FROM t1 PARTITION (`p0-29`);
187 a b
188 1 subp4-upd
189 2 subp5-upd
190 21 subp3-upd
191 22 subp4-upd
192 23 subp5-upd
193 24 subp3-upd
194 3 subp3-upd
195 4 subp4-upd
196 SELECT
197 (SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) =
198 (SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ;
199 MUST BE TRUE (1)
200 1
201 CREATE TABLE t2
202 (a INT NOT NULL,
203 b varchar (64),
204 INDEX ind_t2 (b,a),
205 PRIMARY KEY (a))
206 ENGINE = InnoDB
207 PARTITION BY RANGE (a)
208 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
209 (PARTITION pNeg VALUES LESS THAN (0)
210 (SUBPARTITION subp0 ,
211 SUBPARTITION subp1 ,
212 SUBPARTITION subp2 ),
213 PARTITION `p0-29` VALUES LESS THAN (30)
214 (SUBPARTITION subp3 ,
215 SUBPARTITION subp4 ,
216 SUBPARTITION subp5 ),
217 PARTITION `p30-299` VALUES LESS THAN (300)
218 (SUBPARTITION subp6 ,
219 SUBPARTITION subp7 ,
220 SUBPARTITION subp8 ),
221 PARTITION `p300-2999` VALUES LESS THAN (3000)
222 (SUBPARTITION subp9 ,
223 SUBPARTITION subp10 ,
224 SUBPARTITION subp11 ),
225 PARTITION `p3000-299999` VALUES LESS THAN (300000)
226 (SUBPARTITION subp12 ,
227 SUBPARTITION subp13 ,
228 SUBPARTITION subp14 ));
229 SELECT * FROM t2 PARTITION (`p30-299`);
230 a b
231 UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
232 DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39;
233 SELECT * FROM t2 PARTITION (`p300-2999`);
234 a b
235 UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
236 DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309;
237 SELECT * FROM t2 PARTITION (`p3000-299999`);
238 a b
239 UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
240 DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009;
241 SELECT * FROM t2 PARTITION (`p0-29`);
242 a b
243 UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
244 DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9;
245 UPDATE t2 PARTITION (subp3) SET b='subp3-upd';
246 DELETE FROM t2 PARTITION (subp3);
247 UPDATE t2 PARTITION (subp4) SET b='subp4-upd';
248 DELETE FROM t2 PARTITION (subp4);
249 UPDATE t2 PARTITION (subp5) SET b='subp5-upd';
250 DELETE FROM t2 PARTITION (subp5);
251 UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd';
252 DELETE FROM t2 PARTITION (subp4,subp5,subp3);
253 SELECT * FROM t2 PARTITION (subp3);
254 a b
255 SELECT * FROM t2 PARTITION (subp4);
256 a b
257 SELECT * FROM t2 PARTITION (subp5);
258 a b
259 disconnect session1;
260 disconnect session2;
261 connection default;
262 DROP TABLE t1, t2;
263 DROP USER test_user_1;
264 DROP USER test_user_2;