Re: [Django] #11459: QuerySet.count() generates invalid sql when used with select_related() and extra(where=[customWhereClause])
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by jamey):
* status: closed => reopened
* severity: => Normal
* cc: jamey (added)
* resolution: duplicate =>
* component: Uncategorized => Database layer (models, ORM)
* version: 1.0 => 1.3
* easy: => 0
* ui_ux: => 0
* type: => Bug
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: j (added)
Comment:
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?
{{{#!python
songs = models.Song.objects.select_related('album__genre__genre__id',
'artist__name').extra(where = [where_clause])
t = songs.annotate().count()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:4>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
> 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:
> 'artist__name').extra(where = [where_clause])
>
> 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.
New description:
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:
{{{
'artist__name').extra(where = [where_clause])
}}}
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.
--
Comment (by aaugustin):
Fixed formatting (please use preview).
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:5>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by aaugustin):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:6>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by timo):
#18870 is a duplicate.
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:8>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (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().
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:9>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: => QuerySet.extra
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:10>
Django
extra(where=[customWhereClause])
-------------------------------------+-------------------------------------
Reporter: eddified | Owner: nobody
Component: Database layer | Version: 1.3
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):
* status: new => closed
* resolution: => wontfix
Comment:
We are no longer fixing bugs with `QuerySet.extra()` per
[https://groups.google.com/d/topic/django-
developers/FojuU0syO8Y/discussion discussion on django-developers].
--
Ticket URL: <https://code.djangoproject.com/ticket/11459#comment:11>