Fixed LP bug #885168.

The call of the virtual function cancel_pushed_idx_cond in the code of
the function test_if_skip_sort_order was misplaced when backporting the
fix for bug 58816.

5 files changed, 126 insertions, 1 deletions

diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index c219bf9cdc2..2814c4f10b5 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -668,3 +668,32 @@ WHERE (6, 234) IN (
 
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch= @tmp_778434;
+
+--echo #
+--echo # Bug#885168: ICP for one index + potential ORDER BY for another
+--echo #
+
+CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ;
+INSERT INTO t1 VALUES 
+  ('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'),
+  ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei');
+
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN
+SELECT * FROM t1 
+  WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
+  ORDER BY a;
+SELECT * FROM t1 
+  WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
+  ORDER BY a;
+
+SET SESSION optimizer_switch='index_condition_pushdown=on';
+EXPLAIN
+SELECT * FROM t1 
+  WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
+  ORDER BY a;
+SELECT * FROM t1 
+  WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
+  ORDER BY a;
+
+DROP TABLE t1;
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index c048b3fda94..edebc64ef20 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -623,5 +623,37 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10
 f11	f10
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch= @tmp_778434;
+#
+# Bug#885168: ICP for one index + potential ORDER BY for another
+#
+CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ;
+INSERT INTO t1 VALUES 
+('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'),
+('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN
+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 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
+SET SESSION optimizer_switch='index_condition_pushdown=on';
+EXPLAIN
+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
+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;
 set optimizer_switch=@innodb_icp_tmp;
 set storage_engine= @save_storage_engine;
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index d2b2d3ae3cf..7f314a2b9fa 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_icp.result
@@ -629,5 +629,37 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10
 f11	f10
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch= @tmp_778434;
+#
+# Bug#885168: ICP for one index + potential ORDER BY for another
+#
+CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ;
+INSERT INTO t1 VALUES 
+('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'),
+('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN
+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 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
+SET SESSION optimizer_switch='index_condition_pushdown=on';
+EXPLAIN
+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
+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;
 set storage_engine= @save_storage_engine;
 set optimizer_switch=@maria_icp_tmp;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index e31fba49eae..ede9db91442 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -627,6 +627,38 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10
 f11	f10
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch= @tmp_778434;
+#
+# Bug#885168: ICP for one index + potential ORDER BY for another
+#
+CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ;
+INSERT INTO t1 VALUES 
+('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'),
+('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN
+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 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
+SET SESSION optimizer_switch='index_condition_pushdown=on';
+EXPLAIN
+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
+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;
 drop table if exists t0, t1, t1i, t1m;
 #
 # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index bf513779dc0..f98504bbcda 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 where; Using filesort
+2	SUBQUERY	t2	ref	int_key	int_key	5	const	1	100.00	Using index condition; 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;