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
|
#
# UPDATE statements using index_merge optimization
#
# 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. UPDATE statements with various combinations of comparison operators
# intersect optimization technique test cases for non-selects
# are in a separate file(index_merge_intersect_dml.inc)
# due to different table requirements.
# union and sort_union
--let $query = WHERE key1=80 OR key4=49
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key6=7 $query
--eval SELECT COUNT(*), SUM(key6) FROM t1 $query
--eval UPDATE t1 SET key6=7 $query
--eval SELECT COUNT(*), SUM(key6) FROM t1 $query
--let $query = WHERE (key3=80 AND key5 IS NOT NULL) OR (key2=49 AND key6 IS NULL)
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key1=18 $query
--eval SELECT COUNT(*), SUM(key1) FROM t1 $query
--eval UPDATE t1 SET key1=18 $query
--eval SELECT COUNT(*), SUM(key1) FROM t1 $query
--let $query = WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key3=18 $query
--eval SELECT COUNT(*), SUM(key3) FROM t1 $query
--eval UPDATE t1 SET key3=18 $query
--eval SELECT COUNT(*), SUM(key3) FROM t1 $query
--let $query = WHERE key2=80 OR key3=1025 OR key4 <=> NULL
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key8=64 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
--eval UPDATE t1 SET key8=64 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
--let $query = WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2)
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key8=18 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
--eval UPDATE t1 SET key8=18 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
--let $query = WHERE key1 between 25 AND 40 OR key2 between 15 AND 49
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key3=32 $query
--eval SELECT COUNT(*), SUM(key3) FROM t1 $query
--eval UPDATE t1 SET key3=32 $query
--eval SELECT COUNT(*), SUM(key3) FROM t1 $query
--let $query = WHERE key1 IN (15,70) OR key2 IN (15,70)
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key7=25 $query
--eval SELECT COUNT(*), SUM(key7) FROM t1 $query
--eval UPDATE t1 SET key7=25 $query
--eval SELECT COUNT(*), SUM(key7) FROM t1 $query
#2. Statement with ORDER BY
--let $query = WHERE key5<5 OR key8<10 ORDER BY key1
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key1=10 $query
--eval SELECT COUNT(*), SUM(key1) FROM t1 $query
--eval UPDATE t1 SET key1=10 $query
--eval SELECT COUNT(*), SUM(key1) FROM t1 $query
#3. Multi table SQL statements
--let $query = t1 left join t2 ON (t2.key1=t2.key1) SET t1.key4=15 WHERE t1.key5=40 OR t1.key4=15
--replace_column 10 #
--eval EXPLAIN UPDATE $query
SELECT COUNT(*), SUM(key4) FROM t1;
--eval UPDATE $query
SELECT COUNT(*), SUM(key4) FROM t1;
# Statements with subqueries
--let $query2 = key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3)
--let $query1 = WHERE (key3=28 OR key4=10)
--replace_column 10 #
--eval EXPLAIN UPDATE t2 SET key7=7 $query1 AND $query2
--eval SELECT COUNT(*), SUM(key7) FROM t2 $query1 AND $query2
--eval UPDATE t2 SET key7=7 $query1 AND $query2
--eval SELECT COUNT(*), SUM(key7) FROM t2 $query1 AND $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 = WHERE key1=25 OR key4=40
--replace_column 10 #
--eval EXPLAIN UPDATE t2 SET key2=2 $query1 AND ($query2 OR $query3)
--eval SELECT COUNT(*), SUM(key2) FROM t2 $query1 AND ($query2 OR $query3)
--eval UPDATE t2 SET key2=2 $query1 AND ($query2 OR $query3)
--eval SELECT COUNT(*), SUM(key2) FROM t2 $query1 AND ($query2 OR $query3)
--let $query = WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10)
--replace_column 10 #
--eval EXPLAIN UPDATE t2 SET key3=20 $query
--eval SELECT COUNT(*), SUM(key3) FROM t2 $query
--eval UPDATE t2 SET key3=20 $query
--eval SELECT COUNT(*), SUM(key3) FROM t2 $query
# The following statement doesn't use index_merge optimization technique when
# the table is created with single column index setup.
--let $query = WHERE key5=80 OR key6=49
--replace_column 10 #
--eval EXPLAIN UPDATE t1 SET key8=50 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
--eval UPDATE t1 SET key8=50 $query
--eval SELECT COUNT(*), SUM(key8) FROM t1 $query
#
# end of UPDATE statements
#
|
