Opened 11 years ago

Closed 9 years ago

#659 closed defect (fixed)

Selecting by month with DateField is broken with sqlite3 backend

Reported by: pgross Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version:
Severity: major Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I have a model with a DateField. Selecting by month works works mysql, but not with sqlite3. Below is a rough outline of the code I have:

class Party(meta.Model):
    when = meta.DateField()

parties_this_month = parties.get_list(when__year=2005, when__month=10)

Attachments (2)

sqlite3.py.patch (1.5 KB) - added by pgross 11 years ago.
This isn't fully tested, but this patch fixed my bug. Basically, instead of using the django_extract function I just use strftime to extract the year/month/day.
659_tests.diff (1.3 KB) - added by Philippe Raoult 9 years ago.
tests proving that SVN doesn't have this bug.

Download all attachments as: .zip

Change History (20)

Changed 11 years ago by pgross

Attachment: sqlite3.py.patch added

This isn't fully tested, but this patch fixed my bug. Basically, instead of using the django_extract function I just use strftime to extract the year/month/day.

comment:1 Changed 11 years ago by Adrian Holovaty

(In [1033]) Added model unit tests for year, month and day lookup. Refs #659

comment:2 Changed 11 years ago by Adrian Holovaty

I added unit tests in [1033], but I can't reproduce the bug. I'm using version 3.2.1 of SQLite and version 1.0.1 of the SQLite Python bindings. Which versions are you using? And what's the full traceback you get?

comment:3 Changed 11 years ago by pgross

I'm using version 2.04 of pysqlite. There is no traceback or exception. The query merely doesn't return any results with pysqlite, whereas it works fine with mysql.

comment:4 Changed 11 years ago by Jacob

Resolution: invalid
Status: newclosed

Django requires sqlite3.

comment:5 Changed 11 years ago by Jacob

Resolution: invalid
Status: closedreopened

Sorry - I totally misread your comment; reopening.

comment:6 Changed 11 years ago by pgross

I just ran the tests with both mysql and sqlite3. Here are the errors I get with sqlite3 that I don't get with mysql:

'basic' module: API test raised an exception
============================================
Code: 'articles.get_object(pub_date__year=2005, pub_date__month=7)'
Line: 41
Exception:   File "C:\apps\django\tests\doctest.py", line 1243, in __run
    compileflags, 1) in test.globs
  File "<doctest basic[13]>", line 1, in ?
    articles.get_object(pub_date__year=2005, pub_date__month=7)
  File "C:\Python24\lib\site-packages\django\utils\functional.py", line 3, in _curried
    return args[0](*(args[1:]+moreargs), **dict(kwargs.items() + morekwargs.items()))
  File "C:\Python24\lib\site-packages\django\core\meta\__init__.py", line 1097, in function_get_object
    raise does_not_exist_exception, "%s does not exist for %s" % (opts.object_name, kwargs)
ArticleDoesNotExist: Article does not exist for {'pub_date__month': 7, 'pub_date__year': 2005}


'basic' module: API test raised an exception
============================================
Code: 'articles.get_object(pub_date__year=2005, pub_date__month=7, pub_date__day=28)'
Line: 43
Exception:   File "C:\apps\django\tests\doctest.py", line 1243, in __run
    compileflags, 1) in test.globs
  File "<doctest basic[14]>", line 1, in ?
    articles.get_object(pub_date__year=2005, pub_date__month=7, pub_date__day=28)
  File "C:\Python24\lib\site-packages\django\utils\functional.py", line 3, in _curried
    return args[0](*(args[1:]+moreargs), **dict(kwargs.items() + morekwargs.items()))
  File "C:\Python24\lib\site-packages\django\core\meta\__init__.py", line 1097, in function_get_object
    raise does_not_exist_exception, "%s does not exist for %s" % (opts.object_name, kwargs)
ArticleDoesNotExist: Article does not exist for {'pub_date__month': 7, 'pub_date__year': 2005, 'pub_date__day': 28}


'basic' module: API test failed
===============================
Code: 'articles.get_list(pub_date__year=2005, pub_date__month=7)'
Line: 50
Expected: '[<Article object>]\n'
Got: '[]\n'

comment:7 Changed 11 years ago by Adrian Holovaty

Resolution: fixed
Status: reopenedclosed

The tests all pass in SQLite, so I'm closing.

comment:8 Changed 11 years ago by slev1@…

Resolution: fixed
Status: closedreopened

I ran into the same problem and am able to reproduce it consistently, so I would like to reopen this. The "month" selection criteria works iif the target column is a DateTimeField. If the target column os a DateField it does not work. Apparently the line "dt = typecasts.typecast_timestamp(dt)" in the function "_sqlite_extract" raises an exception if the column is a DateField. Perhaps there is a way to detect which of the two types it is and call the proper typecast function. (calling "dt = typecasts.typecast_date(dt)" makes the function work for DateField, btw).

comment:9 Changed 11 years ago by slev1@…

It seems to me that typecasts.typecast_date should be able to handle a either a timestamp string or just a date string. So I made the following changes locally which handles both cases:

typecasts.py: line 8
    # split the string by ' ' first and use the first element as the date string to parse
    return s and datetime.date(*map(int, s.split(' ')[0].split('-'))) or None # returns None if s is null

sqlite3.py: line 95
        # use typecast_date instead of typecast_timestamp because we are only interested in year/month/day
        dt = typecasts.typecast_date(dt)

comment:10 Changed 11 years ago by (none)

Are you still seeing this problem on trunk? It sounds awfully similar to #1062...

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

Resolution: fixed
Status: reopenedclosed

This was fixed by #1062 (I get failures using a version before that change and no failures afterwards).

comment:12 Changed 9 years ago by Michael Radziej

Needs tests: set
Resolution: fixed
Status: closedreopened
Triage Stage: UnreviewedAccepted

Reopened because user reported that this still does not work. A reproduceable test case for this ticket is most welcome ;-)

See this thread in django-developers

comment:13 Changed 9 years ago by shaleh

I do not see any issues with current 6780 trunk.

class Person(models.Model):

name = models.CharField(max_length=30)
birthday = models.DateField()

class Meta:

ordering = ['birthday', 'name']

def unicode(self):

return u"%s, %s" % (self.name, self.birthday)

Person.objects.all()
[<Person: John, 1965-04-30>, <Person: Charles, 2001-03-24>]

Person.objects.filter(birthdayyear=1965)
[<Person: John, 1965-04-30>]

Person.objects.filter(birthdaymonth=4)
[<Person: John, 1965-04-30>]

print django.db.backend

<module 'django.db.backends.sqlite3.base' from '/home/shaleh/repos/django/django/db/backends/sqlite3/base.pyc'>

I am using Debian's python-pysqlite2 package version 2.3.5-1.

The thread referenced in the previous comment seems to be discussing a different problem.

comment:14 Changed 9 years ago by Pete Crosier

Has patch: set

comment:15 Changed 9 years ago by Philippe Raoult

Needs tests: unset
Triage Stage: AcceptedReady for checkin

using:

ii libsqlite3-0 3.4.2-1 SQLite 3 shared library
ii python-pysqlite2 2.3.2-2 python interface to SQLite 3

it works.

Changed 9 years ago by Philippe Raoult

Attachment: 659_tests.diff added

tests proving that SVN doesn't have this bug.

comment:16 Changed 9 years ago by Philippe Raoult

Just to make things clear: I marked as ready for checkin so we can have the test and close this ticket, the first patch (sqlite3.py.patch) does NOT need to be merged.

comment:17 Changed 9 years ago by Malcolm Tredinnick

I'm going to commit the test case here and then close this. If anybody feels that it should be reopened because they've "rediscovered" the bug, they must include details such as SQLite version, Python version and sqlite-python wrapper version (for python < 2.5). I suspect this is highly version dependent.

comment:18 Changed 9 years ago by Malcolm Tredinnick

Resolution: fixed
Status: reopenedclosed

(In [7359]) Added a test for month selection under SQLite in case an old possible bug ever
resurfaces. Patch from Pilippe Raoult.

Fixed #659

Note: See TracTickets for help on using tickets.
Back to Top