Opened 15 years ago

Closed 9 years ago

#11459 closed Bug (wontfix)

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

Reported by: eddified Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords: QuerySet.extra
Cc: Jamey Sharp, j Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Aymeric Augustin)

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.

Change History (11)

comment:1 by eddified, 15 years ago

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.

comment:2 by Alex Gaynor, 15 years ago

Resolution: duplicate
Status: newclosed

Dupe of #11329.

comment:3 by Jamey Sharp, 13 years ago

Cc: Jamey Sharp added
Component: UncategorizedDatabase layer (models, ORM)
Easy pickings: unset
Resolution: duplicate
Severity: Normal
Status: closedreopened
Type: Bug
UI/UX: unset
Version: 1.01.3

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.

comment:4 by Alex Ogier, 13 years ago

Cc: j added

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

comment:5 by Aymeric Augustin, 12 years ago

Description: modified (diff)

Fixed formatting (please use preview).

EDIT: sorry, I saw you reposted a properly formatted report afterwards!

Last edited 12 years ago by Aymeric Augustin (previous) (diff)

comment:6 by Aymeric Augustin, 12 years ago

Triage Stage: UnreviewedAccepted

comment:7 by Aymeric Augustin, 12 years ago

Status: reopenednew

comment:8 by Tim Graham, 11 years ago

#18870 is a duplicate.

comment:9 by Anssi Kääriäinen, 11 years ago

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

comment:10 by Tim Graham, 9 years ago

Keywords: QuerySet.extra added

comment:11 by Tim Graham, 9 years ago

Resolution: wontfix
Status: newclosed

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

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