[patch] [magic-removal] pub_date__lt does not behave as expected with SQLite

[matt]

The following test fails for me (magic-removal [2475] using OSX 10.4.5, SQLite 3.1.3 and PySQLite 2.0.7). It appears that filtering by pub_date__lt does not work with SQLite:

'get_latest' module: API test failed
====================================
Code: 'Article.objects.filter(pub_date__lt=datetime(2005, 7, 27)).latest()'
Line: 23
Expected: 'Article 1\n'
Got: 'Article 3\n'

For some context, here's why we should really be getting Article 1 and not Article 3:

>>> a1 = Article(headline='Article 1', pub_date=datetime(2005, 7, 26), expire_date=datetime(2005, 9, 1))
>>> a1.save()
>>> a2 = Article(headline='Article 2', pub_date=datetime(2005, 7, 27), expire_date=datetime(2005, 7, 28))
>>> a2.save()
>>> a3 = Article(headline='Article 3', pub_date=datetime(2005, 7, 27), expire_date=datetime(2005, 8, 27))
>>> a3.save()
>>> a4 = Article(headline='Article 4', pub_date=datetime(2005, 7, 28), expire_date=datetime(2005, 7, 30))
>>> a4.save()
# Get the latest Article that matches certain filters.
>>> Article.objects.filter(pub_date__lt=datetime(2005, 7, 27)).latest()
Article 1

I'm not sure if this problem is showing up all the time or only when latest() is involved.

[Malcolm Tredinnick <malcolm@…>]

In SQLite, comparing a Date column to a Datetime literal does not work quite as expected. In particular, notice the following slightly unusual (for SQL) behaviour:

SQLite version 3.2.7
Enter ".help" for instructions
sqlite> create table foo(my_date date);
sqlite> insert into foo values ('2006-03-01');
sqlite> insert into foo values ('2006-03-02');
sqlite> select * from foo where my_date < '2006-03-02';
my_date
----------
2006-03-01
sqlite> select * from foo where my_date < '2006-03-02 00:00:00';
my_date
----------
2006-03-01
2006-03-02

The attached patch changes the way LIKE queries are constructed involving dates (not datetimes) slightly for every backend so that SQLite is happy. It is still legal SQL and, at least, the tests all still pass with postgreSQL as well as SQLite.

[Malcolm Tredinnick <malcolm@…>]

Fix date comparisons in SQLite

[Adrian Holovaty]

(In [2517]) magic-removal: Fixed #1460 -- Fixed pub_datelt behavior in SQLite. Thanks for the patch, Malcolm Tredinnick

[Adrian Holovaty]

Milestone Version 0.92 deleted