Debian Codegraph @

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

raw
1 SET GLOBAL innodb_strict_mode=OFF;
2 SET innodb_strict_mode=OFF;
3 # Additional tests for WL#5217 by QA, testplan 1.1
4 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
5 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
6 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
7 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
8 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
9 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
10 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
11 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
12 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
13 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
14 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
15 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
16 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
17 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
18 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
19 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
20 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
21 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
22 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
23 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
24 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
25 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
26 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
27 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
28 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
29 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
30 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
31 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
32 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
33 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
34 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
35 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
36 CREATE USER test_user_1 IDENTIFIED BY 'testpw';
37 CREATE USER test_user_2 IDENTIFIED BY 'testpw';
38 GRANT ALL PRIVILEGES ON test.* TO test_user_1;
39 GRANT ALL PRIVILEGES ON test.* TO test_user_2;
40 # FILE is needed to use the DATA DIRECTORY or INDEX DIRECTORY option
41 GRANT FILE ON *.* TO test_user_2;
42 connect session1, localhost, test_user_1,'testpw',test;
43 SELECT * FROM t1 PARTITION (`p0-29`);
44 a b
45 1 (p0-29-)subp3
46 2 (p0-29-)subp3
47 21 (p0-29-)subp5
48 22 (p0-29-)subp5
49 23 (p0-29-)subp5
50 24 (p0-29-)subp5
51 3 (p0-29-)subp3
52 4 (p0-29-)subp3
53 send
54 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
55 connect session2, localhost, test_user_2,'testpw',test;
56 SELECT * FROM t1 PARTITION (`p30-299`);
57 a b
58 231 (p30-299-)subp8
59 232 (p30-299-)subp8
60 233 (p30-299-)subp8
61 234 (p30-299-)subp8
62 31 (p30-299-)subp6
63 32 (p30-299-)subp6
64 33 (p30-299-)subp6
65 34 (p30-299-)subp6
66 UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
67 SELECT * FROM t1 PARTITION (`p30-299`);
68 a b
69 231 (p30-299-)subp8
70 232 (p30-299-)subp8
71 233 (p30-299-)subp8
72 234 (p30-299-)subp8
73 31 p30-299-upd
74 32 p30-299-upd
75 33 p30-299-upd
76 34 p30-299-upd
77 SELECT * FROM t1 PARTITION (`p300-2999`);
78 a b
79 301 (p300-2999-)subp8
80 302 (p300-2999-)subp8
81 303 (p300-2999-)subp8
82 304 (p300-2999-)subp8
83 UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
84 SELECT * FROM t1 PARTITION (`p300-2999`);
85 a b
86 301 p300-2999-upd
87 302 p300-2999-upd
88 303 p300-2999-upd
89 304 p300-2999-upd
90 SELECT * FROM t1 PARTITION (`p3000-299999`);
91 a b
92 299996 (p3000-299999-)subp14
93 299997 (p3000-299999-)subp14
94 299998 (p3000-299999-)subp14
95 299999 (p3000-299999-)subp14
96 3001 (p3000-299999-)subp12
97 3002 (p3000-299999-)subp12
98 3003 (p3000-299999-)subp12
99 3004 (p3000-299999-)subp12
100 UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
101 SELECT * FROM t1 PARTITION (`p3000-299999`);
102 a b
103 299996 (p3000-299999-)subp14
104 299997 (p3000-299999-)subp14
105 299998 (p3000-299999-)subp14
106 299999 (p3000-299999-)subp14
107 3001 p3000-299999-upd
108 3002 p3000-299999-upd
109 3003 p3000-299999-upd
110 3004 p3000-299999-upd
111 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
112 SELECT * FROM t1 PARTITION (`p0-29`);
113 a b
114 1 p0-29-upd-2
115 2 p0-29-upd-2
116 21 (p0-29-)subp5
117 22 (p0-29-)subp5
118 23 (p0-29-)subp5
119 24 (p0-29-)subp5
120 3 p0-29-upd-2
121 4 p0-29-upd-2
122 UPDATE t1 PARTITION (subp3) SET b='subp3-upd';
123 UPDATE t1 PARTITION (subp4) SET b='subp4-upd';
124 UPDATE t1 PARTITION (subp5) SET b='subp5-upd';
125 SELECT * FROM t1 PARTITION (subp3);
126 a b
127 21 subp3-upd
128 24 subp3-upd
129 3 subp3-upd
130 SELECT * FROM t1 PARTITION (subp4);
131 a b
132 1 subp4-upd
133 22 subp4-upd
134 4 subp4-upd
135 SELECT * FROM t1 PARTITION (subp5);
136 a b
137 2 subp5-upd
138 23 subp5-upd
139 SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a;
140 a b
141 3 subp3-upd
142 21 subp3-upd
143 24 subp3-upd
144 1 subp4-upd
145 4 subp4-upd
146 22 subp4-upd
147 2 subp5-upd
148 23 subp5-upd
149 connection session1;
150 reap
151 SELECT * FROM t1 PARTITION (`p0-29`);
152 a b
153 1 subp4-upd
154 2 subp5-upd
155 21 subp3-upd
156 22 subp4-upd
157 23 subp5-upd
158 24 subp3-upd
159 3 subp3-upd
160 4 subp4-upd
161 connection session2;
162 SELECT * FROM t1 PARTITION (`p0-29`);
163 a b
164 1 subp4-upd
165 2 subp5-upd
166 21 subp3-upd
167 22 subp4-upd
168 23 subp5-upd
169 24 subp3-upd
170 3 subp3-upd
171 4 subp4-upd
172 SELECT
173 (SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) =
174 (SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ;
175 MUST BE TRUE (1)
176 1
177 SELECT * FROM t2 PARTITION (`p30-299`);
178 a b
179 UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39;
180 DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39;
181 SELECT * FROM t2 PARTITION (`p300-2999`);
182 a b
183 UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309;
184 DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309;
185 SELECT * FROM t2 PARTITION (`p3000-299999`);
186 a b
187 UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009;
188 DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009;
189 SELECT * FROM t2 PARTITION (`p0-29`);
190 a b
191 UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
192 DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9;
193 UPDATE t2 PARTITION (subp3) SET b='subp3-upd';
194 DELETE FROM t2 PARTITION (subp3);
195 UPDATE t2 PARTITION (subp4) SET b='subp4-upd';
196 DELETE FROM t2 PARTITION (subp4);
197 UPDATE t2 PARTITION (subp5) SET b='subp5-upd';
198 DELETE FROM t2 PARTITION (subp5);
199 UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd';
200 DELETE FROM t2 PARTITION (subp4,subp5,subp3);
201 SELECT * FROM t2 PARTITION (subp3);
202 a b
203 SELECT * FROM t2 PARTITION (subp4);
204 a b
205 SELECT * FROM t2 PARTITION (subp5);
206 a b
207 disconnect session1;
208 disconnect session2;
209 connection default;
210 DROP TABLE t1, t2;
211 DROP USER test_user_1;
212 DROP USER test_user_2;
213 SET GLOBAL innodb_strict_mode=default;