Merge sunlight.local:/local_work/tmp_merge-5.0-opt-mysql

into  sunlight.local:/local_work/tmp_merge-5.1-opt-mysql


client/mysql.cc:
  Auto merged
mysql-test/r/date_formats.result:
  Auto merged
mysql-test/r/func_gconcat.result:
  Auto merged
mysql-test/r/func_group.result:
  Auto merged
mysql-test/r/func_str.result:
  Auto merged
mysql-test/r/group_min_max.result:
  Auto merged
BitKeeper/deleted/.del-make_win_src_distribution.sh~f80d8fca44e4e5f1:
  Auto merged
BitKeeper/deleted/.del-mysqld.dsp~ffdbf2d234e23e56:
  Auto merged
BitKeeper/deleted/.del-mysqld_ia64.dsp~7f8cf84d81ee04e2:
  Auto merged
BitKeeper/deleted/.del-mysqldump.dsp~a8bd23547d3fc27e:
  Auto merged
BitKeeper/deleted/.del-mysqldump_ia64.dsp~a2aabe898be35b31:
  Auto merged
mysql-test/r/innodb.result:
  Auto merged
mysql-test/r/innodb_mysql.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/r/type_ranges.result:
  Auto merged
mysql-test/r/udf.result:
  Auto merged
mysql-test/r/union.result:
  Auto merged
mysql-test/r/view.result:
  Auto merged
mysql-test/t/date_formats.test:
  Auto merged
mysql-test/t/func_gconcat.test:
  Auto merged
mysql-test/t/func_group.test:
  Auto merged
mysql-test/t/group_min_max.test:
  Auto merged
mysql-test/t/innodb.test:
  Auto merged
mysql-test/t/innodb_mysql.test:
  Auto merged
mysql-test/t/mysql.test:
  Auto merged
mysql-test/t/select.test:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
mysql-test/t/view.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_func.h:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/opt_range.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
sql/time.cc:
  Auto merged
storage/ndb/test/ndbapi/Makefile.am:
  Auto merged
strings/decimal.c:
  Auto merged
mysql-test/r/analyse.result:
  Manual merge
mysql-test/r/bigint.result:
  Manual merge
mysql-test/r/create.result:
  Manual merge
mysql-test/r/information_schema.result:
  Manual merge
mysql-test/r/ps_2myisam.result:
  Manual merge
mysql-test/r/ps_3innodb.result:
  Manual merge
mysql-test/r/ps_4heap.result:
  Manual merge
mysql-test/r/ps_5merge.result:
  Manual merge
mysql-test/r/ps_6bdb.result:
  Manual merge
mysql-test/r/rpl_insert_id.result:
  Manual merge
mysql-test/r/select.result:
  Manual merge
mysql-test/r/sp.result:
  Manual merge
mysql-test/r/subselect.result:
  Manual merge
mysql-test/t/information_schema.test:
  Manual merge
mysql-test/t/rpl_insert_id.test:
  Manual merge
sql/field.h:
  Manual merge
sql/item.cc:
  Manual merge
sql/item.h:
  Manual merge
sql/item_strfunc.h:
  Manual merge
sql/item_sum.cc:
  Manual merge
sql/mysql_priv.h:
  Manual merge
sql/share/errmsg.txt:
  Manual merge
sql/sql_class.h:
  Manual merge
sql/sql_select.cc:
  Manual merge

1 files changed, 106 insertions, 17 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f224f333a98..52f09b13311 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -689,6 +689,24 @@ JOIN::optimize()
       DBUG_PRINT("info",("Select tables optimized away"));
       zero_result_cause= "Select tables optimized away";
       tables_list= 0;				// All tables resolved
+      /*
+        Extract all table-independent conditions and replace the WHERE
+        clause with them. All other conditions were computed by opt_sum_query
+        and the MIN/MAX/COUNT function(s) have been replaced by constants,
+        so there is no need to compute the whole WHERE clause again.
+        Notice that make_cond_for_table() will always succeed to remove all
+        computed conditions, because opt_sum_query() is applicable only to
+        conjunctions.
+      */
+      if (conds)
+      {
+        COND *table_independent_conds=
+          make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0);
+        DBUG_EXECUTE("where",
+                     print_where(table_independent_conds,
+                                 "where after opt_sum_query()"););
+        conds= table_independent_conds;
+      }
     }
   }
   if (!tables_list)
@@ -1067,6 +1085,23 @@ JOIN::optimize()
     {
       need_tmp=1; simple_order=simple_group=0;	// Force tmp table without sort
     }
+    if (order)
+    {
+      /*
+        Force using of tmp table if sorting by a SP or UDF function due to
+        their expensive and probably non-deterministic nature.
+      */
+      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      {
+        Item *item= *tmp_order->item;
+        if (item->walk(&Item::is_expensive_processor,(byte*)0))
+        {
+          /* Force tmp table without sort */
+          need_tmp=1; simple_order=simple_group=0;
+          break;
+        }
+      }
+    }
   }
 
   tmp_having= having;
@@ -1212,6 +1247,11 @@ int
 JOIN::reinit()
 {
   DBUG_ENTER("JOIN::reinit");
+
+  unit->offset_limit_cnt= (ha_rows)(select_lex->offset_limit ?
+                                    select_lex->offset_limit->val_uint() :
+                                    ULL(0));
+
   first_record= 0;
 
   if (exec_tmp_table1)
@@ -2492,8 +2532,11 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
 	  /* field = expression OR field IS NULL */
 	  old->level= and_level;
 	  old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
-	  /* Remember the NOT NULL value */
-	  if (old->val->is_null())
+	  /*
+            Remember the NOT NULL value unless the value does not depend
+            on other tables.
+          */
+	  if (!old->val->used_tables() && old->val->is_null())
 	    old->val= new_fields->val;
           /* The referred expression can be NULL: */ 
           old->null_rejecting= 0;
@@ -5520,6 +5563,7 @@ make_join_readinfo(JOIN *join, uint options)
 {
   uint i;
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
+  bool ordered_set= 0;
   bool sorted= 1;
   DBUG_ENTER("make_join_readinfo");
 
@@ -5530,6 +5574,22 @@ make_join_readinfo(JOIN *join, uint options)
     tab->read_record.table= table;
     tab->read_record.file=table->file;
     tab->next_select=sub_select;		/* normal select */
+
+    /*
+      Determine if the set is already ordered for ORDER BY, so it can 
+      disable join cache because it will change the ordering of the results.
+      Code handles sort table that is at any location (not only first after 
+      the const tables) despite the fact that it's currently prohibited.
+    */
+    if (!ordered_set && 
+        (table == join->sort_by_table &&
+         (!join->order || join->skip_sort_order ||
+          test_if_skip_sort_order(tab, join->order, join->select_limit,
+                                  1))
+        ) ||
+        (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
+      ordered_set= 1;
+
     tab->sorted= sorted;
     sorted= 0;                                  // only first must be sorted
     switch (tab->type) {
@@ -5602,10 +5662,11 @@ make_join_readinfo(JOIN *join, uint options)
     case JT_ALL:
       /*
 	If previous table use cache
+        If the incoming data set is already sorted don't use cache.
       */
       table->status=STATUS_NO_RECORD;
       if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
-          tab->use_quick != 2 && !tab->first_inner)
+          tab->use_quick != 2 && !tab->first_inner && !ordered_set)
       {
 	if ((options & SELECT_DESCRIBE) ||
 	    !join_init_cache(join->thd,join->join_tab+join->const_tables,
@@ -6192,10 +6253,16 @@ return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables,
   DBUG_RETURN(0);
 }
 
-
+/*
+  used only in JOIN::clear
+*/
 static void clear_tables(JOIN *join)
 {
-  for (uint i=0 ; i < join->tables ; i++)
+  /* 
+    must clear only the non-const tables, as const tables
+    are not re-calculated.
+  */
+  for (uint i=join->const_tables ; i < join->tables ; i++)
     mark_as_null_row(join->table[i]);		// All fields are NULL
 }
 
@@ -8056,7 +8123,12 @@ Field *create_tmp_field_from_field(THD *thd, Field *org_field,
 {
   Field *new_field;
 
-  if (convert_blob_length && (org_field->flags & BLOB_FLAG))
+  /* 
+    Make sure that the blob fits into a Field_varstring which has 
+    2-byte lenght. 
+  */
+  if (convert_blob_length && convert_blob_length < UINT_MAX16 &&
+      (org_field->flags & BLOB_FLAG))
     new_field= new Field_varstring(convert_blob_length,
                                    org_field->maybe_null(),
                                    org_field->field_name, table->s,
@@ -8121,8 +8193,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
                                 item->name, item->decimals);
     break;
   case INT_RESULT:
-    new_field= new Field_longlong(item->max_length, maybe_null,
-                                  item->name, item->unsigned_flag);
+    /* Select an integer type with the minimal fit precision */
+    if (item->max_length > 11)
+      new_field=new Field_longlong(item->max_length, maybe_null,
+                                   item->name, item->unsigned_flag);
+    else
+      new_field=new Field_long(item->max_length, maybe_null,
+                               item->name, item->unsigned_flag);
     break;
   case STRING_RESULT:
     DBUG_ASSERT(item->collation.collation);
@@ -8135,8 +8212,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
     if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
         type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE)
       new_field= item->tmp_table_field_from_field_type(table, 1);
+    /* 
+      Make sure that the blob fits into a Field_varstring which has 
+      2-byte lenght. 
+    */
     else if (item->max_length/item->collation.collation->mbmaxlen > 255 &&
-             convert_blob_length)
+             item->max_length/item->collation.collation->mbmaxlen < UINT_MAX16
+             && convert_blob_length)
       new_field= new Field_varstring(convert_blob_length, maybe_null,
                                      item->name, table->s,
                                      item->collation.collation);
@@ -10710,8 +10792,13 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
 	{
 	  if (!join->first_record)
 	  {
+            List_iterator_fast<Item> it(*join->fields);
+            Item *item;
 	    /* No matching rows for group function */
 	    join->clear();
+
+            while ((item= it++))
+              item->no_rows_in_result();
 	  }
 	  if (join->having && join->having->val_int() == 0)
 	    error= -1;				// Didn't satisfy having
@@ -12881,7 +12968,7 @@ count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
     {
       if (! field->const_item())
       {
-	Item_sum *sum_item=(Item_sum*) field;
+	Item_sum *sum_item=(Item_sum*) field->real_item();
 	if (!sum_item->quick_group)
 	  param->quick_group=0;			// UDF SUM function
 	param->sum_func_count++;
@@ -13141,10 +13228,11 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param,
   param->copy_funcs.empty();
   for (i= 0; (pos= li++); i++)
   {
-    if (pos->real_item()->type() == Item::FIELD_ITEM)
+    Item *real_pos= pos->real_item();
+    if (real_pos->type() == Item::FIELD_ITEM)
     {
       Item_field *item;
-      pos= pos->real_item();
+      pos= real_pos;
       if (!(item= new Item_field(thd, ((Item_field*) pos))))
 	goto err;
       pos= item;
@@ -13183,12 +13271,13 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param,
       }				
       }
     }
-    else if ((pos->type() == Item::FUNC_ITEM ||
-	      pos->type() == Item::SUBSELECT_ITEM ||
-	      pos->type() == Item::CACHE_ITEM ||
-	      pos->type() == Item::COND_ITEM) &&
-	     !pos->with_sum_func)
+    else if ((real_pos->type() == Item::FUNC_ITEM ||
+	      real_pos->type() == Item::SUBSELECT_ITEM ||
+	      real_pos->type() == Item::CACHE_ITEM ||
+	      real_pos->type() == Item::COND_ITEM) &&
+	     !real_pos->with_sum_func)
     {						// Save for send fields
+      pos= real_pos;
       /* TODO:
 	 In most cases this result will be sent to the user.
 	 This should be changed to use copy_int or copy_real depending