Search Postgresql Archives

Scalar subquery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux