Skip to content
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

Open
firebird-automations opened this issue Mar 11, 2014 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This is an implementation detail. Firebird does not materialize simple expressions.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Hi Dmitry
is there a plan for change this and materialize expressions result?

e.g. you need to have result + some calculation on it and you got 2 different values also from derived tables
SELECT
PASS
, 'ALTER USER ''' || trim(LOGIN) || ''' SET PASSWORD ''' || PASS || ''';'
FROM
(SELECT
REPLACE(UUID_TO_CHAR(gen_uuid()), '-', '') AS PASS
, R.RDB$RELATION_NAME AS LOGIN
FROM
RDB$RELATIONS R)
ORDER BY
LOGIN DESC

result:
PASS CONCATENATION
493B728B22CB4A19885A6E15F3F04A3C ALTER USER 'RDB$REF_CONSTRAINTS' SET PASSWORD 'FCEA77108AB3402A8798D35C2A6A56A3';

@firebird-automations
Copy link
Collaborator Author

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
from (
select user_login, user_pswd
,row_number()over() r
from (
select
replace(r.rdb$relation_name,'$','_') as user_login
,left(replace(uuid_to_char(gen_uuid()),'-',''),20) user_pswd
from rdb$relations r
rows 3
)
)

@firebird-automations
Copy link
Collaborator Author

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)
-------------------------------------------------------------
SELECT
PASS
, 'ALTER USER ''' || trim(LOGIN) || ''' SET PASSWORD ''' || PASS || ''';'
FROM
(SELECT
B.*
FROM

    \(SELECT 
            REPLACE\(UUID\_TO\_CHAR\(gen\_uuid\(\)\), '\-', ''\) AS PASS 
            , R\.RDB$RELATION\_NAME AS LOGIN 
            , row\_number\(\)over\(\) r 
    FROM 
            RDB$RELATIONS R\)  B

\)

ORDER BY
LOGIN DESC

Select Expression
-> Sort (record length: 132, key length: 100)
-> Window
-> Record Buffer (record length: 57)
-> Table "RDB$RELATIONS" as "B R" Full Scan

-------------------------------------------------------------
but this one is ok
SELECT
PASS
, 'ALTER USER ''' || trim(LOGIN) || ''' SET PASSWORD ''' || PASS || ''';'
FROM
(SELECT
B.*, row_number()over() r
FROM

    \(SELECT 
            REPLACE\(UUID\_TO\_CHAR\(gen\_uuid\(\)\), '\-', ''\) AS PASS 
            , R\.RDB$RELATION\_NAME AS LOGIN 

    FROM 
            RDB$RELATIONS R\)  B

\)

ORDER BY
LOGIN DESC

Select Expression
-> Sort (record length: 284, key length: 100)
-> Window
-> Record Buffer (record length: 57)
-> Table "RDB$RELATIONS" as "B R" Full Scan

it looks like it must be
select from ((derived + window) from derived)
not
select from (derived from (derived + window))

@firebird-automations
Copy link
Collaborator Author

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;
2) it supposes that columns in SELECT section are evaluated from right to left (yes, this is Firebird specific that can be considered as stable for last NN years, but who knows what will be in the future... this behaviour is NOT documented!)

Please note, that we HAVE to mention "dummy_result" column in top-level select list, otherwise result of "final_expr" will be null.
I can't explain this :-)

select
user_login
,user_pswd
,iif(dummy_result=0,'','') || 'alter user ' || trim(user_login) || ' password ''' || user_pswd || '''' as final_expr
from (
select user_login, user_pswd, dummy_result
from (
select
replace(r.rdb$relation_name,'$','_') as user_login
,rdb$get_context(
'USER_TRANSACTION'
,'USER_PSWD_FOR_' || replace(r.rdb$relation_name,'$','_')
) user_pswd
,rdb$set_context(
'USER_TRANSACTION'
,'USER_PSWD_FOR_' || replace(r.rdb$relation_name,'$','_')
,left(replace(uuid_to_char(gen_uuid()),'-','') , 20)
) as dummy_result
from rdb$relations r
rows 3
) t
);

Result:
USER_LOGIN RDB_PAGES
USER_PSWD 52705F695C3348719FDB
FINAL_EXPR alter user RDB_PAGES password '52705F695C3348719FDB'

USER_LOGIN RDB_DATABASE
USER_PSWD A39BDC8320CD4DD9990E
FINAL_EXPR alter user RDB_DATABASE password 'A39BDC8320CD4DD9990E'

USER_LOGIN RDB_FIELDS
USER_PSWD 1BFC42E322D64D4AACFE
FINAL_EXPR alter user RDB_FIELDS password '1BFC42E322D64D4AACFE'

PS.
The single plus of this "solution" - its plan, w/o SORT.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant