Opened 6 years ago

Closed 6 years ago

#12611 closed (wontfix)

Incorrect quoting in QuerySet.query.__str__()

Reported by: jmcvetta Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: sql quoting
Cc: Triage Stage: Someday/Maybe
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

When I call __str__() on a QuerySet object's query member, it returns invalid SQL. Arguments to the WHERE clause are not quoted. This bug makes it difficult to log queries then feed them to the database's EXPLAIN command for performance analysis.

For instance, if we construct a QuerySet like this:

ogtt75_events = [
    'ogtt75_fasting_pos',
    'ogtt75_random_pos',
    'ogtt75_30m_pos',
    'ogtt75_1hr_pos',
    'ogtt75_90m_pos',
    'ogtt75_2hr_pos',
    ]
q_obj = Q(name__in=ogtt75_events)
q_obj &= Q(patient__event__name='pregnancy_diagnosis')
qs = Event.objects.filter(q_obj)

it will return SQL like this (fed through sqlparse library for pretty indentation):

SELECT "hef_event"."id",
       "hef_event"."name",
       "hef_event"."date",
       "hef_event"."patient_id",
       "hef_event"."timestamp",
       "hef_event"."run_id",
       "hef_event"."content_type_id",
       "hef_event"."object_id"
FROM "hef_event"
INNER JOIN "emr_patient" ON ("hef_event"."patient_id" = "emr_patient"."id")
INNER JOIN "hef_event" T3 ON ("emr_patient"."id" = T3."patient_id")
WHERE ("hef_event"."name" IN (ogtt75_fasting_pos,
                              ogtt75_random_pos,
                              ogtt75_30m_pos,
                              ogtt75_1hr_pos,
                              ogtt75_90m_pos,
                              ogtt75_2hr_pos)
       AND T3."name" = pregnancy_diagnosis)
ORDER BY "hef_event"."date" ASC, "hef_event"."patient_id" ASC, "hef_event"."name" ASC

This behavior was observed using the latest trunk checkout of Django, with PostgreSQL as backend.

Change History (1)

comment:1 Changed 6 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed
  • Triage Stage changed from Unreviewed to Someday/Maybe

I completely acknowledge the problem, but it is non-trivial to fix. Database quoting is actually done by the database cursor at the time of query execution. What is returned by QuerySet.query is the best attempt at rendering the query as it will be executed, but there's no guarantee it will be completely accurate.

Marking wontfix due to the absence of a workable solution. If anyone can propose something (that doesn't involve reimplementing the full quoting behavior of a DB cursor), please reopen with that suggestion.

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