Re: Scalar subquery

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vyacheslav Kalinin <vka(at)mgcp(dot)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
>