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