MDEV-21383: Possible range plan is not used under certain conditionsbb-10.3-mdev21383

[Variant 2 of the fix: collect the attached conditions]

Problem:
make_join_select() has a section of code which starts with
 "We plan to scan all rows. Check again if we should use an index."

the code in that section will [unnecessarily] re-run the range
optimizer using this condition:

  condition_attached_to_current_table AND current_table's_ON_expr

Note that the original invocation of range optimizer in
make_join_statistics was done using the whole select's WHERE condition.
Taking the whole select's WHERE condition and using multiple-equalities
allowed the range optimizer to infer more range restrictions.

The fix:
- Do range optimization using a condition that is an AND of this table's
condition and all of the previous tables' conditions.
- Also, fix the range optimizer to prefer SEL_ARGs with type=KEY_RANGE
over SEL_ARGS with type=MAYBE_KEY, regardless of the key part.
Computing
key_and(
  SEL_ARG(type=MAYBE_KEY key_part=1),
  SEL_ARG(type=KEY_RANGE, key_part=2)
)
will now produce the SEL_ARG with type=KEY_RANGE.

4 files changed, 194 insertions, 1 deletions

diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index fb4f35ed555..fe6d18f7807 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3339,3 +3339,59 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	
 drop table t0,t1;
+# 
+# MDEV-21383: Possible range plan is not used under certain conditions
+# 
+drop table if exists t10, t1000, t03;
+create table t10(a int);
+insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1000(a int);
+insert into t1000 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C;
+create table t03(a int);
+insert into t03 values (0),(1),(2);
+create table t1 (
+stationid int
+);
+insert into t1 select a from t10;
+CREATE TABLE t2 (
+stationId int,
+startTime int,
+filler char(100),
+key1 int,
+key2 int,
+key(key1),
+key(key2),
+PRIMARY KEY (`stationId`,`startTime`)
+);
+insert into t2 select
+A.a, 
+B.a,
+repeat('filler=data-', 4),
+B.a,
+1
+from 
+t03 A,
+t1000 B;
+analyze table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+create table t3(a int, filler char(100), key(a));
+insert into t3 select A.a+1000*B.a, 'filler-data' from t1000 A, t10 B;
+# This should produce a join order of t1,t2,t3
+#  t2 should have type=range, key=PRIMARY key_len=8 (not type=ALL or key_len<8)
+explain
+SELECT *
+FROM
+t1,t2,t3
+WHERE   
+t2.startTime <= 100 and
+t2.stationId = t1.stationId and
+(t1.stationid = 1 or t1.stationid = 2 or t1.stationid = 3) and
+key1 >0 and
+t2.key2=t3.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	range	PRIMARY,key1,key2	PRIMARY	8	NULL	219	Using index condition; Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ref	a	a	5	test.t2.key2	1	
+drop table t1,t2,t3;
+drop table t1000,t10,t03;
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index c5d62e213d8..c72ff0e1a8c 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1748,3 +1748,68 @@ show keys from t1;
 explain select * from t0,t1 where t0.a=t1.a;
 
 drop table t0,t1;
+
+--echo # 
+--echo # MDEV-21383: Possible range plan is not used under certain conditions
+--echo # 
+
+--disable_warnings
+drop table if exists t10, t1000, t03;
+--enable_warnings
+
+create table t10(a int);
+insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1000(a int);
+insert into t1000 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C;
+
+create table t03(a int);
+insert into t03 values (0),(1),(2);
+
+
+create table t1 (
+  stationid int
+);
+insert into t1 select a from t10;
+
+CREATE TABLE t2 (
+  stationId int,
+  startTime int,
+  filler char(100),
+  key1 int,
+  key2 int,
+  key(key1),
+  key(key2),
+  PRIMARY KEY (`stationId`,`startTime`)
+);
+
+insert into t2 select
+  A.a, 
+  B.a,
+  repeat('filler=data-', 4),
+  B.a,
+  1
+from 
+  t03 A,
+  t1000 B;
+analyze table t2;
+
+create table t3(a int, filler char(100), key(a));
+insert into t3 select A.a+1000*B.a, 'filler-data' from t1000 A, t10 B;
+
+--echo # This should produce a join order of t1,t2,t3
+--echo #  t2 should have type=range, key=PRIMARY key_len=8 (not type=ALL or key_len<8)
+explain
+SELECT *
+FROM
+   t1,t2,t3
+WHERE   
+  t2.startTime <= 100 and
+  t2.stationId = t1.stationId and
+  (t1.stationid = 1 or t1.stationid = 2 or t1.stationid = 3) and
+  key1 >0 and
+  t2.key2=t3.a;
+
+drop table t1,t2,t3;
+drop table t1000,t10,t03;
+
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 32e6a767f15..bd5f25d67c2 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -8999,6 +8999,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2,
   }
   if (key1->type == SEL_ARG::MAYBE_KEY)
   {
+    if (key2->type == SEL_ARG::KEY_RANGE)
+      return key2;
     key1->right= key1->left= &null_element;
     key1->next= key1->prev= 0;
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 87a45a1baed..ae26458d451 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10543,6 +10543,74 @@ make_outerjoin_info(JOIN *join)
 }
 
 
+/*
+  @brief
+    Build a temporary join prefix condition for JOIN_TABs up to the last tab
+
+  @param  ret  OUT  the condition is returned here
+
+  @return
+     false  OK
+     true   Out of memory
+
+  @detail
+    Walk through the join prefix (from the first table to the last_tab) and
+    build a condition:
+
+    join_tab_1_cond AND join_tab_2_cond AND ... AND last_tab_conds
+
+    The condition is only intended to be used by the range optimizer, so:
+    - it is not normalized (can have Item_cond_and inside another
+      Item_cond_and)
+    - it does not include join->exec_const_cond and other similar conditions.
+*/
+
+bool build_tmp_join_prefix_cond(JOIN *join, JOIN_TAB *last_tab, Item **ret)
+{
+  THD *const thd= join->thd;
+  Item_cond_and *all_conds= NULL;
+
+  Item *res= NULL;
+
+  // Pick the ON-expression. Use the same logic as in get_sargable_cond():
+  if (last_tab->on_expr_ref)
+    res= *last_tab->on_expr_ref;
+  else if (last_tab->table->pos_in_table_list &&
+           last_tab->table->pos_in_table_list->embedding &&
+           !last_tab->table->pos_in_table_list->embedding->sj_on_expr)
+  {
+    res= last_tab->table->pos_in_table_list->embedding->on_expr;
+  }
+
+  for (JOIN_TAB *tab= first_depth_first_tab(join);
+       tab;
+       tab= next_depth_first_tab(join, tab))
+  {
+    if (tab->select_cond)
+    {
+      if (!res)
+        res= tab->select_cond;
+      else
+      {
+        if (!all_conds)
+        {
+          if (!(all_conds= new (thd->mem_root)Item_cond_and(thd, res,
+                                                            tab->select_cond)))
+            return true;
+          res= all_conds;
+        }
+        else
+          all_conds->add(tab->select_cond, thd->mem_root);
+      }
+    }
+    if (tab == last_tab)
+      break;
+  }
+  *ret= all_conds? all_conds: res;
+  return false;
+}
+
+
 static bool
 make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 {
@@ -10890,7 +10958,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 	  {
 	    /* Join with outer join condition */
 	    COND *orig_cond=sel->cond;
-	    sel->cond= and_conds(thd, sel->cond, *tab->on_expr_ref);
+
+            if (build_tmp_join_prefix_cond(join, tab, &sel->cond))
+              return true;
 
 	    /*
               We can't call sel->cond->fix_fields,