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.

5 files changed, 136 insertions, 12 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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1f591624457..aee6905c5a6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10270,20 +10270,25 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
 	  else if (!table->covering_keys.is_clear_all() &&
 		   !(tab->select && tab->select->quick))
 	  {					// Only read index tree
+            if (tab->loosescan_match_tab)
+              tab->index= tab->loosescan_key;
+            else 
+            {
 #ifdef BAD_OPTIMIZATION
-	    /*
-              It has turned out that the below change, while speeding things
-              up for disk-bound loads, slows them down for cases when the data
-              is in disk cache (see BUG#35850):
-              See bug #26447: "Using the clustered index for a table scan
-              is always faster than using a secondary index".
-            */
-            if (table->s->primary_key != MAX_KEY &&
-                table->file->primary_key_is_clustered())
-              tab->index= table->s->primary_key;
-            else
+              /*
+                It has turned out that the below change, while speeding things
+                up for disk-bound loads, slows them down for cases when the data
+                is in disk cache (see BUG#35850):
+                See bug #26447: "Using the clustered index for a table scan
+                is always faster than using a secondary index".
+              */
+              if (table->s->primary_key != MAX_KEY &&
+                  table->file->primary_key_is_clustered())
+                tab->index= table->s->primary_key;
+              else
 #endif
-              tab->index=find_shortest_key(table, & table->covering_keys);
+                tab->index=find_shortest_key(table, & table->covering_keys);
+            }
 	    tab->read_first_record= join_read_first;
             /* Read with index_first / index_next */
 	    tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT;