Bug #62323 Documentation of 'NaN'
Submitted: 1 Sep 2011 14:01 Modified: 2 Jul 2018 13:59
Reporter: Jens Rantil Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[1 Sep 2011 14:01] Jens Rantil
Description:
I've been having a really hard time to find any documentation whatsoever on how MySQL handles 'NaN'. The only source in I've found among official documentation in the comment section of http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html

I have for example figured out that myself that:
=====================
mysql> CREATE TABLE `testfloat` (`val` float NOT NULL);
Query OK, 0 rows affected (0.01 sec)
=====================
Prepared statement:
=====================
        final Transaction transaction = HibernateUtil.getSession().beginTransaction();
        Connection conn = HibernateUtil.getSession().connection();
        PreparedStatement stmnt = conn.prepareStatement("INSERT INTO testfloat(val) VALUES (?)");
        stmnt.setFloat(1, Float.NaN);
        stmnt.execute();
        transaction.commit();
=====================
will yield
=====================
mysql> SELECT * FROM testfloat;
+-----+
| val |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)
=====================
while
=====================
mysql> CREATE TABLE `testfloat` (`val` float NULL);
Query OK, 0 rows affected (0.01 sec)
=====================
Prepared statement:
=====================
        final Transaction transaction = HibernateUtil.getSession().beginTransaction();
        Connection conn = HibernateUtil.getSession().connection();
        PreparedStatement stmnt = conn.prepareStatement("INSERT INTO testfloat(val) VALUES (?)");
        stmnt.setFloat(1, Float.NaN);
        stmnt.execute();
        transaction.commit();
=====================
will yield
=====================
mysql> SELECT * FROM testfloat;
+------+
| val  |
+------+
| NULL |
+-----+
1 row in set (0.00 sec)
=====================

So far, I haven't figured out how to INSERT rows containing NaN:s any other way than through a prepared statement. If this can be done, it should be documented. If you don't want people to work with NaN:s (they can be a mess, I know), that should too be documented.

How to repeat:
Go to http://search.oracle.com/search/search?search.timezone=-120&search_startnum=11&search_endn...
and notice how few results show up showing any information on the matter :-P

Suggested fix:
Add documentation on:
 * How to define NaN:s and what is means.
 * How NaN:s are handled, ie. converted to NULL or 0 etc.
[2 Sep 2011 8:49] Valeriy Kravchuk
Thank you for a valid documentation request. It would be also nice to describe/mention problems with NaN etc at http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-statement-problems.html.
[2 Jul 2018 13:59] Paul Dubois
Posted by developer:
 
There does not appear to be an explicit development policy regarding this behavior.