Re: [Django] #31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong results.

[Django]

#31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong
results.
-+-
 Reporter:  Chris Bell   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, subquery, q,| Triage Stage:  Accepted
  order  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Jacob Walls):

 Dropping a failing test for tests/queries/tests.py:
 {{{
 def test_ticket_31922(self):
 from django.db.models import Subquery
 a1 = Order.objects.create(id=1, name='a1')
 a2 = Order.objects.create(id=2, name='a2')
 OrderItem.objects.create(order=a1, status=1)
 OrderItem.objects.create(order=a1, status=2)
 OrderItem.objects.create(order=a2, status=1)

 q1 =
 Q(items__id__in=Subquery(OrderItem.objects.only('id').filter(status=1)))
 q2 =
 ~Q(items__id__in=Subquery(OrderItem.objects.only('id').filter(status=2)))

 qs = Order.objects.filter(q1 & q2)
 self.assertQuerysetEqual(qs, [repr(a2)])
 }}}

 This patch skips putting the join on the subquery, but it breaks the test
 for #14511, where the join is desired on `exclude()`, so although it
 doesn't seem like the right patch it at least shows Chris where the
 changes are happening. If we need this join for `exclude()` but not for
 `filter()` I'm all ears for how we're going to tell that apart upstream.

 {{{
 diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
 index 4648daf395..fa707d6b04 100644
 --- a/django/db/models/sql/query.py
 +++ b/django/db/models/sql/query.py
 @@ -1792,10 +1792,12 @@ class Query(BaseExpression):
  lookup_class = select_field.get_lookup('exact')
  # Note that the query.select[0].alias is different from alias
  # due to bump_prefix above.
 -lookup = lookup_class(pk.get_col(query.select[0].alias),
 -  pk.get_col(alias))
 -query.where.add(lookup, AND)
 -query.external_aliases[alias] = True
 +col1 = pk.get_col(query.select[0].alias)
 +col2 = pk.get_col(alias)
 +if col1.target != col2.target:
 +lookup = lookup_class(col1, col2)
 +query.where.add(lookup, AND)
 +query.external_aliases[alias] = True

  condition, needed_inner = self.build_filter(
  ('%s__in' % trimmed_prefix, query),
 }}}

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.a41b09dd6922916a5835d99137a007fe%40djangoproject.com.

Re: [Django] #31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong results.

[Django]

#31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong
results.
-+-
 Reporter:  Chris Bell   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, subquery, q,| Triage Stage:  Accepted
  order  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by felixxm):

 Replying to [comment:4 Christian Klus]:
 > I'm experiencing this on a few very complicated querysets - reverting
 back to 3.0.6 fixes it, I'm thinking some of the queryset changes
 introduced in 3.0.7 are the cause of this.

 I'm able to reproduce the issue in Django < 3.0.6, so it seems that you've
 encountered a different problem.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.de605b2ca6dd06eae315615dd14386d1%40djangoproject.com.

Re: [Django] #31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong results.

[Django]

#31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong
results.
-+-
 Reporter:  Chris Bell   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, subquery, q,| Triage Stage:  Accepted
  order  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Christian Klus):

 I'm experiencing this on a few very complicated querysets - reverting back
 to 3.0.6 fixes it, I'm thinking some of the queryset changes introduced in
 3.0.7 are the cause of this.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.cb8803608626870e6ea727df914d13aa%40djangoproject.com.

Re: [Django] #31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong results.

[Django]

#31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong
results.
-+-
 Reporter:  Chris Bell   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, subquery, q,| Triage Stage:  Accepted
  order  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Chris Bell):

 Hi @felixmm et al.

 Thanks for confirming the issue and suggestion for using Exists.

 The concern for us is how wide reaching this problem is. Ideally our tests
 would catch erroneous SQL but that is not guaranteed and we are concerned
 as to the possibility of security related issues arising from a similar
 scenario.

 We are looking into ways of trying to identify examples of our Q usages
 that may be susceptible to this problem. In the meantime, if anyone in the
 community has any tips/thoughts on where this issue may originate, we
 would love to hear from you. We may be able to help investigate it with a
 bit of assistance, specifically from those with experience of the inner
 workings of the ORM.

 Many thanks in advance

 Chris

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.57c7938be0aa410de50a4e77b52ac8f9%40djangoproject.com.

Re: [Django] #31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong results. (was: Changing ordering of Q statements in filter produces different SQL and results.)

[Django]

#31922: QuerySet.filter() against Q() with Subquery() and __in produces wrong
results.
-+-
 Reporter:  Chris Bell   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, subquery, q,| Triage Stage:  Accepted
  order  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * version:  2.2 => master
 * stage:  Unreviewed => Accepted


Comment:

 Thanks for the detailed report, I was able to reproduce this issue.

 Everything works properly with `Exists()`:
 {{{
 >>> q1_exists = Exists(BookStatus.objects.filter(status='DRAFT',
 book__id=OuterRef('pk')))
 >>> q2_exists = ~Exists(BookStatus.objects.filter(status='PUBLISHED',
 book__id=OuterRef('pk')))
 >>> Book.objects.filter(q2_exists & q1_exists)
 ]>
 >>> Book.objects.filter(q1_exists & q2_exists)
 ]>
 }}}

 Reproduced at 41725602afebe2ddb018b99afe134384cc3bf69e.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.76686aa577fb0af308a3d300e0448c04%40djangoproject.com.