Backported the test case for bug 59186 from mysql-5.6 code line.

The bug was fixed by the patch for LP bug 694092 that was
applied earlier to the mariadb-5.3 code.

1 files changed, 29 insertions, 0 deletions

diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index aacb129b5de..7f99dd4809b 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -485,6 +485,35 @@ AND 6=7));
 t1_int	t1_time
 DROP TABLE t1,t2;
 #
+# Bug#59186: Wrong results of join when ICP is enabled
+#            (fixed by the patch for LP bug #694092)
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+c2 VARCHAR(6) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+EXPLAIN
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using join buffer (flat, BNL join)
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+c2
+DROP TABLE t1, t2;
+#
 # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
 #
 CREATE TABLE t1 ( f11 int) ;