id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24386	Querysets with filters and exclusions based on deep relations build invalid queries.	Raphael Gaschignard		"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 
"	Bug	closed	Database layer (models, ORM)	dev	Normal	duplicate		Can Sarıgöl	Accepted	0	0	0	0	0	0