Merge weblab.(none):/home/marcsql/TREE/mysql-5.0-base

into  weblab.(none):/home/marcsql/TREE/mysql-5.0-rt-merge


mysql-test/mysql-test-run.pl:
  Auto merged
mysql-test/t/disabled.def:
  Auto merged
server-tools/instance-manager/instance_options.cc:
  Auto merged
server-tools/instance-manager/mysqlmanager.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/sp_head.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

12 files changed, 459 insertions, 51 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e0d74cc7547..edae81b8e2d 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -793,6 +793,59 @@ int Arg_comparator::compare_e_row()
 }
 
 
+void Item_func_truth::fix_length_and_dec()
+{
+  maybe_null= 0;
+  null_value= 0;
+  decimals= 0;
+  max_length= 1;
+}
+
+
+void Item_func_truth::print(String *str)
+{
+  str->append('(');
+  args[0]->print(str);
+  str->append(STRING_WITH_LEN(" is "));
+  if (! affirmative)
+    str->append(STRING_WITH_LEN("not "));
+  if (value)
+    str->append(STRING_WITH_LEN("true"));
+  else
+    str->append(STRING_WITH_LEN("false"));
+  str->append(')');
+}
+
+
+bool Item_func_truth::val_bool()
+{
+  bool val= args[0]->val_bool();
+  if (args[0]->null_value)
+  {
+    /*
+      NULL val IS {TRUE, FALSE} --> FALSE
+      NULL val IS NOT {TRUE, FALSE} --> TRUE
+    */
+    return (! affirmative);
+  }
+
+  if (affirmative)
+  {
+    /* {TRUE, FALSE} val IS {TRUE, FALSE} value */
+    return (val == value);
+  }
+
+  /* {TRUE, FALSE} val IS NOT {TRUE, FALSE} value */
+  return (val != value);
+}
+
+
+longlong Item_func_truth::val_int()
+{
+  return (val_bool() ? 1 : 0);
+}
+
+
 bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
 {
   if (!args[0]->fixed && args[0]->fix_fields(thd, args) ||
@@ -1529,6 +1582,7 @@ Item_func_if::fix_length_and_dec()
 {
   maybe_null=args[1]->maybe_null || args[2]->maybe_null;
   decimals= max(args[1]->decimals, args[2]->decimals);
+  unsigned_flag=args[1]->unsigned_flag && args[2]->unsigned_flag;
 
   enum Item_result arg1_type=args[1]->result_type();
   enum Item_result arg2_type=args[2]->result_type();
@@ -1558,12 +1612,20 @@ Item_func_if::fix_length_and_dec()
       collation.set(&my_charset_bin);	// Number
     }
   }
-  max_length=
-    (cached_result_type == DECIMAL_RESULT || cached_result_type == INT_RESULT) ?
-    (max(args[1]->max_length - args[1]->decimals,
-         args[2]->max_length - args[2]->decimals) + decimals +
-         (unsigned_flag ? 0 : 1) ) :
-    max(args[1]->max_length, args[2]->max_length);
+
+  if ((cached_result_type == DECIMAL_RESULT )
+      || (cached_result_type == INT_RESULT))
+  {
+    int len1= args[1]->max_length - args[1]->decimals
+      - (args[1]->unsigned_flag ? 0 : 1);
+
+    int len2= args[2]->max_length - args[2]->decimals
+      - (args[2]->unsigned_flag ? 0 : 1);
+
+    max_length=max(len1, len2) + decimals + (unsigned_flag ? 0 : 1);
+  }
+  else
+    max_length= max(args[1]->max_length, args[2]->max_length);
 }
 
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 47f67d0392f..319c6c2a36f 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -101,6 +101,92 @@ public:
   uint decimal_precision() const { return 1; }
 };
 
+
+/**
+  Abstract Item class, to represent <code>X IS [NOT] (TRUE | FALSE)</code>
+  boolean predicates.
+*/
+
+class Item_func_truth : public Item_bool_func
+{
+public:
+  virtual bool val_bool();
+  virtual longlong val_int();
+  virtual void fix_length_and_dec();
+  virtual void print(String *str);
+
+protected:
+  Item_func_truth(Item *a, bool a_value, bool a_affirmative)
+  : Item_bool_func(a), value(a_value), affirmative(a_affirmative)
+  {}
+
+  ~Item_func_truth()
+  {}
+private:
+  /**
+    True for <code>X IS [NOT] TRUE</code>,
+    false for <code>X IS [NOT] FALSE</code> predicates.
+  */
+  const bool value;
+  /**
+    True for <code>X IS Y</code>, false for <code>X IS NOT Y</code> predicates.
+  */
+  const bool affirmative;
+};
+
+
+/**
+  This Item represents a <code>X IS TRUE</code> boolean predicate.
+*/
+
+class Item_func_istrue : public Item_func_truth
+{
+public:
+  Item_func_istrue(Item *a) : Item_func_truth(a, true, true) {}
+  ~Item_func_istrue() {}
+  virtual const char* func_name() const { return "istrue"; }
+};
+
+
+/**
+  This Item represents a <code>X IS NOT TRUE</code> boolean predicate.
+*/
+
+class Item_func_isnottrue : public Item_func_truth
+{
+public:
+  Item_func_isnottrue(Item *a) : Item_func_truth(a, true, false) {}
+  ~Item_func_isnottrue() {}
+  virtual const char* func_name() const { return "isnottrue"; }
+};
+
+
+/**
+  This Item represents a <code>X IS FALSE</code> boolean predicate.
+*/
+
+class Item_func_isfalse : public Item_func_truth
+{
+public:
+  Item_func_isfalse(Item *a) : Item_func_truth(a, false, true) {}
+  ~Item_func_isfalse() {}
+  virtual const char* func_name() const { return "isfalse"; }
+};
+
+
+/**
+  This Item represents a <code>X IS NOT FALSE</code> boolean predicate.
+*/
+
+class Item_func_isnotfalse : public Item_func_truth
+{
+public:
+  Item_func_isnotfalse(Item *a) : Item_func_truth(a, false, false) {}
+  ~Item_func_isnotfalse() {}
+  virtual const char* func_name() const { return "isnotfalse"; }
+};
+
+
 class Item_cache;
 #define UNKNOWN ((my_bool)-1)
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 20d9ed8affa..12ae0c026eb 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -51,6 +51,10 @@ Item_subselect::Item_subselect():
 void Item_subselect::init(st_select_lex *select_lex,
 			  select_subselect *result)
 {
+  /*
+    Please see Item_singlerow_subselect::invalidate_and_restore_select_lex(),
+    which depends on alterations to the parse tree implemented here.
+  */
 
   DBUG_ENTER("Item_subselect::init");
   DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
@@ -91,6 +95,12 @@ void Item_subselect::init(st_select_lex *select_lex,
   DBUG_VOID_RETURN;
 }
 
+st_select_lex *
+Item_subselect::get_select_lex()
+{
+  return unit->first_select();
+}
+
 void Item_subselect::cleanup()
 {
   DBUG_ENTER("Item_subselect::cleanup");
@@ -268,6 +278,26 @@ Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
   DBUG_VOID_RETURN;
 }
 
+st_select_lex *
+Item_singlerow_subselect::invalidate_and_restore_select_lex()
+{
+  DBUG_ENTER("Item_singlerow_subselect::invalidate_and_restore_select_lex");
+  st_select_lex *result= get_select_lex();
+
+  DBUG_ASSERT(result);
+
+  /*
+    This code restore the parse tree in it's state before the execution of
+    Item_singlerow_subselect::Item_singlerow_subselect(),
+    and in particular decouples this object from the SELECT_LEX,
+    so that the SELECT_LEX can be used with a different flavor
+    or Item_subselect instead, as part of query rewriting.
+  */
+  unit->item= NULL;
+
+  DBUG_RETURN(result);
+}
+
 Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param,
                                              Item_subselect *parent,
 					     st_select_lex *select_lex,
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 485a896c1c7..6b605e96432 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -126,6 +126,12 @@ public:
   virtual void reset_value_registration() {}
   enum_parsing_place place() { return parsing_place; }
 
+  /**
+    Get the SELECT_LEX structure associated with this Item.
+    @return the SELECT_LEX structure associated with this Item
+  */
+  st_select_lex* get_select_lex();
+
   friend class select_subselect;
   friend class Item_in_optimizer;
   friend bool Item_field::fix_fields(THD *, Item **);
@@ -169,6 +175,20 @@ public:
   bool null_inside();
   void bring_value();
 
+  /**
+    This method is used to implement a special case of semantic tree
+    rewriting, mandated by a SQL:2003 exception in the specification.
+    The only caller of this method is handle_sql2003_note184_exception(),
+    see the code there for more details.
+    Note that this method breaks the object internal integrity, by
+    removing it's association with the corresponding SELECT_LEX,
+    making this object orphan from the parse tree.
+    No other method, beside the destructor, should be called on this
+    object, as it is now invalid.
+    @return the SELECT_LEX structure that was given in the constructor.
+  */
+  st_select_lex* invalidate_and_restore_select_lex();
+
   friend class select_singlerow_subselect;
 };
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 24bdd2db5b6..71460a67934 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -36,6 +36,7 @@ Item_result
 sp_map_result_type(enum enum_field_types type)
 {
   switch (type) {
+  case MYSQL_TYPE_BIT:
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG:
@@ -58,6 +59,7 @@ Item::Type
 sp_map_item_type(enum enum_field_types type)
 {
   switch (type) {
+  case MYSQL_TYPE_BIT:
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG:
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 630d135be48..26133c6b66e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1210,6 +1210,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   int4store(key + key_length, thd->server_id);
   int4store(key + key_length + 4, thd->variables.pseudo_thread_id);
 
+  /*
+    Unless requested otherwise, try to resolve this table in the list
+    of temporary tables of this thread. In MySQL temporary tables
+    are always thread-local and "shadow" possible base tables with the
+    same name. This block implements the behaviour.
+    TODO: move this block into a separate function.
+  */
   if (!table_list->skip_temporary)
   {
     for (table= thd->temporary_tables; table ; table=table->next)
@@ -1218,6 +1225,12 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
 	  !memcmp(table->s->table_cache_key, key,
 		  key_length + TMP_TABLE_KEY_EXTRA))
       {
+        /*
+          We're trying to use the same temporary table twice in a query.
+          Right now we don't support this because a temporary table
+          is always represented by only one TABLE object in THD, and
+          it can not be cloned. Emit an error for an unsupported behaviour.
+        */
 	if (table->query_id == thd->query_id ||
             thd->prelocked_mode && table->query_id)
 	{
@@ -1233,6 +1246,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     }
   }
 
+  /*
+    The table is not temporary - if we're in pre-locked or LOCK TABLES
+    mode, let's try to find the requested table in the list of pre-opened
+    and locked tables. If the table is not there, return an error - we can't
+    open not pre-opened tables in pre-locked/LOCK TABLES mode.
+    TODO: move this block into a separate function.
+  */
   if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) &&
       (thd->locked_tables || thd->prelocked_mode))
   {						// Using table locks
@@ -1304,7 +1324,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
       goto reset;
     }
     /*
-      is it view?
+      Is this table a view and not a base table?
       (it is work around to allow to open view with locked tables,
       real fix will be made after definition cache will be made)
     */
@@ -1338,8 +1358,32 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     DBUG_RETURN(0);
   }
 
+  /*
+    Non pre-locked/LOCK TABLES mode, and the table is not temporary:
+    this is the normal use case.
+    Now we should:
+    - try to find the table in the table cache.
+    - if one of the discovered TABLE instances is name-locked
+      (table->s->version == 0) or some thread has started FLUSH TABLES
+      (refresh_version > table->s->version), back off -- we have to wait
+      until no one holds a name lock on the table.
+    - if there is no such TABLE in the name cache, read the table definition
+    and insert it into the cache.
+    We perform all of the above under LOCK_open which currently protects
+    the open cache (also known as table cache) and table definitions stored
+    on disk.
+  */
+
   VOID(pthread_mutex_lock(&LOCK_open));
 
+  /*
+    If it's the first table from a list of tables used in a query,
+    remember refresh_version (the version of open_cache state).
+    If the version changes while we're opening the remaining tables,
+    we will have to back off, close all the tables opened-so-far,
+    and try to reopen them.
+    Note: refresh_version is currently changed only during FLUSH TABLES.
+  */
   if (!thd->open_tables)
     thd->version=refresh_version;
   else if ((thd->version != refresh_version) &&
@@ -1356,12 +1400,39 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   if (thd->handler_tables)
     mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE, TRUE);
 
+  /*
+    Actually try to find the table in the open_cache.
+    The cache may contain several "TABLE" instances for the same
+    physical table. The instances that are currently "in use" by
+    some thread have their "in_use" member != NULL.
+    There is no good reason for having more than one entry in the
+    hash for the same physical table, except that we use this as
+    an implicit "pending locks queue" - see
+    wait_for_locked_table_names for details.
+  */
   for (table= (TABLE*) hash_first(&open_cache, (byte*) key, key_length,
                                   &state);
        table && table->in_use ;
        table= (TABLE*) hash_next(&open_cache, (byte*) key, key_length,
                                  &state))
   {
+    /*
+      Normally, table->s->version contains the value of
+      refresh_version from the moment when this table was
+      (re-)opened and added to the cache.
+      If since then we did (or just started) FLUSH TABLES
+      statement, refresh_version has been increased.
+      For "name-locked" TABLE instances, table->s->version is set
+      to 0 (see lock_table_name for details).
+      In case there is a pending FLUSH TABLES or a name lock, we
+      need to back off and re-start opening tables.
+      If we do not back off now, we may dead lock in case of lock
+      order mismatch with some other thread:
+      c1: name lock t1; -- sort of exclusive lock 
+      c2: open t2;      -- sort of shared lock
+      c1: name lock t2; -- blocks
+      c2: open t1; -- blocks
+    */
     if (table->s->version != refresh_version)
     {
       DBUG_PRINT("note",
@@ -1375,16 +1446,35 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
       }
 
       /*
-        There is a refresh in progress for this table
-        Wait until the table is freed or the thread is killed.
+        Back off, part 1: mark the table as "unused" for the
+        purpose of name-locking by setting table->db_stat to 0. Do
+        that only for the tables in this thread that have an old
+        table->s->version (this is an optimization (?)).
+        table->db_stat == 0 signals wait_for_locked_table_names
+        that the tables in question are not used any more. See
+        table_is_used call for details.
       */
       close_old_data_files(thd,thd->open_tables,0,0);
+      /*
+        Back-off part 2: try to avoid "busy waiting" on the table:
+        if the table is in use by some other thread, we suspend
+        and wait till the operation is complete: when any
+        operation that juggles with table->s->version completes,
+        it broadcasts COND_refresh condition variable.
+      */
       if (table->in_use != thd)
+      {
 	wait_for_refresh(thd);
+        /* wait_for_refresh will unlock LOCK_open for us */
+      }
       else
       {
 	VOID(pthread_mutex_unlock(&LOCK_open));
       }
+      /*
+        There is a refresh in progress for this table.
+        Signal the caller that it has to try again.
+      */
       if (refresh)
 	*refresh=1;
       DBUG_RETURN(0);
@@ -1392,6 +1482,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   }
   if (table)
   {
+    /* Unlink the table from "unused_tables" list. */
     if (table == unused_tables)
     {						// First unused
       unused_tables=unused_tables->next;	// Remove from link
@@ -1404,6 +1495,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   }
   else
   {
+    /* Insert a new TABLE instance into the open cache */
     TABLE_SHARE *share;
     int error;
     /* Free cache if too big */
@@ -2145,6 +2237,10 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
     }
   }
 
+  /*
+    For every table in the list of tables to open, try to find or open
+    a table.
+  */
   for (tables= *start; tables ;tables= tables->next_global)
   {
     /*
@@ -2159,6 +2255,12 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
         goto process_view_routines;
       continue;
     }
+    /*
+      If this TABLE_LIST object is a placeholder for an information_schema
+      table, create a temporary table to represent the information_schema
+      table in the query. Do not fill it yet - will be filled during
+      execution.
+    */
     if (tables->schema_table)
     {
       if (!mysql_schema_table(thd, thd->lex, tables))
@@ -2166,7 +2268,11 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
       DBUG_RETURN(-1);
     }
     (*counter)++;
-    
+
+    /*
+      Not a placeholder: must be a base table or a view, and the table is
+      not opened yet. Try to open the table.
+    */
     if (!tables->table &&
 	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags)))
     {
@@ -2273,7 +2379,7 @@ process_view_routines:
       {
         /*
           Serious error during reading stored routines from mysql.proc table.
-          Something's wrong with the table or its contents, and an error has
+          Something is wrong with the table or its contents, and an error has
           been emitted; we must abort.
         */
         result= -1;
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 8e6846fdcd2..d06ac7824fd 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -1765,8 +1765,18 @@ void Query_cache::free_cache()
 {
   DBUG_ENTER("Query_cache::free_cache");
   if (query_cache_size > 0)
-  {
     flush_cache();
+  /*
+    There may be two free_cache() calls in progress, because we
+    release 'structure_guard_mutex' in flush_cache().  When the second
+    flush_cache() wakes up from the wait on 'COND_flush_finished', the
+    first call to free_cache() has done its job.  So we have to test
+    'query_cache_size > 0' the second time to see if the cache wasn't
+    reset by other thread, or if it was reset and was re-enabled then.
+    If the cache was reset, then we have nothing to do here.
+  */
+  if (query_cache_size > 0)
+  {
 #ifndef DBUG_OFF
     if (bins[0].free_blocks == 0)
     {
@@ -1808,6 +1818,12 @@ void Query_cache::free_cache()
     flush_in_progress flag and releases the lock, so other threads may
     proceed skipping the cache as if it is disabled.  Concurrent
     flushes are performed in turn.
+
+    After flush_cache() call, the cache is flushed, all the freed
+    memory is accumulated in bin[0], and the 'structure_guard_mutex'
+    is locked.  However, since we could release the mutex during
+    execution, the rest of the cache state could have been changed,
+    and should not be relied on.
 */
 
 void Query_cache::flush_cache()
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index ab9bda44f26..cef9257c235 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -319,6 +319,7 @@ void THD::init(void)
 
 void THD::init_for_queries()
 {
+  set_time(); 
   ha_enable_transaction(this,TRUE);
 
   reset_root_defaults(mem_root, variables.query_alloc_block_size,
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index cd4d2e85316..9a9731459b3 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -99,6 +99,16 @@ void lex_free(void)
 }
 
 
+void
+st_parsing_options::reset()
+{
+  allows_variable= TRUE;
+  allows_select_into= TRUE;
+  allows_select_procedure= TRUE;
+  allows_derived= TRUE;
+}
+
+
 /*
   This is called before every query that is to be parsed.
   Because of this, it's critical to not do too much things here.
@@ -149,6 +159,7 @@ void lex_start(THD *thd, uchar *buf,uint length)
   lex->safe_to_cache_query= 1;
   lex->time_zone_tables_used= 0;
   lex->leaf_tables_insert= 0;
+  lex->parsing_options.reset();
   lex->empty_field_list_on_rset= 0;
   lex->select_lex.select_number= 1;
   lex->next_state=MY_LEX_START;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 86d01675c2a..b963ada2004 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -891,10 +891,8 @@ struct st_parsing_options
   bool allows_select_procedure;
   bool allows_derived;
 
-  st_parsing_options()
-    : allows_variable(TRUE), allows_select_into(TRUE),
-      allows_select_procedure(TRUE), allows_derived(TRUE)
-  {}
+  st_parsing_options() { reset(); }
+  void reset();
 };
 
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 4c250092ba6..7f5fb416a94 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1162,7 +1162,6 @@ pthread_handler_t handle_one_connection(void *arg)
     thd->version= refresh_version;
     thd->proc_info= 0;
     thd->command= COM_SLEEP;
-    thd->set_time();
     thd->init_for_queries();
 
     if (sys_init_connect.value_length && !(sctx->master_access & SUPER_ACL))
@@ -1178,7 +1177,6 @@ pthread_handler_t handle_one_connection(void *arg)
         sql_print_warning("%s", net->last_error);
       }
       thd->proc_info=0;
-      thd->set_time();
       thd->init_for_queries();
     }
 
@@ -1312,6 +1310,7 @@ pthread_handler_t handle_bootstrap(void *arg)
       mode we have only one thread.
     */
     thd->query_id=next_query_id();
+    thd->set_time();
     mysql_parse(thd,thd->query,length);
     close_thread_tables(thd);			// Free tables
     if (thd->is_fatal_error)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 473a300feeb..a9921581f0c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -59,15 +59,6 @@ const LEX_STRING null_lex_str={0,0};
     YYABORT;				\
   }
 
-/* Helper for parsing "IS [NOT] truth_value" */
-inline Item *is_truth_value(Item *A, bool v1, bool v2)
-{
-  return new Item_func_if(create_func_ifnull(A,
-	new Item_int((char *) (v2 ? "TRUE" : "FALSE"), v2, 1)),
-	new Item_int((char *) (v1 ? "TRUE" : "FALSE"), v1, 1),
-	new Item_int((char *) (v1 ? "FALSE" : "TRUE"),!v1, 1));
-}
-
 #ifndef DBUG_OFF
 #define YYDEBUG 1
 #else
@@ -277,6 +268,81 @@ void case_stmt_action_end_case(LEX *lex, bool simple)
   lex->sphead->do_cont_backpatch();
 }
 
+/**
+  Helper to resolve the SQL:2003 Syntax exception 1) in <in predicate>.
+  See SQL:2003, Part 2, section 8.4 <in predicate>, Note 184, page 383.
+  This function returns the proper item for the SQL expression
+  <code>left [NOT] IN ( expr )</code>
+  @param thd the current thread
+  @param left the in predicand
+  @param equal true for IN predicates, false for NOT IN predicates
+  @param expr first and only expression of the in value list
+  @return an expression representing the IN predicate.
+*/
+Item* handle_sql2003_note184_exception(THD *thd, Item* left, bool equal,
+                                       Item *expr)
+{
+  /*
+    Relevant references for this issue:
+    - SQL:2003, Part 2, section 8.4 <in predicate>, page 383,
+    - SQL:2003, Part 2, section 7.2 <row value expression>, page 296,
+    - SQL:2003, Part 2, section 6.3 <value expression primary>, page 174,
+    - SQL:2003, Part 2, section 7.15 <subquery>, page 370,
+    - SQL:2003 Feature F561, "Full value expressions".
+
+    The exception in SQL:2003 Note 184 means:
+    Item_singlerow_subselect, which corresponds to a <scalar subquery>,
+    should be re-interpreted as an Item_in_subselect, which corresponds
+    to a <table subquery> when used inside an <in predicate>.
+
+    Our reading of Note 184 is reccursive, so that all:
+    - IN (( <subquery> ))
+    - IN ((( <subquery> )))
+    - IN '('^N <subquery> ')'^N
+    - etc
+    should be interpreted as a <table subquery>, no matter how deep in the
+    expression the <subquery> is.
+  */
+
+  Item *result;
+
+  DBUG_ENTER("handle_sql2003_note184_exception");
+
+  if (expr->type() == Item::SUBSELECT_ITEM)
+  {
+    Item_subselect *expr2 = (Item_subselect*) expr;
+
+    if (expr2->substype() == Item_subselect::SINGLEROW_SUBS)
+    {
+      Item_singlerow_subselect *expr3 = (Item_singlerow_subselect*) expr2;
+      st_select_lex *subselect;
+
+      /*
+        Implement the mandated change, by altering the semantic tree:
+          left IN Item_singlerow_subselect(subselect)
+        is modified to
+          left IN (subselect)
+        which is represented as
+          Item_in_subselect(left, subselect)
+      */
+      subselect= expr3->invalidate_and_restore_select_lex();
+      result= new (thd->mem_root) Item_in_subselect(left, subselect);
+
+      if (! equal)
+        result = negate_expression(thd, result);
+
+      DBUG_RETURN(result);
+    }
+  }
+
+  if (equal)
+    result= new (thd->mem_root) Item_func_eq(left, expr);
+  else
+    result= new (thd->mem_root) Item_func_ne(left, expr);
+
+  DBUG_RETURN(result);
+}
+
 %}
 %union {
   int  num;
@@ -4393,13 +4459,18 @@ bool_factor:
 	| bool_test ;
 
 bool_test:
-	bool_pri IS TRUE_SYM	{ $$= is_truth_value($1,1,0); }
-	| bool_pri IS not TRUE_SYM { $$= is_truth_value($1,0,0); }
-	| bool_pri IS FALSE_SYM	{ $$= is_truth_value($1,0,1); }
-	| bool_pri IS not FALSE_SYM { $$= is_truth_value($1,1,1); }
-	| bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); }
-	| bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); }
-	| bool_pri ;
+          bool_pri IS TRUE_SYM
+          { $$= new (YYTHD->mem_root) Item_func_istrue($1); }
+        | bool_pri IS not TRUE_SYM
+          { $$= new (YYTHD->mem_root) Item_func_isnottrue($1); }
+        | bool_pri IS FALSE_SYM
+          { $$= new (YYTHD->mem_root) Item_func_isfalse($1); }
+        | bool_pri IS not FALSE_SYM
+          { $$= new (YYTHD->mem_root) Item_func_isnotfalse($1); }
+        | bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); }
+        | bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); }
+        | bool_pri
+        ;
 
 bool_pri:
 	bool_pri IS NULL_SYM	{ $$= new Item_func_isnull($1); }
@@ -4412,31 +4483,37 @@ bool_pri:
 	| predicate ;
 
 predicate:
-        bit_expr IN_SYM '(' subselect ')'
-	  { $$= new Item_in_subselect($1, $4); }
-	| bit_expr not IN_SYM '(' subselect ')'
-          { $$= negate_expression(YYTHD, new Item_in_subselect($1, $5)); }
+          bit_expr IN_SYM '(' subselect ')'
+          {
+            $$= new (YYTHD->mem_root) Item_in_subselect($1, $4);
+          }
+        | bit_expr not IN_SYM '(' subselect ')'
+          {
+            THD *thd= YYTHD;
+            Item *item= new (thd->mem_root) Item_in_subselect($1, $5);
+            $$= negate_expression(thd, item);
+          }
         | bit_expr IN_SYM '(' expr ')'
           {
-              $$= new Item_func_eq($1, $4);
+            $$= handle_sql2003_note184_exception(YYTHD, $1, true, $4);
           }
-	| bit_expr IN_SYM '(' expr ',' expr_list ')'
-	  { 
-              $6->push_front($4);
-              $6->push_front($1);
-              $$= new Item_func_in(*$6);
+        | bit_expr IN_SYM '(' expr ',' expr_list ')'
+          { 
+            $6->push_front($4);
+            $6->push_front($1);
+            $$= new (YYTHD->mem_root) Item_func_in(*$6);
           }
         | bit_expr not IN_SYM '(' expr ')'
           {
-              $$= new Item_func_ne($1, $5);
+            $$= handle_sql2003_note184_exception(YYTHD, $1, false, $5);
           }
-	| bit_expr not IN_SYM '(' expr ',' expr_list ')'
+        | bit_expr not IN_SYM '(' expr ',' expr_list ')'
           {
-              $7->push_front($5);
-              $7->push_front($1);
-              Item_func_in *item = new Item_func_in(*$7);
-              item->negate();
-              $$= item;
+            $7->push_front($5);
+            $7->push_front($1);
+            Item_func_in *item = new (YYTHD->mem_root) Item_func_in(*$7);
+            item->negate();
+            $$= item;
           }
 	| bit_expr BETWEEN_SYM bit_expr AND_SYM predicate
 	  { $$= new Item_func_between($1,$3,$5); }