1

I have a issue with round, trunc function from BigQuery standard query .

enter image description here

I expected at 3953.7, 3053.67, 3053.667. f1_, f2_ result is different. It is a bug??

enter image description here

I expected at 3.195, 3.195, 3.1955, 3.1965, 3.1945. f1_, f3_ result is different. Is it my fault?

Bohee Choi
  • 33
  • 2
  • 5

2 Answers2

5

The ROUND() is used to round a numeric field to the nearest number of decimals specified. There is a limitation of floating point values. They can only represent binary values, but cannot precisely represent decimal digits after the decimal point (see here).

In case of SELECT ROUND(3053.665,2) you receive: 3053.66, you can overcome it by using: ROUND(value + 0.005, 2), which allows you to receive 3053.67.

Anyway, if you want to take care about precise decimal results, you should use the NUMERIC type. The following query gives results that you expect:

SELECT ROUND(3953.65,1), ROUND(numeric '3053.665',2), ROUND(numeric '3053.6665',3)

TRUNC(), the following query gives results that you expect:

SELECT TRUNC(3.1955,3), TRUNC(numeric'3.195',3), TRUNC(3.1955,4), TRUNC(numeric '3.1965',4), TRUNC(3.1945,4)

BigQuery parses fractional numbers as floating point by default for better performance, while other databases parses fractional numbers as NUMERIC by default. This means the other databases would interpret TRUNC(3.03,2) in the same way BigQuery interprets TRUNC(numeric '3.03',2).

I hope it will helps you.

aga
  • 3,790
  • 3
  • 11
  • 18
1

This is due to the fact that, in BigQuery, digits are stored as floating point values by default.

You can fin more information about how these work in Wikipedia, but the main idea is that some numbers are not stored as they are but as the closest approximation its representation allows. If instead of 3.03 it is internally represented as 3.0299999999..., when you trunc it the result will be 3.02.

Same thing happens with round function, if 3053.665 is internally stored as 3053.6649999999..., the result of rounding it will be 3053.66.

If you specify it to be stored as NUMERIC, it then works as "expected":

 select trunc(numeric '3.195', 3)

gives as result 3.195

You can find more information about Numeric Types in the official BigQuery Documentation.

Albert Albesa
  • 419
  • 3
  • 6