1

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 ).

5

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.