Current testing environment 10.3.3, also confirmed on 10.3.8

I can confirm the observations for 10.3.8

This behaviour might be a side-effect on an incomplete feature. That is, some aspects may have been implemented for other purposes initially, but no specific focus has been on completing this feature.
The result is curious behaviour:

MariaDB [test]> SELECT * FROM (VALUES (1), (2), (3)) AS t;

+---+

| 1 |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 rows in set (0.001 sec)

When using SELECT *, the column is named after the value of the first column.

SELECT `1` FROM (VALUES (1), (2), (3)) AS t

also works as the 1 is really regarded as the column name (note the use of backticks to indicate an identifier).
You can't do SELECT `2` or SELECT `colname`, you get ERROR 1054 (42S22): Unknown column 'colname' in 'field list'

The following should work to specify the column names (as per the SQL standard), but yields a syntax error.

MariaDB [test]> SELECT * FROM (VALUES (1), (2), (3)) AS t(i);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(i)' at line 1

Suggestion: this feature is almost complete (accidentally or otherwise), all it needs is a few additions in the parser!

MariaDB (and MySQL) are very far from claiming that they implement SQL standard completely. However, since it's not documented (or cannot be found), there is no way to know what was skipped intentionally and what was simply forgotten.

shagalla,
Could you please clarify whether the current limitations to the syntax are intentional, and then

• if it's a bug, then fix it;
• if it's expected behavior, convert it to a feature request;

and in both cases communicate with greenman to provide him enough material for proper documentation.

elenst,
This problem doesn't concern table value constructors, it concerns derived tables:

MariaDB [test]> select * from (select id from t1) as t2(a);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(a)' at line 1

So it should be a feature for derived tables.

That's right. It really applies to almost every type of table that can be placed in the FROM clause, as I mentioned in the description. This particular use is a <derived table>.

But for most cases, having a derived column name is not an issue. A normal <derived table>, based on a <query expression>/SELECT has <derived columns> which allows an AS clause for the derived column name.

The <table value constructor> does not provide for this, so the column names are derived from the first values of the first row in the constructor. It's really not very usable like that, as Arjen noted and I also showed in the description.

It would be a nice addition to support a <derived column list> for that case.

A new task for this issue was created: MDEV-16901