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
30379	ProgrammingError from exclude using models.F with long relation	Peter Law	nobody	"The following query emits a `ProgrammingError` due to the query omitting the definitions of a number of references it uses:
{{{#!python
Alert.objects.exclude(
    item__order_items__order__user=models.F('user'),
)
}}}

{{{
django.db.utils.ProgrammingError: missing FROM-clause entry for table ""u2""
}}}

The spelling using a specific user does seem to work correctly, hinting that the issue is somehow related to the handling of the `models.F` component.

{{{#!python
Alert.objects.exclude(
    item__order_items__order__user=the_user,
)
}}}

Models:
{{{#!python
from django.db import models

class Item(models.Model):
    name = models.TextField()

class Order(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)

class UnsizedOrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='items')
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='order_items')

class Alert(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='alerts')
}}}

I originally saw this with another layer of many-to-many between the item and the order, but that turned out not to be necessary to reproduce the issue:
{{{#!python
class SizedItem(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='sizes')
    size = models.TextField()

class SizedOrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='sized_items')
    sized_item = models.ForeignKey(SizedItem, on_delete=models.CASCADE, related_name='sized_order_items')

Alert.objects.exclude(
    item__sizes__sized_order_items__order__user=models.F('user'),
)
}}}

Traceback:
{{{
  File "".../lib/python3.5/site-packages/django/db/models/query.py"", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "".../lib/python3.5/site-packages/django/db/models/sql/compiler.py"", line 1097, in execute_sql
    cursor.execute(sql, params)
  File "".../lib/python3.5/site-packages/sentry_sdk/integrations/django/__init__.py"", line 298, in execute
    return real_execute(self, sql, params)
  File "".../lib/python3.5/site-packages/django/db/backends/utils.py"", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "".../lib/python3.5/site-packages/django/db/backends/utils.py"", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "".../lib/python3.5/site-packages/django/db/backends/utils.py"", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "".../lib/python3.5/site-packages/django/db/utils.py"", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "".../lib/python3.5/site-packages/django/db/backends/utils.py"", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: missing FROM-clause entry for table ""u2""
LINE 1: ...JOIN ""liked_email_alerts_unsizedorderitem"" U3 ON (U2.""id"" = ...
                                                             ^
}}}

Versions:
 * I've seen this under both 2.1.7 and 2.2
 * I'm using Python 3.5.2
 * I'm using Postgres 9.6 (psycopg2-binary==2.7.7)

For reference here's the SQL generated by printing the `.query` on the querysets:
{{{#!sql
-- relative

SELECT
    ""liked_email_alerts_alert"".""id"",
    ""liked_email_alerts_alert"".""user_id"",
    ""liked_email_alerts_alert"".""item_id""
FROM
    ""liked_email_alerts_alert""
WHERE
    NOT (""liked_email_alerts_alert"".""item_id"" IN (
            SELECT
                U3. ""item_id""
            FROM
                ""liked_email_alerts_alert"" U0
                INNER JOIN ""liked_email_alerts_unsizedorderitem"" U3 ON (U2. ""id"" = U3. ""item_id"")
                INNER JOIN ""liked_email_alerts_order"" U4 ON (U3. ""order_id"" = U4. ""id"")
            WHERE
                U4. ""user_id"" = (U0. ""user_id"")));

-- specific

SELECT
    ""liked_email_alerts_alert"".""id"",
    ""liked_email_alerts_alert"".""user_id"",
    ""liked_email_alerts_alert"".""item_id""
FROM
    ""liked_email_alerts_alert""
WHERE
    NOT (""liked_email_alerts_alert"".""item_id"" IN (
            SELECT
                U2. ""item_id""
            FROM
                ""liked_email_alerts_unsizedorderitem"" U2
                INNER JOIN ""liked_email_alerts_order"" U3 ON (U2. ""order_id"" = U3. ""id"")
            WHERE
                U3. ""user_id"" = 13))
}}}"	Bug	new	Database layer (models, ORM)	2.2	Normal				Unreviewed	0	0	0	0	0	0