MySQL Bugs: #5225: DOUBLE datatype rounding causes .Net crash and severe data corruption

Bug #5225	DOUBLE datatype rounding causes .Net crash and severe data corruption
Submitted:	26 Aug 2004 13:13	Modified:	6 Feb 2008 19:44
Reporter:	d di (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:		OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	.net crash, data corruption, double precision, floating point, mysqldump, qc, Rounding

Description:
.Net DataSet updates fails on tables with DOUBLE columns that contain specific values.

To be more specific:
0.6666666666666666 fails,
0.666666666666667 works.

It seems that the SELECT * FROM table returns data from the table in a rounded fashion, whereafter .Net, be that through a component like a datagrid or what not, fails to send updated values to the table, because it searches for this rounded value which MySQL won't return any rows on.

It is evident in eg. MySQL-Front and the likes that MySQL seem to return rounded values for the double field. It is also evident that a subsequent SELECT using the rounded value will not return the rows desired.

I've included a simple test case to demonstrate how this affects .Net applications. The test case is simple to modify with a minor workaround to achieve the desired effect, however the shown functionality is used internally in all of .Net's data based components, and as such will probably be a headache for the unexperienced .Net developer wishing to use DOUBLE fields.

How to repeat:
Use the following SQL to create and populate a test database:
-------------------
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE testtable (
	Id TINYINT (3),
	DecayRate DOUBLE NOT NULL,
	PRIMARY KEY(Id)
);

INSERT INTO testtable (Id, DecayRate) VALUES (1, 0.666666666666667);
INSERT INTO testtable (Id, DecayRate) VALUES (2, 0.6666666666666666);
-------------------

Save the following text lines to eg. TestClient.cs and compile with 'csc.exe TestClient.cs'. You must have .Net framework SDK v1.1 (or newer) installed.
-------------------
using System;
using System.Data;
using System.Data.Odbc;

class TestClient {
	static void Main(string[] args) {
		const string server   = "enter mysql server host here";
		const string userId   = "login goes here";
		const string password = "and the password";

		string dsn = "Driver={MySQL ODBC 3.51 Driver}" +
		             ";Option=3" +
		             ";Server=" + server +
		             ";Uid=" + userId +
		             ";Pwd=" + password +
		             ";Database=TestDb";

		string select = "SELECT * FROM testtable";
		OdbcDataAdapter oda = new OdbcDataAdapter(select, dsn);
		OdbcCommandBuilder ocb = new OdbcCommandBuilder(oda);

		DataSet ds = new DataSet();
		oda.Fill(ds);
		DataRow firstRow = ds.Tables[0].Rows[0];
		DataRow secondRow = ds.Tables[0].Rows[1];
		Console.Out.WriteLine("Current data:");
		Console.Out.WriteLine("DecayRate, 1st row: {0}", firstRow["DecayRate"]);
		Console.Out.WriteLine("DecayRate, 2nd row: {0}", secondRow["DecayRate"]);
		Console.Out.WriteLine();
		try {
			Console.Out.Write("Updating first row... ");
			firstRow["DecayRate"] = "12345";
			oda.Update(ds);
			Console.Out.WriteLine("OK!");

			Console.Out.Write("Updating second row... ");
			secondRow["DecayRate"] = "12345";
			oda.Update(ds);
			Console.Out.WriteLine("OK!");
		} catch (Exception e) {
			Console.Out.WriteLine("Exception occured!\r\n" + e);
		}
	}
}
-------------------

This is the output from the test program:
-------------------
Current data:
DecayRate, 1st row: 0,666666666666667
DecayRate, 2nd row: 0,666666666666667

Updating first row... OK!
Updating second row... Exception occured!
System.Data.DBConcurrencyException:
 Concurrency violation:
  the UpdateCommand affected 0 records.
-------------------

This is the SQL log from the MySQL server, showing what .Net is doing behind the scenes:
-------------------
Connect         <login>@<server> on TestDb
Query           SELECT * FROM testtable
Query           SELECT * FROM testtable
Field List      testtable 
Query           UPDATE testtable SET DecayRate = 1.23450000000000000e+004 WHERE ( (Id = 1) AND ((0 = 1 AND DecayRate IS NULL) OR (DecayRate = 6.66666666666666960e-001)) )
Query           UPDATE testtable SET DecayRate = 1.23450000000000000e+004 WHERE ( (Id = 2) AND ((0 = 1 AND DecayRate IS NULL) OR (DecayRate = 6.66666666666666960e-001)) )
Quit           

Suggested fix:
I've tried the test documented above on a Microsoft SQL Server with the float(53) data type, which according to it's documentation is a synonym for DOUBLE. The SQL Server acts in exactly the same way as MySQL when working through the data manually. Inserted values are rounded upon retrieval, however when one does a 'SELECT' on the rounded value, only the row that matches exactly is returned.
(eg. SELECT * FROM testtable WHERE DecayRate=0.666666666666667 returns one row, however a SELECT * FROM testtable returns two rows with the above value in the DecayRate field).

The test case acts differently on SQL Server though, in fact it works flawlessly:
-------------
Current data:
DecayRate, 1st row: 0,666666666666667
DecayRate, 2nd row: 0,666666666666667

Updating first row... OK!
Updating second row... OK!
-------------

I'm not an expert on the subject, so I won't suggest how to fix the situation.
Since the test case above works on SQL Server using System.Data.Odbc components from .Net, my best guess would be that the bug resides either in the MySQL ODBC Connector or, possibly, in the .Net ODBC components.

Thank you for the bug report ans its test case. I was able to repeat:

C:\code-for-bugs\bug5225>bug5225
Current data:
DecayRate, 1st row: 0,666666666666667
DecayRate, 2nd row: 0,666666666666667

Updating first row... OK!
Updating second row... Exception occured!
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
   at TestClient.Main(String[] args)

I am writing a client application in Visual Studio 2003.NET with C# and am using the MySQL .NET Connector 1.04 and I have been plagued with a DBConcurrency violation exception upon executing a MySqlDataAdapter.update(dataset, "tablename") command after my datagrid changes a column that is a MySQL.float data type. I saw this thread and have tested it with MySQL 4.0.24 and MySQL 4.1 with the results already posted in this thread. I saw this bug was reported last August 2004 and was wondering if it will be fixed soon.

This is the SQL log from the Microsoft SQL server, showing what .Net is doing behind the scenes:
-------------------
Login   set quoted_identifier on
        set implicit_transactions off
        set cursor_close_on_commit off
        set ansi_warnings on
        set ansi_padding on
        set ansi_nulls on
        set concat_null_yields_null on
        set language us_english
        set dateformat mdy
        set datefirst 7
SQL     SELECT * FROM testtable
RPC     exec sp_reset_connection
SQL     SET NO_BROWSETABLE ON
RPC     declare @P1 int
        set @P1=-1
        exec sp_prepare @P1 output, NULL, N'SELECT * FROM testtable', 1
        select @P1
SQL     SET NO_BROWSETABLE OFF
RPC     exec sp_special_columns N'testtable', NULL, NULL, N'V', N'T', N'O', @ODBCVer = 3
RPC     exec sp_special_columns N'testtable', NULL, NULL, N'V', N'T', N'O', @ODBCVer = 3
RPC     exec sp_executesql N'UPDATE testtable SET DecayRate = @P1 WHERE ((Id = @P2) AND (DecayRate = @P3))', N'@P1 float,@P2 tinyint,@P3 float', 1.234500000000000e+004, 1, 6.666666666666670e-001
RPC     exec sp_unprepare 1
RPC     exec sp_reset_connection
RPC     exec sp_executesql N'UPDATE testtable SET DecayRate = @P1 WHERE ((Id = @P2) AND (DecayRate = @P3))', N'@P1 float,@P2 tinyint,@P3 float', 1.234500000000000e+004, 2, 6.666666666666666e-001
-------------------
See MySQL log in the original bug description for a comparison.

As can be seen in the Microsoft SQL Server log, .NET here requests to update columns with two different values: 6.666666666666670e-001 and
6.666666666666666e-001.

This differs from .NET accessing a MySQL database, where the requested value is (wrongly) 6.66666666666666960e-001 in _both_ update commands.

According to the recent addition by Christopher Gruber, .NET sees the wrong value when using Connector/NET in addition to Connector/ODBC.  That would suggest that my guess from August 2004 is probably wrong and that there is in fact something wrong with the way the MySQL server returns DOUBLE values, not the client.

For testing that, here's a quick run using the command line clients for both products.

MySQL:
-------------------
C:\>mysql -h <server hostname> -D TestDb -u root --password=<sa password>
mysql> select * from testtable;
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
|  2 | 0.666666666666667 |
+----+-------------------+
2 rows in set (0.00 sec)
-------------------

Microsoft SQL Server:
-------------------
C:\>osql -S <server hostname> -d TestDB -U sa -P <sa password>
1> select * from testtable
2> go
 Id   DecayRate
 ---- ------------------------
    1      0.66666666666666696
    2      0.66666666666666663
(2 rows affected)
-------------------

Above: Results from the MySQL server are wrong in the command line client too, while results from Microsoft SQL Server are correct (albeit being rounded in a fashion that looks a little odd).

"Wrong" in the above sentence means that values returned from MySQL are too heavily rounded.  Copy/pasting the value that MySQL returns in the above into a SELECT statement will only yield half of the results expected (namely the entry with Id=1).  Obviously this is very similar to the problem that .NET developers are facing, because .NET does exactly this behind your back.

Just for laughs and giggles, here's a CLI run that shows that the server itself sees the correct value when doing the SELECT in one big swoop using a subquery.  I don't know the internals, for those who do, this may not be surprising.  Here it is anyway, just in case anyone else thought of doing this experiment:

mysql> SELECT * FROM testtable WHERE DecayRate IN (SELECT DecayRate FROM testtable);
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
|  2 | 0.666666666666667 |
+----+-------------------+
2 rows in set (0.00 sec)

What .NET does:

mysql> SELECT * FROM testtable WHERE DecayRate=0.666666666666667;
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
+----+-------------------+
1 row in set (0.00 sec)

Does anyone know *why* the DOUBLE values returned by SELECT are so aggressively rounded by MySQL?

This bug also causes severe data corruption when restoring backups made with 3rd party mysql backup systems or with mysqldump.

To reproduce, run mysqldump on the "testtable" table from this bug report, and then restore the table with another name (eg. "restored"):

$ mysqldump testdb testtable > db_dump.sql
$ sed 's/testtable/restored/g' -i db_dump.sql
$ mysql -D testdb < db_dump.sql

Then compare the output of fx. a SELECT command on the original table and the restored table respectively:

mysql> SELECT * FROM testtable WHERE DecayRate=0.666666666666667;
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
+----+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM restored WHERE DecayRate=0.666666666666667;
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
|  2 | 0.666666666666667 |
+----+-------------------+
2 rows in set (0.00 sec)

David,

I got the response from developers regarding this bug. They considered this record their TODO, but the fix gets priority dependent on how serious the bug is and how many customers are affected by it. As it is not associated with any support issue, the priority of this bug is lower than ones with support assignments. Unfortunately, I cannot provide any time estimates when it is going to be fixed.

Might I suggest that this bug is fixed and the fixed mode of operation get designated a new SQL_MODE flag?

Examples could be "ROUND_ON_INSERT", "NO_ROUND_ON_SELECT" or even "NO_RESULT_ROUNDING".

This seems to be how similar MySQL issues are fixed, while remaining backwards compatible with any theoretically existent systems that would depend on the old, broken behaviour.

(I'm assuming that is why this bug has been lingering for several years even though it seems to be a one-liner..)

I might not be the best person for closing this report as I opted clearly there is *no* bug here at all...

I am not about to quote IEEE specifications and the way floats are stored in CPU. As for .NET framework (and for that matters *all* of MS stuff) DOUBLE is TRUNCATED on MAX 15 decimals when mapped to MS types:
http://msdn2.microsoft.com/en-us/library/ae382yt8(VS.80).aspx
and I suggest to end this discussion with MS recommendation:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798634&SiteID=1, "Please use DECIMAL".

Anyway, our developers decided to "fix" this behavior in 6.0 and it works as David expects:
C:\mysql60\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 6.0.5-alpha-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE testtable (
    ->  Id TINYINT (3),
    ->  DecayRate DOUBLE NOT NULL,
    ->  PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO testtable (Id, DecayRate) VALUES (1, 0.666666666666667);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testtable (Id, DecayRate) VALUES (2, 0.6666666666666666);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtable WHERE DecayRate = 0.6666666666666666;
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  2 | 0.6666666666666666 |
+----+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM testtable WHERE DecayRate IN (SELECT DecayRate FROM testtab
le);
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
+----+--------------------+
2 rows in set (0.03 sec)

mysql> UPDATE testtable SET DecayRate = 0.88888888888888 WHERE DecayRate = 0.666
6666666666666;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testtable;
+----+-------------------+
| Id | DecayRate         |
+----+-------------------+
|  1 | 0.666666666666667 |
|  2 |  0.88888888888888 |
+----+-------------------+
2 rows in set (0.00 sec)

mysql>

Tonci,

I think you might have misunderstood what is the underlying issue. It is not about DOTNET at all.

My fault probably, as the first example I used was a piece of C# code. The reason for that was simply that we experienced massive problems in a number of systems coded in C# when they were switched to use MySQL Server.

Anyway, the real issue is that MySQL Server breaks the client's expectations by storing numbers in a higher precision than the client is allowed to see. The rounding is not performed in DOTNET as you suggest, but in MySQL Server.

(The simplest explanation I can think of is to say that MySQL "rounds on SELECT", as opposed to every other DBMS on the planet I've seen which does "round on INSERT".)

If you need non-DOTNET related evidence, you can choose other examples than the breakage caused to optimistic concurrency. For example, using the test case table, do a mysqldump, restore from the dump. Then compare the results of the "SELECT [...] 0.666666666666667" query against these two tables, and you'll get different results! The "round on SELECT" behaviour causes backups created with mysqldump to contain less data than was in the original database.

Reopened for practical reasons, to keep track of when this issue is fixed in a release.

About the "bug" word:

I agree with you that this is not an implementation bug.

Not sure what to call it when a product is designed in a way that causes users of that product to fail. A chocolate teapot being made of chocolate is not a "bug" per se, but there definitely is a design issue, at least if you'd like to drink tea out of it ;-).

(Maybe we could dub this a behavioural or design bug^H^H^Hissue or something. All we'd need then would be an appropriate place to mark it as such in the issue tracker...)

By the way, a big THANK YOU to the developer(s) who have finally nailed this one
! :-)

David, I didn't misunderstood anything, I was just being thorough and I think I've addressed all of your questions/concerns.

1) (The simplest explanation I can think of is to say that MySQL "rounds on SELECT", as opposed to every other DBMS on the planet I've seen which does "round on INSERT".)
- Fixed in 6.0.5, as you can see from my example.

2) If you need non-DOTNET related evidence, you can choose other examples than the breakage caused to optimistic concurrency.  For example, using the test case table, do a mysqldump, restore from the dump.  Then compare the results of the "SELECT [...] 0.666666666666667" query against these two tables, and you'll get different results!  The "round on SELECT" behaviour causes backups created with mysqldump to contain less data than was in the original database.
- Fixed in 6.0.5, tested, works:
C:\mysql60\bin>mysqldump --opt -uroot -p test > tst.sql
C:\mysql60\bin>mysql -uroot -p test < tst.sql

mysql> select * from testtabledmp;
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
+----+--------------------+
2 rows in set (0.00 sec)

mysql> select * from testtable;
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
+----+--------------------+
2 rows in set (0.00 sec)

mysql> select * from testtable
    -> union all
    -> select * from testtabledmp;
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
+----+--------------------+
4 rows in set (0.00 sec)

mysql> select * from testtable
    -> union
    -> select * from testtabledmp GROUP BY DecayRate;
+----+--------------------+
| Id | DecayRate          |
+----+--------------------+
|  1 |  0.666666666666667 |
|  2 | 0.6666666666666666 |
+----+--------------------+
2 rows in set (0.00 sec)

3) I agree with you that this is not an implementation bug.
- IMO using FLOAT(s) this way is a bug in itself and I will not elaborate that.

Now, I would like to know why you reopened this report? Will "Fixed in release == 6.0.5" be sufficient for your tracking purposes as I can not keep "bug" open when there's nothing to be fixed? Also, I am always subscribed to all bug reports I've worked on so no need for you to set bug report to "Open" to draw my attention.

> I didn't misunderstood anything, I was just being thorough

Oh, okay.  I misunderstood the motivation for the dotnet speech, then ;-P.

> IMO using FLOAT(s) this way is a bug in itself

Perhaps the complaint should be lodged at Microsoft for using FLOAT fields in conjunction with optimistic concurrency.  (To their defense, it does work with other DBMSs.)

> and I will not elaborate that.

Fair enough, issue's fixed, everybody's happy!

> Now, I would like to know why you reopened this report?

"not a bug" seemed a bit incorrect-ish.  I don't have all the options you have mind you - as far as changing status goes, I can pretty much only reset the case to open.  (Plus I had this idea that issues that are fixed should "end" with a commit message quoted, but then maybe not).

I'll just set the case to "closed" (my other status option ;-)), hope we're all happy with that..

David, thanks for being constructive and patient. We do appreciate you input very much.