The result looks a bit weird to me. I was expecting something like 2.2001, but got this this instead. Has this something to do with how the float values are interpreted?
Query:
WITH ABC AS (
SELECT 1 AS A
UNION ALL
SELECT 1.1
UNION ALL
SELECT 0.1
UNION ALL
SELECT 0
UNION ALL
SELECT 0.0001
)
SELECT SUM(A) FROM ABC
When BigQuery sees literals like 1.1, 0.1, and 0.0001, it stores them as FLOAT64 (64-bit IEEE 754 floating point). The problem is that most decimal fractions cannot be represented exactly in binary floating point.
For example, 0.1 in binary is actually something like 0.1000000000000000055511151231257827021181583404541015625... — it never terminates cleanly, so the system stores the closest approximation it can, and those tiny errors accumulate across your SUM.
thanks @Sujai_Shukla, would it be possible for you to share the official source of this information?
I’m interested in knowing more about this!!
@rajatanand … let me suggest that you do a Google search using:
”numeric representation in computers introduces errors”
I found that the articles returned there give a good selection of descriptions of this common puzzle.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
