#29021: Weird behavior of foreign key lookup and annotate F expression
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Keywords: django-model, | Triage Stage:
foreign-key | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Paul):
* status: closed => new
* resolution: invalid =>
Old description:
> {{{
> 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
> }}}
>
> Why is numbering so strange (starts with `T3`)? But moreover why `n2` is
> assigned to `T4`, not `T5`? Same for `n4` and `T6`. Looks like numbers go
> by 2.
>
> What I want to accomplish is capture token id on each step of inner join.
> It works when there are one join, but then it breaks.
>
> Any suggestions?
New description:
{{{
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 no `T4."id" AS "n2"` instead of `T5."id" AS "n2"`, so chaining is
working incorrectly.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:2>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.43a5951bd5fd3e57a8f76a7bf504e7b4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
