How can I track down RedShift throwing errors when aggregating on an otherwise functioning query involving CTEs and UNIONS?

Question

I have a query (actual query is obv larger and more complex) like:

```sql
INSERT INTO my_table
WITH a (SELECT timestamp, count FROM foo),
     b (SELECT timestamp, count FROM bar),
     c (
       SELECT * FROM foo
       UNION ALL
       SELECT * FROM bar
     )
SELECT timestamp, count FROM c;

SELECT timestamp, SUM(count) FROM my_table;
```

This works fine. However when I change it  because I'm investigating the query results to:

```sql
WITH a (SELECT timestamp, count FROM foo),
     b (SELECT timestamp, count FROM bar),
     c  (
       SELECT * FROM foo
       UNION ALL
       SELECT * FROM bar
     )
SELECT timestamp, SUM(count)
FROM c
GROUP BY 1;
```

It throws a Not Implemented error with code 1001. Now this is 100% a RedShift bug because it seems to not be able to track the types across my query, but my actual question is all I can see in `STL_ERROR` is:

```
file,linenum,context,error
../src/sys/pg_utils.cpp,2218,Not implemented,'value' - Ill formed PARAM_EXEC in expression                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
../src/sys/pg_utils.cpp,2218,Not implemented,'value' - Ill formed PARAM_EXEC in expression                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
```

In `STV_RECENT` it doesn't even appear. How can I break down what RedShift is doing to track down the point where it's failing exactly? The *only* change to my original query is attempting to do a `... SUM ... GROUP 1` on the original query causing it to fail.

Answer

Timestamp and Count are reserved words. It is recommended to avoid using these as column names. Else use double quotes around them in your code as shown below. Note that I have removed the table "a" and table "b" as you were not using them anyways

```
WITH c as (
       SELECT "timestamp", "count" FROM foo
       UNION ALL
       SELECT "timestamp", "count" FROM bar
     )
SELECT "timestamp", SUM("count")
FROM c
GROUP BY 1;
```

How can I track down RedShift throwing errors when aggregating on an otherwise functioning query involving CTEs and UNIONS?

관련 콘텐츠