Opened 11 years ago

Closed 11 years ago

Last modified 9 years ago

#1460 closed defect (fixed)

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

Reported by: matt Owned by: Adrian Holovaty
Component: Tools Version: magic-removal
Severity: normal Keywords: 123
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (1)

datefield-lookup.diff (1.0 KB) - added by Malcolm Tredinnick <malcolm@…> 11 years ago.
Fix date comparisons in SQLite

Download all attachments as: .zip

Change History (5)

comment:1 Changed 11 years ago by Malcolm Tredinnick <malcolm@…>

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

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.

Changed 11 years ago by Malcolm Tredinnick <malcolm@…>

Attachment: datefield-lookup.diff added

Fix date comparisons in SQLite

comment:2 Changed 11 years ago by Adrian Holovaty

Resolution: fixed
Status: newclosed

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

comment:3 Changed 10 years ago by Adrian Holovaty

milestone: Version 0.92

Milestone Version 0.92 deleted

comment:4 Changed 9 years ago by anonymous

Component: Database wrapperTools
Keywords: 123 added
Needs tests: set
Triage Stage: UnreviewedAccepted
Note: See TracTickets for help on using tickets.
Back to Top