MDEV-6263: Wrong result when using IN subquery with order by

- When the optimizer chose LooseScan, make_join_readinfo() should
  use the index that was chosen for LooseScan, and should not try 
  to find a better (shortest) index.

4 files changed, 119 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 2975176c64a..43ba6ead575 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1148,5 +1148,35 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t4	index	NULL	PRIMARY	59	NULL	2	Using where; Using index; End temporary
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index ff97882c469..c45ab02a646 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1163,6 +1163,36 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (incremental, BNL join)
 1	PRIMARY	t4	hash_index	NULL	#hash#$hj:PRIMARY	55:59	test.t3.t3_c	2	Using where; End temporary; Using join buffer (incremental, BNLH join)
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index f4d7d986a9d..0ff366b0d7a 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1150,6 +1150,36 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t4	index	NULL	PRIMARY	59	NULL	2	Using where; Using index; End temporary
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-6263: Wrong result when using IN subquery with order by
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+nombre varchar(255) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+CREATE TABLE t2 (
+id_algo int(11) NOT NULL,
+id_agente int(11) NOT NULL,
+PRIMARY KEY (id_algo,id_agente), 
+KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+id	nombre
+1	row 1
+2	row 2
+3	row 3
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 9d9e7848415..a82baf095c1 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1291,5 +1291,34 @@ SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_p
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # MDEV-6263: Wrong result when using IN subquery with order by
+--echo #
+CREATE TABLE t1 (
+  id int(11) NOT NULL,
+  nombre varchar(255) NOT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 (id, nombre) VALUES
+(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
+(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
+
+CREATE TABLE t2 (
+  id_algo int(11) NOT NULL,
+  id_agente int(11) NOT NULL,
+  PRIMARY KEY (id_algo,id_agente), 
+  KEY another_data (id_agente)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO t2 (id_algo, id_agente) VALUES
+(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
+
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC;
+
+SELECT * FROM t1 WHERE id in (select distinct id_agente from t2);
+
+DROP TABLE t1, t2;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;