MySQL Bugs: #19792: With ORDER BY RAND() updated variable values are not visible in query results

Bug #19792	With ORDER BY RAND() updated variable values are not visible in query results
Submitted:	13 May 2006 3:47	Modified:	2 Aug 2006 19:34
Reporter:	Gerardo Narvaja	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.21 and previous	OS:	Windows (Windows XP)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
Set the initial conditions:

CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT);
INSERT INTO t1 VALUES (rand(1) * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000), 
                                   (rand() * 10000, rand() * 10000);
SET @time_step = 1;

The regular query produces the following output:

SELECT t1.f1, rand() rn, @tstep := @tstep + 1 as ts, now() tn, date_add(now(), INTERVAL @tstep second) as d FROM t1;

The result should similar to:

+------+-------------------+------+---------------------+---------------------+
| f1   | rn                | ts   | tn                  | d                   |
+------+-------------------+------+---------------------+---------------------+
| 1530 |  0.75213940139128 |  134 | 2006-05-12 20:42:36 | 2006-05-12 20:44:50 |
| 1543 |  0.27005456785676 |  135 | 2006-05-12 20:42:36 | 2006-05-12 20:44:51 |

If you add "ORDER by rn" at the end of the select statement: 

SELECT t1.f1, rand() rn, @tstep := @tstep + 1 as ts, now() tn, date_add(now(), INTERVAL @tstep second) as d FROM t1 ORDER by rn;

The result now becomes:

+------+---------------------+------+---------------------+---------------------+
| f1   | rn                  | ts   | tn                  | d                   |
+------+---------------------+------+---------------------+---------------------+
| 5181 | 0.00786400121437758 |  142 | 2006-05-12 20:45:36 | 2006-05-12 20:47:57 |
| 4054 |   0.366927726535991 |  143 | 2006-05-12 20:45:36 | 2006-05-12 20:47:57 |
| 4671 |   0.421729430017741 |  144 | 2006-05-12 20:45:36 | 2006-05-12 20:47:57 |

The last column remains the same, no matter how many rows are there.

How to repeat:
See above

Verified, attaching simplified test case as this
is just about variables and ORDER BY RAND()
and not specific to DATE_ADD()

See also bug #16861

mysqltest test case

Attachment: bug19792.tar.gz (application/x-gzip, text), 869 bytes.

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/9552

This is an example of a statement that assigns and uses a variable in different parts of the same statement.

Please see the user variable MySQL Manual Entry (http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) where it states:

"
The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part the same statement. You might get the results you expect, but this is not guaranteed.
"

Also, please see bug# 1739 for more information.