Re: ARRAY(subquery) volatility

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> Why does the first query below return the same value for each row
> while the second query returns random values? Planner optimization?

> test=> SELECT ARRAY(SELECT random()) FROM generate_series(1, 5);
> test=> SELECT ARRAY(SELECT random() + x * 0) FROM generate_series(1, 5) AS g(x);

The sub-SELECT in the first one is considered an uncorrelated subquery,
so you get a plan that evaluates the subquery just once:

Function Scan on generate_series (cost=0.01..12.51 rows=1000 width=0)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0)

In the second case, x is an outer parameter to the subquery, so it has
to be re-evaluated for each row of the outer query:

Function Scan on generate_series g (cost=0.00..32.50 rows=1000 width=4)
SubPlan
-> Result (cost=0.00..0.02 rows=1 width=0)

Note the "InitPlan" vs "SubPlan" labels --- they look similar, but the
evaluation rules are totally different.

The fact that there's a volatile function in the subquery isn't
considered while making this decision. I'm not sure if it should be.

regards, tom lane