Re: Scalar subquery

[Vyacheslav Kalinin <vka@xxxxxxxx>]

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@xxxxxxxx> 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().