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
isthe <select statement:   single row> or innermost <query specification> that contains FC.  The   scope of a range
variableof 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
<collectionderived table> that is simply   contained in FC and is preceded by TR, and the <join condition> of all
<joinedtable>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
priorto 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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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