Query.as_sql() breaks annotations

[Glenn Maynard]

Calling as_sql() on a query with annotations breaks the query later. This is particularly annoying because it's a delayed effect. Repro attached.

ProgrammingError: missing FROM-clause entry for table "U0"
LINE 1: ...em"."creator_id", "queries_item"."note_id", COUNT("U0"."id")...

[Malcolm Tredinnick]

Something weird going on here. The query printed out by test.as_sql() in your failing example doesn't look even close to the right query (there's no "count" bit there).

[Alex Gaynor]

Doing a similar query looks ok for me: ​http://dpaste.com/1265/ .

[Malcolm Tredinnick]

Oh, right.. I understand the difference now. The patch is using QuerySet.as_sql(), not Query.as_sql().

Question for the original reporter: is this report part of some larger problem? What are you actually doing that triggers this? I'm guessing a nested query of some kind, which is probably fine, but I'd like some confirmation.

Normally, you shouldn't ever be calling QuerySet.as_sql(). In fact, I'm fairly likely to change the name prior to Django 1.1 to indicate it's a true internal method (currently the name makes some other code a bit simpler, but the cost in inadvertent usability mistakes is probably too high, on reflection).

[Glenn Maynard]

So far, just for examining specific queries when debugging. It only started showing problems after a trunk update. Is there a method to get the resulting SQL query (as far as is known so far, anyway) without unwanted side-effects on the object itself?

[Russell Keith-Magee]

@Glenn: I think what you're looking for is QuerySet.query.as_sql().

As Malcolm has noted, as_sql() on a QuerySet is an internal method that is used to manipulate a QuerySet so it can be used for subquery evaluation - it actually changes the queryset into a ValuesQuerySet. It is not intended to be public API. If calling QuerySet.as_sql() appeared to be working previously, it was almost certainly an accidental result stemming from the bugs that were recently closed.

In the test case you provided, lines 1049-50 should read:

>>> test = Item.objects.annotate(cnt=Count("id"))
>>> test.query.as_sql()

This asks Django for the underlying query that will extract the data from the database; since it is an SQL query, the as_sql() call on the underlying query will render that query as the SQL + parameters that will be sent to the database.

The problem as reported is therefore an invalid report; however, I'll leave this open as a marker to Malcolm about renaming as_sql() so that others don't mistakenly use that method as public API.

[Malcolm Tredinnick]

(In [9928]) To avoid an unfortunately common user-error, rename QuerySet.as_sql().

This was never a public API method, so this is backwards compatible, unless
you're poking at the internals. Refs #10352.