By using AWS re:Post, you agree to the Terms of Use

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

0

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

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:

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.

  • timestamp is a reserved word so it might be having trouble trying to associate the position alias of "1" with the reserved word "timestamp". Try using double quotes around the column name and/or explicitly using the column name in the group by rather than the position alias.

asked 3 months ago55 views
1 Answers
0

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;
answered 3 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions