AWS Redshift typical error and potential root cause

1, Full join issue: When use full join, the below join condition should not occur:

1, OR statement
2, an obvious false or true condition, like 1 = 0 or 1=1
3, the datatype of the join column should not be timestamp, this case is very common in pcp join, when use the date type column as the join key. 

2, ERROR: 42846: could not convert type "unknown" to numeric because of modifier

When the datatype of a column is supposed to be numeric in a UNION ALL context(basically there are more than three sub query parts), 
If there exists NULL value for some sub query, it is needed to cast the NULL value to the numeric datatype.

3, Error info: [Assert error]

This means the Redshift performance is bad, usually multiple large table join, more CTAS is needed.

4, Error info: query is automatically killed saying cancelled on user's request

5, Error info: could not devise a query plan for the given query

The issue is prabably due to there is an join condition in the full join clause. The join column is report_date, which is a date datatype, but there is function applied to report_date, which convert the datatype to timestamp. Unluckily redshift seems not support this datatype in the join condition.

To solve this issue, the join column could be casted to date again, like below:
DATEADD(MONTH,-12,CUR.REPORT_DATE) :: DATE = RESULT_PCP.REPORT_DATE ::DATE

6, Tips:

a, Avoid to use the string concatenation when doing a join, create a temporary table for that particular column.

b, Sort key performance
There is no significant improvement on a table about 10 millon rows. Maybe there is a difference when testing on a larger data set or table applied with column compression.

PS: Redshift related documents

https://blogs.aws.amazon.com/bigdata/post/Tx31034QG0G3ED1/Top-10-Performance-Tuning-Techniques-for-Amazon-Redshift
http://docs.aws.amazon.com/redshift/latest/dg/c_Byte_dictionary_encoding.html
http://docs.aws.amazon.com/redshift/latest/dg/welcome.html
https://aws.amazon.com/redshift/pricing/