Opened 6 years ago

Closed 6 years ago

Last modified 2 years ago

#11117 closed (duplicate)

placeholder confusion when using aggregated query with extra

Reported by: slide21@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Let's assume something like this:

Book.objects.extra(select={'date': "DATE_FORMAT(timestamp, '%s')"}, select_params=('%%H',), where=["DATE_FORMAT(timestamp, '%%H:%%i') > '%s'"], params=['22:00']).values('date').annotate(cnt=Count('id'))

the resulting sql query is:

SELECT DATE_FORMAT(timestamp, '%H') AS `date`, COUNT(id) AS `cnt` FROM `books` WHERE DATE_FORMAT(timestamp, '%H:%i') > '%H' GROUP BY DATE_FORMAT(timestamp, '22:00') ORDER BY timestamp ASC

the placeholder values '%H' and '22:00' are interchanged, which is obviously a result of using the GROUP BY here on a user-defined variable with placeholder (--> 'date')

.query.as_sql() gives something like

(u"SELECT DATE_FORMAT(timestamp, '%s') AS `date`, COUNT(id) AS `cnt` FROM `books` WHERE DATE_FORMAT(timestamp, '%H:%i') > '%s' GROUP BY DATE_FORMAT(timestamp, '%s') ORDER BY timestamp ASC", (u'%H', u'%H', u'22:00'))

Change History (2)

comment:1 Changed 6 years ago by russellm

  • Component changed from Database layer (models, ORM) to ORM aggregation
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

I'm going to close this as a dupe of #11104. It's not a literal duplicate - the problem that is being described is different - but the underlying cause (mishandling of SQL params) is the same. I'll put a link on #11104 to ensure that this problem is also fixed.

comment:2 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top