Debian Codegraph @

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

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