Code

Opened 5 years ago

Last modified 8 months ago

#11459 new Bug

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:
Cc: jamey, 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 aaugustin)

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.

Attachments (0)

Change History (9)

comment:1 Changed 5 years ago by eddified

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 5 years ago by Alex

  • Resolution set to duplicate
  • Status changed from new to closed

Dupe of #11329.

comment:3 Changed 2 years ago by jamey

  • Cc jamey added
  • Component changed from Uncategorized to Database layer (models, ORM)
  • Easy pickings unset
  • Resolution duplicate deleted
  • Severity set to Normal
  • Status changed from closed to reopened
  • Type set to Bug
  • UI/UX unset
  • Version changed from 1.0 to 1.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 Changed 2 years ago by Clueless

  • 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 Changed 23 months ago by aaugustin

  • Description modified (diff)

Fixed formatting (please use preview).

Version 0, edited 23 months ago by aaugustin (next)

comment:6 Changed 23 months ago by aaugustin

  • Triage Stage changed from Unreviewed to Accepted

comment:7 Changed 13 months ago by aaugustin

  • Status changed from reopened to new

comment:8 Changed 8 months ago by timo

#18870 is a duplicate.

comment:9 Changed 8 months ago by akaariai

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.