bk commit into 4.1 tree (iggy:1.2555) BUG#20836

[Ignacio Galarza]

Below is the list of changes that have just been committed into a local
4.1 repository of iggy. When iggy 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, 2006-11-09 19:00:49-05:00, iggy@stripped +5 -0
  Bug#20836 Selecting into variables results in wrong results being returned
  This error is displayed anytime the SELECT statement needs a temp table to
  return correct results because the object (select_dumpvar) that represents
  variables named in the INTO clause stored the results before the temp
  table was considered.  The problem was fixed by creating the necessary
  Item_func_set_user_var objects once the correct data is ready.

  mysql-test/r/distinct.result@stripped, 2006-11-09 19:00:47-05:00,
iggy@stripped +35 -0
    Bug#20836 Selecting into variables results in wrong results being returned
    - Added results

  mysql-test/t/distinct.test@stripped, 2006-11-09 19:00:47-05:00,
iggy@stripped +37 -0
    Bug#20836 Selecting into variables results in wrong results being returned
    - Added various Selects that use the INTO statement and a temp table.

  sql/item_func.h@stripped, 2006-11-09 19:00:47-05:00, iggy@stripped +2 -1
    Bug#20836 Selecting into variables results in wrong results being returned
    - Backported functionality from 5.0 added for bug#16861.

  sql/sql_class.cc@stripped, 2006-11-09 19:00:47-05:00, iggy@stripped +30
-26
    Bug#20836 Selecting into variables results in wrong results being returned
    - The select_dumpvar variable created a Item_func_set_user_var too early
    and once set, it was not possible to change.  The Item_func_set_user_var
    is now created once the final results are available.  Special attention
    has been paid to Item_func_set_user_var Items (eg SELECT @v1 := column 
    INTO @v2) 

  sql/sql_class.h@stripped, 2006-11-09 19:00:47-05:00, iggy@stripped +1 -2
    Bug#20836 Selecting into variables results in wrong results being returned
    - Removed unnecessary object members.

# 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:	iggy
# Host:	rolltop.ignatz42.dyndns.org
# Root:	/mnt/storeage/bug20836/my41-bug20836

--- 1.134/sql/item_func.h	2006-11-09 19:00:53 -05:00
+++ 1.135/sql/item_func.h	2006-11-09 19:00:53 -05:00
@@ -51,7 +51,7 @@ public:
 		  SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC,
 		  SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING,
 		  SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN,
-		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC};
+		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC, SUSERVAR_FUNC};
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL };
   enum Type type() const { return FUNC_ITEM; }
   virtual enum Functype functype() const   { return UNKNOWN_FUNC; }
@@ -958,6 +958,7 @@ public:
   Item_func_set_user_var(LEX_STRING a,Item *b)
     :Item_func(b), cached_result_type(INT_RESULT), name(a)
   {}
+  enum Functype functype() const { return SUSERVAR_FUNC; }
   double val();
   longlong val_int();
   String *val_str(String *str);

--- 1.211/sql/sql_class.cc	2006-11-09 19:00:53 -05:00
+++ 1.212/sql/sql_class.cc	2006-11-09 19:00:53 -05:00
@@ -1372,37 +1372,20 @@ bool select_exists_subselect::send_data(
 
 int select_dumpvar::prepare(List<Item> &list, SELECT_LEX_UNIT *u)
 {
-  List_iterator_fast<Item> li(list);
-  List_iterator_fast<LEX_STRING> gl(var_list);
-  Item *item;
-  LEX_STRING *ls;
+  unit= u;
+  
   if (var_list.elements != list.elements)
   {
     my_error(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT, MYF(0));
     return 1;
   }
-  unit=u;
-  while ((item=li++))
-  {
-    ls= gl++;
-    Item_func_set_user_var *xx = new Item_func_set_user_var(*ls,item);
-    /*
-      Item_func_set_user_var can't substitute something else on its place =>
-      0 can be passed as last argument (reference on item)
-    */
-    xx->fix_fields(thd,(TABLE_LIST*) thd->lex->select_lex.table_list.first,
-		   0);
-    xx->fix_length_and_dec();
-    vars.push_back(xx);
-  }
   return 0;
 }
 
 
 void select_dumpvar::cleanup()
 {
-  vars.empty();
-  row_count=0;
+  row_count= 0;
 }
 
 
@@ -1744,12 +1727,14 @@ Statement_map::~Statement_map()
 
 bool select_dumpvar::send_data(List<Item> &items)
 {
-  List_iterator_fast<Item_func_set_user_var> li(vars);
-  Item_func_set_user_var *xx;
+  List_iterator_fast<LEX_STRING> var_li(var_list);
+  List_iterator<Item> it(items);
+  Item *item;
+  LEX_STRING *ls;
   DBUG_ENTER("send_data");
 
   if (unit->offset_limit_cnt)
-  {				          // Using limit offset,count
+  {						// using limit offset,count
     unit->offset_limit_cnt--;
     DBUG_RETURN(0);
   }
@@ -1758,10 +1743,29 @@ bool select_dumpvar::send_data(List<Item
     my_error(ER_TOO_MANY_ROWS, MYF(0));
     DBUG_RETURN(1);
   }
-  while ((xx=li++))
+  while ((ls= var_li++) && (item= it++))
   {
-    xx->check();
-    xx->update();
+    Item_func_set_user_var *suv;
+
+    if (item->type() == Item::FUNC_ITEM && ((Item_func*)item)->functype()
== Item_func::SUSERVAR_FUNC)
+    {        
+      Item_func_get_user_var *guv= new Item_func_get_user_var(((Item_func_set_user_var
*)item)->name);
+      suv= new Item_func_set_user_var(*ls, guv);      
+    }
+    else
+      suv= new Item_func_set_user_var(*ls, item);
+
+    /*
+    Item_func_set_user_var can't substitute something else on its
+    place => NULL may be passed as last argument (reference on
+    item) Item_func_set_user_var can't be fixed after creation, so
+    we do not check var->fixed
+    */
+
+    suv->fix_fields(thd, (TABLE_LIST *) thd->lex->select_lex.table_list.first,
+            0);
+    suv->check();
+    suv->update();  
   }
   DBUG_RETURN(0);
 }

--- 1.290/sql/sql_class.h	2006-11-09 19:00:53 -05:00
+++ 1.291/sql/sql_class.h	2006-11-09 19:00:53 -05:00
@@ -1573,8 +1573,7 @@ class select_dumpvar :public select_resu
   ha_rows row_count;
 public:
   List<LEX_STRING> var_list;
-  List<Item_func_set_user_var> vars;
-  select_dumpvar(void)  { var_list.empty(); vars.empty(); row_count=0;}
+  select_dumpvar()  { var_list.empty(); row_count= 0;}
   ~select_dumpvar() {}
   int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
   bool send_data(List<Item> &items);

--- 1.34/mysql-test/r/distinct.result	2006-11-09 19:00:53 -05:00
+++ 1.35/mysql-test/r/distinct.result	2006-11-09 19:00:53 -05:00
@@ -566,3 +566,38 @@ a	b
 3	2
 2	3
 DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) default
NULL);
+INSERT INTO t1 VALUES (1,1,'ORANGE');
+INSERT INTO t1 VALUES (2,2,'APPLE');
+INSERT INTO t1 VALUES (3,2,'APPLE');
+INSERT INTO t1 VALUES (4,3,'PEAR');
+SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v1, @v2;
+@v1	@v2
+2	APPLE
+SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, fruit_name
HAVING fruit_name = 'APPLE';
+SELECT @v3, @v4;
+@v3	@v4
+2	APPLE
+SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE fruit_name =
'APPLE';
+SELECT @v5, @v6, @v7, @v8;
+@v5	@v6	@v7	@v8
+2	APPLE	2	APPLE
+SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 WHERE
fruit_name = 'APPLE';
+SELECT @v5, @v6, @v7, @v8, @v9, @v10;
+@v5	@v6	@v7	@v8	@v9	@v10
+2	APPLE	2	APPLE	4	APPLEAPPLE
+SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO @v13, @v14
FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v11, @v12, @v13, @v14;
+@v11	@v12	@v13	@v14
+4	APPLEAPPLE	4	APPLEAPPLE
+SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v15, @v16;
+@v15	@v16
+4	APPLEAPPLE
+SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v17, @v18;
+@v17	@v18
+4	Bob
+DROP TABLE t1;

--- 1.19/mysql-test/t/distinct.test	2006-11-09 19:00:53 -05:00
+++ 1.20/mysql-test/t/distinct.test	2006-11-09 19:00:53 -05:00
@@ -389,4 +389,41 @@ explain SELECT DISTINCT a, b FROM t1 ORD
 SELECT DISTINCT a, b FROM t1 ORDER BY b;
 DROP TABLE t1;
 
+#
+#Bug #20836: Selecting into variables results in wrong results being returned
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) default
NULL);
+
+INSERT INTO t1 VALUES (1,1,'ORANGE');
+INSERT INTO t1 VALUES (2,2,'APPLE');
+INSERT INTO t1 VALUES (3,2,'APPLE');
+INSERT INTO t1 VALUES (4,3,'PEAR');
+
+SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v1, @v2;
+
+SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, fruit_name
HAVING fruit_name = 'APPLE';
+SELECT @v3, @v4;
+
+SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE fruit_name =
'APPLE';
+SELECT @v5, @v6, @v7, @v8;
+
+SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 WHERE
fruit_name = 'APPLE';
+SELECT @v5, @v6, @v7, @v8, @v9, @v10;
+
+SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO @v13, @v14
FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v11, @v12, @v13, @v14;
+
+SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v15, @v16;
+
+SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = 'APPLE';
+SELECT @v17, @v18;
+
+DROP TABLE t1;
+
 # End of 4.1 tests