Fixed LP bug #886145.

The bug happened because in some cases the function JOIN::exec
did not save the value of TABLE::pre_idx_push_select_cond in
TABLE::select->pre_idx_push_select_cond for the sort table.

Noticed and fixed a bug in the function make_cond_remainder
that builds the remainder condition after extraction of an index
pushdown condition from the where condition. The code
erroneously assumed that the function make_cond_for_table left
the value of ICP_COND_USES_INDEX_ONLY in sub-condition markers.
Adjusted many result files from the regression test suite
after this fix .

1 files changed, 33 insertions, 2 deletions

diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index edebc64ef20..9bfa3990970 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -506,7 +506,7 @@ 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	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Using where
 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
@@ -648,12 +648,43 @@ SELECT * FROM t1
 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
   ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	b	b	13	NULL	2	Using index condition; Rowid-ordered scan; Using filesort
+1	SIMPLE	t1	range	b	b	13	NULL	2	Using index condition; Using where; Rowid-ordered scan; Using filesort
 SELECT * FROM t1 
 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
   ORDER BY a;
 a	b
 d	xdmbdkpjda
 DROP TABLE t1;
+#
+# Bug#886145: join with ICP + ORDER BY  
+#
+CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b));
+INSERT INTO t1 VALUES (1,4,'Ill');
+CREATE TABLE t2 (a varchar(1024), KEY (a(512)));
+INSERT INTO t2 VALUES
+('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 
+HAVING t1.c != 5 ORDER BY t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	515	test.t1.a	1	Using where
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+HAVING t1.c != 5 ORDER BY t1.c;
+b	c
+1	4
+SET SESSION optimizer_switch='index_condition_pushdown=on';
+EXPLAIN
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+HAVING t1.c != 5 ORDER BY t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	515	test.t1.a	1	Using where
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+HAVING t1.c != 5 ORDER BY t1.c;
+b	c
+1	4
+DROP TABLE t1,t2;
 set optimizer_switch=@innodb_icp_tmp;
 set storage_engine= @save_storage_engine;