Support FROM (VALUES (...), ...) AS alias(col, ...) table value constructor #99605
Description
Summary
ClickHouse does not support the VALUES clause as a derived table in a FROM clause. This is a widely-used SQL feature that allows constructing inline result sets without a real table, supported by PostgreSQL, MySQL, DuckDB, SQL Server, and others. See the PostgreSQL documentation on VALUES lists for reference.
Current Behavior
SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, val);
-- Code: 62. DB::Exception: Syntax errorExpected Behavior
SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, val);
-- Returns:
-- id | val
-- 1 | a
-- 2 | b
-- Usable in JOINs:
SELECT t.id, t.val, o.name
FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, val)
JOIN other_table o ON t.id = o.id;
-- Usable in CTEs:
WITH mapping AS (
SELECT * FROM (VALUES ('US', 'United States'), ('DE', 'Germany')) AS m(code, name)
)
SELECT * FROM mapping;Workaround
-- ClickHouse-specific values() table function:
SELECT * FROM values('id UInt32, val String', (1, 'a'), (2, 'b'));
-- Or UNION ALL subquery:
SELECT * FROM (
SELECT 1 AS id, 'a' AS val
UNION ALL SELECT 2, 'b'
) AS t;