MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding

- Make JOIN::const_key_parts include keyparts for which 
  the WHERE clause has an equality in form 
  "t.key_part=reference_outside_this_select"
- This allows to avoid filesort'ing in some cases (and also 
  avoid a difficult choice between using filesort or using an index)

1 files changed, 33 insertions, 1 deletions

diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index e9e1ccd0bf6..159b1d4be81 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -313,7 +313,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS
 (SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY,d	d	2	func	1	Using where
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY,d	PRIMARY	1	func	1	Using where
 3	DEPENDENT SUBQUERY	t2	index	NULL	d	2	NULL	1	Using where; Using index
 DROP TABLE t2;
 CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB;
@@ -495,3 +495,35 @@ HAVING SQ2_alias1 . col_int_key >= 7
 1
 drop table t1;
 set optimizer_switch=@subselect_innodb_tmp;
+#
+# MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
+#
+create table t1(a int) engine=innodb;
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(
+id int primary key,
+key1 int,
+col1 int,
+key(key1)
+) engine=innodb;
+insert into t2 
+select 
+A.a + B.a*10 + C.a*100 + D.a* 1000, 
+A.a + 10*B.a, 
+123456 
+from t1 A, t1 B, t1 C, t1 D;
+# Table tsubq:
+#   - must use 'ref' (not 'index'), and must not use 'Using filesort'
+#   - shows a bad estimate for 'rows' (but I'm not sure if one can do better w/o histograms)
+explain select 
+(SELECT 
+concat(id, '-', key1, '-', col1)
+FROM t2
+WHERE t2.key1 = t1.a
+ORDER BY t2.id ASC LIMIT 1)
+from 
+t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+2	DEPENDENT SUBQUERY	t2	ref	key1	key1	5	test.t1.a	1	Using where
+drop table t1, t2;