Error (500310) Invalid Operation - When joining on WITH clause

Question

Hi,  
I'm doing a simple join between a WITH clause that produces some dates and another table -   
and I'm getting the following error:

```
[2018-04-15 15:21:27] [0A000][500310] [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;
[2018-04-15 15:21:27] java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;
[2018-04-15 15:21:27] 	at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
[2018-04-15 15:21:27] 	at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
```

I've added a small script to reproduce the problem.  
What am I doing wrong?  
Thanks in advance !

```
-- drop table if exists temp_users
create table temp_users (
		id INT,
		name varchar,
		created_at date
);

insert INTO temp_users
(id, name, created_at)
VALUES
( 1, 'Anna', now()::date)
, ( 2, 'Charlie', now()::date - interval '1 month')
, ( 3, 'Dave', now()::date - interval '2 month')
, ( 4, 'Eddie', now()::date - interval '3 month')
, ( 5, 'Fiona', now()::date - interval '4 month') ;

select * from temp_users;

with generated_dates as (
	SELECT date_trunc('mon', current_date) - (n || ' months')::interval as generated_date
	from generate_series (1, 3) n
	order by generated_date desc
)
select
	u.name as metric,
	generated_dates.generated_date as time
FROM generated_dates
JOIN temp_users as u ON date_trunc('mon', u.created_at) = generated_dates.generated_date
```

Edited by: Eligo on Apr 15, 2018 6:28 AM

Answer

As @toebs2 says, this function is not supported. You can often use the ROW_NUMBER() function over an internal table to generate a series of data points instead.

```
WITH generated_dates
AS (SELECT DATEADD('month', -n, DATE_TRUNC('month', CURRENT_DATE)) AS generated_date
    FROM (SELECT ROW_NUMBER() OVER () AS n FROM stl_scan LIMIT 3) n
    ORDER BY generated_date DESC
    )
SELECT u.name AS metric
    ,generated_dates.generated_date AS TIME
FROM generated_dates
INNER JOIN temp_users AS u ON date_trunc('mon', u.created_at) = generated_dates.generated_date
;
```

Answer

Redshift does not support the function generate_series().  
  
https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html

Error (500310) Invalid Operation - When joining on WITH clause

Relevanter Inhalt