random results in logical operations on rand() generated numbers
----------------------------------------------------------------
Key: CORE-4362
URL: http://tracker.firebirdsql.org/browse/CORE-4362
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.2 Update 1
Environment: Linux
Reporter: Edivaldo de Araújo Pereira
Priority: Trivial
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?
Example:
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);
The query
select lx.*,vlr*2 as dbr,vlr*3 as trp from (
select lixo.*,cast(cod+(random()*20) as float) as vlr from lixo
) lx
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
) lx
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
