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 7:43	Modified:	24 Jul 2008 17:28
Reporter:	Carsten Breum
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	5.1.4 (and earlier)	OS:	Microsoft Windows
Assigned to:		Target Version:

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.