Error in Adding LIMIT fixes Invalid digit Value N in Amazon Redshift

Question

I have a standard listings table on Redshift table with all varchars (due to loading into database)

This query (simplified) gives me error:

with AL as (
  select
    L.price::int as price,
  from listings L
  where L.price <> 'NULL'
    and L.listing_type <> 'NULL'
)
select price from AL
where price < 800

and the error:

  -----------------------------------------------
  error:  Invalid digit, Value 'N', Pos 0, Type: Integer 
  code:      1207
  context:   NULL
  query:     2422868
  location:  :0
  process:   query0_24 [pid=0]
  -----------------------------------------------

If I remove the where price < 800 condition, the query returns just fine... but I need the where condition to be there.

I've also checked the number validity of the price field and all look good.

After playing around, this actually makes it work, and I can't quite explain why.

with AL as (
  select
    L.price::int as price,
  from listings L
  where L.price <> 'NULL'
    and L.listing_type <> 'NULL'
  limit 10000000000
)
select price from AL
where price < 800

Note that the table has far less records than the number stated in limit.

Can anyone (possibly from the Redshift engineer team) explain why this is the way it is? Possibly something to do with how the query plan being executed and parallelized?

Comments

Exact same issue here. I have no idea why but adding a LIMIT as you did resolved the issue for me as well

Answer 1

This could be expressed simply as:

SELECT TOP 10 field1, field2
FROM table1
INNER JOIN table2
ON table1.field3::int = table2.field3
ORDER BY table1.field1 DESC

Removing the explicit cast to ::int solved a similar error for me.

Meanwhile, postgresql locally requires the "::int" to work.

For what it's worth, my local postgresql version is PostgreSQL 9.6.4 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit