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

[bkvaiude@…]

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

total_query=total.query

---

SELECT intentapp_intentstats.id, 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.id, 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 = intentapp_intent.id) WHERE date(intentapp_intent.created_at) between '2012-08-27' and '2012-08-28'

---

total=total.count()
I got this output through django shell
Ans: 0

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

---

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.

[Anssi Kääriäinen]

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.

[anonymous]

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

There is no intention.

BTW ,
I solved it following way

total=total.annotate().count() #Bug #18870 Link Reference: https://code.djangoproject.com/ticket/11459

Thanks for reply.

[Tim Graham]

Looks like a duplicate of #11459.