JSON UInt64 as string

[tobia]

Is there a reason why Int64 and UInt64 are output as string in JSON?

 :) select toUInt64(1) as n format JSONEachRow;
 {"n":"1"}

 :) select toUInt32(1) as n format JSONEachRow;
 {"n":1}

As far as I can tell, JSON does not put a limit on the size of a number, and most implementations use double precision floats, which are a superset of 53 bit integers (which should be enough for most needs, even if the field is 64 bit wide in CH.)

This stringification causes problems in the frontend code, where one needs to call parseInt() on almost every value returned by queries. Can I override this setting somewhere?

[zhouhong]

I Found "IDataTypeNumber.h" had wrote like this :
template <> inline void IDataTypeNumber::serializeTextJSON(const IColumn & column, size_t row_num, WriteBuffer & ostr) const
{
writeChar('"', ostr);
serializeText(column, row_num, ostr);
writeChar('"', ostr);
}

I guess that's the reason. And they would like to do that for themselves.

[alexey-milovidov]

As far as I can tell, JSON does not put a limit on the size of a number, and most implementations use double precision floats, which are a superset of 53 bit integers (which should be enough for most needs, even if the field is 64 bit wide in CH.)

We need to make JSON format compatible with JavaScript.
JavaScript represents numbers as double. It is suitable for some, but not for all UInt64 numbers.
So, we have following variants:

1. Always place UInt64 values in quotes.
2. Never place UInt64 values in quotes.
3. Write values that fit in double as number and other values in quotes.

We are using first variant.

Second variant is inappropriate because it will lead to silent loss of precision in most implementations. But it is suitable for some sophisticated JSON implementations.

Third variant is inappropriate (in my sense) too, because it will lead to surprising, non obvious behaviour. And to have full support of UInt64, you need to call parseInt for some numbers, just as in first variant - so, this variant has no advantages for correct implementations of readers.

[tobia]

Just to be obvious, Uint32 holds values from 0 to ~ 4 billions, while UInt64 goes from 0 to ~ 18 billion billions.

A JavaScript number is a double precision float, so it can store numbers from 0 to 2⁵³ (~ 9 million billions) with exact precision, and subsequent numbers up to the UInt64 range (and beyond) with some loss in precision.

For example the largest UInt64 number, 18 billion billions, is represented as a JavaScript double with an error of ± 0.00000000000001%. All the other numbers have a smaller error (or no error at all if they are < 9 million billions.)

I would argue that for 99.99% of applications, especially JavaScript front-end applications, this level of precision is enough.

My opinion is that it would be much more useful to have UInt64 and Int64 values without quotes by default, which includes all the aggregate results such as count(), and accept the very rare and small loss of precision that happens when one is dealing with numbers greater than 9 million billions in JavaScript.

In the remaining cases (if any) in which the developer really needs to perform exact integer arithmetic on large numbers on the client side, they can always use toString() on the SQL query to transmit the value as a string and use some unlimited precision numeric library on the JavaScript side.

[alexey-milovidov]

I agree, but only partially.
UInt64 is frequently used for hash codes and identifiers. In that cases, loss of precision is inappropriate.

Maybe better to add setting, which will allow to write UInt64 without quotes?

[tobia]

Maybe better to add setting, which will allow to write UInt64 without quotes?

I think that would be very appropriate.

[ludv1x]

PR #126 adds new option that switches "quoted" and unquoted formats of *Int64 numbers.