Re: LATERAL, UNNEST and spec compliance

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * David Fetter (david(at)fetter(dot)org) wrote:
>> 3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
>>
>> (As far as I can tell, those cases whose behaviour would be changed by
>> this actually produce errors in versions prior to 9.3, so no working
>> code should be affected.)

> +1 for me on this idea. If you're calling an SRF, passing in a lateral
> value, 'LATERAL' seems like it's just a noise word, and apparently the
> SQL authors felt the same, as they don't require it for unnest().

At first I didn't like this idea, but it's growing on me.

However ... David is wrong to claim that it's zero-risk. It's true that
an SRF can't contain any side-references today, but it can contain an
outer reference. Consider a case like

SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)

In existing releases the "y" could be a valid outer reference to a.y.
If b also has a column y, David's proposal would cause us to prefer
that interpretation, since b.y would be more closely nested than a.y.
If you're lucky, you'd get a type-mismatch error, but if the two y's
are of similar datatypes the query would just silently do something
different than it used to.

This is a little bit far-fetched, but it could happen. As against that,
we make incompatible changes in every release, and it does seem like
assuming LATERAL for functions in FROM would be a usability gain most
of the time. And special-casing UNNEST to satisfy the standard seems
*really* ugly.

> I agree that we should really hammer this down before 9.3 is out the
> door.

Yeah, if we're going to do this it'd make the most sense to do it in the
same release that introduces LATERAL.

regards, tom lane