merging

sql/item.cc:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged

13 files changed, 804 insertions, 287 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f12e0396694..6411bf2a3bc 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -52,6 +52,54 @@ a
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
 a
 1
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+(SELECT 1,2,3) = ROW(1,2,3)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+(SELECT 1,2,3) = ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+(SELECT 1,2,3) < ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+(SELECT 1,2,3) > ROW(1,2,1)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+(SELECT 1,2,3) = ROW(1,2,NULL)
+NULL
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+ROW(1,2,3) = (SELECT 1,2,3)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+ROW(1,2,3) = (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+ROW(1,2,3) < (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+ROW(1,2,3) > (SELECT 1,2,1)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+ROW(1,2,3) = (SELECT 1,2,NULL)
+NULL
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
+1
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
+0
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+(SELECT 1.5,2,'a') = ROW('b',2,'b')
+0
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+(SELECT 'b',2,'a') = ROW(1.5,2,'a')
+0
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
+0
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
+(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
+0
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
 create table t2 (a int, b int);
@@ -602,7 +650,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
 INSERT INTO t1 values (1),(1);
 UPDATE t SET id=(SELECT * FROM t1);
 Subselect returns more than 1 record
-drop table t;
+drop table t, t1;
 create table t (a int);
 insert into t values (1),(2),(3);
 select 1 IN (SELECT * from t);
@@ -711,3 +759,54 @@ This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a')	(select c from t1 where a=t2.a)
+1	1	a
+2	0	b
+NULL	NULL	NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b')	(select c from t1 where a=t2.a)
+1	0	a
+2	1	b
+NULL	NULL	NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c')	(select c from t1 where a=t2.a)
+1	0	a
+2	0	b
+NULL	NULL	NULL
+drop table t1,t2;
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+ROW(1, 1, 'a') IN (select a,b,c from t)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+ROW(1, 2, 'a') IN (select a,b,c from t)
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+ROW(1, 1, 'a') IN (select b,a,c from t)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 1, 'a') IN (select a,b,c from t where a is not null)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 2, 'a') IN (select a,b,c from t where a is not null)
+0
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+ROW(1, 1, 'a') IN (select b,a,c from t where a is not null)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a')
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a')
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a')
+1
+drop table if exists t;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 347d6276280..cd4baacdacb 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -26,6 +26,22 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
@@ -363,7 +379,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
 INSERT INTO t1 values (1),(1);
 -- error 1240
 UPDATE t SET id=(SELECT * FROM t1);
-drop table t;
+drop table t, t1;
 
 
 #NULL test
@@ -416,4 +432,26 @@ create table t (a float);
 select 10.5 IN (SELECT * from t LIMIT 1);
 -- error 1235
 select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
-drop table t;
\ No newline at end of file
+drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+drop table t1,t2;
+
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+drop table if exists t;
diff --git a/sql/item.cc b/sql/item.cc
index feb318f829d..461f2ba9de5 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -47,11 +47,6 @@ Item::Item():
   loop_id= 0;
 }
 
-Item_ref_in_optimizer::Item_ref_in_optimizer(Item_in_optimizer *master,
-					     char *table_name_par,
-					     char *field_name_par):
-  Item_ref(master->args, table_name_par, field_name_par), owner(master) {}
-
 
 bool Item::check_loop(uint id)
 {
@@ -437,20 +432,6 @@ String *Item_copy_string::val_str(String *str)
   return &str_value;
 }
 
-double Item_ref_in_optimizer::val()
-{
-  return owner->get_cache();
-}
-longlong Item_ref_in_optimizer::val_int()
-{
-  return owner->get_cache_int();
-}
-String* Item_ref_in_optimizer::val_str(String* s)
-{
-  return owner->get_cache_str(s);
-}
-
-
 /*
   Functions to convert item to field (for send_fields)
 */
@@ -464,18 +445,6 @@ bool Item::fix_fields(THD *thd,
   return 0;
 }
 
-bool Item_outer_select_context_saver::fix_fields(THD *thd,
-						 struct st_table_list *list,
-						 Item ** ref)
-{
-  DBUG_ENTER("Item_outer_select_context_saver::fix_fields");
-  bool res= item->fix_fields(thd,
-			     0, // do not show current subselect fields
-			     &item);
-  *ref= item;
-  DBUG_RETURN(res);
-}
-
 bool Item_asterisk_remover::fix_fields(THD *thd,
 				       struct st_table_list *list,
 				       Item ** ref)
@@ -529,6 +498,27 @@ bool Item_asterisk_remover::fix_fields(THD *thd,
   DBUG_RETURN(res);
 }
 
+bool Item_ref_on_list_position::fix_fields(THD *thd,
+					   struct st_table_list *tables,
+					   Item ** reference)
+{
+  ref= 0;
+  List_iterator<Item> li(list);
+  Item *item;
+  uint i= 0;
+  for (; (item= li++) && i < pos; i++);
+  if (i == pos)
+  {
+    ref= li.ref();
+    return Item_ref_null_helper::fix_fields(thd, tables, reference);
+  }
+  else
+  {
+    my_error(ER_CARDINALITY_COL, MYF(0), pos);
+    return 1;
+  }
+}
+
 double Item_ref_null_helper::val()
 {
   double tmp= (*ref)->val_result();
@@ -1221,6 +1211,148 @@ bool field_is_equal_to_item(Field *field,Item *item)
   return result == field->val_real();
 }
 
+Item_cache* Item_cache::get_cache(Item_result type)
+{
+  switch (type)
+  {
+  case INT_RESULT:
+    return new Item_cache_int();
+  case REAL_RESULT:
+    return new Item_cache_real();
+  case STRING_RESULT:
+    return new Item_cache_str();
+  case ROW_RESULT:
+    return new Item_cache_row();
+  default:
+    // should never be in real life
+    DBUG_ASSERT(0);
+    return 0;
+  }
+}
+
+void Item_cache_str::store(Item *item)
+{
+  str_value.set(buffer, sizeof(buffer), item->charset());
+  value= item->str_result(&str_value);
+  if ((null_value= item->null_value))
+    value= 0;
+  else if (value != &str_value)
+  {
+    /*
+      We copy string value to avoid changing value if 'item' is table field
+      in queries like following (where t1.c is varchar):
+      select a, 
+             (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),
+             (select c from t1 where a=t2.a)
+        from t2;
+    */
+    str_value.copy(*value);
+    value= &str_value;
+  }
+
+}
+double Item_cache_str::val()
+{ 
+  if (value)
+    return my_strntod(value->charset(), value->ptr(),
+		      value->length(), (char**)0);
+  else
+    return (double)0;
+}
+longlong Item_cache_str::val_int()
+{
+  if (value)
+    return my_strntoll(value->charset(), value->ptr(),
+		       value->length(), (char**) 0, 10);
+  else
+    return (longlong)0;
+}
+
+bool Item_cache_row::allocate(uint num)
+{
+  n= num;
+  THD *thd= current_thd;
+  if (!(values= (Item_cache **) thd->calloc(sizeof(Item_cache *)*n)))
+  {
+    my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+    thd->fatal_error= 1;
+    return 1;
+  }
+  return 0;
+}
+
+bool Item_cache_row::setup(Item * item)
+{
+  if (!values && allocate(item->cols()))
+    return 1;
+  for(uint i= 0; i < n; i++)
+  {
+    if (!(values[i]= Item_cache::get_cache(item->el(i)->result_type())))
+    {
+      my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+      current_thd->fatal_error= 1;
+      return 1;
+    }
+    values[i]->setup(item->el(i));
+  }
+  return 0;
+}
+
+void Item_cache_row::store(Item * item)
+{
+  null_value= 0;
+  item->bring_value();
+  for(uint i= 0; i < n; i++)
+  {
+    values[i]->store(item->el(i));
+    null_value|= values[i]->null_value;
+  }
+}
+
+void Item_cache_row::illegal_method_call(const char *method)
+{
+  DBUG_ENTER("Item_cache_row::illegal_method_call");
+  DBUG_PRINT("error", ("!!! %s method was called for row item", method));
+  DBUG_ASSERT(0);
+  my_error(ER_CARDINALITY_COL, MYF(0), 1);
+  DBUG_VOID_RETURN;
+}
+
+bool Item_cache_row::check_cols(uint c)
+{
+  if (c != n)
+  {
+    my_error(ER_CARDINALITY_COL, MYF(0), c);
+    return 1;
+  }
+  return 0;
+}
+
+bool Item_cache_row::null_inside()
+{
+  for (uint i= 0; i < n; i++)
+  {
+    if (values[i]->cols() > 1)
+    {
+      if (values[i]->null_inside())
+	return 1;
+    }
+    else
+    {
+      values[i]->val_int();
+      if (values[i]->null_value)
+	return 1;
+    }
+  }
+  return 0;
+}
+
+void Item_cache_row::bring_value()
+{
+  for (uint i= 0; i < n; i++)
+    values[i]->bring_value();
+  return;
+}
 
 /*****************************************************************************
 ** Instantiate templates
diff --git a/sql/item.h b/sql/item.h
index 1ea76731fd3..4e3c5f597ab 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -31,12 +31,12 @@ public:
   static void *operator new(size_t size) {return (void*) sql_alloc((uint) size); }
   static void operator delete(void *ptr,size_t size) {} /*lint -e715 */
 
-  enum Type {FIELD_ITEM,FUNC_ITEM,SUM_FUNC_ITEM,STRING_ITEM,
-	     INT_ITEM,REAL_ITEM,NULL_ITEM,VARBIN_ITEM,
-	     COPY_STR_ITEM,FIELD_AVG_ITEM, DEFAULT_ITEM,
-	     PROC_ITEM,COND_ITEM,REF_ITEM,FIELD_STD_ITEM, 
-	     FIELD_VARIANCE_ITEM,CONST_ITEM,
-             SUBSELECT_ITEM, ROW_ITEM};
+  enum Type {FIELD_ITEM, FUNC_ITEM, SUM_FUNC_ITEM, STRING_ITEM,
+	     INT_ITEM, REAL_ITEM, NULL_ITEM, VARBIN_ITEM,
+	     COPY_STR_ITEM, FIELD_AVG_ITEM, DEFAULT_ITEM,
+	     PROC_ITEM,COND_ITEM, REF_ITEM, FIELD_STD_ITEM, 
+	     FIELD_VARIANCE_ITEM, CONST_ITEM,
+             SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM};
   enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE };
 
   String str_value;			/* used to store value */
@@ -103,64 +103,12 @@ public:
   virtual Item** addr(uint i) { return 0; }
   virtual bool check_cols(uint c);
   // It is not row => null inside is impossible
-  virtual bool null_inside() { return 0; };
+  virtual bool null_inside() { return 0; }
+  // used in row subselects to get value of elements
+  virtual void bring_value() {}
 };
 
 
-/*
-  Wrapper base class
-*/
-
-class Item_wrapper :public Item
-{
-protected:
-  Item *item;
-public:
-  /* 
-     Following methods should not be used, because fix_fields exclude this 
-     item (it assign '*ref' with field 'item' in derived classes)
-  */
-  enum Type type() const         { return item->type(); }
-  enum_field_types field_type() const { return item->field_type(); }
-  double val()                   { return item->val(); }
-  longlong val_int()             { return item->val_int(); }
-  String* val_str(String* s)     { return item->val_str(s); }
-  bool check_cols(uint col)      { return item->check_cols(col); }
-  bool eq(const Item *item, bool binary_cmp) const
-  { return item->eq(item, binary_cmp); }
-  bool is_null() 
-  {
-    item->val_int();
-    return item->null_value;
-  }
-  bool get_date(TIME *ltime, bool fuzzydate)
-  {  
-    return (null_value=item->get_date(ltime, fuzzydate));
-  }
-  bool send(Protocol *prot, String *tmp) { return item->send(prot, tmp); }
-  int  save_in_field(Field *field, bool no_conversions)
-  {
-    return item->save_in_field(field, no_conversions);
-  }
-  void save_org_in_field(Field *field)	{ item->save_org_in_field(field); }
-  enum Item_result result_type () const { return item->result_type(); }
-  table_map used_tables() const		{ return item->used_tables(); }  
-};
-
-
-/*
-  Save context of name resolution for Item, used in subselect transformer.
-*/
-class Item_outer_select_context_saver :public Item_wrapper
-{
-public:
-  Item_outer_select_context_saver(Item *it)
-  {
-    item= it;
-  }
-  bool fix_fields(THD *, struct st_table_list *, Item ** ref);
-};
-
 class st_select_lex;
 class Item_ident :public Item
 {
@@ -381,7 +329,8 @@ public:
     name=(char*) str_value.ptr();
     decimals=NOT_FIXED_DEC;
   }
-  Item_string(const char *name_par,const char *str,uint length,CHARSET_INFO *cs)
+  Item_string(const char *name_par, const char *str, uint length,
+	      CHARSET_INFO *cs)
   {
     str_value.set(str,length,cs);
     max_length=length;
@@ -392,11 +341,13 @@ public:
   enum Type type() const { return STRING_ITEM; }
   double val()
   { 
-    return my_strntod(str_value.charset(),str_value.ptr(),str_value.length(),(char**)NULL);
+    return my_strntod(str_value.charset(), str_value.ptr(),
+		      str_value.length(), (char**) 0);
   }
   longlong val_int()
   {
-    return my_strntoll(str_value.charset(),str_value.ptr(),str_value.length(),(char**) 0,10);
+    return my_strntoll(str_value.charset(), str_value.ptr(),
+		       str_value.length(), (char**) 0, 10);
   }
   String *val_str(String*) { return (String*) &str_value; }
   int save_in_field(Field *field, bool no_conversions);
@@ -550,7 +501,7 @@ protected:
   Item_in_subselect* owner;
 public:
   Item_ref_null_helper(Item_in_subselect* master, Item **item,
-		       char *table_name_par,char *field_name_par):
+		       char *table_name_par, char *field_name_par):
     Item_ref(item, table_name_par, field_name_par), owner(master) {}
   double val();
   longlong val_int();
@@ -558,6 +509,24 @@ public:
   bool get_date(TIME *ltime, bool fuzzydate);
 };
 
+
+/*
+  Used to find item in list of select items after '*' items processing.
+*/
+class Item_ref_on_list_position: public Item_ref_null_helper
+{
+protected:
+  List<Item> &list;
+  uint pos;
+public:
+  Item_ref_on_list_position(Item_in_subselect* master,
+			    List<Item> &li, uint num,
+			    char *table_name, char *field_name):
+    Item_ref_null_helper(master, 0, table_name, field_name),
+    list(li), pos(num) {}
+  bool fix_fields(THD *, struct st_table_list *, Item ** ref);
+};
+
 /*
   To resolve '*' field moved to condition
   and register NULL values
@@ -574,24 +543,6 @@ public:
   bool fix_fields(THD *, struct st_table_list *, Item ** ref);
 };
 
-class Item_in_optimizer;
-class Item_ref_in_optimizer: public Item_ref
-{
-protected:
-  Item_in_optimizer* owner;
-public:
-  Item_ref_in_optimizer(Item_in_optimizer* master,
-			char *table_name_par,char *field_name_par);
-  double val();
-  longlong val_int();
-  String* val_str(String* s);
-  bool fix_fields(THD *, struct st_table_list *, Item ** ref)
-  {
-    fixed= 1;
-    return 0;
-  }
-};
-
 /*
   The following class is used to optimize comparing of date columns
   We need to save the original item, to be able to set the field to the
@@ -706,6 +657,122 @@ public:
   bool cmp(void);
 };
 
+class Item_cache: public Item
+{
+public:
+  virtual bool allocate(uint i) { return 0; };
+  virtual bool setup(Item *) { return 0; };
+  virtual void store(Item *)= 0;
+  void set_len_n_dec(uint32 max_len, uint8 dec)
+  {
+    max_length= max_len;
+    decimals= dec;
+  }
+  enum Type type() const { return CACHE_ITEM; }
+  static Item_cache* get_cache(Item_result type);
+};
+
+class Item_cache_int: public Item_cache
+{
+  longlong value;
+public:
+  Item_cache_int() { fixed= 1; null_value= 1; }
+  
+  void store(Item *item)
+  {
+    value= item->val_int_result();
+    null_value= item->null_value;
+  }
+  double val() { return (double) value; }
+  longlong val_int() { return value; }
+  String* val_str(String *str) { str->set(value, thd_charset()); return str; }
+  enum Item_result result_type() const { return INT_RESULT; }
+};
+
+class Item_cache_real: public Item_cache
+{
+  double value;
+public:
+  Item_cache_real() { fixed= 1; null_value= 1; }
+  
+  void store(Item *item)
+  {
+    value= item->val_result();
+    null_value= item->null_value;
+  }
+  double val() { return value; }
+  longlong val_int() { return (longlong) (value+(value > 0 ? 0.5 : -0.5)); }
+  String* val_str(String *str)
+  {
+    str->set(value, decimals, thd_charset());
+    return str;
+  }
+  enum Item_result result_type() const { return REAL_RESULT; }
+};
+
+class Item_cache_str: public Item_cache
+{
+  char buffer[80];
+  String *value;
+public:
+  Item_cache_str() { fixed= 1; null_value= 1; }
+  
+  void store(Item *item);
+  double val();
+  longlong val_int();
+  String* val_str(String *) { return value; }
+  enum Item_result result_type() const { return STRING_RESULT; }
+  CHARSET_INFO *charset() const { return value->charset(); };
+};
+
+class Item_cache_row: public Item_cache
+{
+  Item_cache  **values;
+  uint n;
+public:
+  Item_cache_row(): values(0), n(2) { fixed= 1; null_value= 1; }
+  
+  /*
+    'allocate' used only in row transformer, to preallocate space for row 
+    cache.
+  */
+  bool allocate(uint num);
+  /*
+    'setup' is needed only by row => it not called by simple row subselect
+    (only by IN subselect (in subselect optimizer))
+  */
+  bool setup(Item *item);
+  void store(Item *item);
+  void illegal_method_call(const char *);
+  void make_field(Send_field *)
+  {
+    illegal_method_call((const char*)"make_field");
+  };
+  double val()
+  {
+    illegal_method_call((const char*)"val");
+    return 0;
+  };
+  longlong val_int()
+  {
+    illegal_method_call((const char*)"val_int");
+    return 0;
+  };
+  String *val_str(String *)
+  {
+    illegal_method_call((const char*)"val_str");
+    return 0;
+  };
+  enum Item_result result_type() const { return ROW_RESULT; }
+  
+  uint cols() { return n; }
+  Item* el(uint i) { return values[i]; }
+  Item** addr(uint i) { return (Item **) (values + i); }
+  bool check_cols(uint c);
+  bool null_inside();
+  void bring_value();
+};
+
 extern Item_buff *new_Item_buff(Item *item);
 extern Item_result item_cmp_type(Item_result a,Item_result b);
 extern Item *resolve_const_item(Item *item,Item *cmp_item);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 851a591bae9..df949a910bd 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -247,6 +247,8 @@ int Arg_comparator::compare_e_int()
 int Arg_comparator::compare_row()
 {
   int res= 0;
+  (*a)->bring_value();
+  (*b)->bring_value();
   uint n= (*a)->cols();
   for (uint i= 0; i<n; i++)
   {
@@ -261,6 +263,8 @@ int Arg_comparator::compare_row()
 int Arg_comparator::compare_e_row()
 {
   int res= 0;
+  (*a)->bring_value();
+  (*b)->bring_value();
   uint n= (*a)->cols();
   for (uint i= 0; i<n; i++)
   {
@@ -270,60 +274,67 @@ int Arg_comparator::compare_e_row()
   return 1;
 }
 
-longlong Item_in_optimizer::val_int()
+bool Item_in_optimizer::preallocate_row()
 {
-  int_cache_ok= 1;
-  flt_cache_ok= 0;
-  str_cache_ok= 0;
-  int_cache= args[0]->val_int_result();
-  if (args[0]->null_value)
-  {
-    null_value= 1;
+  if ((cache= Item_cache::get_cache(ROW_RESULT)))
     return 0;
-  }
-  longlong tmp= args[1]->val_int_result();
-  null_value= args[1]->null_value;
-  return tmp;
+  my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+  current_thd->fatal_error= 1;
+  return 1;
 }
 
-longlong Item_in_optimizer::get_cache_int()
+bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
+				   Item ** ref)
 {
-  if (!int_cache_ok)
+  if (args[0]->fix_fields(thd, tables, args))
+    return 1;
+  if (args[0]->maybe_null)
+    maybe_null=1;
+  if (args[0]->binary())
+	set_charset(my_charset_bin);
+  with_sum_func= args[0]->with_sum_func;
+  used_tables_cache= args[0]->used_tables();
+  const_item_cache= args[0]->const_item();
+  if (!cache && !(cache= Item_cache::get_cache(args[0]->result_type())))
+  {
+    my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+    thd->fatal_error= 1;
+    return 1;
+  }
+  cache->setup(args[0]);
+  if (args[1]->fix_fields(thd, tables, args))
+    return 1;
+  Item_in_subselect * sub= (Item_in_subselect *)args[1];
+  if (args[0]->cols() != sub->engine->cols())
   {
-    int_cache_ok= 1;
-    flt_cache_ok= 0;
-    str_cache_ok= 0;
-    int_cache= args[0]->val_int_result();
-    null_value= args[0]->null_value;
+    my_error(ER_CARDINALITY_COL, MYF(0), args[0]->cols());
+    return 1;
   }
-  return int_cache;
+  if (args[1]->maybe_null)
+    maybe_null=1;
+  with_sum_func= with_sum_func || args[1]->with_sum_func;
+  used_tables_cache|= args[1]->used_tables();
+  const_item_cache&= args[1]->const_item();
+  return 0;
 }
 
-double Item_in_optimizer::get_cache()
+longlong Item_in_optimizer::val_int()
 {
-  if (!flt_cache_ok)
+  cache->store(args[0]);
+  if (cache->null_value)
   {
-    flt_cache_ok= 1;
-    int_cache_ok= 0;
-    str_cache_ok= 0;
-    flt_cache= args[0]->val_result();
-    null_value= args[0]->null_value;
+    null_value= 1;
+    return 0;
   }
-  return flt_cache;
+  longlong tmp= args[1]->val_int_result();
+  null_value= args[1]->null_value;
+  return tmp;
 }
 
-String *Item_in_optimizer::get_cache_str(String *s)
+bool Item_in_optimizer::is_null()
 {
-  if (!str_cache_ok)
-  {
-    str_cache_ok= 1;
-    int_cache_ok= 0;
-    flt_cache_ok= 0;
-    str_value.set(buffer, sizeof(buffer), s->charset());
-    str_cache= args[0]->str_result(&str_value);
-    null_value= args[0]->null_value;
-  }
-  return str_cache;
+  cache->store(args[0]);
+  return (null_value= (cache->null_value || args[1]->is_null()));
 }
 
 longlong Item_func_eq::val_int()
@@ -1217,8 +1228,9 @@ void cmp_item_row::store_value(Item *item)
 {
   THD *thd= current_thd;
   n= item->cols();
-  if ((comparators= (cmp_item **) thd->alloc(sizeof(cmp_item *)*n)))
+  if ((comparators= (cmp_item **) thd->calloc(sizeof(cmp_item *)*n)))
   {
+    item->bring_value();
     item->null_value= 0;
     for (uint i=0; i < n; i++)
       if ((comparators[i]= cmp_item::get_comparator(item->el(i))))
@@ -1252,6 +1264,7 @@ void cmp_item_row::store_value_by_template(cmp_item *t, Item *item)
   n= tmpl->n;
   if ((comparators= (cmp_item **) sql_alloc(sizeof(cmp_item *)*n)))
   {
+    item->bring_value();
     item->null_value= 0;
     for (uint i=0; i < n; i++)
       if ((comparators[i]= tmpl->comparators[i]->make_same()))
@@ -1284,6 +1297,7 @@ int cmp_item_row::cmp(Item *arg)
     return 1;
   }
   bool was_null= 0;
+  arg->bring_value();
   for (uint i=0; i < n; i++)
     if (comparators[i]->cmp(arg->el(i)))
     {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 994e51ef89f..e3d8eb7746d 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -85,25 +85,21 @@ public:
   void fix_length_and_dec() { decimals=0; max_length=1; }
 };
 
+class Item_cache;
 class Item_in_optimizer: public Item_bool_func
 {
 protected:
-  char buffer[80];
-  longlong int_cache;
-  double flt_cache;
-  String *str_cache;
-  bool int_cache_ok, flt_cache_ok, str_cache_ok;
-public:
-  Item_in_optimizer(Item *a,Item *b):
-    Item_bool_func(a,b), int_cache_ok(0), flt_cache_ok(0), str_cache_ok(0) {}
-  bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); }
+  Item_cache *cache;
+public:
+  Item_in_optimizer(Item *a, Item_in_subselect *b):
+    Item_bool_func(a, (Item *)b), cache(0) {}
+  // used by row in transformer
+  bool preallocate_row();
+  bool fix_fields(THD *, struct st_table_list *, Item **);
+  bool is_null();
   longlong val_int();
-
-  double get_cache();
-  longlong get_cache_int();
-  String *get_cache_str(String *s);
-
-  friend class Item_ref_in_optimizer;
+  
+  Item_cache **get_cache() { return &cache; }
 };
 
 class Item_bool_func2 :public Item_int_func
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 85a81a50256..9d605e05242 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -41,7 +41,7 @@ void Item_row::illegal_method_call(const char *method)
   DBUG_ENTER("Item_row::illegal_method_call");
   DBUG_PRINT("error", ("!!! %s method was called for row item", method));
   DBUG_ASSERT(0);
-  my_error(ER_CARDINALITY_COL, MYF(0), arg_count);
+  my_error(ER_CARDINALITY_COL, MYF(0), 1);
   DBUG_VOID_RETURN;
 }
 
@@ -100,3 +100,10 @@ bool Item_row::null_inside()
   }
   return 0;
 }
+
+void Item_row::bring_value()
+{
+  for (uint i= 0; i < arg_count; i++)
+    items[i]->bring_value();
+  return;
+}
diff --git a/sql/item_row.h b/sql/item_row.h
index 4767d19d08f..cf67567c3ed 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -70,4 +70,5 @@ public:
   Item** addr(uint i) { return items + i; }
   bool check_cols(uint c);
   bool null_inside();
+  void bring_value();
 };
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 4b9e9c256d1..74824fc7757 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -112,9 +112,14 @@ bool Item_subselect::check_loop(uint id)
   DBUG_RETURN(engine->check_loop(id));
 }
 
+Item::Type Item_subselect::type() const 
+{
+  return SUBSELECT_ITEM;
+}
+
 void Item_subselect::fix_length_and_dec()
 {
-  engine->fix_length_and_dec();
+  engine->fix_length_and_dec(0);
 }
 
 inline table_map Item_subselect::used_tables() const
@@ -122,56 +127,131 @@ inline table_map Item_subselect::used_tables() const
   return (table_map) engine->depended() ? 1L : 0L; 
 }
 
-Item_singleval_subselect::Item_singleval_subselect(THD *thd,
+Item_singlerow_subselect::Item_singlerow_subselect(THD *thd,
 						   st_select_lex *select_lex):
-  Item_subselect()
+  Item_subselect(), value(0)
 {
-  DBUG_ENTER("Item_singleval_subselect::Item_singleval_subselect");
-  init(thd, select_lex, new select_singleval_subselect(this));
+  DBUG_ENTER("Item_singlerow_subselect::Item_singlerow_subselect");
+  init(thd, select_lex, new select_singlerow_subselect(this));
   max_columns= 1;
   maybe_null= 1;
+  max_columns= UINT_MAX;
   DBUG_VOID_RETURN;
 }
 
-void Item_singleval_subselect::fix_length_and_dec()
+void Item_singlerow_subselect::reset()
 {
-  engine->fix_length_and_dec();
-  res_type= engine->type();
+  null_value= 1;
+  if (value)
+    value->null_value= 1;
 }
 
-Item::Type Item_subselect::type() const 
+void Item_singlerow_subselect::store(uint i, Item *item)
 {
-  return SUBSELECT_ITEM;
+  row[i]->store(item);
 }
 
-double Item_singleval_subselect::val () 
+enum Item_result Item_singlerow_subselect::result_type() const
 {
-  if (engine->exec())
+  return engine->type();
+}
+
+void Item_singlerow_subselect::fix_length_and_dec()
+{
+  if ((max_columns= engine->cols()) == 1)
+  {
+    engine->fix_length_and_dec(row= &value);
+    if (!(value= Item_cache::get_cache(engine->type())))
+    {
+      my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+      current_thd->fatal_error= 1;
+      return;
+    }  
+  }
+  else
+  {
+    THD *thd= current_thd;
+    if (!(row= (Item_cache**)thd->alloc(sizeof(Item_cache*)*max_columns)))
+    {
+      my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+      thd->fatal_error= 1;
+      return;
+    }
+    engine->fix_length_and_dec(row);
+    value= *row;
+  }
+}
+
+uint Item_singlerow_subselect::cols()
+{
+  return engine->cols();
+}
+
+bool Item_singlerow_subselect::check_cols(uint c)
+{
+  if (c != engine->cols())
+  {
+    my_error(ER_CARDINALITY_COL, MYF(0), c);
+    return 1;
+  }
+  return 0;
+}
+
+bool Item_singlerow_subselect::null_inside()
+{
+  for (uint i= 0; i < max_columns ; i++)
+  {
+    if (row[i]->null_value)
+      return 1;
+  }
+  return 0;
+}
+
+void Item_singlerow_subselect::bring_value()
+{
+  engine->exec();
+}
+
+double Item_singlerow_subselect::val () 
+{
+  if (!engine->exec() && !value->null_value)
+  {
+    null_value= 0;
+    return value->val();
+  }
+  else
   {
     reset();
     return 0;
   }
-  return real_value;
 }
 
-longlong Item_singleval_subselect::val_int () 
+longlong Item_singlerow_subselect::val_int () 
 {
-  if (engine->exec())
+  if (!engine->exec() && !value->null_value)
+  {
+    null_value= 0;
+    return value->val_int();
+  }
+  else
   {
     reset();
     return 0;
   }
-  return int_value;
 }
 
-String *Item_singleval_subselect::val_str (String *str) 
+String *Item_singlerow_subselect::val_str (String *str) 
 {
-  if (engine->exec() || null_value)
+  if (!engine->exec() && !value->null_value)
+  {
+    null_value= 0;
+    return value->val_str(str);
+  }
+  else
   {
     reset();
     return 0;
   }
-  return &string_value;
 }
 
 Item_exists_subselect::Item_exists_subselect(THD *thd,
@@ -213,7 +293,7 @@ Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
   left_expr= left_exp;
   func= f;
   init(thd, select_lex, new select_exists_subselect(this));
-  max_columns= UINT_MAX;
+  max_columns= 1;
   reset();
   // We need only 1 row to determinate existence
   select_lex->master_unit()->global_parameters->select_limit= 1;
@@ -223,8 +303,9 @@ Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
 
 void Item_exists_subselect::fix_length_and_dec()
 {
-   decimals=0;
+   decimals= 0;
    max_length= 1;
+   max_columns= engine->cols();
 }
 
 double Item_exists_subselect::val () 
@@ -336,8 +417,9 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
     As far as  Item_ref_in_optimizer do not substitude itself on fix_fields
     we can use same item for all selects.
   */
-  Item *expr= new Item_ref_in_optimizer(optimizer, (char *)"<no matter>",
-					(char*)"<left expr>");
+  Item *expr= new Item_ref((Item**)optimizer->get_cache(), 
+			   (char *)"<no matter>",
+			   (char*)"<left expr>");
   select_lex->master_unit()->dependent= 1;
   for (SELECT_LEX * sl= select_lex; sl; sl= sl->next_select())
   {
@@ -373,7 +455,7 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
 	  sl->having= item;
       else
 	if (sl->where)
-	  sl->where= new Item_cond_and(sl->having, item);
+	  sl->where= new Item_cond_and(sl->where, item);
 	else
 	  sl->where= item;
     }
@@ -431,10 +513,68 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
   DBUG_VOID_RETURN;
 }
 
+void Item_in_subselect::row_value_transformer(st_select_lex *select_lex,
+					      Item *left_expr)
+{
+  DBUG_ENTER("Item_in_subselect::row_value_transformer");
+  Item_in_optimizer *optimizer;
+  substitution= optimizer= new Item_in_optimizer(left_expr, this);
+  if (!optimizer)
+  {
+    current_thd->fatal_error= 1;
+    DBUG_VOID_RETURN;
+  }
+  select_lex->master_unit()->dependent= 1;
+  uint n= left_expr->cols();
+  if (optimizer->preallocate_row() || (*optimizer->get_cache())->allocate(n))
+    DBUG_VOID_RETURN;
+  for (SELECT_LEX * sl= select_lex; sl; sl= sl->next_select())
+  {
+    select_lex->dependent= 1;
+
+    Item *item= 0;
+    List_iterator_fast<Item> li(sl->item_list);
+    for (uint i= 0; i < n; i++)
+    {
+      Item *func=
+	new Item_ref_on_list_position(this, sl->item_list, i,
+					       (char *) "<no matter>",
+					       (char *) "<list ref>");
+      func=
+	Item_bool_func2::eq_creator(new Item_ref((*optimizer->get_cache())->
+						 addr(i), 
+						 (char *)"<no matter>",
+						 (char *)"<left expr>"),
+				    func);
+      if (!item)
+	item= func;
+      else
+	item= new Item_cond_and(item, func);
+    }
+
+    if (sl->having || sl->with_sum_func || sl->group_list.first ||
+	!sl->table_list.elements)
+      if (sl->having)
+	sl->having= new Item_cond_and(sl->having, item);
+      else
+	sl->having= item;
+    else
+      if (sl->where)
+	sl->where= new Item_cond_and(sl->where, item);
+      else
+	sl->where= item;
+  }
+  DBUG_VOID_RETURN;
+}
+
+
 void Item_in_subselect::select_transformer(st_select_lex *select_lex)
 {
-  single_value_transformer(select_lex, left_expr,
-			   &Item_bool_func2::eq_creator);
+  if (left_expr->cols() == 1)
+    single_value_transformer(select_lex, left_expr,
+			     &Item_bool_func2::eq_creator);
+  else
+    row_value_transformer(select_lex, left_expr);
 }
 
 void Item_allany_subselect::select_transformer(st_select_lex *select_lex)
@@ -509,31 +649,85 @@ int subselect_union_engine::prepare()
   return unit->prepare(thd, result);
 }
 
-void subselect_single_select_engine::fix_length_and_dec()
+static Item_result set_row(SELECT_LEX *select_lex, Item * item,
+			   Item_cache **row)
 {
+  Item_result res_type= STRING_RESULT;
+  Item *sel_item;
   List_iterator_fast<Item> li(select_lex->item_list);
-  Item *sel_item= li++;
-  item->max_length= sel_item->max_length;
-  res_type= sel_item->result_type();
-  item->decimals= sel_item->decimals;
+  for (uint i= 0; (sel_item= li++); i++)
+  {
+    item->max_length= sel_item->max_length;
+    res_type= sel_item->result_type();
+    item->decimals= sel_item->decimals;
+    if (row)
+    {
+      if (!(row[i]= Item_cache::get_cache(res_type)))
+      {
+	my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+	current_thd->fatal_error= 1;
+	return STRING_RESULT; // we should return something
+      }
+      row[i]->set_len_n_dec(sel_item->max_length, sel_item->decimals);
+    }
+  }
+  if (select_lex->item_list.elements > 1)
+    res_type= ROW_RESULT;
+  return res_type;
 }
 
-void subselect_union_engine::fix_length_and_dec()
+void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
 {
-  uint32 mlen= 0, len;
-  Item *sel_item= 0;
-  for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+  DBUG_ASSERT(row || select_lex->item_list.elements==1);
+  res_type= set_row(select_lex, item, row);
+}
+
+void subselect_union_engine::fix_length_and_dec(Item_cache **row)
+{
+  DBUG_ASSERT(row || unit->first_select()->item_list.elements==1);
+
+  if (unit->first_select()->item_list.elements == 1)
   {
-    List_iterator_fast<Item> li(sl->item_list);
-    Item *s_item= li++;
-    if ((len= s_item->max_length))
-      mlen= len;
-    if (!sel_item)
-      sel_item= s_item;
+    uint32 mlen= 0, len;
+    Item *sel_item= 0;
+    for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+    {
+      List_iterator_fast<Item> li(sl->item_list);
+      Item *s_item= li++;
+      if ((len= s_item->max_length) > mlen)
+	mlen= len;
+      if (!sel_item)
+	sel_item= s_item;
+    }
+    item->max_length= mlen;
+    res_type= sel_item->result_type();
+    item->decimals= sel_item->decimals;
+    if (row)
+    {
+      if (!(row[0]= Item_cache::get_cache(res_type)))
+      {
+	my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+	current_thd->fatal_error= 1;
+	return;
+      }
+      row[0]->set_len_n_dec(mlen, sel_item->decimals);
+    }
+  }
+  else
+  {
+    SELECT_LEX *sl= unit->first_select();
+    res_type= set_row(sl, item, row);
+    for(sl= sl->next_select(); sl; sl->next_select())
+    {
+      List_iterator_fast<Item> li(sl->item_list);
+      Item *sel_item;
+      for (uint i= 0; (sel_item= li++); i++)
+      {
+	if (sel_item->max_length > row[i]->max_length)
+	  row[i]->max_length= sel_item->max_length;
+      }
+    }
   }
-  item->max_length= mlen;
-  res_type= sel_item->result_type();
-  item->decimals= sel_item->decimals;
 }
 
 int subselect_single_select_engine::exec()
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 0e6f939803d..04f0e6f3c83 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -86,51 +86,42 @@ public:
   bool check_loop(uint id);
 
   friend class select_subselect;
+  friend class Item_in_optimizer;
 };
 
 /* single value subselect */
 
-class Item_singleval_subselect :public Item_subselect
+class Item_cache;
+class Item_singlerow_subselect :public Item_subselect
 {
 protected:
-  longlong int_value; /* Here stored integer value of this item */
-  double real_value; /* Here stored real value of this item */
-  /* 
-     Here stored string value of this item.
-     (str_value used only as temporary buffer, because it can be changed 
-     by Item::save_field)
-  */
-  String string_value; 
-  enum Item_result res_type; /* type of results */
-  
+  Item_cache *value, **row;
 public:
-  Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
-  Item_singleval_subselect(Item_singleval_subselect *item):
+  Item_singlerow_subselect(THD *thd, st_select_lex *select_lex);
+  Item_singlerow_subselect(Item_singlerow_subselect *item):
     Item_subselect(item)
   {
-    int_value= item->int_value;
-    real_value= item->real_value;
-    string_value.set(item->string_value, 0, item->string_value.length());
+    value= item->value;
     max_length= item->max_length;
     decimals= item->decimals;
-    res_type= item->res_type;
-  }
-  virtual void reset() 
-  {
-    null_value= 1;
-    int_value= 0;
-    real_value= 0;
-    max_length= 4;
-    res_type= STRING_RESULT;
   }
-  double val ();
+  void reset();
+  void store(uint i, Item* item);
+  double val();
   longlong val_int ();
   String *val_str (String *);
-  Item *new_item() { return new Item_singleval_subselect(this); }
-  enum Item_result result_type() const { return res_type; }
+  Item *new_item() { return new Item_singlerow_subselect(this); }
+  enum Item_result result_type() const;
   void fix_length_and_dec();
 
-  friend class select_singleval_subselect;
+  uint cols();
+  Item* el(uint i) { return (Item*)row[i]; }
+  Item** addr(uint i) { return (Item**)row + i; }
+  bool check_cols(uint c);
+  bool null_inside();
+  void bring_value();
+
+  friend class select_singlerow_subselect;
 };
 
 /* exists subselect */
@@ -149,7 +140,7 @@ public:
   }
   Item_exists_subselect(): Item_subselect() {}
 
-  virtual void reset() 
+  void reset() 
   {
     value= 0;
   }
@@ -184,6 +175,7 @@ public:
   virtual void select_transformer(st_select_lex *select_lex);
   void single_value_transformer(st_select_lex *select_lex,
 				Item *left_expr, compare_func_creator func);
+  void row_value_transformer(st_select_lex *select_lex, Item *left_expr);
   longlong val_int();
   double val();
   String *val_str(String*);
@@ -228,7 +220,7 @@ public:
   }
 
   virtual int prepare()= 0;
-  virtual void fix_length_and_dec()= 0;
+  virtual void fix_length_and_dec(Item_cache** row)= 0;
   virtual int exec()= 0;
   virtual uint cols()= 0; /* return number of columnss in select */
   virtual bool depended()= 0; /* depended from outer select */
@@ -249,7 +241,7 @@ public:
 				 select_subselect *result,
 				 Item_subselect *item);
   int prepare();
-  void fix_length_and_dec();
+  void fix_length_and_dec(Item_cache** row);
   int exec();
   uint cols();
   bool depended();
@@ -266,7 +258,7 @@ public:
 			 select_subselect *result,
 			 Item_subselect *item);
   int prepare();
-  void fix_length_and_dec();
+  void fix_length_and_dec(Item_cache** row);
   int exec();
   uint cols();
   bool depended();
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index ef40ebeb273..0ab47951074 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -917,10 +917,10 @@ select_subselect::select_subselect(Item_subselect *item)
   this->item=item;
 }
 
-bool select_singleval_subselect::send_data(List<Item> &items)
+bool select_singlerow_subselect::send_data(List<Item> &items)
 {
-  DBUG_ENTER("select_singleval_subselect::send_data");
-  Item_singleval_subselect *it= (Item_singleval_subselect *)item;
+  DBUG_ENTER("select_singlerow_subselect::send_data");
+  Item_singlerow_subselect *it= (Item_singlerow_subselect *)item;
   if (it->assigned())
   {
       my_message(ER_SUBSELECT_NO_1_ROW, ER(ER_SUBSELECT_NO_1_ROW), MYF(0));
@@ -932,32 +932,9 @@ bool select_singleval_subselect::send_data(List<Item> &items)
     DBUG_RETURN(0);
   }
   List_iterator_fast<Item> li(items);
-  Item *val_item= li++;                   // Only one (single value subselect)
-  /*
-    Following val() call have to be first, because function AVG() & STD()
-    calculate value on it & determinate "is it NULL?".
-  */
-  it->real_value= val_item->val_result();
-  if ((it->null_value= val_item->null_value))
-  {
-    it->reset();
-  } 
-  else 
-  {
-    it->max_length= val_item->max_length;
-    it->decimals= val_item->decimals;
-    it->set_charset(val_item->charset());
-    it->int_value= val_item->val_int_result();
-    String *s= val_item->str_result(&it->string_value);
-    if (s != &it->string_value)
-    {
-      it->string_value.set(*s, 0, s->length());
-    }
-    // TODO: remove when correct charset handling appeared for Item
-    it->str_value.set(*s, 0, s->length()); // store charset
-
-    it->res_type= val_item->result_type();
-  }
+  Item *val_item;
+  for (uint i= 0; (val_item= li++); i++)
+    it->store(i, val_item);
   it->assigned(1);
   DBUG_RETURN(0);
 }
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 7ca66d9ffb7..3abcf12e4b7 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -824,10 +824,10 @@ public:
 };
 
 /* Single value subselect interface class */
-class select_singleval_subselect :public select_subselect
+class select_singlerow_subselect :public select_subselect
 {
 public:
-  select_singleval_subselect(Item_subselect *item):select_subselect(item){}
+  select_singlerow_subselect(Item_subselect *item):select_subselect(item){}
   bool send_data(List<Item> &items);
 };
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 55f165c0739..d160c5281a8 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -587,7 +587,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr
 	table_wild opt_pad no_in_expr expr_expr simple_expr no_and_expr
 	using_list expr_or_default set_expr_or_default
-	param_marker singleval_subselect singleval_subselect_init
+	param_marker singlerow_subselect singlerow_subselect_init
 	exists_subselect exists_subselect_init
 
 %type <item_list>
@@ -2017,7 +2017,7 @@ simple_expr:
 	    $$= new Item_row(*$5);
 	  }
 	| EXISTS exists_subselect { $$= $2; }
-	| singleval_subselect   { $$= $1; }
+	| singlerow_subselect   { $$= $1; }
 	| '{' ident expr '}'	{ $$= $3; }
         | MATCH ident_list_arg AGAINST '(' expr ')'
           { Select->add_ftfunc_to_list((Item_func_match *)
@@ -4487,17 +4487,17 @@ union_option:
 	/* empty */ {}
 	| ALL {Select->master_unit()->union_option= 1;};
 
-singleval_subselect:
-	subselect_start singleval_subselect_init
+singlerow_subselect:
+	subselect_start singlerow_subselect_init
 	subselect_end
 	{
 	  $$= $2;
 	};
 
-singleval_subselect_init:
+singlerow_subselect_init:
 	select_init2
 	{
-	  $$= new Item_singleval_subselect(YYTHD,
+	  $$= new Item_singlerow_subselect(YYTHD,
 					   Lex->current_select->master_unit()->
                                              first_select());
 	};