Bugs item #1597404, was opened at 2006-11-16 02:00
Message generated for change (Comment added) made by ghaering
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=105470&aid=1597404&group_id=5470
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Python Library
Group: Python 2.5
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Michael Salib (msalib_ita)
Assigned to: Gerhard Häring (ghaering)
Summary: sqlite timestamp converter bug (floating point)
Initial Comment:
The pysqlite code in Python 2.5 has a bug. This bug also exists in the upstream
pysqlite2 release, but their tracker is down so I cannot submit it there.
The bug is as follows: if you insert a datetime object into a sqlite database
and then try to retrieve the object, you will (in some cases) get a datetime
instance with a slightly smaller value for the microseconds field. This bug
occurs because pysqlite is doing pointless floating point conversions. I
describe two fixes and an extra test case below.
This bug is real. I have observed it in the wild. The test set for my
application can trigger this bug about once every 20 runs.
This is what happens:
* pysqlite includes an adapter and converter function so that datetime objects
can transparently be inserted and retrieved from a sqlite database column of
type timestamp.
* When inserting a datetime object, pysqlite's adapter will insert the
isoformat() value of the object.
* When retrieving, pysqlite will take the iso formatted string representation
of the datetime object and convert it into an actual datetime object. This
conversion is buggy.
* Check out line 71 of Lib/sqlite3/dbapi2.py. The code is:
microseconds = int(float("0." + timepart_full[1]) * 1000000)
And that is where the bug is. This code takes an integer value, converts it
into a float (implicitly dividing by 1000000, then multiplies that by 1000000
and takes the integer part. For most values, that process gives the result you
expect. For some values however, like 510241, that process gives slightly
smaller values because of floating point rounding.
There are two possible fixes:
1. The simple fix is to just do rounding properly by using this line in place
of the previous line:
microseconds = int(0.5 + (float("0." + timepart_full[1]) * 1000000))
This will eliminate the bug.
2. The better fix (IMHO) is to stop playing games with floating point numbers.
There is absolutely no reason to introduce floats into this computation. The
datetime object stores microseconds as an integer value and it gets written to
the database as a stringified integer value. Taking apart that string and
converting it into an integer is a lossless operation. My preferred fix is thus:
microseconds = int(timepart_full[1])
This will eliminate the bug and it has the benefit of being shorter as well.
I've attached a patch with my preferred fix as well as an extra test in the
pysqlite test suite (Lib/sqlite3/test/types.py). You can run the pysqlite test
suite by running Lib/sqlite3/test/types.py. Note that without my fix, the test
that I added (DateTimeTests.CheckDateTimeSubSecondsFloatingPoint) will fail but
with my fix it will pass.
----------------------------------------------------------------------
>Comment By: Gerhard Häring (ghaering)
Date: 2006-12-01 23:48
Message:
Logged In: YES
user_id=163326
Originator: NO
Fixed in upstream pysqlite. Leaving open until next merge to Python's
sqlite3 module.
----------------------------------------------------------------------
Comment By: Martin v. Löwis (loewis)
Date: 2006-11-16 07:18
Message:
Logged In: YES
user_id=21627
Originator: NO
Gerhard, can you please take a look? If not, unassign.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=105470&aid=1597404&group_id=5470
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
