Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 144 insertions, 72 deletions

diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 9fbf36e3..62709070 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -365,7 +365,7 @@ patient_uq	clinic_uq
 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t7	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index
-1	PRIMARY	t6	ALL	i1	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t6	ref	i1	i1	5	test.t7.uq	1	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where `test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`
@@ -660,7 +660,18 @@ a	b
 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);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
+select * from t11;
+a	b
+0	10
+1	11
+select * from t12;
+a	b
+33	10
+22	11
+delete from t11;
+delete from t12;
+insert into t11 values (0, 10),(1, 11),(2, 12);
+insert into t12 values (33, 10),(22, 11),(2, 12);
 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 t2 where t11.a = t2.a);
@@ -954,6 +965,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -1485,6 +1499,9 @@ drop table if exists t1;
 (SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
 a
 1
+#
+# IN subselect optimization test
+#
 create table t1 (a int not null, b int, primary key (a));
 create table t2 (a int not null, primary key (a));
 create table t3 (a int not null, b int, primary key (a));
@@ -1508,21 +1525,21 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	4	100.00	Using index
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	Using where
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	100.00	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <> 30
 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 a
 2
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	4	100.00	Using index
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	
-1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where; Using index; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	4	100.00	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using index
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a`
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a`
 drop table t1, t2, t3;
 create table t1 (a int, b int, index a (a,b));
 create table t2 (a int, index a (a));
@@ -1531,42 +1548,48 @@ insert into t1 values (1,10), (2,20), (3,30), (4,40);
 create table t0(a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 insert into t1
-select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
+select rand()*100000+200,rand(1)*100000 from t0 A, t0 B, t0 C, t0 D;
 insert into t2 values (2), (3), (4), (5);
 insert into t3 values (10,3), (20,4), (30,5);
-select * from t2 where t2.a in (select a from t1);
-a
-2
-3
-4
 explain extended select * from t2 where t2.a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	0.99	Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a`
-select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+select * from t2 where t2.a in (select a from t1);
 a
 2
+3
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	0.99	Using where; Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30
-select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 a
 2
-3
+4
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
 1	PRIMARY	t3	range	a	a	5	NULL	3	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	0.29	Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where `test`.`t1`.`b` = `test`.`t3`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a`
+select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+a
+2
+3
 insert into t1 values (3,31);
+explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	0.99	Using where; Using index; FirstMatch(t2)
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 a
 2
@@ -1576,12 +1599,6 @@ select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31
 a
 2
 4
-explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; FirstMatch(t2)
-Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30
 drop table t0, t1, t2, t3;
 create table t1 (a int, b int);
 create table t2 (a int, b int);
@@ -1643,6 +1660,9 @@ Note	1003	(select 'tttt' AS `s1` from dual)
 s1
 tttt
 drop table t1;
+#
+# IN optimisation test results
+#
 create table t1 (s1 char(5), index s1(s1));
 create table t2 (s1 char(5), index s1(s1));
 insert into t1 values ('a1'),('a2'),('a3');
@@ -2476,20 +2496,23 @@ a
 1
 3
 DROP TABLE t1;
+#
+# SELECT(EXISTS * ...)optimisation
+#
 create table t1 (a int, b int);
-insert into t1 values (1,2),(3,4);
-select * from t1 up where exists (select * from t1 where t1.a=up.a);
-a	b
-1	2
-3	4
-explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
+insert into t1 values (1,2),(3,4),(5,6),(7,8);
+insert into t1 select seq,seq from seq_20_to_40;
+select sum(a+b) from t1 up where exists (select * from t1 where t1.a=up.a);
+sum(a+b)
+1296
+explain extended select sum(a+b) from t1 up where exists (select * from t1 where t1.a=up.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	up	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	up	ALL	NULL	NULL	NULL	NULL	25	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	25	100.00	
 Warnings:
 Note	1276	Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` semi join (`test`.`t1`) where 1
+Note	1003	select sum(`test`.`up`.`a` + `test`.`up`.`b`) AS `sum(a+b)` from `test`.`t1` `up` semi join (`test`.`t1`) where 1
 drop table t1;
 CREATE TABLE t1 (t1_a int);
 INSERT INTO t1 VALUES (1);
@@ -3157,9 +3180,13 @@ retailerID	statusID	changed
 0048	1	2006-01-06 12:37:50
 0059	1	2006-01-06 12:37:50
 drop table t1;
+#
+# Bug#21180 Subselect with index for both WHERE and ORDER BY
+#           produces empty result
+#
 create table t1(a int, primary key (a));
 insert into t1 values (10);
-create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+create table t2 (a int primary key, b varchar(32), c int, unique key cb(c, b));
 insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
 insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999');
 analyze table t1;
@@ -3172,7 +3199,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
-2	SUBQUERY	t2	range	b	b	40	NULL	3	Using where
+2	SUBQUERY	t2	range	cb	cb	40	NULL	3	Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
@@ -3184,7 +3211,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
-2	SUBQUERY	t2	range	b	b	40	NULL	3	Using index condition
+2	SUBQUERY	t2	range	cb	cb	40	NULL	3	Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
@@ -4279,8 +4306,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3);
 SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
 id	st
-3	FL
 1	GA
+3	FL
 7	FL
 SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4381,6 +4408,9 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
 0
 0
 DROP TABLE t1, t2;
+#
+# Bug#28076 inconsistent binary/varbinary comparison
+#
 CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
 INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
 SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
@@ -4442,8 +4472,8 @@ CREATE INDEX I1 ON t1 (a);
 CREATE INDEX I2 ON t1 (b);
 EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	I2	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t1	ref	I1	I1	2	test.t1.b	2	Using where; Using index; FirstMatch(t1)
+1	PRIMARY	t1	index	I1	I1	2	NULL	2	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	I2	I2	13	test.t1.a	1	Using index condition
 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
 a	b
 CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4452,15 +4482,15 @@ CREATE INDEX I1 ON t2 (a);
 CREATE INDEX I2 ON t2 (b);
 EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	I2	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ref	I1	I1	4	test.t2.b	2	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	I1	I1	4	NULL	2	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	I2	I2	13	test.t2.a	1	Using index condition
 SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
 a	b
 EXPLAIN
 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	I2	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t1	ref	I1	I1	2	test.t1.b	2	Using where; Using index; FirstMatch(t1)
+1	PRIMARY	t1	index	I1	I1	2	NULL	2	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	I2	I2	13	test.t1.a	1	Using index condition
 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 a	b
 DROP TABLE t1,t2;
@@ -4490,10 +4520,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
-INSERT INTO t1 VALUES (1),(2);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4501,19 +4534,18 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1
+Note	1003	select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a`
 EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5752,7 +5784,8 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
+insert into t2 select seq from seq_3_to_500;
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5763,7 +5796,7 @@ WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	502	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
@@ -5772,9 +5805,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	11	Using index; FirstMatch(t1)
 DROP TABLE t1,t2;
 #
 # BUG#752992: Wrong results for a subquery with 'semijoin=on'
@@ -5791,9 +5823,9 @@ SET @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=0;
 EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
-1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(it)
+1	PRIMARY	it	index	PRIMARY	PRIMARY	4	NULL	3	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.it.pk	1	
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(t1)
 SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 pk	i
 11	0
@@ -6134,8 +6166,7 @@ WHERE col_varchar_nokey IN
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index
+1	PRIMARY	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index; FirstMatch(ot)
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
@@ -6147,8 +6178,7 @@ WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
-2	MATERIALIZED	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index
+1	PRIMARY	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index; FirstMatch(ot)
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
@@ -6718,7 +6748,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer
 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	a	4	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	4	func	2	Using index
+2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	4	func	1	Using index
 SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1);
 a
 2009-01-01
@@ -6923,7 +6953,7 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	alias1	index	a	a	19	NULL	11	Using where; Using index
-1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	2	Using index
+1	PRIMARY	alias2	ref	a	a	19	test.alias1.a	1	Using index
 1	PRIMARY	alias3	index	NULL	a	19	NULL	11	Using index; Using join buffer (flat, BNL join)
 2	SUBQUERY	t1	index_subquery	a	a	19	const	1	Using index; Using where
 SELECT MAX( alias2.a )
@@ -7082,7 +7112,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using index
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	1	Using index
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 set @tmp_mdev410=@@global.userstat;
 set global userstat=on;
@@ -7114,7 +7144,7 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
-2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	1	Using index
 DROP TABLE t1,t2;
 #
 # MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
@@ -7148,7 +7178,7 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
-2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	1	Using index
 DROP TABLE t1,t2;
 #
 # MDEV-5991: crash in Item_field::used_tables
@@ -7561,3 +7591,45 @@ drop table t1, t2, t3;
 #
 # End of 10.6 tests
 #
+#
+# MDEV-32656: ASAN errors in base_list_iterator::next /
+# setup_table_map upon 2nd execution of PS
+# (10.10 part)
+#
+CREATE TABLE t1 (id BIGINT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (b INT);
+INSERT INTO t3 VALUES (3),(4);
+PREPARE stmt FROM "UPDATE t2 SET a = 1 WHERE (select 1 where ('e','e') IN (SELECT v1.id, v1.id FROM v1 JOIN t3)) <> 0";
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+PREPARE stmt FROM "UPDATE t2 SET a = 1 WHERE ('e') IN (SELECT v1.id FROM v1 JOIN t3)";
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+PREPARE stmt FROM "UPDATE t2 SET a = 1 WHERE ('e','e') IN (SELECT v1.id, v1.id FROM v1 JOIN t3)";
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+EXECUTE stmt;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+Warning	1292	Truncated incorrect DECIMAL value: 'e'
+DROP VIEW v1;
+DROP TABLE t1, t2, t3;
+#
+# End of 10.10 tests
+#