MDEV-12580 Wrong query result in join when using an index (Version > "10.2.3")

JOIN_TAB::remove_redundant_bnl_scan_conds() removes select_cond
from a JOIN_TAB if join cache is enabled, and tab->cache_select->cond
is the equal to tab->select_cond.

But after 8d99166c69 the code to initialize join cache was moved
to happen much later than JOIN_TAB::remove_redundant_bnl_scan_conds(),
and that code might, under certain conditions, revert to *not* using
join cache (set_join_cache_denial()).

If JOIN_TAB::remove_redundant_bnl_scan_conds() removes the WHERE
condition from the JOIN_TAB and later set_join_cache_denial() disables
join cache, we end up with no WHERE condition at all.

Fix: move JOIN_TAB::remove_redundant_bnl_scan_conds() to happen
after all possible set_join_cache_denial() calls.

1 files changed, 4 insertions, 3 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a685dd73956..c703e58d237 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1138,6 +1138,8 @@ int JOIN::init_join_caches()
     }
     if (tab->cache && tab->cache->init(select_options & SELECT_DESCRIBE))
       revise_cache_usage(tab);
+    else
+      tab->remove_redundant_bnl_scan_conds();
   }
   return 0;
 }
@@ -11161,8 +11163,8 @@ void JOIN_TAB::remove_redundant_bnl_scan_conds()
     select->cond is not processed separately. This method assumes it is always
     the same as select_cond.
   */
-  DBUG_ASSERT(!select || !select->cond ||
-              (select->cond == select_cond));
+  if (select && select->cond != select_cond)
+    return;
 
   if (is_cond_and(select_cond))
   {
@@ -11472,7 +11474,6 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
       /* purecov: end */
     }
 
-    tab->remove_redundant_bnl_scan_conds();
     DBUG_EXECUTE("where",
                  char buff[256];
                  String str(buff,sizeof(buff),system_charset_info);