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

[Django]

#32839: Unnecessary qoutes around table name with in subquery
-+-
 Reporter:  Bálint Balina|Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  duplicate
 Keywords:  Subquery, annotate,  | Triage Stage:
  FilteredRelation, OuterRef |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak):

 * status:  new => closed
 * type:  Uncategorized => Bug
 * resolution:   => duplicate


Comment:

 Thanks for the detailed report, it's a duplicate of #29214 (see also
 #30009).

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/075.1eb57e6b6efe98beef4fed1ea5cf6ca6%40djangoproject.com.

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

[Django]

#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', ), ('U1', ), ('U2',
 )])

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