Обсуждение: Subselects in select expressions

Royce Ausburn <royce.ml@inomial.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.

            regards, tom lane

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

> Royce Ausburn <royce.ml@inomial.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 =)