MySQL Bugs: #8654: corelated subquery refering to rand() in outer query returns wrong result (OR CR

Bug #8654	corelated subquery refering to rand() in outer query returns wrong result (OR CR
Submitted:	21 Feb 2005 16:49	Modified:	10 Jul 2005 15:57
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
see how to repeat, in both querys the values returned by the subquery do not match the condition

How to repeat:
 create table r2 (a int, b int);
 insert into r2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11);

 select a, left(rand(1),4) x, (select a/10 from r2 where a/10 = x ) from  r2 ;
+------+------+---------------------------------------+
| a    | x    | (select a/10 from r2 where a/10 = x ) |
+------+------+---------------------------------------+
|    1 | 0.40 |                                  NULL |
|    2 | 0.81 |                                  0.20 |
|    3 | 0.19 |                                  NULL |
|    4 | 0.71 |                                  NULL |
|    5 | 0.74 |                                  NULL |
|    6 | 0.56 |                                  0.10 |
|    7 | 0.11 |                                  NULL |
|    8 | 0.52 |                                  NULL |
|    9 | 0.24 |                                  NULL |
|   10 | 0.74 |                                  NULL |
|   11 | 0.90 |                                  NULL |
+------+------+---------------------------------------+
11 rows in set (0.00 sec)

select a, rand(1) x, (select concat(min(a/10),' - ',max(a/10)) from r2 where a/10 < x ) from  r2 ;
+------+------------------+--------------------------------------------------------------------+
| a    | x                | (select concat(min(a/10),' - ',max(a/10)) from r2 where a/10 < x ) |
+------+------------------+--------------------------------------------------------------------+
|    1 | 0.40540353712198 | 0.10 - 0.60                                                        |
|    2 | 0.81216494907827 | 0.10 - 0.60                                                        |
|    3 | 0.19808567520053 | 0.10 - 0.80                                                        |
|    4 | 0.71504499364183 | 0.10 - 0.70                                                        |
|    5 | 0.74756920034771 | 0.10 - 0.60                                                        |
|    6 | 0.56570853904421 | 0.10 - 0.80                                                        |
|    7 | 0.11803021013553 | 0.10 - 0.50                                                        |
|    8 |  0.5235725860331 | 0.10 - 0.80                                                        |
|    9 |  0.2496939667032 | 0.20 - 0.70                                                        |
|   10 | 0.74280610004701 | 0.10 - 0.80                                                        |
|   11 | 0.90282696662734 | 0.10 - 0.80                                                        |
+------+------------------+--------------------------------------------------------------------+
11 rows in set (0.01 sec)

# line 9 is somehow outstandig, if the server thought 0.2 to be smaller, why would 0.1 not be?

Suggested fix:
-

also look at these, on the same table as above, cut of, for simplification

 select a, rand(1), rand(1) x, rand(1) from  r2 ;
+------+-------------------+-------------------+-------------------+
| a    | rand(1)           | x                 | rand(1)           |
+------+-------------------+-------------------+-------------------+
|    1 |  0.40540353712198 |  0.40540353712198 |  0.40540353712198 |
|    2 |  0.87161418038571 |  0.87161418038571 |  0.87161418038571 |
|    3 |  0.14186032129625 |  0.14186032129625 |  0.14186032129625 |
rand(1) stays the same for all of each row (correct)

 select a, rand(1), rand(1) x, (select rand(1)  ), rand(1) from  r2 ;
+------+-------------------+-------------------+--------------------+-------------------+
| a    | rand(1)           | x                 | (select rand(1)  ) | rand(1)           |
+------+-------------------+-------------------+--------------------+-------------------+
|    1 |  0.40540353712198 |  0.40540353712198 |   0.40540353712198 |  0.40540353712198 |
|    2 |  0.87161418038571 |  0.87161418038571 |   0.87161418038571 |  0.87161418038571 |
|    3 |  0.14186032129625 |  0.14186032129625 |   0.14186032129625 |  0.14186032129625 |
|    4 | 0.094459096057768 | 0.094459096057768 |  0.094459096057768 | 0.094459096057768 |
#again the same, again correct

# now with reference to the alias, the value also changes in the outer query within one row
#from row 2 onwards
select a, rand(1), rand(1) x, (select x  ), rand(1) from  r2 ;
+------+-------------------+-------------------+-------------------+-------------------+
| a    | rand(1)           | x                 | (select x  )      | rand(1)           |
+------+-------------------+-------------------+-------------------+-------------------+
|    1 |  0.40540353712198 |  0.40540353712198 |  0.87161418038571 |  0.40540353712198 |
|    2 |  0.87161418038571 |  0.14186032129625 | 0.094459096057768 |  0.87161418038571 |
|    3 |  0.14186032129625 | 0.046714547133739 |  0.95019547822903 |  0.14186032129625 |
|    4 | 0.094459096057768 |   0.6108337804776 |  0.20358249843454 | 0.094459096057768 |

and this one causes a crash

 select a, rand(1) x,  (select group_concat(x) from r2    ) from  r2 ;

After bug 8216, I believe this is the same as the bug, with random above

select a y, (select max(b) from r2 where a< y ) x,  (select group_concat(x) from r2    ) from  r2 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query

sorry the last comment, is a seperate issue, nothing to to with refering to rand or subquery.

added the last one as bug #8656

Thank you for bugreport! It is not a bug but unsupported behaviour. In http://dev.mysql.com/doc/mysql/en/mathematical-functions.html we have following:
"You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times." Subqueries evaluate reference on rand several times, too. Of course it have to be explained well in documentation.

(and I checked it with group_concat - it do not carash server any more as far as bug#8656 is fixed)

Should have been closed as Not A Bug.