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