SQL Error [500310] [42846]: [Amazon](500310) Invalid operation: could not convert type "unknown" to numeric because of modifier;

Question

How to fix the error in the title querying AWS Redshift Data in DBeaver.

Individual selects work, but if I try to do a union all it fails, even if all datatypes are declared. Same with a join.

Table with 4 columns postl_st_cd (varchar), cnt (integer), var1 (boolean), var2 (varchar)

Example1:

select 
postl_st_cd::varchar as state,
(case when cnt >=1 and (var1 is true or var2 in('A','B','C','D')) then 1 else 0 end)::integer as cnt_rc
from Table
union all
select 
postl_st_cd::varchar as state,
0::integer as cnt_rc
from Table

Example2:

select 
postl_st_cd::varchar as state,
(case when cnt >=1 and (var1 is true or var2 in('A','B','C','D')) then 1 else 0 end)::integer as cnt_rc
from Table


select 
postl_st_cd::varchar as state,
0::integer as cnt_rc
from Table

Example1 results in an error.

Example2 queried separately works fine.

Full Error Message:

com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: could not convert type "unknown" to numeric because of modifier;
    at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
    at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.moveThroughMetadata(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.getNoData(Unknown Source)
    at com.amazon.redshift.client.PGClient.directExecuteExtraMetadataWithMessage(Unknown Source)
    at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
    at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
    at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

Answer 1

As nbk suggested it appears to be a bug.

I reached out to AWS support and they indicated they have received similar feedback and are doing a "deep dive" to determine if there is anything they need to fix.

Edit to add: One potential work around mentioned by support is to make sure the original view/table has nulls casted to the correct datatype. Unfortunately, in my scenario I don't have access to edit the original table so I have forwarded to the relevant party.