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
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)
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.
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.
New Feature
Critical
MDEV-34931 MDEV-31466 name resolution fails in --view
MDEV-34884 Update documentation for derived table column name specification
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)