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