#24386: Querysets with filters and exclusions based on deep relations build
invalid
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/047.a863db610e9a63c09a95598558556d6e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
