WITH cte AS [NOT] MATERIALIZED (SELECT ...) SELECT * FROM cte #53449
Description
Use case
Add keyword for materialization of CTE expressions
Describe the solution you'd like
WITH cte AS [NOT] MATERIALIZED (SELECT ...) SELECT * FROM cte
Describe alternatives you've considered
hack with groupArray and scalar alias
WITH
(
SELECT groupArray(number)
FROM numbers(10000)
WHERE NOT sleep(1)
) AS x
SELECT count()
FROM
(
SELECT *
FROM numbers(100)
)
WHERE number IN
(
SELECT arrayJoin(x)
)
For multiple columns
WITH
(
SELECT groupArray(tuple(number, 2))
FROM numbers(10000)
WHERE NOT sleep(1)
) AS x
SELECT count()
FROM
(
SELECT *
FROM numbers(100)
)
WHERE number IN
(
SELECT arrayJoin(x).1
)
Additional context
PostgreSQL documentation https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7
Related #23539 (comment)
Related #21992