Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

Question

I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?

SELECT *
FROM ( VALUES (1) ) AS t(x);
 x 
---
 1
(1 row)

(syntax from postgresql).

Answer 1

MariaDB 10.3+

Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors.

SELECT 1, t.7
FROM (VALUES (7),(42)) AS t;
 1 |  7
--------
 1 |  7
 1 |  42

You'll notice that there is no support for column_alias in the FROM clause, instead requiring you to use the table_alias and the name of the first row's value for that column. But column_alias are allowed in a CTE's WITH clause,

WITH t(a) AS ( VALUES (1),(2) )
SELECT t.a, t.a AS b
FROM t;
 a |  b
--------
 1 |  1
 2 |  2

Additionally you may able to skirt this by giving the first row a definitive alias.

SELECT 1 AS x ,2 AS y
UNION VALUES (3,4),(5,6);
 x | y
-------
 1 | 2
 3 | 4
 5 | 6

also see examples in https://jira.mariadb.org/browse/MDEV-12172

---

PostgreSQL permits column aliases in FROM clauses, as show above with t(x), this syntax is also not supported in MariaDB

MariaDB <10.3; MySQL 5.x & 8.x (Workaround)

Prior to MariaDB 10.3, and all versions of MySQL do not support the VALUES expression.

SELECT * FROM ( VALUES (1) ) AS t(x);
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 'VALUES (1) ) AS t(x)' at line 1

However, it does support sub-selects with literals.

SELECT * FROM (SELECT (1) AS x) AS t;
-- more simply
-- SELECT * FROM (SELECT 1 AS x) AS t;
+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

PostgreSQL also supports this syntax.

SELECT * FROM ( SELECT 1 AS x ) AS t;
 x 
---
 1
(1 row)

However, things get a lot more verbose with this syntax if you're doing multiple rows. Below from MariaDB, also works in PostgreSQL

SELECT * FROM ( SELECT 1 AS x UNION ALL SELECT 2 ) AS t;
+---+
| x |
+---+
| 1 |
| 2 |
+---+

Whereas with VALUES, it's simply VALUES (1),(2)

Answer 2

As noted above, the syntax between MySQL 8 and MariaDB doesn't appear to be the same (MySQL requires the ROW keyword around the individual row values, MariaDB wants parentheses)

However you can use json_table to do the same thing, and that works the same for both.

SELECT * FROM json_table(
  "[1,2,3,4]", 
  '$[*]' COLUMNS(c1 INT PATH '$' ERROR ON ERROR)
) as t;

It's a great deal more concise than UNION ALL for each row (though it's not as well supported on older versions)