#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.
