1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
#
# DELETE Statements
#
# The include file works as intended only when index_merge_single_col_setup.inc
# or index_merge_multi_col_setup.inc is used. The table t1 set-up is done in
# these two include files.
--disable_warnings
DROP TABLE IF EXISTS t2;
--enable_warnings
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;
ANALYZE TABLE t2;
#1. SQL statments with various combinations of comparison operators
# union and sort_union
--let $query = FROM t1 WHERE key1=25 OR key4=10
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key1=48 OR key4=2 OR key6=3
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key3=1025 OR key5 IS NULL
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key3=45 OR key1=6 OR key6 IS NULL
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1=4 AND key3=6)
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
INSERT INTO t1 VALUES (42,42,42,42,3,3,3,3),(4,4,6,6,6,6,4,4);
--let $query = FROM t1 WHERE (key4=42 AND key6=3) OR (key1=4 AND key3=6)
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
INSERT INTO t1 VALUES (42,42,42,42,3,3,3,3),(4,4,6,6,6,6,4,4);
--let $query = FROM t1 WHERE (key4=42 AND key6=3) OR (key1=4 AND key3=6) AND key5<>50
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key1<2 OR key2<3
--replace_result "i1,i2" "i2,i1"
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key1<5 OR key3<7
--replace_result "i1,i3" "i3,i1"
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
INSERT INTO t1 VALUES (10,10,10,10,10,10,10,10),(3,3,3,3,3,3,3,3);
--let $query = FROM t1 WHERE key2=10 OR key3=3 OR key4 IS NULL
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
INSERT INTO t1 VALUES (10,10,10,10,10,10,10,10),(3,3,3,3,3,3,3,3);
--let $query = FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE (key1>30 AND key1<35) OR (key2>32 AND key2<40)
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key1 BETWEEN 30 AND 35 OR key2 BETWEEN 32 AND 40
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE (key1>30 AND key1<35) OR (key2>32 AND key2<40) OR (key3>20 AND key3<45)
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE key1 BETWEEN 30 AND 35 OR key2 BETWEEN 32 AND 40 OR key3 BETWEEN 20 AND 45
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
INSERT INTO t1 VALUES (1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3),(4,4,4,4,4,4,4,4);
--let $query = FROM t1 WHERE ((key3<4 OR key5<3) AND (key1<3 OR key2<3)) OR (((key3<5 AND key6<5) OR key5<2) AND (key5<4 OR key6<4))
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE (key4<42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
--let $query = FROM t1 WHERE (key5 IN (15,70) OR key6 IN (15,70))
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
#2. Statements with ORDER BY
INSERT INTO t1 VALUES (1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3);
--let $query = FROM t1 WHERE key1<2 OR key2<3 ORDER BY key1
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
#3. Multi table SQL statements
--let $query = FROM t1 USING t1 INNER JOIN t2 ON (t2.key1=t2.key1) WHERE t1.key1=3 OR t1.key2=4
--replace_column 9 #
--eval EXPLAIN DELETE $query
SELECT COUNT(*) FROM t1;
--eval DELETE $query
SELECT COUNT(*) FROM t1;
# Statements with subqueries
--let $query1 = FROM t2 WHERE key6 = (SELECT key6 FROM t1 WHERE key1=10 OR key5=3 OR key2 IS NULL)
--let $query2 = key3=28 OR key4=10
--replace_column 9 #
--eval EXPLAIN DELETE $query1 OR $query2
--eval SELECT COUNT(*) $query1 OR $query2
--eval DELETE $query1 OR $query2
--eval SELECT COUNT(*) $query1 OR $query2
--let $query3 = key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7)
--let $query2 = key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8)
--let $query1 = FROM t2 WHERE key1=25 OR key4=40
--replace_column 9 #
--eval EXPLAIN DELETE $query1 AND ($query2 OR $query3)
--eval SELECT COUNT(*) $query1 AND ($query2 OR $query3)
--eval DELETE $query1 AND ($query2 OR $query3)
--eval SELECT COUNT(*) $query1 AND ($query2 OR $query3)
INSERT INTO t1 VALUES (2,2,2,2,2,2,2,2),(5,5,5,5,5,5,5,5);
--let $query = FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE key1<5 OR key3<7)
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
# The following statement uses index_merge optimization only when the table is
# created with multi column setup.
INSERT INTO t1 VALUES ( 3,3,3,3,3,3,3,3 ),(4,4,4,4,4,4,4,4);
--let $query = FROM t1 WHERE key7 = 3 OR key8 = 4
--replace_column 9 #
--eval EXPLAIN DELETE $query
--eval SELECT COUNT(*) $query
--eval DELETE $query
--eval SELECT COUNT(*) $query
#
# end of DELETE statements
#