Re: Scalar subquery

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.
Help anyone?

On Fri, Aug 27, 2010 at 5:41 PM, Vyacheslav Kalinin <vka(at)mgcp(dot)com> wrote:

> Hi,
>
> Apparently scalar subquery when used as a part of SELECT statement and when
> it does not depend on outer query columns
> is executed only once per statement, e.g.:
>
> postgres=# select i, (select random()) rand from generate_series(1, 3) i;
> i | rand
> ---+-------------------
> 1 | 0.992319826036692
> 2 | 0.992319826036692
> 3 | 0.992319826036692
>
> (Though term "depend" is subtle, compare these:
>
> postgres=# select i, (select random() + case when false then i else 0 end )
> rand from generate_series(1, 3) i;
> i | rand
> ---+-------------------
> 1 | 0.806265413761139
> 2 | 0.806265413761139
> 3 | 0.806265413761139
> (3 rows)
>
>
> postgres=# select i, (select random() where i=i ) rand from
> generate_series(1, 3) i;
> i | rand
> ---+-------------------
> 1 | 0.426443862728775
> 2 | 0.133071997668594
> 3 | 0.751982506364584
> (3 rows)
>
>
> postgres=# select i, (select random() where i=i or i is null ) rand from
> generate_series(1, 3) i;
> i | rand
> ---+-------------------
> 1 | 0.320982406847179
> 2 | 0.996762252878398
> 3 | 0.076554249972105
> (3 rows)
>
> Looks like dependence is not there anymore if PG is smart enough to
> simplify boolean expressions)
>
> Anyway, as some older PG versions and Oracle behave similarly I suppose
> this result is expected and desired (correct?),
> but unfortunately not well-documented (did I miss it mentioned?).
> Can anyone shed some light on this and/or probably update docs?
>
> P.S.
> I got bitten by a statement like this:
> select (select nextval('someseq') * a + b from somefunc()), col1, ....
> with a and b being OUT parameters of somefunc().
>
>
>
>