1 files changed, 59 insertions, 37 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9a64055f2e3..f05bf555816 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -165,13 +165,15 @@ static COND *make_cond_for_table(COND *cond,table_map table,
 static Item* part_of_refkey(TABLE *form,Field *field);
 uint find_shortest_key(TABLE *table, const key_map *usable_keys);
 static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
-				    ha_rows select_limit, bool no_changes);
+				    ha_rows select_limit, bool no_changes,
+                                    key_map *map);
 static bool list_contains_unique_index(TABLE *table,
                           bool (*find_func) (Field *, void *), void *data);
 static bool find_field_in_item_list (Field *field, void *data);
 static bool find_field_in_order_list (Field *field, void *data);
 static int create_sort_index(THD *thd, JOIN *join, ORDER *order,
-			     ha_rows filesort_limit, ha_rows select_limit);
+			     ha_rows filesort_limit, ha_rows select_limit,
+                             bool is_order_by);
 static int remove_duplicates(JOIN *join,TABLE *entry,List<Item> &fields,
 			     Item *having);
 static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field,
@@ -916,14 +918,15 @@ JOIN::optimize()
     JOIN_TAB *tab= &join_tab[const_tables];
     bool all_order_fields_used;
     if (order)
-      skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1);
+      skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1, 
+        &tab->table->keys_in_use_for_order_by);
     if ((group_list=create_distinct_group(thd, select_lex->ref_pointer_array,
                                           order, fields_list,
 				          &all_order_fields_used)))
     {
       bool skip_group= (skip_sort_order &&
-			test_if_skip_sort_order(tab, group_list, select_limit,
-						1) != 0);
+        test_if_skip_sort_order(tab, group_list, select_limit, 1, 
+                                &tab->table->keys_in_use_for_group_by) != 0);
       if ((skip_group && all_order_fields_used) ||
 	  select_limit == HA_POS_ERROR ||
 	  (order && !skip_sort_order))
@@ -1113,7 +1116,9 @@ JOIN::optimize()
         ((group_list &&
           (!simple_group ||
            !test_if_skip_sort_order(&join_tab[const_tables], group_list,
-                                    unit->select_limit_cnt, 0))) ||
+                                    unit->select_limit_cnt, 0, 
+                                    &join_tab[const_tables].table->
+                                    keys_in_use_for_group_by))) ||
          select_distinct) &&
         tmp_table_param.quick_group && !procedure)
     {
@@ -1215,7 +1220,7 @@ JOIN::optimize()
       DBUG_PRINT("info",("Sorting for group"));
       thd->proc_info="Sorting for group";
       if (create_sort_index(thd, this, group_list,
-			    HA_POS_ERROR, HA_POS_ERROR) ||
+			    HA_POS_ERROR, HA_POS_ERROR, FALSE) ||
 	  alloc_group_fields(this, group_list) ||
           make_sum_func_list(all_fields, fields_list, 1) ||
           setup_sum_funcs(thd, sum_funcs))
@@ -1232,7 +1237,7 @@ JOIN::optimize()
 	DBUG_PRINT("info",("Sorting for order"));
 	thd->proc_info="Sorting for order";
 	if (create_sort_index(thd, this, order,
-                              HA_POS_ERROR, HA_POS_ERROR))
+                              HA_POS_ERROR, HA_POS_ERROR, TRUE))
 	  DBUG_RETURN(1);
 	order=0;
       }
@@ -1259,7 +1264,9 @@ JOIN::optimize()
       {
  	/* Should always succeed */
 	if (test_if_skip_sort_order(&join_tab[const_tables],
-				    order, unit->select_limit_cnt, 0))
+				    order, unit->select_limit_cnt, 0, 
+                                    &join_tab[const_tables].table->
+                                      keys_in_use_for_order_by))
 	  order=0;
       }
     }
@@ -1452,7 +1459,9 @@ JOIN::exec()
 	(const_tables == tables ||
  	 ((simple_order || skip_sort_order) &&
 	  test_if_skip_sort_order(&join_tab[const_tables], order,
-				  select_limit, 0))))
+				  select_limit, 0, 
+                                  &join_tab[const_tables].table->
+                                    keys_in_use_for_order_by))))
       order=0;
     having= tmp_having;
     select_describe(this, need_tmp,
@@ -1628,7 +1637,7 @@ JOIN::exec()
 	  DBUG_VOID_RETURN;
 	}
 	if (create_sort_index(thd, curr_join, curr_join->group_list,
-			      HA_POS_ERROR, HA_POS_ERROR) ||
+			      HA_POS_ERROR, HA_POS_ERROR, FALSE) ||
 	    make_group_fields(this, curr_join))
 	{
 	  DBUG_VOID_RETURN;
@@ -1844,7 +1853,8 @@ JOIN::exec()
 			    curr_join->group_list : curr_join->order,
 			    curr_join->select_limit,
 			    (select_options & OPTION_FOUND_ROWS ?
-			     HA_POS_ERROR : unit->select_limit_cnt)))
+			     HA_POS_ERROR : unit->select_limit_cnt),
+                            curr_join->group_list ? TRUE : FALSE))
 	DBUG_VOID_RETURN;
       sortorder= curr_join->sortorder;
       if (curr_join->const_tables != curr_join->tables &&
@@ -3842,7 +3852,7 @@ best_access_path(JOIN      *join,
             /* Limit the number of matched rows */
             tmp= records;
             set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
-            if (table->used_keys.is_set(key))
+            if (table->covering_keys.is_set(key))
             {
               /* we can use only index tree */
               uint keys_per_block= table->file->stats.block_size/2/
@@ -4009,7 +4019,7 @@ best_access_path(JOIN      *join,
 
             /* Limit the number of matched rows */
             set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
-            if (table->used_keys.is_set(key))
+            if (table->covering_keys.is_set(key))
             {
               /* we can use only index tree */
               uint keys_per_block= table->file->stats.block_size/2/
@@ -4068,7 +4078,7 @@ best_access_path(JOIN      *join,
       !(s->quick && best_key && s->quick->index == best_key->key &&      // (2)
         best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
       !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&   // (3)
-        ! s->table->used_keys.is_clear_all() && best_key) &&             // (3)
+        ! s->table->covering_keys.is_clear_all() && best_key) &&         // (3)
       !(s->table->force_index && best_key && !s->quick))                 // (4)
   {                                             // Check full join
     ha_rows rnd_records= s->found_records;
@@ -5965,7 +5975,7 @@ make_join_readinfo(JOIN *join, ulonglong options)
         (table == join->sort_by_table &&
          (!join->order || join->skip_sort_order ||
           test_if_skip_sort_order(tab, join->order, join->select_limit,
-                                  1))
+                                  1, &table->keys_in_use_for_order_by))
         ) ||
         (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
       ordered_set= 1;
@@ -5982,7 +5992,7 @@ make_join_readinfo(JOIN *join, ulonglong options)
       table->status=STATUS_NO_RECORD;
       tab->read_first_record= join_read_const;
       tab->read_record.read_record= join_no_more_records;
-      if (table->used_keys.is_set(tab->ref.key) &&
+      if (table->covering_keys.is_set(tab->ref.key) &&
           !table->no_keyread)
       {
         table->key_read=1;
@@ -6000,7 +6010,7 @@ make_join_readinfo(JOIN *join, ulonglong options)
       tab->quick=0;
       tab->read_first_record= join_read_key;
       tab->read_record.read_record= join_no_more_records;
-      if (table->used_keys.is_set(tab->ref.key) &&
+      if (table->covering_keys.is_set(tab->ref.key) &&
 	  !table->no_keyread)
       {
 	table->key_read=1;
@@ -6017,7 +6027,7 @@ make_join_readinfo(JOIN *join, ulonglong options)
       }
       delete tab->quick;
       tab->quick=0;
-      if (table->used_keys.is_set(tab->ref.key) &&
+      if (table->covering_keys.is_set(tab->ref.key) &&
 	  !table->no_keyread)
       {
 	table->key_read=1;
@@ -6103,15 +6113,15 @@ make_join_readinfo(JOIN *join, ulonglong options)
 	{
 	  if (tab->select && tab->select->quick &&
               tab->select->quick->index != MAX_KEY && //not index_merge
-	      table->used_keys.is_set(tab->select->quick->index))
+	      table->covering_keys.is_set(tab->select->quick->index))
 	  {
 	    table->key_read=1;
 	    table->file->extra(HA_EXTRA_KEYREAD);
 	  }
-	  else if (!table->used_keys.is_clear_all() &&
+	  else if (!table->covering_keys.is_clear_all() &&
 		   !(tab->select && tab->select->quick))
 	  {					// Only read index tree
-	    tab->index=find_shortest_key(table, & table->used_keys);
+	    tab->index=find_shortest_key(table, & table->covering_keys);
 	    tab->read_first_record= join_read_first;
 	    tab->type=JT_NEXT;		// Read with index_first / index_next
 	  }
@@ -9179,7 +9189,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
   table->copy_blobs= 1;
   table->in_use= thd;
   table->quick_keys.init();
-  table->used_keys.init();
+  table->covering_keys.init();
   table->keys_in_use_for_query.init();
 
   table->s= share;
@@ -10804,7 +10814,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos)
   }
   else
   {
-    if (!table->key_read && table->used_keys.is_set(tab->ref.key) &&
+    if (!table->key_read && table->covering_keys.is_set(tab->ref.key) &&
 	!table->no_keyread &&
         (int) table->reginfo.lock_type <= (int) TL_READ_HIGH_PRIORITY)
     {
@@ -11109,7 +11119,7 @@ join_read_first(JOIN_TAB *tab)
 {
   int error;
   TABLE *table=tab->table;
-  if (!table->key_read && table->used_keys.is_set(tab->index) &&
+  if (!table->key_read && table->covering_keys.is_set(tab->index) &&
       !table->no_keyread)
   {
     table->key_read=1;
@@ -11148,7 +11158,7 @@ join_read_last(JOIN_TAB *tab)
 {
   TABLE *table=tab->table;
   int error;
-  if (!table->key_read && table->used_keys.is_set(tab->index) &&
+  if (!table->key_read && table->covering_keys.is_set(tab->index) &&
       !table->no_keyread)
   {
     table->key_read=1;
@@ -12170,7 +12180,7 @@ find_field_in_item_list (Field *field, void *data)
 
 static bool
 test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
-			bool no_changes)
+			bool no_changes, key_map *map)
 {
   int ref_key;
   uint ref_key_parts;
@@ -12184,9 +12194,16 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
     Check which keys can be used to resolve ORDER BY.
     We must not try to use disabled keys.
   */
-  usable_keys= table->s->keys_in_use;
-  /* we must not consider keys that are disabled by IGNORE INDEX */
-  usable_keys.intersect(table->keys_in_use_for_query);
+  usable_keys= *map;
+
+  /* 
+    If there is a covering index, and we have IGNORE INDEX FOR GROUP/ORDER
+    and this index is used for the JOIN part, then we have to ignore the
+    IGNORE INDEX FOR GROUP/ORDER
+  */  
+  if (table->key_read ||
+      (table->covering_keys.is_set(tab->index) && !table->no_keyread))
+    usable_keys.set_bit (tab->index);
 
   for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
   {
@@ -12244,8 +12261,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 	If using index only read, only consider other possible index only
 	keys
       */
-      if (table->used_keys.is_set(ref_key))
-	usable_keys.intersect(table->used_keys);
+      if (table->covering_keys.is_set(ref_key))
+	usable_keys.intersect(table->covering_keys);
       if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
 				       &usable_keys)) < MAX_KEY)
       {
@@ -12360,7 +12377,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
     if (select_limit >= table->file->stats.records)
     {
       keys= *table->file->keys_to_use_for_scanning();
-      keys.merge(table->used_keys);
+      keys.merge(table->covering_keys);
 
       /*
 	We are adding here also the index specified in FORCE INDEX clause, 
@@ -12388,7 +12405,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 	    tab->read_first_record=  (flag > 0 ? join_read_first:
 				      join_read_last);
 	    tab->type=JT_NEXT;	// Read with index_first(), index_next()
-	    if (table->used_keys.is_set(nr))
+	    if (table->covering_keys.is_set(nr))
 	    {
 	      table->key_read=1;
 	      table->file->extra(HA_EXTRA_KEYREAD);
@@ -12414,6 +12431,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
      filesort_limit	Max number of rows that needs to be sorted
      select_limit	Max number of rows in final output
 		        Used to decide if we should use index or not
+     is_order_by        true if we are sorting on ORDER BY, false if GROUP BY
+                        Used to decide if we should use index or not     
 
 
   IMPLEMENTATION
@@ -12432,7 +12451,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 
 static int
 create_sort_index(THD *thd, JOIN *join, ORDER *order,
-		  ha_rows filesort_limit, ha_rows select_limit)
+		  ha_rows filesort_limit, ha_rows select_limit,
+                  bool is_order_by)
 {
   uint length;
   ha_rows examined_rows;
@@ -12453,7 +12473,9 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   */
   if ((order != join->group_list || 
        !(join->select_options & SELECT_BIG_RESULT)) &&
-      test_if_skip_sort_order(tab,order,select_limit,0))
+      test_if_skip_sort_order(tab,order,select_limit,0, 
+                              is_order_by ?  &table->keys_in_use_for_order_by :
+                              &table->keys_in_use_for_group_by))
     DBUG_RETURN(0);
   if (!(join->sortorder= 
         make_unireg_sortorder(order,&length,join->sortorder)))
@@ -15047,7 +15069,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
       /* Build "Extra" field and add it to item_list. */
       my_bool key_read=table->key_read;
       if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
-          table->used_keys.is_set(tab->index))
+          table->covering_keys.is_set(tab->index))
 	key_read=1;
       if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
           !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)