bk commit into 4.1 tree (bell:1.2189) BUG#8888

[sanja]

Below is the list of changes that have just been committed into a local
4.1 repository of bell. When bell does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2189 05/03/10 14:01:22 bell@stripped +4 -0
  fixed left expression of IN/ALL/ANY subquery transformation support (BUG#8888)

  sql/item_subselect.h
    1.59 05/03/10 14:00:30 bell@stripped +4 -4
    optimised field initialization and new method

  sql/item_subselect.cc
    1.129 05/03/10 14:00:30 bell@stripped +111 -67
    fixfields made for left part of IN/ALL/ANY subquery before calling cols() to detect
type of transformation (scalar or row)
    similar action of both transformation moved to separate function

  mysql-test/t/subselect.test
    1.142 05/03/10 14:00:30 bell@stripped +10 -2
    fixed last test
    test of transformation of left expression of subquery

  mysql-test/r/subselect.result
    1.163 05/03/10 14:00:30 bell@stripped +10 -2
    result changing
    fixed last test
    test of transformation of left expression of subquery

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	bell
# Host:	sanja.is.com.ua
# Root:	/home/bell/mysql/bk/work-4.1

--- 1.162/mysql-test/r/subselect.result	Mon Feb 14 22:48:59 2005
+++ 1.163/mysql-test/r/subselect.result	Thu Mar 10 14:00:30 2005
@@ -2128,7 +2128,7 @@
 create table t1 (a1 int);
 create table t2 (b1 int);
 select * from t1 where a2 > any(select b1 from t2);
-ERROR 42S22: Unknown column 'a2' in 'scalar IN/ALL/ANY subquery'
+ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
 select * from t1 where a1 > any(select b1 from t2);
 a1
 drop table t1,t2;
@@ -2267,4 +2267,12 @@
 1	5141	12	group2	12	group2	5	1	1	87	Oct04
 1	5141	12	group2	12	group2	5	1	2	88	Oct04
 1	5141	12	group2	12	group2	5	1	2	89	Oct04
-drop table if exists t1, t2, t3, t4, t5;
+drop table t1, t2, t3, t4, t5;
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
+1
+1
+1
+1
+drop table t1;

--- 1.141/mysql-test/t/subselect.test	Mon Feb 14 22:48:59 2005
+++ 1.142/mysql-test/t/subselect.test	Thu Mar 10 14:00:30 2005
@@ -1438,8 +1438,9 @@
 select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
 drop table t1;
 
+#
 # Test for BUG#8218
-
+#
 CREATE TABLE t1 (
   categoryId int(11) NOT NULL,
   courseId int(11) NOT NULL,
@@ -1536,5 +1537,12 @@
 group by 
   groupstuff.groupname, colhead , t2.courseid;
 
-drop table if exists t1, t2, t3, t4, t5;
+drop table t1, t2, t3, t4, t5;
 
+#
+# Transformation in left expression of subquery (BUG#8888)
+#
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
+drop table t1;

--- 1.128/sql/item_subselect.cc	Wed Feb  9 21:08:04 2005
+++ 1.129/sql/item_subselect.cc	Thu Mar 10 14:00:30 2005
@@ -542,7 +542,7 @@
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  Item_exists_subselect(), transformed(0), upper_item(0)
+  Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -669,18 +669,9 @@
 Item_in_subselect::single_value_transformer(JOIN *join,
 					    Comp_creator *func)
 {
-  const char *save_where= thd->where;
   DBUG_ENTER("Item_in_subselect::single_value_transformer");
 
-  if (changed)
-  {
-    DBUG_RETURN(RES_OK);
-  }
-
   SELECT_LEX *select_lex= join->select_lex;
-  Item_arena *arena, backup;
-  arena= thd->change_arena_if_needed(&backup);
-  thd->where= "scalar IN/ALL/ANY subquery";
 
   /*
     Check that the right part of the subselect contains no more than one
@@ -689,7 +680,7 @@
   if (select_lex->item_list.elements > 1)
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
-    goto err;
+    DBUG_RETURN(RES_ERROR);
   }
 
   /*
@@ -709,7 +700,7 @@
     if (substitution)
     {
       // It is second (third, ...) SELECT of UNION => All is done
-      goto ok;
+      DBUG_RETURN(RES_OK);
     }
 
     Item *subs;
@@ -748,7 +739,7 @@
 	reference
       */
       if (item->fix_fields(thd, join->tables_list, 0))
-	goto err;
+	DBUG_RETURN(RES_ERROR);
       /* we added aggregate function => we have to change statistic */
       count_field_types(&join->tmp_table_param, join->all_fields, 0);
 
@@ -764,25 +755,16 @@
       if (upper_item)
         upper_item->set_sub_test(item);
     }
-    // left expression belong to outer select
-    SELECT_LEX *current= thd->lex->current_select, *up;
-    thd->lex->current_select= up= current->return_after_parsing();
-    if (!left_expr->fixed && 
-        left_expr->fix_fields(thd, up->get_table_list(), &left_expr))
-    {
-      thd->lex->current_select= current;
-      goto err;
-    }
-    thd->lex->current_select= current;
+    /* fix fields is already called for  left expression */
     substitution= func->create(left_expr, subs);
-    goto ok;
+    DBUG_RETURN(RES_OK);
   }
 
   if (!substitution)
   {
     //first call for this unit
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
-    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+    substitution= optimizer;
 
     SELECT_LEX *current= thd->lex->current_select, *up;
 
@@ -791,7 +773,7 @@
     if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
       thd->lex->current_select= current;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     thd->lex->current_select= current;
 
@@ -835,7 +817,7 @@
     if (join->having->fix_fields(thd, join->tables_list, 0))
     {
       select_lex->having_fix_field= 0;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     select_lex->having_fix_field= 0;
   }
@@ -865,7 +847,7 @@
 	if (join->having->fix_fields(thd, join->tables_list, 0))
 	{
 	  select_lex->having_fix_field= 0;
-	  goto err;
+	  DBUG_RETURN(RES_ERROR);
 	}
 	select_lex->having_fix_field= 0;
 	item= new Item_cond_or(item,
@@ -879,7 +861,7 @@
       */
       select_lex->where= join->conds= and_items(join->conds, item);
       if (join->conds->fix_fields(thd, join->tables_list, 0))
-	goto err;
+	DBUG_RETURN(RES_ERROR);
     }
     else
     {
@@ -901,7 +883,7 @@
 				     0))
 	{
 	  select_lex->having_fix_field= 0;
-	  goto err;
+	  DBUG_RETURN(RES_ERROR);
 	}
 	select_lex->having_fix_field= 0;
       }
@@ -919,54 +901,34 @@
 	  push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
 		       ER_SELECT_REDUCED, warn_buff);
 	}
-	if (arena)
-	  thd->restore_backup_item_arena(arena, &backup);
 	DBUG_RETURN(RES_REDUCE);
       }
     }
   }
 
-ok:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  thd->where= save_where;
   DBUG_RETURN(RES_OK);
-
-err:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  DBUG_RETURN(RES_ERROR);
 }
 
 
 Item_subselect::trans_res
 Item_in_subselect::row_value_transformer(JOIN *join)
 {
-  const char *save_where= thd->where;
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
-  if (changed)
-  {
-    DBUG_RETURN(RES_OK);
-  }
-  Item_arena *arena, backup;
   Item *item= 0;
   SELECT_LEX *select_lex= join->select_lex;
 
-  thd->where= "row IN/ALL/ANY subquery";
-  arena= thd->change_arena_if_needed(&backup);
-
   if (select_lex->item_list.elements != left_expr->cols())
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), left_expr->cols());
-    goto err;
+    DBUG_RETURN(RES_ERROR);
   }
 
   if (!substitution)
   {
     //first call for this unit
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
-    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+    substitution= optimizer;
 
     SELECT_LEX *current= thd->lex->current_select, *up;
     thd->lex->current_select= up= current->return_after_parsing();
@@ -974,7 +936,7 @@
     if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
       thd->lex->current_select= current;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
 
     // we will refer to apper level cache array => we have to save it in PS
@@ -993,7 +955,7 @@
       DBUG_ASSERT(left_expr->fixed &&
select_lex->ref_pointer_array[i]->fixed);
       if (select_lex->ref_pointer_array[i]->
           check_cols(left_expr->el(i)->cols()))
-        goto err;
+        DBUG_RETURN(RES_ERROR);
       Item *func= new Item_ref_null_helper(this,
 					   select_lex->ref_pointer_array+i,
 					   (char *) "<no matter>",
@@ -1021,7 +983,7 @@
     if (join->having->fix_fields(thd, join->tables_list, 0))
     {
       select_lex->having_fix_field= 0;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     select_lex->having_fix_field= 0;
   }
@@ -1034,27 +996,109 @@
     */
     select_lex->where= join->conds= and_items(join->conds, item);
     if (join->conds->fix_fields(thd, join->tables_list, 0))
-      goto err;
+      DBUG_RETURN(RES_ERROR);
   }
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  thd->where= save_where;
   DBUG_RETURN(RES_OK);
-
-err:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  DBUG_RETURN(RES_ERROR);
 }
 
 
 Item_subselect::trans_res
 Item_in_subselect::select_transformer(JOIN *join)
 {
+  return select_in_like_transformer(join, &eq_creator);
+}
+
+
+/*
+  Prepare IN/ALL/ANY/SOME subquery transformation and call appropriate
+  transformation function
+
+  SYNOPSIS
+    Item_in_subselect::select_in_like_transformer()
+    join    JOIN object of transforming subquery
+    func    creator of condition function of subquery
+
+  DESCRIPTION
+    To decide which transformation procedure (scalar or row) applicable here
+    we have to call fix_fields() for left expression to be able to call
+    cols() method on it. Also this method make arena management for
+    underlying transformation methods.
+
+  RETURN
+    RES_OK      OK
+    RES_REDUCE  OK, and current subquery was reduced during transformation
+    RES_ERROR   Error
+*/
+
+Item_subselect::trans_res
+Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
+{
+  Item_arena *arena, backup;
+  SELECT_LEX *current= thd->lex->current_select, *up;
+  const char *save_where= thd->where;
+  Item_subselect::trans_res res= RES_ERROR;
+  bool result;
+
+  DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
+  if (changed)
+  {
+    DBUG_RETURN(RES_OK);
+  }
+
+  thd->where= "IN/ALL/ANY subquery";
+
+  /*
+    In some optimisation cases we will not need this Item_in_optimizer
+    object, but we can't know it here, but here we need address correct
+    reference on left expresion.
+  */
+  if (!optimizer)
+  {
+    arena= thd->change_arena_if_needed(&backup);
+    result= (!(optimizer= new Item_in_optimizer(left_expr, this)));
+    if (arena)
+      thd->restore_backup_item_arena(arena, &backup);
+    if (result)
+      goto err;
+  }
+
+  thd->lex->current_select= up= current->return_after_parsing();
+  result= (!left_expr->fixed &&
+           left_expr->fix_fields(thd, up->get_table_list(),
+                                 optimizer->arguments()));
+  /* fix_fields can change reference to left_expr, we need reassign it */
+  left_expr= optimizer->arguments()[0];
+
+  thd->lex->current_select= current;
+  if (result)
+    goto err;
+
   transformed= 1;
+  arena= thd->change_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
+    which allow to us call them with changed arena (if we do not know nature
+    of Item, we have to call fix_fields() for it only with original arena to
+    avoid memory leack)
+  */
   if (left_expr->cols() == 1)
-    return single_value_transformer(join, &eq_creator);
-  return row_value_transformer(join);
+    res= single_value_transformer(join, func);
+  else
+  {
+    /* we do not support row operation for ALL/ANY/SOME */
+    if (func != &eq_creator)
+    {
+      my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
+      DBUG_RETURN(RES_ERROR);
+    }
+    res= row_value_transformer(join);
+  }
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
+err:
+  thd->where= save_where;
+  DBUG_RETURN(res);
 }
 
 
@@ -1077,7 +1121,7 @@
   transformed= 1;
   if (upper_item)
     upper_item->show= 1;
-  return single_value_transformer(join, func);
+  return select_in_like_transformer(join, func);
 }
 
 

--- 1.58/sql/item_subselect.h	Tue Dec  7 21:18:12 2004
+++ 1.59/sql/item_subselect.h	Thu Mar 10 14:00:30 2005
@@ -220,8 +220,8 @@
 
   Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect()
-    :Item_exists_subselect(), abort_on_null(0), transformed(0), upper_item(0)
-     
+    :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
+     upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -232,8 +232,8 @@
     was_null= 0;
   }
   trans_res select_transformer(JOIN *join);
-  trans_res single_value_transformer(JOIN *join,
-				     Comp_creator *func);
+  trans_res select_in_like_transformer(JOIN *join, Comp_creator *func);
+  trans_res single_value_transformer(JOIN *join, Comp_creator *func);
   trans_res row_value_transformer(JOIN * join);
   longlong val_int();
   double val();