Re: PostgreSQL 9.6 behavior change with set returning (funct).*

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Mar 23, 2016 at 6:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> A possibly larger problem is that it causes the SRFs to be evaluated
>> before sorting/ordering/limiting.

> I'm not sure I understand quite what the problem is here.

If you have "SELECT srf(x), y FROM ... ORDER BY y LIMIT n", we go
to some lengths as of 9118d03a8 to ensure that the ordering happens
before the SRF is expanded, meaning in particular that the SRF is
expanded only far enough to satisfy the limit. That is not the case
for SRF-in-FROM, and can't be if for instance there's a GROUP BY
involved. As-is, the proposed transformation obviously breaks the
semantics if grouping/aggregation/windowing is involved, and my point
is that that's true for ORDER BY/LIMIT as well. We'd need to fix
things so that the apparent order of performing those steps stays
the same.

I think that's probably doable in most cases by making a sub-select,
but I'm not sure if that works for every case --- in particular, what
do we do with a SRF referenced in ORDER BY or GROUP BY?

regards, tom lane