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

Question

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.

You are also going to have issues with paramname because you don't specify a scale for your nvarchar. That means it will use the default. Do you know the default can change based on context? It will be either 1 or 30. ALWAYS define the scale. https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length — Sean Lange, Oct 11 '19 at 19:34
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. — timothyjb, Oct 11 '19 at 19:39
Actually paramname is nvarchar(50). I don't know how that ended up like that in my post. — timothyjb, Oct 11 '19 at 19:42
Also, the value is being used as a dictionary key so it's important that it actually be 0.76 and not something else. — timothyjb, Oct 11 '19 at 19:45
@timothyjb I would recommend in the **strongest** language to use integers for dictionary keys rather than floating point numbers. — Brian, Oct 15 '19 at 16:00

score 2 · Answer 1 · answered Oct 11 '19 at 19:26

2

This is due to rounding - the Real type is approximated.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

answered Oct 11 '19 at 19:26

Brian

1,238
2
11
17

*Approximated*... I would say it is how floating point precision numbers work. They just cannot be infinite precision numbers. – Michel de Ruiter Apr 13 '21 at 14:12

Gord Thompson · Accepted Answer · 2019-10-16T13:27:23.660

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.

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

2 Answers2