Query JSON with SQL: Big number changes last digit in query

[Reboon]

Hi everyone

I noticed the following problem, which caused me many hours of debugging.
The "Query JSON with SQL" logic converts the number 20210113092221445 to 20210113092221444 for no apparent reason.

This seems to be a serious bug which can easily lead to wrong numbers and wrong matching of records (in my case I used "MAX" to find the max value in a table and 20210113092221445 always returned as 20210113092221444). Most likely it is some float conversion issue.

You can try it yourself with this example here:

select max(cast(20210113092221445 as varchar(255))) as A,
max(20210113092221445) as B,
cast(20210113092221445 as varchar(255)) as C,
20210113092221445 as D,
'20210113092221445' as E

[Kabirdas]

Hey there @Reboon! It looks like the issue here is that 20210113092221445 is not a safe integer in JavaScript, i.e. it is larger than the max integer size (2^53 - 1) and needs to be cast as a BigInt to be handled properly. While this is technically a SQL query we are using AlaSQL under the hood which is likely why the answer still lies in some JS! Notice the following: