[#MDEV-31466] Add optional correlation column list for derived tables

In MariaDB for table value constructors, the column names are taken from the value of the first set of results

MariaDB [test]> values (3,4),(5,6);

+---+---+

| 3 | 4 |

+---+---+

| 3 | 4 |

| 5 | 6 |

+---+---+

2 rows in set (0.002 sec)

There is no mechanism to assign them names. The SQL standard tells us that the column name(s) for a table value expression is "implementation dependent".

PostgreSQL can (re)assign column names in the derived table name syntax element.

rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);

 col1 | col2

------+------

    3 |    4

    5 |    6

(2 rows)

rex=# select * from (select concat(col1, col2)  from (values (3,4)) as foo(col1,col2)) as foo2 (a);

----

(1 row)

This is similar to the syntax used in the specification of a CTE, i.e.

MariaDB [test]> WITH cte(a,b) as (values (3,4),(5,6) ) select * from cte ;

+---+---+

| a | b |

+---+---+

| 3 | 4 |

| 5 | 6 |

+---+---+

2 rows in set (0.005 sec)

The SQL (2016) Foundation document, (starting at page 406) describes this syntax for the <table primary> syntax element.
Note the option of this being <derived table> <correlation or recognition>
where <derived table> translates into '(' <query expression> ')'.

<table primary> ::=

    <table or query name>

    [ <query system time period specification> ]

    [ <correlation or recognition> ]

  | <derived table> <correlation or recognition>

  | <lateral derived table> <correlation or recognition>

  | <collection derived table> <correlation or recognition>

  | <table function derived table> <correlation or recognition>

  | <PTF derived table> [ <correlation or recognition> ]

  | <only spec> [ <correlation or recognition> ]

  | <data change delta table> [ <correlation or recognition> ]

  | <JSON table> <correlation or recognition>

  | <JSON table primitive> <correlation name>

  | <parenthesized joined table>

<correlation or recognition> ::=

  [ AS ] <correlation name>

  [ <parenthesized derived column list> ]

<parenthesized derived column list> ::=

  <left paren> <derived column list> <right paren>

<derived column list> ::=

  <column name list>

<column name list> ::=

  <column name> [ { <comma> <column name> }... ]

The MDEV covers the implementation of this for MariaDB.

[MDEV-31466] Add optional correlation column list for derived tables Created: 2023-06-13 Updated: 2024-02-06
Status:	In Progress
Project:	MariaDB Server
Component/s:	Data Definition - Temporary, Data Manipulation - Insert
Fix Version/s:	11.5

[MDEV-31466] Add optional correlation column list for derived tables Created: 2023-06-13 Updated: 2024-02-06