[Django] #29021: Weird behavior of foreign key lookup and annotate F expression
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) | Keywords: django-model,
Severity: Normal | foreign-key
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
{{{
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/29021>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution: invalid
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
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
This looks like an "is it a bug?" or "how do I do X?" question which
should be asked on [wiki:TicketClosingReasons/UseSupportChannels 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
foreign-key | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: invalid =>
Old description:
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
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: django-model, | Triage Stage:
foreign-key | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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
> }}}
>
> There is no `T4."id" AS "n2"` instead of `T5."id" AS "n2"`, so chaining
> is working incorrectly.
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 `T4."id" AS "n2"` instead of `T5."id" AS "n2"`, so chaining is
working incorrectly.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: django-model, | Triage Stage:
foreign-key | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Is that the simplest queryset that demonstrates the issue?
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: django-model, | Triage Stage:
foreign-key | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Paul):
Replying to [comment:4 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Paul | Owner: nobody
Component: Database layer | Version: 1.10
(models, ORM) |
Keywords: django-model, | Triage Stage:
foreign-key | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:6>