CODEWITHSUNDEEP
sqlamazon-redshift
Patthebug
Patthebug

Reputation: 4787

Overflow error in Redshift

I'm running the following, pretty simple query on Amazon Redshift:

SELECT member_id,
       COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE member_id in (select memberid from auth.members)
group by member_id

But this is giving me the following error:

An error occurred when executing the SQL command:
SELECT member_id,
       COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE member_id IN (SELEC...

[Amazon](500310) Invalid operation: Overflow (Integer valid range -2147483648 to 2147483647)
Details: 
 -----------------------------------------------
  error:  Overflow (Integer valid range -2147483648 to 2147483647)
  code:      1207
  context:   Value: '2952163128494839000', Character: '8'
  query:     2748804
  location:  :0
  process:   query5_95_2748804 [pid=0]
  -----------------------------------------------;

Execution time: 2.11s
1 statement failed.

I checked and the table members doesn't have a memberid of '2952163128494839000'. I also tried to use CONVERT and CAST functions to convert the memberid to integer but I got the same error.

Why am I facing this error and how do I go about fixing it?

Upvotes: 2

Views: 3872

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Presumably, you have a problem with the member id comparison. I find it highly unlikely that you have enough dates to overflow the count(distinct).

Convert them to strings:

SELECT member_id,
       COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE cast(member_id as varchar(255)) in (select cast(memberid as varchar(255)) from auth.members)
group by member_id

Upvotes: 2

Related Questions