Debian Codegraph @

mysql-test/suite/ndb_opt/rbwr.test

raw
1 #
2 # Test for read before write removal(rbwr) optimization in MySQL Cluster
3 #
4 # Parameter based testcase using a table containing the different queries
5 # and their expected number of roundtrips(regarding rbwr). This allows the
6 # same queries to be run with different settings while making sure that
7 # the query result is always the same. Of course some settings causes the
8 # rbwr optimzation to not apply and the expected number of roundtrips etc.
9 # shouldn't be checked.
10 #
11 -- source include/have_ndb.inc
12
13 --disable_query_log
14 --disable_result_log
15
16 ### Setup parameter tables and utility procedures
17 create table rbwr_tests (
18 test_id int primary key auto_increment,
19 table_name varchar(255),
20 query varchar(255),
21 execute_count int,
22 affected_rows int
23 ) engine = MEMORY;
24
25 ### Setup test tables
26 # Same table as ndb_update_no_read
27 create table t1 (
28 a int not null primary key,
29 b int not null,
30 c int,
31 unique index_b (b) using hash
32 ) engine = ndb;
33 create temporary table data_t1 (
34 a int not null primary key,
35 b int not null,
36 c int
37 );
38 insert into data_t1 values
39 (1,10,1),(2,9,1),(3,8,1),(4,7,1),(5,6,1),
40 (6,5,2),(7,4,2),(8,3,2),(9,2,2),(10,1,2);
41
42 # Composite PK
43 create table t2 (
44 pk_a int not null,
45 pk_b int not null,
46 pk_c int not null,
47 PRIMARY KEY (pk_a,pk_b,pk_c),
48 d int
49 ) engine = ndb;
50 create temporary table data_t2 (
51 a int not null,
52 b int not null,
53 c int not null,
54 d int
55 );
56 insert into data_t2 values
57 (1,10,1,37),(2,9,1,38),(3,8,1,39),(4,7,1,40),(5,6,1,41),
58 (6,5,2,42),(7,4,2,43),(8,3,2,44),(9,2,2,45),(10,1,2,46);
59
60 #
61 create table t3 (
62 pk_a varchar(256) not null,
63 b int not null,
64 c int not null,
65 PRIMARY KEY (pk_a),
66 unique index_cb (c,b) using hash,
67 d int
68 ) engine = ndb;
69 create temporary table data_t3 (
70 a varchar(256) not null,
71 b int not null,
72 c int not null,
73 d int
74 );
75 insert into data_t3 values
76 ('11111',10,1,44),('22222',9,2,45),('33333',8,3,46),('44444',7,4,47),('55555',6,5,48),
77 ('66666',5,6,44),('77777',4,7,45),('88888',3,8,46),('99999',2,9,47),('00000',1,10,48);
78
79 # Composite PK, more rows, random number of rows
80 create table t4 (
81 pk_a varchar(256) not null,
82 pk_b int not null,
83 pk_c datetime not null,
84 PRIMARY KEY (pk_a, pk_b, pk_c),
85 d int,
86 e varchar(255),
87 f int,
88 unique index_f (f) using hash,
89 g bigint,
90 h bigint,
91 i bigint,
92 j bigint,
93 unique index_ghij (g,h,i,j) using hash
94 ) engine = ndb;
95 create temporary table data_t4 (
96 a varchar(256) not null,
97 b int not null,
98 c datetime not null,
99 d int,
100 e varchar(255),
101 f int,
102 g bigint,
103 h int,
104 i bigint,
105 j char(11)
106 );
107 let $i = `select round(rand() * 10)`;
108 while ($i)
109 {
110 eval insert into data_t4 values (
111 REPEAT('$i', 4),
112 $i,
113 DATE_ADD("2012-10-15 11:11:11", INTERVAL $i SECOND),
114 100000-$i,
115 REPEAT(CHAR(64+25*RAND()), 1+254*RAND()),
116 $i,
117 1000+$i,
118 10000+$i,
119 100000+$i,
120 1000000+$i
121 );
122
123 dec $i;
124 }
125
126 #Table with BLOB
127 create table t5 (
128 a int not null primary key,
129 b int not null,
130 c int,
131 d text,
132 unique index_b (b) using hash
133 ) engine = ndb;
134 create temporary table data_t5 (
135 a int not null primary key,
136 b int not null,
137 c int,
138 d text
139 );
140 insert into data_t5 values
141 (1,10,1,'aaa'),(2,9,1,'bbb'),(3,8,1,'ccc'),(4,7,1,''),(5,6,1,NULL),
142 (6,5,2,'foo'),(7,4,2,'xxx'),(8,3,2,'yyy'),(9,2,2,'zzz'),(10,1,3,'xyz');
143
144 #enable_result_log;
145 #select * from data_t4;
146 #exit;
147
148
149 ### Add test cases and their expected execute_count results
150
151 # Same testcases as ndb_update_no_read
152 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
153 ("t1", "update t1 set c = 97, b = 98 where a = 1", 2),
154 ("t1", "delete from t1 where a = 1", 1),
155 ("t1", "update t1 set c = 97 where b = 2", 1),
156 ("t1", "update t1 set c = 97, b = 98 where b = 2", 2),
157 ("t1", "delete from t1 where b = 9", 1),
158 ("t1", "update t1 set c = 97 where a = 10 or a >= 10", 3),
159 ("t1", "update t1 set c = 97 where a in (8,10)", 1),
160 ("t1", "update t1 set c = 97 where a in (7,8) or a >= 10", 4),
161 ("t1", "update t1 set a = 99, b = 98 where a = 3", 4),
162 ("t1", "update t1 set a = 99, b = 98 where b = 7", 4),
163 ("t1", "update t1 set c = 97, b = 98 where a = 5 and b = 6", 2),
164 ("t1", "delete from t1 where b = 8 and c = 2", 2);
165
166 # Using full primary key, one roundtrip
167 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
168 ("t1", "delete from t1 where a = 1", 1),
169 ("t1", "update t1 set c = 37 where a = 1", 1),
170 ("t2", "delete from t2 where pk_a = 1 and pk_b = 2 and pk_c = 3", 1),
171 ("t2", "update t2 set d = 37 where pk_a = 1 and pk_b = 2 and pk_c = 3", 1),
172 ("t3", "delete from t3 where pk_a = \"11111\"", 1),
173 ("t3", "update t3 set d = 37 where pk_a = \"11111\"", 1),
174 ("t4", "delete from t4 where pk_a = \"16161616\" and pk_b = 16
175 and pk_c = \"2012-10-15 11:11:27\"", 1),
176 ("t4", "update t4 set d = 37 where pk_a = \"16161616\" and pk_b = 16
177 and pk_c = \"2012-10-15 11:11:27\"", 1);
178
179 # Using full primary key with constant parameter in value list, one roundtrip
180 set @param37 = 37;
181 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
182 ("t1", "update t1 set c = @param37 where a = 1", 1),
183 ("t2", "update t2 set d = @param37 where pk_a = 1 and pk_b = 2 and
184 pk_c = 3", 1),
185 ("t3", "update t3 set d = @param37 where pk_a = \"11111\"", 1),
186 ("t4", "update t4 set d = @param37 where pk_a = \"16161616\" and pk_b = 16
187 and pk_c = \"2012-10-15 11:11:27\"", 1);
188
189 # Using full primary key with constant parameters in value list and where
190 # clause, one roundtrip
191 set @param1 = 1;
192 set @param2 = 2;
193 set @param3 = 3;
194 set @param11111 = "11111";
195 set @param16161616 = "16161616";
196 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
197 ("t1", "delete from t1 where a = @param1", 1),
198 ("t1", "update t1 set c = @param37 where a = @param1", 1),
199 ("t2", "delete from t2 where pk_a = @param1 and pk_b = @param2 and
200 pk_c = 3", 1),
201 ("t2", "update t2 set d = @param37 where pk_a = @param1 and pk_b = @param2 and
202 pk_c = 3", 1),
203 ("t3", "delete from t3 where pk_a = @param11111", 1),
204 ("t3", "update t3 set d = @param37 where pk_a = @param11111", 1),
205 ("t4", "delete from t4 where pk_a = @param16161616 and pk_b = 16
206 and pk_c = \"2012-10-15 11:11:27\"", 1),
207 ("t4", "update t4 set d = @param37 where pk_a = @param16161616 and pk_b = 16
208 and pk_c = \"2012-10-15 11:11:27\"", 1);
209
210 # Using full unique key, one roundtrip
211 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
212 ("t1", "delete from t1 where b = 1", 1),
213 ("t1", "update t1 set c = 37 where b = 1", 1),
214 # no unique key in t2
215 ("t3", "delete from t3 where c = 2 and b = 9", 1),
216 ("t3", "update t3 set d = 37 where c = 2 and b = 9", 1),
217 # The 4 cases below should supposedly also use only 1 roundtrip
218 ("t4", "delete from t4 where f = 37", 2),
219 ("t4", "update t4 set d = 37 where f = 37", 2),
220 ("t4", "delete from t4
221 where g = 1009 and h = 10009 and i = 100009 and j = \"1000009\"", 2),
222 ("t4", "update t4 set d = 37
223 where g = 1009 and h = 10009 and i = 100009 and j = \"1000009\"", 2);
224
225 # Update (part of) primary key (may require read+delete+insert+commit)
226 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
227 # All columns in PK updated
228 ("t1", "update t1 set a = 97 where a = 1", 4), #PK
229 ("t1", "update t1 set a = 98 where b = 1", 4), #Unique key
230
231 # Update PK as keyop
232 ("t2", "update t2 set pk_a = 37 where pk_a = 1 and pk_b = 2 and pk_c = 3", 2), #No match
233 ("t2", "update t2 set pk_a = 38 where pk_a = 9 and pk_b = 2 and pk_c = 2", 4),
234 ("t2", "update t2 set pk_b = 39 where pk_a = 9 and pk_b = 2 and pk_c = 2", 4),
235
236 # Update PK as index-rangeop
237 ("t2", "update t2 set pk_a = 47 where pk_a = 1 and pk_b = 2", 2), #No match
238 ("t2", "update t2 set pk_a = 48 where pk_a = 9 and pk_b = 2", 6),
239
240 # Update PK on table having BLOB
241 ("t5", "update t5 set a = 57 where a = 1", 4), #PK
242 ("t5", "update t5 set a = 58 where b = 1", 4), #Unique key
243
244 # Update PK on table having BLOB, non existing rows
245 ("t5", "update t5 set a = 57 where a = 99", 2), #PK
246 ("t5", "update t5 set a = 58 where b = 99", 2) #Unique key
247 ;
248
249 # Update or delete of table with BLOB (Column 'd')
250 set @b1 = 'b1';
251 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
252 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
253 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
254
255 INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES
256 ("t5", "update t5 set d = @b1 where a = 1", 3), #PK
257 ("t5", "update t5 set d = NULL where a = 1", 3), #PK
258
259 ("t5", "update t5 set d = @b1 where b = 1", 3), #UQ index
260 ("t5", "update t5 set d = NULL where b = 1", 3), #UQ index
261
262 ("t5", "update t5 set d = @b1 where c = 3", 3), #Scan 1 row
263 ("t5", "update t5 set d = NULL where c = 3", 3), #Scan, update 1 row
264 #("t5", "update t5 set d = @b1 where c > 1", 7), #Scan, update 5 rows
265 #("t5", "update t5 set d = @b1", 12), #Scan, update 10 rows
266
267 # Update BLOB in non existing rows
268 ("t5", "update t5 set d = @b1 where a = 99", 2), #PK
269 ("t5", "update t5 set d = NULL where a = 99", 2), #PK
270
271 ("t5", "update t5 set d = @b1 where b = 99", 2), #UQ index
272 ("t5", "update t5 set d = NULL where b = 99", 2), #UQ index
273
274 ("t5", "update t5 set d = @b1 where c = 99", 2), #Scan
275 ("t5", "update t5 set d = NULL where c = 99", 2), #Scan
276
277 # Update BLOB also req. to read the BLOB column
278 ("t5", "update t5 set d = concat(d,@b1) where a = 1", 3), #PK
279 ("t5", "update t5 set d = concat(d,@b1) where b = 1", 3), #UQ index
280 ("t5", "update t5 set d = concat(d,@b1) where c = 3", 3), #Scan 1 row
281 #("t5", "update t5 set d = concat(d,@b1) where c > 1", 7), #Scan 5 rows
282 #("t5", "update t5 set d = concat(d,@b1)", 11), #Scan 10 rows
283
284 # Delete rows with BLOB
285 ("t5", "delete from t5 where a = 1", 2), #PK
286 ("t5", "delete from t5 where b = 1", 2), #UQ index
287 ("t5", "delete from t5 where c = 3", 3), #Scan, single match
288 #("t5", "delete from t5 where c > 1", 4), #Scan, match 5 rows
289 #("t5", "delete from t5 where c > 0", 4), #Scan, match 10 rows
290 ("t5", "delete from t5 where c > 9", 2) #Scan, no match
291 #("t5", "delete from t5", 4)
292 ;
293
294
295 ### Run testcases while there are tests left in the param table
296 while (`select count(*) from rbwr_tests`)
297 {
298 # Find one testcase
299 select @test_id := test_id,
300 @test_table := table_name,
301 @test_query := query,
302 @test_execute_count := execute_count,
303 @test_affected_rows := affected_rows
304 from rbwr_tests order by test_id limit 1;
305 let $test_query = `select @test_query`;
306 let $test_table = `select @test_table`;
307 let $test_execute_count = `select @test_execute_count`;
308
309 --source rbwr.inc
310
311 # Delete testcase
312 delete from rbwr_tests where test_id = @test_id;
313 }
314
315 ## Table with triggers
316 CREATE TABLE main(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, val int)engine=ndb;
317 CREATE TABLE log(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, bug_text varchar(500) NOT NULL)engine=ndb;
318
319 # Check execute count to verify that read removal is not done for
320 # a delete on a table which has a delete trigger
321 CREATE TRIGGER log_deletes AFTER DELETE ON main FOR EACH ROW
322 INSERT INTO log (bug_text) VALUES ("one row deleted");
323
324 let $before= `select VARIABLE_VALUE from performance_schema.session_status
325 where variable_name like 'NDB_EXECUTE_COUNT'`;
326 DELETE FROM main WHERE id = 1;
327 let $after= `select VARIABLE_VALUE from performance_schema.session_status
328 where variable_name like 'NDB_EXECUTE_COUNT'`;
329 if (!`select $after-$before >= 4`)
330 {
331 enable_result_log;
332 echo before: $before;
333 echo after: $after;
334 die Read removal used even when table has delete triggers;
335 }
336
337 # Check execute count to verify that read removal is not done for
338 # an update on a table which has an update trigger
339 CREATE TRIGGER log_updates AFTER UPDATE ON main FOR EACH ROW
340 INSERT INTO log (bug_text) VALUES ("one row updated");
341
342 let $before= `select VARIABLE_VALUE from performance_schema.session_status
343 where variable_name like 'NDB_EXECUTE_COUNT'`;
344 UPDATE main SET val = 111 WHERE id = 1;
345 let $after= `select VARIABLE_VALUE from performance_schema.session_status
346 where variable_name like 'NDB_EXECUTE_COUNT'`;
347 if (!`select $after-$before >= 3`)
348 {
349 enable_result_log;
350 echo before: $before;
351 echo after: $after;
352 die Read removal used even when table has update triggers;
353 }
354
355 ### Cleanup
356 # Drop test tables
357 drop table t1, data_t1;
358 drop table t2, data_t2;
359 drop table t3, data_t3;
360 drop table t4, data_t4;
361 drop table t5, data_t5;
362
363 drop trigger log_deletes;
364 drop trigger log_updates;
365 drop table main;
366 drop table log;
367
368 # Drop parameter tables
369 drop table rbwr_tests;
370
371 --enable_query_log
372 --enable_result_log
373
374 if ($ndb_rbwr_fail)
375 {
376 die Test failure detected!;
377 }
378
379