Opened 17 years ago

Closed 15 years ago

#3501 closed (fixed)

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

Reported by: anonymous Owned by: Peter Baumgartner
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: sqlite
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (15)

comment:1 by Michael Radziej <mir@…>, 17 years ago

Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedAccepted

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.

comment:2 by anonymous, 17 years ago

Patch needs improvement: unset
Summary: sqlite selection on datetime members fail when datefield is null[patch] sqlite selection on datetime members fail when datefield is null

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

comment:3 by nick.lane.au@…, 17 years ago

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

comment:4 by nick.lane.au@…, 17 years ago

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

comment:5 by anonymous, 17 years ago

Owner: changed from nobody to Peter Baumgartner

comment:6 by Peter Baumgartner, 17 years ago

Resolution: worksforme
Status: newclosed

Works for me in current SVN r6326

comment:7 by mrts, 15 years ago

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.

comment:8 by jerry, 15 years ago

Resolution: worksforme
Status: closedreopened

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

comment:9 by jerry, 15 years ago

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

comment:10 by Karen Tracey, 15 years ago

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.

comment:11 by jerry, 15 years ago

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)
>>> 

comment:12 by mrts, 15 years ago

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

comment:13 by Malcolm Tredinnick, 15 years ago

Resolution: fixed
Status: reopenedclosed

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

comment:14 by fero, 15 years ago

Resolution: fixed
Status: closedreopened

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)

comment:15 by Malcolm Tredinnick, 15 years ago

Resolution: fixed
Status: reopenedclosed

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.

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