1 files changed, 91 insertions, 1 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 35aac145e5e..eb0d16ff324 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10541,6 +10541,86 @@ restart:
   }
 }
 
+/**
+  Remove pushdown conditions that are already checked by the scan phase
+  of BNL/BNLH joins.
+
+  @note
+  If the single-table condition for this table will be used by a
+  blocked join to pre-filter this table's rows, there is no need
+  to re-check the same single-table condition for each joined record.
+
+  This method removes from JOIN_TAB::select_cond and JOIN_TAB::select::cond
+  all top-level conjuncts that also appear in in JOIN_TAB::cache_select::cond.
+*/
+
+void JOIN_TAB::remove_redundant_bnl_scan_conds()
+{
+  if (!(select_cond && cache_select && cache &&
+        (cache->get_join_alg() == JOIN_CACHE::BNL_JOIN_ALG ||
+         cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG)))
+    return;
+
+  /*
+    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 (is_cond_and(select_cond))
+  {
+    List_iterator<Item> pushed_cond_li(*((Item_cond*) select_cond)->argument_list());
+    Item *pushed_item;
+    Item_cond_and *reduced_select_cond= new Item_cond_and;
+
+    if (is_cond_and(cache_select->cond))
+    {
+      List_iterator<Item> scan_cond_li(*((Item_cond*) cache_select->cond)->argument_list());
+      Item *scan_item;
+      while ((pushed_item= pushed_cond_li++))
+      {
+        bool found= false;
+        scan_cond_li.rewind();
+        while ((scan_item= scan_cond_li++))
+        {
+          if (pushed_item->eq(scan_item, 0))
+          {
+            found= true;
+            break;
+          }
+        }
+        if (!found)
+          reduced_select_cond->add(pushed_item);
+      }
+    }
+    else
+    {
+      while ((pushed_item= pushed_cond_li++))
+      {
+        if (!pushed_item->eq(cache_select->cond, 0))
+          reduced_select_cond->add(pushed_item);
+      }
+    }
+
+    /*
+      JOIN_CACHE::check_match uses JOIN_TAB::select->cond instead of
+      JOIN_TAB::select_cond. set_cond() sets both pointers.
+    */
+    if (reduced_select_cond->argument_list()->is_empty())
+      set_cond(NULL);
+    else if (reduced_select_cond->argument_list()->elements == 1)
+      set_cond(reduced_select_cond->argument_list()->head());
+    else
+    {
+      reduced_select_cond->quick_fix_field();
+      set_cond(reduced_select_cond);
+    }
+  }
+  else if (select_cond->eq(cache_select->cond, 0))
+    set_cond(NULL);
+}
+
 
 /*
   Plan refinement stage: do various setup things for the executor
@@ -10790,6 +10870,15 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
       abort();
       /* purecov: end */
     }
+
+    tab->remove_redundant_bnl_scan_conds();
+    DBUG_EXECUTE("where",
+                 char buff[256];
+                 String str(buff,sizeof(buff),system_charset_info);
+                 str.length(0);
+                 str.append(tab->table? tab->table->alias.c_ptr() :"<no_table_name>");
+                 str.append(" final_pushdown_cond");
+                 print_where(tab->select_cond, str.c_ptr_safe(), QT_ORDINARY););
   }
   uint n_top_tables= join->join_tab_ranges.head()->end -  
                      join->join_tab_ranges.head()->start;
@@ -23177,7 +23266,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
             eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD);
             eta->range_checked_map= tab->keys;
 	  }
-	  else if (tab->select->cond)
+	  else if (tab->select->cond ||
+                   (tab->cache_select && tab->cache_select->cond))
           {
             const COND *pushed_cond= tab->table->file->pushed_cond;