Inconsistency between SQL SDK and Stored Procedure when handling long integer types

Question

Our team is using Cosmos DB as our backend graph storage. Basically we are using JAVA Gremlin SDK to do queries on vertices/edges, and using SQL SDK to create/update vertices, due to the size and step limit of Gremlin API.

I met this issue when trying to update a vertex that contains an attribute that has type Long. In our code we use CosmosDbElement and another flag as input parameter, call sqlClient.executeStoredProcedure(“sp_name”, pk, new Object[] { document, flag }); The stored proc I use simply does some validation then calls collection.replaceDocument() to replace the document.

When I set different value on this attribute I got different behaviors:

1. Attribute value within [-2^53 + 1, 2 ^53 - 1] (MIN_SAFE_INTEGER to MAX_SAFE_INTEGER in JavaScript)

   • Replace with SQL SDK then get by ID: works well
   • Replace with SP then get by ID: works well

2. Attribute value outside above range and within the range of Int64

   • Replace with SQL SDK then get by ID: works well
   • Replace with SP then get by ID: Last 3 digits are rounded. E.g.

             9223372036854775107 -> 9223372036854775000;

             9223372036854775807 -> 9223372036854776000.

             We can get the same rounded value using SQL SDK.

             For most cases g.V([id]) gets the same rounded value. But if the rounded value is outside of the range of Int64, such as 9223372036854775807 -> 9223372036854776000, g.V([id]) throws exception: Cannot create ValueField on non-primitive type BigInteger.

3. Attribute outside of the range of Int64
   • Replace with SQL SDK then get by ID: works well
   • Replace with SP then get by ID: works well

Am I using SP in a right way?

Is it by design a limit or an issue?

How to avoid such precision lost and be able to query the vertex?

Let me know if you need more information, thanks.

Answer 1

The Stored Procedures are written in JavaScript.

As you know, JavaScript uses double-precision floating-point format numbers as specified in IEEE 754, and it can only safely hold numbers between -(2^53 - 1) and 2^53-1 (i.e., 9007199254740991).

So if you use a sproc to replace the value outside of the safe MIN_SAFE_INTEGER to MAX_SAFE_INTEGER in JavaScript, it will get rounded. This is a by-design limitation.

The recommended workarounds are:

1. If you are ok with the precision-loss, then converting the Int64 values into doubles is recommended so that your application handles the conversion/rounding.
2. If you need to maintain the precision then use strings, with the downside being that arithmetic ops cannot be applied server-side.