SQL Language Extension: derived tables

Function:
    A derived table is a table derived from a select statement.
    Derived tables can also be nested to build complex queries.
    They can be joined the same as normal tables and views.

Author:
    Arno Brinkman <[email protected]>

Format:
    SELECT
      <select list>
    FROM
      <table reference list>

    <table reference list> ::= <table reference> [{<comma> <table reference>}...]

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

    <table primary> ::=
        <table> [[AS] <correlation name>]
      | <derived table>
      | <lateral derived table>

    <derived table> ::=
        <query expression> [[AS] <correlation name>] 
          [<left paren> <derived column list> <right paren>]

    <derived column list> ::= <column name> [{<comma> <column name>}...]

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

Notes:
    - Every column in the derived table must have a name. Unnamed expressions like 
      constants should be added with an alias or the column list should be used.
    - The number of columns in the column list should be the same as the number of 
      columns from the query expression.
    - The optimizer can handle a derived table very efficiently, but if the 
      derived table contains a sub-select then no join order can be made (if the 
      derived table is included in an inner join).
    - Keyword LATERAL allows the derived table to reference fields from the priorly
      listed tables in the current <table reference list>.

Examples:

a) Simple derived table:

  SELECT
    *
  FROM
    (SELECT
       RDB$RELATION_NAME, RDB$RELATION_ID
     FROM
       RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)


b) Aggregate on a derived table which also contains an aggregate:

  SELECT
    DT.FIELDS,
    Count(*)
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       Count(*)
     FROM
       RDB$RELATIONS R
       JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
     GROUP BY
       R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
  GROUP BY
    DT.FIELDS


c) UNION and ORDER BY example:

  SELECT
    DT.*
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     UNION ALL
     SELECT
       R.RDB$OWNER_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     ORDER BY
       2) AS DT
  WHERE
    DT.RDB$RELATION_ID <= 4

d) LATERAL derived table:

  SELECT
    *
  FROM
    (SELECT RDB$RELATION_NAME, RDB$RELATION_ID FROM RDB$RELATIONS)
      AS R (RELATION_NAME, RELATION_ID)
    CROSS JOIN LATERAL
    (SELECT COUNT(*) FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = R.RELATION_NAME)
      AS RF (FIELD_COUNT)