#29697: Complex query crashes with "missing FROM-clause entry for table"
-------------------------------------+-------------------------------------
Reporter: Dmitry | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Old description:
> python version: 3.6.6
> database backend: psycopg2 v2.7.5
>
> Hi. I have next models in my project:
>
> {{{
> class Status(models.Model):
> ...
> company = models.ForeignKey(
> 'customauth.Company',
> on_delete=models.CASCADE)
> key = models.SlugField(max_length=255)
> ...
>
> class User(models.Model):
> ...
> company = models.ForeignKey(
> 'customauth.Company',
> on_delete=models.CASCADE,
> related_name='employees',
> db_index=True)
> permissions = postgres.fields.JSONField(blank=True, default=list)
> is_superuser = models.BooleanField(default=False)
> ...
>
> class Company(models.Model):
> ...
> }}}
>
> When trying execute next code:
>
> {{{
> Status.objects.filter(
> Q(company__employees__is_superuser=True) |
> ~Q(company__employees__permissions__has_key=Concat(Value('records.'),
> 'key', Value('.denied'))),
> company__employees__pk=4)
> }}}
>
> raising error:
>
> {{{
> Traceback (most recent call last):
> File "C:\Users\user\py366-tpr\lib\site-
> packages\django\db\backends\utils.py", line 85, in _execute
> return self.cursor.execute(sql, params)
> psycopg2.ProgrammingError: missing FROM-clause entry for table "u1"
> LINE 1: ...status" U0 INNER JOIN "customauth_user" U2 ON (U1."id" = ...
> ^
> }}}
>
> SQL after call .query() look like this:
>
> {{{
> SELECT *
> FROM records_status
> INNER JOIN customauth_company ON (records_status.company_id =
> customauth_company.id)
> INNER JOIN customauth_user ON (customauth_company.id =
> customauth_user.company_id)
> WHERE
> (
> (
> customauth_user.is_superuser = True OR
> NOT (
> records_status.company_id IN (
> SELECT U2.company_id
> FROM records_status U0
> INNER JOIN customauth_user U2 ON (U1.id =
> U2.company_id)
> WHERE (
> U2.permissions ? (CONCAT('records.', CONCAT(U0.key,
> '.denied'))) AND
> U2.id = (customauth_user.id)
> )
> )
> )
> ) AND
> customauth_user.id = 4 AND
> records_status.company_id = (customauth_user.company_id)
> )
> ORDER BY records_status.key ASC;
> }}}
>
> It is not clear where U1 alias comes from.
New description:
python version: 3.6.6
database backend: psycopg2 v2.7.5
Hi. I have next models in my project:
{{{
from django.db import models
from django.contrib.postgres.fields import JSONField
class Company(models.Model):
...
class Status(models.Model):
...
company = models.ForeignKey(
Company,
on_delete=models.CASCADE)
key = models.SlugField(max_length=255)
...
class User(models.Model):
...
company = models.ForeignKey(
Company,
on_delete=models.CASCADE,
related_name='employees',
db_index=True)
permissions = JSONField(blank=True, default=list)
is_superuser = models.BooleanField(default=False)
...
}}}
When trying execute next code:
{{{
from django.db.models import Q, Value
from django.db.models.functions import Concat
Status.objects.filter(
Q(company__employees__is_superuser=True) |
~Q(company__employees__permissions__has_key=Concat(Value('records.'),
'key', Value('.denied'))),
company__employees__pk=4)
}}}
raising error:
{{{
Traceback (most recent call last):
File "C:\Users\user\py366-tpr\lib\site-
packages\django\db\backends\utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: missing FROM-clause entry for table "u1"
LINE 1: ...status" U0 INNER JOIN "customauth_user" U2 ON (U1."id" = ...
^
}}}
SQL after call .query() look like this:
{{{
SELECT *
FROM records_status
INNER JOIN customauth_company ON (records_status.company_id =
customauth_company.id)
INNER JOIN customauth_user ON (customauth_company.id =
customauth_user.company_id)
WHERE
(
(
customauth_user.is_superuser = True OR
NOT (
records_status.company_id IN (
SELECT U2.company_id
FROM records_status U0
INNER JOIN customauth_user U2 ON (U1.id =
U2.company_id)
WHERE (
U2.permissions ? (CONCAT('records.', CONCAT(U0.key,
'.denied'))) AND
U2.id = (customauth_user.id)
)
)
)
) AND
customauth_user.id = 4 AND
records_status.company_id = (customauth_user.company_id)
)
ORDER BY records_status.key ASC;
}}}
It is not clear where U1 alias comes from.
--
Comment:
Reproduced at 50b8493581fea3d7137dd8db33bac7008868d23a.
--
Ticket URL: <https://code.djangoproject.com/ticket/29697#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/065.2faf1c772adc5f5ff76d856f657e2d09%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
