Thread: BUG #8678: Multiple evaluation single volatile function in select statement

The following bug has been logged on the website:

Bug reference:      8678
Logged by:          Maksym
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.3.2
Operating system:   Linux
Description:

Hi everyone,


One of my client got the problem of multiple-evaluation of the single
volatile function call.


Simplified test case:


CREATE OR REPLACE FUNCTION _test1(OUT val1 float, OUT val2 float) AS
$BODY$
DECLARE
  _val float;
BEGIN
    _val := random();
    val1 := _val;
        val2 := _val;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


Now good case:
 select * from _test1();
       val1        |       val2
-------------------+-------------------
 0.616893119178712 | 0.616893119178712




Wrong case:
select (_test1()).*;
       val1       |       val2
------------------+-------------------
 0.54869711631909 | 0.251445888541639




Wrong case hits especially painful when volatile function performs some
modification of the money-related data.


PS: yes I know such query could be rewritten via LATERAL but it doesn't make
current behavior more correct.

maxim.boguk wrote
> The following bug has been logged on the website:
>
> Bug reference:      8678
> Logged by:          Maksym
> Email address:

> maxim.boguk@

> PostgreSQL version: 9.3.2
> Operating system:   Linux
> Description:
>
> Hi everyone,
>
>
> One of my client got the problem of multiple-evaluation of the single
> volatile function call.
>
>
> Simplified test case:
>
>
> CREATE OR REPLACE FUNCTION _test1(OUT val1 float, OUT val2 float) AS
> $BODY$
> DECLARE
>   _val float;
> BEGIN
>     _val := random();
>     val1 := _val;
>         val2 := _val;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> Now good case:
>  select * from _test1();
>        val1        |       val2
> -------------------+-------------------
>  0.616893119178712 | 0.616893119178712
>
>
>
>
> Wrong case:
> select (_test1()).*;
>        val1       |       val2
> ------------------+-------------------
>  0.54869711631909 | 0.251445888541639
>
>
>
>
> Wrong case hits especially painful when volatile function performs some
> modification of the money-related data.
>
>
> PS: yes I know such query could be rewritten via LATERAL but it doesn't
> make
> current behavior more correct.

I cannot recite any arguments for why this behavior exists but the fact is
that it does and is not likely to be "fixed".  There are numerous ways to
avoid the multiple evaluation situation, not just LATERAL.

I'll add my vote to agreeing something should be done to avoid execution in
this situation but likely the end result will be an error as opposed to it
working correctly - which I am fine with.

If you give specifics about the case where the failure occurred that is a
much stronger example of why this needs to be improved upon compared to your
easily identifiable and fixable useless example.  I know you just meant it
for a proof of your supposed bug but as this behavior is well-known such
proof doesn't add anything to the conversation.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8678-Multiple-evaluation-single-volatile-function-in-select-statement-tp5783110p5783731.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.