[Commits] 8a9532f: MDEV-16188: Fixed several problems and bugs.

IgorBabaev igor at mariadb.com
Thu Jan 17 21:29:13 EET 2019


revision-id: 8a9532f2cc1a8eeb53ff04ca2c28b4756afc845b (mariadb-10.3.6-102-g8a9532f)
parent(s): 5c4b7e6878126cc498f87391875807c68739914f
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-01-17 11:29:13 -0800
message:

MDEV-16188: Fixed several problems and bugs.

1. Completely re-wrote the function that prunes range filter candidates.
The function now properly takes into account filter indexes that overlap.
2. Fixed bugs that calculate the cost of using filter. Ensured that the
gain promised by usage of a filter would never be greater than the cost
of accessing a table without it.
3. Fixed a bug that led to an improper detection of overlapping indexes.
4. Fixed a few other bugs.
5. Adjusted test cases and their output.

---
 mysql-test/main/index_intersect.result        |   2 +
 mysql-test/main/index_intersect.test          |   2 +
 mysql-test/main/index_intersect_innodb.result |   2 +
 mysql-test/main/index_merge_innodb.result     |   1 +
 mysql-test/main/index_merge_innodb.test       |   1 +
 mysql-test/main/index_merge_myisam.result     |  25 +-
 mysql-test/main/index_merge_myisam.test       |  25 +-
 mysql-test/main/join_outer_innodb.result      |   8 +-
 mysql-test/main/key_cache.result              |   6 +-
 mysql-test/main/mrr_icp_extra.result          |   1 +
 mysql-test/main/mrr_icp_extra.test            |   1 +
 mysql-test/main/null_key.result               |   2 +-
 mysql-test/main/order_by.result               |   2 +-
 mysql-test/main/partition_innodb.result       |   2 +
 mysql-test/main/partition_innodb.test         |   2 +
 mysql-test/main/range_mrr_icp.result          |  21 +-
 mysql-test/main/range_mrr_icp.test            |   1 +
 mysql-test/main/rowid_filter.result           | 675 +++++++++++++++++++++++++-
 mysql-test/main/rowid_filter.test             |  78 +++
 mysql-test/main/select.result                 |  13 +-
 mysql-test/main/select.test                   |   2 +
 mysql-test/main/select_jcl6.result            |  13 +-
 mysql-test/main/select_pkeycache.result       |  13 +-
 mysql-test/main/subselect2.result             |   2 +-
 mysql-test/main/subselect_mat_cost.result     |   4 +-
 mysql-test/main/subselect_mat_cost.test       |   4 +
 sql/handler.h                                 |   2 +
 sql/opt_range.cc                              |   2 +
 sql/rowid_filter.cc                           | 269 +++++-----
 sql/rowid_filter.h                            |  45 +-
 sql/sql_select.cc                             |  59 ++-
 sql/structs.h                                 |   2 +-
 sql/table.cc                                  |  35 +-
 sql/table.h                                   |  21 +-
 storage/innobase/handler/ha_innodb.cc         |   3 +-
 storage/myisam/ha_myisam.cc                   |   3 +-
 36 files changed, 1068 insertions(+), 281 deletions(-)

diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result
index 04484c7..bb2478c 100644
--- a/mysql-test/main/index_intersect.result
+++ b/mysql-test/main/index_intersect.result
@@ -38,6 +38,7 @@ SELECT COUNT(*) FROM CountryLanguage;
 COUNT(*)
 984
 CREATE INDEX Name ON City(Name);
+SET SESSION optimizer_switch='rowid_filter=off';
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
 SELECT COUNT(*) FROM City;
 COUNT(*)
@@ -972,3 +973,4 @@ f1	f4	f5
 998	a	0
 DROP TABLE t1;
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
+SET SESSION optimizer_switch='rowid_filter=default';
diff --git a/mysql-test/main/index_intersect.test b/mysql-test/main/index_intersect.test
index 51a3b29..c77eccc 100644
--- a/mysql-test/main/index_intersect.test
+++ b/mysql-test/main/index_intersect.test
@@ -33,6 +33,7 @@ ANALYZE TABLE City;
 --enable_result_log
 --enable_query_log
 
+SET SESSION optimizer_switch='rowid_filter=off';
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
 
 SELECT COUNT(*) FROM City;
@@ -460,3 +461,4 @@ WHERE (f1 < 535  OR  f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
 DROP TABLE t1;
 
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
+SET SESSION optimizer_switch='rowid_filter=default';
diff --git a/mysql-test/main/index_intersect_innodb.result b/mysql-test/main/index_intersect_innodb.result
index c305886..854bcd7 100644
--- a/mysql-test/main/index_intersect_innodb.result
+++ b/mysql-test/main/index_intersect_innodb.result
@@ -44,6 +44,7 @@ SELECT COUNT(*) FROM CountryLanguage;
 COUNT(*)
 984
 CREATE INDEX Name ON City(Name);
+SET SESSION optimizer_switch='rowid_filter=off';
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
 SELECT COUNT(*) FROM City;
 COUNT(*)
@@ -978,6 +979,7 @@ f1	f4	f5
 998	a	0
 DROP TABLE t1;
 SET SESSION optimizer_switch='index_merge_sort_intersection=on';
+SET SESSION optimizer_switch='rowid_filter=default';
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
 set global innodb_stats_persistent_sample_pages=
 @innodb_stats_persistent_sample_pages_save;
diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result
index 6a3ea83..26b51ba 100644
--- a/mysql-test/main/index_merge_innodb.result
+++ b/mysql-test/main/index_merge_innodb.result
@@ -1,5 +1,6 @@
 set @optimizer_switch_save= @@optimizer_switch;
 set optimizer_switch='index_merge_sort_intersection=off';
+set optimizer_switch='rowid_filter=off';
 #---------------- Index merge test 2 -------------------------------------------
 SET SESSION STORAGE_ENGINE = InnoDB;
 drop table if exists t1,t2;
diff --git a/mysql-test/main/index_merge_innodb.test b/mysql-test/main/index_merge_innodb.test
index 53ce311..e8dc837 100644
--- a/mysql-test/main/index_merge_innodb.test
+++ b/mysql-test/main/index_merge_innodb.test
@@ -20,6 +20,7 @@ let $merge_table_support= 0;
 
 set @optimizer_switch_save= @@optimizer_switch;
 set optimizer_switch='index_merge_sort_intersection=off';
+set optimizer_switch='rowid_filter=off';
 
 # The first two tests are disabled because of non deterministic explain output.
 # If include/index_merge1.inc can be enabled for InnoDB and all other
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index eebdf6c..8e83bdf 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -1,4 +1,5 @@
 set @optimizer_switch_save= @@optimizer_switch;
+set optimizer_switch='rowid_filter=off';
 set optimizer_switch='index_merge_sort_intersection=off';
 #---------------- Index merge test 1 -------------------------------------------
 SET SESSION STORAGE_ENGINE = MyISAM;
@@ -1520,12 +1521,12 @@ explain select * from t1 where a=1 or b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	2	Using union(a,b); Using where
 This should use ALL:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a=1 or b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	1000	Using where
 This should use sort-union:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=1 or b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	2	Using sort_union(a,b); Using where
@@ -1535,17 +1536,17 @@ explain select * from t1 where a<1 or b <1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	38	Using sort_union(a,b); Using where
 This should use ALL:
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	1000	Using where
 This should use ALL:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	1000	Using where
 This will use sort-union:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	38	Using sort_union(a,b); Using where
@@ -1557,7 +1558,7 @@ explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b,c,d	a,b	5,5	NULL	3	Using sort_union(a,b); Using where
 And if we disable sort_union, union:
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b,c,d	c,d	5,5	NULL	100	Using union(c,d); Using where
@@ -1576,22 +1577,22 @@ explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	1	Using intersect(a,b); Using where
 No intersect when index_merge is disabled:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	49	Using where
 No intersect if it is disabled:
-set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off';
+set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	49	Using where
 Do intersect when union was disabled
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	1	Using intersect(a,b); Using where
 Do intersect when sort_union was disabled
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	1	Using intersect(a,b); Using where
@@ -1601,13 +1602,13 @@ explain select * from t1 where a=10 and b=10 or c=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b,c	a,b,c	5,5,5	NULL	6	Using union(intersect(a,b),c); Using where
 Should be only union left:
-set optimizer_switch='default,index_merge_intersection=off';
+set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10 or c=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b,c	a,c	5,5	NULL	54	Using union(a,c); Using where
 This will switch to sort-union (intersection will be gone, too,
 that's a known limitation:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10 or c=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	a,b,c	a,c	5,5	NULL	54	Using sort_union(a,c); Using where
diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test
index 75beb9b..c3ac7fd 100644
--- a/mysql-test/main/index_merge_myisam.test
+++ b/mysql-test/main/index_merge_myisam.test
@@ -16,6 +16,7 @@ let $merge_table_support= 1;
 
 set @optimizer_switch_save= @@optimizer_switch;
 
+set optimizer_switch='rowid_filter=off';
 set optimizer_switch='index_merge_sort_intersection=off';
 
 --source include/index_merge1.inc
@@ -39,11 +40,11 @@ from t0 A, t0 B, t0 C;
 explain select * from t1 where a=1 or b=1;
 
 --echo This should use ALL:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a=1 or b=1;
 
 --echo This should use sort-union:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=1 or b=1;
 
 --echo This will use sort-union:
@@ -51,16 +52,16 @@ set optimizer_switch=default;
 explain select * from t1 where a<1 or b <1;
 
 --echo This should use ALL:
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 
 
 --echo This should use ALL:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 
 --echo This will use sort-union:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a<1 or b <1;
 
 alter table t1 add d int, add key(d);
@@ -71,7 +72,7 @@ set optimizer_switch=default;
 explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
 
 --echo And if we disable sort_union, union:
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
 
 drop table t1;
@@ -92,19 +93,19 @@ set optimizer_switch=default;
 explain select * from t1 where a=10 and b=10;
 
 --echo No intersect when index_merge is disabled:
-set optimizer_switch='default,index_merge=off';
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 
 --echo No intersect if it is disabled:
-set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off';
+set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 
 --echo Do intersect when union was disabled
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 
 --echo Do intersect when sort_union was disabled
-set optimizer_switch='default,index_merge_sort_union=off';
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10;
 
 # Now take union-of-intersection and see how we can disable parts of it
@@ -113,12 +114,12 @@ set optimizer_switch=default;
 explain select * from t1 where a=10 and b=10 or c=10;
 
 --echo Should be only union left:
-set optimizer_switch='default,index_merge_intersection=off';
+set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10 or c=10;
 
 --echo This will switch to sort-union (intersection will be gone, too,
 --echo   that's a known limitation:
-set optimizer_switch='default,index_merge_union=off';
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
 explain select * from t1 where a=10 and b=10 or c=10;
 
 set optimizer_switch=default;
diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result
index f00a723..6f3fb09 100644
--- a/mysql-test/main/join_outer_innodb.result
+++ b/mysql-test/main/join_outer_innodb.result
@@ -444,9 +444,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t1.a5	1	
 1	SIMPLE	t12	eq_ref	PRIMARY	PRIMARY	4	test.t11.k3	1	Using where
 1	SIMPLE	l2	eq_ref	PRIMARY	PRIMARY	4	test.t11.k4	1	Using where
-1	SIMPLE	t13	ref|filter	PRIMARY,m3	PRIMARY|m3	4|4	test.t1.a1	1 (33%)	Using where; Using index; Using filter
+1	SIMPLE	t13	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
 1	SIMPLE	l4	eq_ref	PRIMARY	PRIMARY	4	test.t13.m2	1	Using where; Using index
-1	SIMPLE	m2	ref|filter	PRIMARY,m3	PRIMARY|m3	4|4	test.t1.a1	1 (33%)	Using where; Using index; Using filter
+1	SIMPLE	m2	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
 1	SIMPLE	l3	eq_ref	PRIMARY	PRIMARY	4	test.m2.m2	1	Using where
 1	SIMPLE	t14	eq_ref	PRIMARY	PRIMARY	2	test.t1.a8	1	Using where
 1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
@@ -466,9 +466,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t1.a5	1	
 1	SIMPLE	t12	eq_ref	PRIMARY	PRIMARY	4	test.t11.k3	1	Using where
 1	SIMPLE	l2	eq_ref	PRIMARY	PRIMARY	4	test.t11.k4	1	Using where
-1	SIMPLE	t13	ref|filter	PRIMARY,m3	PRIMARY|m3	4|4	test.t1.a1	1 (33%)	Using where; Using index; Using filter
+1	SIMPLE	t13	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
 1	SIMPLE	l4	eq_ref	PRIMARY	PRIMARY	4	test.t13.m2	1	Using where; Using index
-1	SIMPLE	m2	ref|filter	PRIMARY,m3	PRIMARY|m3	4|4	test.t1.a1	1 (33%)	Using where; Using index; Using filter
+1	SIMPLE	m2	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
 1	SIMPLE	l3	eq_ref	PRIMARY	PRIMARY	4	test.m2.m2	1	Using where
 1	SIMPLE	t14	eq_ref	PRIMARY	PRIMARY	2	test.t1.a8	1	Using where
 1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result
index 36c75ad..b3368b2 100644
--- a/mysql-test/main/key_cache.result
+++ b/mysql-test/main/key_cache.result
@@ -739,13 +739,13 @@ p
 1019
 explain select i from t2 where a='yyyy' and i=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	k1,k2	k1	5	const	189	Using where
+1	SIMPLE	t2	ref|filter	k1,k2	k1|k2	5|11	const	189 (27%)	Using where; Using filter
 select i from t2 where a='yyyy' and i=3;
 i
 3
 explain select a from t2 where a='yyyy' and i=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	k1,k2	k1	5	const	189	Using where
+1	SIMPLE	t2	ref|filter	k1,k2	k1|k2	5|11	const	189 (27%)	Using where; Using filter
 select a from t2 where a='yyyy' and i=3 ;
 a
 yyyy
@@ -753,7 +753,7 @@ select * from information_schema.key_caches where segment_number is null;
 KEY_CACHE_NAME	SEGMENTS	SEGMENT_NUMBER	FULL_SIZE	BLOCK_SIZE	USED_BLOCKS	UNUSED_BLOCKS	DIRTY_BLOCKS	READ_REQUESTS	READS	WRITE_REQUESTS	WRITES
 default	2	NULL	32768	1024	#	#	0	3178	24	1552	18
 small	NULL	NULL	1048576	1024	#	#	0	0	0	0	0
-keycache1	7	NULL	262143	2048	#	#	0	3231	43	1594	30
+keycache1	7	NULL	262143	2048	#	#	0	3283	43	1594	30
 keycache2	NULL	NULL	1048576	1024	#	#	0	6	6	3	3
 set global keycache1.key_cache_block_size=2*1024;
 insert into t2 values (7000, 3, 'yyyy');
diff --git a/mysql-test/main/mrr_icp_extra.result b/mysql-test/main/mrr_icp_extra.result
index 6943f12..49acd7b 100644
--- a/mysql-test/main/mrr_icp_extra.result
+++ b/mysql-test/main/mrr_icp_extra.result
@@ -1,6 +1,7 @@
 call mtr.add_suppression("Can't find record in .*");
 set @mrr_icp_extra_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+set optimizer_switch='rowid_filter=off';
 SET NAMES latin1;
 CREATE TABLE t1 
 (s1 char(10) COLLATE latin1_german1_ci,
diff --git a/mysql-test/main/mrr_icp_extra.test b/mysql-test/main/mrr_icp_extra.test
index 75ddc85..38306f5 100644
--- a/mysql-test/main/mrr_icp_extra.test
+++ b/mysql-test/main/mrr_icp_extra.test
@@ -4,6 +4,7 @@ call mtr.add_suppression("Can't find record in .*");
 
 set @mrr_icp_extra_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+set optimizer_switch='rowid_filter=off';
 SET NAMES latin1;
 CREATE TABLE t1 
 (s1 char(10) COLLATE latin1_german1_ci,
diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result
index afb9b42..525a121 100644
--- a/mysql-test/main/null_key.result
+++ b/mysql-test/main/null_key.result
@@ -181,7 +181,7 @@ insert into t2 values (7),(8);
 explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	SIMPLE	t1	ref|filter	a,b	a|b	10|5	test.t2.a,const	2 (13%)	Using where; Using index; Using filter
+1	SIMPLE	t1	ref	a,b	a	10	test.t2.a,const	2	Using where; Using index
 drop index b on t1;
 explain select * from t2,t1 where t1.a=t2.a and b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 17c5a94..6e1518f 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1577,7 +1577,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	b	4	const	4	Using index condition; Using where; Using temporary; Using filesort
-1	SIMPLE	t2	ref|filter	a,b,c	a|b	40|5	test.t1.a,const	11 (26%)	Using index condition; Using filter
+1	SIMPLE	t2	ref	a,b,c	a	40	test.t1.a,const	11	Using index condition
 SELECT d FROM t1, t2
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/partition_innodb.result b/mysql-test/main/partition_innodb.result
index 151218f..46353c6 100644
--- a/mysql-test/main/partition_innodb.result
+++ b/mysql-test/main/partition_innodb.result
@@ -704,9 +704,11 @@ insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
 10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
 2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
 from t2 A, t2 B, t2 C ,t2 D;
+set statement optimizer_switch='rowid_filter=off' for
 explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	PRIMARY,a,b	b,a	4,4	NULL	#	Using intersect(b,a); Using where; Using index
+set statement optimizer_switch='rowid_filter=off' for
 create temporary table t3 as
 select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
 select count(*) from t3;
diff --git a/mysql-test/main/partition_innodb.test b/mysql-test/main/partition_innodb.test
index bc1b323..a8bbb7c 100644
--- a/mysql-test/main/partition_innodb.test
+++ b/mysql-test/main/partition_innodb.test
@@ -782,8 +782,10 @@ insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
 
 # This should show index_merge, using intersect
 --replace_column 9 #
+set statement optimizer_switch='rowid_filter=off' for
 explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
 # 794 rows in output
+set statement optimizer_switch='rowid_filter=off' for
 create temporary table t3 as
 select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
 select count(*) from t3;
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index a39237b..91fd84a 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -1,5 +1,6 @@
 set @mrr_icp_extra_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+set optimizer_switch='rowid_filter=off';
 set @innodb_stats_persistent_save= @@innodb_stats_persistent;
 set @innodb_stats_persistent_sample_pages_save=
 @@innodb_stats_persistent_sample_pages;
@@ -2592,7 +2593,7 @@ explain select * from t1,t2
 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	idx	idx	5	NULL	6	Using index condition; Rowid-ordered scan
-1	SIMPLE	t2	ref|filter	idx1,idx2	idx1|idx2	5|5	test.t1.a	12 (14%)	Using where; Using filter
+1	SIMPLE	t2	ref	idx1,idx2	idx1	5	test.t1.a	12	Using where
 explain format=json select * from t1,t2
 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
 EXPLAIN
@@ -2619,14 +2620,6 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["d"],
       "ref": ["test.t1.a"],
-      "rowid_filter": {
-        "range": {
-          "key": "idx2",
-          "used_key_parts": ["e"]
-        },
-        "rows": 15,
-        "selectivity_pct": 14.423
-      },
       "rows": 12,
       "filtered": 100,
       "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1"
@@ -2697,7 +2690,7 @@ insert into t1 select * from t1;
 explain select * from t1,t2 
 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range|filter	idx1,idx2	idx1|idx2	5|5	NULL	7 (7%)	Using index condition; Using where; Rowid-ordered scan; Using filter
+1	SIMPLE	t2	range	idx1,idx2	idx1	5	NULL	7	Using index condition; Using where; Rowid-ordered scan
 1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	11	
 explain format=json select * from t1,t2 
 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
@@ -2712,14 +2705,6 @@ EXPLAIN
       "key": "idx1",
       "key_length": "5",
       "used_key_parts": ["d"],
-      "rowid_filter": {
-        "range": {
-          "key": "idx2",
-          "used_key_parts": ["e"]
-        },
-        "rows": 7,
-        "selectivity_pct": 6.7308
-      },
       "rows": 7,
       "filtered": 100,
       "index_condition": "t2.d is not null",
diff --git a/mysql-test/main/range_mrr_icp.test b/mysql-test/main/range_mrr_icp.test
index 29e7af3..4c6983c 100644
--- a/mysql-test/main/range_mrr_icp.test
+++ b/mysql-test/main/range_mrr_icp.test
@@ -1,5 +1,6 @@
 set @mrr_icp_extra_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+set optimizer_switch='rowid_filter=off';
 
 --source range.test
 
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index eb20ad1..8d224ee 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -1,13 +1,678 @@
 DROP DATABASE IF EXISTS dbt3_s001;
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
+CREATE INDEX i_l_quantity ON lineitem(l_quantity);
+CREATE INDEX i_o_totalprice ON orders(o_totalprice);
+ANALYZE TABLE lineitem, orders;
+Table	Op	Msg_type	Msg_text
+dbt3_s001.lineitem	analyze	status	Table is already up to date
+dbt3_s001.orders	analyze	status	Table is already up to date
+set optimizer_use_condition_selectivity=2;
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range|filter	i_l_shipdate,i_l_quantity	i_l_shipdate|i_l_quantity	4|9	NULL	509 (11%)	Using index condition; Using where; Using filter
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": ["i_l_shipdate", "i_l_quantity"],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rowid_filter": {
+        "range": {
+          "key": "i_l_quantity",
+          "used_key_parts": ["l_quantity"]
+        },
+        "rows": 662,
+        "selectivity_pct": 11.024
+      },
+      "rows": 509,
+      "filtered": 11.024,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+      "attached_condition": "lineitem.l_quantity > 45"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+l_orderkey	l_linenumber	l_shipdate	l_quantity
+1121	5	1997-04-27	47
+1121	6	1997-04-21	50
+1441	7	1997-06-07	50
+1443	1	1997-02-05	47
+1473	1	1997-05-05	50
+1568	2	1997-04-06	46
+1632	1	1997-01-25	47
+1632	3	1997-01-29	47
+1954	7	1997-06-04	49
+1959	1	1997-05-05	46
+2151	3	1997-01-20	49
+2177	5	1997-05-10	46
+2369	2	1997-01-02	47
+2469	3	1997-01-11	48
+2469	6	1997-03-03	49
+2470	2	1997-06-02	50
+260	1	1997-03-24	50
+288	2	1997-04-19	49
+289	4	1997-03-14	48
+3009	1	1997-03-19	48
+3105	3	1997-02-28	48
+3106	2	1997-02-27	49
+3429	1	1997-04-08	48
+3490	2	1997-06-27	50
+3619	1	1997-01-22	49
+3619	3	1997-01-31	46
+3969	3	1997-05-29	46
+4005	4	1997-01-31	49
+4036	1	1997-06-21	46
+4066	4	1997-02-17	49
+4098	1	1997-01-26	46
+422	3	1997-06-21	46
+4258	3	1997-01-02	46
+4421	2	1997-04-21	46
+4421	3	1997-05-25	46
+4453	3	1997-05-29	48
+4484	7	1997-03-17	50
+4609	3	1997-02-11	46
+484	1	1997-03-06	49
+484	3	1997-01-24	50
+484	5	1997-03-05	48
+485	1	1997-03-28	50
+4868	1	1997-04-29	47
+4868	3	1997-04-23	49
+4934	1	1997-05-20	48
+4967	1	1997-05-27	50
+5090	2	1997-04-05	46
+5152	2	1997-03-10	50
+5158	4	1997-04-10	49
+5606	3	1997-03-11	46
+5606	7	1997-02-01	46
+5762	4	1997-03-02	47
+581	3	1997-02-27	49
+5829	5	1997-01-31	49
+5831	4	1997-02-24	46
+5895	2	1997-04-27	47
+5895	3	1997-03-15	49
+5952	1	1997-06-30	49
+705	1	1997-04-18	46
+836	3	1997-03-21	46
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range	i_l_shipdate,i_l_quantity	i_l_shipdate	4	NULL	509	Using index condition; Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": ["i_l_shipdate", "i_l_quantity"],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rows": 509,
+      "filtered": 11.024,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+      "attached_condition": "lineitem.l_quantity > 45"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+l_orderkey	l_linenumber	l_shipdate	l_quantity
+1121	5	1997-04-27	47
+1121	6	1997-04-21	50
+1441	7	1997-06-07	50
+1443	1	1997-02-05	47
+1473	1	1997-05-05	50
+1568	2	1997-04-06	46
+1632	1	1997-01-25	47
+1632	3	1997-01-29	47
+1954	7	1997-06-04	49
+1959	1	1997-05-05	46
+2151	3	1997-01-20	49
+2177	5	1997-05-10	46
+2369	2	1997-01-02	47
+2469	3	1997-01-11	48
+2469	6	1997-03-03	49
+2470	2	1997-06-02	50
+260	1	1997-03-24	50
+288	2	1997-04-19	49
+289	4	1997-03-14	48
+3009	1	1997-03-19	48
+3105	3	1997-02-28	48
+3106	2	1997-02-27	49
+3429	1	1997-04-08	48
+3490	2	1997-06-27	50
+3619	1	1997-01-22	49
+3619	3	1997-01-31	46
+3969	3	1997-05-29	46
+4005	4	1997-01-31	49
+4036	1	1997-06-21	46
+4066	4	1997-02-17	49
+4098	1	1997-01-26	46
+422	3	1997-06-21	46
+4258	3	1997-01-02	46
+4421	2	1997-04-21	46
+4421	3	1997-05-25	46
+4453	3	1997-05-29	48
+4484	7	1997-03-17	50
+4609	3	1997-02-11	46
+484	1	1997-03-06	49
+484	3	1997-01-24	50
+484	5	1997-03-05	48
+485	1	1997-03-28	50
+4868	1	1997-04-29	47
+4868	3	1997-04-23	49
+4934	1	1997-05-20	48
+4967	1	1997-05-27	50
+5090	2	1997-04-05	46
+5152	2	1997-03-10	50
+5158	4	1997-04-10	49
+5606	3	1997-03-11	46
+5606	7	1997-02-01	46
+5762	4	1997-03-02	47
+581	3	1997-02-27	49
+5829	5	1997-01-31	49
+5831	4	1997-02-24	46
+5895	2	1997-04-27	47
+5895	3	1997-03-15	49
+5952	1	1997-06-30	49
+705	1	1997-04-18	46
+836	3	1997-03-21	46
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	98	Using index condition
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (5%)	Using where; Using filter
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity"
+      ],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rows": 98,
+      "filtered": 100,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
+    },
+    "table": {
+      "table_name": "orders",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["o_orderkey"],
+      "ref": ["dbt3_s001.lineitem.l_orderkey"],
+      "rowid_filter": {
+        "range": {
+          "key": "i_o_totalprice",
+          "used_key_parts": ["o_totalprice"]
+        },
+        "rows": 81,
+        "selectivity_pct": 5.4
+      },
+      "rows": 1,
+      "filtered": 5.4,
+      "attached_condition": "orders.o_totalprice between 200000 and 230000"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	o_totalprice
+1156	3	1997-01-24	217682.81
+1156	4	1997-01-18	217682.81
+1156	6	1997-01-27	217682.81
+1156	7	1997-01-01	217682.81
+2180	2	1997-01-03	208481.57
+2180	3	1997-01-03	208481.57
+3619	1	1997-01-22	222274.54
+3619	3	1997-01-31	222274.54
+3619	6	1997-01-25	222274.54
+484	3	1997-01-24	219920.62
+5606	6	1997-01-11	219959.08
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	98	Using index condition
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity"
+      ],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rows": 98,
+      "filtered": 100,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
+    },
+    "table": {
+      "table_name": "orders",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["o_orderkey"],
+      "ref": ["dbt3_s001.lineitem.l_orderkey"],
+      "rows": 1,
+      "filtered": 5.4,
+      "attached_condition": "orders.o_totalprice between 200000 and 230000"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	o_totalprice
+1156	3	1997-01-24	217682.81
+1156	4	1997-01-18	217682.81
+1156	6	1997-01-27	217682.81
+1156	7	1997-01-01	217682.81
+2180	2	1997-01-03	208481.57
+2180	3	1997-01-03	208481.57
+3619	1	1997-01-22	222274.54
+3619	3	1997-01-31	222274.54
+3619	6	1997-01-25	222274.54
+484	3	1997-01-24	219920.62
+5606	6	1997-01-11	219959.08
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity	i_l_shipdate|i_l_quantity	4|9	NULL	509 (11%)	Using index condition; Using where; Using filter
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (10%)	Using where; Using filter
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity",
+        "i_l_quantity"
+      ],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rowid_filter": {
+        "range": {
+          "key": "i_l_quantity",
+          "used_key_parts": ["l_quantity"]
+        },
+        "rows": 662,
+        "selectivity_pct": 11.024
+      },
+      "rows": 509,
+      "filtered": 11.024,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+      "attached_condition": "lineitem.l_quantity > 45"
+    },
+    "table": {
+      "table_name": "orders",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["o_orderkey"],
+      "ref": ["dbt3_s001.lineitem.l_orderkey"],
+      "rowid_filter": {
+        "range": {
+          "key": "i_o_totalprice",
+          "used_key_parts": ["o_totalprice"]
+        },
+        "rows": 152,
+        "selectivity_pct": 10.133
+      },
+      "rows": 1,
+      "filtered": 10.133,
+      "attached_condition": "orders.o_totalprice between 180000 and 230000"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	l_quantity	o_totalprice
+1632	1	1997-01-25	47	183286.33
+1632	3	1997-01-29	47	183286.33
+2177	5	1997-05-10	46	183493.42
+2469	3	1997-01-11	48	192074.23
+2469	6	1997-03-03	49	192074.23
+3619	1	1997-01-22	49	222274.54
+3619	3	1997-01-31	46	222274.54
+484	1	1997-03-06	49	219920.62
+484	3	1997-01-24	50	219920.62
+484	5	1997-03-05	48	219920.62
+4934	1	1997-05-20	48	180478.16
+5606	3	1997-03-11	46	219959.08
+5606	7	1997-02-01	46	219959.08
+5829	5	1997-01-31	49	183734.56
+5895	2	1997-04-27	47	201419.83
+5895	3	1997-03-15	49	201419.83
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity	i_l_shipdate	4	NULL	509	Using index condition; Using where
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "range",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity",
+        "i_l_quantity"
+      ],
+      "key": "i_l_shipdate",
+      "key_length": "4",
+      "used_key_parts": ["l_shipDATE"],
+      "rows": 509,
+      "filtered": 11.024,
+      "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+      "attached_condition": "lineitem.l_quantity > 45"
+    },
+    "table": {
+      "table_name": "orders",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["o_orderkey"],
+      "ref": ["dbt3_s001.lineitem.l_orderkey"],
+      "rows": 1,
+      "filtered": 10.133,
+      "attached_condition": "orders.o_totalprice between 180000 and 230000"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	l_quantity	o_totalprice
+1632	1	1997-01-25	47	183286.33
+1632	3	1997-01-29	47	183286.33
+2177	5	1997-05-10	46	183493.42
+2469	3	1997-01-11	48	192074.23
+2469	6	1997-03-03	49	192074.23
+3619	1	1997-01-22	49	222274.54
+3619	3	1997-01-31	46	222274.54
+484	1	1997-03-06	49	219920.62
+484	3	1997-01-24	50	219920.62
+484	5	1997-03-05	48	219920.62
+4934	1	1997-05-20	48	180478.16
+5606	3	1997-03-11	46	219959.08
+5606	7	1997-02-01	46	219959.08
+5829	5	1997-01-31	49	183734.56
+5895	2	1997-04-27	47	201419.83
+5895	3	1997-03-15	49	201419.83
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	range	PRIMARY,i_o_totalprice	i_o_totalprice	9	NULL	81	Using index condition
+1	SIMPLE	lineitem	ref|filter	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY|i_l_shipdate	4|4	dbt3_s001.orders.o_orderkey	4 (8%)	Using where; Using filter
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "orders",
+      "access_type": "range",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "i_o_totalprice",
+      "key_length": "9",
+      "used_key_parts": ["o_totalprice"],
+      "rows": 81,
+      "filtered": 100,
+      "index_condition": "orders.o_totalprice between 200000 and 230000"
+    },
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "ref",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity"
+      ],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["l_orderkey"],
+      "ref": ["dbt3_s001.orders.o_orderkey"],
+      "rowid_filter": {
+        "range": {
+          "key": "i_l_shipdate",
+          "used_key_parts": ["l_shipDATE"]
+        },
+        "rows": 509,
+        "selectivity_pct": 8.4763
+      },
+      "rows": 4,
+      "filtered": 8.4763,
+      "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	o_totalprice
+1156	3	1997-01-24	217682.81
+1156	4	1997-01-18	217682.81
+1156	6	1997-01-27	217682.81
+1156	7	1997-01-01	217682.81
+1890	1	1997-04-02	202364.58
+1890	3	1997-02-09	202364.58
+1890	4	1997-04-08	202364.58
+1890	5	1997-04-15	202364.58
+1890	6	1997-02-13	202364.58
+2180	2	1997-01-03	208481.57
+2180	3	1997-01-03	208481.57
+3619	1	1997-01-22	222274.54
+3619	3	1997-01-31	222274.54
+3619	4	1997-03-18	222274.54
+3619	6	1997-01-25	222274.54
+453	1	1997-06-30	216826.73
+453	2	1997-06-30	216826.73
+484	1	1997-03-06	219920.62
+484	2	1997-04-09	219920.62
+484	3	1997-01-24	219920.62
+484	4	1997-04-29	219920.62
+484	5	1997-03-05	219920.62
+484	6	1997-04-06	219920.62
+5606	2	1997-02-23	219959.08
+5606	3	1997-03-11	219959.08
+5606	4	1997-02-06	219959.08
+5606	6	1997-01-11	219959.08
+5606	7	1997-02-01	219959.08
+5859	2	1997-05-15	210643.96
+5859	5	1997-05-28	210643.96
+5859	6	1997-06-15	210643.96
+5895	1	1997-04-05	201419.83
+5895	2	1997-04-27	201419.83
+5895	3	1997-03-15	201419.83
+5895	4	1997-03-03	201419.83
+5895	5	1997-04-30	201419.83
+5895	6	1997-04-19	201419.83
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	range	PRIMARY,i_o_totalprice	i_o_totalprice	9	NULL	81	Using index condition
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "orders",
+      "access_type": "range",
+      "possible_keys": ["PRIMARY", "i_o_totalprice"],
+      "key": "i_o_totalprice",
+      "key_length": "9",
+      "used_key_parts": ["o_totalprice"],
+      "rows": 81,
+      "filtered": 100,
+      "index_condition": "orders.o_totalprice between 200000 and 230000"
+    },
+    "table": {
+      "table_name": "lineitem",
+      "access_type": "ref",
+      "possible_keys": [
+        "PRIMARY",
+        "i_l_shipdate",
+        "i_l_orderkey",
+        "i_l_orderkey_quantity"
+      ],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["l_orderkey"],
+      "ref": ["dbt3_s001.orders.o_orderkey"],
+      "rows": 4,
+      "filtered": 8.4763,
+      "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'"
+    }
+  }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+o_orderkey	l_linenumber	l_shipdate	o_totalprice
+1156	3	1997-01-24	217682.81
+1156	4	1997-01-18	217682.81
+1156	6	1997-01-27	217682.81
+1156	7	1997-01-01	217682.81
+1890	1	1997-04-02	202364.58
+1890	3	1997-02-09	202364.58
+1890	4	1997-04-08	202364.58
+1890	5	1997-04-15	202364.58
+1890	6	1997-02-13	202364.58
+2180	2	1997-01-03	208481.57
+2180	3	1997-01-03	208481.57
+3619	1	1997-01-22	222274.54
+3619	3	1997-01-31	222274.54
+3619	4	1997-03-18	222274.54
+3619	6	1997-01-25	222274.54
+453	1	1997-06-30	216826.73
+453	2	1997-06-30	216826.73
+484	1	1997-03-06	219920.62
+484	2	1997-04-09	219920.62
+484	3	1997-01-24	219920.62
+484	4	1997-04-29	219920.62
+484	5	1997-03-05	219920.62
+484	6	1997-04-06	219920.62
+5606	2	1997-02-23	219959.08
+5606	3	1997-03-11	219959.08
+5606	4	1997-02-06	219959.08
+5606	6	1997-01-11	219959.08
+5606	7	1997-02-01	219959.08
+5859	2	1997-05-15	210643.96
+5859	5	1997-05-28	210643.96
+5859	6	1997-06-15	210643.96
+5895	1	1997-04-05	201419.83
+5895	2	1997-04-27	201419.83
+5895	3	1997-03-15	201419.83
+5895	4	1997-03-03	201419.83
+5895	5	1997-04-30	201419.83
+5895	6	1997-04-19	201419.83
 # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate
 EXPLAIN SELECT *
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
 l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_receiptdate|i_l_shipdate	4|4	NULL	60 (1%)	Using index condition; Using where; Using filter
+1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_receiptdate|i_l_shipdate	4|4	NULL	53 (2%)	Using index condition; Using where; Using filter
 1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
 # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate
 # orders : {i_o_orderdate} -> i_o_orderdate
@@ -17,7 +682,7 @@ WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
 l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND
 o_orderdate > '1997-01-15';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_receiptdate|i_l_shipdate	4|4	NULL	60 (1%)	Using index condition; Using where; Using filter
+1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_receiptdate|i_l_shipdate	4|4	NULL	53 (2%)	Using index condition; Using where; Using filter
 1	SIMPLE	orders	eq_ref	PRIMARY,i_o_orderdate	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
 # lineitem : {i_l_receiptdate, i_l_shipdate,
 #             i_l_commitdate} -> i_l_receiptdate
@@ -27,7 +692,7 @@ WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
 l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND
 l_commitdate BETWEEN '1997-01-05' AND '1997-01-25';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	i_l_receiptdate|i_l_commitdate	4|4	NULL	60 (1%)	Using index condition; Using where; Using filter
+1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	i_l_receiptdate|i_l_commitdate	4|4	NULL	53 (1%)	Using index condition; Using where; Using filter
 1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
 # lineitem : {i_l_receiptdate, i_l_shipdate,
 #             i_l_commitdate} -> i_l_commitdate
@@ -37,7 +702,7 @@ WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
 l_receiptdate BETWEEN '1997-01-01' AND '1997-01-25' AND
 l_commitdate BETWEEN '1997-01-15' AND '1997-01-25';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	i_l_commitdate|i_l_shipdate	4|4	NULL	35 (1%)	Using index condition; Using where; Using filter
+1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	i_l_commitdate|i_l_receiptdate	4|4	NULL	28 (1%)	Using index condition; Using where; Using filter
 1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
 CREATE INDEX i_l_extendedprice ON lineitem(l_extendedprice);
 # lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate,
@@ -179,7 +844,7 @@ EXPLAIN
         "selectivity_pct": 0.1332
       },
       "rows": 6,
-      "filtered": 100,
+      "filtered": 0.1332,
       "index_condition": "lineitem.l_receiptDATE between '1997-01-09' and '1997-01-10'",
       "attached_condition": "lineitem.l_shipDATE between '1997-01-09' and '1997-01-10'"
     },
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index 73d7326..274fab6 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -14,6 +14,84 @@ use dbt3_s001;
 --enable_result_log
 --enable_query_log
 
+CREATE INDEX i_l_quantity ON lineitem(l_quantity);
+
+CREATE INDEX i_o_totalprice ON orders(o_totalprice);
+
+ANALYZE TABLE lineitem, orders;
+
+set optimizer_use_condition_selectivity=2;
+
+let $with_filter=
+set statement optimizer_switch='rowid_filter=on' for;
+
+let $without_filter=
+set statement optimizer_switch='rowid_filter=off' for;
+
+let $q1=
+SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+         l_quantity > 45;
+
+eval $with_filter EXPLAIN $q1;
+eval $with_filter EXPLAIN FORMAT=JSON $q1;
+--sorted_result
+eval $with_filter $q1;
+
+eval $without_filter EXPLAIN $q1;
+eval $without_filter EXPLAIN FORMAT=JSON $q1;
+--sorted_result
+eval $without_filter $q1;
+
+let $q2=
+SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+         o_totalprice between 200000 and 230000;
+
+eval $with_filter EXPLAIN $q2;
+eval $with_filter EXPLAIN FORMAT=JSON $q2;
+--sorted_result
+eval $with_filter $q2;
+
+eval $without_filter EXPLAIN $q2;
+eval $without_filter EXPLAIN FORMAT=JSON $q2;
+--sorted_result
+eval $without_filter $q2;
+
+let $q3=
+SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+  WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+         l_quantity > 45 AND
+         o_totalprice between 180000 and 230000;
+
+eval $with_filter EXPLAIN $q3;
+eval $with_filter EXPLAIN FORMAT=JSON $q3;
+--sorted_result
+eval $with_filter $q3;
+
+eval $without_filter EXPLAIN $q3;
+eval $without_filter EXPLAIN FORMAT=JSON $q3;
+--sorted_result
+eval $without_filter $q3;
+
+let $q4=
+SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+  FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+  WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+        o_totalprice between 200000 and 230000;
+
+eval $with_filter EXPLAIN $q4;
+eval $with_filter EXPLAIN FORMAT=JSON $q4;
+--sorted_result
+eval $with_filter $q4;
+
+eval $without_filter EXPLAIN $q4;
+eval $without_filter EXPLAIN FORMAT=JSON $q4;
+--sorted_result
+eval $without_filter $q4;
+
 --echo # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate
 EXPLAIN SELECT *
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 75106b5..220f500 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3698,6 +3698,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
 COUNT(*)
 24
@@ -3740,7 +3743,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	2	Using index condition; Using where
+1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using filter
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test
index e7525f8..0d43dfd 100644
--- a/mysql-test/main/select.test
+++ b/mysql-test/main/select.test
@@ -3230,6 +3230,8 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
 
+ANALYZE TABLE t1;
+
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index 16672bd..bc86a27 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3485,13 +3485,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3709,6 +3709,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
 COUNT(*)
 24
@@ -3751,7 +3754,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	2	Using index condition; Using where
+1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using filter
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 75106b5..220f500 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 EXPLAIN
 SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	c	NULL	NULL	NULL	18	Using where
-1	SIMPLE	t1	eq_ref|filter	PRIMARY,b	PRIMARY|b	4|5	test.t2.c	1 (30%)	Using where; Using filter
+1	SIMPLE	t1	range	PRIMARY,b	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	
 DROP TABLE t1, t2;
 create table t1 (
 a int unsigned    not null auto_increment primary key,
@@ -3698,6 +3698,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
 INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
 COUNT(*)
 24
@@ -3740,7 +3743,7 @@ EXPLAIN SELECT * FROM t1
 WHERE ID_better=1 AND ID1_with_null IS NULL AND 
 (ID2_with_null=1 OR ID2_with_null=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1,idx2	idx1	5	const	2	Using index condition; Using where
+1	SIMPLE	t1	ref|filter	idx1,idx2	idx1|idx2	5|4	const	2 (1%)	Using index condition; Using where; Using filter
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
 INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index f2047bf..7620842 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -132,7 +132,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
-1	PRIMARY	t3	eq_ref|filter	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY|CMFLDRPARNT_IDX	34|35	test.t3.PARENTID	1 (29%)	Using where; Using filter
+1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);
diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result
index 245739c..5acf8cb 100644
--- a/mysql-test/main/subselect_mat_cost.result
+++ b/mysql-test/main/subselect_mat_cost.result
@@ -280,6 +280,7 @@ Q2.2m:
 Countries that speak French OR Spanish, but do not speak English
 MATERIALIZATION because the outer query filters less rows than Q5-a,
 so there are more lookups.
+set statement optimizer_switch='rowid_filter=off' for
 EXPLAIN
 SELECT Country.Name
 FROM Country, CountryLanguage 
@@ -289,7 +290,8 @@ AND Code = Country;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	CountryLanguage	range	PRIMARY,Language	Language	30	NULL	44	Using index condition; Using where; Rowid-ordered scan
 1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
-2	MATERIALIZED	CountryLanguage	ref	PRIMARY,Language	Language	30	const	48	Using index condition
+3	MATERIALIZED	CountryLanguage	ref	PRIMARY,Language	Language	30	const	48	Using index condition
+set statement optimizer_switch='rowid_filter=off' for
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
diff --git a/mysql-test/main/subselect_mat_cost.test b/mysql-test/main/subselect_mat_cost.test
index 5a1fb55..5f44d0d 100644
--- a/mysql-test/main/subselect_mat_cost.test
+++ b/mysql-test/main/subselect_mat_cost.test
@@ -205,6 +205,9 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
 -- echo Countries that speak French OR Spanish, but do not speak English
 -- echo MATERIALIZATION because the outer query filters less rows than Q5-a,
 -- echo so there are more lookups.
+
+
+set statement optimizer_switch='rowid_filter=off' for
 EXPLAIN
 SELECT Country.Name
 FROM Country, CountryLanguage 
@@ -212,6 +215,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
   AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
   AND Code = Country;
 
+set statement optimizer_switch='rowid_filter=off' for
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
diff --git a/sql/handler.h b/sql/handler.h
index f122a26..8869d3d 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -325,6 +325,8 @@ enum enum_alter_inplace_result {
 */
 #define HA_CLUSTERED_INDEX      512
 
+#define HA_DO_RANGE_FILTER_PUSHDOWN  1024
+
 /*
   bits in alter_table_flags:
 */
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 4fc321f..ba2705b 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2520,6 +2520,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   quick=0;
   needed_reg.clear_all();
   quick_keys.clear_all();
+  head->with_impossible_ranges.clear_all();
   DBUG_ASSERT(!head->is_filled_at_execution());
   if (keys_to_use.is_clear_all() || head->is_filled_at_execution())
     DBUG_RETURN(0);
@@ -8556,6 +8557,7 @@ int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree1, SEL_TREE *tree2,
       if (key && key->type == SEL_ARG::IMPOSSIBLE)
       {
 	result->type= SEL_TREE::IMPOSSIBLE;
+        param->table->with_impossible_ranges.set_bit(param->real_keynr[key_no]);
         DBUG_RETURN(1);
       }
       result_keys.set_bit(key_no);
diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc
index 2ce6d83..7af9c4e 100644
--- a/sql/rowid_filter.cc
+++ b/sql/rowid_filter.cc
@@ -5,6 +5,27 @@
 #include "rowid_filter.h"
 #include "sql_select.h"
 
+inline
+double Range_filter_cost_info::lookup_cost(
+                               Rowid_filter_container_type cont_type)
+{
+  switch (cont_type) {
+  case ORDERED_ARRAY_CONTAINER:
+    return log(est_elements)*0.01;
+  default:
+    DBUG_ASSERT(0);
+    return 0;
+  }
+}
+
+
+inline
+double Range_filter_cost_info::avg_access_and_eval_gain_per_row(
+                        Rowid_filter_container_type cont_type)
+{
+  return (1+1.0/TIME_FOR_COMPARE) * (1 - selectivity) -
+         lookup_cost(cont_type);
+}
 
 /**
   Sets information about filter with key_numb index.
@@ -12,15 +33,19 @@
   and gets slope and interscept values.
 */
 
-void Range_filter_cost_info::init(TABLE *tab, uint key_numb)
+void Range_filter_cost_info::init(Rowid_filter_container_type cont_type,
+                                  TABLE *tab, uint idx)
 {
+  container_type= cont_type;
   table= tab;
-  key_no= key_numb;
+  key_no= idx;
   est_elements= table->quick_rows[key_no];
-  b= build_cost(ORDERED_ARRAY_CONTAINER);
+  b= build_cost(container_type);
   selectivity= est_elements/((double) table->stat_records());
-  a= (1 + COST_COND_EVAL)*(1 - selectivity) - lookup_cost();
-  intersect_x_axis_abcissa= b/a;
+  a= avg_access_and_eval_gain_per_row(container_type);
+  if (a > 0)
+    cross_x= b/a;
+  abs_independent.clear_all();
 }
 
 double
@@ -43,128 +68,99 @@ Range_filter_cost_info::build_cost(Rowid_filter_container_type container_type)
   return cost;
 }
 
-/**
-  @brief
-    Sort available filters by their building cost in the increasing order
-
-  @details
-    The method starts sorting available filters from the first filter that
-    is not defined as the best filter. If there are two filters that are
-    defined as the best filters there is no need to sort other filters.
-    Best filters are already sorted by their building cost and have the
-    smallest bulding cost in comparison with other filters by definition.
-
-    As the sorting method bubble sort is used.
-*/
 
-void TABLE::sort_range_filter_cost_info_array()
+static
+int compare_range_filter_cost_info_by_a(Range_filter_cost_info **filter_ptr_1,
+                                        Range_filter_cost_info **filter_ptr_2)
 {
-  if (best_filter_count  <= 2)
-    return;
-
-  for (uint i= best_filter_count; i < range_filter_cost_info_elements-1; i++)
-  {
-    for (uint j= i+1; j < range_filter_cost_info_elements; j++)
-    {
-      if (range_filter_cost_info[i].intersect_x_axis_abcissa >
-          range_filter_cost_info[j].intersect_x_axis_abcissa)
-        swap_variables(Range_filter_cost_info,
-                       range_filter_cost_info[i],
-                       range_filter_cost_info[j]);
-    }
-  }
+  double diff= (*filter_ptr_2)->a - (*filter_ptr_1)->a;
+  return (diff < 0 ? -1 : (diff > 0 ? 1 : 0));
 }
 
-
 /**
   @brief
-    The method searches for the filters that can reduce the join cost the most
 
   @details
-    The method looks through the available filters trying to choose the best
-    filter and eliminate as many filters as possible.
-
-    Filters are considered as a linear functions. The best filter is the linear
-    function that intersects all other linear functions not in the I quadrant
-    and has the biggest a (slope) value. This filter will reduce the partial
-    join cost the most. If it is possible the second best filter is also
-    chosen. The second best filter can be used if the ref access is made on
-    the index of the first best filter.
-
-    So there is no need to store all other filters except filters that
-    intersect in the I quadrant. It is impossible to say on this step which
-    filter is better and will give the biggest gain.
-
-    The number of filters that can be used is stored in the
-    range_filter_cost_info_elements variable.
 */
 
 void TABLE::prune_range_filters()
 {
-  key_map pruned_filter_map;
-  pruned_filter_map.clear_all();
-  Range_filter_cost_info *max_slope_filters[2] = {0, 0};
+  uint i, j;
 
-  for (uint i= 0; i < range_filter_cost_info_elements; i++)
+  Range_filter_cost_info **filter_ptr_1= range_filter_cost_info_ptr;
+  for (i= 0; i < range_filter_cost_info_elems; i++, filter_ptr_1++)
   {
-    Range_filter_cost_info *filter= &range_filter_cost_info[i];
-    if (filter->a < 0)
+    uint key_no= (*filter_ptr_1)->key_no;
+    Range_filter_cost_info **filter_ptr_2= filter_ptr_1 + 1;
+    for (j= i+1; j < range_filter_cost_info_elems; j++, filter_ptr_2++)
     {
-      range_filter_cost_info_elements--;
-      swap_variables(Range_filter_cost_info, range_filter_cost_info[i],
-                     range_filter_cost_info[range_filter_cost_info_elements]);
-      i--;
-      continue;
+      key_map map= key_info[key_no].overlapped;
+      map.intersect(key_info[(*filter_ptr_2)->key_no].overlapped);
+      if (map.is_clear_all())
+      {
+        (*filter_ptr_1)->abs_independent.set_bit((*filter_ptr_2)->key_no);
+        (*filter_ptr_2)->abs_independent.set_bit(key_no);
+      }
     }
-    for (uint j= i+1; j < range_filter_cost_info_elements; j++)
-    {
-      Range_filter_cost_info *cand_filter= &range_filter_cost_info[j];
+  }
 
-      double intersect_x= filter->get_intersect_x(cand_filter);
-      double intersect_y= filter->get_intersect_y(intersect_x);
+  /* Sort the array range_filter_cost_info by 'a' */
+  my_qsort(range_filter_cost_info_ptr,
+           range_filter_cost_info_elems,
+           sizeof(Range_filter_cost_info *),
+           (qsort_cmp) compare_range_filter_cost_info_by_a);
 
-      if (intersect_x > 0 && intersect_y > 0)
+  Range_filter_cost_info **cand_filter_ptr= range_filter_cost_info_ptr;
+  for (i= 0; i < range_filter_cost_info_elems; i++, cand_filter_ptr++)
+  {
+    bool is_pruned= false;
+    Range_filter_cost_info **usable_filter_ptr= range_filter_cost_info_ptr;
+    key_map abs_indep;
+    abs_indep.clear_all();
+    for (uint j= 0; j < i; j++, usable_filter_ptr++)
+    {
+      if ((*cand_filter_ptr)->cross_x >= (*usable_filter_ptr)->cross_x)
       {
-        pruned_filter_map.set_bit(cand_filter->key_no);
-        pruned_filter_map.set_bit(filter->key_no);
+        if (abs_indep.is_set((*usable_filter_ptr)->key_no))
+	{
+	  is_pruned= true;
+          break;
+        }
+        abs_indep.merge((*usable_filter_ptr)->abs_independent);
       }
-    }
-    if (!pruned_filter_map.is_set(filter->key_no))
-    {
-      if (!max_slope_filters[0])
-        max_slope_filters[0]= filter;
       else
       {
-        if (!max_slope_filters[1] ||
-            max_slope_filters[1]->a < filter->a)
-          max_slope_filters[1]= filter;
-        if (max_slope_filters[0]->a < max_slope_filters[1]->a)
-          swap_variables(Range_filter_cost_info*, max_slope_filters[0],
-                                                  max_slope_filters[1]);
+        Range_filter_cost_info *moved= *cand_filter_ptr;
+        memmove(usable_filter_ptr+1, usable_filter_ptr,
+                sizeof(Range_filter_cost_info *) * (i-j-1));
+        *usable_filter_ptr= moved;
       }
     }
-  }
-
-  for (uint i= 0; i<2; i++)
-  {
-    if (max_slope_filters[i])
+    if (is_pruned)
     {
-      swap_variables(Range_filter_cost_info,
-                     range_filter_cost_info[i],
-                     *max_slope_filters[i]);
-      if (i == 0 &&
-          max_slope_filters[1] == &range_filter_cost_info[0])
-        max_slope_filters[1]= max_slope_filters[0];
-
-      best_filter_count++;
-      max_slope_filters[i]= &range_filter_cost_info[i];
+      memmove(cand_filter_ptr, cand_filter_ptr+1,
+              sizeof(Range_filter_cost_info *) *
+              (range_filter_cost_info_elems - 1 - i));
+      range_filter_cost_info_elems--;
     }
   }
-  sort_range_filter_cost_info_array();
 }
 
 
-void TABLE::select_usable_range_filters(THD *thd)
+static uint
+get_max_range_filter_elements_for_table(THD *thd, TABLE *tab,
+                                        Rowid_filter_container_type cont_type)
+{
+  switch (cont_type) {
+  case ORDERED_ARRAY_CONTAINER :
+    return thd->variables.max_rowid_filter_size/tab->file->ref_length;
+  default :
+    DBUG_ASSERT(0);
+    return 0;
+  }
+}
+
+void TABLE::init_cost_info_for_usable_range_filters(THD *thd)
 {
   uint key_no;
   key_map usable_range_filter_keys;
@@ -172,73 +168,74 @@ void TABLE::select_usable_range_filters(THD *thd)
   key_map::Iterator it(quick_keys);
   while ((key_no= it++) != key_map::Iterator::BITMAP_END)
   {
-    if (quick_rows[key_no] >
-        thd->variables.max_rowid_filter_size/file->ref_length)
+    if (!(file->index_flags(key_no, 0, 1) & HA_DO_RANGE_FILTER_PUSHDOWN))
+      continue;
+    if (key_no == s->primary_key && file->primary_key_is_clustered())
+      continue;
+   if (quick_rows[key_no] >
+       get_max_range_filter_elements_for_table(thd, this,
+                                               ORDERED_ARRAY_CONTAINER))
       continue;
     usable_range_filter_keys.set_bit(key_no);
   }
 
-  if (usable_range_filter_keys.is_clear_all())
+  range_filter_cost_info_elems= usable_range_filter_keys.bits_set();
+  if (!range_filter_cost_info_elems)
     return;
 
-  range_filter_cost_info_elements= usable_range_filter_keys.bits_set();
+  range_filter_cost_info_ptr=
+    (Range_filter_cost_info **) thd->calloc(sizeof(Range_filter_cost_info *) *
+                                            range_filter_cost_info_elems);
   range_filter_cost_info=
-    new (thd->mem_root) Range_filter_cost_info [range_filter_cost_info_elements];
+    new (thd->mem_root) Range_filter_cost_info[range_filter_cost_info_elems];
+  if (!range_filter_cost_info_ptr || !range_filter_cost_info)
+  {
+    range_filter_cost_info_elems= 0;
+    return;
+  }
+
+  Range_filter_cost_info **curr_ptr= range_filter_cost_info_ptr;
   Range_filter_cost_info *curr_filter_cost_info= range_filter_cost_info;
 
   key_map::Iterator li(usable_range_filter_keys);
   while ((key_no= li++) != key_map::Iterator::BITMAP_END)
   {
-    curr_filter_cost_info->init(this, key_no);
+    *curr_ptr= curr_filter_cost_info;
+    curr_filter_cost_info->init(ORDERED_ARRAY_CONTAINER, this, key_no);
+    curr_ptr++;
     curr_filter_cost_info++;
   }
   prune_range_filters();
 }
 
 
-Range_filter_cost_info
-*TABLE::best_filter_for_current_join_order(uint ref_key_no,
-                                           double record_count,
-                                           double records)
+Range_filter_cost_info *TABLE::best_filter_for_partial_join(uint access_key_no,
+                                                            double records)
 {
-  if (!this || range_filter_cost_info_elements == 0)
+  if (!this || range_filter_cost_info_elems == 0 ||
+      covering_keys.is_set(access_key_no))
     return 0;
 
-  double card= record_count*records;
-  Range_filter_cost_info *best_filter= &range_filter_cost_info[0];
-
-  if (card < best_filter->intersect_x_axis_abcissa)
+  if (access_key_no == s->primary_key && file->primary_key_is_clustered())
     return 0;
-  if (best_filter_count != 0)
-  {
-    if (best_filter->key_no == ref_key_no)
-    {
-      if (best_filter_count == 2)
-      {
-        best_filter= &range_filter_cost_info[1];
-        if (card < best_filter->intersect_x_axis_abcissa)
-          return 0;
-        return best_filter;
-      }
-    }
-    else
-      return best_filter;
-  }
 
-  double best_filter_improvement= 0.0;
-  best_filter= 0;
+  Range_filter_cost_info *best_filter= 0;
+  double best_filter_gain= 0;
 
-  key_map *intersected_with= &key_info->intersected_with;
-  for (uint i= best_filter_count; i < range_filter_cost_info_elements; i++)
+  key_map *overlapped= &key_info[access_key_no].overlapped;
+  for (uint i= 0; i < range_filter_cost_info_elems ;  i++)
   {
-    Range_filter_cost_info *filter= &range_filter_cost_info[i];
-    if ((filter->key_no == ref_key_no) || intersected_with->is_set(filter->key_no))
+    double curr_gain = 0;
+    Range_filter_cost_info *filter= range_filter_cost_info_ptr[i];
+    if ((filter->key_no == access_key_no) ||
+        overlapped->is_set(filter->key_no))
       continue;
-    if (card < filter->intersect_x_axis_abcissa)
+    if (records < filter->cross_x)
       break;
-    if (best_filter_improvement < filter->get_filter_gain(card))
+    curr_gain= filter->get_gain(records);
+    if (best_filter_gain < curr_gain)
     {
-      best_filter_improvement= filter->get_filter_gain(card);
+      best_filter_gain= curr_gain;
       best_filter= filter;
     }
   }
diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h
index 9b93679..7cec865 100644
--- a/sql/rowid_filter.h
+++ b/sql/rowid_filter.h
@@ -129,48 +129,51 @@ typedef enum
 class Range_filter_cost_info : public Sql_alloc
 {
 public:
+  Rowid_filter_container_type container_type;
   TABLE *table;
   uint key_no;
   double est_elements;
   double b;                         // intercept of the linear function
   double a;                         // slope of the linear function
   double selectivity;
-  double intersect_x_axis_abcissa;
+  double cross_x;
+  key_map abs_independent;
 
   /**
     Filter cost functions
   */
-  /* Cost to lookup into filter */
-  inline double lookup_cost()
-  {
-    return log(est_elements)*0.01;
-  }
 
   Range_filter_cost_info() : table(0), key_no(0) {}
 
-  void init(TABLE *tab, uint key_numb);
+  void init(Rowid_filter_container_type cont_type,
+            TABLE *tab, uint key_numb);
 
   double build_cost(Rowid_filter_container_type container_type);
 
-  inline double get_intersect_x(Range_filter_cost_info *filter)
+  inline double lookup_cost(Rowid_filter_container_type cont_type);
+
+  inline double
+  avg_access_and_eval_gain_per_row(Rowid_filter_container_type cont_type);
+
+  /**
+    Get the gain that usage of filter promises for 'rows' key entries
+  */
+  inline double get_gain(double rows)
+  {
+    return rows * a - b;
+  }
+
+  inline double get_adjusted_gain(double rows, double worst_seeks)
   {
-    if (a == filter->a)
-      return DBL_MAX;
-    return (b - filter->b)/(a - filter->a);
+    return get_gain(rows) -
+           (1 - selectivity) * (rows - MY_MIN(rows, worst_seeks));
   }
-  inline double get_intersect_y(double intersect_x)
+
+  inline double get_cmp_gain(double rows)
   {
-    if (intersect_x == DBL_MAX)
-      return DBL_MAX;
-    return intersect_x*a - b;
+    return rows * (1 - selectivity) / TIME_FOR_COMPARE;
   }
 
-  /**
-    Get a gain that a usage of filter in some partial join order
-    with the cardinaly card gives
-  */
-  inline double get_filter_gain(double card)
-  {  return card*a - b;  }
 };
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3edf63f..724e156 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1490,6 +1490,7 @@ bool JOIN::make_range_filters()
       key_map filter_map;
       filter_map.clear_all();
       filter_map.set_bit(tab->filter->key_no);
+      filter_map.merge(tab->table->with_impossible_ranges);
       bool force_index_save= tab->table->force_index;
       tab->table->force_index= true;
       (void) sel->test_quick_select(thd, filter_map, (table_map) 0,
@@ -5118,9 +5119,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
         select->quick=0;
         impossible_range= records == 0 && s->table->reginfo.impossible_range;
         if (join->thd->lex->sql_command == SQLCOM_SELECT &&
-            join->table_count > 1 &&
             optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
-          s->table->select_usable_range_filters(join->thd);
+          s->table->init_cost_info_for_usable_range_filters(join->thd);
       }
       if (!impossible_range)
       {
@@ -7328,11 +7328,14 @@ best_access_path(JOIN      *join,
 
       if (records < DBL_MAX)
       {
-        filter= table->best_filter_for_current_join_order(start_key->key,
-                                                        records,
-                                                        record_count);
-        if (filter && (filter->get_filter_gain(record_count*records) < tmp))
-          tmp= tmp - filter->get_filter_gain(record_count*records);
+        double rows= record_count * records;
+        filter= table->best_filter_for_partial_join(start_key->key, rows);
+        if (filter)
+	{
+          tmp-= filter->get_adjusted_gain(rows, s->worst_seeks) -
+	        filter->get_cmp_gain(rows);
+          DBUG_ASSERT(tmp >= 0);
+        }
       }
 
       if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
@@ -7438,6 +7441,7 @@ best_access_path(JOIN      *join,
       Here we estimate its cost.
     */
 
+    filter= 0;
     if (s->quick)
     {
       /*
@@ -7453,6 +7457,18 @@ best_access_path(JOIN      *join,
         (s->quick->read_time +
          (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
 
+      if ( s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
+      {
+        double rows= record_count * s->found_records;
+        uint key_no= s->quick->index;
+        filter= s->table->best_filter_for_partial_join(key_no, rows);
+        if (filter)
+        {
+          tmp-= filter->get_gain(rows);
+          DBUG_ASSERT(tmp >= 0);
+	}
+      }
+
       loose_scan_opt.check_range_access(join, idx, s->quick);
     }
     else
@@ -7498,24 +7514,23 @@ best_access_path(JOIN      *join,
     else
       tmp+= s->startup_cost;
 
-    if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
-    {
-      filter= s->table->best_filter_for_current_join_order(s->quick->index,
-                                                           rnd_records,
-                                                           record_count);
-      if (filter && (filter->get_filter_gain(record_count*rnd_records) < tmp))
-        tmp= tmp - filter->get_filter_gain(record_count*rnd_records);
-    }
-
     /*
       We estimate the cost of evaluating WHERE clause for found records
       as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
       tmp give us total cost of using TABLE SCAN
     */
+
+    double filter_cmp_gain= 0;
+    if (filter)
+    {
+      filter_cmp_gain= filter->get_cmp_gain(record_count * s->found_records);
+    }
+
     if (best == DBL_MAX ||
         (tmp  + record_count/(double) TIME_FOR_COMPARE*rnd_records <
          (best_key->is_for_hash_join() ? best_time :
-          best + record_count/(double) TIME_FOR_COMPARE*records)))
+          best + record_count/(double) TIME_FOR_COMPARE*records -
+          filter_cmp_gain)))
     {
       /*
         If the table has a range (s->quick is set) make_join_select()
@@ -12708,9 +12723,7 @@ ha_rows JOIN_TAB::get_examined_rows()
   double examined_rows;
   SQL_SELECT *sel= filesort? filesort->select : this->select;
 
-  if (filter)
-    examined_rows= records_read;
-  else if (sel && sel->quick && use_quick != 2)
+  if (sel && sel->quick && use_quick != 2)
     examined_rows= (double)sel->quick->records;
   else if (type == JT_NEXT || type == JT_ALL ||
            type == JT_HASH || type ==JT_HASH_NEXT)
@@ -22477,6 +22490,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
         tab->use_quick=1;
         tab->ref.key= -1;
         tab->ref.key_parts=0;		// Don't use ref key.
+        tab->filter= 0;
+        if (tab->rowid_filter)
+	{
+          delete tab->rowid_filter;
+          tab->rowid_filter= 0;
+        }
         tab->read_first_record= join_init_read_record;
         if (tab->is_using_loose_index_scan())
           tab->join->tmp_table_param.precomputed_group_by= TRUE;
diff --git a/sql/structs.h b/sql/structs.h
index 5cc64c1..a45cc34 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -120,7 +120,7 @@ typedef struct st_key {
   */
   LEX_CSTRING name;
   key_part_map ext_key_part_map;
-  key_map intersected_with;
+  key_map overlapped;
   uint  block_size;
   enum  ha_key_alg algorithm;
   /* 
diff --git a/sql/table.cc b/sql/table.cc
index a2408d6..67c369f 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1228,12 +1228,13 @@ static const Type_handler *old_frm_type_handler(uint pack_flag,
 }
 
 
-void TABLE_SHARE::set_intersected_keys()
+void TABLE_SHARE::set_overlapped_keys()
 {
   KEY *key1= key_info;
   for (uint i= 0; i < keys; i++, key1++)
   {
-    key1->intersected_with.clear_all();
+    key1->overlapped.clear_all();
+    key1->overlapped.set_bit(i);
   }
   key1= key_info;
   for (uint i= 0; i < keys; i++, key1++)
@@ -1242,18 +1243,23 @@ void TABLE_SHARE::set_intersected_keys()
     for (uint j= i+1; j < keys; j++, key2++)
     {
       KEY_PART_INFO *key_part1= key1->key_part;
-      KEY_PART_INFO *key_part2= key2->key_part;
-      uint n= key1->user_defined_key_parts;
-      set_if_smaller(n, key2->user_defined_key_parts);
-      for (uint k= 0; k < n; k++, key_part1++, key_part2++)
+      uint n1= key1->user_defined_key_parts;
+      uint n2= key2->user_defined_key_parts;
+      for (uint k= 0; k < n1; k++, key_part1++)
       {
-        if (key_part1->fieldnr == key_part2->fieldnr)
+        KEY_PART_INFO *key_part2= key2->key_part;
+        for (uint l= 0; l < n2; l++, key_part2++)
 	{
-          key1->intersected_with.set_bit(j);
-          key2->intersected_with.set_bit(i);
-          break;
+          if (key_part1->fieldnr == key_part2->fieldnr)
+	  {
+            key1->overlapped.set_bit(j);
+            key2->overlapped.set_bit(i);
+            goto end_checking_overlap;
+          }
         }
-      } 
+      }
+    end_checking_overlap:
+      ;
     }
   }
 }
@@ -2553,7 +2559,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
           null_length, 255);
   }
 
-  set_intersected_keys();
+  set_overlapped_keys();
 
   /* Handle virtual expressions */
   if (vcol_screen_length && share->frm_version >= FRM_VER_EXPRESSSIONS)
@@ -4689,8 +4695,9 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
   created= TRUE;
   cond_selectivity= 1.0;
   cond_selectivity_sampling_explain= NULL;
-  best_filter_count= 0;
-  range_filter_cost_info_elements= 0;
+  range_filter_cost_info_elems= 0;
+  range_filter_cost_info_ptr= NULL;
+  range_filter_cost_info= NULL;
 #ifdef HAVE_REPLICATION
   /* used in RBR Triggers */
   master_had_triggers= 0;
diff --git a/sql/table.h b/sql/table.h
index a027c85..feeb9ee 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1003,7 +1003,7 @@ struct TABLE_SHARE
   /* frees the memory allocated in read_frm_image */
   void free_frm_image(const uchar *frm);
 
-  void set_intersected_keys();
+  void set_overlapped_keys();
 };
 
 
@@ -1503,21 +1503,14 @@ struct TABLE
   double get_materialization_cost(); // Now used only if is_splittable()==true
   void add_splitting_info_for_key_field(struct KEY_FIELD *key_field);
 
-
-  /**
-    Range filter info
-  */
-  /* Minimum possible #T value to apply filter*/
-  uint best_filter_count;
-  uint range_filter_cost_info_elements;
+  key_map with_impossible_ranges;
+  uint range_filter_cost_info_elems;
+  Range_filter_cost_info **range_filter_cost_info_ptr;
   Range_filter_cost_info *range_filter_cost_info;
-  Range_filter_cost_info
-    *best_filter_for_current_join_order(uint ref_key_no,
-                                        double record_count,
-                                        double records);
-  void sort_range_filter_cost_info_array();
+  void init_cost_info_for_usable_range_filters(THD *thd);
   void prune_range_filters();
-  void select_usable_range_filters(THD *thd);
+  Range_filter_cost_info *best_filter_for_partial_join(uint access_key_no,
+                                                       double records);
   /**
     System Versioning support
    */
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index e43019b..8a092d6 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -5250,7 +5250,8 @@ ha_innobase::index_flags(
 	ulong flags = HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER
 		      | HA_READ_RANGE | HA_KEYREAD_ONLY
 		      | extra_flag
-		      | HA_DO_INDEX_COND_PUSHDOWN;
+		      | HA_DO_INDEX_COND_PUSHDOWN
+                      | HA_DO_RANGE_FILTER_PUSHDOWN;
 
 	/* For spatial index, we don't support descending scan
 	and ICP so far. */
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index de7a71d..012691e 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -769,7 +769,8 @@ ulong ha_myisam::index_flags(uint inx, uint part, bool all_parts) const
   else 
   {
     flags= HA_READ_NEXT | HA_READ_PREV | HA_READ_RANGE |
-          HA_READ_ORDER | HA_KEYREAD_ONLY | HA_DO_INDEX_COND_PUSHDOWN;
+           HA_READ_ORDER | HA_KEYREAD_ONLY | HA_DO_INDEX_COND_PUSHDOWN |
+           HA_DO_RANGE_FILTER_PUSHDOWN;
   }
   return flags;
 }



More information about the commits mailing list