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
|
# Include to test update with same table as source and target
create table t1 (old_c1 integer,
old_c2 integer,
c1 integer,
c2 integer,
c3 integer);
create view v1 as select * from t1 where c2=2;
delimiter /;
create trigger trg_t1 before update on t1 for each row
begin
set new.old_c1=old.c1;
set new.old_c2=old.c2;
end;
/
delimiter ;/
insert into t1(c1,c2,c3)
values (1,1,1), (1,2,2), (1,3,3),
(2,1,4), (2,2,5), (2,3,6),
(2,4,7), (2,5,8);
insert into t1 select NULL, NULL, c1+10,c2,c3+10 from t1;
insert into t1 select NULL, NULL, c1+20,c2+1,c3+20 from t1;
analyze table t1 persistent for all;
create table tmp as select * from t1;
--echo #######################################
--echo # Test without any index #
--echo #######################################
--source include/update_use_source_cases.inc
--echo #######################################
--echo # Test with an index #
--echo #######################################
create index t1_c2 on t1 (c2,c1);
analyze table t1;
--source include/update_use_source_cases.inc
--echo #######################################
--echo # Test with a primary key #
--echo #######################################
drop index t1_c2 on t1;
alter table t1 add primary key (c3);
analyze table t1;
--source include/update_use_source_cases.inc
--echo # Update with error "Subquery returns more than 1 row"
--error ER_SUBQUERY_NO_1_ROW
update t1 set c2=(select c2 from t1);
--sorted_result
select c1,c2,c3 from t1;
--echo # Update with error "Subquery returns more than 1 row"
--echo # and order by
--error ER_SUBQUERY_NO_1_ROW
update t1 set c2=(select c2 from t1) order by c3;
--sorted_result
select c1,c2,c3 from t1;
-- echo # Duplicate value on update a primary key
--error ER_DUP_ENTRY
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
-- echo # Duplicate value on update a primary key with ignore
--enable_info ONCE
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
-- echo # Duplicate value on update a primary key and limit
--error ER_DUP_ENTRY
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
-- echo # Duplicate value on update a primary key with ignore
-- echo # and limit
--enable_info ONCE
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo # Update no rows found
--enable_info ONCE
update t1 set c1=10
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo # Update no rows changed
drop trigger trg_t1;
--enable_info ONCE
update t1 set c1=c1
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Check call of after trigger
--echo #
delimiter /;
create or replace trigger trg_t2 after update on t1 for each row
begin
declare msg varchar(100);
if (new.c3 = 5) then
set msg=concat('in after update trigger on ',new.c3);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
end if;
end;
/
delimiter ;/
--error 1644
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
--echo #
--echo # Check update with order by and after trigger
--echo #
--error 1644
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1)
order by t1.c2, t1.c1;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
drop view v1;
--echo #
--echo # Check update on view with check option
--echo #
create view v1 as select * from t1 where c2=2 with check option;
-- error 1369
update v1 set c2=3 where c1=1;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
-- error 1369
update v1 set c2=(select max(c3) from v1) where c1=1;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
--sorted_result
select c1,c2,c3 from t1;
truncate table t1;
insert into t1 select * from tmp;
drop table tmp;
drop view v1;
drop table t1;
|
