Cannot reference column from CTE in a set returning function about duckdb

[kryonix]

I don't want to run the expensive computation multiple times. A CTE seemed like the right choice [...]

A non-materialized CTE will not do what you are trying to achieve. You will have to use a materialized CTE:

WITH t1(a) AS MATERIALIZED [...]

[redneb]

An additional experiment: Since it is complaining that the second argument of generate_series is a VARCHAR, let's try to explicitly cast it to an int:

> WITH t1(a) AS (SELECT 7) SELECT * FROM t1, generate_series(1, a::int);
Conversion Error: Could not convert string 'a' to INT32
LINE 1: ... SELECT * FROM t1, generate_series(1, a::int);
                                                  ^

So it thinks that the column name, i.e. a, is the string 'a'!

[redneb]

Also:

> SELECT * FROM (SELECT 7 AS a) t1, generate_series(1, a);
Binder Error: No function matches the given name and argument types 'generate_series(INTEGER, VARCHAR)'. You might need to add explicit type casts.
	Candidate functions:
	generate_series(BIGINT)
	generate_series(BIGINT, BIGINT)
	generate_series(BIGINT, BIGINT, BIGINT)
	generate_series(TIMESTAMP, TIMESTAMP, INTERVAL)
	generate_series(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INTERVAL)

LINE 1: SELECT * FROM (SELECT 7 AS a) t1, generate_series(1, a);
                                          ^

So it is not specific to CTEs as I originally thought.

[kryonix]

This is a known issue currently. See also: #10927. It is currently not possible to use columns of the (here: implicit) lateral join as input parameters.

[Mytherin]

As mentioned this is currently an expected restriction - but the error message is very confusing. I've added a more clear error message in #11436.

[ggPeti]

As a workaround, I used unnest(range(...)).