MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:eugene Date:January 30 2007 10:07pm
Subject:bk commit into 5.0 tree (evgen:1.2386) BUG#23800
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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@stripped, 2007-01-31 01:07:38+03:00, evgen@stripped +12 -0
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  
  All outer fields in a subquery were represented by Item_field objects.
  If the outer select employs a temporary table all such fields should be
  replaced with fields from that temporary table in order to point to the 
  actual data. This replacement wasn't done and that resulted in a wrong
  subquery result and wrong result of the whole query.
  
  Now any outer field is represented by two objects - Item_field placed in the
  outer select and Item_ref in the subquery. Item_field object is processed as
  a normal field and takes place in the ref_pointer_array, if there is a 
  temporary table it is copied there, so on. Thus the Item_ref is always
  references the correct field. Original field is substituted with the reference
  in the Item_field::fix_outer_field() function.
  
  New function called fix_inner_refs() is added to fix fields referenced from
  inner selects and references (Item_ref objects) to these fields.
  
  Item_ref class is updated to behave more like Item_field when used to
  represent an outer reference.

  mysql-test/r/subselect.result@stripped, 2007-01-30 23:54:23+03:00, evgen@stripped +18 -9
    Added a test case for bug#23800: Correlated sub query returning incorrect results when operated upon.

  mysql-test/r/subselect3.result@stripped, 2007-01-30 23:54:42+03:00, evgen@stripped +4 -4
    Corrected test cases result after bfix for bug#23800: Correlated sub query returning incorrect results when operated upon.

  mysql-test/t/subselect.test@stripped, 2007-01-30 23:55:26+03:00, evgen@stripped +10 -0
    Added a test case for bug#23800: Correlated sub query returning incorrect results when operated upon.

  sql/item.cc@stripped, 2007-01-31 01:05:25+03:00, evgen@stripped +68 -8
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Now all outer fields are substituted with references to them (Item_ref objects) in the Item_field::fix_outer_field() function.
    Original fields are saved in the inner_fields_list of the outer select. The new reference is saved in the inner_refs_list of the outer select.
    Item_ref class is updated to behave more like Item_field when used to
    represent an outer reference.

  sql/item.h@stripped, 2007-01-31 00:18:34+03:00, evgen@stripped +11 -4
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Item_ref class is updated to behave more like Item_field when used to
    represent an outer reference.

  sql/mysql_priv.h@stripped, 2007-01-31 00:06:40+03:00, evgen@stripped +2 -0
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Added the fix_inner_refs() function prototype.

  sql/sql_delete.cc@stripped, 2007-01-31 00:07:22+03:00, evgen@stripped +6 -0
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Added call to the fix_inner_refs() function.

  sql/sql_lex.cc@stripped, 2007-01-31 00:13:02+03:00, evgen@stripped +2 -0
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Initialize of the inner_refs_list and the inner_fields_list lists.

  sql/sql_lex.h@stripped, 2007-01-31 00:11:04+03:00, evgen@stripped +4 -1
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    The inner_refs_list and the inner_fields_list are added to the SELECT_LEX class.

  sql/sql_select.cc@stripped, 2007-01-31 01:06:12+03:00, evgen@stripped +74 -2
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    The new function called fix_inner_refs() is added.

  sql/sql_union.cc@stripped, 2007-01-31 00:12:23+03:00, evgen@stripped +2 -0
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Cleanup of the inner_refs_list and the inner_fields_list lists.

  sql/sql_update.cc@stripped, 2007-01-31 00:14:37+03:00, evgen@stripped +5 -0
    Bug#23800: Correlated sub query returning incorrect results when operated upon.
    Added call to the fix_inner_refs() function.

# 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:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/23800-bug-5.0-opt-mysql

--- 1.249/sql/item.cc	2007-01-11 23:17:37 +03:00
+++ 1.250/sql/item.cc	2007-01-31 01:05:25 +03:00
@@ -3481,8 +3481,54 @@
         }
         if (*from_field != view_ref_found)
         {
+          bool inner_found= false;
+          List_iterator<Item_field> li(select->inner_fields_list);
+          Item_field *item, **field_ref= NULL;
+          Item_ref *rf;
+
           prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
           prev_subselect_item->const_item_cache= 0;
+          if (!last_checked_context->select_lex->having_fix_field)
+          {
+            /*
+              All outer fields are replaced with Item_ref objects.
+              This is done in order to get correct results when the outer
+              select employs a temporary table.
+              Original fields are saved in the inner_fields_list of the outer
+              select. We need it due to we need a place to reference to in the
+              Item_ref object. The new Item_ref object replaces the original
+              field and is also saved in the inner_refs_list of the outer
+              select. Here it is only created. It can be fixed only after
+              the original field and this is done in the fix_inner_refs()
+              function.
+
+              See also comments to the fix_inner_refs() function.
+            */
+            set_field(*from_field);
+            /* check whether this field already saved */
+            while ((item= li++))
+            {
+              if (this->eq(item, 0))
+              {
+                inner_found= TRUE;
+                field_ref= li.ref();
+                break;
+              }
+            }
+            if (!inner_found)
+            {
+              select->inner_fields_list.push_front(this);
+              field_ref= select->inner_fields_list.head_ref();
+            }
+            rf= new Item_ref(context, (Item**)field_ref,
+                             cached_table ? cached_table->alias : "",
+                             field_name);
+            if (!rf)
+              return -1;
+
+            thd->change_item_tree(reference, rf);
+            select->inner_refs_list.push_back(rf);
+          }
           if (thd->lex->in_sum_func &&
               thd->lex->in_sum_func->nest_level == 
               thd->lex->current_select->nest_level)
@@ -3609,7 +3655,7 @@
   {
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex,
-                      this, this);
+                      this, (Item_ident*)*reference);
     if (last_checked_context->select_lex->having_fix_field)
     {
       Item_ref *rf;
@@ -4796,7 +4842,7 @@
                    Item **item, const char *table_name_arg,
                    const char *field_name_arg)
   :Item_ident(context_arg, NullS, table_name_arg, field_name_arg),
-   result_field(0), ref(item)
+   result_field(0), ref(item), outer_ref(0)
 {
   /*
     This constructor used to create some internals references over fixed items
@@ -5039,6 +5085,7 @@
   }
 
   DBUG_ASSERT(*ref);
+
   /*
     Check if this is an incorrect reference in a group function or forward
     reference. Do not issue an error if this is an unnamed reference inside an
@@ -5084,6 +5131,18 @@
     alias_name_used= ((Item_ident *) (*ref))->alias_name_used;
   else
     alias_name_used= TRUE; // it is not field, so it is was resolved by alias
+  /* Mark as an outer reference */
+  if ((*ref)->type() == Item::FIELD_ITEM)
+  {
+    Item_field *field= *(Item_field**)ref;
+    if (field->cached_table && field->cached_table->select_lex &&
+        context->select_lex &&
+        field->cached_table->select_lex != context->select_lex)
+
+    {
+      outer_ref= 1;
+    }
+  }
   fixed= 1;
 }
 
@@ -5102,7 +5161,7 @@
   if (ref)
   {
     if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
-        name && alias_name_used)
+        !outer_ref && name && alias_name_used)
     {
       THD *thd= current_thd;
       append_identifier(thd, str, name, (uint) strlen(name));
@@ -5204,7 +5263,7 @@
 double Item_ref::val_real()
 {
   DBUG_ASSERT(fixed);
-  double tmp=(*ref)->val_result();
+  double tmp= outer_ref ? (*ref)->val_real() : (*ref)->val_result();
   null_value=(*ref)->null_value;
   return tmp;
 }
@@ -5213,7 +5272,7 @@
 longlong Item_ref::val_int()
 {
   DBUG_ASSERT(fixed);
-  longlong tmp=(*ref)->val_int_result();
+  longlong tmp= outer_ref ? (*ref)->val_int() : (*ref)->val_int_result();
   null_value=(*ref)->null_value;
   return tmp;
 }
@@ -5222,7 +5281,7 @@
 bool Item_ref::val_bool()
 {
   DBUG_ASSERT(fixed);
-  bool tmp= (*ref)->val_bool_result();
+  bool tmp= outer_ref ? (*ref)->val_bool() : (*ref)->val_bool_result();
   null_value= (*ref)->null_value;
   return tmp;
 }
@@ -5231,7 +5290,7 @@
 String *Item_ref::val_str(String* tmp)
 {
   DBUG_ASSERT(fixed);
-  tmp=(*ref)->str_result(tmp);
+  tmp= outer_ref ? (*ref)->val_str(tmp) : (*ref)->str_result(tmp);
   null_value=(*ref)->null_value;
   return tmp;
 }
@@ -5252,7 +5311,8 @@
 
 my_decimal *Item_ref::val_decimal(my_decimal *decimal_value)
 {
-  my_decimal *val= (*ref)->val_decimal_result(decimal_value);
+  my_decimal *val= outer_ref ? (*ref)->val_decimal(decimal_value) :
+                   (*ref)->val_decimal_result(decimal_value);
   null_value= (*ref)->null_value;
   return val;
 }

--- 1.217/sql/item.h	2007-01-13 00:43:23 +03:00
+++ 1.218/sql/item.h	2007-01-31 00:18:34 +03:00
@@ -1825,11 +1825,12 @@
   enum Ref_Type { REF, DIRECT_REF, VIEW_REF };
   Field *result_field;			 /* Save result here */
   Item **ref;
+  bool outer_ref;
   Item_ref(Name_resolution_context *context_arg,
            const char *db_arg, const char *table_name_arg,
            const char *field_name_arg)
     :Item_ident(context_arg, db_arg, table_name_arg, field_name_arg),
-     result_field(0), ref(0) {}
+     result_field(0), ref(0), outer_ref(0) {}
   /*
     This constructor is used in two scenarios:
     A) *item = NULL
@@ -1849,7 +1850,8 @@
 
   /* Constructor need to process subselect with temporary tables (see Item) */
   Item_ref(THD *thd, Item_ref *item)
-    :Item_ident(thd, item), result_field(item->result_field), ref(item->ref) {}
+    :Item_ident(thd, item), result_field(item->result_field), ref(item->ref),
+    outer_ref(item->outer_ref) {}
   enum Type type() const		{ return REF_ITEM; }
   bool eq(const Item *item, bool binary_cmp) const
   { 
@@ -1883,7 +1885,9 @@
                           (*ref)->get_tmp_table_item(thd));
   }
   table_map used_tables() const		
-  { 
+  {
+    if (outer_ref)
+      return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
@@ -1891,7 +1895,10 @@
   bool is_result_field() { return 1; }
   void save_in_result_field(bool no_conversions)
   {
-    (*ref)->save_in_field(result_field, no_conversions);
+    if (outer_ref)
+      (*ref)->save_org_in_field(result_field);
+    else
+      (*ref)->save_in_field(result_field, no_conversions);
   }
   Item *real_item()
   {

--- 1.428/sql/mysql_priv.h	2007-01-11 23:17:39 +03:00
+++ 1.429/sql/mysql_priv.h	2007-01-31 00:06:40 +03:00
@@ -739,6 +739,8 @@
 int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 		List<Item> &fields, List<Item> &all_fields, ORDER *order,
 		bool *hidden_group_fields);
+bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                   Item **ref_pointer_array);
 
 bool handle_select(THD *thd, LEX *lex, select_result *result,
                    ulong setup_tables_done_option);

--- 1.189/sql/sql_delete.cc	2007-01-12 16:40:31 +03:00
+++ 1.190/sql/sql_delete.cc	2007-01-31 00:07:22 +03:00
@@ -349,6 +349,7 @@
   Item *fake_conds= 0;
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   DBUG_ENTER("mysql_prepare_delete");
+  List<Item> all_fields;
 
   thd->lex->allow_sum_func= 0;
   if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
@@ -372,6 +373,11 @@
       DBUG_RETURN(TRUE);
     }
   }
+
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
+
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }

--- 1.210/sql/sql_lex.cc	2007-01-11 23:17:40 +03:00
+++ 1.211/sql/sql_lex.cc	2007-01-31 00:13:02 +03:00
@@ -1182,6 +1182,8 @@
   is_correlated= 0;
   cur_pos_in_select_list= UNDEF_POS;
   non_agg_fields.empty();
+  inner_fields_list.empty();
+  inner_refs_list.empty();
 }
 
 /*

--- 1.236/sql/sql_lex.h	2007-01-11 23:17:40 +03:00
+++ 1.237/sql/sql_lex.h	2007-01-31 00:11:04 +03:00
@@ -548,7 +548,10 @@
   bool  braces;   	/* SELECT ... UNION (SELECT ... ) <- this braces */
   /* TRUE when having fix field called in processing of this SELECT */
   bool having_fix_field;
-
+  /* List of references to fields referenced from inner selects */
+  List<Item_ref> inner_refs_list;
+  /* List of fields referenced from inner selects */
+  List<Item_field> inner_fields_list;
   /* Number of Item_sum-derived objects in this SELECT */
   uint n_sum_items;
   /* Number of Item_sum-derived objects in children and descendant SELECTs */

--- 1.480/sql/sql_select.cc	2007-01-15 22:40:19 +03:00
+++ 1.481/sql/sql_select.cc	2007-01-31 01:06:12 +03:00
@@ -269,6 +269,72 @@
 
 
 /*
+  Fix fields referenced from inner selects.
+
+  SYNOPSIS
+    fix_inner_fields()
+    thd               Thread handle
+    all_fields        List of all fields used in select
+    select            Current select
+    ref_pointer_array Array of references to Items used in current select
+
+  DESCRIPTION
+    Fix fields referenced from inner selects and references (Item_ref objects)
+    to these fields. Each field is fixed as a usual hidden field of the current
+    select - it is added to the all_fields list and the pointer to it is saved
+    in the ref_pointer_array if latter is provided.
+    After a field gets fixed we proceed to fixing references (Item_ref objects)
+    to that field. If the ref_pointer_array is provided then Item_ref objects
+    is set to reference element in that array with the pointer to the field.
+
+  RETURN
+    TRUE  an error occur
+    FALSE ok
+*/
+
+bool
+fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                 Item **ref_pointer_array)
+{
+  Item_field *item;
+  List_iterator<Item_ref> ref_it(select->inner_refs_list);
+  List_iterator<Item_field> fld_it(select->inner_fields_list);
+  while ((item= fld_it++))
+  {
+    Item_ref *ref;
+    Item_field *new_item;
+    int el= all_fields.elements;
+    item->fixed= 0;
+    item->field= 0;
+    item->context= &select->context;
+    if (item->fix_fields(thd, (Item**)fld_it.ref()) ||
+        (new_item= *(fld_it.ref()))->check_cols(1))
+      return TRUE;
+    thd->used_tables|= item->used_tables();
+    /* save field in the select list of current select*/
+    if (ref_pointer_array)
+      ref_pointer_array[el]= (Item*)new_item;
+    all_fields.push_front((Item*)new_item);
+    ref_it.rewind();
+    /*
+      reset all Item_ref item which references this field to the new item
+      reference
+    */
+    while ((ref= ref_it++))
+    {
+      if (*ref->ref == item)
+      {
+        if (ref_pointer_array)
+          ref->ref= ref_pointer_array + el;
+        if (!ref->fixed && ref->fix_fields(thd, 0))
+          return TRUE;
+      }
+    }
+  }
+  return FALSE;
+}
+
+/*
   Function to setup clauses without sum functions
 */
 inline int setup_without_group(THD *thd, Item **ref_pointer_array,
@@ -395,6 +461,10 @@
   if (having && having->with_sum_func)
     having->split_sum_func2(thd, ref_pointer_array, all_fields,
                             &having, TRUE);
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, ref_pointer_array))
+    DBUG_RETURN(-1);
+
   if (select_lex->inner_sum_func_list)
   {
     Item_sum *end=select_lex->inner_sum_func_list;
@@ -5096,13 +5166,15 @@
 				    key_part->length,
 				    keyuse->val);
   }
-  else if (keyuse->val->type() == Item::FIELD_ITEM)
+  else if (keyuse->val->type() == Item::FIELD_ITEM ||
+           (keyuse->val->type() == Item::REF_ITEM &&
+            ((Item_ref*)keyuse->val)->outer_ref) )
     return new store_key_field(thd,
 			       key_part->field,
 			       key_buff + maybe_null,
 			       maybe_null ? key_buff : 0,
 			       key_part->length,
-			       ((Item_field*) keyuse->val)->field,
+			       ((Item_field*) keyuse->val->real_item())->field,
 			       keyuse->val->full_name());
   return new store_key_item(thd,
 			    key_part->field,

--- 1.207/sql/sql_update.cc	2006-12-30 23:02:07 +03:00
+++ 1.208/sql/sql_update.cc	2007-01-31 00:14:37 +03:00
@@ -136,6 +136,7 @@
   READ_RECORD	info;
   SELECT_LEX    *select_lex= &thd->lex->select_lex;
   bool need_reopen;
+  List<Item> all_fields;
   DBUG_ENTER("mysql_update");
 
   LINT_INIT(timestamp_query_id);
@@ -227,6 +228,10 @@
     free_underlaid_joins(thd, select_lex);
     DBUG_RETURN(1);				/* purecov: inspected */
   }
+
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
 
   if (conds)
   {

--- 1.136/sql/sql_union.cc	2007-01-11 23:17:41 +03:00
+++ 1.137/sql/sql_union.cc	2007-01-31 00:12:23 +03:00
@@ -733,6 +733,8 @@
     error= (bool) ((uint) error | (uint) lex_unit->cleanup());
   }
   non_agg_fields.empty();
+  inner_fields_list.empty();
+  inner_refs_list.empty();
   DBUG_RETURN(error);
 }
 

--- 1.167/mysql-test/r/subselect.result	2006-12-12 22:53:07 +03:00
+++ 1.168/mysql-test/r/subselect.result	2007-01-30 23:54:23 +03:00
@@ -224,7 +224,7 @@
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 Warnings:
-Note	1276	Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
 Note	1003	select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
 select * from t3 where exists (select * from t2 where t2.b=t3.a);
 a
@@ -313,8 +313,8 @@
 3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1276	Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
-Note	1276	Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
 Note	1003	select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 ERROR 21000: Subquery returns more than 1 row
@@ -330,9 +330,9 @@
 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t6	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	Using index
+2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	t6.clinic_uq	1	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
 ERROR 23000: Column 'a' in field list is ambiguous
@@ -868,7 +868,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 Warnings:
-Note	1276	Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1249	Select 2 was reduced during optimization
 Note	1003	select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
 select (select a+1) from t1;
@@ -1741,9 +1741,9 @@
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	Using where
-2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	tt.id	1	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))
 insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
 create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
@@ -2279,7 +2279,7 @@
 1	PRIMARY	up	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 Warnings:
-Note	1276	Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
 drop table t1;
 CREATE TABLE t1 (t1_a int);
@@ -3605,3 +3605,12 @@
 COUNT(*)
 3000
 DROP TABLE t1,t2;
+create table t1(f1 int);
+create table t2(f2 int, f21 int);
+insert into t1 values (1),(1),(2),(2);
+insert into t2 values (1,1), (2,2);
+select ((select f2 from t2 where f21=f1 limit 1) * count(f1)) as sq from t1 group by f1;
+sq
+2
+4
+drop table t1,t2;

--- 1.137/mysql-test/t/subselect.test	2006-12-15 23:06:18 +03:00
+++ 1.138/mysql-test/t/subselect.test	2007-01-30 23:55:26 +03:00
@@ -2508,3 +2508,13 @@
           FROM t1) t;
 
 DROP TABLE t1,t2;
+
+#
+# Bug#23800: Correlated sub query returning incorrect results when operated upon
+#
+create table t1(f1 int);
+create table t2(f2 int, f21 int);
+insert into t1 values (1),(1),(2),(2);
+insert into t2 values (1,1), (2,2);
+select ((select f2 from t2 where f21=f1 limit 1) * count(f1)) as sq from t1 group by f1;
+drop table t1,t2;

--- 1.1/mysql-test/r/subselect3.result	2006-10-31 20:42:45 +03:00
+++ 1.2/mysql-test/r/subselect3.result	2007-01-30 23:54:42 +03:00
@@ -30,7 +30,7 @@
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) 
 from t1 where oref=t2.oref group by grp)` from `test`.`t2`
 explain extended
@@ -40,7 +40,7 @@
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
 create table t3 (a int);
 insert into t3 values (NULL), (NULL);
@@ -83,7 +83,7 @@
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
 2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using index; Using where
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2`
 flush status;
 select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
@@ -148,6 +148,6 @@
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	4	Using where
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.b	1	Using where
 Warnings:
-Note	1276	Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
 drop table t1, t2, t3;
Thread
bk commit into 5.0 tree (evgen:1.2386) BUG#23800eugene30 Jan