Issue Details (XML | Word | Printable)

Key: CORE-4362
Type: Bug Bug
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Edivaldo de Araújo Pereira
Votes: 1
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

random results in logical operations on rand() generated numbers

Created: 11/Mar/14 02:34 PM   Updated: 05/Jun/19 04:05 AM
Component/s: Engine
Affects Version/s: 2.5.2 Update 1
Fix Version/s: None

Environment: Linux


 Description  « Hide
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.



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 11/Mar/14 02:56 PM
This is an implementation detail. Firebird does not materialize simple expressions.

Karol Bieniaszewski added a comment - 17/May/19 01:21 PM - edited
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';

Pavel Zotov added a comment - 22/May/19 07:05 AM
> 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
    )
)


Karol Bieniaszewski added a comment - 22/May/19 05:17 PM
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))

Pavel Zotov added a comment - 05/Jun/19 04:05 AM
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.