Every logical operation executed on numbers generated by rand() in a "virtual" table (aliased query?) produces random results. It looks like the rand() function is called each time a generated number is referenced. This is not the behaviour of other DBMS, like MySQL or Postgesql: once generated, there is no more random behaviour of the numbers.
Is this a bug or by design?
create table lixo(cod integer, nme char(5), rnd float);
insert into lixo values(1,'um',random()*100);
insert into lixo values(2,'dois',random()*100);
insert into lixo values(3,'três',random()*100);
select lx.*,vlr*2 as dbr,vlr*3 as trp from (
select lixo.*,cast(cod+(random()*20) as float) as vlr from lixo
order by vlr
produces random numbers for dbr and trp, with no mathematical relation to vlr as expected, and the order is random too; but the query
select lx.*,rnd*2 as dbr,rnd*3 as trp from (
select lixo.*,cast(cod+(rand()*20) as float) as vlr from lixo
order by rnd
produces the expected results: dbr is the double of vlr, trp is the triple and the order is rnd crescent. Note that rnd is a number generated by rand(), but once it is stored in the table, there is no more random behaviour. The use of cast() makes no difference, it was just a try...
I tried a lot of operations over rand() generated numbers, all of them with random results: comparisons in a where clause, range in between, arithmetic operations, etc.