Code

Opened 7 years ago

Closed 5 years ago

#3501 closed (fixed)

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

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

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

Attachments (0)

Change History (15)

comment:1 Changed 7 years ago by Michael Radziej <mir@…>

  • Needs documentation unset
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

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

  • Patch needs improvement unset
  • Summary changed from sqlite selection on datetime members fail when datefield is null to [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 Changed 7 years ago by nick.lane.au@…

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

comment:4 Changed 7 years ago by nick.lane.au@…

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

comment:5 Changed 7 years ago by anonymous

  • Owner changed from nobody to baumer1122

comment:6 Changed 7 years ago by baumer1122

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

Works for me in current SVN r6326

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

comment:8 Changed 6 years ago by jerry

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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

comment:10 Changed 6 years ago by kmtracey

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

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

comment:13 Changed 6 years ago by mtredinnick

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

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

comment:14 Changed 5 years ago by fero

  • Resolution fixed deleted
  • Status changed from closed to reopened

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

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

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.

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.