QuerySet.count() generates invalid sql when used with select_related() and extra(where=[customWhereClause])

[eddified]

Here is the error message:

(1054, "Unknown column 'beyondStore_artist.name' in 'where clause'")

This is the invalid sql that calling count() is generating that causes the above error message:

SELECT COUNT(*) FROM `sss_song` WHERE (match(sss_song.title) against('hits')) or (match(sss_artist.name, sss_artist.description) against('hits')) or (match(sss_album.title, sss_album.description) against('hits'))

Here is the code that generates the QuerySet:

songs = models.Song.objects.select_related('album__genre__genre__id', 'artist__name').extra(where = [where_clause])
t = songs.count() # sql error is caused by this call to count()

I know that the query to get the actual data works just fine. It is only the COUNT that is not working. The work around was to get the count manually (by writing an sql statement that gets the count, and executing the statement 'manually' using connection.cursor().execute(sql)).
The two arguments passed to "select related" are both valid.

The problem is that the 'where' clause uses the two tables that are specified by the "select_related" call, but the count() function apparently ignores the select_related call.

Perhaps the QuerySet.count() function should be smart enough to take the "select_related" args into consideration when generating the COUNT sql.

I did try to specify missing tables using tables=[...] as an argument to extra(), but I couldn't get it to work with both the count() and the actual data query.

[eddified]

Oops. I forgot to change "beyondStore_artist.name" to "sss_artist.name" in the error message. (Everywhere it says "sss" in the original post, I had changed it from "beyondStore" to "sss" manually before posting, trying to protect client's identity.) Just wanted to clear up that "sss_artist.name"==="beyondStore_artist.name" because I changed one instance but forgot to change the other.

Also, the wikiformatting messed up my intentions in the Original Post. (Should have used preview, sorry).

Here is the original post, again:

Here is the error message:

(1054, "Unknown column 'sss_artist.name' in 'where clause'")

This is the invalid sql that calling count() is generating that causes the above error message:

SELECT COUNT(*) FROM `sss_song` WHERE (match(sss_song.title) against('hits')) or (match(sss_artist.name, sss_artist.description) against('hits')) or (match(sss_album.title, sss_album.description) against('hits'))

Here is the code that generates the QuerySet:

songs = models.Song.objects.select_related('album__genre__genre__id', 'artist__name').extra(where = [where_clause])
t = songs.count() # sql error is caused by this call to count()

I know that the query to get the actual data works just fine. It is only the COUNT that is not working. The work around was to get the count manually (by writing an sql statement that gets the count, and executing the statement 'manually' using connection.cursor().execute(sql)).
The two arguments passed to "select related" are both valid.

The problem is that the 'where' clause uses the two tables that are specified by the "select_related" call, but the count() function apparently ignores the select_related call, so the 'where' clause tacked onto the COUNT sql fails.

Perhaps the QuerySet.count() function should be smart enough to take the "select_related" args into consideration when generating the COUNT sql.

I did try to specify missing tables using tables=[...] as an argument to extra(), but I couldn't get it to work with both the count() and the actual data query.

[Alex Gaynor]

Dupe of #11329.

[Jamey Sharp]

I don't think this is a duplicate of #11329. While both bugs show up when using extra(where=...), that bug is about column names introduced in extra(select=...). This bug is about table names introduced with select_related().

In particular, the patch in the other bug doesn't work around the issue for code that looks like

qs.select_related('entry').extra(where=["entry.name='foo'"])

even though that patch happens to mask this bug if there's a .extra(select=...) in the query as well.

I can confirm that this bug still exists in Django 1.3.1. Also, qs.exists() fails on the same queries.

[Alex Ogier]

I worked around a similar problem by calling .annotate() to force a sub-select query. I think the problem is specific to MySQL. Can you confirm that the following works?

songs = models.Song.objects.select_related('album__genre__genre__id', 'artist__name').extra(where = [where_clause])
t = songs.annotate().count()

[Aymeric Augustin]

Fixed formatting (please use preview).

[Tim Graham]

#18870 is a duplicate.

[Anssi Kääriäinen]

Like I commented some time ago in ticket #18870, I don't see any easy solution here. Not doing select_related is the right choice for .count(), joins generated by it can't affect the count so it will just slow down the query. The exception is usage of .extra(), but Django can't know if the joins that would be generated by select_related are used by extra. So, if this is going to be fixed there must be some way to tell Django which select_related() clauses are going to be needed even in case of .count(). Maybe extra(tables=...) could be used for this?

As a temporary workaround it is possible to use a no-op filter (for example Q(album__genre__genre__id__isnull=True)|Q(album__genre__genre__id__isnull=False). This will guarantee that joins are generated even in case of .count().

[Tim Graham]

We are no longer fixing bugs with QuerySet.extra() per ​discussion on django-developers.