#30132: str(QuerySet.query) returns broken SQL for filter on text field
-------------------------------------+-------------------------------------
Reporter: bernd- | Owner: nobody
wechner |
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords: QuerySet SQL
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The context is a Postgresql database. The observation is that
str(QuerySet.query) returns valid SQL and is often advised as the method
for returning the SQL that a QuerySet will execute. The desire, is for
this to be true.
I cannot find anywhere in the documentation that this is guaranteed alas.
The closes I come is this:
https://docs.djangoproject.com/en/2.1/faq/models/#how-can-i-see-the-raw-
sql-queries-django-is-running
which advises checking the connection log, but this only works when DEBUG
is enabled and hence is not a production solution.
In production, we must therefore, it seems, fall back on
str(QuerySet.query), which empirically seems to work almost all the time
and be regulalrly advised in response to questions on-line as the method
to employ.
To wit, we could expect that a round trip always works:
{{{
qs=model.objects.somequeryset
sql=str(qs.query)
raw_qs=model.objects.raw(sql)
}}}
and that qs and raw_qs return the same objects.
And this in fact works most of the time. But if somequeryset is a filter
on a text field it does not. The sql above fails to quote the text literal
in the WHERE clause.
I have a clear example tested and coded here:
https://github.com/bernd-
wechner/DjangoTutorial/blob/238787c83ef8515aeeb405577980e71ff35664e8/Library/views.py
using only the very basic tutorial examples. I am looking at a list of
Book objects, and this works fine, but the code produces this output
before crashing because of ill formed SQL.
{{{
Queryset returns 1 items.
The executed SQL was:
SELECT "Library_book"."id", "Library_book"."title",
"Library_book"."author_id" FROM "Library_book" WHERE
"Library_book"."title" = 'My Life'
The SQL that queryset.query returns is:
SELECT "Library_book"."id", "Library_book"."title",
"Library_book"."author_id" FROM "Library_book" WHERE
"Library_book"."title" = My Life
}}}
The second SQL dump should be identical to the first (in fact I need it to
be on a production site - or some other way to get it!).
The crash that follows is:
{{{
django.db.utils.ProgrammingError: syntax error at or near "Life"
LINE 1: ..." FROM "Library_book" WHERE "Library_book"."title" = My Life
}}}
I've tried tracing into the Django source code to find where the second
SQL string is generated but my ignorance has got the better of me and it's
costingme too much time so I have to park it. I can't seem to single step
my debugger sensibly to any point yet where I see str(QuerySet.query)
doing its thing to generate SQL.
So instead I content myself for now in concluding that if the premise
holds (that str(QuerySet.query) should return the same SQl as the
connection Log, and SQL that can be used to generate the same result using
a raw query, then this is a bug.
I'd gladly fix it and offer a PR but alas lack the skills to do that in a
timely manner and will park it here for now as where I need this in my
production site is not on a critical path and can easily wait (i.e. I have
higher priorities calling for my skill set currently).
If the premise does not hold, and there is no expectation that
str(QuerySet.query) should return valid SQL that can be used like this, my
apologies and this becomes a support question and I will move it to that
context. The question being how can one in a production environment
without DEBUG enabled extract the SQL from a QuerySet.
This document:
https://docs.djangoproject.com/en/2.1/faq/models/#how-can-i-see-the-raw-
sql-queries-django-is-running
seems to imply Django has no production environment method for extracting
this SQL and the much advertised on-line method of str(QuerySet.query) is
dangerous and not reliable.
--
Ticket URL: <https://code.djangoproject.com/ticket/30132>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/056.063d4a66dbff808d8bd91fda14905e8f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
