[Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
#29021: Weird behavior of foreign key lookup and annotate F expression
-------------------------------------+-------------------------------------
               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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/049.09e3ba46e753294492b4bec0b7b5a7fb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
#29021: Weird behavior of foreign key lookup and annotate F expression
-------------------------------------+-------------------------------------
     Reporter:  Paul                 |                    Owner:  nobody
         Type:  Bug                  |                   Status:  closed
    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
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * 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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.48eeeab13f2314266ac820a993b0b4a4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
In reply to this post by Django
#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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
In reply to this post by Django
#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
-------------------------------------+-------------------------------------
Description changed by Paul:

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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.88ae3be6cde53a622cbf8d16d7126385%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
In reply to this post by Django
#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
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

 Is that the simplest queryset that demonstrates the issue?

--
Ticket URL: <https://code.djangoproject.com/ticket/29021#comment:4>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.ac88a802e72d796391b3e8c013f49283%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
In reply to this post by Django
#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
-------------------------------------+-------------------------------------

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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.253e6234311a37834777406d4a5773db%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29021: Weird behavior of foreign key lookup and annotate F expression

Django
In reply to this post by Django
#29021: Weird behavior of foreign key lookup and annotate F expression
-------------------------------------+-------------------------------------
     Reporter:  Paul                 |                    Owner:  nobody
         Type:  Bug                  |                   Status:  closed
    Component:  Database layer       |                  Version:  1.10
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  needsinfo
     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 Ramiro Morales):

 * 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>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.2625b12384cf7c9cc896e6240dce6d87%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.