Opened 10 years ago

Closed 10 years ago

Last modified 7 years ago

#8416 closed (duplicate)

Using Sqlite as database backend causes list_filter and date_hierarchy misbehave in Admin pages

Reported by: amiroff Owned by: nobody
Component: Core (Management commands) Version: master
Severity: Keywords:
Cc: amiroff@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


list_filter and date_hierarchy in Admin do not work properly when using Sqlite as a db backend and filtering by DateField / DateTimeField field types.

To see the bug in action:

  1. Create a Django project and an app

2 Set sqlite as db backend

  1. Create a test model with DateTimeField
  1. Sync the database
  1. Add some entries.
  1. Observe how clicking "Today" or "This month" does not reveal the entries that were just created.
  1. Also, date_hierarchy behaves wierd too, displaying Current month's name and returning no results when clicked.

I came across this bug while following the official tutorial at

Switching to Mysql as a db backend solves the problem.

Using latest (1.0-beta_1-SVN-8437) SVN checkout
Python 2.5.2
Sqlite bundled with Python 2.5.2
Windows XP Pro SP2

Attachments (1)

django_sqlite_test_case.diff (576 bytes) - added by haakeyar 10 years ago.
Simple test case. Passes in MySQL, but not in SQLite

Download all attachments as: .zip

Change History (6)

comment:1 Changed 10 years ago by haakeyar

I did some research on this yesterday. Apparently it has to do with the fact that the admin interface sends all parameters to the orm as unicode strings. For example field_name__month=u'8'.

According to django.db.connection.queries, passing the parameter as a (unicode) string sends the same query to the db as when you pass the parameter as an integer, so maybe the problem has to do with what is done to the response afterwards.

I am completely new to the django codebase, so I didn't manage to locate the problem - for some reason, it worked each time I tried to use a step-by-step debugger (which usually could indicate a race condition, but that sounds a little strange in this case). I don't have access to the computer I used yesterday or django at the moment, but I will give more detailed information when I do, unless anyone has found the source of the problem by then.

comment:2 Changed 10 years ago by haakeyar

Ok, I promised some details.

I am running SQLite 3.5.6, Django trunk r8467, Python 2.5.1.

As I said, the same query appears to be run both when using a string and when using an integer for __month:

class DateModel(models.Model):
    date_field = models.DateTimeField()
In [1]: from myapp.models import DateModel
In [2]: from datetime import datetime
In [3]: DateModel(date_field=datetime(2008,8,22)).save()
In [4]: DateModel.objects.all()
Out[4]: [<DateModel: DateModel object>]
In [5]: DateModel.objects.filter(date_field__month=8)
Out[5]: [<DateModel: DateModel object>]
In [6]: DateModel.objects.filter(date_field__month='8')
Out[6]: []
In [7]: from django.db import connection
In [8]: connection.queries
[{'sql': u'INSERT INTO "myapp_datemodel" ("date_field") VALUES (2008-08-22 00:00:00)',
  'time': '0.003'},
 {'sql': u'SELECT "myapp_datemodel"."id", "myapp_datemodel"."date_field" FROM "myapp_datemodel"',
  'time': '0.001'},
 {'sql': u'SELECT "myapp_datemodel"."id", "myapp_datemodel"."date_field" FROM "myapp_datemodel" WHERE django_extract("month", "myapp_datemodel"."date_field") = 8',
  'time': '0.001'},
 {'sql': u'SELECT "myapp_datemodel"."id", "myapp_datemodel"."date_field" FROM "myapp_datemodel" WHERE django_extract("month", "myapp_datemodel"."date_field") = 8',
  'time': '0.000'}]

I said that each time I used an interactive debugger (the one in Pydev for Eclipse), I got the correct result even when using a string for __month. I managed to repeat it today. Each time I follow the execution quite deeply step by step, I get the correct result (__date as string returns the item). If I run the code through the debugger without any breakpoints, or with a breakpoint, but continuing instead of following the execution step by step, I get the wrong result (__date as string doesn't return any items).

These symptoms sound a lot like a race condition, don't they? I didn't expect to find any threading code in the ORM, but a simple grep proved me wrong. I haven't looked at the threading code, so I don't know what it does, but maybe this is a race condition after all?

I will attach a simple test case, which passes in MySQL, but not in SQLite. I haven't testet it with other DB backends.

Changed 10 years ago by haakeyar

Simple test case. Passes in MySQL, but not in SQLite

comment:3 Changed 10 years ago by zhaoz

Triage Stage: UnreviewedAccepted

I was able to reproduce this. (Python 2.5.2)

comment:4 Changed 10 years ago by James Bennett

Resolution: duplicate
Status: newclosed

#8214 is the same issue and has a patch which claims to fix it.

comment:5 Changed 7 years ago by Jacob

milestone: 1.0

Milestone 1.0 deleted

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