CODEWITHSUNDEEP
sqlgoogle-bigquery
Niklas9
Niklas9

Reputation: 9396

BigQuery producing negative (!) zero when numeric 0.0 multiplied with negative numerics/integers

I intend to do a CSV export directly from BigQuery, using the bq CLI (with flag --format=csv). I run into an issue where if a column with a numeric value is multiplied with a negative one, BigQuery returns -0.0 (whatever that means) and this causes issues in the importing system of this CSV file.

Example:

> SELECT (0.0 * -1) AS result;
Row result  
1   -0.0

How can get I this result in 0.0 instead (of course the numeric value 0.0 will in most cases not be 0.0)? Is this a bug in BigQuery?

Upvotes: 1

Views: 831

Answers (1)

T Tse
T Tse

Reputation: 846

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#floating_point_semantics

Arithmetic operators provide standard IEEE-754 behavior for all finite input values that produce finite output and for all operations for which at least one input is non-finite.

This is just standard float behavior. Other programming languages do the same thing:

Python 3.8.2 (default, Jul 16 2020, 14:00:26) 
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> 0.0 * -1
-0.0

Welcome to Node.js v14.12.0.
Type ".help" for more information.
> 0 * -1
-0

Be assured though, -0.0 = 0.0 will still give you TRUE.

Upvotes: 2

Related Questions