1
# Additional tests for WL#5217 by QA, testplan 1.1
2
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
3
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
4
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
5
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
6
INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
7
INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
8
INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
9
INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
10
INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
11
INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
12
INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
13
INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
14
INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
15
INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
16
INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
17
INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
18
INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
19
INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
20
INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
21
INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
22
INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
23
INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
24
INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
25
INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
26
INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
27
INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
28
INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
29
INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
30
INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
31
INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
32
INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
33
INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
34
GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
35
GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw';
36
connect session1, localhost, test_user_1,'testpw',test;
37
SELECT * FROM t1 PARTITION (`p0-29`);
48
UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
49
connect session2, localhost, test_user_2,'testpw',test;
50
SELECT * FROM t1 PARTITION (`p30-299`);
60
UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
61
SELECT * FROM t1 PARTITION (`p30-299`);
71
SELECT * FROM t1 PARTITION (`p300-2999`);
77
UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
78
SELECT * FROM t1 PARTITION (`p300-2999`);
84
SELECT * FROM t1 PARTITION (`p3000-299999`);
86
299996 (p3000-299999-)subp14
87
299997 (p3000-299999-)subp14
88
299998 (p3000-299999-)subp14
89
299999 (p3000-299999-)subp14
90
3001 (p3000-299999-)subp12
91
3002 (p3000-299999-)subp12
92
3003 (p3000-299999-)subp12
93
3004 (p3000-299999-)subp12
94
UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
95
SELECT * FROM t1 PARTITION (`p3000-299999`);
97
299996 (p3000-299999-)subp14
98
299997 (p3000-299999-)subp14
99
299998 (p3000-299999-)subp14
100
299999 (p3000-299999-)subp14
101
3001 p3000-299999-upd
102
3002 p3000-299999-upd
103
3003 p3000-299999-upd
104
3004 p3000-299999-upd
105
UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
106
SELECT * FROM t1 PARTITION (`p0-29`);
116
UPDATE t1 PARTITION (subp3) SET b='subp3-upd';
117
UPDATE t1 PARTITION (subp4) SET b='subp4-upd';
118
UPDATE t1 PARTITION (subp5) SET b='subp5-upd';
119
SELECT * FROM t1 PARTITION (subp3);
124
SELECT * FROM t1 PARTITION (subp4);
129
SELECT * FROM t1 PARTITION (subp5);
133
SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a;
145
SELECT * FROM t1 PARTITION (`p0-29`);
156
SELECT * FROM t1 PARTITION (`p0-29`);
167
(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) =
168
(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ;
171
SELECT * FROM t2 PARTITION (`p30-299`);
173
UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
174
DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39;
175
SELECT * FROM t2 PARTITION (`p300-2999`);
177
UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
178
DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309;
179
SELECT * FROM t2 PARTITION (`p3000-299999`);
181
UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
182
DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009;
183
SELECT * FROM t2 PARTITION (`p0-29`);
185
UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
186
DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9;
187
UPDATE t2 PARTITION (subp3) SET b='subp3-upd';
188
DELETE FROM t2 PARTITION (subp3);
189
UPDATE t2 PARTITION (subp4) SET b='subp4-upd';
190
DELETE FROM t2 PARTITION (subp4);
191
UPDATE t2 PARTITION (subp5) SET b='subp5-upd';
192
DELETE FROM t2 PARTITION (subp5);
193
UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd';
194
DELETE FROM t2 PARTITION (subp4,subp5,subp3);
195
SELECT * FROM t2 PARTITION (subp3);
197
SELECT * FROM t2 PARTITION (subp4);
199
SELECT * FROM t2 PARTITION (subp5);
205
DROP USER test_user_1;
206
DROP USER test_user_2;