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-28 17:21:39-05:00, iggy@stripped +4 -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-28 17:21:37-05:00,
iggy@stripped +59 -0
    Bug#20836 Selecting into variables results in wrong results being returned
    - Added results

  mysql-test/t/distinct.test@stripped, 2006-11-28 17:21:37-05:00,
iggy@stripped +65 -0
    Bug#20836 Selecting into variables results in wrong results being returned
    - Added various Selects that use the INTO statement and a temp table.
    - Added Select Into Outfile variant tests also.

  sql/sql_class.cc@stripped, 2006-11-28 17:21:37-05:00, iggy@stripped +22
-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.

  sql/sql_class.h@stripped, 2006-11-28 17:21:37-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.211/sql/sql_class.cc	2006-11-28 17:21:43 -05:00
+++ 1.212/sql/sql_class.cc	2006-11-28 17:21:43 -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,21 @@ 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= 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-28 17:21:43 -05:00
+++ 1.291/sql/sql_class.h	2006-11-28 17:21:43 -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-28 17:21:43 -05:00
+++ 1.35/mysql-test/r/distinct.result	2006-11-28 17:21:43 -05:00
@@ -566,3 +566,62 @@ 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 IF EXISTS t2;
+CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20)
+default NULL);
+SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE 
+'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE';
+LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2;
+SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE 
+'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE';
+LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2;
+SELECT @v19, @v20;
+@v19	@v20
+2	APPLE
+SELECT * FROM t2;
+fruit_id	fruit_name
+2	APPLE
+2	APPLE
+DROP TABLE t1;
+DROP TABLE t2;

--- 1.19/mysql-test/t/distinct.test	2006-11-28 17:21:43 -05:00
+++ 1.20/mysql-test/t/distinct.test	2006-11-28 17:21:43 -05:00
@@ -389,4 +389,69 @@ 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;
+
+--disable_warnings
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20)
+default NULL);
+
+SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE 
+'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE';
+LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2;
+--exec rm $MYSQL_TEST_DIR/var/tmp/data1.tmp
+
+SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE 
+'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE';
+LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2;
+--exec rm $MYSQL_TEST_DIR/var/tmp/data2.tmp
+
+SELECT @v19, @v20;
+SELECT * FROM t2;
+
+DROP TABLE t1;
+DROP TABLE t2;
+
 # End of 4.1 tests