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
|
--echo #
--echo # Update with value from subquery on the same table
--echo #
analyze table t1 persistent for all;
let $q = update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
eval explain select * from t1;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with EXISTS subquery over the updated table
--echo # in WHERE + possibly sargable condition
--echo #
analyze table t1 persistent for all;
let $c = c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
let $q = update t1 set c1=10 where $c;
eval explain select * from t1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with EXISTS subquery over the updated table
--echo # in WHERE + non-sargable condition
--echo #
analyze table t1 persistent for all;
let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
let $q = update t1 set c1=c1+10 where $c;
eval explain select * from t1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with order by
--echo #
analyze table t1 persistent for all;
let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
let $q = update t1 set c1=c1+10 where $c;
eval explain select * from t1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with a reference to view in subquery
--echo # in settable value
--echo #
analyze table t1 persistent for all;
let $q = update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
eval explain select * from t1;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update view
--echo #
analyze table t1 persistent for all;
let $q = update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
eval explain select * from v1;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update view with reference to the same view in subquery
--echo #
analyze table t1 persistent for all;
let $c = c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
let $q = update v1 set c1=c1 + 1 where $c;
eval explain select * from v1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update view with EXISTS and reference to the same view in subquery
--echo #
analyze table t1 persistent for all;
let $c = c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
let $q = update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where $c;
eval explain select * from v1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with IN predicand over the updated table in WHERE
--echo #
analyze table t1 persistent for all;
let $c = c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
let $q = update t1 set c3=c3+110 where $c;
eval explain select * from t1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
--sorted_result
select c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with a limit
--echo #
analyze table t1 persistent for all;
let $q = update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
eval explain select * from t1 limit 2;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Update with a limit and an order by
--echo #
analyze table t1 persistent for all;
let $q=update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
eval explain select * from t1 order by c3 desc limit 2;
eval explain $q;
--enable_info ONCE
eval $q;
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
truncate table t1;
insert into t1 select * from tmp;
|
