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