Bug 360896

Description Brian Payton 2011-10-13 18:37:06 EDT

Need to update the SQL Query Model to add support for the "lateral derived table" SQL element.  In ISO SQL terms, it's a form of table reference.  Namely:

<table reference> ::=
    <table factor>
  | <joined table>

<table factor> ::=
    <table primary> 

<table primary> ::=
    <table or query name> ...
  | <derived table> ...
  | <lateral derived table> ...
    ...

<lateral derived table> ::=
  LATERAL <table subquery>

Here's an example:

  SELECT d.dept_name, e.emp_name
    FROM 
      dept d, 
      LATERAL ( SELECT emp_id, emp_name
        FROM emp
        WHERE emp_dept = d.dept_id ) e

The difference the LATERAL keyword makes is that the subquery definition following the LATERAL keyword is allowed to refer to tables defined ahead of it in the same FROM clause.  A regular subquery can only refer to tables defined at a higher level in the query.

So in this example the subquery "e" can refer to the dept table "d" in its definition where otherwise that would be an invalid reference.