[Django] #29697: Missing FROM-clause entry for table

Django

unread,

Aug 21, 2018, 1:04:19 PM8/21/18

to django-...@googlegroups.com

#29697: Missing FROM-clause entry for table
-------------------------------------+-------------------------------------
Reporter: Dmitry | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29697>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Aug 22, 2018, 9:35:59 AM8/22/18

to django-...@googlegroups.com

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

-------------------------------------+-------------------------------------
Reporter: Dmitry | Owner: nobody
Type: Bug | Status: new

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* stage: Unreviewed => Accepted

Old description:

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

raising error:

--

Comment:

Reproduced at 50b8493581fea3d7137dd8db33bac7008868d23a.

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:1>

Django

unread,

Oct 8, 2018, 8:19:11 AM10/8/18

to django-...@googlegroups.com

#29697: Complex query crashes with "missing FROM-clause entry for table"
-------------------------------------+-------------------------------------
Reporter: Dmitry | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned

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 Can Sarıgöl):

* owner: nobody => Can Sarıgöl
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:2>

Django

unread,

Oct 8, 2018, 3:51:26 PM10/8/18

to django-...@googlegroups.com

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Can Sarıgöl):

Hello. I guess the problem is in trim start function. I want to change
this code like above. 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])

}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:3>

Django

unread,

Oct 10, 2018, 5:56:52 AM10/10/18

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Can Sarıgöl):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/10498 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:4>

Django

unread,

Nov 3, 2018, 9:58:25 AM11/3/18

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* needs_better_patch: 0 => 1

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:5>

Django

unread,

Mar 18, 2019, 5:10:27 AM3/18/19

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Can Sarıgöl):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:6>

Django

unread,

Mar 29, 2019, 3:13:41 PM3/29/19

to django-...@googlegroups.com

#29697: Complex query crashes with "missing FROM-clause entry for table"
-------------------------------------+-------------------------------------
Reporter: Dmitry | Owner: Can
| Sarıgöl

Type: Bug | Status: closed

Component: Database layer | Version: 2.1
(models, ORM) |

Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* status: assigned => closed
* resolution: => fixed

Comment:

Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:7>

Django

unread,

Mar 30, 2019, 10:08:02 AM3/30/19

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"96446c7152c115beee925bbd1f3273a3634d7983" 96446c71]:
{{{
#!CommitTicketReference repository=""
revision="96446c7152c115beee925bbd1f3273a3634d7983"
Refs #29697 -- Added test for excluding m2m intermediary table.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29697#comment:8>

Reply all

Reply to author

Forward