MDEV-12874 UPDATE statements with the same source and target

11 files changed, 1201 insertions, 179 deletions

diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index 6ccfe29b2cd..af53f67869d 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -15,74 +15,6 @@ create table t2aA (col1 int);
 create view v1Aa as select * from t1aA;
 create view v2aA as select * from v1aA;
 create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
-update v2aA set col1 = (select max(col1) from v1Aa);
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 'v2aA'
-update v2Aa set col1 = (select max(col1) from t1Aa);
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 'v2Aa'
-update v2aA set col1 = (select max(col1) from v2Aa);
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v2aA'
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 't1aA'
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v1aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 't2Aa'
-update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 't2Aa'
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v2aA'
-update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v1aA'
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't1aA'
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 'v1aA'
-update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 't2Aa'
-update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
-ERROR HY000: The definition of table 'v3aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
-ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'
-delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 't1Aa'
-delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 'v2Aa'
-delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v1Aa'
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 't1aA'
-delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation DELETE on table 'v1aA'
 insert into v2Aa values ((select max(col1) from v1aA));
 ERROR HY000: The definition of table 'v1aA' prevents operation INSERT on table 'v2Aa'
 insert into t1aA values ((select max(col1) from v1Aa));
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index f55251ac199..afea08593a6 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3714,34 +3714,6 @@ insert into m1 (a) values ((select max(a) from v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'
 insert into m1 (a) values ((select max(a) from tmp, v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'
-update m1 set a = ((select max(a) from m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from v1));
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
-update m1 set a = ((select max(a) from tmp, v1));
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
 drop view v1;
 drop temporary table tmp;
 drop table t1, t2, t3, m1, m2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 339dc35e9c4..45239f6e090 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -442,7 +442,6 @@ drop table t1, t2, t3;
 create table t1 (col1 int);
 create table t2 (col1 int);
 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
 ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 drop table t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 919693efffb..7ba8b545e6a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -583,8 +583,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 806475b3380..c09f3c94710 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -587,8 +587,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 237a6dbf9bb..0aefeaf44d9 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -590,8 +590,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index af1afe47f32..92defb3c36d 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -586,8 +586,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 75be8642069..b47dab2e79e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -589,8 +589,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index a6a6397375b..9d04ddd9829 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -586,8 +586,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/update_use_source.result b/mysql-test/r/update_use_source.result
new file mode 100644
index 00000000000..e5585fcee5d
--- /dev/null
+++ b/mysql-test/r/update_use_source.result
@@ -0,0 +1,1201 @@
+create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=InnoDb;
+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);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+commit;
+select * from t1;
+old_c1	old_c2	c1	c2	c3
+NULL	NULL	1	1	1
+NULL	NULL	1	2	2
+NULL	NULL	1	3	3
+NULL	NULL	2	1	4
+NULL	NULL	2	2	5
+NULL	NULL	2	3	6
+NULL	NULL	2	4	7
+NULL	NULL	2	5	8
+Test without any index
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+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	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  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	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+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	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+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	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+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	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+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	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with an index on updated columns
+create index t1_c2 on t1 (c2,c1);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+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	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 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	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	4	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  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	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+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	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+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	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 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	8	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+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+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	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+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	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with an index on updated columns
+create index t1_c3 on t1 (c3);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+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	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 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	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  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	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+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	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+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	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 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	8	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+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+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	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+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	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with a primary key on updated columns
+drop index t1_c3 on t1;
+alter table t1 add primary key (c3);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+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	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 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	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  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	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+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	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  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	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+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	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+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	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 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	8	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+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+14
+12
+15
+13
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+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	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+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	 
+2->7	7	*
+2->8	8	*
+rollback;
+# 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
+# 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
+Duplicate value on update a primary key
+start transaction;
+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'
+rollback;
+Duplicate value on update a primary key with ignore
+start transaction;
+update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+rollback;
+Duplicate value on update a primary key and limit
+start transaction;
+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'
+rollback;
+Duplicate value on update a primary key with ignore and limit
+start transaction;
+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
+rollback;
+# 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: 0
+info: Rows matched: 0  Changed: 0  Warnings: 0
+# Update no rows changed
+drop trigger trg_t1;
+start transaction;
+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
+rollback;
+#
+# 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
+#
+# 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;
+ERROR 45000: in after update trigger on 5
+drop view v1;
+#
+# Check update on view with check option
+#
+create view v1 as select * from t1 where c2=2 with check option;
+start transaction;
+update v1 set c2=3 where c1=1;
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+rollback;
+start transaction;
+update v1 set c2=(select max(c3) from v1) where c1=1;
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+rollback;
+start transaction;
+update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
+rollback;
+drop view v1;
+drop table t1;
+#
+# Test with a temporary table
+#
+create temporary table t1 (c1 integer, c2 integer, c3 integer) engine=InnoDb;
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+start transaction;
+update t1
+set c1=(select a.c2
+from t1 a
+where a.c3 = t1.c3) limit 3;
+affected rows: 2
+info: Rows matched: 3  Changed: 2  Warnings: 0
+select * from t1 ;
+c1	c2	c3
+1	1	1
+2	2	2
+3	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+rollback;
+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)) ENGINE=MyISAM;
+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;
+#
+# 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	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;
+ERROR 42S22: Unknown column '1' in 'order clause'
+update v1 set t1c1=2 limit 1;
+drop table t1;
+drop table t2;
+drop view v1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 24c669308cd..6eead303c7a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -943,74 +943,6 @@ create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
-update v2 set col1 = (select max(col1) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'
-update v2 set col1 = (select max(col1) from t1);
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'
-update v2 set col1 = (select max(col1) from v2);
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'
-update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'
-update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
-ERROR HY000: Table 'v1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'
-update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v1'
-update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't1'
-update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v1'
-update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update v3 set v3.col1 = (select max(col1) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from t1);
-ERROR HY000: The definition of table 'v3' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from v2);
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from v3);
-ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
-delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 't1'
-delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
-ERROR HY000: Table 'v1' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'
-delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v1'
-delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 't1'
-delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v1'
 insert into v2 values ((select max(col1) from v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'
 insert into t1 values ((select max(col1) from v1));
@@ -2024,8 +1956,6 @@ create view v1 as select f59, f60 from t1 where f59 in
 (select f59 from t1);
 update v1 set f60=2345;
 ERROR HY000: The target table v1 of the UPDATE is not updatable
-update t1 set f60=(select max(f60) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'
 drop view v1;
 drop table t1;
 create table t1 (s1 int);