Interval values with month or year parts are not supported

0

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

AJP
asked 9 months ago34 views