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

On 2016-09-12 11:29:37 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > 0002-Shore-up-some-weird-corner-cases-for-targetlist-SRFs.patch
> > Forbid UPDATE ... SET foo = SRF() and ORDER BY / GROUP BY containing
> > SRFs that would change the number of returned rows. Without the
> > latter e.g. SELECT 1 ORDER BY generate_series(1,10); returns 10 rows.
>
> I'm on board with disallowing SRFs in UPDATE, because it produces
> underdetermined and unspecified results; but the other restriction
> seems 100% arbitrary. There is no semantic difference between
> SELECT a, b FROM ... ORDER BY srf();
> and
> SELECT a, b, srf() FROM ... ORDER BY 3;
> except that in the first case the ordering column doesn't get returned to
> the client. I do not see why that's so awful that we should make it fail
> after twenty years of allowing it.

I do think it's awful that an ORDER BY / GROUP BY changes the number of
rows processed. This should never have been allowed. You just need a
little typo somewhere that makes the targetlist entry not match the
ORDER/GROUP BY anymore and your results will differ in weird ways -
rather hard to debug in the GROUP BY case.

> And I certainly don't see why there
> would be an implementation reason why we couldn't support it anymore
> if we can still do the second one.

There's nothing requiring this here, indeed.

Andres