Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#30379 closed Bug (duplicate)

ProgrammingError from exclude using models.F with long relation

Reported by: Peter Law Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Peter Law)

The following query emits a ProgrammingError due to the query omitting the definitions of a number of references it uses:

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.

Alert.objects.exclude(
    item__order_items__order__user=the_user,
)

Models:

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:

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:

-- 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))

Change History (3)

comment:1 by Peter Law, 5 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 5 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #21703.

comment:3 by Peter Law, 5 years ago

Interestingly, attempting to cut this down further results in both the relative and specific spellings of the query emitting similar errors.
Given:

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

class UserItem(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='user_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')

then both

Alert.objects.exclude(item__user_items__user=models.F('user'))

and

Alert.objects.exclude(item__user_items__user=the_user)

both emit ProgrammingErrors.

Last edited 5 years ago by Peter Law (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top