Обсуждение: 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