#33929: Field Reference in FilteredRelation Does Not Recognize Previously
Defined
FilteredRelation
-------------------------------------+-------------------------------------
Reporter: Matt | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Nick Vellios):
I hope to add some more context after looking this over. It appears as
though Matt is correct regarding being related to
https://code.djangoproject.com/ticket/33766. The fields are not being
properly aliased.
App name in my tests is `interface` which I left references of in the
console output, but I removed the `interface_` prefix from the formatted
SQL queries for readability.
{{{
>>> qs = A.objects.annotate(
... binc=FilteredRelation("b", condition=Q(b__complete=False)),
... cinc=FilteredRelation("c", condition=Q(c__b=F("binc__pk"),
c__complete=False)),
... b_count=Count("binc"),
... c_count=Count("cinc"),
... )
>>> qs.query.alias_map
{'interface_a': <django.db.models.sql.datastructures.BaseTable object at
0x11095fa60>, 'binc': <django.db.models.sql.datastructures.Join object at
0x11095c6a0>, 'cinc': <django.db.models.sql.datastructures.Join object at
0x11095de10>}
}}}
Invoking the query compiler adds the naive alias `T4` to the query which
also later shows up in the `Query.alias_map`.
{{{
>>> str(qs.query)
SELECT
"a"."id",
COUNT(binc."id") AS "b_count",
COUNT(cinc."id") AS "c_count"
FROM "a"
LEFT OUTER JOIN "b" binc
ON ("a"."id" = binc."a_id"
AND (NOT binc."complete"))
LEFT OUTER JOIN "c" cinc
ON ("a"."id" = cinc."a_id"
AND ((cinc."b_id" = (T4."id")
AND NOT cinc."complete")))
GROUP BY "a"."id"
}}}
Notice the incrementing `Tn` on the `Query.alias_map` but only the last
one is referenced in the resulting query:
{{{
>>> qs.query.alias_map
{'interface_a': <django.db.models.sql.datastructures.BaseTable object at
0x11095fa60>, 'binc': <django.db.models.sql.datastructures.Join object at
0x11095c6a0>, 'cinc': <django.db.models.sql.datastructures.Join object at
0x11095de10>, 'T4': <django.db.models.sql.dat
astructures.Join object at 0x1077991b0>, 'T5':
<django.db.models.sql.datastructures.Join object at 0x10778b400>}
>>> str(qs.query)
'SELECT "interface_a"."id", COUNT(binc."id") AS "b_count",
COUNT(cinc."id") AS "c_count" FROM "interface_a" LEFT OUTER JOIN
"interface_b" binc ON ("interface_a"."id" = binc."a_id" AND (NOT
binc."complete")) LEFT OUTER JOIN "interface_c" cinc ON
("interface_a"."id" =
cinc."a_id" AND ((cinc."b_id" = (T6."id") AND NOT cinc."complete")))
GROUP BY "interface_a"."id"'
>>> str(qs.query)
'SELECT "interface_a"."id", COUNT(binc."id") AS "b_count",
COUNT(cinc."id") AS "c_count" FROM "interface_a" LEFT OUTER JOIN
"interface_b" binc ON ("interface_a"."id" = binc."a_id" AND (NOT
binc."complete")) LEFT OUTER JOIN "interface_c" cinc ON
("interface_a"."id" =
cinc."a_id" AND ((cinc."b_id" = (T8."id") AND NOT cinc."complete")))
GROUP BY "interface_a"."id"'
>>> qs.query.alias_map
{'interface_a': <django.db.models.sql.datastructures.BaseTable object at
0x11095fa60>, 'binc': <django.db.models.sql.datastructures.Join object at
0x11095c6a0>, 'cinc': <django.db.models.sql.datastructures.Join object at
0x11095de10>, 'T4': <django.db.models.sql.dat
astructures.Join object at 0x1077991b0>, 'T5':
<django.db.models.sql.datastructures.Join object at 0x10778b400>, 'T6':
<django.db.models.sql.datastructures.Join object at 0x11099b640>, 'T7':
<django.db.models.sql.datastructures.Join object at 0x110834580>, 'T8':
<dj
ango.db.models.sql.datastructures.Join object at 0x110ad0610>, 'T9':
<django.db.models.sql.datastructures.Join object at 0x110ad03a0>}
>>> str(qs.query)
SELECT
"a"."id",
COUNT(binc."id") AS "b_count",
COUNT(cinc."id") AS "c_count"
FROM "a"
LEFT OUTER JOIN "b" binc
ON ("a"."id" = binc."a_id"
AND (NOT binc."complete"))
LEFT OUTER JOIN "c" cinc
ON ("a"."id" = cinc."a_id"
AND ((cinc."b_id" = (T9"id")
AND NOT cinc."complete")))
GROUP BY "a"."id"
}}}
The following works but selects and groups by one additional field
`binc_pk`. Postgres Query Planner output included. On a larger queryset
this could get expensive.
{{{
A.objects.annotate(
binc=FilteredRelation("b", condition=Q(b__complete=False)),
binc_pk=F('binc__pk'), # <-- Allows elimination of relying on raw
SQL, however...
cinc=FilteredRelation("c", condition=Q(c__b=F('binc_pk'),
c__complete=False)),
b_count=Count("binc"),
c_count=Count("cinc"),
)
}}}
{{{
SELECT
"a"."id",
binc."id" AS "binc_pk", /* <-- Ugly */
COUNT(binc."id") AS "b_count",
COUNT(cinc."id") AS "c_count"
FROM "a"
LEFT OUTER JOIN "b" binc
ON ("a"."id" = binc."a_id"
AND (NOT binc."complete"))
LEFT OUTER JOIN "c" cinc
ON ("a"."id" = cinc."a_id"
AND ((cinc."b_id" = (binc."id")
AND NOT cinc."complete")))
GROUP BY "a"."id",
binc."id" /* <-- Ugly */
}}}
Not ideal.
{{{
➜ ~ psql -d dj_issue_33929
psql (14.4)
Type "help" for help.
dj_issue_33929=# EXPLAIN SELECT
dj_issue_33929-# "interface_a"."id",
dj_issue_33929-# binc."id" AS "binc_pk",
dj_issue_33929-# COUNT(binc."id") AS "b_count",
dj_issue_33929-# COUNT(cinc."id") AS "c_count"
dj_issue_33929-# FROM "interface_a"
dj_issue_33929-# LEFT OUTER JOIN "interface_b" binc
dj_issue_33929-# ON ("interface_a"."id" = binc."a_id"
dj_issue_33929(# AND (NOT binc."complete"))
dj_issue_33929-# LEFT OUTER JOIN "interface_c" cinc
dj_issue_33929-# ON ("interface_a"."id" = cinc."a_id"
dj_issue_33929(# AND ((cinc."b_id" = (binc."id")
dj_issue_33929(# AND NOT cinc."complete")))
dj_issue_33929-# GROUP BY "interface_a"."id",
dj_issue_33929-# binc."id";
QUERY PLAN
---------------------------------------------------------------------------------------
HashAggregate (cost=162.74..185.34 rows=2260 width=32)
Group Key: interface_a.id, binc.id
-> Hash Left Join (cost=97.80..140.14 rows=2260 width=24)
Hash Cond: ((interface_a.id = cinc.a_id) AND (binc.id =
cinc.b_id))
-> Hash Right Join (cost=60.85..91.33 rows=2260 width=16)
Hash Cond: (binc.a_id = interface_a.id)
-> Seq Scan on interface_b binc (cost=0.00..28.10
rows=905 width=16)
Filter: (NOT complete)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on interface_a (cost=0.00..32.60
rows=2260 width=8)
-> Hash (cost=25.40..25.40 rows=770 width=24)
-> Seq Scan on interface_c cinc (cost=0.00..25.40
rows=770 width=24)
Filter: (NOT complete)
(13 rows)
dj_issue_33929=# EXPLAIN SELECT
dj_issue_33929-# "interface_a"."id",
dj_issue_33929-# COUNT(binc."id") AS "b_count",
dj_issue_33929-# COUNT(cinc."id") AS "c_count"
dj_issue_33929-# FROM "interface_a"
dj_issue_33929-# LEFT OUTER JOIN "interface_b" binc
dj_issue_33929-# ON ("interface_a"."id" = binc."a_id"
dj_issue_33929(# AND (NOT binc."complete"))
dj_issue_33929-# LEFT OUTER JOIN "interface_c" cinc
dj_issue_33929-# ON ("interface_a"."id" = cinc."a_id"
dj_issue_33929(# AND ((cinc."b_id" = (binc.id)
dj_issue_33929(# AND NOT cinc."complete")))
dj_issue_33929-# GROUP BY "interface_a"."id";
QUERY PLAN
---------------------------------------------------------------------------------------
HashAggregate (cost=157.09..179.69 rows=2260 width=24)
Group Key: interface_a.id
-> Hash Left Join (cost=97.80..140.14 rows=2260 width=24)
Hash Cond: ((interface_a.id = cinc.a_id) AND (binc.id =
cinc.b_id))
-> Hash Right Join (cost=60.85..91.33 rows=2260 width=16)
Hash Cond: (binc.a_id = interface_a.id)
-> Seq Scan on interface_b binc (cost=0.00..28.10
rows=905 width=16)
Filter: (NOT complete)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on interface_a (cost=0.00..32.60
rows=2260 width=8)
-> Hash (cost=25.40..25.40 rows=770 width=24)
-> Seq Scan on interface_c cinc (cost=0.00..25.40
rows=770 width=24)
Filter: (NOT complete)
(13 rows)
}}}
Instinctively I looked into the implementation of `OuterRef` for ideas,
but the implementation is quite contrasting to FilteredRelation.
--
Ticket URL: <https://code.djangoproject.com/ticket/33929#comment:1>
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/01070182a994190d-66690d69-0473-4010-8ff0-6b8cec4c8383-000000%40eu-central-1.amazonses.com.
