Opened 19 years ago

Closed 17 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: no UI/UX: no

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 19 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 17 years ago.
tests proving that SVN doesn't have this bug.

Download all attachments as: .zip

Change History (20)

by pgross, 19 years ago

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 by Adrian Holovaty, 19 years ago

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

comment:2 by Adrian Holovaty, 19 years ago

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 by pgross, 19 years ago

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 by Jacob, 19 years ago

Resolution: invalid
Status: newclosed

Django requires sqlite3.

comment:5 by Jacob, 19 years ago

Resolution: invalid
Status: closedreopened

Sorry - I totally misread your comment; reopening.

comment:6 by pgross, 19 years ago

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 by Adrian Holovaty, 19 years ago

Resolution: fixed
Status: reopenedclosed

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

comment:8 by slev1@…, 19 years ago

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 by slev1@…, 19 years ago

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 by (none), 19 years ago

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

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

Resolution: fixed
Status: reopenedclosed

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

comment:12 by Michael Radziej, 17 years ago

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 by shaleh, 17 years ago

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 by Pete Crosier, 17 years ago

Has patch: set

comment:15 by Philippe Raoult, 17 years ago

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.

by Philippe Raoult, 17 years ago

Attachment: 659_tests.diff added

tests proving that SVN doesn't have this bug.

comment:16 by Philippe Raoult, 17 years ago

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 by Malcolm Tredinnick, 17 years ago

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 by Malcolm Tredinnick, 17 years ago

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