MySQL Bugs: #16861: User variables and ORDER BY RAND() LIMIT 1 product unexpected result

Bug #16861	User variables and ORDER BY RAND() LIMIT 1 product unexpected result
Submitted:	27 Jan 2006 23:17	Modified:	16 Jan 2007 7:14
Reporter:	Nathan Tanner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (FC4)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
If you try to use ORDER BY RAND() LIMIT 1 to set a user variable to a random value from a table, you get the last value in the table as if no ORDER BY had been performed.

How to repeat:
create table test (id int not null auto_increment primary key);

insert into test values (null),(null),(null),(null),(null),(null),(null),(null);

select @random_id:=id from test order by rand() limit 1;

select @random_id;

Suggested fix:
I would expect both SELECT statements to return the same value: 

test> create table test (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

test> insert into test values (null),(null),(null),(null),(null),(null),(null),(null);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

test> select @random_id:=id from test order by rand() limit 1;
+----------------+
| @random_id:=id |
+----------------+
|              6 |
+----------------+
1 row in set (0.01 sec)

test> select @random_id;
+------------+
| @random_id |
+------------+
| 8          |
+------------+
1 row in set (0.00 sec)

Instead, @random_id will always be set to the last id in the table, no matter what is returned by select @random_id:=id from test order by rand() limit 1;

Thanks for your bug report

mysql> select @random_id:=id from test order by rand() limit 1;
+----------------+
| @random_id:=id |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> select @random_id;
+------------+
| @random_id |
+------------+
| 8          |
+------------+
1 row in set (0.00 sec)

mysql>

See also bug #19792

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10606

ChangeSet@1.2251, 2006-08-18 02:18:30+04:00, evgen@sunlight.local +7 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  Sorting by RAND() uses a temporary table in order to get a correct results.
  User defined variable was set during filling the temporary table and later
  on it is substituted with its value from the temporary table. Due to this
  it contains the last value stored in the temporary table.
  
  Now if the result_field is set for the Item_func_set_user_var object it 
  updates variable from the result_field value when being sent to a client.
  
  The Item_func_set_user_var::check() now accepts a use_result_field
  parameter. Depending on its value the result_field or the args[0] is used
  to get current value.

Fixed in 5.0.25

Available in 5.0.25.

Fixed in 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 & 5.1.12 changelogs.

The patch only fixes one manifestation of the problem, but doesn't fix the problem  itself.
E.g. when using an item in a different context, it still produces incorrect results. A test case follows:
mysql> create table t1(f1 int, f2 int);
Query OK, 0 rows affected (0.02 sec) 
mysql> insert into t1 values (1,2),(2,3),(3,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0 
mysql>  select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+----------+
| @var:=f2 |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec) 
mysql> select @var;
+------+
| @var |
+------+
| 3    |
+------+
1 row in set (0.00 sec) 
mysql> create table t2 select @var:=f2 from t1 group by f1 
c limit 1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0 
mysql> select * from t2;
+----------+
| @var:=f2 |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec) 
mysql> select @var;
+------+
| @var |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16538

ChangeSet@1.2320, 2006-12-06 21:37:16+03:00, evgen@moonbone.local +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  The Item::save_in_field() function is called from fill_record() to fill fields 
  with data for a new row in a CREATE TABLE ... SELECT statement.
  It calls val_xxx() methods in order to get values. Due to this
  Item_func_set_user_var object returns values from the original table, not from
  a temporary one.
  
  The save_in_field() member function is added to the Item_func_set_user_var
  class. It detects when the result field should be used and uses it in such
  cases.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16810

ChangeSet@1.2320, 2006-12-12 03:48:12+03:00, evgen@moonbone.local +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  Sorting by RAND() uses a temporary table in order to get a correct results.
  User defined variable was set during filling the temporary table and later
  on it is substituted for its value from the temporary table. Due to this
  it contains the last value stored in the temporary table.
  
  Now if the result_field is set for the Item_func_set_user_var object it 
  updates variable from the result_field value when being sent to a client.
  
  The Item_func_set_user_var::check() now accepts a use_result_field
  parameter. Depending on it the result_field or the arg[0] is used to get
  current value.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16987

ChangeSet@1.2320, 2006-12-14 23:40:50+03:00, evgen@moonbone.local +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  The Item::save_in_field() function is called from fill_record() to the 
  new row with a data while the CREATE TABLE ... SELECT statement.
  Item::save_in_field() calls val_xxx() methods in order to get values.
  val_xxx() methods do not take into account the result field. Due to this
  Item_func_set_user_var object returns values from the original table, 
  not from a temporary one.
  
  The save_in_field() member function is added to the Item_func_set_user_var
  class. It detects when the result field should be used and properly updates
  the value of the user variable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17071

ChangeSet@1.2320, 2006-12-15 21:03:00+03:00, evgen@moonbone.local +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  The Item::save_in_field() function is called from fill_record() to the 
  new row with data while execution of the CREATE TABLE ... SELECT statement.
  Item::save_in_field() calls val_xxx() methods in order to get values.
  val_xxx() methods do not take into account the result field. Due to this
  Item_func_set_user_var::val_xxx() methods returns values from the original
  table, not from the temporary one.
  
  The save_in_field() member function is added to the Item_func_set_user_var
  class. It detects whether the result field should be used and properly updates
  the value of the user variable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17809

ChangeSet@1.2371, 2007-01-09 23:24:56+03:00, evgen@moonbone.local +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  The Item::save_in_field() function is called from fill_record() to fill the 
  new row with data while execution of the CREATE TABLE ... SELECT statement.
  Item::save_in_field() calls val_xxx() methods in order to get values.
  val_xxx() methods do not take into account the result field. Due to this
  Item_func_set_user_var::val_xxx() methods returns values from the original
  table, not from the temporary one.
  
  The save_in_field() member function is added to the Item_func_set_user_var
  class. It detects whether the result field should be used and properly updates
  the value of the user variable.

Fixed in 5.0.34, 5.1.15-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Updated fix documented in 5.0.34 and 5.1.15 changelogs.