merge 10.2->10.3 with conflict resolutions

1 files changed, 56 insertions, 0 deletions

diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index c091ce07ff8..ad0034d60e3 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1825,6 +1825,62 @@ b	a	a	b
 9	9	10	10
 set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
 drop table t1,t2,t3;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id	a
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	a	5	NULL	100	Using where; Using index
+2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	Using index
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
+EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	const	PRIMARY,a	PRIMARY	4	const	1	
+1	SIMPLE	B	ref	a	a	5	const	1	Using index
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	a	5	NULL	100	Using where; Using index
+2	DEPENDENT SUBQUERY	A	ref	PRIMARY,a	a	5	test.t1.a	1	Using index
+2	DEPENDENT SUBQUERY	t2	ref	a,b	a	5	test.A.id	1	Using where
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
 # End of 10.1 tests
 set @@global.histogram_size=@save_histogram_size;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;