[patch] sqlite selection on datetime members fail when datefield is null

[anonymous]

Doing selects like model.objects.filter(datefield__year=2007) fail when a datefield contains a NULL value.
Patch follows as a code block:

Index: django/db/backends/sqlite3/base.py
===================================================================
--- django/db/backends/sqlite3/base.py	(revision 4501)
+++ django/db/backends/sqlite3/base.py	(working copy)
@@ -119,6 +119,7 @@
     return 'django_extract("%s", %s)' % (lookup_type.lower(), table_name)
 
 def _sqlite_extract(lookup_type, dt):
+    if not dt: return None
     try:
         dt = util.typecast_timestamp(dt)
     except (ValueError, TypeError):

Thanks for all the good work!

Niels Poppe

[Michael Radziej <mir@…>]

I haven't tested it, but this rather springs into my eye as a valid bug report.

Two things about the patch:

• It should test for dt is None.

• Django style requires that you use another line

A test case should be provided, too.

[anonymous]

Okay... i've confirmed testing for None fixes the problem, added a newline, but i'm not yet up to creating a test case.
Regards, Niels Poppe

Index: django/db/backends/sqlite3/base.py
===================================================================
--- django/db/backends/sqlite3/base.py	(revision 4528)
+++ django/db/backends/sqlite3/base.py	(working copy)
@@ -119,6 +119,8 @@
     return 'django_extract("%s", %s)' % (lookup_type.lower(), table_name)
 
 def _sqlite_extract(lookup_type, dt):
+    if dt is None:
+        return None
     try:
         dt = util.typecast_timestamp(dt)
     except (ValueError, TypeError):

[nick.lane.au@…]

I had the same problem and had to use a similar patch to fix it. See also #1468.

[nick.lane.au@…]

Oops, they aren't exactly related in the way I first thought.

[Peter Baumgartner]

Works for me in current SVN r6326

[mrts]

In Version1.1Features, someone has raised the issue if the bug is still open as of 1.0. Though the patch has not been applied, Django works as expected:

>>> from bug3501.datefield_null.models import DateStamp
>>> import datetime
>>> d = DateStamp(date=None)
>>> d.save()
>>> d = DateStamp(date=datetime.date.today())
>>> d.save()
>>> DateStamp.objects.filter(date__year=2008)
[<DateStamp: DateStamp object>]
>>> DateStamp.objects.filter(date__year=2007)
[]
>>> DateStamp.objects.filter(date=None)
[<DateStamp: DateStamp object>]

where DATABASE_ENGINE = 'sqlite3' and datefield_null/models.py contains the following:

class DateStamp(models.Model):
    date = models.DateField(blank=True, null=True)

So, this ticket was and is fixed.

[jerry]

The problem appears to be with .filter(datemonth=x). Using 1.0.1 I can successfully perform the tests above on my own data, but when I try month (without the above patch applied) I get:

Album.objects.filter(purchaseDatemonth=3)

Traceback (most recent call last):

File "<console>", line 1, in <module>
File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 147, in repr

data = list(self[:REPR_OUTPUT_SIZE + 1])

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 162, in len

self._result_cache.extend(list(self._iter))

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 275, in iterator

for row in self.query.results_iter():

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 206, in results_iter

for rows in self.execute_sql(MULTI):

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql

cursor.execute(sql, params)

File "/Library/Python/2.5/site-packages/django/db/backends/sqlite3/base.py", line 168, in execute

return Database.Cursor.execute(self, query, params)

OperationalError: user-defined function raised exception

The same with day; it fails as above; but applying the patch to _sqlite_extract, the errors go away and the filter works as expected. (And I can use the admin to focus on the date_hierarchy field.)

[jerry]

Sorry about the formatting. This should have been:

The problem appears to be with .filter(date__month=x).
Using 1.0.1 I can successfully perform the tests above on my own data,
but when I try __month (without the above patch applied) I get:

>>> Album.objects.filter(purchaseDate__month=3)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 147, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 162, in __len__
    self._result_cache.extend(list(self._iter))
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 275, in iterator
    for row in self.query.results_iter():
  File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql
    cursor.execute(sql, params)
  File "/Library/Python/2.5/site-packages/django/db/backends/sqlite3/base.py", line 168, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: user-defined function raised exception

The same with __day; it fails as above; but applying the patch to _sqlite_extract,
the errors go away and the filter works as expected. (And I can use the admin
to focus on the date_hierarchy field.)

[Karen Tracey]

Whenever there are multiple reports of people saying "it's broken" and "it works for me" it helps if people identify not only the version of Django they are using, but the version of related software, in this case sqlite. So the output from:

>>> import sqlite3
>>> sqlite3.dbapi2.sqlite_version_info

in a Python prompt could be useful.

[jerry]

I've got sqlite 3.4.0 on Mac OS X 10.5.5.

George:~ jerry$ python
Python 2.5.1 (r251:54863, Jan 17 2008, 19:35:16) 
[GCC 4.0.1 (Apple Inc. build 5465)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.dbapi2.sqlite_version_info
(3, 4, 0)
>>> 

[mrts]

Indeed, using date__month causes the error.

Given

$ cat datefield_null/models.py
from django.db import models

class DateStamp(models.Model):
    date = models.DateField(blank=True, null=True)

$ grep DATABASE settings.py
DATABASE_ENGINE = 'sqlite3'           # 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
DATABASE_NAME = os.path.join(PROJDIR, 'test.db')             # Or path to database file if using sqlite3.
DATABASE_USER = ''             # Not used with sqlite3.
DATABASE_PASSWORD = ''         # Not used with sqlite3.
DATABASE_HOST = ''             # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = ''             # Set to empty string for default. Not used with sqlite3.

$ python -c 'import sqlite3; print sqlite3.dbapi2.sqlite_version_info'
(3, 5, 9)

$ python -c 'import django; print django.get_version()'
1.0.2 pre-alpha SVN-9462

$ cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=8.10
DISTRIB_CODENAME=intrepid
DISTRIB_DESCRIPTION="Ubuntu 8.10"

the following occurs:

>>> from bug3501.datefield_null.models import DateStamp
>>> import datetime
>>> d = DateStamp(date=None)
>>> d.save()
>>> d = DateStamp(date=datetime.date.today())
>>> d.save()
>>> DateStamp.objects.filter(date__year=2008)
[<DateStamp: DateStamp object>, <DateStamp: DateStamp object>]
>>> DateStamp.objects.filter(date__month=2)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 147, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 162, in __len__
    self._result_cache.extend(list(self._iter))
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 275, in iterator
    for row in self.query.results_iter():
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py", line 168, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: user-defined function raised exception

[Malcolm Tredinnick]

(In [9466]) Fixed #3501 -- Fixed date filtering in querysets for nullable date fields. Only
affects SQLite.

[fero]

For me the patched version did not work.

In order to support also blank=True DateTime fields I apply the following patch.

--- django/db/backends/sqlite3/base.py  2009-02-12 12:24:12.000000000 +0100
+++ django/db/backends/sqlite3/base.py.new      2009-02-12 12:23:52.000000000 +0100
@@ -179,7 +179,7 @@
         return query % tuple("?" * num_params)

 def _sqlite_extract(lookup_type, dt):
-    if dt is None:
+    if dt in (None, u""):
         return None
     try:
         dt = util.typecast_timestamp(dt)

[Malcolm Tredinnick]

The issue raised in this ticket (nullable fields) was fixed last year. If you have some other problem, please open a new ticket, including a description of how to repeat the problem. We cannot evaluate patches in isolation from understanding how they are caused, since the cause might not be valid usage.

Also, the version fo SQLite you are running will be useful to know, since there are differences caused by that. Reclosing.