Why does SQL Server return numbers like 0.759999 while MySQL returns 0.76?

I have a SQL Server database table which has three columns. As an example, one of the values in this column might be 0.76. This column of data, named ‘paramvalue’ is defined as real. When I use the pyodbc module command fetchall() I get back a number like 0.7599999904632568 instead of 0.76. I’m using Visual Studio 2017 and Python Tools for Visual Studio. I’ve also tried the pypyodbc module but get the same problem.

The table has three columns and are defined as follows;

pconfig_id [int] IDENTITY(41,1) NOT NULL,
paramname  [nvarchar](50)       NOT NULL,
paramvalue [real]                   NULL

My Python code:

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=DBNAME;UID=USER;PWD=PASSWORD;Connect Timeout=15')
cursor = cnxn.cursor()
dict = {}
rows = cursor.execute("SELECT * FROM mytable")
for row in cursor.fetchall() :
    if not row[1] in dict.keys():
        dict[row[1]] = {}
        dict[row[1]][row[2]] = row[0]

In the example above, row[2] for a typical row has the value 0.7599999904632568 instead of 0.76 as expected.

Answer

I should mention that this database was migrated from a MySQL database, in which case the type was float. When I used the MySQLdb module to fetch the data from that MySQL table it came in as 0.76 as expected.

This is not an issue that is caused by pyodbc. It is a difference between MySQL and MSSQL in the way floating-point numbers are displayed.

0.76 is not a value that can be represented exactly as a 32-bit floating-point (“single precision”) value. As this site (and others) will tell you, the most accurate representation of that number is 7.599999904632568359375E-1, so that is what both databases store (internally represented as 0x3F428F5C).

When retrieving the value, MSSQL returns the actual value that was written to the database. That’s why it comes back as 0.7599999904632568.

MySQL, on the other hand, returns the shortest string that represents a floating-point value which would result in the given stored value. As described in the documentation:

F -> D conversion is done with the best possible precision, returning D as the shortest string that yields F when read back in and rounded to the nearest value in native binary format as specified by IEEE.

So, MySQL round-trips 0.76 because it just happens to be the shortest value that corresponds to the float value whose internal representation is 0x3F428F5C. This can be illustrated by testing with a number that is very close to 0.76, but not exactly equal:

is_mssql = (cnxn.getinfo(pyodbc.SQL_DRIVER_NAME) == 'msodbcsql17.dll')

crsr = cnxn.cursor()

test_value = '0.7599999905'
if is_mssql:
    crsr.execute("CREATE TABLE #foo (x real)")
    crsr.execute(f"INSERT INTO #foo (x) VALUES ('{test_value}')")
    result = crsr.execute("SELECT x FROM #foo").fetchval()
else:
    crsr.execute("CREATE TEMPORARY TABLE foo (x float(23))")
    crsr.execute(f"INSERT INTO foo (x) VALUES ('{test_value}')")
    result = crsr.execute("SELECT x FROM foo").fetchval()
print(f'{"MSSQL" if is_mssql else "MySQL"} returned {result}')

Even though 0.7599999905 is the “actual” value, MySQL still returns 0.76 and MSSQL still returns 0.7599999904632568.