Code

Opened 9 years ago

Closed 6 years ago

#659 closed defect (fixed)

Selecting by month with DateField is broken with sqlite3 backend

Reported by: pgross Owned by: adrian
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 8 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 PhiR 6 years ago.
tests proving that SVN doesn't have this bug.

Download all attachments as: .zip

Change History (20)

Changed 8 years ago by 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.

comment:1 Changed 8 years ago by adrian

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

comment:2 Changed 8 years ago by adrian

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 8 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 8 years ago by jacob

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

Django requires sqlite3.

comment:5 Changed 8 years ago by jacob

  • Resolution invalid deleted
  • Status changed from closed to reopened

Sorry - I totally misread your comment; reopening.

comment:6 Changed 8 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 8 years ago by adrian

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

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

comment:8 Changed 8 years ago by slev1@…

  • Resolution fixed deleted
  • Status changed from closed to reopened

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 8 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 8 years ago by anonymous

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

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

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

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

comment:12 Changed 7 years ago by mir

  • Needs tests set
  • Resolution fixed deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Unreviewed to Accepted

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 6 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 6 years ago by PJCrosier

  • Has patch set

comment:15 Changed 6 years ago by PhiR

  • Needs tests unset
  • Triage Stage changed from Accepted to Ready 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 6 years ago by PhiR

tests proving that SVN doesn't have this bug.

comment:16 Changed 6 years ago by PhiR

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 6 years ago by mtredinnick

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 6 years ago by mtredinnick

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

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

Fixed #659

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.