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