Opened 5 years ago

Closed 5 years ago

Last modified 4 years ago

#12818 closed (fixed)

sqlite3 backend should use single quotes for string values passed as args to django_date_trunc and django_extract

Reported by: lakinwecker Owned by: SmileyChris
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords:
Cc: lakin@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Test case models.py:

from django.db import models

#------------------------------------------------------------------------------
class SimpleModel(models.Model):
    month = models.PositiveIntegerField()
    some_date = models.DateTimeField()

#------------------------------------------------------------------------------
class SecondModel(models.Model):
    year = models.PositiveIntegerField()
    some_date = models.DateTimeField()

Test case admin.py:

from django.contrib import admin
from datehierarchy.testbed.models import SimpleModel, SecondModel

#-------------------------------------------------------------------------------
class SimpleModelAdmin(admin.ModelAdmin):
    list_display = ('month', 'some_date',)
    date_hierarchy = 'some_date'

admin.site.register(SimpleModel, SimpleModelAdmin)


#-------------------------------------------------------------------------------
class SecondModelAdmin(admin.ModelAdmin):
    list_display = ('year', 'some_date',)
    date_hierarchy = 'some_date'

# Content Models
admin.site.register(SecondModel, SecondModelAdmin)

Produces tracebacks like this even if valid dates are present:

Traceback:
File "/home/lakin/Desktop/django/core/handlers/base.py" in get_response
  92.                 response = callback(request, *callback_args, **callback_kwargs)
File "/home/lakin/Desktop/django/contrib/admin/options.py" in wrapper
  226.                 return self.admin_site.admin_view(view)(*args, **kwargs)
File "/home/lakin/Desktop/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "/home/lakin/Desktop/django/contrib/admin/sites.py" in inner
  186.             return view(request, *args, **kwargs)
File "/home/lakin/Desktop/django/contrib/admin/options.py" in changelist_view
  986.         ], context, context_instance=context_instance)
File "/home/lakin/Desktop/django/shortcuts/__init__.py" in render_to_response
  20.     return HttpResponse(loader.render_to_string(*args, **kwargs), **httpresponse_kwargs)
File "/home/lakin/Desktop/django/template/loader.py" in render_to_string
  108.     return t.render(context_instance)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/home/lakin/Desktop/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/home/lakin/Desktop/django/template/loader_tags.py" in render
  97.         return compiled_parent.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/home/lakin/Desktop/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/home/lakin/Desktop/django/template/loader_tags.py" in render
  97.         return compiled_parent.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/home/lakin/Desktop/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/home/lakin/Desktop/django/template/loader_tags.py" in render
  24.         result = self.nodelist.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/home/lakin/Desktop/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/home/lakin/Desktop/django/template/loader_tags.py" in render
  24.         result = self.nodelist.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/home/lakin/Desktop/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/home/lakin/Desktop/django/template/__init__.py" in render
  936.                     dict = func(*args)
File "/home/lakin/Desktop/django/contrib/admin/templatetags/admin_list.py" in date_hierarchy
  313.                 } for year in years]
File "/home/lakin/Desktop/django/db/models/query.py" in _result_iter
  106.                 self._fill_cache()
File "/home/lakin/Desktop/django/db/models/query.py" in _fill_cache
  692.                     self._result_cache.append(self._iter.next())
File "/home/lakin/Desktop/django/db/models/sql/subqueries.py" in results_iter
  397.                     date = typecast_timestamp(str(date))
File "/home/lakin/Desktop/django/db/backends/util.py" in typecast_timestamp
  68.     if not ' ' in s: return typecast_date(s)
File "/home/lakin/Desktop/django/db/backends/util.py" in typecast_date
  53.     return s and datetime.date(*map(int, s.split('-'))) or None # returns None if s is null

Exception Type: ValueError at /admin/testbed/secondmodel/
Exception Value: invalid literal for int() with base 10: 'None'

This is due to the use of double quotes in queries like the following:

SELECT DISTINCT django_date_trunc('year', "testbed_secondmodel"."some_date") FROM "testbed_secondmodel" ORDER BY 1 ASC

Double quotes refer to column names, and int he above test case, the table testbed_secondmodel has a column named year, and as such the value of year is passed into the the python function _sqlite_date_trunc - instead of the string "year". Instead, if one uses single quotes as per the string constant section in http://www.sqlite.org/lang_expr.html - it works just fine.

Possible patch:

Index: sqlite3/base.py
===================================================================
--- sqlite3/base.py	(revision 12398)
+++ sqlite3/base.py	(working copy)
@@ -65,12 +65,12 @@
     def date_extract_sql(self, lookup_type, field_name):
         # sqlite doesn't support extract, so we fake it with the user-defined
         # function django_extract that's registered in connect().
-        return 'django_extract("%s", %s)' % (lookup_type.lower(), field_name)
+        return 'django_extract(\'%s\', %s)' % (lookup_type.lower(), field_name)
 
     def date_trunc_sql(self, lookup_type, field_name):
         # sqlite doesn't support DATE_TRUNC, so we fake it with a user-defined
         # function django_date_trunc that's registered in connect().
-        return 'django_date_trunc("%s", %s)' % (lookup_type.lower(), field_name)
+        return 'django_date_trunc(\'%s\', %s)' % (lookup_type.lower(), field_name)
 
     def drop_foreignkey_sql(self):
         return ""

Attachments (2)

12818.diff (4.2 KB) - added by SmileyChris 5 years ago.
12818.2.diff (4.0 KB) - added by SmileyChris 5 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 Changed 5 years ago by SmileyChris

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

I can confirm the issue. It's pretty obvious what's going wrong (and well summarised in the title of this ticket)

comment:2 Changed 5 years ago by lakinwecker

  • Cc lakin@… added

Changed 5 years ago by SmileyChris

comment:3 Changed 5 years ago by SmileyChris

  • milestone set to 1.2
  • Needs tests unset
  • Owner changed from nobody to SmileyChris
  • Status changed from new to assigned
  • Triage Stage changed from Accepted to Ready for checkin

Changed 5 years ago by SmileyChris

comment:4 Changed 5 years ago by Alex

  • Has patch set

comment:5 Changed 5 years ago by jkocherhans

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

(In [12573]) Fixed #12818. SQLite now properly quotes strings for date extraction and truncation. Thanks, SmilyChris.

comment:6 Changed 5 years ago by jkocherhans

(In [12575]) [1.1.X] Fixed #12818. SQLite now properly quotes strings for date extraction and truncation. Backport of r12573 from trunk.

comment:7 Changed 4 years ago by jacob

  • milestone 1.2 deleted

Milestone 1.2 deleted

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