Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
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);
|
a
|
----
|
34
|
(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.
Attachments
Issue Links
- causes
-
MDEV-34931 MDEV-31466 name resolution fails in --view
-
- Closed
-
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-34884 Update documentation for derived table column name specification
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Rex Johnston [ JIRAUSER52533 ] |
| Fix Version/s | 11.2 [ 28603 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| 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> |
| Fix Version/s | 11.3 [ 28565 ] | |
| Fix Version/s | 11.2 [ 28603 ] |
| 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> |
| 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. |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Priority | Minor [ 4 ] | Critical [ 2 ] |
| 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} |
| 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} |
| 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. ] |
| 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} |
| Summary | Implement column names for Table Value Expressions | Optional column name specification for derived tables. |
| 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. |
| Link |
This issue relates to |
| Summary | Optional column name specification for derived tables. | Implement column name assignment for derived table expressions. |
| Link |
This issue blocks |
| Link |
This issue relates to |
| Summary | Implement column name assignment for derived table expressions. | add optional |
| Summary | add optional | add optional [<parenthesized derived column list>] to <derived table> [ AS ] <correlation name> |
| 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. |
| Summary | add optional [<parenthesized derived column list>] to <derived table> [ AS ] <correlation name> | Add optional correlation column list for derived tables |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.3 [ 28565 ] |
| Link |
This issue blocks |
| Fix Version/s | 11.5 [ 29506 ] | |
| Fix Version/s | 11.4 [ 29301 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 11.6 [ 29515 ] | |
| Fix Version/s | 11.5 [ 29506 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Fix Version/s | 11.7 [ 29815 ] | |
| Fix Version/s | 11.6 [ 29515 ] |
| Link |
This issue relates to |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | In Testing [ 10301 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| 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 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Roel Van de Paar [ roel ] |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link | This issue is part of TODO-4907 [ TODO-4907 ] |
| Link |
This issue causes |
| Labels | Preview_11.7 |
| Assignee | Roel Van de Paar [ roel ] | Rex Johnston [ JIRAUSER52533 ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| Fix Version/s | 11.8 [ 29921 ] | |
| Fix Version/s | 11.7 [ 29815 ] |
| Fix Version/s | 11.7 [ 29815 ] | |
| Fix Version/s | 11.8 [ 29921 ] |
| 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 ] |
| Fix Version/s | 11.7 [ 29815 ] | |
| Fix Version/s | 11.7.1 [ 29913 ] |
| Fix Version/s | 11.7.1 [ 29913 ] | |
| Fix Version/s | 11.7 [ 29815 ] |
| Link |
This issue is duplicated by |