Opened 9 years ago

Closed 9 years ago

#10352 closed (invalid)

Query.as_sql() breaks annotations

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


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")...

Attachments (1)

10352-test.diff (652 bytes) - added by Glenn Maynard 9 years ago.

Download all attachments as: .zip

Change History (8)

Changed 9 years ago by Glenn Maynard

Attachment: 10352-test.diff added

comment:1 Changed 9 years ago by Malcolm Tredinnick

Triage Stage: UnreviewedAccepted

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).

comment:2 Changed 9 years ago by Alex Gaynor

Doing a similar query looks ok for me: .

comment:3 Changed 9 years ago by 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).

comment:4 Changed 9 years ago by 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?

comment:5 Changed 9 years ago by 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.

comment:6 Changed 9 years ago by 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.

comment:7 Changed 9 years ago by Malcolm Tredinnick

Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top