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
-
- relates to
-
-
- Closed
-
-
MDEV-34884 Update documentation for derived table column name specification
-
- Closed
-