title: Mysql order by rand and setting user variable
body: <p>Can anyone explain what is happening with the following SQL?  I understand order by rand is not a good practice, but I am interested in an explanation of why this is happening</p>
  
  <pre><code>create database test_views_rand;
  use test_views_rand;
  create table test_table (number int);
  
  insert into test_table  values (1), (2), (3), (4);
  
  select @test := number from test_table order by rand() limit 1;
  # selects a random number from 1 - 4
  select @test;
  # Always gives 4, regardless of what the actual number was in the select query
  </code></pre>
  
  <p>Just in case it isn't clear, the first select statement is random as expected. The second select statement always gives 4, regardless of the outcome of the first select statement.</p>