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

Rex Johnston created issue - 2023-06-13 02:51

Rex Johnston made changes - 2023-06-13 02:53

Field	Original Value	New Value
Assignee		Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2023-06-13 02:53

Fix Version/s

11.2 [ 28603 ]

Rex Johnston made changes - 2023-06-13 02:54

Status

Open [ 1 ]

In Progress [ 3 ]

Rex Johnston made changes - 2023-06-14 23:12

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

A popular alternative can assign the names at the level of a derived table.

{code:SQL}
rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);
col1 | col2
------+------
3 | 4
5 | 6
(2 rows)
{code}

It is proposed that we follow this standard.
<fill in details about the standard here>

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

A popular alternative database can assign the names at the level of a derived table.

{code:SQL}
rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);
col1 | col2
------+------
3 | 4
5 | 6
(2 rows)
{code}

It is proposed that we follow this standard.
<fill in details about the standard here>

Sergei Golubchik added a comment - 2023-06-27 15:53

what "popular alternative database"? What does the standard say?

Sergei Golubchik added a comment - 2023-06-27 15:53 what "popular alternative database"? What does the standard say?

Sergei Golubchik made changes - 2023-06-27 15:53

Fix Version/s		11.3 [ 28565 ]
Fix Version/s	11.2 [ 28603 ]

Rex Johnston made changes - 2023-08-14 04:32

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

A popular alternative database can assign the names at the level of a derived table.

{code:SQL}
rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);
col1 | col2
------+------
3 | 4
5 | 6
(2 rows)
{code}

It is proposed that we follow this standard.
<fill in details about the standard here>

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

A popular database (PostgreSQL) can assign the names at the level of a derived table.

{code:SQL}
rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);
col1 | col2
------+------
3 | 4
5 | 6
(2 rows)
{code}

It is proposed that we follow this standard.
<fill in details about the standard here>

Rex Johnston made changes - 2023-08-15 18:39

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

A popular database (PostgreSQL) can assign the names at the level of a derived table.

{code:SQL}
rex=# select * from (values (3,4),(5,6)) as foo(Col1, Col2);
col1 | col2
------+------
3 | 4
5 | 6
(2 rows)
{code}

It is proposed that we follow this standard.
<fill in details about the standard here>

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

It is proposed that we follow this standard.

Rex Johnston made changes - 2023-08-15 18:40

Priority

Major [ 3 ]

Minor [ 4 ]

Sergei Golubchik made changes - 2023-08-16 14:43

Priority

Minor [ 4 ]

Critical [ 2 ]

Rex Johnston made changes - 2023-08-16 22:46

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

It is proposed that we follow this standard.

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

The SQL (2016) Foundation document, page 406 describes this syntax

{noformat}
<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> }... ]

{noformat}

Rex Johnston made changes - 2023-08-16 22:46

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

The SQL (2016) Foundation document, page 406 describes this syntax

{noformat}
<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> }... ]

{noformat}

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

The SQL (2016) Foundation document, (starting at page 406) describes this syntax

{noformat}
<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> }... ]

{noformat}

Rex Johnston made changes - 2023-08-16 23:49

Comment

[ The 2016 SQL Foundation Document, page 1598
26) Subclause 7.3, “<table value constructor>”:
a) The column names of a <table value constructor> or a <contextually typed table value constructor> are implementation-dependent. ]

Rex Johnston made changes - 2023-08-16 23:57

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

The SQL (2016) Foundation document, (starting at page 406) describes this syntax

{noformat}
<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> }... ]

{noformat}

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

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

{noformat}
<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> }... ]
{noformat}

Rex Johnston made changes - 2023-08-23 22:00

Summary

Implement column names for Table Value Expressions

Optional column name specification for derived tables.

Rex Johnston made changes - 2023-08-23 22:05

Description

Implement column names for Table Value Expressions.

currently column names are simply taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is currently no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

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

{noformat}
<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> }... ]
{noformat}

The SQL standard tells us that the column name(s) for a table value expression is "implementation dependent".
In MariaDB, the column names are taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

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

{noformat}
<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> }... ]
{noformat}

The MDEV covers the implementation of this for MariaDB.

Rex Johnston made changes - 2023-08-23 22:09

Link

This issue relates to ~~MDEV-31995~~ [ ~~MDEV-31995~~ ]

Rex Johnston made changes - 2023-08-30 00:34

Summary

Optional column name specification for derived tables.

Implement column name assignment for derived table expressions.

Rex Johnston made changes - 2023-09-01 03:31

Link

This issue blocks ~~MDEV-31995~~ [ ~~MDEV-31995~~ ]

Rex Johnston made changes - 2023-09-01 03:31

Link

This issue relates to ~~MDEV-31995~~ [ ~~MDEV-31995~~ ]

Rex Johnston made changes - 2023-09-05 20:23

Summary

Implement column name assignment for derived table expressions.

add optional

Rex Johnston made changes - 2023-09-05 20:24

Summary

add optional

add optional [<parenthesized derived column list>] to <derived table> [ AS ] <correlation name>

Rex Johnston made changes - 2023-09-05 21:22

Description

The SQL standard tells us that the column name(s) for a table value expression is "implementation dependent".
In MariaDB, the column names are taken from the value of the first set of results

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

There is no mechanism to assign them names.

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

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

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

{noformat}
<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> }... ]
{noformat}

The MDEV covers the implementation of this for MariaDB.

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

{code:SQL}
MariaDB [test]> values (3,4),(5,6);
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
| 5 | 6 |
+---+---+
2 rows in set (0.002 sec)
{code}

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.

{code:SQL}
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);
a
----
34
(1 row)

{code}

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

{code:SQL}
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)
{code}

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

{noformat}
<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> }... ]
{noformat}

The MDEV covers the implementation of this for MariaDB.

Igor Babaev (Inactive) made changes - 2023-09-06 02:00

Summary

add optional [<parenthesized derived column list>] to <derived table> [ AS ] <correlation name>

Add optional correlation column list for derived tables

Sergei Golubchik made changes - 2023-09-17 18:02

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.3 [ 28565 ]

Rex Johnston made changes - 2023-09-25 02:04

Link

This issue blocks ~~MDEV-31995~~ [ ~~MDEV-31995~~ ]

Sergei Golubchik made changes - 2023-10-30 12:59

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Julien Fritsch made changes - 2023-12-07 10:25

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-01-29 21:48

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Rex Johnston made changes - 2024-03-12 20:19

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2024-03-19 18:32

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Rex Johnston made changes - 2024-05-28 15:20

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-05-28 15:20

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-05-28 15:21

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Rex Johnston added a comment - 2024-06-04 03:32 - edited

Hi igor, please review ~~github.com/MariaDB/server/tree/bb-11.6MDEV-31466~~-
Thanks, Rex

[Edit] This tree has been removed, current tree is https://github.com/MariaDB/server/tree/bb-11.7-MDEV-31466

Rex Johnston added a comment - 2024-06-04 03:32 - edited Hi igor , please review github.com/MariaDB/server/tree/bb-11.6 MDEV-31466 - Thanks, Rex [Edit] This tree has been removed, current tree is https://github.com/MariaDB/server/tree/bb-11.7-MDEV-31466

Rex Johnston made changes - 2024-06-04 03:32

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Igor Babaev (Inactive) made changes - 2024-06-06 21:18

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-06-07 17:25

Assignee

Rex Johnston [ JIRAUSER52533 ]

Igor Babaev [ igor ]

Sergei Golubchik made changes - 2024-06-16 17:02

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Rex Johnston made changes - 2024-07-01 23:29

Link

This issue relates to ~~MDEV-34506~~ [ ~~MDEV-34506~~ ]

Sergei Golubchik made changes - 2024-08-22 11:17

Priority

Critical [ 2 ]

Major [ 3 ]

Igor Babaev (Inactive) made changes - 2024-08-25 20:51

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Igor Babaev (Inactive) added a comment - 2024-08-25 20:53

Johnston Please find my feedback in your mail box.

Igor Babaev (Inactive) added a comment - 2024-08-25 20:53 Johnston Please find my feedback in your mail box.

Rex Johnston made changes - 2024-08-26 03:51

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-08-26 03:51

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2024-08-27 15:27

Issue Type

Task [ 3 ]

New Feature [ 2 ]

Sergei Golubchik made changes - 2024-08-27 15:27

Priority

Major [ 3 ]

Critical [ 2 ]

Rex Johnston made changes - 2024-08-29 04:15

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Rex Johnston made changes - 2024-08-29 04:15

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-08-29 04:17

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Rex Johnston made changes - 2024-08-29 04:18

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Rex Johnston made changes - 2024-08-29 04:35

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2024-08-29 04:35

Comment

[ Hi Igor, please review https://github.com/MariaDB/server/tree/bb-11.7-MDEV-31466.
Latest changes are specifically in mysql-test/main/derived.test as detailed in slack.
Thanks, Rex ]

Rex Johnston added a comment - 2024-08-29 04:36 - edited

Please test

https://github.com/MariaDB/server/tree/bb-11.7-MDEV-31466

Rex Johnston added a comment - 2024-08-29 04:36 - edited Please test https://github.com/MariaDB/server/tree/bb-11.7-MDEV-31466

Roel Van de Paar added a comment - 2024-09-05 21:50

So before it was supported, but in a very limited fashion only - i.e. not more than one column at the time and not more than one row at the time - correct?

CS 10.5.27 0e27351028a4888b0da271e2089bf1f847620396 (Optimized)
10.5.27-opt>select (values(1)) as 'a', (values(2)) as 'b';
+------+------+
\| a \| b \|
+------+------+
\| 1 \| 2 \|
+------+------+
1 row in set (0.001 sec)

Roel Van de Paar added a comment - 2024-09-05 21:50 So before it was supported, but in a very limited fashion only - i.e. not more than one column at the time and not more than one row at the time - correct? CS 10.5.27 0e27351028a4888b0da271e2089bf1f847620396 (Optimized) 10.5.27-opt>select (values(1)) as 'a', (values(2)) as 'b'; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.001 sec)

Roel Van de Paar made changes - 2024-09-05 21:51

Assignee

Rex Johnston [ JIRAUSER52533 ]

Roel Van de Paar [ roel ]

Rex Johnston added a comment - 2024-09-05 22:19 - edited

The mechanism renames columns outside the query block, rather than inside. So for the block

( select c1 as a, c2 as b from t1 where a > 1 )

in clauses within the block, we can use either c1 or a as field references, if we assign the block to a derived table

( select c1 as a, c2 as b from t1 where a > 1 ) dt (something1, something2)

our clauses within the block need not change, but outside this block, we are now forced to use something1 or something2.
It means we can easily rip out tables from a query and drop in derived table with the same names and not have to rewrite other parts of the query to accommodate different column names.

Rex Johnston added a comment - 2024-09-05 22:19 - edited The mechanism renames columns outside the query block, rather than inside. So for the block ( select c1 as a, c2 as b from t1 where a > 1 ) in clauses within the block, we can use either c1 or a as field references, if we assign the block to a derived table ( select c1 as a, c2 as b from t1 where a > 1 ) dt (something1, something2) our clauses within the block need not change, but outside this block, we are now forced to use something1 or something2. It means we can easily rip out tables from a query and drop in derived table with the same names and not have to rewrite other parts of the query to accommodate different column names.

Roel Van de Paar made changes - 2024-09-06 01:29

Link

This issue relates to ~~MDEV-34884~~ [ ~~MDEV-34884~~ ]

Sergei Golubchik made changes - 2024-09-15 06:46

Link

This issue causes ~~MDEV-34931~~ [ ~~MDEV-34931~~ ]

Sergei Golubchik made changes - 2024-09-16 21:30

Link

This issue is part of TODO-4907 [ TODO-4907 ]

Roel Van de Paar added a comment - 2024-09-17 04:00 - edited

Created ~~MDEV-34941~~ ~~MDEV-31466~~: SIGSEGV in st_select_lex_unit::rename_types_list on SELECT ... UNION

UPDATE: retested patched branch bb-11.7-~~MDEV-31466~~ looks fine.

Roel Van de Paar added a comment - 2024-09-17 04:00 - edited Created MDEV-34941 MDEV-31466 : SIGSEGV in st_select_lex_unit::rename_types_list on SELECT ... UNION UPDATE: retested patched branch bb-11.7- MDEV-31466 looks fine.

Roel Van de Paar made changes - 2024-09-17 04:00

Link

This issue causes ~~MDEV-34941~~ [ ~~MDEV-34941~~ ]

Ralf Gebhardt made changes - 2024-09-17 08:51

Labels

Preview_11.7

Roel Van de Paar added a comment - 2024-09-21 05:08

OK to push

Roel Van de Paar added a comment - 2024-09-21 05:08 OK to push

Roel Van de Paar made changes - 2024-09-21 05:09

Assignee

Roel Van de Paar [ roel ]

Rex Johnston [ JIRAUSER52533 ]

Roel Van de Paar made changes - 2024-09-21 05:09

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Rex Johnston made changes - 2024-09-22 19:55

Assignee	Rex Johnston [ JIRAUSER52533 ]	Igor Babaev [ igor ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Igor Babaev (Inactive) added a comment - 2024-09-22 19:58

Ok, I finally approve the implementation of this task.

Igor Babaev (Inactive) added a comment - 2024-09-22 19:58 Ok, I finally approve the implementation of this task.

Igor Babaev (Inactive) made changes - 2024-09-22 19:58

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2024-09-22 19:58

Assignee

Igor Babaev [ igor ]

Rex Johnston [ JIRAUSER52533 ]

Sergei Golubchik made changes - 2024-09-24 13:54

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7 [ 29815 ]

Sergei Golubchik made changes - 2024-10-07 12:24

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.8 [ 29921 ]

Rex Johnston made changes - 2024-10-14 20:38

Component/s		Data Manipulation - Subquery [ 10107 ]
Component/s	Data Definition - Temporary [ 10123 ]
Component/s	Data Manipulation - Insert [ 10101 ]
Fix Version/s		11.7.1 [ 29913 ]
Fix Version/s	11.7 [ 29815 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Rex Johnston made changes - 2024-10-14 21:32

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.7.1 [ 29913 ]

Rex Johnston made changes - 2024-11-13 19:17

Fix Version/s		11.7.1 [ 29913 ]
Fix Version/s	11.7 [ 29815 ]

Sergei Golubchik made changes - 2025-02-14 21:18

Link

This issue is duplicated by ~~MDEV-16901~~ [ ~~MDEV-16901~~ ]

MariaDB Server

Add optional correlation column list for derived tables

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration