id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29697	"Complex query crashes with ""missing FROM-clause entry for table"""	Dmitry	Can Sarıgöl	"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.
"	Bug	closed	Database layer (models, ORM)	2.1	Normal	fixed			Accepted	1	0	0	0	0	0