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.
