0

What is the largest precision error on a positive integer greater than 2^53 stored in a double? In other words, for all positive integers from 2^53+1 to max(double) what would be the largest precision difference between the actual integer and what the double value would be.

A little background on why I'm asking: I'm reading SNMP counters from a pubsub and writing them to BigQuery. The counters are UINT64 but BigQuery's data type for integer is INT64. So I'm currently using FLOAT in my BQ schema. It would not be a problem for my use case if the counters were off by some value in the hundreds. Unless there is another alternative on the BQ side (that doesn't involve using strings!)

Melissa Stockman
  • 339
  • 3
  • 10

2 Answers2

1

2018 update: BigQuery now has the NUMERIC type.

From what I've gathered so far:

  • You should be able to store UINT64 as INT64 without any loss of precision.
  • Arithmetic on 64-bit values doesn't depend on whether they're interpreted as signed or unsigned (except for division and modulo).
  • Comparisons: Instead of (A > B) you can do (A - B > 0).

Historically Java hasn't handled UINT64, and there are documented ways to work around this: https://www.nayuki.io/page/unsigned-int-considered-harmful-for-java

Would be interesting to know what cases can't be handled with these workarounds!

In BigQuery:

#legacySQL
SELECT 9223372036854775818
-9223372036854775798    

#legacySQL
SELECT 9223372036854775807
9223372036854775807

#legacySQL
SELECT 9223372036854775818 - 9223372036854775807
11
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • 1
    There are also a few other operations that differentiate signed and unsigned types [Which arithmetic operations are the same on unsigned and two's complement signed numbers?](http://stackoverflow.com/q/21475286/995714), [Difference between signed and unsigned on bitwise operations](http://stackoverflow.com/q/13224273/995714). And there are [unsigned helper functions in Java8](http://stackoverflow.com/q/25556017/995714) – phuclv Jan 07 '17 at 07:08
1

The gap between floating-point numbers increases as the numbers get bigger.

Regarding the whole range of double-precision floating-point numbers:

  • The largest finite value is (2 − 2−52)×21023 (around 1.8×10308).
  • The second largest value is (2 − 2−51)×21023.
  • Their difference is 2971 (about 2.0×10292).
  • Hence by rounding-to-nearest, the maximum error is half of the step size, so the answer to your question is 2970 (about 1.0×10292).

Regarding the range of double-precision floating-point numbers up to 264:

  • 264 can be represented exactly in DP FP.
  • The previous (smaller) number is 18446744073709549568.
  • Their difference is 2048.
  • Hence when converting a uint64 to float64, the maximum rounding error is ±1024.
Nayuki
  • 17,911
  • 6
  • 53
  • 80