Opened 7 years ago

Closed 6 years ago

#18870 closed Bug (duplicate)

QuerySet.count() generates invalid sql when used with select_related() and extra()

Reported by: bkvaiude@… Owned by: B001
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


total = IntentStats.objects.filter().extra(where=['date(intentapp_intent.created_at) between \+start_date.str()+'\' and \+end_date.str()+'\],tables=intentapp_intent?).select_related('intent')


SELECT, intentapp_intentstats.intent_id, intentapp_intentstats.category, intentapp_intentstats.support_count, intentapp_intentstats.comment_count, intentapp_intentstats.adoption_count, intentapp_intentstats.updated_at,, intentapp_intent.user_id, intentapp_intent.body, intentapp_intent.category, intentapp_intent.why_important, intentapp_intent.can_help, intentapp_intent.inspiration, intentapp_intent.parent_id, intentapp_intent.deleted, intentapp_intent.sponsored, intentapp_intent.fulfilled_at, intentapp_intent.created_at, intentapp_intent.updated_at, intentapp_intent.legacy_intent_id, intentapp_intent.homepage_location FROM intentapp_intentstats INNER JOIN intentapp_intent ON (intentapp_intentstats.intent_id = WHERE date(intentapp_intent.created_at) between '2012-08-27' and '2012-08-28'

I got this output through django shell
Ans: 0

But when call it in my app it gives me XXX count.

SELECT COUNT(*) FROM intentapp_intentstats , intentapp_intent WHERE date(intentapp_intent.created_at) between '2012-08-27' and '2012-08-28'

Please let me any solution for above issue.

thanks in advanced.

Change History (3)

comment:1 Changed 7 years ago by Anssi Kääriäinen

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug
Version: 1.3-beta1.4

What is happening here is that the select_related() call is removed for .count() call. In addition, the .extra(tables) is defined in a way that it uses an existing join if there is one, otherwise it just appends the table to the from list. So, if you have an existing join or not radically changes the results of the .extra(tables) call. The existing join in this case is from the select_related().

I think you should define the join condition in .extra(where). It will be redundant in the non-count() case, but it should work for both queries.

It is a bit unfortunate the .extra(tables) works this way. In general, the .extra() has potential errors like this, and they are hard to fix.

I am going to accept this, but at least personally I am not going to use a lot of effort to fix this. The extra is basically a hack that is nearly impossible to fix cleanly for all errors like this. Instead improving the ORM in ways that make .extra() non-necessary to begin with seems like the way to go.

If somebody comes up with an easy fix for this, I am happy to apply it of course.

BTW did you intentionally set the version to 1.3-beta? If so, you should really be using something else. I verified this on 1.4.

comment:2 Changed 7 years ago by anonymous

As I checked in my app version is,
import django; print(django.get_version())

There is no intention.

I solved it following way

total=total.annotate().count() #Bug #18870 Link Reference:

Thanks for reply.

comment:3 Changed 6 years ago by Tim Graham

Resolution: duplicate
Status: newclosed

Looks like a duplicate of #11459.

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