From: | Vyacheslav Kalinin <vka(at)mgcp(dot)com> |
---|---|
To: | General postgres mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Scalar subquery |
Date: | 2010-08-27 13:41:20 |
Message-ID: | AANLkTi=A_zzmAxh8E1UaxYynWx1JgG2dbDVmtRavL6VQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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().
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Bradley | 2010-08-27 13:50:04 | createdb process hangs when batched |
Previous Message | Andreas Schmitz | 2010-08-27 13:06:20 | Re: Too much logging |