MySQL Bugs: #33322: Incorrect Double/Single value saved to MySQL database using MySQL Connector for

Bug #33322	Incorrect Double/Single value saved to MySQL database using MySQL Connector for
Submitted:	18 Dec 2007 6:43	Modified:	24 Jul 2008 15:28
Reporter:	Carsten Breum	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.1.4 (and earlier)	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When reading back a stored double value (or single) using the .NET provider the value has less precision than the one stored.

How to repeat:
When executing the following code (assuming that a ‘TestDoubles’ database exist):

        static void Main(string[] args)

        {

            MySqlConnection connection = new MySqlConnection("Database=TestDoubles;Server=;User Id= TEST;Password=TEST");

            connection.Open();

 

            new MySqlCommand("DROP TABLE IF EXISTS TableDoubles;", connection).ExecuteNonQuery();

            new MySqlCommand("CREATE TABLE TableDoubles (Value double NOT NULL)", connection).ExecuteNonQuery();

 

            MySqlCommand cmdInsert = new MySqlCommand("INSERT INTO TableDoubles (Value) VALUES (?Value)", connection);

            cmdInsert.Parameters.Add("?Value", MySqlDbType.Double);

            cmdInsert.Prepare();

 

            cmdInsert.Parameters["?Value"].Value = Math.PI;

            cmdInsert.ExecuteNonQuery();

 

            MySqlCommand cmdRead = new MySqlCommand("SELECT Value FROM TableDoubles", connection);

            cmdRead.Prepare();

            double piRead = 0.0;

            using (MySqlDataReader reader = cmdRead.ExecuteReader())

            {

                foreach (DbDataRecord record in reader)

                {

                    piRead = record.GetDouble(0);

                    break;

                }

            }

 

            if (Math.PI != piRead)

            {

                Console.WriteLine("PI read from database: '{0:E17}'", piRead);

                Console.WriteLine("Actual value of PI:    '{0:E17}'", Math.PI);

            }

            else

                Console.WriteLine("MySQL stores and reads PI correctly");

        }

 

the double value read back has less precision than the one stored.

The output of this program is:

PI read from database: '3,14159265358979000E+000'

Actual value of PI:    '3,14159265358979310E+000'

 

The source of the problem has been identified to be the SQL Connector for .NET, as this on save rounds Double's to approximately 15 decimal digits.

Suggested fix:
The cause of the problem is most likely the following statement in MySqlDouble.cs:

            stream.WriteStringNoNull(v.ToString(CultureInfo.InvariantCulture));

This is not the correct way of converting a floating point number to text - the proper way is:

            stream.WriteStringNoNull(v.ToString("R", CultureInfo.InvariantCulture));

as this guarantees that the floating point number received by Double.Parse is identical to 'v'.

 

 ------------------------

The same problem exist for Single's.

PI read from database: '3,141590120E+000'

Actual value of PI:    '3,141592740E+000'

Hi Carsten and thanks for your report. Your problem is not a result of bug; please check http://msdn2.microsoft.com/en-us/library/system.double(VS.80).aspx for details.
"By default, a Double value contains 15 decimal digits of precision, although a maximum of 17 digits is maintained internally."

I have to disagree. Why are you making our data less precise just because you can, instead of returning the same value as originally stored?

By the way the Core Lab MySql .NET provider does it correctly and the provider for SQL server does it correctly so please take that into account and update the provider as described in the original post as it is the correct way to convert the value to a string.

Verified but the users description is not 100% correct.  Both Connector/Net and CoreLabs drivers work correctly when using prepared statements.  The reason the user was seeing errors using prepared statements is because he had not enabled server side prepared statements.  By default, calling .Prepare is a no-op and  you have to add 'ignore prepare=false' to your connection string to enable server side PS.  Once you do this, it works.

Using non-ps, there problem explained by the user is correct but it will still fail (as of mysqld 5.1) unless you give a display size of double greater than 15.  For the following table it will still fail because mysql will just return 15 digits.

CREATE TABLE test (d DOUBLE);

If you create the column as DOUBLE(25,20) for example (this is non-standard syntax), then up to 25 digits will be returned.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50193

Fixed in 5.0.10, 5.1.7, and 5.2.3+

An entry was added to the 5.0.10, 5.1.7 and 5.2.3 changelogs:

When reading back a stored double or single value using the .NET provider, the value had less precision than the one stored.