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