MDEV-21628: Index condition pushdown condition ... not used with BKA

(Backport to 10.3)
Partitioning storage now supports MRR but doesn't support Index Condition
Pushdown (aka ICP). This causes counter-intuitive query plans for queries
that use BKA and conditions that depend on index fields:
- If the condition refers to other tables, BKA's variant of ICP is used
   to handle it.
- If the condition depends on this table only, the optimizer will try to
  use regular ICP for it, which will fail because the storage engine
  doesn't support ICP.

Make the optimizer be smarter in the second case: if we were not able to
use regular ICP, use BKA's variant of ICP..

3 files changed, 133 insertions, 0 deletions

diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result
index 1f1cea8e9d6..d989536324c 100644
--- a/mysql-test/main/partition_mrr_myisam.result
+++ b/mysql-test/main/partition_mrr_myisam.result
@@ -77,3 +77,62 @@ ID	part_id	key_col	col2
 28	8	2	123456
 29	9	2	123456
 drop table t1,t3;
+#
+# MDEV-21628: Index condition pushdown for a simple condition over 
+# index fields is not used for ref access of partitioned tables when employing BKA
+#
+create table t0 (
+tp int, a int, b int, c varchar(12), index idx (a,b)
+);
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+create table t1 (
+tp int, a int, b int, c varchar(12), index idx (a,b)
+) engine=myisam
+partition by list (tp)
+( partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3));
+insert into t1 select * from t0;
+create table t2 (a int, index idx(a)) engine=myisam;
+insert into t2 values (1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+analyze table t0,t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t0	analyze	status	Engine-independent statistics collected
+test.t0	analyze	status	OK
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch;
+set join_cache_level=6, optimizer_switch='mrr=on';
+explain
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	Using where; Using index
+1	SIMPLE	t0	ref	idx	idx	5	test.t2.a	12	Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+# This will use "Using index condition(BKA)"
+explain
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	Using where; Using index
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.a	12	Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+set join_cache_level=@tmp1, optimizer_switch=@tmp2;
+drop table t0,t1,t2;
diff --git a/mysql-test/main/partition_mrr_myisam.test b/mysql-test/main/partition_mrr_myisam.test
index d67a37ab3d2..eb30319d81f 100644
--- a/mysql-test/main/partition_mrr_myisam.test
+++ b/mysql-test/main/partition_mrr_myisam.test
@@ -1,3 +1,62 @@
 let $engine_type= myisam;
 
 --source include/partition_mrr.inc
+
+--echo #
+--echo # MDEV-21628: Index condition pushdown for a simple condition over 
+--echo # index fields is not used for ref access of partitioned tables when employing BKA
+--echo #
+
+create table t0 (
+tp int, a int, b int, c varchar(12), index idx (a,b)
+);
+
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+ 
+create table t1 (
+tp int, a int, b int, c varchar(12), index idx (a,b)
+) engine=myisam
+partition by list (tp)
+( partition p1 values in (1),
+  partition p2 values in (2),
+  partition p3 values in (3));
+insert into t1 select * from t0;
+ 
+create table t2 (a int, index idx(a)) engine=myisam;
+insert into t2 values (1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+
+analyze table t0,t1,t2;
+
+set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch;
+set join_cache_level=6, optimizer_switch='mrr=on';
+
+explain
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+
+--echo # This will use "Using index condition(BKA)"
+explain
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+
+set join_cache_level=@tmp1, optimizer_switch=@tmp2;
+
+drop table t0,t1,t2;
+
+
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index 8f3f9cdac72..af6a75cdef2 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -393,8 +393,23 @@ void push_index_cond(JOIN_TAB *tab, uint keyno)
            ~(tab->table->map | tab->join->const_table_map)))
         tab->cache_idx_cond= idx_cond;
       else
+      {
         idx_remainder_cond= tab->table->file->idx_cond_push(keyno, idx_cond);
 
+        /*
+          If (1) there is an index condition that we couldn't push using ICP,
+             (2) we are using Join Buffering
+             (3) and we are using BKA
+          then use BKA's Index Condition Pushdown mechanism to check it.
+        */
+        if (idx_remainder_cond && tab->use_join_cache &&   // (1) && (2)
+            tab->icp_other_tables_ok)                      // (3)
+        {
+          tab->cache_idx_cond= idx_remainder_cond;
+          idx_remainder_cond= NULL;
+        }
+      }
+
       /*
         Disable eq_ref's "lookup cache" if we've pushed down an index
         condition.