Re: LATERAL, UNNEST and spec compliance - Mailing list pgsql-hackers

From Tom Lane
Subject Re: LATERAL, UNNEST and spec compliance
Date
Msg-id 15925.1359138792@sss.pgh.pa.us
Whole thread Raw
In response to Re: LATERAL, UNNEST and spec compliance  (Stephen Frost <sfrost@snowman.net>)
Responses Re: LATERAL, UNNEST and spec compliance
Re: LATERAL, UNNEST and spec compliance
List pgsql-hackers
Stephen Frost <sfrost@snowman.net> writes:
> * David Fetter (david@fetter.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



pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Question regarding Sync message and unnamed portal
Next
From: Dhruv Ahuja
Date:
Subject: Re: "pg_ctl promote" exit status