Hello Redshift team,
I am trying to understand as to why the query fails when a variable is introduced to it.
The below query expands date range, and returns the list. In the "date_list" CTE table expression, a variable 'n' is referenced, which causes the query to fail with the error "Interval values with month or year parts are not supported"
Query 1:
WITH recursive numbers(n) as
(
SELECT 0 as n
UNION all
SELECT n + 1
FROM numbers n
WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date))
),
date_list as (
SELECT n as id,trunc('1980-01-01'::date + n * interval '1 year') as date_v FROM numbers
)
select * from date_list
However the following query same as above one succeeds when the variable 'n' is substituted with a static value of 10 in the "date_list" CTE table expression.
WITH recursive numbers(n) as
(
SELECT 0 as n
UNION all
SELECT n + 1
FROM numbers n
WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date))
),
date_list as (
SELECT n as id,trunc('1980-01-01'::date + 10 * interval '1 year') as date_v FROM numbers
)
select * from date_list
Can you please help me understand, why year interval executes successfully, when passing a static value over a dynamic variable?
Cluster details:
Version: 1.0.36224
Region: us-east-1
SQL Client: Redshift Query Editor V2
