From: Date: November 28 2006 11:21pm Subject: bk commit into 4.1 tree (iggy:1.2555) BUG#20836 List-Archive: http://lists.mysql.com/commits/16051 X-Bug: 20836 Message-Id: <20061128222143.5154B17750D@ignatz42.dyndns.org> 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 &list, SELECT_LEX_UNIT *u) { - List_iterator_fast li(list); - List_iterator_fast 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 &items) { - List_iterator_fast li(vars); - Item_func_set_user_var *xx; + List_iterator_fast var_li(var_list); + List_iterator 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(Listcheck(); - 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 var_list; - List 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 &list, SELECT_LEX_UNIT *u); bool send_data(List &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