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

0 votes

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?

Sep 4, 2018 in AWS by bug_seeker
• 15,520 points
10,754 views
Exact same issue here. I have no idea why but adding a LIMIT as you did resolved the issue for me as well

1 answer to this question.

0 votes

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

answered Sep 4, 2018 by Priyaj
• 58,100 points

Related Questions In AWS

0 votes
1 answer

How to connect Amazon RedShift in Apache Spark?

It turns out you just need a ...READ MORE

answered Aug 22, 2018 in AWS by Archana
• 4,170 points
7,428 views
0 votes
1 answer

How to restore a cluster from backup in Amazon Redshift

Amazon Redshift replicates all your data within ...READ MORE

answered Sep 28, 2018 in AWS by Archana
• 4,170 points
1,822 views
0 votes
1 answer

Encrypting the data stored in the clusters of Amazon Redshift

Below is the answer to your question. ...READ MORE

answered Oct 4, 2018 in AWS by Archana
• 4,170 points
1,444 views
0 votes
1 answer

How to execute scheduled SQL scripts in on Amazon Redshift?

I had faced the same problem earlier. ...READ MORE

answered Nov 19, 2018 in AWS by Archana
• 5,640 points
3,925 views
0 votes
1 answer

Scaling in Amazon Redshift

The existing data warehouse cluster remains available ...READ MORE

answered Sep 27, 2018 in AWS by Archana
• 4,170 points
744 views
0 votes
1 answer

Single-Node Cluster & Multiple-Node Cluster

In the single-node cluster, you have a ...READ MORE

answered Sep 27, 2018 in AWS by Archana
• 4,170 points
1,365 views
0 votes
1 answer

AWS Redshift failed to make a valid plan when trying to run a complicated query

-The Original query was an Oracle query ...READ MORE

answered Oct 3, 2018 in AWS by Priyaj
• 58,100 points
1,147 views
+1 vote
2 answers
0 votes
1 answer

Creating A New MySQL User In Amazon RDS Environment

AWS RDS security groups documentation (a common ...READ MORE

answered Jul 18, 2018 in AWS by Priyaj
• 58,100 points
2,015 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP