[Django] #32839: Unnecessary qoutes around table name with in subquery

3 views

Skip to first unread message

Django

unread,

Jun 11, 2021, 6:47:27 AM6/11/21

to django-...@googlegroups.com

#32839: Unnecessary qoutes around table name with in subquery
-------------------------------------+-------------------------------------
Reporter: Bálint | Owner: nobody
Balina |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: Subquery, annotate,
Severity: Normal | FilteredRelation, OuterRef
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I am using a combination of subquery with filtered relations, and i
receive the error: missing FROM-clause entry for table "table alias"

{{{
from django.db.models import FilteredRelation, Q, OuterRef, Subquery
from django.db.models.functions import Coalesce

qs = models.Product.objects.annotate(
profile1=FilteredRelation('product_profiles',
condition=Q(product_profiles__id=1)),
profile2=FilteredRelation('product_profiles',
condition=Q(product_profiles__id=2)),
category_name=Subquery(
models.ProductCategoryRelationship.objects.filter(
product_profile=Coalesce(OuterRef('profile1__id'),
OuterRef('profile2__id'))
).values('category__name')[:1]
),
).values('id', 'category_name')
}}}

The above expression generates the below query

{{{
SELECT "product"."id",
(SELECT U2."name"
FROM "product_category_relationship" U0
INNER JOIN "product_category" U2 ON (U0."category_id" =
U2."id")
WHERE U0."product_profile_id" = (COALESCE("profile1"."id",
"T3"."id"))) AS "x"
FROM "product"
LEFT OUTER JOIN "product_profile" profile1 ON ("product"."id" =
profile1."product_id" AND (profile1."id" = 1))
LEFT OUTER JOIN "product_profile" T3 ON ("product"."id" =
T3."product_id" AND (T3."id" = 2))
}}}

where in the subquery's coalesce part there is "T3"."id" instead of
T3."id".

I have drilled down the problem somewhat, and the problematic code is
this:

{{{
# django.db.models.sql.compiler.py::383

def quote_name_unless_alias(self, name):
"""
A wrapper around connection.ops.quote_name that doesn't quote
aliases
for table names. This avoids problems with some SQL dialects that
treat
quoted strings specially (e.g. PostgreSQL).
"""
if name in self.quote_cache:
return self.quote_cache[name]
if ((name in self.query.alias_map and name not in
self.query.table_map) or
name in self.query.extra_select or (
name in self.query.external_aliases and name not in
self.query.table_map)):
self.quote_cache[name] = name
return name
r = self.connection.ops.quote_name(name)
self.quote_cache[name] = r
return r
}}}

There is that complex condition about the alias being a table name or not,
which can't see the outer query's aliases.

{{{
ipdb> self.query.alias_map
OrderedDict([('U0', <django.db.models.sql.datastructures.BaseTable object
at 0x7fac19d66e80>), ('U1', <django.db.models.sql.datastructures.Join
object at 0x7fac19d4d100>), ('U2',
<django.db.models.sql.datastructures.Join object at 0x7fac19dc07f0>)])

ipdb> self.query.table_map
{'product_category_relationship': ['U0'], 'product_profile': ['U1'],
'product_category': ['U2']}

ipdb> self.query.extra_select
{}

ipdb> self.query.external_aliases
set()

}}}

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

Django

unread,

Jun 11, 2021, 7:15:12 AM6/11/21

to django-...@googlegroups.com

#32839: Unnecessary qoutes around table name with in subquery
-------------------------------------+-------------------------------------

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