Merge bk-internal:/home/bk/mysql-5.1

into  magare.gmz:/home/kgeorge/mysql/work/merge-build-5.1-bugteam


sql/log.cc:
  Auto merged
sql/slave.cc:
  Auto merged
sql/share/errmsg.txt:
  merged 5.1-main to 5.1-bugteam

16 files changed, 425 insertions, 201 deletions

diff --git a/sql/item.cc b/sql/item.cc
index 883c293f645..fb8b0c1de2b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4003,9 +4003,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
       }
       if ((ret= fix_outer_field(thd, &from_field, reference)) < 0)
         goto error;
-      else if (!ret)
-        return FALSE;
       outer_fixed= TRUE;
+      if (!ret)
+        goto mark_non_agg_field;
     }
     else if (!from_field)
       goto error;
@@ -4017,9 +4017,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
       int ret;
       if ((ret= fix_outer_field(thd, &from_field, reference)) < 0)
         goto error;
-      if (!ret)
-        return FALSE;
       outer_fixed= 1;
+      if (!ret)
+        goto mark_non_agg_field;
     }
 
     /*
@@ -4103,6 +4103,26 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
     thd->lex->current_select->non_agg_fields.push_back(this);
     marker= thd->lex->current_select->cur_pos_in_select_list;
   }
+mark_non_agg_field:
+  if (fixed && thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+  {
+    /*
+      Mark selects according to presence of non aggregated fields.
+      Fields from outer selects added to the aggregate function
+      outer_fields list as its unknown at the moment whether it's
+      aggregated or not.
+    */
+    if (!thd->lex->in_sum_func)
+      cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+    else
+    {
+      if (outer_fixed)
+        thd->lex->in_sum_func->outer_fields.push_back(this);
+      else if (thd->lex->in_sum_func->nest_level !=
+          thd->lex->current_select->nest_level)
+        cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+    }
+  }
   return FALSE;
 
 error:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0ccadaf28da..ea16f3c3518 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1498,6 +1498,19 @@ Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
 
   DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
 
+  {
+    /*
+      IN/SOME/ALL/ANY subqueries aren't support LIMIT clause. Without it
+      ORDER BY clause becomes meaningless thus we drop it here.
+    */
+    SELECT_LEX *sl= current->master_unit()->first_select();
+    for (; sl; sl= sl->next_select())
+    {
+      if (sl->join)
+        sl->join->order= 0;
+    }
+  }
+
   if (changed)
   {
     DBUG_RETURN(RES_OK);
@@ -1532,6 +1545,7 @@ Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
 
   transformed= 1;
   arena= thd->activate_stmt_arena_if_needed(&backup);
+
   /*
     Both transformers call fix_fields() only for Items created inside them,
     and all that items do not make permanent changes in current item arena
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index ce9e2ad906c..264b53c780a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -68,6 +68,7 @@ bool Item_sum::init_sum_func_check(THD *thd)
   aggr_sel= NULL;
   max_arg_level= -1;
   max_sum_func_level= -1;
+  outer_fields.empty();
   return FALSE;
 }
 
@@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
                MYF(0));
     return TRUE;
   }
+
   if (in_sum_func)
   {
     /*
@@ -196,6 +198,68 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
       set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
     set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
   }
+
+  /*
+    Check that non-aggregated fields and sum functions aren't mixed in the
+    same select in the ONLY_FULL_GROUP_BY mode.
+  */
+  if (outer_fields.elements)
+  {
+    Item_field *field;
+    /*
+      Here we compare the nesting level of the select to which an outer field
+      belongs to with the aggregation level of the sum function. All fields in
+      the outer_fields list are checked.
+
+      If the nesting level is equal to the aggregation level then the field is
+        aggregated by this sum function.
+      If the nesting level is less than the aggregation level then the field
+        belongs to an outer select. In this case if there is an embedding sum
+        function add current field to functions outer_fields list. If there is
+        no embedding function then the current field treated as non aggregated
+        and the select it belongs to is marked accordingly.
+      If the nesting level is greater than the aggregation level then it means
+        that this field was added by an inner sum function.
+        Consider an example:
+
+          select avg ( <-- we are here, checking outer.f1
+            select (
+              select sum(outer.f1 + inner.f1) from inner
+            ) from outer)
+          from most_outer;
+
+        In this case we check that no aggregate functions are used in the
+        select the field belongs to. If there are some then an error is
+        raised.
+    */
+    List_iterator<Item_field> of(outer_fields);
+    while ((field= of++))
+    {
+      SELECT_LEX *sel= field->cached_table->select_lex;
+      if (sel->nest_level < aggr_level)
+      {
+        if (in_sum_func)
+        {
+          /*
+            Let upper function decide whether this field is a non
+            aggregated one.
+          */
+          in_sum_func->outer_fields.push_back(field);
+        }
+        else
+          sel->full_group_by_flag|= NON_AGG_FIELD_USED;
+      }
+      if (sel->nest_level > aggr_level &&
+          (sel->full_group_by_flag & SUM_FUNC_USED) &&
+          !sel->group_list.elements)
+      {
+        my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+                   ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+        return TRUE;
+      }
+    }
+  }
+  aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
   update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
   return FALSE;
diff --git a/sql/item_sum.h b/sql/item_sum.h
index d1e6a74e85e..bee8792fbfa 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -239,6 +239,13 @@ public:
   int8 max_arg_level;     /* max level of unbound column references          */
   int8 max_sum_func_level;/* max level of aggregation for embedded functions */
   bool quick_group;			/* If incremental update of fields */
+  /*
+    This list is used by the check for mixing non aggregated fields and
+    sum functions in the ONLY_FULL_GROUP_BY_MODE. We save all outer fields
+    directly or indirectly used under this function it as it's unclear
+    at the moment of fixing outer field whether it's aggregated or not.
+  */
+  List<Item_field> outer_fields;
 
 protected:  
   table_map used_tables_cache;
diff --git a/sql/log.cc b/sql/log.cc
index 764396ec5e9..46efc68fc38 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -52,8 +52,6 @@ LOGGER logger;
 MYSQL_BIN_LOG mysql_bin_log;
 ulong sync_binlog_counter= 0;
 
-static Muted_query_log_event invisible_commit;
-
 static bool test_if_number(const char *str,
 			   long *res, bool allow_wildcards);
 static int binlog_init(void *p);
@@ -155,7 +153,7 @@ private:
 class binlog_trx_data {
 public:
   binlog_trx_data()
-    : m_pending(0), before_stmt_pos(MY_OFF_T_UNDEF)
+    : at_least_one_stmt(0), m_pending(0), before_stmt_pos(MY_OFF_T_UNDEF)
   {
     trans_log.end_of_file= max_binlog_cache_size;
   }
@@ -188,6 +186,16 @@ public:
     reinit_io_cache(&trans_log, WRITE_CACHE, pos, 0, 0);
     if (pos < before_stmt_pos)
       before_stmt_pos= MY_OFF_T_UNDEF;
+
+    /*
+      The only valid positions that can be truncated to are at the
+      beginning of a statement. We are relying on this fact to be able
+      to set the at_least_one_stmt flag correctly. In other word, if
+      we are truncating to the beginning of the transaction cache,
+      there will be no statements in the cache, otherwhise, we will
+      have at least one statement in the transaction cache.
+     */
+    at_least_one_stmt= (pos > 0);
   }
 
   /*
@@ -213,6 +221,12 @@ public:
 
   IO_CACHE trans_log;                         // The transaction cache
 
+  /**
+    Boolean that is true if there is at least one statement in the
+    transaction cache.
+  */
+  bool at_least_one_stmt;
+
 private:
   /*
     Pending binrows event. This event is the event where the rows are
@@ -1374,26 +1388,20 @@ binlog_end_trans(THD *thd, binlog_trx_data *trx_data,
       inside a stored function.
      */
     thd->binlog_flush_pending_rows_event(TRUE);
+
+    error= mysql_bin_log.write(thd, &trx_data->trans_log, end_ev);
+    trx_data->reset();
+
     /*
-      We write the transaction cache to the binary log if either we're
-      committing the entire transaction, or if we are doing an
-      autocommit outside a transaction.
-     */
-    if (all || !(thd->options & (OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT)))
+      We need to step the table map version after writing the
+      transaction cache to disk.
+    */
+    mysql_bin_log.update_table_map_version();
+    statistic_increment(binlog_cache_use, &LOCK_status);
+    if (trans_log->disk_writes != 0)
     {
-      error= mysql_bin_log.write(thd, &trx_data->trans_log, end_ev);
-      trx_data->reset();
-      /*
-        We need to step the table map version after writing the
-        transaction cache to disk.
-      */
-      mysql_bin_log.update_table_map_version();
-      statistic_increment(binlog_cache_use, &LOCK_status);
-      if (trans_log->disk_writes != 0)
-      {
-        statistic_increment(binlog_cache_disk_use, &LOCK_status);
-        trans_log->disk_writes= 0;
-      }
+      statistic_increment(binlog_cache_disk_use, &LOCK_status);
+      trans_log->disk_writes= 0;
     }
   }
   else
@@ -1432,6 +1440,8 @@ static int binlog_prepare(handlerton *hton, THD *thd, bool all)
   return 0;
 }
 
+#define YESNO(X) ((X) ? "yes" : "no")
+
 /**
   This function is called once after each statement.
 
@@ -1440,10 +1450,8 @@ static int binlog_prepare(handlerton *hton, THD *thd, bool all)
 
   @param hton  The binlog handlerton.
   @param thd   The client thread that executes the transaction.
-  @param all   true if this is the last statement before a COMMIT
-               statement; false if either this is a statement in a
-               transaction but not the last, or if this is a statement
-               not inside a BEGIN block and autocommit is on.
+  @param all   This is @c true if this is a real transaction commit, and
+               @false otherwise.
 
   @see handlerton::commit
 */
@@ -1459,26 +1467,86 @@ static int binlog_commit(handlerton *hton, THD *thd, bool all)
     trx_data->reset();
     DBUG_RETURN(0);
   }
+
   /*
-    Write commit event if at least one of the following holds:
-     - the user sends an explicit COMMIT; or
-     - the autocommit flag is on, and we are not inside a BEGIN.
-    However, if the user has not sent an explicit COMMIT, and we are
-    either inside a BEGIN or run with autocommit off, then this is not
-    the end of a transaction and we should not write a commit event.
+    Decision table for committing a transaction. The top part, the
+    *conditions* represent different cases that can occur, and hte
+    bottom part, the *actions*, represent what should be done in that
+    particular case.
+
+    Real transaction        'all' was true
+
+    Statement in cache      There were at least one statement in the
+                            transaction cache
+
+    In transaction          We are inside a transaction
+
+    Stmt modified non-trans The statement being committed modified a
+                            non-transactional table
+
+    All modified non-trans  Some statement before this one in the
+                            transaction modified a non-transactional
+                            table
+
+
+    =============================  = = = = = = = = = = = = = = = =
+    Real transaction               N N N N N N N N N N N N N N N N
+    Statement in cache             N N N N N N N N Y Y Y Y Y Y Y Y
+    In transaction                 N N N N Y Y Y Y N N N N Y Y Y Y
+    Stmt modified non-trans        N N Y Y N N Y Y N N Y Y N N Y Y
+    All modified non-trans         N Y N Y N Y N Y N Y N Y N Y N Y
+
+    Action: (C)ommit/(A)ccumulate  C C - C A C - C - - - - A A - A
+    =============================  = = = = = = = = = = = = = = = =
+
+
+    =============================  = = = = = = = = = = = = = = = =
+    Real transaction               Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
+    Statement in cache             N N N N N N N N Y Y Y Y Y Y Y Y
+    In transaction                 N N N N Y Y Y Y N N N N Y Y Y Y
+    Stmt modified non-trans        N N Y Y N N Y Y N N Y Y N N Y Y
+    All modified non-trans         N Y N Y N Y N Y N Y N Y N Y N Y
+
+    (C)ommit/(A)ccumulate/(-)      - - - - C C - C - - - - C C - C
+    =============================  = = = = = = = = = = = = = = = =
+
+    In other words, we commit the transaction if and only if both of
+    the following are true:
+     - We are not in a transaction and committing a statement
+
+     - We are in a transaction and one (or more) of the following are
+       true:
+
+       - A full transaction is committed
+
+         OR
+
+       - A non-transactional statement is committed and there is
+         no statement cached
+
+    Otherwise, we accumulate the statement
   */
-  if (all || !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
+  ulonglong const in_transaction=
+    thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN);
+  DBUG_PRINT("debug",
+             ("all: %d, empty: %s, in_transaction: %s, all.modified_non_trans_table: %s, stmt.modified_non_trans_table: %s",
+              all,
+              YESNO(trx_data->empty()),
+              YESNO(in_transaction),
+              YESNO(thd->transaction.all.modified_non_trans_table),
+              YESNO(thd->transaction.stmt.modified_non_trans_table)));
+  if (in_transaction &&
+      (all ||
+       (!trx_data->at_least_one_stmt &&
+        thd->transaction.stmt.modified_non_trans_table)) ||
+      !in_transaction && !all)
   {
     Query_log_event qev(thd, STRING_WITH_LEN("COMMIT"), TRUE, FALSE);
     qev.error_code= 0; // see comment in MYSQL_LOG::write(THD, IO_CACHE)
     int error= binlog_end_trans(thd, trx_data, &qev, all);
     DBUG_RETURN(error);
   }
-  else
-  {
-    int error= binlog_end_trans(thd, trx_data, &invisible_commit, all);
-    DBUG_RETURN(error);
-  }
+  DBUG_RETURN(0);
 }
 
 /**
@@ -1491,10 +1559,8 @@ static int binlog_commit(handlerton *hton, THD *thd, bool all)
 
   @param hton  The binlog handlerton.
   @param thd   The client thread that executes the transaction.
-  @param all   true if this is the last statement before a COMMIT
-               statement; false if either this is a statement in a
-               transaction but not the last, or if this is a statement
-               not inside a BEGIN block and autocommit is on.
+  @param all   This is @c true if this is a real transaction rollback, and
+               @false otherwise.
 
   @see handlerton::rollback
 */
@@ -1510,21 +1576,36 @@ static int binlog_rollback(handlerton *hton, THD *thd, bool all)
     DBUG_RETURN(0);
   }
 
-  /*
-    Update the binary log with a BEGIN/ROLLBACK block if we have
-    cached some queries and we updated some non-transactional
-    table. Such cases should be rare (updating a
-    non-transactional table inside a transaction...)
-  */
-  if (unlikely(thd->transaction.all.modified_non_trans_table || 
-               (thd->options & OPTION_KEEP_LOG)))
+  DBUG_PRINT("debug", ("all: %s, all.modified_non_trans_table: %s, stmt.modified_non_trans_table: %s",
+                       YESNO(all),
+                       YESNO(thd->transaction.all.modified_non_trans_table),
+                       YESNO(thd->transaction.stmt.modified_non_trans_table)));
+  if (all && thd->transaction.all.modified_non_trans_table ||
+      !all && thd->transaction.stmt.modified_non_trans_table ||
+      (thd->options & OPTION_KEEP_LOG))
   {
+    /*
+      We write the transaction cache with a rollback last if we have
+      modified any non-transactional table. We do this even if we are
+      committing a single statement that has modified a
+      non-transactional table since it can have modified a
+      transactional table in that statement as well, which needs to be
+      rolled back on the slave.
+    */
     Query_log_event qev(thd, STRING_WITH_LEN("ROLLBACK"), TRUE, FALSE);
     qev.error_code= 0; // see comment in MYSQL_LOG::write(THD, IO_CACHE)
     error= binlog_end_trans(thd, trx_data, &qev, all);
   }
-  else
+  else if (all && !thd->transaction.all.modified_non_trans_table ||
+           !all && !thd->transaction.stmt.modified_non_trans_table)
+  {
+    /*
+      If we have modified only transactional tables, we can truncate
+      the transaction cache without writing anything to the binary
+      log.
+     */
     error= binlog_end_trans(thd, trx_data, 0, all);
+  }
   DBUG_RETURN(error);
 }
 
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 1f5351c1b3f..6b0e19f27cb 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -2607,21 +2607,6 @@ Query_log_event::do_shall_skip(Relay_log_info *rli)
 
 
 /**************************************************************************
-	Muted_query_log_event methods
-**************************************************************************/
-
-#ifndef MYSQL_CLIENT
-/*
-  Muted_query_log_event::Muted_query_log_event()
-*/
-Muted_query_log_event::Muted_query_log_event()
-  :Query_log_event()
-{
-}
-#endif
-
-
-/**************************************************************************
 	Start_log_event_v3 methods
 **************************************************************************/
 
@@ -7058,7 +7043,7 @@ int Table_map_log_event::save_field_metadata()
 #if !defined(MYSQL_CLIENT)
 Table_map_log_event::Table_map_log_event(THD *thd, TABLE *tbl, ulong tid,
                                          bool is_transactional, uint16 flags)
-  : Log_event(thd, 0, is_transactional),
+  : Log_event(thd, 0, true),
     m_table(tbl),
     m_dbnam(tbl->s->db.str),
     m_dblen(m_dbnam ? tbl->s->db.length : 0),
diff --git a/sql/log_event.h b/sql/log_event.h
index 2cf69e975f4..76d92b23189 100644
--- a/sql/log_event.h
+++ b/sql/log_event.h
@@ -1599,31 +1599,6 @@ public:        /* !!! Public in this patch to allow old usage */
 };
 
 
-/**
-  @class Muted_query_log_event
-
-  Pretends to log SQL queries, but doesn't actually do so.  This is
-  used internally only and never written to any binlog.
-
-  @section Muted_query_log_event_binary_format Binary Format
-
-  This log event is not stored, and thus the binary format is 0 bytes
-  long.  Note that not even the Common-Header is stored.
-*/
-class Muted_query_log_event: public Query_log_event
-{
-public:
-#ifndef MYSQL_CLIENT
-  Muted_query_log_event();
-
-  bool write(IO_CACHE* file) { return(false); };
-  virtual bool write_post_header_for_derived(IO_CACHE* file) { return FALSE; }
-#else
-  Muted_query_log_event() {}
-#endif
-};
-
-
 #ifdef HAVE_REPLICATION
 
 /**
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 75220a0fff1..69f214d95f3 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1407,6 +1407,13 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables,
 extern Item **not_found_item;
 
 /*
+  A set of constants used for checking non aggregated fields and sum
+  functions mixture in the ONLY_FULL_GROUP_BY_MODE.
+*/
+#define NON_AGG_FIELD_USED  1
+#define SUM_FUNC_USED       2
+
+/*
   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.
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index a1ad097f12c..e5709f418f7 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5722,52 +5722,70 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
        field->type() == MYSQL_TYPE_DATETIME))
     field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
   err= value->save_in_field_no_warnings(field, 1);
-  if (err > 0 && field->cmp_type() != value->result_type())
+  if (err > 0)
   {
-    if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
-	value->result_type() == item_cmp_type(field->result_type(),
-                                              value->result_type()))
-
+    if (field->cmp_type() != value->result_type())
     {
-      tree= new (alloc) SEL_ARG(field, 0, 0);
-      tree->type= SEL_ARG::IMPOSSIBLE;
-      goto end;
-    }
-    else
-    {
-      /*
-        TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
-        for the cases like int_field > 999999999999999999999999 as well.
-      */
-      tree= 0;
-      if (err == 3 && field->type() == FIELD_TYPE_DATE && 
-          (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC || 
-           type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+      if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
+          value->result_type() == item_cmp_type(field->result_type(),
+                                                value->result_type()))
+      {
+        tree= new (alloc) SEL_ARG(field, 0, 0);
+        tree->type= SEL_ARG::IMPOSSIBLE;
+        goto end;
+      }
+      else
       {
         /*
-          We were saving DATETIME into a DATE column, the conversion went ok
-          but a non-zero time part was cut off.
+          TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
+          for the cases like int_field > 999999999999999999999999 as well.
+        */
+        tree= 0;
+        if (err == 3 && field->type() == FIELD_TYPE_DATE &&
+            (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC ||
+             type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+        {
+          /*
+            We were saving DATETIME into a DATE column, the conversion went ok
+            but a non-zero time part was cut off.
 
-          In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
-          values. Index over a DATE column uses DATE comparison. Changing 
-          from one comparison to the other is possible:
+            In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
+            values. Index over a DATE column uses DATE comparison. Changing 
+            from one comparison to the other is possible:
 
-          datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
-          datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
+            datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
+            datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
 
-          datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
-          datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
+            datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
+            datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
 
-          but we'll need to convert '>' to '>=' and '<' to '<='. This will
-          be done together with other types at the end of this function
-          (grep for field_is_equal_to_item)
-        */
+            but we'll need to convert '>' to '>=' and '<' to '<='. This will
+            be done together with other types at the end of this function
+            (grep for field_is_equal_to_item)
+          */
+        }
+        else
+          goto end;
       }
-      else
-        goto end;
     }
-  } 
-  if (err < 0)
+
+    /*
+      guaranteed at this point:  err > 0; field and const of same type
+      If an integer got bounded (e.g. to within 0..255 / -128..127)
+      for < or >, set flags as for <= or >= (no NEAR_MAX / NEAR_MIN)
+    */
+    else if (err == 1 && field->result_type() == INT_RESULT)
+    {
+      if (type == Item_func::LT_FUNC && (value->val_int() > 0))
+        type = Item_func::LE_FUNC;
+      else if (type == Item_func::GT_FUNC &&
+               !((Field_num*)field)->unsigned_flag &&
+               !((Item_int*)value)->unsigned_flag &&
+               (value->val_int() < 0))
+        type = Item_func::GE_FUNC;
+    }
+  }
+  else if (err < 0)
   {
     field->table->in_use->variables.sql_mode= orig_sql_mode;
     /* This happens when we try to insert a NULL field in a not null column */
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 3e12cbf4f22..d37d8972c49 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -6120,5 +6120,8 @@ ER_NO_FORMAT_DESCRIPTION_EVENT_BEFORE_BINLOG_STATEMENT
   eng "The BINLOG statement of type `%s` was not preceded by a format description BINLOG statement."
 ER_SLAVE_CORRUPT_EVENT
   eng "Corrupted replication event was detected"
+ER_LOAD_DATA_INVALID_COLUMN
+  eng "Invalid column reference (%-.64s) in LOAD DATA"
+
 ER_LOG_PURGE_NO_FILE  
 	eng "Being purged log %s was not found"
diff --git a/sql/sql_connect.cc b/sql/sql_connect.cc
index 6f8cd6494cd..d9fb586eb77 100644
--- a/sql/sql_connect.cc
+++ b/sql/sql_connect.cc
@@ -701,20 +701,24 @@ static int check_connection(THD *thd)
     bzero((char*) &thd->remote, sizeof(thd->remote));
   }
   vio_keepalive(net->vio, TRUE);
+  
+  ulong server_capabilites;
   {
     /* buff[] needs to big enough to hold the server_version variable */
     char buff[SERVER_VERSION_LENGTH + SCRAMBLE_LENGTH + 64];
-    ulong client_flags = (CLIENT_LONG_FLAG | CLIENT_CONNECT_WITH_DB |
-			  CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION);
+    server_capabilites= CLIENT_BASIC_FLAGS;
 
     if (opt_using_transactions)
-      client_flags|=CLIENT_TRANSACTIONS;
+      server_capabilites|= CLIENT_TRANSACTIONS;
 #ifdef HAVE_COMPRESS
-    client_flags |= CLIENT_COMPRESS;
+    server_capabilites|= CLIENT_COMPRESS;
 #endif /* HAVE_COMPRESS */
 #ifdef HAVE_OPENSSL
     if (ssl_acceptor_fd)
-      client_flags |= CLIENT_SSL;       /* Wow, SSL is available! */
+    {
+      server_capabilites |= CLIENT_SSL;       /* Wow, SSL is available! */
+      server_capabilites |= CLIENT_SSL_VERIFY_SERVER_CERT;
+    }
 #endif /* HAVE_OPENSSL */
 
     end= strnmov(buff, server_version, SERVER_VERSION_LENGTH) + 1;
@@ -733,7 +737,7 @@ static int check_connection(THD *thd)
     */
     end= strmake(end, thd->scramble, SCRAMBLE_LENGTH_323) + 1;
    
-    int2store(end, client_flags);
+    int2store(end, server_capabilites);
     /* write server characteristics: up to 16 bytes allowed */
     end[2]=(char) default_charset_info->number;
     int2store(end+3, thd->server_status);
@@ -763,7 +767,7 @@ static int check_connection(THD *thd)
   if (thd->packet.alloc(thd->variables.net_buffer_length))
     return 1; /* The error is set by alloc(). */
 
-  thd->client_capabilities=uint2korr(net->read_pos);
+  thd->client_capabilities= uint2korr(net->read_pos);
   if (thd->client_capabilities & CLIENT_PROTOCOL_41)
   {
     thd->client_capabilities|= ((ulong) uint2korr(net->read_pos+2)) << 16;
@@ -778,6 +782,11 @@ static int check_connection(THD *thd)
     thd->max_client_packet_length= uint3korr(net->read_pos+2);
     end= (char*) net->read_pos+5;
   }
+  /*
+    Disable those bits which are not supported by the server.
+    This is a precautionary measure, if the client lies. See Bug#27944.
+  */
+  thd->client_capabilities&= server_capabilites;
 
   if (thd->client_capabilities & CLIENT_IGNORE_SPACE)
     thd->variables.sql_mode|= MODE_IGNORE_SPACE;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 58acf40964b..89038bacda3 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -701,8 +701,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
 
   error=0;
   thd_proc_info(thd, "update");
-  if (duplic != DUP_ERROR || ignore)
-    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
   if (duplic == DUP_REPLACE &&
       (!table->triggers || !table->triggers->has_delete_triggers()))
     table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);
@@ -720,8 +718,15 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
     values_list.elements, and - if nothing else - to initialize
     the code to make the call of end_bulk_insert() below safe.
   */
-  if (lock_type != TL_WRITE_DELAYED && !thd->prelocked_mode)
-    table->file->ha_start_bulk_insert(values_list.elements);
+#ifndef EMBEDDED_LIBRARY
+  if (lock_type != TL_WRITE_DELAYED)
+#endif /* EMBEDDED_LIBRARY */
+  {
+    if (duplic != DUP_ERROR || ignore)
+      table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+    if (!thd->prelocked_mode)
+      table->file->ha_start_bulk_insert(values_list.elements);
+  }
 
   thd->abort_on_warning= (!ignore && (thd->variables.sql_mode &
                                        (MODE_STRICT_TRANS_TABLES |
@@ -840,6 +845,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
       table->file->print_error(my_errno,MYF(0));
       error=1;
     }
+    if (duplic != DUP_ERROR || ignore)
+      table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
+
     transactional_table= table->file->has_transactions();
 
     if ((changed= (info.copied || info.deleted || info.updated)))
@@ -932,8 +940,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
   table->next_number_field=0;
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;
   table->auto_increment_field_not_null= FALSE;
-  if (duplic != DUP_ERROR || ignore)
-    table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
   if (duplic == DUP_REPLACE &&
       (!table->triggers || !table->triggers->has_delete_triggers()))
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
@@ -1267,7 +1273,12 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
     select_lex->fix_prepare_information(thd, &fake_conds, &fake_conds);
     select_lex->first_execution= 0;
   }
-  if (duplic == DUP_UPDATE || duplic == DUP_REPLACE)
+  /*
+    Only call prepare_for_posistion() if we are not performing a DELAYED
+    operation. It will instead be executed by delayed insert thread.
+  */
+  if ((duplic == DUP_UPDATE || duplic == DUP_REPLACE) &&
+      (table->reginfo.lock_type != TL_WRITE_DELAYED))
     table->prepare_for_position();
   DBUG_RETURN(FALSE);
 }
@@ -2426,6 +2437,7 @@ pthread_handler_t handle_delayed_insert(void *arg)
       */
       di->table->file->ha_release_auto_increment();
       mysql_unlock_tables(thd, lock);
+      ha_autocommit_or_rollback(thd, 0);
       di->group_count=0;
       pthread_mutex_lock(&di->mutex);
     }
@@ -3690,13 +3702,10 @@ void select_create::abort()
   DBUG_ENTER("select_create::abort");
 
   /*
-   Disable binlog, because we "roll back" partial inserts in ::abort
-   by removing the table, even for non-transactional tables.
-  */
-  tmp_disable_binlog(thd);
-  /*
     In select_insert::abort() we roll back the statement, including
-    truncating the transaction cache of the binary log.
+    truncating the transaction cache of the binary log. To do this, we
+    pretend that the statement is transactional, even though it might
+    be the case that it was not.
 
     We roll back the statement prior to deleting the table and prior
     to releasing the lock on the table, since there might be potential
@@ -3707,7 +3716,9 @@ void select_create::abort()
     of the table succeeded or not, since we need to reset the binary
     log state.
   */
+  tmp_disable_binlog(thd);
   select_insert::abort();
+  thd->transaction.stmt.modified_non_trans_table= FALSE;
   reenable_binlog(thd);
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b07efc62a00..449c2fccb0b 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1601,6 +1601,7 @@ void st_select_lex::init_select()
   non_agg_fields.empty();
   cond_value= having_value= Item::COND_UNDEF;
   inner_refs_list.empty();
+  full_group_by_flag= 0;
 }
 
 /*
@@ -1836,8 +1837,6 @@ bool st_select_lex::test_limit()
              "LIMIT & IN/ALL/ANY/SOME subquery");
     return(1);
   }
-  // no sense in ORDER BY without LIMIT
-  order_list.empty();
   return(0);
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b1a0d506382..443c85b4854 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -692,6 +692,16 @@ public:
     joins on the right.
   */
   List<String> *prev_join_using;
+  /*
+    Bitmap used in the ONLY_FULL_GROUP_BY_MODE to prevent mixture of aggregate
+    functions and non aggregated fields when GROUP BY list is absent.
+    Bits:
+      0 - non aggregated fields are used in this select,
+          defined as NON_AGG_FIELD_USED.
+      1 - aggregate functions are used in this select,
+          defined as SUM_FUNC_USED.
+  */
+  uint8 full_group_by_flag;
   void init_query();
   void init_select();
   st_select_lex_unit* master_unit();
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index a96d6f23a22..255d8e5813d 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -237,9 +237,11 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
 
   while ((item= it++))
   {
-    if (item->type() == Item::FIELD_ITEM)
+    Item *real_item= item->real_item();
+
+    if (real_item->type() == Item::FIELD_ITEM)
     {
-      Field *field= ((Item_field*)item)->field;
+      Field *field= ((Item_field*)real_item)->field;
       if (field->flags & BLOB_FLAG)
       {
         use_blobs= 1;
@@ -248,7 +250,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
       else
         tot_length+= field->field_length;
     }
-    else
+    else if (item->type() == Item::STRING_ITEM)
       use_vars= 1;
   }
   if (use_blobs && !ex->line_term->length() && !field_term->length())
@@ -706,6 +708,7 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
     {
       uint length;
       uchar *pos;
+      Item *real_item;
 
       if (read_info.read_field())
 	break;
@@ -717,14 +720,17 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
       pos=read_info.row_start;
       length=(uint) (read_info.row_end-pos);
 
+      real_item= item->real_item();
+
       if (!read_info.enclosed &&
 	  (enclosed_length && length == 4 &&
            !memcmp(pos, STRING_WITH_LEN("NULL"))) ||
 	  (length == 1 && read_info.found_null))
       {
-        if (item->type() == Item::FIELD_ITEM)
+
+        if (real_item->type() == Item::FIELD_ITEM)
         {
-          Field *field= ((Item_field *)item)->field;
+          Field *field= ((Item_field *)real_item)->field;
           if (field->reset())
           {
             my_error(ER_WARN_NULL_TO_NOTNULL, MYF(0), field->field_name,
@@ -741,25 +747,39 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
                                  ER_WARN_NULL_TO_NOTNULL, 1);
           }
 	}
-        else
+        else if (item->type() == Item::STRING_ITEM)
+        {
           ((Item_user_var_as_out_param *)item)->set_null_value(
                                                   read_info.read_charset);
+        }
+        else
+        {
+          my_error(ER_LOAD_DATA_INVALID_COLUMN, MYF(0), item->full_name());
+          DBUG_RETURN(1);
+        }
+
 	continue;
       }
 
-      if (item->type() == Item::FIELD_ITEM)
+      if (real_item->type() == Item::FIELD_ITEM)
       {
-
-        Field *field= ((Item_field *)item)->field;
+        Field *field= ((Item_field *)real_item)->field;
         field->set_notnull();
         read_info.row_end[0]=0;			// Safe to change end marker
         if (field == table->next_number_field)
           table->auto_increment_field_not_null= TRUE;
         field->store((char*) pos, length, read_info.read_charset);
       }
-      else
+      else if (item->type() == Item::STRING_ITEM)
+      {
         ((Item_user_var_as_out_param *)item)->set_value((char*) pos, length,
-                                                read_info.read_charset);
+                                                        read_info.read_charset);
+      }
+      else
+      {
+        my_error(ER_LOAD_DATA_INVALID_COLUMN, MYF(0), item->full_name());
+        DBUG_RETURN(1);
+      }
     }
     if (read_info.error)
       break;
@@ -775,9 +795,10 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
 	break;
       for (; item ; item= it++)
       {
-        if (item->type() == Item::FIELD_ITEM)
+        Item *real_item= item->real_item();
+        if (real_item->type() == Item::FIELD_ITEM)
         {
-          Field *field= ((Item_field *)item)->field;
+          Field *field= ((Item_field *)real_item)->field;
           if (field->reset())
           {
             my_error(ER_WARN_NULL_TO_NOTNULL, MYF(0),field->field_name,
@@ -797,9 +818,16 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
                               ER_WARN_TOO_FEW_RECORDS,
                               ER(ER_WARN_TOO_FEW_RECORDS), thd->row_count);
         }
-        else
+        else if (item->type() == Item::STRING_ITEM)
+        {
           ((Item_user_var_as_out_param *)item)->set_null_value(
                                                   read_info.read_charset);
+        }
+        else
+        {
+          my_error(ER_LOAD_DATA_INVALID_COLUMN, MYF(0), item->full_name());
+          DBUG_RETURN(1);
+        }
       }
     }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 518247c7a6b..164edacc932 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -586,37 +586,13 @@ JOIN::prepare(Item ***rref_pointer_array,
   /*
     Check if there are references to un-aggregated columns when computing 
     aggregate functions with implicit grouping (there is no GROUP BY).
-    TODO:  Add check of calculation of GROUP functions and fields:
-	   SELECT COUNT(*)+table.col1 from table1;
   */
-  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list &&
+      select_lex->full_group_by_flag == (NON_AGG_FIELD_USED | SUM_FUNC_USED))
   {
-    if (!group_list)
-    {
-      uint flag=0;
-      List_iterator_fast<Item> it(fields_list);
-      Item *item;
-      while ((item= it++))
-      {
-	if (item->with_sum_func)
-	  flag|=1;
-	else if (!(flag & 2) && !item->const_during_execution())
-	  flag|=2;
-      }
-      if (having)
-      {
-        if (having->with_sum_func)
-          flag |= 1;
-        else if (!having->const_during_execution())
-          flag |= 2;
-      }
-      if (flag == 3)
-      {
-	my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
-                   ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
-	DBUG_RETURN(-1);
-      }
-    }
+    my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+               ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+    DBUG_RETURN(-1);
   }
   {
     /* Caclulate the number of groups */
@@ -13159,6 +13135,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
           tab->read_first_record= best_key_direction > 0 ?
                                   join_read_first:join_read_last;
           tab->type=JT_NEXT;           // Read with index_first(), index_next()
+          if (select && select->quick)
+          {
+            delete select->quick;
+            select->quick= 0;
+          }
           if (table->covering_keys.is_set(best_key))
           {
             table->key_read=1;
@@ -13169,15 +13150,27 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
           {
             tab->ref.key= -1;
             tab->ref.key_parts= 0;
-            if (select && select->quick)
-            {
-              delete select->quick;
-              select->quick= 0;
-            }
             if (select_limit < table_records) 
               tab->limit= select_limit;
           }
         }
+        else if (tab->type != JT_ALL)
+        {
+          /*
+            We're about to use a quick access to the table.
+            We need to change the access method so as the quick access
+            method is actually used.
+          */
+          DBUG_ASSERT(tab->select->quick);
+          tab->type=JT_ALL;
+          tab->use_quick=1;
+          tab->ref.key= -1;
+          tab->ref.key_parts=0;		// Don't use ref key.
+          tab->read_first_record= join_init_read_record;
+          /*
+            TODO: update the number of records in join->best_positions[tablenr]
+          */
+        }
       }
       used_key_parts= best_key_parts;
       order_direction= best_key_direction;