[ python-Bugs-1597404 ] sqlite timestamp converter bug (floating point)

[SourceForge.net]

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