Bug #74934 NaN not supported
Submitted: 20 Nov 2014 5:48 Modified: 8 Feb 2022 19:20
Reporter: Guillaume Poulin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:2.0.2 OS:Any
Assigned to: CPU Architecture:Any

[20 Nov 2014 5:48] Guillaume Poulin
Description:
mysql-connector-python doesn't support the python float NaN

How to repeat:
Try inserting NaN

cur.execute('INSERT INTO table_name (float_col) VALUES (%s)', (float('NaN')))

Suggested fix:
change MySQLConverter._float_to_mysql to the following

```
def _float_to_mysql(self, value):
    if not math.isnan(value):
        return None
    return float(value)
```
[20 Nov 2014 7:44] MySQL Verification Team
Hello Guillaume,

Thank you for the report.

Thanks,
Umesh
[28 Jan 2015 11:43] Peeyush Gupta
It might not always be a good idea to convert
float NaN to NULL, for example if the column is 
defined NOT NULL and we convert NaN to NULL while inserting,
the server will throw an error which can be misleading.

NaN values can be taken care at the application level and
appropriate step can be taken(like inserting NULL) there.
[1 Mar 2015 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[18 Feb 2019 12:59] Glyn Astill
The existing behavior is wrong, and needs to be changed to facilitate both the SQL standard and the strong typing used in the client language.

If the client pulls back a Null value it needs to register that the value is known to be Null in the database, not just say it's a NaN because we loose the context of why it is NaN,

From my perspective the correct way to handle this is to have an "is_null" property attached to floating point values pulled back from the database.  For null values the value is "NaN" and the is_null property is True.
[8 Feb 2022 19:20] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.29 release, and here's the proposed changelog entry from the documentation team:

Added support for NaN in the float to MySQL conversion; it's now set to
None.

Thank you for the bug report.