MySQL Lists: commits: bk commit into 5.1 tree (holyfoot:1.2466)

List:	Commits	« Previous Message Next Message »
From:	holyfoot	Date:	March 8 2007 4:08pm
Subject:	bk commit into 5.1 tree (holyfoot:1.2466)
View as plain text
Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-03-08 19:08:28+04:00, holyfoot@hfmain.(none) +33 -0
  Merge mysql.com:/home/hf/work/mrg/mysql-5.0-opt
  into  mysql.com:/home/hf/work/mrg/mysql-5.1-opt
  MERGE: 1.1810.2372.111

  BUILD/check-cpu@stripped, 2007-03-08 19:00:04+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.13.1.6

  mysql-test/r/explain.result@stripped, 2007-03-08 19:00:04+04:00, holyfoot@hfmain.(none) +0
-0
    Auto merged
    MERGE: 1.17.1.2

  mysql-test/r/func_in.result@stripped, 2007-03-08 19:01:11+04:00, holyfoot@hfmain.(none) +0
-0
    SCCS merged
    MERGE: 1.28.1.5

  mysql-test/r/func_str.result@stripped, 2007-03-08 19:00:04+04:00, holyfoot@hfmain.(none) +0
-0
    Auto merged
    MERGE: 1.110.1.17

  mysql-test/r/func_test.result@stripped, 2007-03-08 19:00:04+04:00, holyfoot@hfmain.(none) +0
-0
    Auto merged
    MERGE: 1.29.1.5

  mysql-test/r/order_by.result@stripped, 2007-03-08 19:01:41+04:00, holyfoot@hfmain.(none) +0
-0
    SCCS merged
    MERGE: 1.55.1.5

  mysql-test/r/sp.result@stripped, 2007-03-08 19:01:51+04:00, holyfoot@hfmain.(none) +0 -13
    merging
    MERGE: 1.170.27.5

  mysql-test/r/subselect.result@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none)
+0 -0
    Auto merged
    MERGE: 1.134.1.40

  mysql-test/r/update.result@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.31.1.2

  mysql-test/r/view.result@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.138.1.54

  mysql-test/t/explain.test@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.10.1.2

  mysql-test/t/func_in.test@stripped, 2007-03-08 19:02:52+04:00, holyfoot@hfmain.(none) +0 -0
    SCCS merged
    MERGE: 1.22.1.5

  mysql-test/t/func_str.test@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0
-0
    Auto merged
    MERGE: 1.92.1.8

  mysql-test/t/order_by.test@stripped, 2007-03-08 19:03:35+04:00, holyfoot@hfmain.(none) +0 -0
    SCCS merged
    MERGE: 1.41.1.2

  mysql-test/t/sp.test@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.174.27.4

  mysql-test/t/view.test@stripped, 2007-03-08 19:04:41+04:00, holyfoot@hfmain.(none) +1 -1
    merging
    MERGE: 1.126.1.51

  sql/item.cc@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.113.1.139

  sql/item.h@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.183.1.37

  sql/item_cmpfunc.cc@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.187.1.50

  sql/item_cmpfunc.h@stripped, 2007-03-08 19:05:24+04:00, holyfoot@hfmain.(none) +1 -1
    merging
    MERGE: 1.122.2.22

  sql/item_func.cc@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.270.1.53

  sql/item_strfunc.h@stripped, 2007-03-08 19:00:05+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.101.1.20

  sql/mysql_priv.h@stripped, 2007-03-08 19:07:43+04:00, holyfoot@hfmain.(none) +0 -1
    merging
    MERGE: 1.290.1.145

  sql/sql_base.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -1
    Auto merged
    MERGE: 1.235.1.136

  sql/sql_delete.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -1
    Auto merged
    MERGE: 1.144.1.49

  sql/sql_insert.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -1
    Auto merged
    MERGE: 1.146.1.72

  sql/sql_lex.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.142.1.72

  sql/sql_lex.h@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.175.1.64

  sql/sql_load.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -1
    Auto merged
    MERGE: 1.78.1.28

  sql/sql_parse.cc@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -1
    Auto merged
    MERGE: 1.426.1.179

  sql/sql_select.cc@stripped, 2007-03-08 19:08:24+04:00, holyfoot@hfmain.(none) +0 -0
    SCCS merged
    MERGE: 1.312.1.187

  sql/sql_select.h@stripped, 2007-03-08 19:00:06+04:00, holyfoot@hfmain.(none) +0 -0
    Auto merged
    MERGE: 1.92.1.24

  sql/sql_update.cc@stripped, 2007-03-08 19:00:07+04:00, holyfoot@hfmain.(none) +0 -2
    Auto merged
    MERGE: 1.154.2.57

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	holyfoot
# Host:	hfmain.(none)
# Root:	/home/hf/work/mrg/mysql-5.1-opt/RESYNC

--- 1.246/sql/item.cc	2007-03-08 19:08:35 +04:00
+++ 1.247/sql/item.cc	2007-03-08 19:08:35 +04:00
@@ -3354,7 +3354,7 @@ resolve_ref_in_select_and_group(THD *thd
   ORDER *group_list= (ORDER*) select->group_list.first;
   bool ambiguous_fields= FALSE;
   uint counter;
-  bool not_used;
+  enum_resolution_type resolution;
 
   /*
     Search for a column or derived column named as 'ref' in the SELECT
@@ -3362,8 +3362,10 @@ resolve_ref_in_select_and_group(THD *thd
   */
   if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()),
                                       &counter, REPORT_EXCEPT_NOT_FOUND,
-                                      &not_used)))
+                                      &resolution)))
     return NULL; /* Some error occurred. */
+  if (resolution == RESOLVED_AGAINST_ALIAS)
+    ref->alias_name_used= TRUE;
 
   /* If this is a non-aggregated field inside HAVING, search in GROUP BY. */
   if (select->having_fix_field && !ref->with_sum_func &&
group_list)
@@ -3664,9 +3666,9 @@ Item_field::fix_outer_field(THD *thd, Fi
     *ref= NULL;                             // Don't call set_properties()
     rf= (place == IN_HAVING ?
          new Item_ref(context, ref, (char*) table_name,
-                      (char*) field_name) :
+                      (char*) field_name, alias_name_used) :
          new Item_direct_ref(context, ref, (char*) table_name,
-                             (char*) field_name));
+                             (char*) field_name, alias_name_used));
     *ref= save;
     if (!rf)
       return -1;
@@ -3784,12 +3786,14 @@ bool Item_field::fix_fields(THD *thd, It
       if (thd->lex->current_select->is_item_list_lookup)
       {
         uint counter;
-        bool not_used;
+        enum_resolution_type resolution;
         Item** res= find_item_in_list(this, thd->lex->current_select->item_list,
                                       &counter, REPORT_EXCEPT_NOT_FOUND,
-                                      &not_used);
+                                      &resolution);
         if (!res)
           return 1;
+        if (resolution == RESOLVED_AGAINST_ALIAS)
+          alias_name_used= TRUE;
         if (res != (Item **)not_found_item)
         {
           if ((*res)->type() == Item::FIELD_ITEM)
@@ -4984,10 +4988,12 @@ Item *Item_field::update_value_transform
 
 Item_ref::Item_ref(Name_resolution_context *context_arg,
                    Item **item, const char *table_name_arg,
-                   const char *field_name_arg)
+                   const char *field_name_arg,
+                   bool alias_name_used_arg)
   :Item_ident(context_arg, NullS, table_name_arg, field_name_arg),
    result_field(0), ref(item)
 {
+  alias_name_used= alias_name_used_arg;
   /*
     This constructor used to create some internals references over fixed items
   */
@@ -5270,11 +5276,13 @@ void Item_ref::set_properties()
   */
   with_sum_func= (*ref)->with_sum_func;
   unsigned_flag= (*ref)->unsigned_flag;
+  fixed= 1;
+  if (alias_name_used)
+    return;
   if ((*ref)->type() == FIELD_ITEM)
     alias_name_used= ((Item_ident *) (*ref))->alias_name_used;
   else
     alias_name_used= TRUE; // it is not field, so it is was resolved by alias
-  fixed= 1;
 }
 
 
@@ -5292,7 +5300,7 @@ void Item_ref::print(String *str)
   if (ref)
   {
     if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF
&&
-        ref_type() != OUTER_REF && name && alias_name_used)
+        !table_name && name && alias_name_used)
     {
       THD *thd= current_thd;
       append_identifier(thd, str, name, (uint) strlen(name));

--- 1.227/sql/item.h	2007-03-08 19:08:35 +04:00
+++ 1.228/sql/item.h	2007-03-08 19:08:35 +04:00
@@ -1876,7 +1876,10 @@ public:
   Item_hex_string(const char *str,uint str_length);
   enum Type type() const { return VARBIN_ITEM; }
   double val_real()
-    { DBUG_ASSERT(fixed == 1); return (double) Item_hex_string::val_int(); }
+  { 
+    DBUG_ASSERT(fixed == 1); 
+    return (double) (ulonglong) Item_hex_string::val_int();
+  }
   longlong val_int();
   bool basic_const_item() const { return 1; }
   String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; }
@@ -1951,7 +1954,8 @@ public:
          with Bar, and if we have a more broader set of problems like this.
   */
   Item_ref(Name_resolution_context *context_arg, Item **item,
-           const char *table_name_arg, const char *field_name_arg);
+           const char *table_name_arg, const char *field_name_arg,
+           bool alias_name_used_arg= FALSE);
 
   /* Constructor need to process subselect with temporary tables (see Item) */
   Item_ref(THD *thd, Item_ref *item)
@@ -2026,8 +2030,11 @@ class Item_direct_ref :public Item_ref
 public:
   Item_direct_ref(Name_resolution_context *context_arg, Item **item,
                   const char *table_name_arg,
-                  const char *field_name_arg)
-    :Item_ref(context_arg, item, table_name_arg, field_name_arg) {}
+                  const char *field_name_arg,
+                  bool alias_name_used_arg= FALSE)
+    :Item_ref(context_arg, item, table_name_arg,
+              field_name_arg, alias_name_used_arg)
+  {}
   /* Constructor need to process subselect with temporary tables (see Item) */
   Item_direct_ref(THD *thd, Item_direct_ref *item) : Item_ref(thd, item) {}
 

--- 1.248/sql/item_cmpfunc.cc	2007-03-08 19:08:35 +04:00
+++ 1.249/sql/item_cmpfunc.cc	2007-03-08 19:08:35 +04:00
@@ -2128,9 +2128,100 @@ void Item_func_coalesce::fix_length_and_
  Classes and function for the IN operator
 ****************************************************************************/
 
-static int cmp_longlong(void *cmp_arg, longlong *a,longlong *b)
+/*
+  Determine which of the signed longlong arguments is bigger
+
+  SYNOPSIS
+    cmp_longs()
+      a_val     left argument
+      b_val     right argument
+
+  DESCRIPTION
+    This function will compare two signed longlong arguments
+    and will return -1, 0, or 1 if left argument is smaller than,
+    equal to or greater than the right argument.
+
+  RETURN VALUE
+    -1          left argument is smaller than the right argument.
+    0           left argument is equal to the right argument.
+    1           left argument is greater than the right argument.
+*/
+static inline int cmp_longs (longlong a_val, longlong b_val)
 {
-  return *a < *b ? -1 : *a == *b ? 0 : 1;
+  return a_val < b_val ? -1 : a_val == b_val ? 0 : 1;
+}
+
+
+/*
+  Determine which of the unsigned longlong arguments is bigger
+
+  SYNOPSIS
+    cmp_ulongs()
+      a_val     left argument
+      b_val     right argument
+
+  DESCRIPTION
+    This function will compare two unsigned longlong arguments
+    and will return -1, 0, or 1 if left argument is smaller than,
+    equal to or greater than the right argument.
+
+  RETURN VALUE
+    -1          left argument is smaller than the right argument.
+    0           left argument is equal to the right argument.
+    1           left argument is greater than the right argument.
+*/
+static inline int cmp_ulongs (ulonglong a_val, ulonglong b_val)
+{
+  return a_val < b_val ? -1 : a_val == b_val ? 0 : 1;
+}
+
+
+/*
+  Compare two integers in IN value list format (packed_longlong) 
+
+  SYNOPSIS
+    cmp_longlong()
+      cmp_arg   an argument passed to the calling function (qsort2)
+      a         left argument
+      b         right argument
+
+  DESCRIPTION
+    This function will compare two integer arguments in the IN value list
+    format and will return -1, 0, or 1 if left argument is smaller than,
+    equal to or greater than the right argument.
+    It's used in sorting the IN values list and finding an element in it.
+    Depending on the signedness of the arguments cmp_longlong() will
+    compare them as either signed (using cmp_longs()) or unsigned (using
+    cmp_ulongs()).
+
+  RETURN VALUE
+    -1          left argument is smaller than the right argument.
+    0           left argument is equal to the right argument.
+    1           left argument is greater than the right argument.
+*/
+int cmp_longlong(void *cmp_arg, 
+                 in_longlong::packed_longlong *a,
+                 in_longlong::packed_longlong *b)
+{
+  if (a->unsigned_flag != b->unsigned_flag)
+  { 
+    /* 
+      One of the args is unsigned and is too big to fit into the 
+      positive signed range. Report no match.
+    */  
+    if (a->unsigned_flag && ((ulonglong) a->val) > LONGLONG_MAX ||
+        b->unsigned_flag && ((ulonglong) b->val) > LONGLONG_MAX)
+      return a->unsigned_flag ? 1 : -1;
+    /*
+      Although the signedness differs both args can fit into the signed 
+      positive range. Make them signed and compare as usual.
+    */  
+    return cmp_longs (a->val, b->val);
+  }
+  if (a->unsigned_flag)
+    return cmp_ulongs ((ulonglong) a->val, (ulonglong) b->val);
+  else
+    return cmp_longs (a->val, b->val);
 }
 
 static int cmp_double(void *cmp_arg, double *a,double *b)
@@ -2255,19 +2346,23 @@ void in_row::set(uint pos, Item *item)
 }
 
 in_longlong::in_longlong(uint elements)
-  :in_vector(elements,sizeof(longlong),(qsort2_cmp) cmp_longlong, 0)
+  :in_vector(elements,sizeof(packed_longlong),(qsort2_cmp) cmp_longlong, 0)
 {}
 
 void in_longlong::set(uint pos,Item *item)
 {
-  ((longlong*) base)[pos]=item->val_int();
+  struct packed_longlong *buff= &((packed_longlong*) base)[pos];
+  
+  buff->val= item->val_int();
+  buff->unsigned_flag= item->unsigned_flag;
 }
 
 byte *in_longlong::get_value(Item *item)
 {
-  tmp= item->val_int();
+  tmp.val= item->val_int();
   if (item->null_value)
     return 0;
+  tmp.unsigned_flag= item->unsigned_flag;
   return (byte*) &tmp;
 }
 
@@ -2603,6 +2698,31 @@ void Item_func_in::fix_length_and_dec()
   */
   if (type_cnt == 1 && const_itm && !nulls_in_row())
   {
+    /*
+      IN must compare INT/DATE/DATETIME/TIMESTAMP columns and constants
+      as int values (the same way as equality does).
+      So we must check here if the column on the left and all the constant 
+      values on the right can be compared as integers and adjust the 
+      comparison type accordingly.
+    */  
+    if (args[0]->real_item()->type() == FIELD_ITEM &&
+        thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+        thd->lex->sql_command != SQLCOM_SHOW_CREATE &&
+        cmp_type != INT_RESULT)
+    {
+      Field *field= ((Item_field*) (args[0]->real_item()))->field;
+      if (field->can_be_compared_as_longlong())
+      {
+        bool all_converted= TRUE;
+        for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+        {
+          if (!convert_constant_item (thd, field, &arg[0]))
+            all_converted= FALSE;
+        }
+        if (all_converted)
+          cmp_type= INT_RESULT;
+      }
+    }
     switch (cmp_type) {
     case STRING_RESULT:
       array=new in_string(arg_count - 1,(qsort2_cmp) srtcmp_in,

--- 1.151/sql/item_cmpfunc.h	2007-03-08 19:08:35 +04:00
+++ 1.152/sql/item_cmpfunc.h	2007-03-08 19:08:35 +04:00
@@ -699,7 +699,16 @@ public:
 
 class in_longlong :public in_vector
 {
-  longlong tmp;
+  /*
+    Here we declare a temporary variable (tmp) of the same type as the
+    elements of this vector. tmp is used in finding if a given value is in 
+    the list. 
+  */
+  struct packed_longlong 
+  {
+    longlong val;
+    longlong unsigned_flag;  // Use longlong, not bool, to preserve alignment
+  } tmp;
 public:
   in_longlong(uint elements);
   void set(uint pos,Item *item);
@@ -715,9 +724,13 @@ public:
   }
   void value_to_item(uint pos, Item *item)
   {
-    ((Item_int*)item)->value= ((longlong*)base)[pos];
+    ((Item_int*) item)->value= ((packed_longlong*) base)[pos].val;
+    ((Item_int*) item)->unsigned_flag= (my_bool)
+      ((packed_longlong*) base)[pos].unsigned_flag;
   }
   Item_result result_type() { return INT_RESULT; }
+
+  friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
 };
 
 class in_double :public in_vector

--- 1.357/sql/item_func.cc	2007-03-08 19:08:35 +04:00
+++ 1.358/sql/item_func.cc	2007-03-08 19:08:35 +04:00
@@ -413,8 +413,13 @@ bool Item_func::eq(const Item *item, boo
   if (item->type() != FUNC_ITEM)
     return 0;
   Item_func *item_func=(Item_func*) item;
-  if (arg_count != item_func->arg_count ||
-      func_name() != item_func->func_name())
+  Item_func::Functype func_type;
+  if ((func_type= functype()) != item_func->functype() ||
+      arg_count != item_func->arg_count ||
+      (func_type != Item_func::FUNC_SP &&
+       func_name() != item_func->func_name()) ||
+      (func_type == Item_func::FUNC_SP &&
+       my_strcasecmp(system_charset_info, func_name(), item_func->func_name())))
     return 0;
   for (uint i=0; i < arg_count ; i++)
     if (!args[i]->eq(item_func->args[i], binary_cmp))

--- 1.125/sql/item_strfunc.h	2007-03-08 19:08:35 +04:00
+++ 1.126/sql/item_strfunc.h	2007-03-08 19:08:35 +04:00
@@ -601,7 +601,11 @@ class Item_func_unhex :public Item_str_f
 {
   String tmp_value;
 public:
-  Item_func_unhex(Item *a) :Item_str_func(a) {}
+  Item_func_unhex(Item *a) :Item_str_func(a) 
+  { 
+    /* there can be bad hex strings */
+    maybe_null= 1; 
+  }
   const char *func_name() const { return "unhex"; }
   String *val_str(String *);
   void fix_length_and_dec()

--- 1.486/sql/mysql_priv.h	2007-03-08 19:08:35 +04:00
+++ 1.487/sql/mysql_priv.h	2007-03-08 19:08:35 +04:00
@@ -1185,9 +1185,29 @@ SQL_SELECT *make_select(TABLE *head, tab
 			table_map read_tables, COND *conds,
                         bool allow_null_cond,  int *error);
 extern Item **not_found_item;
+
+/*
+  This enumeration type is used only by the function find_item_in_list
+  to return the info on how an item has been resolved against a list
+  of possibly aliased items.
+  The item can be resolved: 
+   - against an alias name of the list's element (RESOLVED_AGAINST_ALIAS)
+   - against non-aliased field name of the list  (RESOLVED_WITH_NO_ALIAS)
+   - against an aliased field name of the list   (RESOLVED_BEHIND_ALIAS)
+   - ignoring the alias name in cases when SQL requires to ignore aliases
+     (e.g. when the resolved field reference contains a table name or
+     when the resolved item is an expression)   (RESOLVED_IGNORING_ALIAS)    
+*/
+enum enum_resolution_type {
+  NOT_RESOLVED=0,
+  RESOLVED_IGNORING_ALIAS,
+  RESOLVED_BEHIND_ALIAS,
+  RESOLVED_WITH_NO_ALIAS,
+  RESOLVED_AGAINST_ALIAS
+};
 Item ** find_item_in_list(Item *item, List<Item> &items, uint *counter,
                           find_item_error_report_type report_error,
-                          bool *unaliased);
+                          enum_resolution_type *resolution);
 bool get_key_map_from_key_list(key_map *map, TABLE *table,
                                List<String> *index_list);
 bool insert_fields(THD *thd, Name_resolution_context *context,
@@ -1245,7 +1265,8 @@ TABLE_LIST *find_table_in_list(TABLE_LIS
                                st_table_list *TABLE_LIST::*link,
                                const char *db_name,
                                const char *table_name);
-TABLE_LIST *unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list);
+TABLE_LIST *unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
+                         bool check_alias);
 TABLE *find_temporary_table(THD *thd, const char *db, const char *table_name);
 TABLE *find_temporary_table(THD *thd, TABLE_LIST *table_list);
 bool close_temporary_table(THD *thd, TABLE_LIST *table_list);

--- 1.378/sql/sql_base.cc	2007-03-08 19:08:35 +04:00
+++ 1.379/sql/sql_base.cc	2007-03-08 19:08:35 +04:00
@@ -1362,6 +1362,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIS
     thd                   thread handle
     table                 table which should be checked
     table_list            list of tables
+    check_alias           whether to check tables' aliases
 
   NOTE: to exclude derived tables from check we use following mechanism:
     a) during derived table processing set THD::derived_tables_processing
@@ -1389,10 +1390,11 @@ TABLE_LIST *find_table_in_list(TABLE_LIS
     0 if table is unique
 */
 
-TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list)
+TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
+                         bool check_alias)
 {
   TABLE_LIST *res;
-  const char *d_name, *t_name;
+  const char *d_name, *t_name, *t_alias;
   DBUG_ENTER("unique_table");
   DBUG_PRINT("enter", ("table alias: %s", table->alias));
 
@@ -1420,6 +1422,7 @@ TABLE_LIST* unique_table(THD *thd, TABLE
   }
   d_name= table->db;
   t_name= table->table_name;
+  t_alias= table->alias;
 
   DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name));
   for (;;)
@@ -1427,6 +1430,9 @@ TABLE_LIST* unique_table(THD *thd, TABLE
     if (((! (res= find_table_in_global_list(table_list, d_name, t_name))) &&
          (! (res= mysql_lock_have_duplicate(thd, table, table_list)))) ||
         ((!res->table || res->table != table->table) &&
+         (!check_alias || !(lower_case_table_names ?
+          my_strcasecmp(files_charset_info, t_alias, res->alias) :
+          strcmp(t_alias, res->alias))) &&
          res->select_lex &&
!res->select_lex->exclude_from_table_unique_test &&
          !res->prelocking_placeholder))
       break;
@@ -4393,10 +4399,13 @@ find_field_in_tables(THD *thd, Item_iden
 				return not_found_item, report other errors,
 				return 0
       IGNORE_ERRORS		Do not report errors, return 0 if error
-    unaliased                   Set to true if item is field which was found
-                                by original field name and not by its alias
-                                in item list. Set to false otherwise.
-
+    resolution                  Set to the resolution type if the item is found 
+                                (it says whether the item is resolved 
+                                 against an alias name,
+                                 or as a field name without alias,
+                                 or as a field hidden by alias,
+                                 or ignoring alias)
+                                
   RETURN VALUES
     0			Item is not found or item is not unique,
 			error message is reported
@@ -4412,7 +4421,8 @@ Item **not_found_item= (Item**) 0x1;
 
 Item **
 find_item_in_list(Item *find, List<Item> &items, uint *counter,
-                  find_item_error_report_type report_error, bool *unaliased)
+                  find_item_error_report_type report_error,
+                  enum_resolution_type *resolution)
 {
   List_iterator<Item> li(items);
   Item **found=0, **found_unaliased= 0, *item;
@@ -4426,10 +4436,9 @@ find_item_in_list(Item *find, List<Item>
   */
   bool is_ref_by_name= 0;
   uint unaliased_counter;
-
   LINT_INIT(unaliased_counter);                 // Dependent on found_unaliased
 
-  *unaliased= FALSE;
+  *resolution= NOT_RESOLVED;
 
   is_ref_by_name= (find->type() == Item::FIELD_ITEM  || 
                    find->type() == Item::REF_ITEM);
@@ -4496,63 +4505,77 @@ find_item_in_list(Item *find, List<Item>
           }
           found_unaliased= li.ref();
           unaliased_counter= i;
+          *resolution= RESOLVED_IGNORING_ALIAS;
           if (db_name)
             break;                              // Perfect match
         }
       }
-      else if (!my_strcasecmp(system_charset_info, item_field->name,
-                              field_name))
-      {
-        /*
-          If table name was not given we should scan through aliases
-          (or non-aliased fields) first. We are also checking unaliased
-          name of the field in then next else-if, to be able to find
-          instantly field (hidden by alias) if no suitable alias (or
-          non-aliased field) was found.
-        */
-        if (found)
-        {
-          if ((*found)->eq(item, 0))
-            continue;                           // Same field twice
-          if (report_error != IGNORE_ERRORS)
-            my_error(ER_NON_UNIQ_ERROR, MYF(0),
-                     find->full_name(), current_thd->where);
-          return (Item**) 0;
-        }
-        found= li.ref();
-        *counter= i;
-      }
-      else if (!my_strcasecmp(system_charset_info, item_field->field_name,
-                              field_name))
+      else
       {
-        /*
-          We will use un-aliased field or react on such ambiguities only if
-          we won't be able to find aliased field.
-          Again if we have ambiguity with field outside of select list
-          we should prefer fields from select list.
-        */
-        if (found_unaliased)
+        int fname_cmp= my_strcasecmp(system_charset_info,
+                                     item_field->field_name,
+                                     field_name);
+        if (!my_strcasecmp(system_charset_info,
+                           item_field->name,field_name))
         {
-          if ((*found_unaliased)->eq(item, 0))
-            continue;                           // Same field twice
-          found_unaliased_non_uniq= 1;
+          /*
+            If table name was not given we should scan through aliases
+            and non-aliased fields first. We are also checking unaliased
+            name of the field in then next  else-if, to be able to find
+            instantly field (hidden by alias) if no suitable alias or
+            non-aliased field was found.
+          */
+          if (found)
+          {
+            if ((*found)->eq(item, 0))
+              continue;                           // Same field twice
+            if (report_error != IGNORE_ERRORS)
+              my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                       find->full_name(), current_thd->where);
+            return (Item**) 0;
+          }
+          found= li.ref();
+          *counter= i;
+          *resolution= fname_cmp ? RESOLVED_AGAINST_ALIAS:
+	                           RESOLVED_WITH_NO_ALIAS;
         }
-        else
+        else if (!fname_cmp)
         {
+          /*
+            We will use non-aliased field or react on such ambiguities only if
+            we won't be able to find aliased field.
+            Again if we have ambiguity with field outside of select list
+            we should prefer fields from select list.
+          */
+          if (found_unaliased)
+          {
+            if ((*found_unaliased)->eq(item, 0))
+              continue;                           // Same field twice
+            found_unaliased_non_uniq= 1;
+          }
           found_unaliased= li.ref();
           unaliased_counter= i;
         }
       }
     }
-    else if (!table_name && (find->eq(item,0) ||
-			     is_ref_by_name && find->name && item->name &&
-			     !my_strcasecmp(system_charset_info, 
-					    item->name,find->name)))
-    {
-      found= li.ref();
-      *counter= i;
-      break;
-    }
+    else if (!table_name)
+    { 
+      if (is_ref_by_name && find->name && item->name &&
+	  !my_strcasecmp(system_charset_info,item->name,find->name))
+      {
+        found= li.ref();
+        *counter= i;
+        *resolution= RESOLVED_AGAINST_ALIAS;
+        break;
+      }
+      else if (find->eq(item,0))
+      {
+        found= li.ref();
+        *counter= i;
+        *resolution= RESOLVED_IGNORING_ALIAS;
+        break;
+      }
+    } 
   }
   if (!found)
   {
@@ -4567,7 +4590,7 @@ find_item_in_list(Item *find, List<Item>
     {
       found= found_unaliased;
       *counter= unaliased_counter;
-      *unaliased= TRUE;
+      *resolution= RESOLVED_BEHIND_ALIAS;
     }
   }
   if (found)
@@ -5349,6 +5372,7 @@ bool setup_fields(THD *thd, Item **ref_p
   enum_mark_columns save_mark_used_columns= thd->mark_used_columns;
   nesting_map save_allow_sum_func= thd->lex->allow_sum_func;
   List_iterator<Item> it(fields);
+  bool save_is_item_list_lookup;
   DBUG_ENTER("setup_fields");
 
   thd->mark_used_columns= mark_used_columns;
@@ -5356,6 +5380,8 @@ bool setup_fields(THD *thd, Item **ref_p
   if (allow_sum_func)
     thd->lex->allow_sum_func|= 1 <<
thd->lex->current_select->nest_level;
   thd->where= THD::DEFAULT_WHERE;
+  save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup;
+  thd->lex->current_select->is_item_list_lookup= 0;
 
   /*
     To prevent fail on forward lookup we fill it with zerows,
@@ -5378,6 +5404,7 @@ bool setup_fields(THD *thd, Item **ref_p
     if (!item->fixed && item->fix_fields(thd, it.ref()) ||
 	(item= *(it.ref()))->check_cols(1))
     {
+      thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup;
       thd->lex->allow_sum_func= save_allow_sum_func;
       thd->mark_used_columns= save_mark_used_columns;
       DBUG_PRINT("info", ("thd->mark_used_columns: %d", thd->mark_used_columns));
@@ -5391,6 +5418,7 @@ bool setup_fields(THD *thd, Item **ref_p
     thd->used_tables|= item->used_tables();
     thd->lex->current_select->cur_pos_in_select_list++;
   }
+  thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup;
   thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS;
 
   thd->lex->allow_sum_func= save_allow_sum_func;
@@ -5884,6 +5912,8 @@ int setup_conds(THD *thd, TABLE_LIST *ta
   */
   bool it_is_update= (select_lex == &thd->lex->select_lex) &&
     thd->lex->which_check_option_applicable();
+  bool save_is_item_list_lookup= select_lex->is_item_list_lookup;
+  select_lex->is_item_list_lookup= 0;
   DBUG_ENTER("setup_conds");
 
   if (select_lex->conds_processed_with_permanent_arena ||
@@ -5959,9 +5989,11 @@ int setup_conds(THD *thd, TABLE_LIST *ta
     select_lex->where= *conds;
     select_lex->conds_processed_with_permanent_arena= 1;
   }
+  thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup;
   DBUG_RETURN(test(thd->net.report_error));
 
 err_no_arena:
+  select_lex->is_item_list_lookup= save_is_item_list_lookup;
   DBUG_RETURN(1);
 }
 

--- 1.209/sql/sql_delete.cc	2007-03-08 19:08:35 +04:00
+++ 1.210/sql/sql_delete.cc	2007-03-08 19:08:35 +04:00
@@ -395,7 +395,7 @@ bool mysql_prepare_delete(THD *thd, TABL
   }
   {
     TABLE_LIST *duplicate;
-    if ((duplicate= unique_table(thd, table_list, table_list->next_global)))
+    if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0)))
     {
       update_non_unique_table_error(table_list, "DELETE", duplicate);
       DBUG_RETURN(TRUE);
@@ -492,7 +492,7 @@ bool mysql_multi_delete_prepare(THD *thd
     {
       TABLE_LIST *duplicate;
       if ((duplicate= unique_table(thd, target_tbl->correspondent_table,
-                                   lex->query_tables)))
+                                   lex->query_tables, 0)))
       {
         update_non_unique_table_error(target_tbl->correspondent_table,
                                       "DELETE", duplicate);

--- 1.250/sql/sql_insert.cc	2007-03-08 19:08:35 +04:00
+++ 1.251/sql/sql_insert.cc	2007-03-08 19:08:35 +04:00
@@ -1024,7 +1024,7 @@ bool mysql_prepare_insert(THD *thd, TABL
   {
     Item *fake_conds= 0;
     TABLE_LIST *duplicate;
-    if ((duplicate= unique_table(thd, table_list, table_list->next_global)))
+    if ((duplicate= unique_table(thd, table_list, table_list->next_global, 1)))
     {
       update_non_unique_table_error(table_list, "INSERT", duplicate);
       DBUG_RETURN(TRUE);
@@ -2538,7 +2538,7 @@ select_insert::prepare(List<Item> &value
     query
   */
   if (!(lex->current_select->options & OPTION_BUFFER_RESULT) &&
-      unique_table(thd, table_list, table_list->next_global))
+      unique_table(thd, table_list, table_list->next_global, 0))
   {
     /* Using same table for INSERT and SELECT */
     lex->current_select->options|= OPTION_BUFFER_RESULT;

--- 1.227/sql/sql_lex.cc	2007-03-08 19:08:35 +04:00
+++ 1.228/sql/sql_lex.cc	2007-03-08 19:08:35 +04:00
@@ -1213,6 +1213,7 @@ void st_select_lex::init_select()
   is_correlated= 0;
   cur_pos_in_select_list= UNDEF_POS;
   non_agg_fields.empty();
+  cond_value= having_value= Item::COND_UNDEF;
   inner_refs_list.empty();
 }
 

--- 1.263/sql/sql_lex.h	2007-03-08 19:08:35 +04:00
+++ 1.264/sql/sql_lex.h	2007-03-08 19:08:35 +04:00
@@ -523,6 +523,8 @@ public:
   Item *where, *having;                         /* WHERE & HAVING clauses */
   Item *prep_where; /* saved WHERE clause for prepared statement processing */
   Item *prep_having;/* saved HAVING clause for prepared statement processing */
+  /* Saved values of the WHERE and HAVING clauses*/
+  Item::cond_result cond_value, having_value;
   /* point on lex in which it was created, used in view subquery detection */
   st_lex *parent_lex;
   enum olap_type olap;

--- 1.114/sql/sql_load.cc	2007-03-08 19:08:35 +04:00
+++ 1.115/sql/sql_load.cc	2007-03-08 19:08:35 +04:00
@@ -175,7 +175,7 @@ bool mysql_load(THD *thd,sql_exchange *e
     table is marked to be 'used for insert' in which case we should never
     mark this table as 'const table' (ie, one that has only one row).
   */
-  if (unique_table(thd, table_list, table_list->next_global))
+  if (unique_table(thd, table_list, table_list->next_global, 0))
   {
     my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
     DBUG_RETURN(TRUE);

--- 1.629/sql/sql_parse.cc	2007-03-08 19:08:35 +04:00
+++ 1.630/sql/sql_parse.cc	2007-03-08 19:08:35 +04:00
@@ -2049,7 +2049,7 @@ mysql_execute_command(THD *thd)
         if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
         {
           TABLE_LIST *duplicate;
-          if ((duplicate= unique_table(thd, create_table, select_tables)))
+          if ((duplicate= unique_table(thd, create_table, select_tables, 0)))
           {
             update_non_unique_table_error(create_table, "CREATE", duplicate);
             res= 1;
@@ -2065,7 +2065,7 @@ mysql_execute_command(THD *thd)
                tab= tab->next_local)
           {
             TABLE_LIST *duplicate;
-            if ((duplicate= unique_table(thd, tab, select_tables)))
+            if ((duplicate= unique_table(thd, tab, select_tables, 0)))
             {
               update_non_unique_table_error(tab, "CREATE", duplicate);
               res= 1;

--- 1.497/sql/sql_select.cc	2007-03-08 19:08:35 +04:00
+++ 1.498/sql/sql_select.cc	2007-03-08 19:08:35 +04:00
@@ -396,6 +396,7 @@ JOIN::prepare(Item ***rref_pointer_array
   join_list= &select_lex->top_join_list;
   union_part= (unit_arg->first_select()->next_select() != 0);
 
+  thd->lex->current_select->is_item_list_lookup= 1;
   /*
     If we have already executed SELECT, then it have not sense to prevent
     its table from update (see unique_table())
@@ -455,6 +456,17 @@ JOIN::prepare(Item ***rref_pointer_array
 
   select_lex->fix_prepare_information(thd, &conds, &having);
 
+  if (order)
+  {
+    ORDER *ord;
+    for (ord= order; ord; ord= ord->next)
+    {
+      Item *item= *ord->item;
+      if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
+        item->split_sum_func(thd, ref_pointer_array, all_fields);
+    }
+  }
+
   if (having && having->with_sum_func)
     having->split_sum_func2(thd, ref_pointer_array, all_fields,
                             &having, TRUE);
@@ -738,7 +750,6 @@ JOIN::optimize()
   }
 
   {
-    Item::cond_result having_value;
     having= optimize_cond(this, having, join_list, &having_value);
     if (thd->net.report_error)
     {
@@ -746,6 +757,10 @@ JOIN::optimize()
       DBUG_PRINT("error",("Error from optimize_cond"));
       DBUG_RETURN(1);
     }
+    if (select_lex->where)
+      select_lex->cond_value= cond_value;
+    if (select_lex->having)
+      select_lex->having_value= having_value;
 
     if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || 
         (!unit->select_limit_cnt && !(select_options &
OPTION_FOUND_ROWS)))
@@ -906,6 +921,7 @@ JOIN::optimize()
     conds->update_used_tables();
     DBUG_EXECUTE("where", print_where(conds, "after substitute_best_equal"););
   }
+
   /*
     Permorm the the optimization on fields evaluation mentioned above
     for all on expressions.
@@ -7772,6 +7788,9 @@ static COND* substitute_for_best_equal_f
           break;
       }
     }
+    if (!((Item_cond*)cond)->argument_list()->elements)
+      cond= new Item_int((int32)cond->val_bool());
+
   }
   else if (cond->type() == Item::FUNC_ITEM && 
            ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
@@ -13407,7 +13426,7 @@ find_order_in_list(THD *thd, Item **ref_
   Item **select_item; /* The corresponding item from the SELECT clause. */
   Field *from_field;  /* The corresponding field from the FROM clause. */
   uint counter;
-  bool unaliased;
+  enum_resolution_type resolution;
 
   /*
     Local SP variables may be int but are expressions, not positions.
@@ -13430,7 +13449,7 @@ find_order_in_list(THD *thd, Item **ref_
   }
   /* Lookup the current GROUP/ORDER field in the SELECT clause. */
   select_item= find_item_in_list(order_item, fields, &counter,
-                                 REPORT_EXCEPT_NOT_FOUND, &unaliased);
+                                 REPORT_EXCEPT_NOT_FOUND, &resolution);
   if (!select_item)
     return TRUE; /* The item is not unique, or some other error occured. */
 
@@ -13444,7 +13463,7 @@ find_order_in_list(THD *thd, Item **ref_
       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 && !order_item->fixed &&
+    if (resolution == RESOLVED_BEHIND_ALIAS && !order_item->fixed &&
         order_item->fix_fields(thd, order->item))
       return TRUE;
 
@@ -13514,16 +13533,11 @@ find_order_in_list(THD *thd, Item **ref_
     We check order_item->fixed because Item_func_group_concat can put
     arguments for which fix_fields already was called.
   */
-  thd->lex->current_select->is_item_list_lookup= 1;
   if (!order_item->fixed &&
       (order_item->fix_fields(thd, order->item) ||
        (order_item= *order->item)->check_cols(1) ||
        thd->is_fatal_error))
-  {
-    thd->lex->current_select->is_item_list_lookup= 0;
     return TRUE; /* Wrong field. */
-  }
-  thd->lex->current_select->is_item_list_lookup= 0;
 
   uint el= all_fields.elements;
   all_fields.push_front(order_item); /* Add new field to field list. */
@@ -13675,7 +13689,7 @@ setup_new_fields(THD *thd, List<Item> &f
 {
   Item	  **item;
   uint counter;
-  bool not_used;
+  enum_resolution_type not_used;
   DBUG_ENTER("setup_new_fields");
 
   thd->mark_used_columns= MARK_COLUMNS_READ;       // Not really needed, but...
@@ -15635,10 +15649,13 @@ void st_select_lex::print(THD *thd, Stri
   Item *cur_where= where;
   if (join)
     cur_where= join->conds;
-  if (cur_where)
+  if (cur_where || cond_value != Item::COND_UNDEF)
   {
     str->append(STRING_WITH_LEN(" where "));
-    cur_where->print(str);
+    if (cur_where)
+      cur_where->print(str);
+    else
+      str->append(cond_value != Item::COND_FALSE ? "1" : "0");
   }
 
   // group by & olap
@@ -15664,10 +15681,13 @@ void st_select_lex::print(THD *thd, Stri
   if (join)
     cur_having= join->having;
 
-  if (cur_having)
+  if (cur_having || having_value != Item::COND_UNDEF)
   {
     str->append(STRING_WITH_LEN(" having "));
-    cur_having->print(str);
+    if (cur_having)
+      cur_having->print(str);
+    else
+      str->append(having_value != Item::COND_FALSE ? "1" : "0");
   }
 
   if (order_list.elements)

--- 1.117/sql/sql_select.h	2007-03-08 19:08:35 +04:00
+++ 1.118/sql/sql_select.h	2007-03-08 19:08:35 +04:00
@@ -331,7 +331,7 @@ public:
 
   bool need_tmp, hidden_group_fields;
   DYNAMIC_ARRAY keyuse;
-  Item::cond_result cond_value;
+  Item::cond_result cond_value, having_value;
   List<Item> all_fields; // to store all fields that used in query
   //Above list changed to use temporary table
   List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;

--- 1.227/sql/sql_update.cc	2007-03-08 19:08:35 +04:00
+++ 1.228/sql/sql_update.cc	2007-03-08 19:08:35 +04:00
@@ -761,7 +761,7 @@ bool mysql_prepare_update(THD *thd, TABL
   /* Check that we are not using table that we are updating in a sub select */
   {
     TABLE_LIST *duplicate;
-    if ((duplicate= unique_table(thd, table_list, table_list->next_global)))
+    if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0)))
     {
       update_non_unique_table_error(table_list, "UPDATE", duplicate);
       my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
@@ -987,7 +987,7 @@ reopen_tables:
         tl->lock_type != TL_READ_NO_INSERT)
     {
       TABLE_LIST *duplicate;
-      if ((duplicate= unique_table(thd, tl, table_list)))
+      if ((duplicate= unique_table(thd, tl, table_list, 0)))
       {
         update_non_unique_table_error(table_list, "UPDATE", duplicate);
         DBUG_RETURN(TRUE);
@@ -1203,7 +1203,7 @@ static bool safe_update_on_fly(THD *thd,
                                TABLE_LIST *table_ref, TABLE_LIST *all_tables)
 {
   TABLE *table= join_tab->table;
-  if (unique_table(thd, table_ref, all_tables))
+  if (unique_table(thd, table_ref, all_tables, 0))
     return 0;
   switch (join_tab->type) {
   case JT_SYSTEM:

--- 1.166/mysql-test/r/subselect.result	2007-03-08 19:08:35 +04:00
+++ 1.167/mysql-test/r/subselect.result	2007-03-08 19:08:35 +04:00
@@ -421,7 +421,7 @@ id	select_type	table	type	possible_keys	
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1`
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -1180,7 +1180,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
@@ -1190,7 +1190,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
 CREATE TABLE `t1` (
 `i` int(11) NOT NULL default '0',
@@ -1532,7 +1532,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
@@ -1540,7 +1540,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1548,7 +1548,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
@@ -1556,7 +1556,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1618,7 +1618,7 @@ id	select_type	table	type	possible_keys	
 3	UNION	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

--- 1.205/mysql-test/r/view.result	2007-03-08 19:08:35 +04:00
+++ 1.206/mysql-test/r/view.result	2007-03-08 19:08:35 +04:00
@@ -3126,6 +3126,43 @@ code	COUNT(DISTINCT country)
 100	2
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (
+lid int NOT NULL PRIMARY KEY,
+name char(10) NOT NULL
+);
+INSERT INTO t1 (lid, name) VALUES
+(1, 'YES'), (2, 'NO');
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY, 
+gid int NOT NULL,
+lid int NOT NULL,
+dt date
+);
+INSERT INTO t2 (id, gid, lid, dt) VALUES
+(1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'),
+(3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02');
+SELECT DISTINCT t2.gid AS lgid,
+(SELECT t1.name FROM t1, t2
+WHERE t1.lid  = t2.lid AND t2.gid = lgid
+ORDER BY t2.dt DESC LIMIT 1
+) as clid
+FROM t2;
+lgid	clid
+1	NO
+2	YES
+CREATE VIEW v1 AS
+SELECT DISTINCT t2.gid AS lgid,
+(SELECT t1.name FROM t1, t2
+WHERE t1.lid  = t2.lid AND t2.gid = lgid
+ORDER BY t2.dt DESC LIMIT 1
+) as clid
+FROM t2;
+SELECT * FROM v1;
+lgid	clid
+1	NO
+2	YES
+DROP VIEW v1;
+DROP table t1,t2;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;

--- 1.185/mysql-test/t/view.test	2007-03-08 19:08:35 +04:00
+++ 1.186/mysql-test/t/view.test	2007-03-08 19:08:35 +04:00
@@ -3049,6 +3049,46 @@ SELECT code, COUNT(DISTINCT country) FRO
 DROP VIEW v1;
 DROP TABLE t1;
 
+#
+# Bug#26560: view using subquery with a reference to an outer alias 
+#
+
+CREATE TABLE t1 (
+  lid int NOT NULL PRIMARY KEY,
+  name char(10) NOT NULL
+);
+INSERT INTO t1 (lid, name) VALUES
+  (1, 'YES'), (2, 'NO');
+
+CREATE TABLE t2 (
+  id int NOT NULL PRIMARY KEY, 
+  gid int NOT NULL,
+  lid int NOT NULL,
+  dt date
+);
+INSERT INTO t2 (id, gid, lid, dt) VALUES
+ (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'),
+ (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02');
+
+SELECT DISTINCT t2.gid AS lgid,
+                (SELECT t1.name FROM t1, t2
+                   WHERE t1.lid  = t2.lid AND t2.gid = lgid
+                     ORDER BY t2.dt DESC LIMIT 1
+                ) as clid
+  FROM t2;
+
+CREATE VIEW v1 AS
+SELECT DISTINCT t2.gid AS lgid,
+                (SELECT t1.name FROM t1, t2
+                   WHERE t1.lid  = t2.lid AND t2.gid = lgid
+                     ORDER BY t2.dt DESC LIMIT 1
+                ) as clid
+  FROM t2;
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP table t1,t2;
+
 --echo End of 5.0 tests.
 
 #

--- 1.19/mysql-test/r/explain.result	2007-03-08 19:08:35 +04:00
+++ 1.20/mysql-test/r/explain.result	2007-03-08 19:08:35 +04:00
@@ -57,3 +57,33 @@ select 3 into @v1;
 explain select 3 into @v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1);
+create view v1 as select * from t1 where f1=1;
+explain extended select * from v1 where f2=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where
1
+explain extended select * from t1 where 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where
0
+explain extended select * from t1 where 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where
1
+explain extended select * from t1 having 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1`
having 0
+explain extended select * from t1 having 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1`
having 1
+drop view v1;
+drop table t1;

--- 1.37/mysql-test/r/func_in.result	2007-03-08 19:08:35 +04:00
+++ 1.38/mysql-test/r/func_in.result	2007-03-08 19:08:35 +04:00
@@ -399,6 +399,78 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 3
 3
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+a
+SELECT * FROM t1 WHERE a IN (-1, -2);
+a
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+(0x7fffffefffffffff),
+(0x7ffffffeffffffff),
+(0x7fffffffefffffff),
+(0x7ffffffffeffffff),
+(0x7fffffffffefffff),
+(0x7ffffffffffeffff),
+(0x7fffffffffffefff),
+(0x7ffffffffffffeff),
+(0x7fffffffffffffef),
+(0x7ffffffffffffffe),
+(0x7fffffffffffffff),
+(0x8000000000000000),
+(0x8000000000000001),
+(0x8000000000000002),
+(0x8000000000000300),
+(0x8000000000000400),
+(0x8000000000000401),
+(0x8000000000004001),
+(0x8000000000040001),
+(0x8000000000400001),
+(0x8000000004000001),
+(0x8000000040000001),
+(0x8000000400000001),
+(0x8000004000000001),
+(0x8000040000000001);
+SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
+HEX(a)
+BB3C3E98175D33C8
+SELECT HEX(a) FROM t2 WHERE a IN
+(0xBB3C3E98175D33C8,
+0x7fffffffffffffff,
+0x8000000000000000,
+0x8000000000000400,
+0x8000000000000401,
+42);
+HEX(a)
+BB3C3E98175D33C8
+7FFFFFFFFFFFFFFF
+8000000000000000
+8000000000000400
+8000000000000401
+SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001);
+HEX(a)
+7FFFFFFFFFFFFFFF
+8000000000000001
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff);
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc');
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+HEX(a)
+CREATE TABLE t4 (a DATE);
+INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
+SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
+a
+Warnings:
+Warning	1292	Incorrect date value: '19772-07-29' for column 'a' at row 1
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests
 create table t1(f1 char(1));
 insert into t1 values ('a'),('b'),('1');

--- 1.34/mysql-test/r/func_test.result	2007-03-08 19:08:35 +04:00
+++ 1.35/mysql-test/r/func_test.result	2007-03-08 19:08:35 +04:00
@@ -79,7 +79,7 @@ explain extended select * from t1 where 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
 select - a from t1;
 - a
 -1

--- 1.61/mysql-test/r/order_by.result	2007-03-08 19:08:36 +04:00
+++ 1.62/mysql-test/r/order_by.result	2007-03-08 19:08:36 +04:00
@@ -926,6 +926,14 @@ NULL
 2
 3
 DROP TABLE t1,t2,t3,t4;
+create table t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
+select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
+a	ratio
+1	0.5000
+19	1.3333
+9	2.6667
+drop table t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
 INSERT INTO t1 VALUES (1,1),(2,2);
 CREATE TABLE t2 (a INT, b INT, KEY a (a,b));

--- 1.34/mysql-test/r/update.result	2007-03-08 19:08:36 +04:00
+++ 1.35/mysql-test/r/update.result	2007-03-08 19:08:36 +04:00
@@ -377,6 +377,10 @@ create table t1(f1 int, `*f2` int);
 insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
+create table t1(f1 int);
+update t1 set f2=1 order by f2;
+ERROR 42S22: Unknown column 'f2' in 'order clause'
+drop table t1;
 CREATE TABLE t1 (
 request_id int unsigned NOT NULL auto_increment,
 user_id varchar(12) default NULL,

--- 1.11/mysql-test/t/explain.test	2007-03-08 19:08:36 +04:00
+++ 1.12/mysql-test/t/explain.test	2007-03-08 19:08:36 +04:00
@@ -51,4 +51,19 @@ set names latin1;
 select 3 into @v1;
 explain select 3 into @v1;
 
+#
+# Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were
+#            optimized away.
+#
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1);
+create view v1 as select * from t1 where f1=1;
+explain extended select * from v1 where f2=1;
+explain extended select * from t1 where 0;
+explain extended select * from t1 where 1;
+explain extended select * from t1 having 0;
+explain extended select * from t1 having 1;
+drop view v1;
+drop table t1;
+
 # End of 5.0 tests.

--- 1.29/mysql-test/t/func_in.test	2007-03-08 19:08:36 +04:00
+++ 1.30/mysql-test/t/func_in.test	2007-03-08 19:08:36 +04:00
@@ -299,6 +299,68 @@ SELECT STRAIGHT_JOIN 
 
 DROP TABLE t1,t2,t3,t4;  
 
+#
+# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values
+#
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+SELECT * FROM t1 WHERE a IN (-1, -2);
+
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+       (0x7fffffefffffffff),
+       (0x7ffffffeffffffff),
+       (0x7fffffffefffffff),
+       (0x7ffffffffeffffff),
+       (0x7fffffffffefffff),
+       (0x7ffffffffffeffff),
+       (0x7fffffffffffefff),
+       (0x7ffffffffffffeff),
+       (0x7fffffffffffffef),
+       (0x7ffffffffffffffe),
+       (0x7fffffffffffffff),
+       (0x8000000000000000),
+       (0x8000000000000001),
+       (0x8000000000000002),
+       (0x8000000000000300),
+       (0x8000000000000400),
+       (0x8000000000000401),
+       (0x8000000000004001),
+       (0x8000000000040001),
+       (0x8000000000400001),
+       (0x8000000004000001),
+       (0x8000000040000001),
+       (0x8000000400000001),
+       (0x8000004000000001),
+       (0x8000040000000001);
+
+SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
+
+SELECT HEX(a) FROM t2 WHERE a IN
+(0xBB3C3E98175D33C8,
+ 0x7fffffffffffffff,
+ 0x8000000000000000,
+ 0x8000000000000400,
+ 0x8000000000000401,
+ 42);
+
+SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001);
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff);
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc');
+
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+
+CREATE TABLE t4 (a DATE);
+INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
+SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
+
+DROP TABLE t1,t2,t3,t4;
+
 --echo End of 5.0 tests
 
 

--- 1.44/mysql-test/t/order_by.test	2007-03-08 19:08:36 +04:00
+++ 1.45/mysql-test/t/order_by.test	2007-03-08 19:08:36 +04:00
@@ -642,6 +642,14 @@ ON (t1.a=t2.a AND t1.b=t3.b) order by t2
 DROP TABLE t1,t2,t3,t4;
 
 #
+# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
+#
+create table t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
+select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
+drop table t1;
+
+#
 # BUG#16590: Optimized does not do right "const" table pre-read
 #
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));

--- 1.136/mysql-test/r/func_str.result	2007-03-08 19:08:36 +04:00
+++ 1.137/mysql-test/r/func_str.result	2007-03-08 19:08:36 +04:00
@@ -2273,4 +2273,10 @@ abcxx
 select lpad('abc', cast(5 as unsigned integer), 'x');
 lpad('abc', cast(5 as unsigned integer), 'x')
 xxabc
+SELECT UNHEX('G');
+UNHEX('G')
+NULL
+SELECT UNHEX('G') IS NULL;
+UNHEX('G') IS NULL
+1
 End of 5.0 tests

--- 1.102/mysql-test/t/func_str.test	2007-03-08 19:08:36 +04:00
+++ 1.103/mysql-test/t/func_str.test	2007-03-08 19:08:36 +04:00
@@ -1115,4 +1115,10 @@ select repeat('a', cast(2 as unsigned in
 select rpad('abc', cast(5 as unsigned integer), 'x');
 select lpad('abc', cast(5 as unsigned integer), 'x');
 
+#
+# Bug #26537: UNHEX() IS NULL comparison fails
+#
+SELECT UNHEX('G');
+SELECT UNHEX('G') IS NULL;
+
 --echo End of 5.0 tests

--- 1.21/BUILD/check-cpu	2007-03-08 19:08:36 +04:00
+++ 1.22/BUILD/check-cpu	2007-03-08 19:08:36 +04:00
@@ -114,6 +114,10 @@ check_cpu () {
     *i386*i486*)
       cpu_arg="pentium-m";
       ;;
+    #Core 2 Duo  
+    *Intel*Core\(TM\)2*)
+      cpu_arg="nocona";
+      ;;
 
     # Intel ia64
     *Itanium*)

--- 1.216/mysql-test/t/sp.test	2007-03-08 19:08:36 +04:00
+++ 1.217/mysql-test/t/sp.test	2007-03-08 19:08:36 +04:00
@@ -6803,6 +6803,21 @@ DROP PROCEDURE bug24117|
 DROP TABLE t3|
 
 #
+# Bug#25373: Stored functions wasn't compared correctly which leads to a wrong
+#            result.
+#
+--disable_warnings
+DROP FUNCTION IF EXISTS bug25373|
+--disable_warnings
+CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER
+LANGUAGE SQL DETERMINISTIC
+RETURN p1;|
+CREATE TABLE t3 (f1 INT, f2 FLOAT)|
+INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)|
+SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP|
+DROP FUNCTION bug25373|
+DROP TABLE t3|
+#
 # NOTE: The delimiter is `|`, and not `;`. It is changed to `;`
 #       at the end of the file!
 #
Thread
• bk commit into 5.1 tree (holyfoot:1.2466)	holyfoot	8 Mar