Thanks, Tom Can this be clarified in docs? It is stated there now that scalar subquery is one of the kinds of expressions and it is somewhat counter-intuitive that an expression may sometimes not respect its own degree of volatility.
On Wed, Sep 1, 2010 at 2:16 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Vyacheslav Kalinin <v...@mgcp.com> writes: > > I just got my hands on mysql (5.0.something) and it does not cache the > > scalar subquery result. > > So... now I'm completely puzzled whether this is a bug, a desired result > or > > just a loosely standardized thing. > > It's loosely standardized. > > AFAICS, the spec doesn't address the detailed semantics of subqueries at > all, except in wording to this effect: > > Each <subquery> in the <search condition> is effectively > executed for each row of T and the results used in the ap- > plication of the <search condition> to the given row of T. > If any executed <subquery> contains an outer reference to a > column of T, the reference is to the value of that column in > the given row of T. > > There is wording like this for subqueries in WHERE and HAVING, but I > haven't found anything at all that mentions the behavior for subqueries > in the SELECT targetlist. In any case, the fact that they said > "effectively executed" and not simply "executed" seems to be meant to > leave implementors a lot of wiggle room. > > In particular, there isn't any wording that I can find suggesting > that the presence of volatile (or in the spec's classification, > nondeterministic) functions ought to affect the behavior. > > PG's interpretation is that if there is no outer reference in a > subquery, it's okay to implement it as an initplan, meaning it gets > evaluated at most once per call of the containing query. We don't > pay attention to whether there are volatile functions in there. > > regards, tom lane >
