MDEV-8289: Semijoin inflates number of rows in query result

- Make semi-join optimizer not to choose LooseScan
  when 1) the index is not covered and 2) full index
  scan will be required.

- Make sure that the code in make_join_select() that may change
  full index scan into a range scan is not invoked when the table
  uses full scan.

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 9a5da710a4c..e6259a3979b 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1262,5 +1262,38 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	T2_0_	ref	FK_T2_T1Id	FK_T2_T1Id	8	test.T2_1_.t1idref	1	Using index; End temporary
 drop table t3,t2,t1;
 set optimizer_search_depth=@tmp7474;
+#
+#
+#
+CREATE TABLE t1 (
+id int(16) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE t2 (
+id int(16) NOT NULL AUTO_INCREMENT,
+t3_id int(16) NOT NULL DEFAULT '0',
+t1_id int(16) NOT NULL DEFAULT '0',
+PRIMARY KEY (id),
+KEY t3_idx (t3_id),
+KEY t1_idx (t1_id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+CREATE TABLE t3 (
+id int(16) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+INSERT INTO t3 VALUES (1);
+INSERT INTO t2 VALUES (1, 1, 1);
+INSERT INTO t2 VALUES (2, 1, 2);
+INSERT INTO t2 VALUES (3, 1, 2);
+INSERT INTO t2 VALUES (4, 1, 1);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (2);
+SELECT * FROM t1 WHERE t1.id IN (             
+SELECT t2.t1_id FROM t3 JOIN t2 ON t3.id = t2.t3_id WHERE t3.id = 1
+);
+id
+1
+2
+drop table t1,t2,t3;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;