MDEV-30540 Wrong result with IN list length reaching IN_PREDICATE_CONVERSION_THRESHOLD

The problem was the mysql_derived_prepare() did not correctly set
'distinct' when creating a temporary derivated table.

Fixed by separating checking for distinct for queries with and without
UNION.

Other things:
- Fixed bug in generate_derived_keys_for_table() where we set the wrong
  bit for join_tab->keys
- Cleaned up JOIN::drop_unused_derived_keys()
- Changed TABLE::use_index() to keep unique keys and update
  share->key_parts

Author: Sergei Petrunia <sergey@mariadb.com>, monty@mariadb.org

16 files changed, 388 insertions, 155 deletions

diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 91e85ef4339..62bcfaed57b 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1315,7 +1315,7 @@ a	a
 analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select  t2.a from t2 order by c) except(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	0.50	100.00	100.00	
+1	PRIMARY	<derived2>	eq_ref	distinct_key	distinct_key	5	test.t1.a	1	0.50	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	
@@ -1325,6 +1325,23 @@ 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
@@ -1438,5 +1455,26 @@ SET @@IN_PREDICATE_CONVERSION_THRESHOLD=@@global.IN_PREDICATE_CONVERSION_THRESHO
 SET @@OPTIMIZER_SWITCH=@@global.OPTIMIZER_SWITCH;
 DROP TABLE t1, t2;
 #
+# MDEV-30540 Wrong result with IN list length reaching
+# IN_PREDICATE_CONVERSION_THRESHOLD
+#
+CREATE TABLE t1 (a INT PRIMARY KEY);
+INSERT INTO t1 SELECT seq FROM seq_1_to_30;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SET IN_PREDICATE_CONVERSION_THRESHOLD=4;
+SELECT a FROM t1 WHERE a IN ( 1, 1, 2, 194 );
+a
+1
+2
+SET IN_PREDICATE_CONVERSION_THRESHOLD=100;
+SELECT a FROM t1 WHERE a IN ( 1, 1, 2, 194 );
+a
+1
+2
+drop table t1;
+#
 # End of 11.0 tests
 #
diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test
index 5119bca4ea8..e5f01e15821 100644
--- a/mysql-test/main/derived.test
+++ b/mysql-test/main/derived.test
@@ -1130,6 +1130,11 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select
 
 select * from t1 , ( (select t2.a from t2 order by c) union all (select  t2.a from t2 order by c) except(select t3.a from t3 order by b))q  where t1.a=q.a;
 
+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;
+
+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;
+
+
 drop table t1,t2,t3;
 
 --echo #
@@ -1225,5 +1230,19 @@ SET @@OPTIMIZER_SWITCH=@@global.OPTIMIZER_SWITCH;
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-30540 Wrong result with IN list length reaching
+--echo # IN_PREDICATE_CONVERSION_THRESHOLD
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY);
+INSERT INTO t1 SELECT seq FROM seq_1_to_30;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+SET IN_PREDICATE_CONVERSION_THRESHOLD=4;
+SELECT a FROM t1 WHERE a IN ( 1, 1, 2, 194 );
+SET IN_PREDICATE_CONVERSION_THRESHOLD=100;
+SELECT a FROM t1 WHERE a IN ( 1, 1, 2, 194 );
+drop table t1;
+
+--echo #
 --echo # End of 11.0 tests
 --echo #
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 122c4c08520..ddc6505a178 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -6155,7 +6155,7 @@ explain select * from v_union,t2 where
 (v_union.a=1) and (v_union.a=t2.a) and (v_union.c<200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	40	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	40	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -6183,6 +6183,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 9,
             "rows": 40,
             "cost": "COST_REPLACED",
@@ -6363,7 +6364,7 @@ and ((v_union.c>800) or (v1.max_c>200));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
 1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	40	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	40	Using where; Using join buffer (incremental, BNL join)
 4	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
@@ -6434,6 +6435,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 180,
             "rows": 40,
             "cost": "COST_REPLACED",
@@ -6536,7 +6538,7 @@ explain select * from v2_union as v,t2 where
 ((v.a=6) or (v.a=8)) and (v.c>200) and (v.a=t2.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	6	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	6	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 4	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
@@ -6564,8 +6566,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -6739,7 +6741,7 @@ a	b	c	a	b	c	d
 explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	4	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -6765,8 +6767,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -6882,7 +6884,7 @@ a	b	c	a	b	c	d
 explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	4	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -6908,8 +6910,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -6992,7 +6994,7 @@ explain select * from v3_union as v,t2 where
 (v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	4	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -7019,8 +7021,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -7101,7 +7103,7 @@ a	b	c	a	b	c	d
 explain select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	4	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -7127,8 +7129,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -7233,7 +7235,7 @@ explain select * from v4_union as v,t2 where
 (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	4	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -7260,8 +7262,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -13711,7 +13713,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -13737,8 +13739,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -13824,7 +13826,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a=8);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -13851,6 +13853,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 9,
             "rows": 18,
             "cost": "COST_REPLACED",
@@ -13937,7 +13940,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (t2.a=8);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -13964,6 +13967,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 9,
             "rows": 18,
             "cost": "COST_REPLACED",
@@ -14052,7 +14056,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.c>200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14078,8 +14082,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -14168,7 +14172,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>110);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14194,8 +14198,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -14285,7 +14289,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14311,8 +14315,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -14407,7 +14411,7 @@ explain select * from v1,t2 where
 (v1.a=t2.a) and ((v1.c>200) or (v1.c<105));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14434,8 +14438,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -14717,7 +14721,7 @@ where
 ((d1.a<4) and (d1.c<200)));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key1	key1	5	test.t2.b	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.b	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14753,7 +14757,7 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key1"],
+          "possible_keys": ["key1", "distinct_key"],
           "key": "key1",
           "key_length": "5",
           "used_key_parts": ["b"],
@@ -14853,7 +14857,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14879,8 +14883,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -14968,7 +14972,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a=6);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -14995,6 +14999,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 9,
             "rows": 18,
             "cost": "COST_REPLACED",
@@ -15083,7 +15088,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (t2.a=6);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	distinct_key	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15110,6 +15115,7 @@ EXPLAIN
           "table": {
             "table_name": "<derived2>",
             "access_type": "ALL",
+            "possible_keys": ["distinct_key"],
             "loops": 9,
             "rows": 18,
             "cost": "COST_REPLACED",
@@ -15202,7 +15208,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.c>500);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15228,8 +15234,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -15318,7 +15324,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15344,8 +15350,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -15437,7 +15443,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15463,8 +15469,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -15559,7 +15565,7 @@ explain select * from v1,t2 where
 (v1.a=t2.a) and ((v1.c<400) or (v1.c>800));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15586,8 +15592,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -15863,7 +15869,7 @@ where
 ((d1.a>4) and (d1.c>500)));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key1	key1	5	test.t2.b	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.b	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -15899,7 +15905,7 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key1"],
+          "possible_keys": ["key1", "distinct_key"],
           "key": "key1",
           "key_length": "5",
           "used_key_parts": ["b"],
@@ -15998,7 +16004,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	18	Using where
 4	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16027,8 +16033,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16183,7 +16189,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 4	UNION	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16210,8 +16216,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16335,7 +16341,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 4	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16362,8 +16368,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16489,7 +16495,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 4	UNION	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16516,8 +16522,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16641,7 +16647,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<150);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 4	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16668,8 +16674,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16791,7 +16797,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	<derived4>	ALL	NULL	NULL	NULL	NULL	18	Using where
 4	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -16820,8 +16826,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -16975,7 +16981,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	<derived4>	ALL	NULL	NULL	NULL	NULL	18	Using where
 4	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -17005,8 +17011,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -17487,7 +17493,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.b>12) and (v1.c<450);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -17513,8 +17519,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -17610,7 +17616,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -17636,8 +17642,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -17735,7 +17741,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	EXCEPT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -17761,8 +17767,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -17861,7 +17867,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>1) and (v1.b > 12) and (v1.c>400);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	3	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.a	3	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 3	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
 4	UNION	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using temporary; Using filesort
@@ -17888,8 +17894,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.a"],
@@ -18107,7 +18113,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.b	1	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.b	1	Using where
 2	DERIVED	t3	range	i1	i1	5	NULL	2	Using index condition
 3	UNION	t3	range	i1	i1	5	NULL	1	Using index condition
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -18133,8 +18139,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["b"],
           "ref": ["test.t2.b"],
@@ -18228,7 +18234,7 @@ a	b	c	a	b	c
 explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.b	2	Using where
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	5	test.t2.b	2	Using where
 2	DERIVED	t3	range	i1	i1	5	NULL	1	Using index condition
 3	UNION	t3	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -18254,8 +18260,8 @@ EXPLAIN
         "table": {
           "table_name": "<derived2>",
           "access_type": "ref",
-          "possible_keys": ["key0"],
-          "key": "key0",
+          "possible_keys": ["key1", "distinct_key"],
+          "key": "key1",
           "key_length": "5",
           "used_key_parts": ["b"],
           "ref": ["test.t2.b"],
@@ -21357,7 +21363,7 @@ on (t1.id = t2.ro_id AND t2.flag = 1)
 group by t1.id) dt);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	20	100.00	
-1	PRIMARY	<derived3>	ref	key1	key1	4	test.t1.id	2	50.00	FirstMatch(t1)
+1	PRIMARY	<derived3>	ref	key1,distinct_key	key1	4	test.t1.id	2	50.00	FirstMatch(t1)
 3	DERIVED	t1	ALL	PRIMARY	NULL	NULL	NULL	20	100.00	Using temporary; Using filesort
 3	DERIVED	t2	ref	ro_id	ro_id	4	test.t1.id	1	100.00	Using where
 Warnings:
@@ -21470,17 +21476,21 @@ ANALYZE
       {
         "table": {
           "table_name": "<derived2>",
-          "access_type": "ALL",
+          "access_type": "const",
+          "possible_keys": ["distinct_key"],
+          "key": "distinct_key",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "ref": ["const"],
           "loops": 1,
           "r_loops": 1,
-          "rows": 4,
-          "r_rows": 2,
+          "rows": 1,
+          "r_rows": 1,
           "cost": "REPLACED",
           "r_table_time_ms": "REPLACED",
           "r_other_time_ms": "REPLACED",
           "filtered": 100,
-          "r_filtered": 50,
-          "attached_condition": "v1.a = 3",
+          "r_filtered": 100,
           "materialized": {
             "query_block": {
               "union_result": {
diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result
index 53ee25963bb..2f3b29c5049 100644
--- a/mysql-test/main/derived_opt.result
+++ b/mysql-test/main/derived_opt.result
@@ -323,7 +323,7 @@ JOIN t1 AS tc ON (tb.pk = tc.pk)
 JOIN t4 AS td ON tc.a = td.a) tu)
 limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	#	
+1	PRIMARY	<derived3>	ALL	distinct_key	NULL	NULL	NULL	#	
 1	PRIMARY	tx	eq_ref	PRIMARY	PRIMARY	4	tu.pk	#	Using index
 1	PRIMARY	ty	eq_ref	PRIMARY	PRIMARY	4	tu.pk	#	Using index
 3	DERIVED	td	system	PRIMARY	NULL	NULL	NULL	#	Using temporary
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 9569b85c234..9ea3d0f1396 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -129,7 +129,7 @@ left join
 on t1.f1=t.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	const	f2	NULL	NULL	NULL	0	Impossible ON condition
-1	PRIMARY	<derived2>	const	key1	NULL	NULL	NULL	0	Impossible ON condition
+1	PRIMARY	<derived2>	const	key0,key1	NULL	NULL	NULL	0	Impossible ON condition
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 2	DERIVED	t2	ALL	PRIMARY	NULL	NULL	NULL	3	Using temporary; Using filesort
 set statement optimizer_switch='split_materialized=off' for explain select t.f2
@@ -139,7 +139,7 @@ left join
 on t1.f1=t.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	const	f2	NULL	NULL	NULL	0	Impossible ON condition
-1	PRIMARY	<derived3>	const	key1	NULL	NULL	NULL	0	Impossible ON condition
+1	PRIMARY	<derived3>	const	key0,key1	NULL	NULL	NULL	0	Impossible ON condition
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 3	DERIVED	t2	index	NULL	PRIMARY	4	NULL	3	
 drop view v1;
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index e7190e29f3f..aa7519ae750 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -1661,7 +1661,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	1	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	10	test.t1.a,test.t1.b	1	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	
 4	UNION	t3	ALL	NULL	NULL	NULL	NULL	4	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
diff --git a/mysql-test/main/skr.result b/mysql-test/main/skr.result
new file mode 100644
index 00000000000..291377573bc
--- /dev/null
+++ b/mysql-test/main/skr.result
@@ -0,0 +1,54 @@
+#
+# MDEV-23406: query with mutually recursive CTEs when big_tables=1
+#
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
+create table  folks(id int, name char(32), dob date, father int, mother int);
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+drop table folks;
+set big_tables=@save_big_tables;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
diff --git a/mysql-test/main/skr.test b/mysql-test/main/skr.test
new file mode 100644
index 00000000000..3094faff696
--- /dev/null
+++ b/mysql-test/main/skr.test
@@ -0,0 +1,56 @@
+--source include/default_optimizer_switch.inc
+
+--echo #
+--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1
+--echo #
+
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+
+create table  folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+let q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+eval $q;
+drop table folks;
+
+set big_tables=@save_big_tables;
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 382e4044081..e0d06298f88 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -1539,7 +1539,7 @@ EXPLAIN
 SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	4	const	1	Using where
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1549,7 +1549,7 @@ EXPLAIN
 SELECT ( 5 ) IN ( SELECT * FROM v1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	4	const	1	Using where
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1560,7 +1560,7 @@ EXPLAIN
 SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	5	const	1	Using where
 3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1580,7 +1580,7 @@ EXPLAIN
 SELECT ( 5 ) IN ( SELECT * FROM v2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	index_subquery	NULL	distinct_key	5	const	2	
 3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1592,7 +1592,7 @@ EXPLAIN
 SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	4	const	1	Using where
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1602,7 +1602,7 @@ EXPLAIN
 SELECT ( 5 ) IN ( SELECT * FROM v1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	4	const	1	Using where
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1613,7 +1613,7 @@ EXPLAIN
 SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	5	const	1	Using where
 3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1623,7 +1623,7 @@ EXPLAIN
 SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	const	NULL	distinct_key	5	const	1	Using where
 3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -1633,7 +1633,7 @@ EXPLAIN
 SELECT ( 5 ) IN ( SELECT * FROM v2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	<derived3>	index_subquery	NULL	distinct_key	5	const	2	
 3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -2797,7 +2797,7 @@ EXPLAIN
 SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abc',1), ('def', 2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
-1	PRIMARY	<derived3>	ref	key1	key1	4	test.t2.b	1	Using where; FirstMatch(t2)
+1	PRIMARY	<derived3>	ref	key1,distinct_key	key1	4	test.t2.b	1	Using where; FirstMatch(t2)
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 set names default;
 set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold;
diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result
index 378e8dafd4b..bcb9cd18c64 100644
--- a/mysql-test/main/subselect_extra.result
+++ b/mysql-test/main/subselect_extra.result
@@ -413,7 +413,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	1	FirstMatch(t1)
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	10	test.t1.a,test.t1.b	1	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	
 4	UNION	t3	ALL	NULL	NULL	NULL	NULL	4	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result
index e4d38719ff2..ead53edf311 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.result
+++ b/mysql-test/main/subselect_extra_no_semijoin.result
@@ -415,7 +415,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	10	func,func	1	Using where
+2	DEPENDENT SUBQUERY	<derived3>	unique_subquery	distinct_key	distinct_key	10	func,func	1	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	
 4	UNION	t3	ALL	NULL	NULL	NULL	NULL	4	
 NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index 34056d0c696..2f413f720de 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -308,7 +308,7 @@ SELECT * FROM federated.t1 WHERE id >= 5) t
 WHERE federated.t3.name=t.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
-1	PRIMARY	<derived2>	ref	key1	key1	18	federated.t3.name	1	
+1	PRIMARY	<derived2>	ref	key1,distinct_key	key1	18	federated.t3.name	1	
 2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 #
 # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 1fa5179875c..22dd7734aea 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -674,7 +674,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
 {
   SELECT_LEX_UNIT *unit= derived->get_unit();
   SELECT_LEX *first_select;
-  bool res= FALSE, keep_row_order;
+  bool res= FALSE, keep_row_order, distinct;
   DBUG_ENTER("mysql_derived_prepare");
   DBUG_PRINT("enter", ("unit: %p  table_list: %p  alias: '%s'",
                        unit, derived, derived->alias.str));
@@ -873,17 +873,23 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
     Temp table is created so that it honors if UNION without ALL is to be
     processed
 
-    As 'distinct' parameter we pass unit->distinct, which tells us if
-    the values should be uniq.
+    We pass as 'distinct' parameter in any of the above cases
+
+    1) It is an UNION and the last part of an union is distinct (as
+       thus the final temporary table should not contain duplicates).
+    2) It is not an UNION and the unit->distinct flag is set. This is the
+       case for WHERE A IN (...).
+
     Note that the underlying query will also control distinct condition.
-    Correct test of distinct underlying query will be is_unit_op &&
-    !unit->union_distinct->next_select() (i.e. it is union and last distinct
-    SELECT is last SELECT of UNION).
   */
   thd->create_tmp_table_for_derived= TRUE;
+  distinct= (unit->first_select()->next_select() ?
+             unit->union_distinct && !unit->union_distinct->next_select() :
+             unit->distinct);
+
   if (!(derived->table) &&
       derived->derived_result->create_result_table(thd, &unit->types,
-                                                   unit->distinct,
+                                                   distinct,
                                                    (first_select->options |
                                                    thd->variables.option_bits |
                                                    TMP_TABLE_ALL_COLUMNS),
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 38d492eb49d..3054681987e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -14004,7 +14004,7 @@ bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
                                (uchar *) &first_keyuse,
                                FALSE))
           return TRUE;
-        table->reginfo.join_tab->keys.set_bit(table->s->keys);
+        table->reginfo.join_tab->keys.set_bit(table->s->keys - 1);
         tab= table->reginfo.join_tab;
         for (uint i=0; i < parts; i++)
           tab->key_dependent|= save_first_keyuse[i].used_tables;
@@ -14116,23 +14116,42 @@ void JOIN::drop_unused_derived_keys()
   {
     
     TABLE *tmp_tbl= tab->table;
-    if (!tmp_tbl)
+    /*
+      Skip placeholders and already created tables (we cannot change keys
+      for created tables)
+    */
+    if (!tmp_tbl || tmp_tbl->is_created())
       continue;
     if (!tmp_tbl->pos_in_table_list->is_materialized_derived())
       continue;
-    if (tmp_tbl->max_keys > 1 && !tab->is_ref_for_hash_join())
-      tmp_tbl->use_index(tab->ref.key);
-    if (tmp_tbl->s->keys)
+
+    /*
+      tmp_tbl->max_keys is the number of keys pre-allocated in
+      TABLE::alloc_keys().  Can be 0 if alloc_keys() was not called.
+
+      tmp_tbl->s->keys is number of keys defined for the table.
+      Normally 0 or 1 (= unique key)
+    */
+
+    if (likely(tmp_tbl->s->keys) && tab->ref.key >= 0 &&
+        !tab->is_ref_for_hash_join())
     {
-      if (tab->ref.key >= 0 && tab->ref.key < MAX_KEY)
-        tab->ref.key= 0;
-      else
+      if (tmp_tbl->s->keys > 1)
       {
-        tmp_tbl->s->keys= 0;
-        tmp_tbl->s->uniques= 0;
+        /* remove all keys except the chosen one and unique keys */
+        tmp_tbl->use_index(tab->ref.key, &tab->keys);
       }
+      /*
+        We dropped all keys except the chosen one and unique keys.
+        The choosen one is stored as the first key (number 0).
+      */
+      tab->ref.key= 0;
+    }
+    else if (tmp_tbl->s->keys)
+    {
+      /* The query cannot use keys, remove all non unique keys */
+      tmp_tbl->use_index(-1, &tab->keys);
     }
-    tab->keys= (key_map) (tmp_tbl->s->keys || tmp_tbl->s->uniques ? 1 : 0);
   }
 }
 
diff --git a/sql/table.cc b/sql/table.cc
index e0189401906..9ba9817d587 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8208,7 +8208,7 @@ void TABLE::restore_blob_values(String *blob_storage)
   @param key_count  number of keys to allocate additionally
 
   @details
-  The function allocates memory  to fit additionally 'key_count' keys 
+  The function allocates memory to fit additionally 'key_count' keys
   for this table.
 
   @return FALSE   space was successfully allocated
@@ -8472,28 +8472,59 @@ bool TABLE::add_tmp_key(uint key, uint key_parts,
 
 /*
   @brief
-  Drop all indexes except specified one.
+  Drop all indexes except specified one and optionally unique keys.
 
-  @param key_to_save the key to save
+  @param  key_to_save   The key to save
+  @param  map_to_update Bitmap showing some of the table's keys. Update it
+                        to show the same keys, if they are not dropped.
+  @param  unique_keys   Keep unique keys
 
   @details
-  Drop all indexes on this table except 'key_to_save'. The saved key becomes
-  key #0. Memory occupied by key parts of dropped keys are freed.
-  If the 'key_to_save' is negative then all keys are freed.
+  Drop all indexes on this table except 'key_to_save' and unique keys.
+
+  The saved key becomes key #0. If key_to_save=-1 then only unique keys
+  remain.
 */
 
-void TABLE::use_index(int key_to_save)
+void TABLE::use_index(int key_to_save, key_map *map_to_update)
 {
-  uint i= 1;
   DBUG_ASSERT(!created && key_to_save < (int)s->keys);
-  if (key_to_save >= 0)
-    /* Save the given key. */
-    memmove(key_info, key_info + key_to_save, sizeof(KEY));
-  else
-    /* Drop all keys; */
-    i= 0;
+  uint saved_keys= 0, key_parts= 0;
+  key_map new_bitmap;
+  new_bitmap.clear_all();
+
+  /*
+    If we have key_to_save, move it to be key#0.
+  */
+  if (key_to_save != -1)
+  {
+    new_bitmap.set_bit(saved_keys);
+
+    KEY tmp_buff= key_info[saved_keys];
+    key_info[saved_keys]= key_info[key_to_save];
+    key_info[key_to_save]= tmp_buff;
+    key_parts= key_info[saved_keys].user_defined_key_parts;
+    saved_keys++;
+  }
 
-  s->keys= i;
+  /*
+    Now, move all unique keys to the front.
+  */
+  for (uint i= saved_keys; i < s->keys; i++)
+  {
+    if (key_info[i].flags & HA_NOSAME)
+    {
+      if (map_to_update->is_set(i))
+        new_bitmap.set_bit(saved_keys);
+      if (i != saved_keys)
+        key_info[saved_keys]= key_info[i];
+      key_parts+= key_info[saved_keys].user_defined_key_parts;
+      saved_keys++;
+    }
+  }
+  *map_to_update= new_bitmap;
+  s->keys= saved_keys;
+  s->key_parts= s->ext_key_parts= key_parts;
 }
 
 /*
diff --git a/sql/table.h b/sql/table.h
index 13b8b1a9dc4..d9c1231db6a 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1694,7 +1694,7 @@ public:
                    bool unique);
   void create_key_part_by_field(KEY_PART_INFO *key_part_info,
                                 Field *field, uint fieldnr);
-  void use_index(int key_to_save);
+  void use_index(int key_to_save, key_map *map_to_update);
   void set_table_map(table_map map_arg, uint tablenr_arg)
   {
     map= map_arg;