Complex query crashes with "missing FROM-clause entry for table"

[Dmitry]

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.

[Tim Graham]

Reproduced at 50b8493581fea3d7137dd8db33bac7008868d23a.

[Can Sarıgöl]

Hello. I guess the problem is in Query.trim_start function. if a table is a reference between two related tables and its columns are not used, its amount of alias_refcount is updated by zero. In this case, I checked "query.where.children[0].children[0].rhs.alias_refcount" and result-> {'U0': 1, 'U1': 0, 'U2': 1}. Because of that U1 wasn't included in join query.

I want to change this code like below. What do you think?

class Query:
    """A single SQL query."""
    ...

   def trim_start(self, names_with_path):
       ...
       lookup_tables = [
            t for t in self.alias_map
            if t in self._lookup_joins or t == self.base_table
        ]
        # from_opts_tables for manage to define unref_alias table.
        # before unref, make sure the table wasn't added
        # in another from_opts as reference.
        **from_opts_tables = [path.from_opts.db_table for path in all_paths]**
        for trimmed_paths, path in enumerate(all_paths):
            ...
            # is the alias exists in another from_opts?
            **if not alias in from_opts_tables:**
                self.unref_alias(alias)
        ...
        if self.alias_map[lookup_tables[trimmed_paths + 1]].join_type != LOUTER:
           ....
           **if not lookup_tables[trimmed_paths] in from_opts_tables:**
               self.unref_alias(lookup_tables[trimmed_paths])
       

[Can Sarıgöl]

​PR

[Tim Graham]

Simon on the PR, "I think we should find a way to test the multi-alias case and address it before merging this change."

[Mariusz Felisiak]

Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 96446c71:

Refs #29697 -- Added test for excluding m2m intermediary table.