Adding LIMIT Hotfixes "Invalid Digit, N Value" error in Amazon Redshift. What for?

Question

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

This query (simplified) gives me an 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 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 delete the condition where price < 800 , the request will return just fine ... but I need the condition to be there.

I also checked the validity of the number of the price field, and everything looks good.

After the game, it actually makes it work, and I can’t 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 

Please note that the table has much fewer entries than the number indicated in the limit.

Can someone (possibly from a Redshift engineer) explain why this is so? Perhaps something has to do with how the query plan is executed and parallelized?

Answer 1

I had a query that could be expressed simply like this:

 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 resolved a similar error for me.

Meanwhile, postgresql locally requires that ":: int" work.

What is it for, my local version of postgresql 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

Answer 2

Load CSV data using NaN in AWS Redshift

I found this post while google searching, but the link above had what I needed. I imported a numeric column with a NaN value that is not supported by redshift.