Backported the fix and the test case for bug #58816 from mysql-5.6 code line.

4 files changed, 70 insertions, 1 deletions

diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 32bbccb5e80..8293b3fbf83 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -439,6 +439,29 @@ pk	i
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1, t2;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+set @save_optimizer_switch= @@optimizer_switch;
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+set optimizer_switch= @save_optimizer_switch;
+#
 # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
 #
 CREATE TABLE t1 ( f11 int) ;
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index 83d1a2b93bc..1ae227fd283 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_icp.result
@@ -445,6 +445,29 @@ pk	i
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1, t2;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+set @save_optimizer_switch= @@optimizer_switch;
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+set optimizer_switch= @save_optimizer_switch;
+#
 # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
 #
 CREATE TABLE t1 ( f11 int) ;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 249b5a73f8b..cd51b0b9c94 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -443,6 +443,29 @@ pk	i
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1, t2;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+set @save_optimizer_switch= @@optimizer_switch;
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+set optimizer_switch= @save_optimizer_switch;
+#
 # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
 #
 CREATE TABLE t1 ( f11 int) ;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index f98504bbcda..bf513779dc0 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -350,7 +350,7 @@ GROUP BY t2.pk
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
-2	SUBQUERY	t2	ref	int_key	int_key	5	const	1	100.00	Using index condition; Using where; Using filesort
+2	SUBQUERY	t2	ref	int_key	int_key	5	const	1	100.00	Using where; Using filesort
 Warnings:
 Note	1003	select min(1) AS `MIN(t1.pk)` from `test`.`t1` where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`) group by `test`.`t2`.`pk`)
 DROP TABLE t1, t2;