Обсуждение: LATERAL and VOLATILE functions

Hello

I tested some usage of LATERAL clause, and I found so LATERAL doesn't
respects difference between VOLATILE and IMMUTABLE functions.

Is this behave expected?

-- unexpected
postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT random()) x;
;v │      random
───┼──────────────────1 │ 0.630256460513922 │ 0.630256460513923 │ 0.63025646051392
(3 rows)


-- expected
postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT
random() - v + v) x;v │     ?column?
───┼───────────────────1 │ 0.3815484778024262 │ 0.7629880602471533 │ 0.181648664642125
(3 rows)


Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:
> Is this behave expected?

> -- unexpected
> postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT random()) x;
> ;
>  v        random
> ---+------------------
>  1   0.63025646051392
>  2   0.63025646051392
>  3   0.63025646051392
> (3 rows)

The LATERAL keyword is a no-op since x doesn't contain any
side-reference to g(v).  So you get a plain join between g and
a single-row relation x.

If the SQL standard actually specified what LATERAL means, we could
argue about whether that's a correct interpretation or not.  I haven't
been able to find anyplace where the spec defines the semantics though.

And I'm fairly certain that we *don't* want it to mean "recompute
for every row generated to the left of the keyword, whether there is
a variable reference or not".  Consider for example
select ... from a, b, c join lateral d on ...

If the D item only contains references to C, it's unlikely that the
programmer wants it to be re-evaluated again for each possible row
in A*B.
        regards, tom lane

2012/12/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> Is this behave expected?
>
>> -- unexpected
>> postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT random()) x;
>> ;
>>  v        random
>> ---+------------------
>>  1   0.63025646051392
>>  2   0.63025646051392
>>  3   0.63025646051392
>> (3 rows)
>
> The LATERAL keyword is a no-op since x doesn't contain any
> side-reference to g(v).  So you get a plain join between g and
> a single-row relation x.
>
> If the SQL standard actually specified what LATERAL means, we could
> argue about whether that's a correct interpretation or not.  I haven't
> been able to find anyplace where the spec defines the semantics though.
>
> And I'm fairly certain that we *don't* want it to mean "recompute
> for every row generated to the left of the keyword, whether there is
> a variable reference or not".  Consider for example
>
>         select ... from a, b, c join lateral d on ...
>
> If the D item only contains references to C, it's unlikely that the
> programmer wants it to be re-evaluated again for each possible row
> in A*B.

Stable and immutable functions should be recalculated once time, but
for volatile functions is recalculation probably more natural
(expected). Every time is strange, when function random() returns same
numbers. I am not sure if this behave can be problem in real usage -
probably it can be a surprise for someone who use random() for some
testing.

Regards

Pavel

>
>                         regards, tom lane