Re: Subselects in select expressions

On 02/11/2011, at 2:16 PM, Tom Lane wrote:

> Royce Ausburn <royce(dot)ml(at)inomial(dot)com> writes:
>> [ random() is evaluated only once in ]
>> test=# select (select random()) from generate_series(1,10); -- rows are the same
>
>> I understand that it's likely an optimisation thing -- postgres knows
>> that the subselect doesn't depend on the FROM rows so it evaluates it
>> only once, but is this really correct behaviour?
>
> I can't find anything in the SQL standard that explicitly addresses this
> point, but a bit of googling turns up documentation for both Oracle and
> DB/2 that say they evaluate uncorrelated subqueries just once:
> http://www.smart-soft.co.uk/Oracle/advanced-sql-tutorial.htm
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm
> whereas correlated subqueries are evaluated once per row.
>
> So we're in good company here. Even if we were not, I'd be loath to
> change it, because people have historically relied on this behavior to
> force single evaluation of what otherwise would be considered volatile
> functions.

That's good enough for me, Tom. Thanks =)