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,350 points
1,733 views

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
• 56,520 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,090 points
1,810 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,090 points
246 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,090 points
126 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,560 points
768 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,090 points
26 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,090 points
34 views
0 votes
1 answer
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
• 56,520 points
139 views