SOQL - Number in Where Clause throwing "Unknown error parsing query"

Question

I am trying to query a record where an external ID field is equal to a specific value. The external ID is set up as a Number data type to allow for 18 characters with 0 decimal places.

When I go to query for specific records (I'm using the Developer Console Query Editor), I get an "Unknown error parsing query" when the number I am querying against exceeds a certain size. But that size is well below the 18 character limit.

My query is very simple, like the below:

Select Id, External_Id__c From Account Where External_Id__c = 21474836000

It has been inconsistent, where sometimes the query will work with a given value and then the next time it will give an error, but the place where it stops working seems to be around this 21474836000 number. Unfortunately, the values I am querying for are closer to 47800000000000.

Is this a SOQL limitation I just didn't know about or am I missing something? How can I query for external IDs beyond this limitation?

Answer 1

Some simple testing makes me think that this is an integer limit issue.

A signed, 32-bit integer has a max value of 2,147,483,647. You should be fine if you're trying to filter using an integer literal in the signed 32-bit int range, but as soon as you go beyond that, you'll get an error (and the error you get changes based on how/where the query is executed).

• In the Query Editor of the developer console, you'll get
  • Unknown error parsing query (exeucting the query)
  • ERROR at Row1:Column:[number] For input string (Query Plan)
  • Bind variables only allowed in Apex code (Query Plan if you try to use a long literal like 2147483648L)
• In Anonymous Apex (at least through the dev console), you'll get
  • Invalid Integer (if you try to use an integer literal like 2147483648)
  • Unexpected token (if you try to use a long literal)

Binding a long literal (WHERE Field = :2147483648L) in Anonymous Apex works (or, at the very least, doesn't cause an error).
Using a decimal literal (WHERE Field = :2147483648.0) works in the Query Editor as well as Anonymous Apex

I don't really have a good setup to test this very rigorously, but it does appear that both a long literal and decimal literal should work with the equality operator. I used the Price field on Asset (Currency(18,0), so not quite a Number(18,0)) as well as a custom field on Asset (which is defined as a Number(4,0)) in my short testing, and got the same records using a regular integer (Field = 1), binded long literal (Field = :1L), and decimal literal (Field = 1.0)

Using a text field for your external Id in this case would probably have been the preferable approach (at least from the SFDC point of view).