Opened 10 years ago

Closed 9 years ago

Last modified 8 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
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@…> 10 years ago.
Fix date comparisons in SQLite

Download all attachments as: .zip

Change History (5)

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

  • Summary changed from [magic-removal] pub_date__lt does not behave as expected with SQLite to [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 10 years ago by Malcolm Tredinnick <malcolm@…>

Fix date comparisons in SQLite

comment:2 Changed 9 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:3 Changed 9 years ago by adrian

  • milestone Version 0.92 deleted

Milestone Version 0.92 deleted

comment:4 Changed 8 years ago by anonymous

  • Component changed from Database wrapper to Tools
  • Keywords 123 added
  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted
Note: See TracTickets for help on using tickets.
Back to Top