Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling)

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2016-09-02 09:05:35 -0500, Kevin Grittner wrote:
>>> In general, we've been skeptical about giving any guarantees about
>>> result ordering.

> Well, it's historically how we behaved for SRFs. I'm pretty sure that
> people will be confused if
> SELECT generate_series(1, 10) FROM sometbl;
> suddenly returns rows in an order that reverse from what
> generate_series() returns.

True, but how much "enforcement" do we need really? This will be a cross
product join, which means that it can only be done as a nestloop not as a
merge or hash (there being no join key to merge or hash on). ISTM all we
need is that the SRF be on the inside of the join, which is automatic
if it's LATERAL.

>> I think it is a very bad idea to move away from the statement that
>> a query generates a set of rows, and that no order is guaranteed
>> unless the top level has an ORDER BY clause. How hard is it to add
>> ORDER BY 1, 2 to the above query? Let the optimizer notice when a
>> node returns data in the needed order and skip the sort if possible.

> There's no such infrastructure for SRFS/ROWS FROM.

And in particular nothing to ORDER BY in this example.

regards, tom lane