[Commits] b673b6a: An attempt to rebase against bb-11.0

15 Feb 2023

revision-id: b673b6ae245de404fb1b49b90ffc58823efb3f14 (mariadb-10.11.1-188-gb673b6a)
parent(s): b3480cd20440a6944871bd7743584cb10521898b
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-02-15 06:52:12 -0800
message:

An attempt to rebase against bb-11.0

---
 mysql-test/include/update_use_source.inc           | 155 +-----------
 mysql-test/include/update_use_source_cases.inc     | 130 +---------
 mysql-test/main/delete.result                      |   6 +-
 mysql-test/main/delete_use_source_engines.result   | 281 ++++++++-------------
 mysql-test/main/derived.result                     |  26 +-
 mysql-test/main/derived_cond_pushdown.result       |  27 +-
 mysql-test/main/log_state.result                   |   2 +-
 mysql-test/main/multi_update.result                |   9 +-
 .../main/myisam_explain_non_select_all.result      |  24 +-
 mysql-test/main/opt_trace.result                   |   4 +-
 mysql-test/main/subselect.result                   |   2 +-
 mysql-test/main/subselect.test                     |   2 +-
 mysql-test/main/subselect_no_exists_to_in.result   |   2 +-
 mysql-test/main/subselect_no_mat.result            |   2 +-
 mysql-test/main/subselect_no_opts.result           |   2 +-
 mysql-test/main/subselect_no_scache.result         |   2 +-
 mysql-test/main/subselect_no_semijoin.result       |   2 +-
 mysql-test/main/update_use_source.test             |  55 +---
 18 files changed, 154 insertions(+), 579 deletions(-)

diff --git a/mysql-test/include/update_use_source.inc b/mysql-test/include/update_use_source.inc
index 196f141..03bf168 100644
--- a/mysql-test/include/update_use_source.inc
+++ b/mysql-test/include/update_use_source.inc
@@ -28,162 +28,11 @@ select * from t1;
 
 create table tmp as select * from t1;
 
---echo # Test without any index
---source include/update_use_source_cases.inc
+# --echo # Test without any index
+# --source include/update_use_source_cases.inc
 
 --echo # Test with an index on updated columns
 create index t1_c2 on t1 (c2,c1);
 analyze table t1;
 --source include/update_use_source_cases.inc
 
---echo # Test with an index on updated columns
-create index t1_c3 on t1 (c3);
-analyze table t1;
---source include/update_use_source_cases.inc
-
---echo # Test with a primary key on updated columns
-drop index t1_c3 on t1;
-alter table t1 add primary key (c3);
-analyze table t1;
---source include/update_use_source_cases.inc
-
---echo # Update with error "Subquery returns more than 1 row"
---error ER_SUBQUERY_NO_1_ROW
-update t1 set c2=(select c2 from t1);
---sorted_result
-select c1,c2,c3 from t1;
-
---echo # Update with error "Subquery returns more than 1 row"
---echo # and order by
---error ER_SUBQUERY_NO_1_ROW
-update t1 set c2=(select c2 from t1) order by c3;
---sorted_result
-select c1,c2,c3 from t1;
-
--- echo # Duplicate value on update a primary key
---error ER_DUP_ENTRY
-
-update t1 set c3=0
-    where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
--- echo # Duplicate value on update a primary key with ignore
---enable_info ONCE
-update ignore t1 set c3=0
-    where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
--- echo # Duplicate value on update a primary key and limit
---error ER_DUP_ENTRY
-update t1 set c3=0
-    where exists (select 'X' from t1 a where a.c2 = t1.c2)
-      and c2 >= 3 limit 2;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
--- echo # Duplicate value on update a primary key with ignore
--- echo # and limit
---enable_info ONCE
-update ignore t1 set c3=0
-    where exists (select 'X' from t1 a where a.c2 = t1.c2)
-    and c2 >= 3 limit 2;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo # Update no rows found
---enable_info ONCE
-update t1 set c1=10
-    where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo # Update no rows changed
-drop trigger trg_t1;
---enable_info ONCE
-update t1 set c1=c1
-    where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Check call of after trigger
---echo #
-
-delimiter /;
-create or replace trigger trg_t2 after update on t1 for each row
-begin
-  declare msg varchar(100);
-  if (new.c3 = 5) then
-    set msg=concat('in after update trigger on ',new.c3);
-    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
-  end if;
-end;
-/
-delimiter ;/
---error 1644
-
-update t1 set c1=2
-    where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Check update with order by and after trigger
---echo #
-
---error 1644
-update t1 set c1=2
-    where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1)
-    order by t1.c2, t1.c1;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
-drop view v1;
-
---echo #
---echo # Check update on view with check option
---echo #
-
-create view v1 as select * from t1 where c2=2 with check option;
-
--- error 1369
-update v1 set c2=3 where c1=1;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
--- error 1369
-update v1 set c2=(select max(c3) from v1) where c1=1;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
-update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
---sorted_result
-select c1,c2,c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
-drop table tmp;
-drop view v1;
-drop table t1;
diff --git a/mysql-test/include/update_use_source_cases.inc b/mysql-test/include/update_use_source_cases.inc
index 55b45a3..6e8e5c0 100644
--- a/mysql-test/include/update_use_source_cases.inc
+++ b/mysql-test/include/update_use_source_cases.inc
@@ -1,31 +1,3 @@
---echo #
---echo # Update with value from subquery on the same table
---echo #
-
-let $q=update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update with EXISTS subquery over the updated table
---echo # in WHERE + possibly sargable condition
---echo #
-
-let $q=update t1 set c1=10
-         where c1 <2
-          and exists (select 'X' from t1 a where a.c1 = t1.c1);
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
 
 --echo #
 --echo # Update with EXISTS subquery over the updated table
@@ -38,7 +10,7 @@ eval explain $q;
 --enable_info ONCE
 eval $q;
 select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+    case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
 truncate table t1;
 insert into t1 select * from tmp;
 
@@ -57,103 +29,3 @@ select concat(old_c1,'->',c1),c3,
 truncate table t1;
 insert into t1 select * from tmp;
 
---echo #
---echo # Update with a reference to view in subquery
---echo #
-
-let $q=update t1 set c1=c1 +(select max(a.c2) from v1 a
-                              where a.c1 = t1.c1);
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update view
---echo #
-
-let $q=update v1 set c1=c1 + (select max(a.c2) from t1 a
-                                 where a.c1 = v1.c1) +10 where c3 > 3;
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update view with reference to the same view in subquery
---echo #
-
-let $q=update v1 set c1=c1 + 1
-            where c1 <2
-            and exists (select 'X' from v1 a where a.c1 = v1.c1);
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update view with EXISTS and reference to the same view in subquery
---echo #
-
-let $q=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);
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update with IN predicand over the updated table in WHERE
---echo #
-
-let $q=update t1 set c3=c3+10
-   where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
-eval explain $q;
---enable_info ONCE
-eval $q;
---sorted_result
-select c3 from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update with a limit
---echo #
-
-let $q=update t1
-         set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
-eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
-
---echo #
---echo # Update with a limit and an order by
---echo #
-
-let $q=update t1
-         set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
-            order by c3 desc limit 2;
-#eval explain $q;
---enable_info ONCE
-eval $q;
-select concat(old_c1,'->',c1),c3,
-   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-truncate table t1;
-insert into t1 select * from tmp;
diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index 98d092c..3e3030a 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -583,8 +583,7 @@ explain delete 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	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
 delete from t1
 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
 select *from t1;
@@ -600,8 +599,7 @@ 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	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
 delete from t1
 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
 order by c2;
diff --git a/mysql-test/main/delete_use_source_engines.result b/mysql-test/main/delete_use_source_engines.result
index b038b77..32d291d 100644
--- a/mysql-test/main/delete_use_source_engines.result
+++ b/mysql-test/main/delete_use_source_engines.result
@@ -72,18 +72,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	12.00	100.00	5.56	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -130,13 +127,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -731,22 +726,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	31.00	25.00	3.23	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	32.00	100.00	3.12	Using where
 select * from t1;
 c1	c2	c3
@@ -843,18 +837,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	32.00	100.00	100.00	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	11.67	100.00	5.71	Using where; Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -900,14 +891,12 @@ test.t1	analyze	status	OK
 create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 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	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	const	8	
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 explain delete 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	ref	t1_c2	t1_c2	5	const	8	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -1038,7 +1027,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	30.00	100.00	13.33	Using where
-1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	100.00	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	18.75	100.00	Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -1502,8 +1491,7 @@ and 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	ref	t1_c2	t1_c2	10	const,const	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index
+1	PRIMARY	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index; FirstMatch(t1)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
@@ -1564,11 +1552,11 @@ create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3
 explain select * from t1 where 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	Using where
-2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	Using index
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	
 explain delete from t1 where 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	Using where
-2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	Using index
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	
 delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
 affected rows: 4
 select * from t1;
@@ -1615,18 +1603,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	12.33	100.00	5.41	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -1673,13 +1658,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -1810,7 +1793,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	1.00	100.00	100.00	Using where; Start temporary
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	100.00	Using where; End temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	3.12	100.00	Using where; End temporary
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -2274,22 +2257,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	5.00	25.00	20.00	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	1.00	100.00	100.00	
 select * from t1;
 c1	c2	c3
@@ -2475,18 +2457,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	12.67	100.00	5.26	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -2533,13 +2512,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -3134,22 +3111,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	5.00	25.00	20.00	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	32.00	100.00	3.12	Using where
 select * from t1;
 c1	c2	c3
@@ -3246,18 +3222,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	32.00	100.00	100.00	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	11.67	100.00	5.71	Using where; Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -3304,13 +3277,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	ref	t1_c2	t1_c2	5	const	8	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 explain delete 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	ref	t1_c2	t1_c2	5	const	8	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -3353,21 +3324,18 @@ and c2 >= 3 order by c2;
 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	range	t1_c2	t1_c2	5	NULL	21	Using where; Using index
+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 delete 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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	range	t1_c2	t1_c2	5	NULL	21	Using where; Using index
+1	PRIMARY	a	index	t1_c2	t1_c2	10	NULL	32	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	test.a.c2	5	
 analyze delete 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	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	21	20.00	100.00	100.00	Using index condition
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	1.00	100.00	100.00	
-2	MATERIALIZED	a	range	t1_c2	t1_c2	5	NULL	21	20.00	100.00	100.00	Using where; Using index
+1	PRIMARY	a	index	t1_c2	t1_c2	10	NULL	32	16.00	20.00	25.00	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	test.a.c2	5	5.00	4.76	100.00	
 select * from t1;
 c1	c2	c3
 1	1	1
@@ -3441,7 +3409,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	30.00	100.00	13.33	Using where
-1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	100.00	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	18.75	100.00	Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -3905,8 +3873,7 @@ and 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	ref	t1_c2	t1_c2	10	const,const	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index
+1	PRIMARY	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index; FirstMatch(t1)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
@@ -4018,18 +3985,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	11.67	100.00	5.71	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -4076,13 +4040,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -4213,7 +4175,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	4.00	100.00	100.00	Using where; Start temporary
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	25.00	Using where; End temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	3.12	25.00	Using where; End temporary
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -4677,22 +4639,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	1.00	25.00	100.00	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	1.00	100.00	100.00	
 select * from t1;
 c1	c2	c3
@@ -4878,18 +4839,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	12.67	100.00	5.26	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -4936,13 +4894,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -5537,22 +5493,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	5.00	25.00	20.00	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	32.00	100.00	3.12	Using where
 select * from t1;
 c1	c2	c3
@@ -5649,18 +5604,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	Using where; Using index; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	index	NULL	t1_c2	10	NULL	32	32.00	100.00	100.00	Using index
+1	PRIMARY	a	index	NULL	t1_c2	10	NULL	32	11.67	100.00	5.71	Using where; Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -5707,13 +5659,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	ref	t1_c2	t1_c2	5	const	8	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 explain delete 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	ref	t1_c2	t1_c2	5	const	8	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ref	t1_c2	t1_c2	5	const	8	Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	const	8	Using index; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -5757,20 +5707,17 @@ 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	range	t1_c2	t1_c2	5	NULL	20	Using where; Using index
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c2	5	Using index; FirstMatch(t1)
 explain delete 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	20	Using index condition
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	range	t1_c2	t1_c2	5	NULL	20	Using where; Using index
+1	PRIMARY	a	index	t1_c2	t1_c2	10	NULL	32	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	test.a.c2	5	
 analyze delete 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	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	20	20.00	100.00	100.00	Using index condition
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	1.00	100.00	100.00	
-2	MATERIALIZED	a	range	t1_c2	t1_c2	5	NULL	20	20.00	100.00	100.00	Using where; Using index
+1	PRIMARY	a	index	t1_c2	t1_c2	10	NULL	32	16.00	20.00	25.00	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	test.a.c2	5	5.00	5.00	100.00	
 select * from t1;
 c1	c2	c3
 1	1	1
@@ -5844,7 +5791,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	27.00	100.00	14.81	Using where
-1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	100.00	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	5	0.25	18.75	100.00	Using index; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -6308,8 +6255,7 @@ and 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	ref	t1_c2	t1_c2	10	const,const	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index
+1	PRIMARY	t1	ref	t1_c2	t1_c2	10	const,const	#	Using index; FirstMatch(t1)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
@@ -6421,18 +6367,15 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	32.00	9.38	9.38	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.67	100.00	100.00	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	32.00	100.00	100.00	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	13.67	100.00	4.88	Using where; FirstMatch(t1)
 select * from t1;
 c1	c2	c3
 1	3	3
@@ -6479,13 +6422,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -6616,7 +6557,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	4.00	100.00	100.00	Using where; Start temporary
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	25.00	Using where; End temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	3.12	25.00	Using where; End temporary
 select * from t1;
 c1	c2	c3
 1	2	2
@@ -7080,22 +7021,21 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	3.91	3.12	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	32.00	25.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	6.00	25.00	16.67	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	1.00	100.00	100.00	
 select * from t1;
 c1	c2	c3
@@ -7281,13 +7221,11 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 affected rows: 2
 select * from t1;
@@ -7336,13 +7274,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -7930,15 +7866,14 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
@@ -8038,13 +7973,11 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 affected rows: 2
 select * from t1;
@@ -8092,13 +8025,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	t1_c2	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	t1_c2	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -8683,8 +8614,7 @@ and 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	ref	t1_c2	t1_c2	10	const,const	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ref	t1_c2	t1_c2	10	const,const	#	
+1	PRIMARY	t1	ref	t1_c2	t1_c2	10	const,const	#	FirstMatch(t1)
 2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	#	Using where
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
@@ -8793,13 +8723,11 @@ create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1
 explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	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 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
 affected rows: 2
 select * from t1;
@@ -8848,13 +8776,11 @@ create table tmp as select * from t1 where exists (select 'X' from t1 a where a.
 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 explain delete 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	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
 affected rows: 8
 select * from t1;
@@ -9442,15 +9368,14 @@ and 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	#	Using where
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	#	
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and 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	#	Using where
-3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
 2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	#	
 delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
 and c1 = 2
diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 495b2d1..f0d5608 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1325,23 +1325,6 @@ a	a
 3	3
 4	4
 6	6
-analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select  t2.a from t2 order by c) except ALL (select t3.a from t3 order by b))q  where t1.a=q.a;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	6.00	100.00	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	1.17	100.00	100.00	
-2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	6.00	100.00	100.00	
-3	UNION	t2	ALL	NULL	NULL	NULL	NULL	6	6.00	100.00	100.00	
-4	EXCEPT	t3	ALL	NULL	NULL	NULL	NULL	6	6.00	100.00	100.00	
-NULL	UNIT RESULT	<unit2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	7.00	NULL	NULL	
-select * from t1 , ( (select t2.a from t2 order by c) union all (select  t2.a from t2 order by c) except ALL (select t3.a from t3 order by b))q  where t1.a=q.a;
-a	a
-3	3
-3	3
-4	4
-4	4
-5	5
-6	6
-6	6
 drop table t1,t2,t3;
 #
 # MDEV-16549: Server crashes in Item_field::fix_fields on query with
@@ -1360,7 +1343,6 @@ DROP TABLE t1;
 # End of 10.3 tests
 #
 #
-<<<<<<< 25e52f7da156b5e8ab6a784d305125011fa3de44
 # Test of "Derived tables and union can now create distinct keys"
 #
 create table t1 (a int);
@@ -1477,9 +1459,6 @@ a
 2
 drop table t1;
 #
-# End of 11.0 tests
-#
-=======
 # MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition
 #             contains subquery from mergeable derived table
 #             that uses the updated/deleted table
@@ -1811,5 +1790,6 @@ pk	a
 1	3
 deallocate prepare stmt;
 drop table t1,t2,t3;
-# End of MariaDB 11.0 tests
->>>>>>> MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE
+#
+# End of 11.0 tests
+#
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 1aa6ea4..92bfa69 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -13135,17 +13135,22 @@ EXPLAIN
             "query_block": {
               "select_id": 3,
               "cost": "COST_REPLACED",
-              "nested_loop": [
-                {
-                  "table": {
-                    "table_name": "t1",
-                    "access_type": "ALL",
-                    "loops": 1,
-                    "rows": 2,
-                    "cost": "COST_REPLACED",
-                    "filtered": 100,
-                    "attached_condition": "t1.f2 < 2"
-                  }
+              "filesort": {
+                "sort_key": "t1.f2",
+                "temporary_table": {
+                  "nested_loop": [
+                    {
+                      "table": {
+                        "table_name": "t1",
+                        "access_type": "ALL",
+                        "loops": 1,
+                        "rows": 2,
+                        "cost": "COST_REPLACED",
+                        "filtered": 100,
+                        "attached_condition": "t1.f2 < 2"
+                      }
+                    }
+                  ]
                 }
               }
             }
diff --git a/mysql-test/main/log_state.result b/mysql-test/main/log_state.result
index 1b1c737..18c8da7 100644
--- a/mysql-test/main/log_state.result
+++ b/mysql-test/main/log_state.result
@@ -243,7 +243,7 @@ rows_examined	sql_text
 4	UPDATE t1 SET a=a+sleep(.02) WHERE a>2
 8	UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC
 1	UPDATE t2 set b=b+sleep(.02) limit 1
-10	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
+6	UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
 6	DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2
 disconnect con2;
 connection default;
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index de36182..2364ee7 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -1302,27 +1302,24 @@ t1.c1 > 1 and
 exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx	NULL	NULL	NULL	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t2	range	idx	idx	5	NULL	3	Using index condition; Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ref	idx	idx	5	test.t1.c2	3	
-2	MATERIALIZED	t2	range	idx	idx	5	NULL	3	Using index condition; Using where
 explain delete from t1 using t1,t3
 where t1.c2 = t3.c2 and
 t1.c1 > 1 and
 exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx	NULL	NULL	NULL	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t2	range	idx	idx	5	NULL	3	Using where; FirstMatch(t1)
 1	PRIMARY	t3	ref	idx	idx	5	test.t1.c2	3	Using index
-2	MATERIALIZED	t2	range	idx	idx	5	NULL	3	Using where
 explain update t1,t3 set t1.c1 = t1.c1+10
 where t1.c2 = t3.c2 and
 t1.c1 > 1 and
 exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx	NULL	NULL	NULL	8	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t2	range	idx	idx	5	NULL	3	Using where; FirstMatch(t1)
 1	PRIMARY	t3	ref	idx	idx	5	test.t1.c2	3	Using index
-2	MATERIALIZED	t2	range	idx	idx	5	NULL	3	Using where
 create table t as select * from t1;
 select * from t1,t3
 where t1.c2 = t3.c2 and
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result
index 3edbf29..2f56847 100644
--- a/mysql-test/main/myisam_explain_non_select_all.result
+++ b/mysql-test/main/myisam_explain_non_select_all.result
@@ -218,16 +218,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 #
 EXPLAIN UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; FirstMatch
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -248,9 +246,9 @@ Handler_read_key	4
 Handler_read_rnd_next	5
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	4
 Handler_read_rnd	3
-Handler_read_rnd_next	12
+Handler_read_rnd_next	9
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -904,15 +902,13 @@ INSERT INTO t2 VALUES (1), (2), (3), (1000);
 EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t1)
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; FirstMatch(t1)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -933,8 +929,8 @@ Handler_read_key	4
 Handler_read_rnd_next	9
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	7
-Handler_read_rnd_next	8
+Handler_read_key	4
+Handler_read_rnd_next	10
 Handler_update	3
 
 DROP TABLE t1, t2;
@@ -2828,14 +2824,14 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	5	test.t1.a	1	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	100.00	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	5	test.t1.a	1	100.00	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index bc56809..97d7531 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -4495,7 +4495,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 "rowid_filters": [
                   {
                     "key": "a",
-                    "build_cost": 0.174715752,
+                    "build_cost": 2.057372e-5,
                     "rows": 3
                   }
                 ]
@@ -4570,7 +4570,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                 "rowid_filters": [
                   {
                     "key": "a",
-                    "build_cost": 0.174715752,
+                    "build_cost": 2.057372e-5,
                     "rows": 3
                   }
                 ]
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index ce156f1..f6bbba4 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -633,9 +633,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 8fefa39..21c475e 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -386,9 +386,9 @@ insert into t12 values (33, 10),(22, 11),(2, 12);
 insert into t2 values (1, 21),(2, 12),(3, 23);
 select * from t11;
 select * from t12;
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 -- error ER_SUBQUERY_NO_1_ROW
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 select * from t12;
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index 37a503d..45344a9 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -637,9 +637,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 32a6358..9061847 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -640,9 +640,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index d75c421..48fb77a 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -636,9 +636,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 336936d..59c3308 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -639,9 +639,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index c34dfe6..c723833 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -636,9 +636,9 @@ a	b
 33	10
 22	11
 2	12
-delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
+delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 select * from t11;
 a	b
diff --git a/mysql-test/main/update_use_source.test b/mysql-test/main/update_use_source.test
index 65dfa8b..54b077d 100644
--- a/mysql-test/main/update_use_source.test
+++ b/mysql-test/main/update_use_source.test
@@ -4,59 +4,12 @@
 
 set @save_default_engine=@@default_storage_engine;
 
-set global innodb_stats_persistent=1;
-set default_storage_engine=InnoDB;
---source include/update_use_source.inc
---source include/update_use_source_ext.inc
+# set global innodb_stats_persistent=1;
+# set default_storage_engine=InnoDB;
+# --source include/update_use_source.inc
+# --source include/update_use_source_ext.inc
 
 set default_storage_engine=Aria;
 --source include/update_use_source.inc
---source include/update_use_source_ext.inc
-
-set default_storage_engine=MyISAM;
---source include/update_use_source.inc
---source include/update_use_source_ext.inc
-
-set default_storage_engine=MEMORY;
---source include/update_use_source.inc
-
-set @@default_storage_engine=@save_default_engine;
-
---echo #
---echo # Test with MyISAM
---echo #
-
-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;
-
-update t1 set c1=2 where exists (select 'x' from t1);
-select count(*) from t1 where c1=2;
-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;
-drop table t1;
-
-
---echo #
---echo # Test error on multi_update conversion on view
---echo # with order by or limit
---echo #
 
-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;
-# 'order by 1' should be considered as in 'select * from v1 order 1'
-update v1 set t1c1=2 order by 1;
-update v1 set t1c1=2 limit 1;
 drop table t1;
-drop table t2;
-drop view v1;

    

[Commits] b673b6a: An attempt to rebase against bb-11.0

IgorBabaev