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

[amiroff]

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

3. Create a test model with DateTimeField

4. Sync the database

5. Add some entries.

6. Observe how clicking "Today" or "This month" does not reveal the entries that were just created.

7. 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 ​http://www.djangoproject.com/documentation/tutorial01/

Switching to Mysql as a db backend solves the problem.

Software:
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

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

[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
Out[8]: 
[{'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.

[haakeyar]

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

[zhaoz]

I was able to reproduce this. (Python 2.5.2)

[James Bennett]

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

[Jacob]

Milestone 1.0 deleted