MySQL Bugs: #67895: Auto increment a value on the fly with RANDOM error

Bug #67895	Auto increment a value on the fly with RANDOM error
Submitted:	13 Dec 2012 13:40	Modified:	13 Dec 2012 18:55
Reporter:	Denis Zhadan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
increment a value working without "order by RAND()" - correct 
id: 1, 2, 3

But with "order by RAND()"
id: 495, 1882, 2674

BTW: in mysql version 5.0.96 - working correct.

How to repeat:
mysql> set @NRT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @NRT,  @NRT := @NRT+1, @NRT from TEST_DATA TD limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    0 |              1 |    1 |
|    1 |              2 |    2 |
|    2 |              3 |    3 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT,  @NRT := @NRT+1, @NRT from TEST_DATA TD order by RAND() limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|  494 |            495 |  495 |
| 1881 |           1882 | 1882 |
| 2673 |           2674 | 2674 |
+------+----------------+------+

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.5/en/user-variables.html:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

please verify: MySQL 5.0.96-community

mysql> set @NRT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    0 |              1 |    1 |
|    1 |              2 |    2 |
|    2 |              3 |    3 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by ID limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    3 |              4 |    4 |
|    4 |              5 |    5 |
|    5 |              6 |    6 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by ID desc limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    6 |              7 |    7 |
|    7 |              8 |    8 |
|    8 |              9 |    9 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by rand() limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    9 |             10 |   10 |
|   10 |             11 |   11 |
|   11 |             12 |   12 |
+------+----------------+------+
3 rows in set (0.00 sec)

value in @NRT with "order by rand()" - correct

and try test with MySQL 5.5.27

mysql> set @NRT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    0 |              1 |    1 |
|    1 |              2 |    2 |
|    2 |              3 |    3 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by ID limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    3 |              4 |    4 |
|    4 |              5 |    5 |
|    5 |              6 |    6 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by ID desc limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|    6 |              7 |    7 |
|    7 |              8 |    8 |
|    8 |              9 |    9 |
+------+----------------+------+
3 rows in set (0.00 sec)

mysql> select @NRT, @NRT := @NRT+1, @NRT  from TEST_DATA TD order by rand() limit 3;
+------+----------------+------+
| @NRT | @NRT := @NRT+1 | @NRT |
+------+----------------+------+
|  908 |            909 |  909 |
|  791 |            792 |  792 |
|  109 |            110 |  110 |
+------+----------------+------+
3 rows in set (0.00 sec)

value in @NRT is NOT correct.

Thank you for the feedback.

I did see 5.0 output.

But "You might get the results you expect, but this is not guaranteed. " means we don't guarantee consistency of results for such queries.

Unfortunately it creates problems for upgrade from version 5.0 on 5.5. 
As earlier our program worked correctly.

Thank you for the feedback.

But "You might get the results you expect, but this is not guaranteed. " means we don't guarantee consistency of results for such queries even if you upgrade from version 5.0. Same text existed in 5.0 user manual as well, so you were warned.

This is not MySQL bug.

Well, suppose, that it is not a bug.

As I understand, You are the professional on MySQL.

Can You explain where mySQL server take these values when using "order by rand() "? summarizes with primary key, is it?