[sqllab] query result is not accurate

[luciuschina]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.18.5
0.17.6

Python version

2.7

Expected results

return the accurate bigint field

Actual results

the last two bits in the bigint is wrong

Steps to reproduce

I use sqllab to query mysql tables. My sql statement is :
select * from ec_order_main where main_order_no = 401501121320267725.
"main_order_no" is the primary key of ec_order_main table,its data type is BIGINT.

Sqllab can find the record,but the 'main_order_no' is 401501121320267700. I expected the "main_order_no" should be "401501121320267725" instead of "401501121320267700".
Am I missing something and how can I address this issue?

[xrmx]

Which database is this? Have you tried doing the same query with the database cli?

[luciuschina]

@xrmx This is mysql, and I use other clients, it is OK!

[xrmx]

Next step is to try the query in pandas, if it's still wrong here then the issue is in pandas.

[luciuschina]

OK , I am not very familiar with python and pandas, but I will have a try.

[xrmx]

This looks like a good start https://stackoverflow.com/a/25030617/4470501

[luciuschina]

A colleague of mine is familiar with python and pandas，he tested this query in pandas：

So it seemed that the pandas has no problems。

[xrmx]

Thanks for checking! Maybe is this just javascript? On my browser console Number.MAX_SAFE_INTEGER < 401501121320267725 returns true and Number.isSafeInteger(401501121320267725) false.

Reference https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Number/MAX_SAFE_INTEGER

[luciuschina]

So is this a bug ？

[mistercrunch]

That is a weird bug. :/

[xrmx]

If it's only a visualization bug the sql lab api can cast everything to string?

[mistercrunch]

Is the value ok in the json ?

[luciuschina]

@mistercrunch I use fiddler to get the result json。It seems the value in json is OK。

Although，I set the type of main_order_no filed as VARCHAR in superset, it still doesn't work ! But when I change the field type from BIGINT to VARCHAR in mysql, It can be displayed correctly.

[mistercrunch]

Seems like a JS/JSON.parse limitation:
https://stackoverflow.com/questions/18755125/node-js-is-there-any-proper-way-to-parse-json-with-large-numbers-long-bigint

[rumbin]

Most simple way to reproduce:

SELECT 401501121320267725 as test

in SQLLab

[dmigo]

here it states that any number longer than 15 digits is going to be imprecise in javascript.
You can try in console:

let b = 123456789012345
b // 123456789012345
b = 12345678901234567
b // 12345678901234568

12345678901234567 is "corrected" to 12345678901234568

[jargnar]

@mistercrunch true, also probably much more fundamental

JS stores numbers as double precision 64 bit format IEEE 754 values. (http://www.ecma-international.org/ecma-262/5.1/#sec-8.5)

I'm guessing this needs to be wrapped in some kind of string format.

[mistercrunch]

Seems like this is what this does:
https://www.npmjs.com/package/json-bigint-string

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 264 days. Feel free to comment and request for this issue to be reopened.