Document how to to use a literal % in Func.template

[Matt Cooper]

I have a database function to annotate the week of a date. It worked in 1.8 but breaks in 1.9a1, failing with a "ValueError: unsupported format character 'W'" exception.

class Week(Func):
    function = 'strftime'
    template = "%(function)s('%%W',%(expressions)s)"

def data_last_n_weeks(user, week_count):
    assert week_count > 0, "week_count must be greater than 0"
    assert week_count < 52, "week_count must be less than 52"

    today = date.today()
    most_recent_monday = today - timedelta(days=(today.isoweekday()-1))
    start_date = most_recent_monday - timedelta(days=7*(week_count-1))

    data = user.serving_set.filter(date__range=(start_date, today)
            ).annotate(week=Week('date', output_field=IntegerField())).values(
            'week').annotate(amt=Sum('amount'), cost=Sum('cost'))

    return data

yields

  File "/Users/[me]/Projects/[proj]/ENV/lib/python3.4/site-packages/django/db/backends/sqlite3/operations.py", line 133, in last_executed_query
    return sql % params
ValueError: unsupported format character 'W' (0x57) at index 18

[Aymeric Augustin]

I think we need to re-escape % after executing template % self.extra in Func.as_sql.

[Tim Graham]

I'm having trouble getting the database function you provided working on 1.8. Am I doing something wrong? I tried Poll.objects.annotate(week=Week('pub_date', output_field=models.IntegerField()).

This gives:

  File "/home/tim/code/django/django/db/backends/utils.py", line 66, in execute
    return self.cursor.execute(sql, params)
IndexError: tuple index out of range

with sql and params:
SELECT "polls_poll"."id", "polls_poll"."question", "polls_poll"."pub_date", "polls_poll"."amount", "polls_poll"."cost", strftime('%W',"polls_poll"."pub_date") AS "week" FROM "polls_poll" LIMIT 21
().

[Matt Cooper]

I don't see anything wrong with the simplified case you wrote. I'm not in a place where I can generate the sql from my repro but I'll get it tonight.

One possible difference is that Serving.date is a DateField while I suspect Poll.pub_date is a DateTimeField. That could matter.

Also, I've only tested with SQLite - possible that this is database dependent?

[Aymeric Augustin]

Looking at the excerpt of the stack trace you posted I'm pretty sure it only happens on SQLite.

[Matt Cooper]

I couldn't repro the IndexError in my setup. Here are two execution logs, one on 1.8.4 and the other on 1.9.0a1 using the exact same code and (a copy of) the exact same database. This time I haven't manually doctored the output for simplification as I did earlier.

1.8.4:

(3NV) mattc ~/Projects/beer30/beersite $ ./manage.py shell
Python 3.4.2 (default, Oct 19 2014, 12:51:48) 
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.54)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.contrib.auth.models import User
>>> from api.views import beer_data_last_n_weeks
>>> u = User.objects.get(pk=1)
>>> beer_data_last_n_weeks(u, 5)
[{'cost': Decimal('41.00'), 'week': 35, 'oz': Decimal('228.0')}, {'cost': Decimal('41.50'), 'week': 36, 'oz': Decimal('158.0')}, {'cost': Decimal('28.93'), 'week': 37, 'oz': Decimal('198.6')}, {'cost': Decimal('24.66'), 'week': 38, 'oz': Decimal('128.0')}, {'cost': Decimal('3.33'), 'week': 39, 'oz': Decimal('12.0')}]

1.9.0a1:

(3NV) mattc ~/Projects/beer31/beersite $ ./manage.py shell
Python 3.4.3 (default, Aug 11 2015, 08:57:25) 
[GCC 4.2.1 Compatible Apple LLVM 6.1.0 (clang-602.0.53)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.contrib.auth.models import User
>>> from api.views import beer_data_last_n_weeks
>>> u = User.objects.get(pk=1)
>>> beer_data_last_n_weeks(u, 5)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/query.py", line 234, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/query.py", line 112, in __iter__
    for row in compiler.results_iter():
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 806, in results_iter
    results = self.execute_sql(MULTI)
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 852, in execute_sql
    cursor.execute(sql, params)
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/backends/utils.py", line 83, in execute
    sql = self.db.ops.last_executed_query(self.cursor, sql, params)
  File "/Users/mattc/Projects/beer31/3NV/lib/python3.4/site-packages/django/db/backends/sqlite3/operations.py", line 133, in last_executed_query
    return sql % params
ValueError: unsupported format character 'W' (0x57) at index 18

Relevant code snippets:

api/views.py

class Week(Func):
    function = 'strftime'
    template = "%(function)s('%%W',%(expressions)s)"


def beer_data_last_n_weeks(user, week_count):
    assert week_count > 0, "week_count must be greater than 0"
    assert week_count < 52, "week_count must be less than 52"

    today = date.today()
    most_recent_monday = today - timedelta(days=(today.isoweekday()-1))
    start_date = most_recent_monday - timedelta(days=7*(week_count-1))

    beer_data = user.serving_set.filter(date__range=(start_date, today)
            ).annotate(week=Week('date', output_field=IntegerField())).values(
            'week').annotate(oz=Sum('amount'), cost=Sum('cost'))

    return beer_data

datamodel/models.py

class Serving(models.Model):
    SERVING_CHOICES = (
        (0, "unknown"),
        (1, "tap"),
        (2, "bottle"),
        (4, "can"),
        (3, "growler"),
    )
    beer = models.ForeignKey(Beer)
    amount = models.DecimalField(max_digits=4, decimal_places=1)
    date = models.DateField()
    type = models.SmallIntegerField(choices=SERVING_CHOICES, default=1)
    owner = models.ForeignKey(User)
    location = models.CharField(max_length=100, blank=True)
    cost = models.DecimalField(max_digits=5,
                               decimal_places=2, null=True, blank=True)

    def __str__(self):
        return " ".join([str(self.amount), "oz of", self.beer.name])

[Tim Graham]

I'm attaching a sample app based on the models you provided (I removed the foreign key to Beer since you didn't provide that model), however, the test works without error for me. Does it work for you? If so, could you say how to modify it to reproduce the error? If not, then perhaps it's something like a difference between versions of SQLite.

System details:
Python 3.4.3 (default, Mar 5 2015, 15:15:09)
[GCC 4.8.2] on linux
sqlite3 2.6.0

[Matt Cooper]

Your test case does not fail on the Windows machine I currently have available (Windows 10.0.10240 with SQLite3 2.6.0, sqlite3.sqlite_version 3.8.3.1). Will check on my Mac later tonight - that's where I've observed the failure.

From looking at Apple's online man pages, 3.1.3 is shown in the sample outputs.

[Matt Cooper]

Argh, that test case doesn't fail on my Mac either. The real app still exhibits the behavior, so I'll keep digging and see what it takes to make the test case fail.

[Matt Cooper]

I'm so sorry. Somewhere between my full app and your test case there's a cutover point where this starts failing on 1.9a1, but I can't find it. I've learned quite a bit more about Pdb tonight than I really intended but to no avail.

1.8 and 1.9 both accept a double-escaped percent sign, so I'm going to update my code to that.

class Week(Func):
    function = 'strftime'
    template = "%(function)s('%%%%W',%(expressions)s)"

I'd be happy to share the whole project with someone from the core team if it would help further debugging.

[Tim Graham]

If you could ​use git bisect to find the commit where the behavior changed, that might yield some insight.

[Aymeric Augustin]

The exception is triggered in code I added in [4f6a7663]. However it's unclear to me why this code would be incorrect.

[Claude Paroz]

It may be because _quote_params_for_last_executed_query is bypassing the Django cursor wrapper. When the wrapper is executing a query, it takes care of not passing params when it is None. Maybe we should do the same dance in _quote_params_for_last_executed_query.

[Claude Paroz]

I think the bug is rather that the initial version didn't break in 1.8! You implicitly counted on the fact the the sqlite driver accepts the following query (2 placeholder-like occurrences, one param):

cursor.execute("SELECT strftime('%W', 'date') WHERE 42=%s", (42,))

It may be because the SQLite driver only recognizes the %s placeholder (a guess). A similar query with MySQL and DATE_FORMAT by example would produce an error.

We might fix the bug by a documentation note somewhere in custom expressions.

[Matt Cooper]

Totally fair, I probably misunderstood the docs :)

So I should have double-escaped to begin with? In retrospect that makes sense. If you guys want to doc that and close this issue, I'm good with that.

class Week(Func):
    function = 'strftime'
    template = "%(function)s('%%%%W',%(expressions)s)"

[Tim Graham]

Looking at this now, I'm not sure what the documentation note should say. Can anyone suggest something or at least give a simple description of the issue?

[Matt Cooper]

I would create a documentation PR myself, but I don't understand the issue enough to feel comfortable educating others. Summary of behavior is that when I created a custom expression, something between the code I wrote and the SQLite driver was eating some of my escape characters. I had to express a logical "%W" as "%%%%W" (four percent characters) to get it working properly. I've lost most of the context about the failure, but I recall expecting to need two percents (%%W) and being surprised that it took four.

[Claude Paroz]

I would place the note here: ​https://docs.djangoproject.com/en/1.10/ref/models/expressions/#django.db.models.Func.template

Something like (feel free to reformulate!):
If you need a literal % character to reach the database, you will need to quadruple it in the template attribute because the string will be extrapoled twice (once during the template extrapolation in as_sql(), and once in the final SQL extrapolation with the query parameters in the database cursor).

[Tim Graham]

​PR

[Claude Paroz]

Patch looks good!

[GitHub <noreply@…>]

In c60feb69:

Fixed #25475 -- Doc'd how to use a literal % in Func.template.

[Tim Graham <timograham@…>]

In be535960:

[1.10.x] Fixed #25475 -- Doc'd how to use a literal % in Func.template.

Backport of c60feb6999f83bfd1fdabff01f0dd4a26d72e158 from master