Debian Codegraph @

mysql-test/suite/ndb/t/ndb_bulk_delete.test

raw
1 #
2 # Basic test of bulk delete interface. Test counts number of execute
3 # as measurement of number of round trips to kernel.
4 #
5 -- source include/have_ndb.inc
6
7 create table t1 (a int key) engine ndb;
8 insert into t1 values (1);
9 insert into t1 select a+1 from t1;
10 insert into t1 select a+2 from t1;
11 insert into t1 select a+4 from t1;
12 insert into t1 select a+8 from t1;
13 insert into t1 select a+16 from t1;
14 insert into t1 select a+32 from t1;
15 insert into t1 select a+64 from t1;
16 insert into t1 select a+128 from t1;
17 select count(*) from t1; # 256 rows
18
19 --enable_info
20
21 --echo
22 --echo # test: simple delete of multiple pk's
23 --echo # expected result 1 roundtrips
24 --echo # 0 - info call
25 --echo # 0 - read the rows (with read before delete, this is 1)
26 --echo # 0 - delete the rows (without bulk update this is 5 + 1 for execute no commit)
27 --echo # 1 - delete the row + commit the transaction
28 --echo
29 --source suite/ndb/include/ndb_init_execute_count.inc
30 delete from t1 where a in (1,7, 90, 100, 130);
31 --source suite/ndb/include/ndb_execute_count.inc
32
33 # Test delete with non existant pk
34 # Bug 37153
35 --echo # expected result 1 roundtrips
36 --echo # 0 - info call
37 --echo # 0 - read the rows
38 --echo # 0 - delete the rows
39 --echo # 1 - commit
40 --echo # affected = 0
41 --echo
42 --source suite/ndb/include/ndb_init_execute_count.inc
43 delete from t1 where a=1000;
44 --source suite/ndb/include/ndb_execute_count.inc
45
46 --echo # expected result 1 roundtrips
47 --echo # 0 - info call
48 --echo # 0 - read the rows
49 --echo # 0 - delete the rows
50 --echo # 1 - commit
51 --echo # affected = 0
52 --echo
53 --source suite/ndb/include/ndb_init_execute_count.inc
54 delete from t1 where a in (1000, 1001, 1002, 1003, 1004);
55 --source suite/ndb/include/ndb_execute_count.inc
56
57 --disable_info
58
59 # Check that the expected rows remains
60 select count(*) as "0(was deleted)" from t1 where a in (1,7, 90, 100, 130);
61 select count(*) as "0(never existed)" from t1 where a=1000;
62 select count(*) as "0(never existed)" from t1
63 where a in (1000, 1001, 1002, 1003, 1004);
64 select count(*) as "251(remaining)" from t1;
65 select count(*) as "5(not deleted)" from t1 where a in (2, 3, 4, 5, 6);
66
67 # cleanup
68 drop table t1;
69
70
71 # Build table t2 with 2000 rows
72 create table t2 (
73 a int primary key,
74 b varchar(256) character set latin1)
75 engine ndb;
76 --disable_result_log
77 --disable_query_log
78 let $i = 200;
79 while($i)
80 {
81 eval insert into t2 values
82 ($i*10,"building a table"),
83 ($i*10+1, "one row"), ($i*10+2, "another row"),
84 ($i*10+3, "the third row"), ($i*10+4, ""),
85 ($i*10+5, "fifth"), ($i*10+6, "a longer string at line six"),
86 ($i*10+7, "in the sevent it will get even longer"),
87 ($i*10+8, "another row 8"), ($i*10+9, "another row 9");
88 dec $i;
89 }
90 --enable_result_log
91 select count(*) as "Has loaded 2000 rows" from t2;
92 --enable_query_log
93
94 # Build table t3 and delete from it with increasing
95 # number of values in the IN clause, don't do too many
96 # as mysqld will switch to full table scan
97 --disable_query_log
98 --disable_result_log
99 create table t3 like t2;
100 let $i = 50;
101 let $in = $i;
102 while($i)
103 {
104 # Repopulate t3 with all rows
105 replace into t3 select * from t2;
106
107 # Extend the IN clause with another 10 values
108 let $in =$in,$i*10,$i*10+1,$i*10+2,$i*10+3,$i*10+4,$i*10+5,
109 $i*10+6,$i*10+7,$i*10+8,$i*10+9;
110
111 # Get the execute count before DELETE
112 let $before= `select VARIABLE_VALUE from performance_schema.session_status
113 where variable_name like 'NDB_EXECUTE_COUNT'`;
114
115 # Run the query
116 eval delete from t3 where a in ($in);
117
118 # Check that execute count after DELETE is not greater than 3
119 let $after= `select VARIABLE_VALUE from performance_schema.session_status
120 where variable_name like 'NDB_EXECUTE_COUNT'`;
121 if (!`select $after-$before <= 3`)
122 {
123 echo before: $before;
124 echo after: $after;
125 enable_result_log;
126 show session status like 'ndb%';
127 die Got unexpected ndb_execute_count value;
128 }
129
130 dec $i;
131 }
132 --enable_result_log
133 select count(*) as "Should be 1500 rows left now" from t3;
134 --enable_query_log
135
136 # Cleanup
137 drop table t2, t3;
138
139