Recommend against Windows/SQLite < 3.3.6 much more strongly

[Russell Keith-Magee]

This is yet another Windows/SQLite 3.3.4 bug; see the first comment below.

Original ticket follows

---

The aggregation and aggregation_regress system tests fail due to problems with DateFields and DecimalFields under Windows and Python 2.5

The problem manifests as the following failures:

======================================================================
FAIL: Doctest: modeltests.aggregation.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "d:\u\kmt\django\aggregation\django\test\_doctest.py", line 2180, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for modeltests.aggregation.models.__test__.API_TESTS
  File "D:\u\kmt\django\aggregation\tests\modeltests\aggregation\models.py", line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\modeltests\aggregation\models.py", line ?, in modeltests.aggregation.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(pk=1).annotate(mean_age=Avg('authors__age')).values()
Expected:
    [{'rating': 4.5, 'isbn': u'159059725', 'name': u'The Definitive Guide to Django: Web Development Done Right', 'pubdate': datetime.date(2007, 12, 6), 'price': Decimal("30..."), 'id': 1, 'publisher_id': 1, 'pages': 447, 'mean_age': 34.5}]
Got:
    [{'rating': 4.5, 'isbn': u'159059725', 'name': u'The Definitive Guide to Django: Web Development Done Right', 'pubdate': u'2007-12-06', 'price': 30, 'id': 1, 'publisher_id': 1, 'pages': 447, 'mean_age': 34.5}]
----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\modeltests\aggregation\models.py", line ?, in modeltests.aggregation.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(pk=1).values().annotate(mean_age=Avg('authors__age'))
Expected:
    [{'rating': 4.5, 'isbn': u'159059725', 'name': u'The Definitive Guide to Django: Web Development Done Right', 'pubdate': datetime.date(2007, 12, 6), 'price': Decimal("30..."), 'id': 1, 'publisher_id': 1, 'pages': 447, 'mean_age': 34.5}]
Got:
    [{'rating': 4.5, 'isbn': u'159059725', 'name': u'The Definitive Guide to Django: Web Development Done Right', 'pubdate': u'2007-12-06', 'price': 30, 'id': 1, 'publisher_id': 1, 'pages': 447, 'mean_age': 34.5}]


======================================================================
FAIL: Doctest: regressiontests.aggregation_regress.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "d:\u\kmt\django\aggregation\django\test\_doctest.py", line 2180, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for regressiontests.aggregation_regress.models.__test__.API_TESTS
  File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line ?, in regressiontests.aggregation_regress.models.__test__.API_TESTS
Failed example:
    sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).__dict__.items())
Expected:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
Got:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', 11.545), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', 23.09), ('pubdate', u'2008-03-03'), ('publisher_id', 2), ('rating', 3.0)]
----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line ?, in regressiontests.aggregation_regress.models.__test__.API_TESTS
Failed example:
    sorted(Book.objects.all().extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2).__dict__.items())
Expected:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
Got:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', 11.545), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', 23.09), ('pubdate', u'2008-03-03'), ('publisher_id', 2), ('rating', 3.0)]
----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line ?, in regressiontests.aggregation_regress.models.__test__.API_TESTS
Failed example:
    sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2).items())
Expected:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
Got:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', 11.545), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', 23.09), ('pubdate', u'2008-03-03'), ('publisher_id', 2), ('rating', 3.0)]
----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line ?, in regressiontests.aggregation_regress.models.__test__.API_TESTS
Failed example:
    sorted(Book.objects.all().values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).items())
Expected:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
Got:
    [('id', 2), ('isbn', u'067232959'), ('manufacture_cost', 11.545), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', 23.09), ('pubdate', u'2008-03-03'), ('publisher_id', 2), ('rating', 3.0)]
----------------------------------------------------------------------
File "D:\u\kmt\django\aggregation\tests\regressiontests\aggregation_regress\models.py", line ?, in regressiontests.aggregation_regress.models.__test__.API_TESTS
Failed example:
    Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards'))
Expected:
    [{'price': Decimal("30..."), 'oldest': 35, 'publisher__num_awards__max': 3}, {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7}, {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1}, {'price': Decimal("75..."), 'oldest': 57, 'publisher__num_awards__max': 9}, {'price': Decimal("82.8..."), 'oldest': 57, 'publisher__num_awards__max': 7}]
Got:
    [{'price': 30, 'oldest': 35, 'publisher__num_awards__max': 3}, {'price': 29.690000000000001, 'oldest': 37, 'publisher__num_awards__max': 7}, {'price': 23.09, 'oldest': 45, 'publisher__num_awards__max': 1}, {'price': 75, 'oldest': 57, 'publisher__num_awards__max': 9}, {'price': 82.799999999999997, 'oldest': 57, 'publisher__num_awards__max': 7}]


----------------------------------------------------------------------

This doesn't appear to affect other versions of Python (2.4 or 2.6), or any version of Python on Linux/OS X. Only Win/Py2.5 appears to be affected.

[Ramiro Morales]

I've tested this and found that using SQLite3 3.3.10 (included with pysqlite.org python2.5 pysqlite2 binary installer for version 2.3.3) doesn't show this problem. Affected version are SQLite 3.3.4 and pysqlite2(aka sqlite3) 2.3.2 as shipped with win32 Python 2.5.x installers.

pysqlite2 change log between 2.3.2 and 2.3.3 don't show anything relevant.

SQLite change log between 3.3.4 and 3.3.10 (​http://www.sqlite.org/cvstrac/timeline?d=334&e=2007-Jan-10&c=2&px=&s=9&dm=1&x=1) show something that might be related (​http://www.sqlite.org/cvstrac/tktview?tn=1726, ​http://www.sqlite.org/cvstrac/tktview?tn=1755, ​http://www.sqlite.org/cvstrac/chngview?cn=3169 committed right after SQLite 3.3.5 was released) but I'm not sure.

At any rate, I think we can:

• Rely in our SQLite database notes already included in the documentation (​http://docs.djangoproject.com/en/dev/ref/databases/#id8) that suggest using 3.3.6 or newer
• change this ticket component to 'documentation'.
• and perhaps add a blurb about this in such notes.

[Jacob]

I agree: we should just change the docs to be more insistant that using SQLite < 3.3.6 is a Bad Idea.

[Ramiro Morales]

Patch with documentation fixes

[Ramiro Morales]

The attached documentation patch includes a fix for this ticket plus the following related fixes:

• Added a note about the ability of Django of using a pysqlite2 module in preference of a standard-lib-provided sqlite3 one.
• Updated info about solving problems with buggy SQLite in Ubuntu 8.10 (Intrepid Ibex)
• Updated the link to the pysqlite web site
• Added a link to the these sqlite3 backend notes in ref/database.txt from topics/install.txt just like is done with the other DB backends and so users have a link trail to reach them from the installation notes.

[Jacob]

(In [10311]) Fixed #10031: updated SQLite database docs to more strongly indicate the problems with versions before 3.3.6. Thanks, ramiro.

[Jacob]

Milestone 1.1 deleted