#30379: ProgrammingError from exclude using models.F with long relation
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
PeterJCLaw |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 "/home/peter-thread/thread/styleme/styleme/graphql/metrics.py",
line 112, in wrapper
return fn(wrapped_self, *args, **kwargs)
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))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30379>
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/053.e60650569edc3510fc66b7f458ad0bff%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
