#30379: ProgrammingError from exclude using models.F with long relation
-------------------------------------+-------------------------------------
Reporter: PeterJCLaw | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by PeterJCLaw:
Old description:
> 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))
> }}}
New description:
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))
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30379#comment:1>
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/068.64366c77618c37e39a34a2e956f1a00e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
