New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
random results in logical operations on rand() generated numbers [CORE4362] #4684
Comments
Commented by: @dyemanov This is an implementation detail. Firebird does not materialize simple expressions. |
Commented by: @livius2 Hi Dmitry e.g. you need to have result + some calculation on it and you got 2 different values also from derived tables result: |
Commented by: @pavel-zotov > plan for change this and materialize expressions result? Windowed functions can help in this: select user_login, user_pswd, 'alter user ' || trim(user_login) || ' password ''' || user_pswd || '''' as final_expr |
Commented by: @livius2 Pavel - good workaround :) interesting that below query does not work but plan is the same as second query(only sort have difference in record length)
ORDER BY Select Expression -------------------------------------------------------------
ORDER BY Select Expression it looks like it must be |
Commented by: @pavel-zotov Found one more query when we can prevent from repeating evaluation of expression (suitable for 2.5). I show it here only for those who want to deeper understand how FB works, but this query can not be considered as solution, at all. 1) it supposes that number of users will not be more than 1023; Please note, that we HAVE to mention "dummy_result" column in top-level select list, otherwise result of "final_expr" will be null. select Result: USER_LOGIN RDB_DATABASE USER_LOGIN RDB_FIELDS PS. |
Submitted by: Edivaldo de Araújo Pereira (edivaldopereira)
Votes: 1
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.
The text was updated successfully, but these errors were encountered: