MDEV-9959: A serious MariaDB server performance bug10.3-MDEV-9959

Step #2: If any field in the select list of the derived tables is present in the group by list also , then we are again guaranteed that ref access to the derived table would always produce one row per key.

3 files changed, 28 insertions, 3 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b169b9e0b27..336fdfe5560 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7459,6 +7459,23 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond,
   return 0;
 }
 
+bool st_select_lex::select_items_in_group_by()
+{
+  if (!group_list.elements)
+    return false;
+  for (ORDER *order= group_list.first; order; order= order->next)
+  {
+    List_iterator_fast<Item> it(item_list);
+    Item *item;
+    while ((item= it++))
+    {
+      if (item->eq(*order->item,false))
+        return true;
+    }
+  }
+  return false;
+}
+
 
 int set_statement_var_if_exists(THD *thd, const char *var_name,
                                 size_t var_name_length, ulonglong value)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 4128c1dd300..531fa5495f9 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1274,6 +1274,7 @@ public:
 
   bool cond_pushdown_is_allowed() const
   { return !olap && !explicit_limit && !tvc; }
+  bool select_items_in_group_by();
   
 private:
   bool m_non_agg_field_used;
diff --git a/sql/table.cc b/sql/table.cc
index d2938a29ea6..f6d4a4ddc63 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7126,10 +7126,17 @@ bool TABLE::add_tmp_key(uint key, uint key_parts,
       This handles the case when we have a single select in the derived table
     */
     st_select_lex* first= derived->first_select();
-    if ((first && !first->is_part_of_union() && 
-        first->options & SELECT_DISTINCT) ||
-        derived->check_distinct_in_union())
+    if (first)
+    {
+      if (!first->is_part_of_union() && first->options & SELECT_DISTINCT)
         keyinfo->rec_per_key[key_parts-1]=1;
+      else
+      {
+        if (derived->check_distinct_in_union() || 
+           (first->select_items_in_group_by()))
+          keyinfo->rec_per_key[key_parts-1]=1;
+      }
+    }
   }
 
   keyinfo->read_stats= NULL;