MySQL Bugs: #9262: Floating point numbers boundary conditions (MinValue/MaxValue)

Bug #9262	Floating point numbers boundary conditions (MinValue/MaxValue)
Submitted:	18 Mar 2005 4:12	Modified:	22 Mar 2005 18:41
Reporter:	Chris Guidry	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.4.20163	OS:	Windows (WinXP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Not sure if this is a Connector/Net issue or if it is a DB issue, but an error occurs using the MySqlDataReader to read double.MaxValue, double.MinValue, float.MaxValue, float.MinValue, although they are accepted by MySqlCommands for INSERT/UPDATE operations.

How to repeat:
Table:
CREATE TABLE TestData
{
  MaxDouble DOUBLE,
  MinDouble DOUBLE,
  MaxFloat FLOAT,
  MinFloat FLOAT
};

.Net:
// INSERT a record to TestData with the floating point boundaries
const string InsertQuery = 
@"
INSERT TestData (MaxDouble, MinDouble, MaxFloat, MinFloat)
VALUES (?maxDouble, ?minDouble, ?maxFloat, ?minFloat)
";
MySqlCommand cmd = new MySqlCommand(InsertQuery);
...
cmd.Parameters.Add("?maxDouble", double.MaxValue);
cmd.Parameters.Add("?minDouble", double.MinValue);
cmd.Parameters.Add("?maxFloat", float.MaxValue);
cmd.Parameters.Add("?minFloat", float.MinValue);
...
cmd.ExecuteNonQuery();

....

// SELECT from TestData
const string SelectQuery = "SELECT * FROM TestData";
MySqlCommand cmd = new MySqlCommand(SelectQuery);
...
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read(); // will throw an OverflowException

Suggested fix:
I only dug into the code a little, but it looks like the double and float values are converted to their string representations before being sent over the wire.  Would there be a way to send their binary representations instead (using BitConverter.GetBytes(...))?

Also, there are some great unit tests in MySql Connector/Net, but you might want to add a full .Net data-type boundary condition test that would confirm the Min, Max, and default values for all primitive types to help catch these.  The tests in DataTypeTests.cs seem to check small-ish values for things.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Chris

This really isn't a bug.  Floating point, by it's very nature, is not precise.  Try this with .NET (or Java):  double.Parse( double.MaxValue.ToString() )

You'll see that an exception is thrown.  With different floating point libraries involved (the MySql server is compiled with one and .NET has a different one), they simply do not agree on what is the largest or smallest double or float value.  You can do double.MaxValue.ToString("R") and that will give you a string that can be parsed back in.  However, if you send that value to MySQL, it converts it to a value that is too large to be parsed back.

The same is not quite true for float value.  Float max values get changed when going to MySQL but the value they are changed to is still parsable by the Single data type.  This effectively makes it impossible to write a unit test case to test the edge conditions with double and float.