Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak ):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 In [changeset:"6b736dd0747dc77473f1f7b691c196ef5912d7dd" 6b736dd0]:
 {{{
 #!CommitTicketReference repository=""
 revision="6b736dd0747dc77473f1f7b691c196ef5912d7dd"
 Fixed #30349 -- Fixed QuerySet.exclude() on FilteredRelation.

 Using annotated FilteredRelations raised a FieldError when coupled with
 exclude(). This is due to not passing filtered relation fields to the
 subquery created in split_exclude(). We fixed this issue by passing the
 filtered relation data to the newly created subquery.

 Secondly, in the case where an INNER JOIN is used in the excluded
 subquery, the ORM would trim the filtered relation INNER JOIN in attempt
 to simplify the query. This will also remove the ON clause filters
 generated by the FilteredRelation. We added logic to not trim the INNER
 JOIN if it is from FilteredRelation.
 }}}

-- 
Ticket URL: 
Django 
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/072.4dcf4870df2163f62e65d87abf0eb6d1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * stage:  Accepted => Ready for checkin


Comment:

 Thanks for the detailed explanation and adjustments robinh00d, the changes
 in `trim_start` do make sense.

 By the way, I wouldn't be surprised if the adjustments made here paved the
 way for adding nested relation supports to `FilteredRelation` (#29789) as
 it was removed at the last minute because of issues when interacting with
 `exclude`. No obligation to tackle this ticket next but I thought I'd
 mention it as you might have a lot of context worth reusing or sharing.

-- 
Ticket URL: 
Django 
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/072.cc3e8ff8a1ac59f1db1447d2d11abe4e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by robinh00d):

 > So the result returned is not the result we are expecting, and the query
 should look like :

 I believe the `INNER JOIN` and the `ON` clause should be generated inside
 the subquery, together with `WHERE book_alice."id" IS NOT NULL `. The
 reason being,  the `FilteredRelation`  ` book_alice__isnull=False` is
 being  used in an exclude filter. This means that a subquery is generated
 along with the filters provided by the FilteredRelation object.

 > I think the appropriate query should be

 From my understanding, the `FilteredRelation`  object is used to generate
 an `ON` clause when a `JOIN` is performed. Your query is missing both of
 these clauses. Is this intentional? Does `FilteredRelation` have other use
 cases?

 > this query should first get filter book starting with 'The book by' so
 books numbers 2, 3 and 4 then exclude all books containing 'Jane' in the
 name. We should end up with only the book 4.

 As mentioned above, because we're using a `FilteredRelation` in an
 exclude, the `FilteredRelation` filtering is generated in the subquery.
 This includes `FilteredRelation` filters created in `annotate` which means
 that this filter `book__title__startswith='The book by'` would be in the
 exclude as well.

 Investigating the code shows the ON clause is being removed in `def
 trim_start`. This happens when the ORM attempts to simplify the subquery
 by trimming the first join. I have rectified this issue by adding logic to
 not trim the `INNER JOIN` if the join has a `FilteredRelation`.

 The testcase mentioned above:
 {{{
 Author.objects.annotate(
 book_alice=FilteredRelation('book',
 condition=Q(book__title__iexact='poem by alice')),
 ).exclude(book_alice__isnull=False)
 }}}

 Now generates the following SQL code:
 {{{
 SELECT "filtered_relation_author"."id",
"filtered_relation_author"."name",
"filtered_relation_author"."content_type_id",
"filtered_relation_author"."object_id"
 FROM   "filtered_relation_author"
 WHERE  NOT (
   "filtered_relation_author"."id" IN
   (
  SELECT u0."id"
  FROM   "filtered_relation_author" U0
  INNER JOIN "filtered_relation_book" U1
  ON (
u0."id" = u1."author_id"
 AND(
   u1."title" LIKE
 poem by alice ESCAPE '\'))
  WHERE  u1."id" IS NOT NULL))
 }}}

 And the other testcase:
 {{{
 Author.objects.annotate(
 book_alice=FilteredRelation('book',
 condition=Q(book__title__startswith='The book by')),
 ).exclude(book_alice__title__contains="Jane")
 }}}

 Now generates the following SQL code:
 {{{
 SELECT "filtered_relation_author"."id",
"filtered_relation_author"."name",
"filtered_relation_author"."content_type_id",
"filtered_relation_author"."object_id"
 FROM   "filtered_relation_author"
 WHERE  NOT (
   "filtered_relation_author"."id" IN
   (
  SELECT u0."id"
  FROM   "filtered_relation_author" U0
  INNER JOIN "filtered_relation_book" U1
  ON (
u0."id" = u1."author_id"
 AND(
   u1."title" LIKE
 the book BY% ESCAPE '\'))
  WHERE  u1."title" LIKE %jane% ESCAPE '\'))
 }}}

 Here is the updated PR: https://github.com/django/django/pull/11265 .

-- 
Ticket URL: 
Django 
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 

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by robinh00d):

 Hi Lucas,

 Thanks for the feedback. I will further investigate and continue working
 on the ticket.

-- 
Ticket URL: 
Django 
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/072.dd6f986955f8d1535f323ea4eff3f94b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Lucas Miller):

 Sure,

 i've added setup datas to have a more explicit test :

 {{{
 cls.author3 = Author.objects.create(name='Rob')
 cls.book5 = Book.objects.create(
 title='Poem by Rob B',
 editor=cls.editor_b,
 author=cls.author3,
 )
 }}}

 then i'm doing

 {{{
 self.assertSequenceEqual(
 Author.objects.annotate(
 book_alice=FilteredRelation('book',
 condition=Q(book__title__startswith='The book by')),
 ).exclude(book_alice__title__contains="Jane"),
 [self.author1]
 )
 }}}

 this query should first get filter book starting with 'The book by' so
 books numbers 2, 3 and 4 then exclude all books containing 'Jane' in the
 name. We should end up with only the book 4.

 The result here is book4 and book5 :

 {{{
 ==
 FAIL: test_with_join_exclude
 (filtered_relation.tests.FilteredRelationTests)
 --
 Traceback (most recent call last):
   File
 "/home/lucas/dev/test/django_lucas/tests/filtered_relation/tests.py", line
 108, in test_with_join_exclude
 [self.author1]
 AssertionError: Sequences differ: , ]> != []

 First sequence contains 1 additional elements.
 First extra element 1:
 

 - , ]>
 + []
 }}}

 The generated SQL is :

 {{{
 SELECT
   "filtered_relation_author"."id",
   "filtered_relation_author"."name",
   "filtered_relation_author"."content_type_id",
   "filtered_relation_author"."object_id"
 FROM
   "filtered_relation_author"
 WHERE
   NOT (
 "filtered_relation_author"."id" IN (
   SELECT
 U1."author_id"
   FROM
 "filtered_relation_book" U1
   WHERE
 U1."title" LIKE % Jane % ESCAPE '\'
 )
   )
 }}}

-- 
Ticket URL: 
Django 
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/072.dc83f7256138277fca1f9034bf4a0ffd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * stage:  Ready for checkin => Accepted


Comment:

 > So the result returned is not the result we are expecting, and the query
 should look like :

 Could you submit an additional test that demonstrate the wrong results are
 returned.

 The query you are suggesting seems problematic because the
 `book_alice."id" IS NULL` predicate contradicts the use of an `INNER JOIN`
 which should already filters out null results. In other words this query
 cannot match any row because the `INNER JOIN` will only return results in
 both sets and thus `book_alice."id" IS NOT NULL` will always be true.

 This kind of match can only be generated using a `LEFT OUTER JOIN` which
 some form of row grouping or a subquery pushdown which is what the ORM
 prefers to do.

 I think the appropriate query should be

 {{{#!sql
 SELECT
   "filtered_relation_author"."id",
   "filtered_relation_author"."name",
   "filtered_relation_author"."content_type_id",
   "filtered_relation_author"."object_id"
 FROM
   "filtered_relation_author"
 WHERE NOT (
 "filtered_relation_author"."id" IN (
 SELECT
   U1."author_id"
 FROM
   "filtered_relation_book"
 WHERE
   "filtered_relation_book"."title" LIKE 'poem by alice'
 )
 )
 }}}

 I guess `split_exclude` need to be adjusted to special case `filter_expr`
 that refer to a filtered expression instead of simply copy'ing
 `filtered_expressions` around.

-- 
Ticket URL: 
Django 
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/072.9f8ccc1c954e1d51f3428a9729cd3537%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Lucas Miller):

 Hi, i've tested the solution you're gicing in your PR but it doesn't seams
 to be ok, let me explain.

 Even if the error is not there anymore, the generated SQL doesn't use the
 FilteredRelation.

 With this code :

 {{{
 Author.objects.annotate(
 book_alice=FilteredRelation('book',
 condition=Q(book__title__iexact='poem by alice')),
 ).filter(book_alice__isnull=False)
 }}}

 the SQL is :

 {{{
 SELECT
   "filtered_relation_author"."id",
   "filtered_relation_author"."name",
   "filtered_relation_author"."content_type_id",
   "filtered_relation_author"."object_id"
 FROM
   "filtered_relation_author"
   INNER JOIN "filtered_relation_book" book_alice ON (
 "filtered_relation_author"."id" = book_alice."author_id"
 AND (
   book_alice."title" LIKE poem by alice ESCAPE '\'))

 WHERE book_alice."id" IS NOT NULL
 }}}

 And with the exclude code :

 {{{
 Author.objects.annotate(
 book_alice=FilteredRelation('book',
 condition=Q(book__title__iexact='poem by alice')),
 ).exclude(book_alice__isnull=False)
 }}}

 ths sql is missing the join clause on with the condition :

 {{{
 SELECT
   "filtered_relation_author"."id",
   "filtered_relation_author"."name",
   "filtered_relation_author"."content_type_id",
   "filtered_relation_author"."object_id"
 FROM
   "filtered_relation_author"
 WHERE
   NOT (
 NOT (
   "filtered_relation_author"."id" IN (
 SELECT
   U1."author_id"
 FROM
   "filtered_relation_book" U1
 WHERE
   U1."id" IS NOT NULL
   )
 )
   )
 }}}

 So the result returned is not the result we are expecting, and the query
 should look like :

 {{{
 SELECT
   "filtered_relation_author"."id",
   "filtered_relation_author"."name",
   "filtered_relation_author"."content_type_id",
   "filtered_relation_author"."object_id"
 FROM
   "filtered_relation_author"
   INNER JOIN "filtered_relation_book" book_alice ON (
 "filtered_relation_author"."id" = book_alice."author_id"
 AND (
   book_alice."title" LIKE poem by alice ESCAPE '\'))

 WHERE
 NOT(
 book_alice."id" IS NOT NULL
 )
 }}}

-- 
Ticket URL: 
Django 
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/072.91f11b48c85d474b92168b7c769fa68b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * version:  2.1 => master
 * stage:  Accepted => Ready for checkin


Comment:

 Patch looks good but it looks like doesn't qualify for a backport given
 `FilteredRelation` was introduced in Django 2.0 and isn't considered ''a
 new feature'' at this point.

-- 
Ticket URL: 
Django 
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/072.7b5c2c913dc032ef60c33966d41d79ea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by robinh00d):

 * has_patch:  0 => 1


Comment:

 I've submitted a PR:
 [https://github.com/django/django/pull/11265 PR]

-- 
Ticket URL: 
Django 
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/072.aabe49a6a744f477e2de80adbfca334c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  robinh00d
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by robinh00d):

 * owner:  nobody => robinh00d
 * status:  new => assigned


-- 
Ticket URL: 
Django 
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/072.2d07a6df58fb7a13e4bb1b8726497ce5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30349: Using exclude on annotated FilteredRelation doesn't work

[Django]

#30349: Using exclude on annotated FilteredRelation doesn't work
-+-
 Reporter:  Lucas Miller |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tobias Kunze):

 * component:  Uncategorized => Database layer (models, ORM)
 * stage:  Unreviewed => Accepted


-- 
Ticket URL: 
Django 
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/072.a7d8748f90c4a714695480f987340998%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.