Opened 6 years ago

Closed 6 years ago

Last modified 2 years ago

#11945 closed (duplicate)

Aggregation on filter got the wrong result

Reported by: jay Owned by:
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Aggregation on complex filter logic will lead to faulty. I prepared a simple project to reproduct this fault. In this project, some of the following queries will get the wrong results:

> Tag.objects.distinct().filter(entries__when__year=2009).annotate(aggr = Count('entries'))
[<Tag: 1 aggr: 10>, <Tag: 2 aggr: 10>, <Tag: 3 aggr: 1>] # It's right
> Tag.objects.distinct().filter(entries__when__year=2009).annotate(aggr = Count('entries')).filter(entries__tags__token = '1').exclude(token='1')
[<Tag: 2 aggr: 100>, <Tag: 3 aggr: 1>] # Ooops, it's wrong.
> Tag.objects.distinct().filter(entries__when__year=2009).filter(entries__tags__token = '1').exclude(token='1').annotate(aggr = Count('entries'))
[<Tag: 2 aggr: 100>, <Tag: 3 aggr: 1>] # the same wrong result
# let's try Sum
> Tag.objects.distinct().filter(entries__when__year=2009).annotate(aggr = Sum('entries__visits'))
[<Tag: 1 aggr: 55>, <Tag: 2 aggr: 55>, <Tag: 3 aggr: 1>] # It's OK
> Tag.objects.distinct().filter(entries__when__year=2009).filter(entries__tags__token = '1').exclude(token='1').annotate(aggr = Sum('entries__visits'))
[<Tag: 2 aggr: 550>, <Tag: 3 aggr: 1>] # wrong

Attachments (1)

ft_aggregation.tbz2 (4.4 KB) - added by jay 6 years ago.
a simple project to reproduce the fault

Download all attachments as: .zip

Change History (3)

Changed 6 years ago by jay

a simple project to reproduce the fault

comment:1 Changed 6 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #10060

comment:2 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top