[Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries
Django
queries
----------------------------------------------+--------------------
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I've hit this bug in 1.6, and reproduced it on master (django
v1.9.dev20150221182749)
I have the following models:
{{{
class Owner(models.Model):
pass
class Employee(models.Model):
owner = models.ForeignKey(Owner)
status = models.CharField(max_length=100)
start_date = models.DateField()
}}}
When trying to find the oldest active employees for each owner, I tried
the following query:
{{{
>>> query =
Employee.objects.filter(status='active').exclude(owner__employee__start_date__lte=F('start_date'),owner__employee__status='active').distinct()
>>> query
[...]
File "/Users/rtpg/proj/test_django/django-
trunk/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/Users/rtpg/proj/test_django/django-trunk/django/db/utils.py",
line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/Users/rtpg/proj/test_django/django-
trunk/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
ProgrammingError: missing FROM-clause entry for table "u1"
LINE 1: ...e_employee" U0 INNER JOIN "core_employee" U2 ON ( U1."id" = ...
^
}}}
The SQL query generated by the queryset seems to be at fault
{{{
>>> print query.query
SELECT DISTINCT "core_employee"."id", "core_employee"."owner_id",
"core_employee"."status", "core_employee"."start_date" FROM
"core_employee" WHERE ("core_employee"."status" = active AND NOT
("core_employee"."owner_id" IN (SELECT U2."owner_id" AS Col1 FROM
"core_employee" U2 WHERE U2."status" = active) AND
"core_employee"."owner_id" IN (SELECT U2."owner_id" AS Col1 FROM
"core_employee" U0 INNER JOIN "core_employee" U2 ON ( U1."id" =
U2."owner_id" ) WHERE U2."start_date" <= (U0."start_date"))))
}}}
I'm not very good at reading complex SQL queries, so I'm not sure what
this is supposed to look like but in any case this query is refused by
PostgreSQL
--
Ticket URL: <https://code.djangoproject.com/ticket/24386>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
queries
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by shaib):
* needs_better_patch: => 0
* stage: Unreviewed => Accepted
* needs_tests: => 1
* needs_docs: => 0
Comment:
Accepting based on details in report, didn't reproduce myself.
Just wanted to note for the submitter that even without the bug, the query
you wrote would not achieve the stated goals -- it would return no
employees. To do what you intended, you need to replace your `lte` with
`lt`. Also, another approach for this relies on annotations (annotate
employee with `Min(owner__employee__startdate)` and filter on that).
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:1>
Django
queries
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by rtpg):
I don't know if annotations allow me to work around this. I need to find
the minimum of only active employees, but to my knowledge the aggregates
don't really work in combination with filters:
I added a {{{number}}} field to my Employee model:
{{{
In [5]: Employee.objects.annotate(Sum('owner__employee__number')).values()
Out[5]: [{'status': u'not_active', 'number': 3, u'id': 2,
'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
23), u'owner_id': 1}, {'status': u'active', 'number': 4, u'id': 1,
'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
23), u'owner_id': 1}]
In [6]:
Employee.objects.filter(number=4).annotate(Sum('owner__employee__number')).values()
Out[6]: [{'status': u'active', 'number': 4, u'id': 1,
'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
23), u'owner_id': 1}]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:2>
Django
queries
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
To find the oldest active employees for each owner you could use a
combination of `DISTINCT ON` and `ORDER BY`:
{{{#!python
Employee.objects.filter(status='active').order_by('owner',
'start_time').distinct('owner')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:3>
Django
queries
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by rtpg):
Seemed I lacked a bit of imagination on that one, thanks for the
workaround for my specific use case charettes. Though I guess that
wouldn't work on a non-Postgres DB.
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:4>
Django
queries
Reporter: rtpg | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by dgouldin):
It looks like this is as a result of using the combination of an exclude
filter with multiple joins and an F object. The multi-join exclude causes
the ORM to represent the filter as a subquery, but since that Query object
doesn't know about its parent, it has to try to make sense of the F object
in the context of the subquery.
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:5>
Django
queries
Reporter: Raphael Gaschignard | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* status: new => assigned
* owner: nobody => Can Sarıgöl
Comment:
this issue has been solved by
[https://github.com/django/django/blob/fc2536fe66c519b306f673672b795d16f87ed57d/django/db/models/sql/query.py#L1664
this lines] of Simon's [https://github.com/django/django/pull/11062/files
#diff-0edd853580d56db07e4020728d59e193R1659 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:6>
Django
queries
Reporter: Raphael Gaschignard | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* status: assigned => new
* cc: Can Sarıgöl (added)
* owner: Can Sarıgöl => (none)
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:7>
Django
queries
Reporter: Raphael Gaschignard | Owner: (none)
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Can Sarıgöl):
I think, [https://github.com/django/django/pull/11142 this test] covers
this.
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Raphael Gaschignard | Owner: (none)
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: new => closed
* resolution: => duplicate
* needs_tests: 1 => 0
Comment:
Test added in 9ab1d5559681d3adde4fcfc98a19a7396ed6a42e.
Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.
Duplicate of #18726.
--
Ticket URL: <https://code.djangoproject.com/ticket/24386#comment:9>