Opened 7 years ago
Closed 7 years ago
#29021 closed Bug (needsinfo)
Weird behavior of foreign key lookup and annotate F expression
Reported by: | Paul | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.10 |
Severity: | Normal | Keywords: | django-model, foreign-key |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
class Sentence(Model): name = CharField() class Tokens(Model): token = CharField() sentence = ForeignKey(Sentence, related_name='tokens')
Sentence.objects.annotate(n=Count('tokens', distinct=True)).filter(n=5).filter(tokens__name__in=['se']).annotate(n0=F('tokens')).filter(tokens__name__in=['faire']).annotate(n1=F('tokens')).filter(tokens__name__in=['faire']).annotate(n2=F('tokens')).filter(tokens__name__in=['un']).annotate(n3=F('tokens')).filter(tokens__name__in=['avoir']).annotate(n4=F('tokens'))
Above code generates the following query:
SELECT "sentence"."id", "sentence"."name" COUNT(DISTINCT "token"."id") AS "n", T3."id" AS "n0", T4."id" AS "n1", T4."id" AS "n2", T6."id" AS "n3", T6."id" AS "n4" FROM "sentence" LEFT OUTER JOIN "token" ON ("sentence"."id" = "token"."sentence_id") INNER JOIN "token" T3 ON ("sentence"."id" = T3."sentence_id") INNER JOIN "token" T4 ON ("sentence"."id" = T4."sentence_id") INNER JOIN "token" T5 ON ("sentence"."id" = T5."sentence_id") INNER JOIN "token" T6 ON ("sentence"."id" = T6."sentence_id") INNER JOIN "token" T7 ON ("sentence"."id" = T7."sentence_id") WHERE (T3."name" IN (se) AND T4."name" IN (faire) AND T5."name" IN (un) AND T6."name" IN (avoir) AND T7."name" IN (faire)) GROUP BY "word_frword"."id", T3."id", T4."id", T6."id" HAVING COUNT(DISTINCT "token"."id") = 5
There is T4."id" AS "n2"
instead of T5."id" AS "n2"
, so chaining is working incorrectly.
Change History (6)
comment:1 by , 7 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
comment:2 by , 7 years ago
Description: | modified (diff) |
---|---|
Resolution: | invalid |
Status: | closed → new |
comment:3 by , 7 years ago
Description: | modified (diff) |
---|
comment:5 by , 7 years ago
Replying to Tim Graham:
Is that the simplest queryset that demonstrates the issue?
The simplest will be with three words:
Sentence.objects.annotate(n=Count('tokens', distinct=True)).filter(n=5).filter(tokens__name__in=['se']).annotate(n0=F('tokens')).filter(tokens__name__in=['faire']).annotate(n1=F('tokens')).filter(tokens__name__in=['un']).annotate(n2=F('tokens'))
Query:
SELECT "sentence"."id", "sentence"."name", COUNT(DISTINCT "token"."id") AS "n", T3."id" AS "n0", T4."id" AS "n1", T4."id" AS "n2" FROM "sentence" LEFT OUTER JOIN "token" ON ("sentence"."id" = "token"."sentence_id") INNER JOIN "token" T3 ON ("sentence"."id" = T3."sentence_id") INNER JOIN "token" T4 ON ("sentence"."id" = T4."sentence_id") INNER JOIN "token" T5 ON ("sentence"."id" = T5."sentence_id") WHERE (T3."name" IN (se) AND T4."name" IN (faire) AND T5."name" IN (un)) GROUP BY "word_frword"."id", T3."id", T4."id" HAVING COUNT(DISTINCT "token"."id") = 5
comment:6 by , 7 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
Are you actually seeing this in Django 1.10 as per the ticket field? If so please try to test it on a newer version, e.g. 1.11 or even better 2.0. For two reasons: Django 1.10 support period has finished (https://www.djangoproject.com/weblog/2015/jun/25/roadmap/) and it could be this is a problem which is already solved.
What database backend are you using? Please reopen when you can provide this information.
This looks like an "is it a bug?" or "how do I do X?" question which should be asked on our support channels rather than in the bug tracker. If you can provide a more minimal query and explain why Django's behavior is incorrect, feel free to reopen.