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-34931MDEV-31466 name resolution fails in --view
Closed
MDEV-34941MDEV-31466-fix column count issue with union in derived table
Closed
is duplicated by
MDEV-16901Implement an optional list of column names with the derived table name
Closed
relates to
MDEV-345062nd execution name resolution problem with pushdown into unions
Closed
MDEV-34884Update documentation for derived table column name specification
Roel Van de Paar
added a comment - - 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.
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 - - 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.
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?
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 - 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)
New Feature
Critical
MDEV-34931 MDEV-31466 name resolution fails in --view
MDEV-34884 Update documentation for derived table column name specification
Ok, I finally approve the implementation of this task.