Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma

Benutzer


products/Sources/formale Sprachen/C/MariaDB/mysql-test/main/ (MariaDB Server Version 8.1-8.4^©) Datei vom 1.9.2026 mit Größe 4 kB

Impressum update_use_source.result Sprache: Lisp

set @save_default_engine=@@default_storage_engine;
#######################################
#                                     #
#            Engine InnoDB            #
#                                     #
#######################################
set global innodb_stats_persistent=1;
set default_storage_engine=InnoDB;
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;
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;
/
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;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
create table tmp as select * from t1;
#######################################
#        Test without any index       #
#######################################
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#          Test with an index         #
#######################################
create index t1_c2 on t1 (c2,c1);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref t1_c2 t1_c2 5 const 8
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where
2 DEPENDENT SUBQUERY a index NULL t1_c2 10 NULL 32 Using where; Using index
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index; FirstMatch(t1)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition; Using where
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 1 Using where; Using index
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 5 const 8 Using where; Using index
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#       Test with a primary key       #
#######################################
drop index t1_c2 on t1;
alter table t1 add primary key (c3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 29 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using buffer
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
# Update with error "Subquery returns more than 1 row"
update t1 set c2=(select c2 from t1);
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Update with error "Subquery returns more than 1 row"
# and order by
update t1 set c2=(select c2 from t1) order by c3;
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Duplicate value on update a primary key
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key and limit
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
# and limit
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows found
update t1 set c1=10
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
10 1 1
10 2 2
10 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows changed
drop trigger trg_t1;
update t1 set c1=c1
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 0
info: Rows matched: 3  Changed: 0  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check call of after trigger
#
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;
/
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check update with order by and after trigger
#
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;
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop view v1;
#
# Check update on view with check option
#
create view v1 as select * from t1 where c2=2 with check option;
update v1 set c2=3 where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
update v1 set c2=(select max(c3) from v1) where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
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;
select c1,c2,c3 from t1;
c1 c2 c3
0 2 2
1 1 1
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop table tmp;
drop view v1;
drop table t1;
#
# Test on dynamic columns (blob)
#
create table assets (
item_name varchar(32) primary key, -- A common attribute for all items
dynamic_cols  blob  -- Dynamic columns will be stored here
);
INSERT INTO assets VALUES ('MariaDB T-shirt',
COLUMN_CREATE('color', 'blue', 'size', 'XL'));
INSERT INTO assets VALUES ('Thinkpad Laptop',
COLUMN_CREATE('color', 'black', 'price', 500));
SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 3 years
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
WHERE item_name in
(select b.item_name from assets b
where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 4 years
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty',
(select COLUMN_GET(b.dynamic_cols, 'color' as char)
from assets b
where assets.item_name = item_name));
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
drop table assets;
#
# Test on fulltext columns
#
CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
INSERT INTO ft2(copy) VALUES
('MySQL vs MariaDB database'),
('Oracle vs MariaDB database'),
('PostgreSQL vs MariaDB database'),
('MariaDB overview'),
('Foreign keys'),
('Primary keys'),
('Indexes'),
('Transactions'),
('Triggers');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
copy
MySQL vs MariaDB database
Oracle vs MariaDB database
PostgreSQL vs MariaDB database
update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5)))
from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
where MATCH(copy) AGAINST('keys');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
copy
mykeyword Postg
mykeyword Postg
drop table ft2;
#######################################
#                                     #
#            Engine Aria              #
#                                     #
#######################################
set default_storage_engine=Aria;
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;
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;
/
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;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
create table tmp as select * from t1;
#######################################
#        Test without any index       #
#######################################
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#          Test with an index         #
#######################################
create index t1_c2 on t1 (c2,c1);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 21 Using index condition
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 21 Using index condition
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 21 Using index condition
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 21 Using index condition
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref t1_c2 t1_c2 5 const 8
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where
2 DEPENDENT SUBQUERY a index NULL t1_c2 10 NULL 32 Using where; Using index
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index; FirstMatch(t1)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition; Using where
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 1 Using where; Using index
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 5 const 8 Using where; Using index
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#       Test with a primary key       #
#######################################
drop index t1_c2 on t1;
alter table t1 add primary key (c3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 30 Using index condition; Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using buffer
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
# Update with error "Subquery returns more than 1 row"
update t1 set c2=(select c2 from t1);
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Update with error "Subquery returns more than 1 row"
# and order by
update t1 set c2=(select c2 from t1) order by c3;
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Duplicate value on update a primary key
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key and limit
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
# and limit
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows found
update t1 set c1=10
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
10 1 1
10 2 2
10 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows changed
drop trigger trg_t1;
update t1 set c1=c1
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 0
info: Rows matched: 3  Changed: 0  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check call of after trigger
#
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;
/
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 4
2 2 2
2 2 5
2 3 3
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check update with order by and after trigger
#
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;
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
1 3 3
11 2 12
11 3 13
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 11
2 1 14
2 1 4
2 2 2
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop view v1;
#
# Check update on view with check option
#
create view v1 as select * from t1 where c2=2 with check option;
update v1 set c2=3 where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
update v1 set c2=(select max(c3) from v1) where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
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;
select c1,c2,c3 from t1;
c1 c2 c3
0 2 2
1 1 1
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop table tmp;
drop view v1;
drop table t1;
#
# Test on dynamic columns (blob)
#
create table assets (
item_name varchar(32) primary key, -- A common attribute for all items
dynamic_cols  blob  -- Dynamic columns will be stored here
);
INSERT INTO assets VALUES ('MariaDB T-shirt',
COLUMN_CREATE('color', 'blue', 'size', 'XL'));
INSERT INTO assets VALUES ('Thinkpad Laptop',
COLUMN_CREATE('color', 'black', 'price', 500));
SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 3 years
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
WHERE item_name in
(select b.item_name from assets b
where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 4 years
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty',
(select COLUMN_GET(b.dynamic_cols, 'color' as char)
from assets b
where assets.item_name = item_name));
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
drop table assets;
#
# Test on fulltext columns
#
CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
INSERT INTO ft2(copy) VALUES
('MySQL vs MariaDB database'),
('Oracle vs MariaDB database'),
('PostgreSQL vs MariaDB database'),
('MariaDB overview'),
('Foreign keys'),
('Primary keys'),
('Indexes'),
('Transactions'),
('Triggers');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
copy
MySQL vs MariaDB database
Oracle vs MariaDB database
PostgreSQL vs MariaDB database
update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5)))
from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
where MATCH(copy) AGAINST('keys');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
copy
mykeyword Postg
mykeyword Postg
drop table ft2;
#######################################
#                                     #
#            Engine MyISAM            #
#                                     #
#######################################
set default_storage_engine=MyISAM;
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;
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;
/
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;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
create table tmp as select * from t1;
#######################################
#        Test without any index       #
#######################################
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#          Test with an index         #
#######################################
create index t1_c2 on t1 (c2,c1);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index; FirstMatch(t1)
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref t1_c2 t1_c2 5 const 8
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where
2 DEPENDENT SUBQUERY a index NULL t1_c2 10 NULL 32 Using where; Using index
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index; FirstMatch(t1)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 1 Using index condition; Using where
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 1 Using where; Using index
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 10 NULL 2 Using index condition
3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 5 const 8 Using where; Using index
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#       Test with a primary key       #
#######################################
drop index t1_c2 on t1;
alter table t1 add primary key (c3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using buffer
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
# Update with error "Subquery returns more than 1 row"
update t1 set c2=(select c2 from t1);
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Update with error "Subquery returns more than 1 row"
# and order by
update t1 set c2=(select c2 from t1) order by c3;
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Duplicate value on update a primary key
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key and limit
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
# and limit
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows found
update t1 set c1=10
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
10 1 1
10 2 2
10 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows changed
drop trigger trg_t1;
update t1 set c1=c1
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 0
info: Rows matched: 3  Changed: 0  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check call of after trigger
#
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;
/
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 4
2 2 2
2 2 5
2 3 3
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check update with order by and after trigger
#
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;
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
1 3 3
11 2 12
11 3 13
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 11
2 1 14
2 1 4
2 2 2
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop view v1;
#
# Check update on view with check option
#
create view v1 as select * from t1 where c2=2 with check option;
update v1 set c2=3 where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
update v1 set c2=(select max(c3) from v1) where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
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;
select c1,c2,c3 from t1;
c1 c2 c3
0 2 2
1 1 1
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop table tmp;
drop view v1;
drop table t1;
#
# Test on dynamic columns (blob)
#
create table assets (
item_name varchar(32) primary key, -- A common attribute for all items
dynamic_cols  blob  -- Dynamic columns will be stored here
);
INSERT INTO assets VALUES ('MariaDB T-shirt',
COLUMN_CREATE('color', 'blue', 'size', 'XL'));
INSERT INTO assets VALUES ('Thinkpad Laptop',
COLUMN_CREATE('color', 'black', 'price', 500));
SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 3 years
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
WHERE item_name in
(select b.item_name from assets b
where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt NULL
Thinkpad Laptop 4 years
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty',
(select COLUMN_GET(b.dynamic_cols, 'color' as char)
from assets b
where assets.item_name = item_name));
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
item_name color
MariaDB T-shirt blue
Thinkpad Laptop black
drop table assets;
#
# Test on fulltext columns
#
CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
INSERT INTO ft2(copy) VALUES
('MySQL vs MariaDB database'),
('Oracle vs MariaDB database'),
('PostgreSQL vs MariaDB database'),
('MariaDB overview'),
('Foreign keys'),
('Primary keys'),
('Indexes'),
('Transactions'),
('Triggers');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
copy
MySQL vs MariaDB database
Oracle vs MariaDB database
PostgreSQL vs MariaDB database
update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5)))
from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
where MATCH(copy) AGAINST('keys');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
copy
mykeyword Postg
mykeyword Postg
drop table ft2;
#######################################
#                                     #
#            Engine MEMORY            #
#                                     #
#######################################
set default_storage_engine=MEMORY;
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;
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;
/
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;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
create table tmp as select * from t1;
#######################################
#        Test without any index       #
#######################################
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#          Test with an index         #
#######################################
create index t1_c2 on t1 (c2,c1);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL t1_c2 NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2 FirstMatch(t1)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 2 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL t1_c2 NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 2 FirstMatch(t1)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where
1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 2 FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Operation failed
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#######################################
#       Test with a primary key       #
#######################################
drop index t1_c2 on t1;
alter table t1 add primary key (c3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
#
# Update with value from subquery on the same table
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
1->3 3 *
2->4 4 *
2->5 5 *
2->6 6 *
2->7 7 *
2->8 8 *
11->11 11
11->12 12 *
11->13 13 *
12->14 14 *
12->15 15 *
12->16 16 *
12->17 17 *
12->18 18 *
21->21 21
21->22 22 *
21->23 23 *
22->24 24 *
22->25 25 *
22->26 26 *
22->27 27 *
22->28 28 *
31->31 31
31->32 32 *
31->33 33 *
32->34 34 *
32->35 35 *
32->36 36 *
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + possibly sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c1=10 where c1 <2
and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->10 1 *
1->10 2 *
1->10 3 *
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with EXISTS subquery over the updated table
# in WHERE + non-sargable condition
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED a ALL NULL NULL NULL NULL 32
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 order by c2;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
1->11 3 *
NULL 4
NULL 5
2->12 6 *
2->12 7 *
2->12 8 *
NULL 11
NULL 12
11->21 13 *
NULL 14
NULL 15
12->22 16 *
12->22 17 *
12->22 18 *
NULL 21
21->31 22 *
21->31 23 *
NULL 24
22->32 25 *
22->32 26 *
22->32 27 *
22->32 28 *
NULL 31
31->41 32 *
31->41 33 *
NULL 34
32->42 35 *
32->42 36 *
32->42 37 *
32->42 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a reference to view in subquery
# in settable value
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update t1 set c1=c1 +(select max(a.c2) from v1 a
where a.c1 = t1.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->3 1 *
1->3 2 *
1->3 3 *
2->4 4 *
2->4 5 *
2->4 6 *
2->4 7 *
2->4 8 *
11->13 11 *
11->13 12 *
11->13 13 *
12->14 14 *
12->14 15 *
12->14 16 *
12->14 17 *
12->14 18 *
21->23 21 *
21->23 22 *
21->23 23 *
22->24 24 *
22->24 25 *
22->24 26 *
22->24 27 *
22->24 28 *
31->33 31 *
31->33 32 *
31->33 33 *
32->34 34 *
32->34 35 *
32->34 36 *
32->34 37 *
32->34 38 *
truncate table t1;
insert into t1 select * from tmp;
#
# Update view
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using where
explain update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + (select max(a.c2) from t1 a
where a.c1 = v1.c1) +10 where c3 > 3;
affected rows: 7
info: Rows matched: 7  Changed: 7  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
2->17 5 *
NULL 6
NULL 7
NULL 8
NULL 11
11->24 12 *
NULL 13
NULL 14
12->27 15 *
NULL 16
NULL 17
NULL 18
21->35 21 *
NULL 22
NULL 23
22->38 24 *
NULL 25
NULL 26
NULL 27
NULL 28
31->45 31 *
NULL 32
NULL 33
32->48 34 *
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1 set c1=c1 + 1 where c1 <2
and exists (select 'X' from v1 a where a.c1 = v1.c1);
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update view with EXISTS and reference to the same view in subquery
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from v1 where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where
update v1
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
where c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
1->11 2 *
NULL 3
NULL 4
2->12 5 *
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with IN predicand over the updated table in WHERE
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1)
update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
affected rows: 32
info: Rows matched: 32  Changed: 32  Warnings: 0
select c3 from t1;
c3
111
112
113
114
115
116
117
118
121
122
123
124
125
126
127
128
131
132
133
134
135
136
137
138
141
142
143
144
145
146
147
148
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
1->1 1
1->2 2 *
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
NULL 37
NULL 38
truncate table t1;
insert into t1 select * from tmp;
#
# Update with a limit and an order by
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze note The storage engine for the table doesn't support analyze
explain select * from t1 order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using filesort
explain update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using filesort
2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1
update t1
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
order by c3 desc limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select concat(old_c1,'->',c1),c3,
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
concat(old_c1,'->',c1) c3 Changed
NULL 1
NULL 2
NULL 3
NULL 4
NULL 5
NULL 6
NULL 7
NULL 8
NULL 11
NULL 12
NULL 13
NULL 14
NULL 15
NULL 16
NULL 17
NULL 18
NULL 21
NULL 22
NULL 23
NULL 24
NULL 25
NULL 26
NULL 27
NULL 28
NULL 31
NULL 32
NULL 33
NULL 34
NULL 35
NULL 36
32->37 37 *
32->38 38 *
truncate table t1;
insert into t1 select * from tmp;
# Update with error "Subquery returns more than 1 row"
update t1 set c2=(select c2 from t1);
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Update with error "Subquery returns more than 1 row"
# and order by
update t1 set c2=(select c2 from t1) order by c3;
ERROR 21000: Subquery returns more than 1 row
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
# Duplicate value on update a primary key
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 20
info: Rows matched: 20  Changed: 20  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key and limit
update t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Duplicate value on update a primary key with ignore
# and limit
update ignore t1 set c3=0
where exists (select 'X' from t1 a where a.c2 = t1.c2)
and c2 >= 3 limit 2;
affected rows: 2
info: Rows matched: 2  Changed: 2  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 0
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows found
update t1 set c1=10
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
10 1 1
10 2 2
10 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
# Update no rows changed
drop trigger trg_t1;
update t1 set c1=c1
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
affected rows: 0
info: Rows matched: 3  Changed: 0  Warnings: 0
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check call of after trigger
#
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;
/
update t1 set c1=2
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 4
2 2 2
2 2 5
2 3 3
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
#
# Check update with order by and after trigger
#
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;
ERROR 45000: in after update trigger on 5
select c1,c2,c3 from t1;
c1 c2 c3
1 3 3
11 2 12
11 3 13
12 2 15
12 3 16
12 4 17
12 5 18
2 1 1
2 1 11
2 1 14
2 1 4
2 2 2
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop view v1;
#
# Check update on view with check option
#
create view v1 as select * from t1 where c2=2 with check option;
update v1 set c2=3 where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
update v1 set c2=(select max(c3) from v1) where c1=1;
ERROR 44000: CHECK OPTION failed `test`.`v1`
select c1,c2,c3 from t1;
c1 c2 c3
1 1 1
1 2 2
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
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;
select c1,c2,c3 from t1;
c1 c2 c3
0 2 2
1 1 1
1 3 3
11 1 11
11 2 12
11 3 13
12 1 14
12 2 15
12 3 16
12 4 17
12 5 18
2 1 4
2 2 5
2 3 6
2 4 7
2 5 8
21 2 21
21 3 22
21 4 23
22 2 24
22 3 25
22 4 26
22 5 27
22 6 28
31 2 31
31 3 32
31 4 33
32 2 34
32 3 35
32 4 36
32 5 37
32 6 38
truncate table t1;
insert into t1 select * from tmp;
drop table tmp;
drop view v1;
drop table t1;
set @@default_storage_engine=@save_default_engine;
#
# Test with MyISAM
#
create table t1 (old_c1 integer,
old_c2 integer,
c1 integer,
c2 integer,
c3 integer) engine=MyISAM;
insert t1 (c1,c2,c3) select 0,seq,seq%10 from seq_1_to_500;
insert t1 (c1,c2,c3) select 1,seq,seq%10 from seq_1_to_400;
insert t1 (c1,c2,c3) select 2,seq,seq%10 from seq_1_to_300;
insert t1 (c1,c2,c3) select 3,seq,seq%10 from seq_1_to_200;
create index t1_idx1 on t1(c3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
update t1 set c1=2 where exists (select 'x' from t1);
select count(*) from t1 where c1=2;
count(*)
1400
update t1 set c1=3 where c3 in (select c3 from t1 b where t1.c3=b.c1);
select count(*) from t1 where c1=3;
count(*)
140
drop table t1;
#
# Test error on multi_update conversion on view
# with order by or limit
#
create table t1 (c1 integer) engine=InnoDb;
create table t2 (c1 integer) engine=InnoDb;
create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1"
        from t1,t2 where t1.c1=t2.c1;
update v1 set t1c1=2 order by 1;
update v1 set t1c1=2 limit 1;
drop table t1;
drop table t2;
drop view v1;

Messung V0.5 in Prozent

¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen2.22Angebot (Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können 2026-10-08) ¤

*Eine klare Vorstellung vom Zielzustand

Wurzel

Suchen

PVS Prover

Isabelle Prover

NIST Cobol Testsuite

Cephes Mathematical Library

Vienna Development Method

Haftungshinweis

Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.