Repository: googleapis/python-bigquery

Issue:
Cannot insert NaN and non-finite float values using Client.insert_rows()
Trying to insert a NaN using `Client.insert_rows` fails. I tracked it down to 

```
def _float_to_json(value):
    """Coerce 'value' to an JSON-compatible representation."""
    return value if value is None else float(value)
```

which should return "NaN" to be compatible with the REST API. Currently it returns an actual nan float value. Probably similar issues occur with other non-finite floats.

#### Environment details

  - OS type and version:
  - Python version: `Python 3.6.8`
  - pip version: `pip 21.1`
  - `google-cloud-bigquery` version: `2.20.0`

#### Steps to reproduce

  1. Try to insert a NaN value into existing table

#### Code example

```python
from google.cloud.bigquery import Client

client = Client(project='bar')
client.insert_rows(table=client.get_table('foo'), rows=[{'bar': float('NaN')}])
```

#### Stack trace
```
google.api_core.exceptions.BadRequest: 400 POST (REDACTED): Invalid JSON payload received. Unexpected token.
json": {"bar": NaN}, "insertId": "1
```

Discussion:
plamut: `test_insert_rows_from_dataframe()` fails, will investigate how the changes affected Nan-handling there.
plamut: I could not reproduce the failed test even after multiple trials, running it both in isolation and in the entire test suite. It seems weird why there would be no rows in the query results, `NaN` conversions didn't seem to play the role here.

```
        rows = list(
            bigquery_client.query(
                "SELECT * FROM `{}.{}.{}`".format(
                    table.project, table.dataset_id, table.table_id
                )
            )
        )

        sorted_rows = sorted(rows, key=operator.attrgetter("int_col"))
        row_tuples = [r.values() for r in sorted_rows]
        expected = [...]

>       assert len(row_tuples) == len(expected)
E       AssertionError: assert 0 == 6
E        +  where 0 = len([])
E        +  and   6 = len([(1.11, True, 'my string', 10), (2.22, False, 'another string', 20), (3.33, False, 'another string', 30), (4.44, True, 'another string', 40), (5.55, False, 'another string', 50), (6.66, True, None, 60)])
```

In any case, re-running the CI checks made them pass, and the same result was consistently reproduced locally.
jimfulton: > I could not reproduce the [failed test](https://source.cloud.google.com/results/invocations/94fdbf31-960d-4ee6-8c93-ab10c3413fdc/targets/cloud-devrel%2Fclient-libraries%2Fpython%2Fgoogleapis%2Fpython-bigquery%2Fpresubmit%2Fprerelease-deps-3.8/log) even after multiple trials, running it both in isolation and in the entire test suite. It seems weird why there would be no rows in the query results, `NaN` conversions didn't seem to play the role here.
> 
> ```
>         rows = list(
>             bigquery_client.query(
>                 "SELECT * FROM `{}.{}.{}`".format(
>                     table.project, table.dataset_id, table.table_id
>                 )
>             )
>         )
> 
>         sorted_rows = sorted(rows, key=operator.attrgetter("int_col"))
>         row_tuples = [r.values() for r in sorted_rows]
>         expected = [...]
> 
> >       assert len(row_tuples) == len(expected)
> E       AssertionError: assert 0 == 6
> E        +  where 0 = len([])
> E        +  and   6 = len([(1.11, True, 'my string', 10), (2.22, False, 'another string', 20), (3.33, False, 'another string', 30), (4.44, True, 'another string', 40), (5.55, False, 'another string', 50), (6.66, True, None, 60)])
> ```
> 
> In any case, re-running the CI checks made them pass, and the same result was consistently reproduced locally.

That failed for me too yesterday. I'll create a flaky-test issue.

Eventual consistency?
plamut: @jimfulton Can't say, it's the first time I'm seeing this failure in 2+ years. Something on the backend might have changed related to when the streamed data is available. It should be as soon as after a [few seconds](https://cloud.google.com/bigquery/streaming-data-into-bigquery#dataavailability), but even this might be too late in certain circumstances, just enough for the test to fail.
tseaver: @plamut, @jimfulton For the flaky test, the `test_utils.retry.RetryResult` wrapper might help.  Something like:

```python
from test_utils.retry import RetryResult

def _list_query(client, query):
    return list(client.query(query)

def _check_result(result, expected_length)
     return len(result) == expected_length

retry = RetryResult(functools.partial(_check_result, expected_length=len(expected))
retry(_list_query)(bigquery_client, query)
```

Patch:
diff --git a/google/cloud/bigquery/_helpers.py b/google/cloud/bigquery/_helpers.py
index 7602483c..77054542 100644
--- a/google/cloud/bigquery/_helpers.py
+++ b/google/cloud/bigquery/_helpers.py
@@ -17,6 +17,7 @@
 import base64
 import datetime
 import decimal
+import math
 import re
 
 from google.cloud._helpers import UTC
@@ -305,7 +306,12 @@ def _int_to_json(value):
 
 def _float_to_json(value):
     """Coerce 'value' to an JSON-compatible representation."""
-    return value if value is None else float(value)
+    if value is None:
+        return None
+    elif math.isnan(value) or math.isinf(value):
+        return str(value)
+    else:
+        return float(value)
 
 
 def _decimal_to_json(value):


Test patch:
diff --git a/tests/unit/test__helpers.py b/tests/unit/test__helpers.py
index 0ac76d42..c62947d3 100644
--- a/tests/unit/test__helpers.py
+++ b/tests/unit/test__helpers.py
@@ -656,9 +656,24 @@ class Test_float_to_json(unittest.TestCase):
 
         return _float_to_json(value)
 
+    def test_w_none(self):
+        self.assertEqual(self._call_fut(None), None)
+
     def test_w_float(self):
         self.assertEqual(self._call_fut(1.23), 1.23)
 
+    def test_w_nan(self):
+        result = self._call_fut(float("nan"))
+        self.assertEqual(result.lower(), "nan")
+
+    def test_w_infinity(self):
+        result = self._call_fut(float("inf"))
+        self.assertEqual(result.lower(), "inf")
+
+    def test_w_negative_infinity(self):
+        result = self._call_fut(float("-inf"))
+        self.assertEqual(result.lower(), "-inf")
+
 
 class Test_decimal_to_json(unittest.TestCase):
     def _call_fut(self, value):