LATERAL, UNNEST and spec compliance

Folks,

Andrew Gierth asked me to send this out as his email is in a parlous
state at the moment. My comments will follow in replies. Without
further ado:

SQL2008 says, for 7.6 <table reference>

6)
a) If TR is contained in a <from clause> FC with no intervening <query
expression>, then the scope clause SC of TR is the <select statement:
single row> or innermost <query specification> that contains FC. The
scope of a range variable of TR is the <select list>, <where clause>,
<group by clause>, <having clause>, and <window clause> of SC, together
with every <lateral derived table> that is simply contained in FC and
is preceded by TR, and every <collection derived table> that is simply
contained in FC and is preceded by TR, and the <join condition> of all
<joined table>s contained in SC that contain TR. If SC is the <query
specification> that is the <query expression body> of a simple table
query STQ, then the scope of a range variable of TR also includes the
<order by clause> of STQ.

This is the clause that defines the scope effect of LATERAL, and as can be
seen, it defines <collection derived table>, i.e. UNNEST(), as having the
same behaviour as <lateral derived table>.

It is also worth noting at this point that pg's "FROM func()" syntax is not
in the spec (the nearest is "FROM TABLE(<collection value expression>)").

Our implementation of UNNEST currently deviates from the spec by not being
implicitly LATERAL; given the (sub)query

SELECT * FROM sometable, UNNEST(somearray);

then "somearray" is required to be a parameter or outer reference rather
than a column of "sometable". To get the spec's behaviour for this, we
currently have to do:

SELECT * FROM sometable, LATERAL UNNEST(somearray);

which is non-standard syntax. (In the spec, only <table subquery> can
follow LATERAL.)

(We also don't accept the (optional) syntax of S301, allowing multiple
parameters to UNNEST().)

As I see it, the current options are:

1. Do nothing, and insist on non-standard use of the LATERAL keyword.

2. Add UNNEST to the grammar (or parse analysis) as a special case, making
it implicitly LATERAL.

(This would make implementing S301 easier, but special cases are ugly.)

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.)

Since LATERAL is new in 9.3, I think the pros and cons of these choices
should be considered now, rather than being allowed to slide by unexamined.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate