str(QuerySet.query) returns broken SQL for filter on text field

[Bernd Wechner]

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.

[Tim Graham]

Duplicate of #25705.