Opened 5 years ago

Last modified 5 years ago

#30770 closed Bug

annotating TruncBase on ExpressionWrapper for date arithmetic, causes unnecessary placeholder "%s" escaping to "%%s" and TypeError — at Version 1

Reported by: Jurgis Pralgauskis Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: TruncBase, DateTime arithmetics,
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Jurgis Pralgauskis)

I tried:

from django.db.models import F,  DateTimeField, ExpressionWrapper
from django.db.models.functions import Cast, Least, Greatest, Now, Coalesce, ExtractWeekDay, TruncDay

User.objects
   .annotate(trial_end=TruncDay(ExpressionWrapper(F('date_joined')+ timedelta(days=30), output_field=DateTimeField())))
   .values('id', 'date_joined', 'trial_end')[:3]
)

and got 

Traceback (most recent call last):
  File "<input>", line 3, in <module>
  File "lib/python3.4/site-packages/django/db/models/query.py", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "lib/python3.4/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "lib/python3.4/site-packages/django/db/models/query.py", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "lib/python3.4/site-packages/django/db/models/query.py", line 106, in __iter__
    for row in compiler.results_iter():
  File "lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 836, in results_iter
    results = self.execute_sql(MULTI)
  File "lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 894, in execute_sql
    raise original_exception
  File "lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 884, in execute_sql
    cursor.execute(sql, params)
  File "lib/python3.4/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "lib/python3.4/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
TypeError: not all arguments converted during string formatting

and

 qs.query.sql_with_params()[0]

shows 

'SELECT "ta_account"."id", "ta_account"."date_joined", DATE_TRUNC(\'day\', ("ta_account"."date_joined" + %%s) AT TIME ZONE %s) AS "trial_end" FROM "ta_account" LIMIT 3'

notice %%s -- that causes problem..

Change History (1)

comment:1 by Jurgis Pralgauskis, 5 years ago

Description: modified (diff)
Summary: annotating TruncBase on ExpressionWrapper for date arithmetic, causes unnecessary placeholder "%s" escaping to "%%s" → annotating TruncBase on ExpressionWrapper for date arithmetic, causes unnecessary placeholder "%s" escaping to "%%s" and TypeError

ps.:
here one can workaround by exchanging TruncDay with ExpressionWrapper:

trial_end=ExpressionWrapper(TruncDay('date_joined')+ timedelta(days=30))

But my situation was more complex (I was doing arithmetics in minutes and applying TruncDay few steps later )

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