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@xxxxxxxxxxxxx> wrote:
Vyacheslav Kalinin <vka@xxxxxxxx> writes:It's loosely standardized.
> 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.
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
