Selecting by month with DateField is broken with sqlite3 backend

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.

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

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?

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.

Django requires sqlite3.

Sorry - I totally misread your comment; reopening.

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'

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

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).

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)

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

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

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

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.

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.

tests proving that SVN doesn't have this bug.

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.

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.

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

Fixed #659