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?
Advertisement
Answer
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.