Merge of WL#1972 "Evaluate HAVING before SELECT select-list"

sql/mysql_priv.h:
  Auto merged
sql/sp.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_help.cc:
  Auto merged
sql/item.cc:
  manual merge
sql/sql_select.cc:
  manul merge

6 files changed, 673 insertions, 309 deletions

diff --git a/sql/item.cc b/sql/item.cc
index 9e28793493d..59126603544 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -26,6 +26,7 @@
 #include "sql_acl.h"
 #include "sp_head.h"
 #include "sql_trigger.h"
+#include "sql_select.h"
 
 static void mark_as_dependent(THD *thd,
 			      SELECT_LEX *last, SELECT_LEX *current,
@@ -1532,170 +1533,420 @@ static void mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
 }
 
 
-bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
+
+
+/*
+  Search a GROUP BY clause for a field with a certain name.
+
+  SYNOPSIS
+    find_field_in_group_list()
+    find_item  the item being searched for
+    group_list GROUP BY clause
+
+  DESCRIPTION
+    Search the GROUP BY list for a column named as find_item. When searching
+    preference is given to columns that are qualified with the same table (and
+    database) name as the one being searched for.
+
+  RETURN
+    - the found item on success
+    - NULL if find_item is not in group_list
+*/
+
+static Item** find_field_in_group_list(Item *find_item, ORDER *group_list)
+{
+  const char *db_name;
+  const char *table_name;
+  const char *field_name;
+  ORDER      *found_group= NULL;
+  int         found_match_degree= 0;
+  Item_field *cur_field;
+  int         cur_match_degree= 0;
+
+  if (find_item->type() == Item::FIELD_ITEM ||
+      find_item->type() == Item::REF_ITEM)
+  {
+    db_name=    ((Item_ident*) find_item)->db_name;
+    table_name= ((Item_ident*) find_item)->table_name;
+    field_name= ((Item_ident*) find_item)->field_name;
+  }
+  else
+    return NULL;
+
+  DBUG_ASSERT(field_name);
+
+  for (ORDER *cur_group= group_list ; cur_group ; cur_group= cur_group->next)
+  {
+    if ((*(cur_group->item))->type() == Item::FIELD_ITEM)
+    {
+      cur_field= (Item_field*) *cur_group->item;
+      cur_match_degree= 0;
+      
+      DBUG_ASSERT(cur_field->field_name);
+
+      if (!my_strcasecmp(system_charset_info,
+                         cur_field->field_name, field_name))
+        ++cur_match_degree;
+      else
+        continue;
+
+      if (cur_field->table_name && table_name)
+      {
+        /* If field_name is qualified by a table name. */
+        if (strcmp(cur_field->table_name, table_name))
+          /* Same field names, different tables. */
+          return NULL;
+
+        ++cur_match_degree;
+        if (cur_field->db_name && db_name)
+        {
+          /* If field_name is also qualified by a database name. */
+          if (strcmp(cur_field->db_name, db_name))
+            /* Same field names, different databases. */
+            return NULL;
+          ++cur_match_degree;
+        }
+      }
+
+      if (cur_match_degree > found_match_degree)
+      {
+        found_match_degree= cur_match_degree;
+        found_group= cur_group;
+      }
+      else if (found_group && (cur_match_degree == found_match_degree) &&
+               ! (*(found_group->item))->eq(cur_field, 0))
+      {
+        /*
+          If the current resolve candidate matches equally well as the current
+          best match, they must reference the same column, otherwise the field
+          is ambiguous.
+        */
+        my_printf_error(ER_NON_UNIQ_ERROR, ER(ER_NON_UNIQ_ERROR),
+                        MYF(0), find_item->full_name(), current_thd->where);
+        return NULL;
+      }
+    }
+  }
+
+  if (found_group)
+    return found_group->item;
+  else
+    return NULL;
+}
+
+
+/*
+  Resolve a column reference in a sub-select.
+
+  SYNOPSIS
+    resolve_ref_in_select_and_group()
+    thd     current thread
+    ref     column reference being resolved
+    select  the sub-select that ref is resolved against
+
+  DESCRIPTION
+    Resolve a column reference (usually inside a HAVING clause) against the
+    SELECT and GROUP BY clauses of the query described by 'select'. The name
+    resolution algorithm searches both the SELECT and GROUP BY clauses, and in
+    case of a name conflict prefers GROUP BY column names over SELECT names. If
+    both clauses contain different fields with the same names, a warning is
+    issued that name of 'ref' is ambiguous. We extend ANSI SQL in that when no
+    GROUP BY column is found, then a HAVING name is resolved as a possibly
+    derived SELECT column.
+
+  NOTES
+    The resolution procedure is:
+    - Search for a column or derived column named col_ref_i [in table T_j]
+      in the SELECT clause of Q.
+    - Search for a column named col_ref_i [in table T_j]
+      in the GROUP BY clause of Q.
+    - If found different columns with the same name in GROUP BY and SELECT
+      - issue a warning and return the GROUP BY column,
+      - otherwise return the found SELECT column.
+
+
+  RETURN
+    NULL - there was an error, and the error was already reported
+    not_found_item - the item was not resolved, no error was reported
+    resolved item - if the item was resolved
+*/
+
+static Item**
+resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select)
+{
+  Item **group_by_ref= NULL;
+  Item **select_ref= NULL;
+  ORDER *group_list= (ORDER*) select->group_list.first;
+  bool ambiguous_fields= FALSE;
+  uint counter;
+  bool not_used;
+
+  /*
+    Search for a column or derived column named as 'ref' in the SELECT
+    clause of the current select.
+  */
+  if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()), &counter,
+                                      REPORT_EXCEPT_NOT_FOUND, &not_used)))
+    return NULL; /* Some error occurred. */
+
+  /* If this is a non-aggregated field inside HAVING, search in GROUP BY. */
+  if (select->having_fix_field && !ref->with_sum_func && group_list)
+  {
+    group_by_ref= find_field_in_group_list(ref, group_list);
+    
+    /* Check if the fields found in SELECT and GROUP BY are the same field. */
+    if (group_by_ref && (select_ref != not_found_item) &&
+        !((*group_by_ref)->eq(*select_ref, 0)))
+    {
+      ambiguous_fields= TRUE;
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
+                          ER(ER_NON_UNIQ_ERROR), ref->full_name(),
+                          current_thd->where);
+
+    }
+  }
+
+  if (select_ref != not_found_item || group_by_ref)
+  {
+    if (select_ref != not_found_item && !ambiguous_fields)
+    {
+      DBUG_ASSERT(*select_ref);
+      if (! (*select_ref)->fixed)
+      {
+        my_error(ER_ILLEGAL_REFERENCE, MYF(0), ref->name,
+                 "forward reference in item list");
+        return NULL;
+      }
+      return (select->ref_pointer_array + counter);
+    }
+    else if (group_by_ref)
+      return group_by_ref;
+    else
+      DBUG_ASSERT(FALSE);
+  }
+  else
+    return (Item**) not_found_item;
+}
+
+
+/*
+  Resolve the name of a column reference.
+
+  SYNOPSIS
+    Item_field::fix_fields()
+    thd       [in]      current thread
+    tables    [in]      the tables in a FROM clause
+    reference [in/out]  view column if this item was resolved to a view column
+
+  DESCRIPTION
+    The method resolves the column reference represented by 'this' as a column
+    present in one of: FROM clause, SELECT clause, GROUP BY clause of a query
+    Q, or in outer queries that contain Q.
+
+  NOTES
+    The name resolution algorithm used is (where [T_j] is an optional table
+    name that qualifies the column name):
+
+      resolve_column_reference([T_j].col_ref_i)
+      {
+        search for a column or derived column named col_ref_i
+        [in table T_j] in the FROM clause of Q;
+
+        if such a column is NOT found AND    // Lookup in outer queries.
+           there are outer queries
+        {
+          for each outer query Q_k beginning from the inner-most one
+          {
+            if - Q_k is not a group query AND
+               - Q_k is not inside an aggregate function
+               OR
+               - Q_(k-1) is not in a HAVING or SELECT clause of Q_k
+            {
+              search for a column or derived column named col_ref_i
+              [in table T_j] in the FROM clause of Q_k;
+            }
+
+            if such a column is not found
+              Search for a column or derived column named col_ref_i
+              [in table T_j] in the SELECT and GROUP clauses of Q_k.
+          }
+        }
+      }
+
+    Notice that compared to Item_ref::fix_fields, here we first search the FROM
+    clause, and then we search the SELECT and GROUP BY clauses.
+
+  RETURN
+    TRUE  if error
+    FALSE on success
+*/
+
+bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **reference)
 {
   DBUG_ASSERT(fixed == 0);
   if (!field)					// If field is not checked
   {
-    bool upward_lookup= 0;
-    Field *tmp= (Field *)not_found_field;
-    if ((tmp= find_field_in_tables(thd, this, tables, ref, 0,
+    bool upward_lookup= FALSE;
+    Field *from_field= (Field *)not_found_field;
+    if ((from_field= find_field_in_tables(thd, this, tables, reference,
+                                   IGNORE_EXCEPT_NON_UNIQUE,
                                    !any_privileges)) ==
 	not_found_field)
     {
-      /*
-	We can't find table field in table list of current select,
-	consequently we have to find it in outer subselect(s).
-	We can't join lists of outer & current select, because of scope
-	of view rules. For example if both tables (outer & current) have
-	field 'field' it is not mistake to refer to this field without
-	mention of table name, but if we join tables in one list it will
-	cause error ER_NON_UNIQ_ERROR in find_field_in_tables.
-      */
-      SELECT_LEX *last= 0;
 #ifdef EMBEDDED_LIBRARY
       thd->net.last_errno= 0;
 #endif
+      SELECT_LEX *last= 0;
       TABLE_LIST *table_list;
-      Item **refer= (Item **)not_found_item;
-      uint counter;
-      bool not_used;
-      // Prevent using outer fields in subselects, that is not supported now
-      SELECT_LEX *cursel= (SELECT_LEX *) thd->lex->current_select;
-      if (cursel->master_unit()->first_select()->linkage != DERIVED_TABLE_TYPE)
+      Item **ref= (Item **) not_found_item;
+      SELECT_LEX *current_sel= (SELECT_LEX *) thd->lex->current_select;
+      /*
+        If there is an outer select, and it is not a derived table (which do
+        not support the use of outer fields for now), try to resolve this
+        reference in the outer select(s).
+      
+        We treat each subselect as a separate namespace, so that different
+        subselects may contain columns with the same names. The subselects are
+        searched starting from the innermost.
+      */
+      if (current_sel->master_unit()->first_select()->linkage !=
+          DERIVED_TABLE_TYPE)
       {
-	SELECT_LEX_UNIT *prev_unit= cursel->master_unit();
-	for (SELECT_LEX *sl= prev_unit->outer_select();
-	     sl;
-	     sl= (prev_unit= sl->master_unit())->outer_select())
+	SELECT_LEX_UNIT *prev_unit= current_sel->master_unit();
+        SELECT_LEX *outer_sel= prev_unit->outer_select();
+	for ( ; outer_sel ;
+              outer_sel= (prev_unit= outer_sel->master_unit())->outer_select())
 	{
-	  upward_lookup= 1;
-	  table_list= (last= sl)->get_table_list();
-	  if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
-	  {
+          last= outer_sel;
+	  Item_subselect *prev_subselect_item= prev_unit->item;
+	  upward_lookup= TRUE;
+
+          /* Search in the tables of the FROM clause of the outer select. */
+	  table_list= outer_sel->get_table_list();
+	  if (outer_sel->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
             /*
-              it is primary INSERT st_select_lex => skip first table
-              resolving
+              It is a primary INSERT st_select_lex => do not resolve against the
+              first table.
             */
 	    table_list= table_list->next_local;
-	  }
 
-	  Item_subselect *prev_subselect_item= prev_unit->item;
           enum_parsing_place place= prev_subselect_item->parsing_place;
           /*
-            check table fields only if subquery used somewhere out of HAVING
-            or outer SELECT do not use groupping (i.e. tables are accessable)
+            Check table fields only if the subquery is used somewhere out of
+            HAVING, or the outer SELECT does not use grouping (i.e. tables are
+            accessible).
           */
           if ((place != IN_HAVING ||
-               (sl->with_sum_func == 0 && sl->group_list.elements == 0)) &&
-              (tmp= find_field_in_tables(thd, this,
-					 table_list, ref,
-					 0, 1)) != not_found_field)
+               (outer_sel->with_sum_func == 0 &&
+                outer_sel->group_list.elements == 0)) &&
+              (from_field= find_field_in_tables(thd, this, table_list,
+                                                reference,
+                                                IGNORE_EXCEPT_NON_UNIQUE,
+                                                TRUE)) !=
+              not_found_field)
 	  {
-	    if (tmp)
+	    if (from_field)
             {
-              if (tmp != view_ref_found)
+              if (from_field != view_ref_found)
               {
-                prev_subselect_item->used_tables_cache|= tmp->table->map;
+                prev_subselect_item->used_tables_cache|= from_field->table->map;
                 prev_subselect_item->const_item_cache= 0;
               }
               else
               {
                 prev_subselect_item->used_tables_cache|=
-                  (*ref)->used_tables();
+                  (*reference)->used_tables();
                 prev_subselect_item->const_item_cache&=
-                  (*ref)->const_item();
+                  (*reference)->const_item();
               }
             }
 	    break;
 	  }
-	  if (sl->resolve_mode == SELECT_LEX::SELECT_MODE &&
-	      (refer= find_item_in_list(this, sl->item_list, &counter,
-                                        REPORT_EXCEPT_NOT_FOUND,
-                                        &not_used)) !=
-	       (Item **) not_found_item)
+
+          /* Search in the SELECT and GROUP lists of the outer select. */
+	  if (outer_sel->resolve_mode == SELECT_LEX::SELECT_MODE)
 	  {
-	    if (*refer && (*refer)->fixed) // Avoid crash in case of error
-	    {
-	      prev_subselect_item->used_tables_cache|= (*refer)->used_tables();
-	      prev_subselect_item->const_item_cache&= (*refer)->const_item();
-	    }
-	    break;
+            if (!(ref= resolve_ref_in_select_and_group(thd, this, outer_sel)))
+              return TRUE; /* Some error occured (e.g. ambigous names). */
+            if (ref != not_found_item)
+            {
+              DBUG_ASSERT(*ref && (*ref)->fixed);
+              prev_subselect_item->used_tables_cache|= (*ref)->used_tables();
+	      prev_subselect_item->const_item_cache&= (*ref)->const_item();
+              break;
+            }
 	  }
 
 	  // Reference is not found => depend from outer (or just error)
 	  prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
 	  prev_subselect_item->const_item_cache= 0;
 
-	  if (sl->master_unit()->first_select()->linkage ==
+	  if (outer_sel->master_unit()->first_select()->linkage ==
 	      DERIVED_TABLE_TYPE)
 	    break; // do not look over derived table
 	}
       }
-      if (!tmp)
-	return -1;
-      if (!refer)
-	return 1;
-      if (tmp == not_found_field && refer == (Item **)not_found_item)
+
+      DBUG_ASSERT(ref);
+      if (!from_field)
+	return TRUE;
+      if (ref == not_found_item && from_field == not_found_field)
       {
 	if (upward_lookup)
 	{
-	  // We can't say exactly what absend table or field
+	  // We can't say exactly what absent table or field
 	  my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
 			  full_name(), thd->where);
 	}
 	else
 	{
 	  // Call to report error
-	  find_field_in_tables(thd, this, tables, ref, 1, 1);
+	  find_field_in_tables(thd, this, tables, reference, REPORT_ALL_ERRORS,
+                               TRUE);
 	}
-	return -1;
+	return TRUE;
       }
-      else if (refer != (Item **)not_found_item)
+      else if (ref != not_found_item)
       {
-	if (!(*refer)->fixed)
-	{
-	  my_error(ER_ILLEGAL_REFERENCE, MYF(0), name,
-		   "forward reference in item list");
-	  return -1;
-	}
+        /* Should have been checked in resolve_ref_in_select_and_group(). */
+        DBUG_ASSERT(*ref && (*ref)->fixed);
 
 	Item_ref *rf= new Item_ref(last->ref_pointer_array + counter,
                                    (char *)table_name, (char *)field_name);
 	if (!rf)
-	  return 1;
-        thd->change_item_tree(ref, rf);
+	  return TRUE;
+        thd->change_item_tree(reference, rf);
 	/*
 	  rf is Item_ref => never substitute other items (in this case)
 	  during fix_fields() => we can use rf after fix_fields()
 	*/
-	if (rf->fix_fields(thd, tables, ref) || rf->check_cols(1))
-	  return 1;
+	if (rf->fix_fields(thd, tables, reference) || rf->check_cols(1))
+	  return TRUE;
 
-	mark_as_dependent(thd, last, cursel, rf);
-	return 0;
+	mark_as_dependent(thd, last, current_sel, rf);
+	return FALSE;
       }
       else
       {
-	mark_as_dependent(thd, last, cursel, this);
+	mark_as_dependent(thd, last, current_sel, this);
 	if (last->having_fix_field)
 	{
 	  Item_ref *rf;
           rf= new Item_ref((cached_table->db[0] ? cached_table->db : 0),
                            (char*) cached_table->alias, (char*) field_name);
 	  if (!rf)
-	    return 1;
-          thd->change_item_tree(ref, rf);
+	    return TRUE;
+          thd->change_item_tree(reference, rf);
 	  /*
 	    rf is Item_ref => never substitute other items (in this case)
 	    during fix_fields() => we can use rf after fix_fields()
 	  */
-	  return rf->fix_fields(thd, tables, ref) ||  rf->check_cols(1);
+	  return rf->fix_fields(thd, tables, reference) ||  rf->check_cols(1);
 	}
       }
     }
-    else if (!tmp)
-      return -1;
+    else if (!from_field)
+      return TRUE;
 
     /*
       if it is not expression from merged VIEW we will set this field.
@@ -1709,8 +1960,8 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 
       Also we suppose that view can't be changed during PS/SP life.
     */
-    if (tmp != view_ref_found)
-      set_field(tmp);
+    if (from_field != view_ref_found)
+      set_field(from_field);
   }
   else if (thd->set_query_id && field->query_id != thd->query_id)
   {
@@ -1746,14 +1997,15 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
                       thd->host_or_ip,
                       field_name,
                       tab);
-      return 1;
+      return TRUE;
     }
   }
 #endif
   fixed= 1;
-  return 0;
+  return FALSE;
 }
 
+
 Item *Item_field::safe_charset_converter(CHARSET_INFO *tocs)
 {
   no_const_subst= 1;
@@ -2428,207 +2680,240 @@ bool Item_field::send(Protocol *protocol, String *buffer)
 
 
 /*
-  This is used for HAVING clause
-  Find field in select list having the same name
+  Resolve the name of a reference to a column reference.
+
+  SYNOPSIS
+    Item_ref::fix_fields()
+    thd       [in]      current thread
+    tables    [in]      the tables in a FROM clause
+    reference [in/out]  view column if this item was resolved to a view column
+
+  DESCRIPTION
+    The method resolves the column reference represented by 'this' as a column
+    present in one of: GROUP BY clause, SELECT clause, outer queries. It is
+    used typically for columns in the HAVING clause which are not under
+    aggregate functions.
+
+  NOTES
+    The name resolution algorithm used is (where [T_j] is an optional table
+    name that qualifies the column name):
+
+      resolve_extended([T_j].col_ref_i)
+      {
+        Search for a column or derived column named col_ref_i [in table T_j]
+        in the SELECT and GROUP clauses of Q.
+
+        if such a column is NOT found AND    // Lookup in outer queries.
+           there are outer queries
+        {
+          for each outer query Q_k beginning from the inner-most one
+         {
+            Search for a column or derived column named col_ref_i
+            [in table T_j] in the SELECT and GROUP clauses of Q_k.
+
+            if such a column is not found AND
+               - Q_k is not a group query AND
+               - Q_k is not inside an aggregate function
+               OR
+               - Q_(k-1) is not in a HAVING or SELECT clause of Q_k
+            {
+              search for a column or derived column named col_ref_i
+              [in table T_j] in the FROM clause of Q_k;
+            }
+          }
+        }
+      }
+
+    This procedure treats GROUP BY and SELECT clauses as one namespace for
+    column references in HAVING. Notice that compared to
+    Item_field::fix_fields, here we first search the SELECT and GROUP BY
+    clauses, and then we search the FROM clause.
+
+  RETURN
+    TRUE  if error
+    FALSE on success
 */
 
 bool Item_ref::fix_fields(THD *thd, TABLE_LIST *tables, Item **reference)
 {
   DBUG_ASSERT(fixed == 0);
-  uint counter;
-  bool not_used;
+  SELECT_LEX *current_sel= thd->lex->current_select;
+
   if (!ref)
   {
-    TABLE_LIST *table_list;
-    bool upward_lookup= 0;
-    SELECT_LEX_UNIT *prev_unit= thd->lex->current_select->master_unit();
-    SELECT_LEX *sl= prev_unit->outer_select();
-    /*
-      Finding only in current select will be performed for selects that have 
-      not outer one and for derived tables (which not support using outer 
-      fields for now)
-    */
-    if ((ref= find_item_in_list(this, 
-				*(thd->lex->current_select->get_item_list()),
-				&counter,
-				((sl && 
-				  thd->lex->current_select->master_unit()->
-				  first_select()->linkage !=
-				  DERIVED_TABLE_TYPE) ? 
-				  REPORT_EXCEPT_NOT_FOUND :
-                                 REPORT_ALL_ERRORS), &not_used)) ==
-	(Item **)not_found_item)
+    SELECT_LEX_UNIT *prev_unit= current_sel->master_unit();
+    SELECT_LEX *outer_sel= prev_unit->outer_select();
+    ORDER *group_list= (ORDER*) current_sel->group_list.first;
+    bool ambiguous_fields= FALSE;
+    Item **group_by_ref= NULL;
+
+    if (!(ref= resolve_ref_in_select_and_group(thd, this, current_sel)))
+      return TRUE;             /* Some error occured (e.g. ambigous names). */
+
+    if (ref == not_found_item) /* This reference was not resolved. */
     {
-      Field *tmp= (Field*) not_found_field;
-      SELECT_LEX *last= 0;
-      upward_lookup= 1;
       /*
-	We can't find table field in select list of current select,
-	consequently we have to find it in outer subselect(s).
-	We can't join lists of outer & current select, because of scope
-	of view rules. For example if both tables (outer & current) have
-	field 'field' it is not mistake to refer to this field without
-	mention of table name, but if we join tables in one list it will
-	cause error ER_NON_UNIQ_ERROR in find_item_in_list.
+        If there is an outer select, and it is not a derived table (which do
+        not support the use of outer fields for now), try to resolve this
+        reference in the outer select(s).
+      
+        We treat each subselect as a separate namespace, so that different
+        subselects may contain columns with the same names. The subselects are
+        searched starting from the innermost.
       */
-      for ( ; sl ; sl= (prev_unit= sl->master_unit())->outer_select())
+      if (outer_sel && (current_sel->master_unit()->first_select()->linkage !=
+                        DERIVED_TABLE_TYPE))
       {
-	last= sl;
-	Item_subselect *prev_subselect_item= prev_unit->item;
-	if (sl->resolve_mode == SELECT_LEX::SELECT_MODE &&
-	    (ref= find_item_in_list(this, sl->item_list,
-				    &counter, REPORT_EXCEPT_NOT_FOUND,
-                                    &not_used)) !=
-	   (Item **)not_found_item)
-	{
-	  if (*ref && (*ref)->fixed) // Avoid crash in case of error
-	  {
-	    prev_subselect_item->used_tables_cache|= (*ref)->used_tables();
-	    prev_subselect_item->const_item_cache&= (*ref)->const_item();
-	  }
-	  break;
-	}
-	table_list= sl->get_table_list();
-	if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
-	{
-	  // it is primary INSERT st_select_lex => skip first table resolving
-	  table_list= table_list->next_local;
-	}
-        enum_parsing_place place= prev_subselect_item->parsing_place;
-        /*
-          Check table fields only if subquery used somewhere out of HAVING
-          or SELECT list or outer SELECT do not use groupping (i.e. tables
-          are accessable)
-        */
-        if ((place != IN_HAVING ||
-             (sl->with_sum_func == 0 && sl->group_list.elements == 0)) &&
-            (tmp= find_field_in_tables(thd, this,
-				       table_list, reference,
-				       0, 1)) != not_found_field)
-	{
-          if (tmp)
+        TABLE_LIST *table_list;
+        Field *from_field= (Field*) not_found_field;
+        SELECT_LEX *last= 0;
+
+        for ( ; outer_sel ;
+              outer_sel= (prev_unit= outer_sel->master_unit())->outer_select())
+        {
+          last= outer_sel;
+          Item_subselect *prev_subselect_item= prev_unit->item;
+
+          /* Search in the SELECT and GROUP lists of the outer select. */
+          if (outer_sel->resolve_mode == SELECT_LEX::SELECT_MODE)
           {
-            if (tmp != view_ref_found)
+            if (!(ref= resolve_ref_in_select_and_group(thd, this, outer_sel)))
+              return TRUE; /* Some error occured (e.g. ambigous names). */
+            if (ref != not_found_item)
             {
-              prev_subselect_item->used_tables_cache|= tmp->table->map;
-              prev_subselect_item->const_item_cache= 0;
+              DBUG_ASSERT(*ref && (*ref)->fixed);
+              prev_subselect_item->used_tables_cache|= (*ref)->used_tables();
+              prev_subselect_item->const_item_cache&= (*ref)->const_item();
+              break;
             }
-            else
+          }
+
+          /* Search in the tables of the FROM clause of the outer select. */
+          table_list= outer_sel->get_table_list();
+          if (outer_sel->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
+            /*
+              It is a primary INSERT st_select_lex => do not resolve against the
+              first table.
+            */
+            table_list= table_list->next_local;
+
+          enum_parsing_place place= prev_subselect_item->parsing_place;
+          /*
+            Check table fields only if the subquery is used somewhere out of
+            HAVING or the outer SELECT does not use grouping (i.e. tables are
+            accessible).
+            TODO: 
+            Here we could first find the field anyway, and then test this
+            condition, so that we can give a better error message -
+            ER_WRONG_FIELD_WITH_GROUP, instead of the less informative
+            ER_BAD_FIELD_ERROR which we produce now.
+          */
+          if ((place != IN_HAVING ||
+               (!outer_sel->with_sum_func &&
+                outer_sel->group_list.elements == 0)))
+          {
+            if ((from_field= find_field_in_tables(thd, this, table_list,
+                                                  reference,
+                                                  IGNORE_EXCEPT_NON_UNIQUE,
+                                                  TRUE)) !=
+                not_found_field)
             {
-              prev_subselect_item->used_tables_cache|=
-                (*reference)->used_tables();
-              prev_subselect_item->const_item_cache&=
-                (*reference)->const_item();
+              if (from_field != view_ref_found)
+              {
+                prev_subselect_item->used_tables_cache|= from_field->table->map;
+                prev_subselect_item->const_item_cache= 0;
+              }
+              else
+              {
+                prev_subselect_item->used_tables_cache|=
+                  (*reference)->used_tables();
+                prev_subselect_item->const_item_cache&=
+                  (*reference)->const_item();
+              }
+              break;
             }
           }
-	  break;
-	}
 
-	// Reference is not found => depend from outer (or just error)
-	prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
-	prev_subselect_item->const_item_cache= 0;
+          /* Reference is not found => depend on outer (or just error). */
+          prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
+          prev_subselect_item->const_item_cache= 0;
 
-	if (sl->master_unit()->first_select()->linkage ==
-	    DERIVED_TABLE_TYPE)
-	  break; // do not look over derived table
-      }
+          if (outer_sel->master_unit()->first_select()->linkage ==
+              DERIVED_TABLE_TYPE)
+            break; /* Do not consider derived tables. */
+        }
 
-      if (!ref)
-	return 1;
-      if (!tmp)
-	return -1;
-      if (ref == (Item **)not_found_item && tmp == not_found_field)
-      {
-	if (upward_lookup)
-	{
-	  // We can't say exactly what absend (table or field)
-	  my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
-			  full_name(), thd->where);
-	}
-	else
-	{
-	  // Call to report error
-	  find_item_in_list(this,
-			    *(thd->lex->current_select->get_item_list()),
-			    &counter, REPORT_ALL_ERRORS, &not_used);
-	}
-        ref= 0;                                 // Safety
-	return 1;
-      }
-      if (tmp != not_found_field)
-      {
-        /*
-          Set ref to 0 as we are replacing this item with the found item
-          and this will ensure we get an error if this item would be
-          used elsewhere
-        */
-	ref= 0;                                 // Safety
-	if (tmp != view_ref_found)
-	{
-	  Item_field* fld;
-	  if (!(fld= new Item_field(tmp)))
-	    return 1;
-          thd->change_item_tree(reference, fld);
-	  mark_as_dependent(thd, last, thd->lex->current_select, fld);
-	  return 0;
-	}
-        /*
-	  We can leave expression substituted from view for next PS/SP
-	  rexecution (i.e. do not register this substitution for reverting
-	  on cleupup() (register_item_tree_changing())), because this
-	  subtree will be fix_field'ed during
-	  setup_tables()->setup_ancestor() (i.e. before all other
-	  expressions of query, and references on tables which do not
-	  present in query will not make problems.
-
-	  Also we suppose that view can't be changed during PS/SP life.
-	*/
-      }
-      else
-      {
-        if (!(*ref)->fixed)
+        DBUG_ASSERT(ref);
+        if (!from_field)
+          return TRUE;
+        if (ref == not_found_item && from_field == not_found_field)
+        {
+          my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
+                          this->full_name(), current_thd->where);
+          ref= 0;                                 // Safety
+          return TRUE;
+        }
+        if (from_field != not_found_field)
+        {
+          /*
+            Set ref to 0 as we are replacing this item with the found item and
+            this will ensure we get an error if this item would be used
+            elsewhere
+          */
+          ref= 0;                                 // Safety
+          if (from_field != view_ref_found)
+          {
+            Item_field* fld;
+            if (!(fld= new Item_field(tmp)))
+              return TRUE;
+            thd->change_item_tree(reference, fld);
+            mark_as_dependent(thd, last, thd->lex->current_select, fld);
+            return FALSE;
+          }
+          /*
+            We can leave expression substituted from view for next PS/SP
+            re-execution (i.e. do not register this substitution for reverting
+            on cleanup() (register_item_tree_changing())), because this subtree
+            will be fix_field'ed during setup_tables()->setup_ancestor()
+            (i.e. before all other expressions of query, and references on
+            tables which do not present in query will not make problems.
+
+            Also we suppose that view can't be changed during PS/SP life.
+          */
+        }
+        else
         {
-          my_error(ER_ILLEGAL_REFERENCE, MYF(0), name,
-                   "forward reference in item list");
-          return -1;
+          /* Should be checked in resolve_ref_in_select_and_group(). */
+          DBUG_ASSERT(*ref && (*ref)->fixed);
+          mark_as_dependent(thd, last, current_sel, this);
         }
-        mark_as_dependent(thd, last, thd->lex->current_select,
-                          this);
-        ref= last->ref_pointer_array + counter;
       }
-    }
-    else if (!ref)
-      return 1;
-    else
-    {
-      if (!(*ref)->fixed)
+      else
       {
-	my_error(ER_ILLEGAL_REFERENCE, MYF(0), name,
-		 "forward reference in item list");
-	return -1;
+        /* The current reference cannot be resolved in this query. */
+        my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
+                        this->full_name(), current_thd->where);
+        return TRUE;
       }
-      ref= thd->lex->current_select->ref_pointer_array + counter;
     }
   }
 
   /*
-    The following conditional is changed as to correctly identify 
-    incorrect references in group functions or forward references 
-    with sub-select's / derived tables, while it prevents this 
-    check when Item_ref is created in an expression involving 
-    summing function, which is to be placed in the user variable.
+    Check if this is an incorrect reference in a group function or forward
+    reference. Do not issue an error if this is an unnamed reference inside an
+    aggregate function.
   */
   if (((*ref)->with_sum_func && name &&
        (depended_from ||
-	!(thd->lex->current_select->linkage != GLOBAL_OPTIONS_TYPE &&
-	  thd->lex->current_select->having_fix_field))) ||
+	!(current_sel->linkage != GLOBAL_OPTIONS_TYPE &&
+          current_sel->having_fix_field))) ||
       !(*ref)->fixed)
   {
     my_error(ER_ILLEGAL_REFERENCE, MYF(0), name, 
 	     ((*ref)->with_sum_func?
-	      "reference on group function":
+	      "reference to group function":
 	      "forward reference in item list"));
-    return 1;
+    return TRUE;
   }
   max_length= (*ref)->max_length;
   maybe_null= (*ref)->maybe_null;
@@ -2638,8 +2923,8 @@ bool Item_ref::fix_fields(THD *thd, TABLE_LIST *tables, Item **reference)
   fixed= 1;
 
   if (ref && (*ref)->check_cols(1))
-    return 1;
-  return 0;
+    return TRUE;
+  return FALSE;
 }
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index b343beaa4e4..20ede9a623e 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -651,8 +651,13 @@ void execute_init_command(THD *thd, sys_var_str *init_command_var,
 			  rw_lock_t *var_mutex);
 extern const Field *not_found_field;
 extern const Field *view_ref_found;
+
+enum find_item_error_report_type {REPORT_ALL_ERRORS, REPORT_EXCEPT_NOT_FOUND,
+				  IGNORE_ERRORS, REPORT_EXCEPT_NON_UNIQUE,
+                                  IGNORE_EXCEPT_NON_UNIQUE};
 Field *find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
-			    Item **ref, bool report_error,
+			    Item **ref,
+                            find_item_error_report_type report_error,
                             bool check_privileges);
 Field *
 find_field_in_table(THD *thd, TABLE_LIST *table_list,
@@ -771,8 +776,6 @@ TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find);
 SQL_SELECT *make_select(TABLE *head, table_map const_tables,
 			table_map read_tables, COND *conds, int *error,
                         bool allow_null_cond= false);
-enum find_item_error_report_type {REPORT_ALL_ERRORS, REPORT_EXCEPT_NOT_FOUND,
-				  IGNORE_ERRORS};
 extern const Item **not_found_item;
 Item ** find_item_in_list(Item *item, List<Item> &items, uint *counter,
                           find_item_error_report_type report_error,
diff --git a/sql/sp.cc b/sql/sp.cc
index 96bb8a17a49..a99a3ee8a2a 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -615,7 +615,7 @@ db_show_routine_status(THD *thd, int type, const char *wild)
       Item_field *field= new Item_field("mysql", "proc",
 					used_field->field_name);
       if (!(used_field->field= find_field_in_tables(thd, field, &tables, 
-						    0, TRUE, 1)))
+						    0, REPORT_ALL_ERRORS, 1)))
       {
 	res= SP_INTERNAL_ERROR;
 	goto err_case1;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 4fae481e5f2..db7f3a35da9 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2225,25 +2225,32 @@ Field *find_field_in_real_table(THD *thd, TABLE *table,
     find_field_in_tables()
     thd			Pointer to current thread structure
     item		Field item that should be found
-    tables		Tables for scanning
-    ref			if view field is found, pointer to view item will
-			be returned via this parameter
-    report_error	If FALSE then do not report error if item not found
-			and return not_found_field
+    tables		Tables to be searched for item
+    ref			If 'item' is resolved to a view field, ref is set to
+                        point to the found view field
+    report_error	Degree of error reporting:
+                        - IGNORE_ERRORS then do not report any error
+                        - IGNORE_EXCEPT_NON_UNIQUE report only non-unique
+                          fields, suppress all other errors
+                        - REPORT_EXCEPT_NON_UNIQUE report all other errors
+                          except when non-unique fields were found
+                        - REPORT_ALL_ERRORS
     check_privileges    need to check privileges
 
   RETURN VALUES
-    0			Field is not found or field is not unique- error
-			message is reported
-    not_found_field	Function was called with report_error == FALSE and
-			field was not found. no error message reported.
-    view_ref_found	view field is found, item passed through ref parameter
-    found field
+    0			If error: the found field is not unique, or there are
+                        no sufficient access priviliges for the found field,
+                        or the field is qualified with non-existing table.
+    not_found_field	The function was called with report_error ==
+                        (IGNORE_ERRORS || IGNORE_EXCEPT_NON_UNIQUE) and a
+			field was not found.
+    view_ref_found	View field is found, item passed through ref parameter
+    found field         If a item was resolved to some field
 */
 
 Field *
 find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
-		     Item **ref, bool report_error,
+		     Item **ref, find_item_error_report_type report_error,
                      bool check_privileges)
 {
   Field *found=0;
@@ -2261,8 +2268,8 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
       TABLE_LIST *tables is not changed during query execution (which 
       is true for all queries except RENAME but luckily RENAME doesn't 
       use fields...) so we can rely on reusing pointer to its member.
-      With this optimisation we also miss case when addition of one more
-      field makes some prepared query ambiguous and so erronous, but we 
+      With this optimization we also miss case when addition of one more
+      field makes some prepared query ambiguous and so erroneous, but we 
       accept this trade off.
     */
     found= find_field_in_real_table(thd, item->cached_table->table,
@@ -2283,7 +2290,7 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
   if (db && lower_case_table_names)
   {
     /*
-      convert database to lower case for comparision.
+      convert database to lower case for comparison.
       We can't do this in Item_field as this would change the
       'name' of the item which may be used in the select list
     */
@@ -2320,8 +2327,10 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
 	    return find;
 	  if (found)
 	  {
-	    my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
-			    item->full_name(),thd->where);
+            if (report_error == REPORT_ALL_ERRORS ||
+                report_error == IGNORE_EXCEPT_NON_UNIQUE)
+              my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
+                              item->full_name(),thd->where);
 	    return (Field*) 0;
 	  }
 	  found=find;
@@ -2330,7 +2339,8 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
     }
     if (found)
       return found;
-    if (!found_table && report_error)
+    if (!found_table && (report_error == REPORT_ALL_ERRORS ||
+                         report_error == REPORT_EXCEPT_NON_UNIQUE))
     {
       char buff[NAME_LEN*2+1];
       if (db && db[0])
@@ -2338,28 +2348,30 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
 	strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS);
 	table_name=buff;
       }
-      if (report_error)
-      {
+      if (report_error == REPORT_ALL_ERRORS ||
+          report_error == REPORT_EXCEPT_NON_UNIQUE)
 	my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
 			table_name, thd->where);
-      }
       else
 	return (Field*) not_found_field;
     }
     else
-      if (report_error)
+      if (report_error == REPORT_ALL_ERRORS ||
+          report_error == REPORT_EXCEPT_NON_UNIQUE)
 	my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),MYF(0),
 			item->full_name(),thd->where);
       else
 	return (Field*) not_found_field;
     return (Field*) 0;
   }
+
   bool allow_rowid= tables && !tables->next_local;	// Only one table
   for (; tables ; tables= tables->next_local)
   {
     if (!tables->table)
     {
-      if (report_error)
+      if (report_error == REPORT_ALL_ERRORS ||
+          report_error == REPORT_EXCEPT_NON_UNIQUE)
 	my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),MYF(0),
 			item->full_name(),thd->where);
       return (Field*) not_found_field;
@@ -2384,8 +2396,10 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
       {
 	if (!thd->where)			// Returns first found
 	  break;
-	my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
-			name,thd->where);
+        if (report_error == REPORT_ALL_ERRORS ||
+            report_error == IGNORE_EXCEPT_NON_UNIQUE)
+          my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
+                          name,thd->where);
 	return (Field*) 0;
       }
       found=field;
@@ -2393,7 +2407,8 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
   }
   if (found)
     return found;
-  if (report_error)
+  if (report_error == REPORT_ALL_ERRORS ||
+      report_error == REPORT_EXCEPT_NON_UNIQUE)
     my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR),
 		    MYF(0), item->full_name(), thd->where);
   else
@@ -2432,7 +2447,7 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
                         found field
 */
 
-// Special Item pointer for find_item_in_list returning
+/* Special Item pointer to serve as a return value from find_item_in_list(). */
 const Item **not_found_item= (const Item**) 0x1;
 
 
@@ -2830,7 +2845,8 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table,
     any_privileges	0 If we should ensure that we have SELECT privileges
 		          for all columns
                         1 If any privilege is ok
-    allocate_view_names if true view names will be copied to current Item_arena                         memory (made for SP/PS)
+    allocate_view_names if true view names will be copied to current Item_arena
+                        memory (made for SP/PS)
   RETURN
     0	ok
         'it' is updated to point at last inserted
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index b3d7bebe96a..aed86b78e93 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -88,7 +88,7 @@ static bool init_fields(THD *thd, TABLE_LIST *tables,
     Item_field *field= new Item_field("mysql", find_fields->table_name,
                                       find_fields->field_name);
     if (!(find_fields->field= find_field_in_tables(thd, field, tables,
-						   0, TRUE, 1)))
+						   0, REPORT_ALL_ERRORS, 1)))
       DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 93b46ec874d..924b74e2e6e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -11168,24 +11168,53 @@ cp_buffer_from_ref(TABLE_REF *ref)
 *****************************************************************************/
 
 /*
-  Find order/group item in requested columns and change the item to point at
-  it. If item doesn't exists, add it first in the field list
-  Return 0 if ok.
+  Resolve an ORDER BY or GROUP BY column reference.
+
+  SYNOPSIS
+    find_order_in_list()
+    thd		      [in]     Pointer to current thread structure
+    ref_pointer_array [in/out] All select, group and order by fields
+    tables            [in]     List of tables to search in (usually FROM clause)
+    order             [in]     Column reference to be resolved
+    fields            [in]     List of fields to search in (usually SELECT list)
+    all_fields        [in/out] All select, group and order by fields
+    is_group_field    [in]     True if order is a GROUP field, false if
+                               ORDER by field
+
+  DESCRIPTION
+    Given a column reference (represented by 'order') from a GROUP BY or ORDER
+    BY clause, find the actual column it represents. If the column being
+    resolved is from the GROUP BY clause, the procedure searches the SELECT
+    list 'fields' and the columns in the FROM list 'tables'. If 'order' is from
+    the ORDER BY clause, only the SELECT list is being searched.
+
+    If 'order' is resolved to an Item, then order->item is set to the found
+    Item. If there is no item for the found column (that is, it was resolved
+    into a table field), order->item is 'fixed' and is added to all_fields and
+    ref_pointer_array.
+
+  RETURN
+    0 if OK
+    1 if error occurred
 */
 
 static int
-find_order_in_list(THD *thd, Item **ref_pointer_array,
-		   TABLE_LIST *tables,ORDER *order, List<Item> &fields,
-		   List<Item> &all_fields)
+find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
+                   ORDER *order, List<Item> &fields, List<Item> &all_fields,
+                   bool is_group_field)
 {
-  Item *it= *order->item;
-  if (it->type() == Item::INT_ITEM)
+  Item *order_item= *order->item; /* The item from the GROUP/ORDER caluse. */
+  Item::Type order_item_type;
+  Item **select_item; /* The corresponding item from the SELECT clause. */
+  Field *from_field;  /* The corresponding field from the FROM clause. */
+
+  if (order_item->type() == Item::INT_ITEM)
   {						/* Order by position */
-    uint count= (uint) it->val_int();
+    uint count= (uint) order_item->val_int();
     if (!count || count > fields.elements)
     {
       my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),
-		      MYF(0), it->full_name(), thd->where);
+		      MYF(0), order_item->full_name(), thd->where);
       return 1;
     }
     order->item= ref_pointer_array + count - 1;
@@ -11194,38 +11223,68 @@ find_order_in_list(THD *thd, Item **ref_pointer_array,
     order->counter_used= 1;
     return 0;
   }
+  /* Lookup the current GROUP/ORDER field in the SELECT clause. */
   uint counter;
   bool unaliased;
-  Item **item= find_item_in_list(it, fields, &counter,
+  select_item= find_item_in_list(order_item, fields, &counter,
                                  REPORT_EXCEPT_NOT_FOUND, &unaliased);
-  if (!item)
-    return 1;
+  if (!select_item)
+    return 1; /* Some error occured. */
 
-  if (item != (Item **)not_found_item)
+
+  /* Check whether the resolved field is not ambiguos. */
+  if (select_item != not_found_item)
   {
     /*
       If we have found field not by its alias in select list but by its
       original field name, we should additionaly check if we have conflict
       for this name (in case if we would perform lookup in all tables).
     */
-    if (unaliased && !it->fixed && it->fix_fields(thd, tables, order->item))
+    if (unaliased && !order_item->fixed && order_item->fix_fields(thd, tables, order->item))
       return 1;
 
-    order->item= ref_pointer_array + counter;
-    order->in_field_list=1;
-    return 0;
+    /* Lookup the current GROUP field in the FROM clause. */
+    order_item_type= order_item->type();
+    if (is_group_field &&
+        order_item_type == Item::FIELD_ITEM || order_item_type == Item::REF_ITEM)
+    {
+      Item **view_ref= NULL;
+      from_field= find_field_in_tables(thd, (Item_ident*) order_item, tables,
+                                       view_ref, IGNORE_ERRORS, TRUE);
+      if(!from_field)
+       from_field= (Field*) not_found_field;
+    }
+    else
+      from_field= (Field*) not_found_field;
+
+    if (from_field == not_found_field ||
+        from_field && from_field != view_ref_found &&
+        (*select_item)->type() == Item::FIELD_ITEM &&
+        ((Item_field*) (*select_item))->field->eq(from_field))
+      /*
+        If there is no such field in the FROM clause, or it is the same field as
+        the one found in the SELECT clause, then use the Item created for the
+        SELECT field. As a result if there was a derived field that 'shadowed'
+        a table field with the same name, the table field will be chosen over
+        the derived field.
+      */
+    {
+      order->item= ref_pointer_array + counter;
+      order->in_field_list=1;
+      return 0;
+    }
   }
 
   order->in_field_list=0;
   /*
-    We check it->fixed because Item_func_group_concat can put
+    We check order_item->fixed because Item_func_group_concat can put
     arguments for which fix_fields already was called.
 
     'it' reassigned in if condition because fix_field can change it.
   */
-  if (!it->fixed &&
-      (it->fix_fields(thd, tables, order->item) ||
-       (it= *order->item)->check_cols(1) ||
+  if (!order_item->fixed &&
+      (order_item->fix_fields(thd, tables, order->item) ||
+       (order_item= *order->item)->check_cols(1) ||
        thd->is_fatal_error))
     return 1;					// Wrong field 
   uint el= all_fields.elements;
@@ -11235,6 +11294,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array,
   return 0;
 }
 
+
 /*
   Change order to point at item in select list. If item isn't a number
   and doesn't exits in the select list, add it the the field list.
@@ -11247,7 +11307,7 @@ int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
   for (; order; order=order->next)
   {
     if (find_order_in_list(thd, ref_pointer_array, tables, order, fields,
-			   all_fields))
+			   all_fields, FALSE))
       return 1;
   }
   return 0;
@@ -11299,7 +11359,7 @@ setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
   for (; order; order=order->next)
   {
     if (find_order_in_list(thd, ref_pointer_array, tables, order, fields,
-			   all_fields))
+			   all_fields, TRUE))
       return 1;
     (*order->item)->marker=1;		/* Mark found */
     if ((*order->item)->with_sum_func)