Selecting by month with DateField is broken with sqlite3 backend

[pgross]

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)

[pgross]

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.

[Adrian Holovaty]

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

[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?

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

[Jacob]

Django requires sqlite3.

[Jacob]

Sorry - I totally misread your comment; reopening.

[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'

[Adrian Holovaty]

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

[slev1@…]

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

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

[(none)]

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

[Malcolm Tredinnick <malcolm@…>]

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

[Michael Radziej]

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

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

[Philippe Raoult]

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.

[Philippe Raoult]

tests proving that SVN doesn't have this bug.

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

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

[Malcolm Tredinnick]

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

Fixed #659