On Mon, 17 Dec 2001, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > However, I'd think:
> > "select (select nextval from test_seq) from multiple_rows;"
> > should give you different values and doesn't, although
> > "select (select nextval from test_seq where i IS NULL or i IS NOT NULL)
> > from multiple_rows;" does give you different values.
>
> In the first case, the subselect is visibly not dependent on the outer
> query, so it's evaluated only once; in the second case it has to be
> re-evaluated for each row using that row's value of i. You can see the
> difference (InitPlan vs. SubPlan) in the query's EXPLAIN output.
I figured that, but I'm not sure whether or not that's a bug of
over-optimizing since I think that it probably *should* give you
different results since it is different each time it's evaluated.
Without checking spec, I'd expect that conceptually the select list
entries are evaluated per row, even if we can avoid that when the
value is certain to be the same, which would mean the result is
incorrect since if it was evaluated per row it would give different
results each time.
