#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 [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/068.64366c77618c37e39a34a2e956f1a00e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
