Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  fixed
 Keywords:  Annotation, Count,   | Triage Stage:  Ready for
  Filter |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham ):

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


Comment:

 In [changeset:"53269bcaaf1fc2ce4db797d7d8935d98a53494df" 53269bca]:
 {{{
 #!CommitTicketReference repository=""
 revision="53269bcaaf1fc2ce4db797d7d8935d98a53494df"
 Fixed #30011 -- Fixed queries that reuse filtered aggregates.

 Thanks Taqi Abbas and Raphael Kimmig for the report.
 }}}

-- 
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/065.34fc5c546ae6696f3b2e044749439c0b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:  Ready for
  Filter |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * stage:  Accepted => Ready for checkin


-- 
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/065.f40d4a095f299e1d9f0a6d05d745af25%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:  Accepted
  Filter |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * stage:  Ready for checkin => 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/065.544d57e4e73eac9a1719221b60a8bc3d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:  Ready for
  Filter |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * stage:  Accepted => Ready for checkin


-- 
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/065.3e3165fe6cec9474986ee82d4f85a9bd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:  Accepted
  Filter |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * has_patch:  0 => 1
 * type:  Uncategorized => Bug
 * stage:  Unreviewed => Accepted


Comment:

 Thanks for confirming as well Raphael!

 With both your reports I managed to come up with a patch. We happened to
 alter `Aggregate.source_expressions` on `get_expressions` when a `filter`
 was specified.

 https://github.com/django/django/pull/10731

-- 
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/065.9565f8b58d57a38ec6ce08e686ce648b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-06 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Uncategorized|   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:
  Filter |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Raphael Kimmig):

 I've got a somewhat smaller repro here, the bug seems to require both a
 filtered annotation and a nested filter.

 {{{
 from django.db.models import Count, Q
 from django.test import TestCase
 from django.db import models, OperationalError


 class Thing(models.Model):
 pass


 class RelatedThing(models.Model):
 thing = models.ForeignKey(
 Thing, on_delete=models.CASCADE,
 )


 class TestCountExtraArg(TestCase):
 def setUp(self):
 t = Thing.objects.create()
 t.relatedthing_set.create()

 def test_issue_not_triggered_without_filter_in_(self):
 queryset = Thing.objects.annotate(
 related_count=Count("relatedthing")
 )
 result = queryset.filter(id__in=queryset.values("id"))

 self.assertEqual(result[0].related_count, 1, result)

 def test_issue_not_triggered_without_nested_query(self):
 queryset = Thing.objects.annotate(
 related_count=Count("relatedthing", filter=Q(id__gt=0))
 )
 result = queryset.filter(id__in=list(queryset.values_list("id",
 flat=True)))
 self.assertEqual(result[0].related_count, 1, result)

 def test_triggering_issue(self):
 queryset = Thing.objects.annotate(
 related_count=Count("relatedthing", filter=Q(id__gt=0))
 )
 result = queryset.filter(id__in=queryset.values("id"))

 with self.assertRaises(OperationalError):
 self.assertEqual(result[0].related_count, 1, result)
 }}}

-- 
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/065.e5d1f7c0f5d663b22a27e88602a4c27b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-05 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Uncategorized|   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:
  Filter |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Taqi Abbas):

 Hey Simon,

 Your suggested code also breaks. This is the line that invalidates the
 sql.
 {{{
 queryset.filter(id__in=filtered_queryset.values('id'))
 }}}

 As for the cross joins issue, this is the code that I basically use to
 remove duplicates.
 {{{
 if hasattr(queryset.model, 'id'):
return queryset.filter(id__in=filtered_queryset.values('id'))
 else:
return filtered_queryset.distinct(*view.get_ordering(request))
 }}}
 its done like this because distinct fails for a model that has a jsonb
 field. So I just do this.

 I have made a github repo with a testcase to reproduce it. Heres the
 link[https://github.com/taqi457/Django-Count-TestCase]:

 This a simpler query reproduced from the repo above, hope this helps.

 {{{
 queryset = Ticket.objects.filter(is_deleted=False)\
.annotate(commit_count=Count('commit_set', filter=Q(is_deleted=False)))
 filtered_queryset = queryset.filter(heading__icontains='')
 result = queryset.filter(id__in=filtered_queryset.values('id'))
 print(result.query)]
 SELECT
 "test_count_ticket"."id",
 "test_count_ticket"."uuid",
 "test_count_ticket"."created_on",
 "test_count_ticket"."updated_on",
 "test_count_ticket"."server_created_on",
 "test_count_ticket"."server_updated_on",
 "test_count_ticket"."is_deleted",
 "test_count_ticket"."heading",
 Count("test_count_commit"."id",
 "test_count_ticket"."is_deleted" = false, "test_count_ticket"."is_deleted"
 = false) filter (WHERE "test_count_ticket"."is_deleted" = false) AS
 "commit_count"
 FROM"test_count_ticket"
 LEFT OUTER JOIN "test_count_commit"
 ON  (
 "test_count_ticket"."id" =
 "test_count_commit"."ticket_id")
 WHERE   (
 "test_count_ticket"."is_deleted" = false
 AND "test_count_ticket"."id" IN
 (
 SELECT  u0."id"
 FROM
 "test_count_ticket" u0
 LEFT OUTER JOIN
 "test_count_commit" u1
 ON  (
 u0."id" = u1."ticket_id")
 WHERE   (
 u0."is_deleted" = false
 AND
 upper(u0."heading"::text) LIKE upper(%%))
 GROUP BYu0."id"))
 GROUP BY"test_count_ticket"."id"
 }}}

-- 
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/065.29ce89d04d6d653995b3340ea6f42fbb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.


Re: [Django] #30011: Count with filter annotation bug on filter

2018-12-05 Thread Django
#30011: Count with filter annotation bug on filter
-+-
 Reporter:  Taqi Abbas   |Owner:  nobody
 Type:  Uncategorized|   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Annotation, Count,   | Triage Stage:
  Filter |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Hello Taqi,

 I see that somehow your `COUNT` annotations get their expressions
 duplicated when a subquery is added. This is probably an issue with
 expression cloning when you create `filtered_queryset` from `queryset`.

 One thing that would tremendously help debugging this further is if you
 could reduce your reproduction case to a minimal set of conditions by
 progressively removing annotations and simplifying your querysets. Right
 now there's a lot of things involved that makes your query massive and
 hard to pinpoint the exact source of the failure.

 To get you started, does the following crash as well?

 {{{#!python
 queryset = Ticket.objects.annotate(
 commit_count=Count('commit',
 filter=models.Q(commit__is_deleted=False))
 )
 filtered_queryset = queryset.filter(Q(heading__icontains=''))
 print(queryset.filter(id__in=filtered_queryset.values('id')).query)
 }}}

 By the way, you probably haven't noticed it yet but your queryset is
 probably not returning the right results for these annotations because
 multiple tables are the cross joined. This is explained in the
 [https://docs.djangoproject.com/en/2.1/topics/db/aggregation/#combining-
 multiple-aggregations documentation] and tracked in #10060. This is a
 limitation that #28296 ought to fix by eventually providing a `subquery`
 argument to `Count`.

-- 
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/065.f5770cf28eedd097072140cc51c68282%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.