#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.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/060.d5c5957a6703fbe23f5ec8efb6eb6bb5%40djangoproject.com.
