Bug #73266 Floating point inaccuracy only on Python 2.x with mysql/connector python
Submitted: 10 Jul 2014 20:03 Modified: 17 Jul 2014 14:01
Reporter: Mike Bayer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:1.2.2 OS:Any
Assigned to: CPU Architecture:Any

[10 Jul 2014 20:03] Mike Bayer
Description:
persisting and/or returning values from a DOUBLE column undergoes data truncation not present in other MySQL drivers, only on Py2K, not Py3K.  The test case below fails only for MySQL connector, not MySQLdb, on Python 2.7, passes on Python 3.3, 3.4:

from __future__ import print_function

def test(dbapi):
    conn = dbapi.connect(
        user='scott', passwd='tiger', host='localhost',
        db='test')

    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE t (
            scale_value DOUBLE(15, 12),
            unscale_value DOUBLE
        )
    """)

    value = 45.768392065789
    cursor.execute(
        "INSERT INTO t (scale_value, unscale_value) VALUES (%s, %s)",
        (value, value))

    cursor.execute("SELECT scale_value, unscale_value FROM t")
    row = cursor.fetchone()
    try:
        print(
            "DBAPI: %s, VALUE: %.11f, ROW[0]: %.11f, ROW[1]: %.11f" %
            (dbapi.__name__, value, row[0], row[1]))
        assert row == (value, value)
    finally:
        cursor.execute("DROP TABLE t")

try:
    import MySQLdb
except ImportError:
    pass
else:
    test(MySQLdb)

from mysql import connector
test(connector)

output on Py2.7:

DBAPI: MySQLdb, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579
DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206580, ROW[1]: 45.76839206580
Traceback (most recent call last):
  File "test.py", line 41, in <module>
    test(connector)
  File "test.py", line 29, in test
    assert row == (value, value)
AssertionError

output on py3.3 (where I dont have MySQLdb installed):

DBAPI: mysql.connector, VALUE: 45.76839206579, ROW[0]: 45.76839206579, ROW[1]: 45.76839206579

it seems that MySQL connector might be running this float through the string function, which truncates a float in the way we see above:

>>> str(45.768392065789)
'45.7683920658'
>>> 

on Py3K, the additional accuracy is preserved:

>>> str(45.768392065789)
'45.768392065789'
>>> 

How to repeat:
run the above script.

Suggested fix:
MySQL connector should not be relying upon the raw str() function to parse floating points.
[16 Jul 2014 9:47] Peeyush Gupta
Thanks for the bug report.
Verified as described using code analysis.
[17 Jul 2014 14:01] Paul Dubois
Noted in 1.2.3 changelog.

With Python 2, Connector/Python could truncate digits of floating-point values.
[21 Jul 2014 14:48] Paul Dubois
Noted in 2.0.1 changelog.