← Back to branch summary

~ubuntu-branches/ubuntu/quantal/python-django/quantal-security

« back to all changes in this revision

Viewing changes to tests/regressiontests/aggregation_regress/tests.py

  • Committer: Bazaar Package Importer
  • Author(s): Jamie Strandboge
  • Date: 2010-10-12 11:34:35 UTC
  • mfrom: (1.2.7 upstream)
  • mto: This revision was merged to the branch mainline in revision 30.
  • Revision ID: james.westby@ubuntu.com-20101012113435-9lnsrh0i3mxozbt0
Tags: upstream-1.2.3
Import upstream version 1.2.3

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/regressiontests/aggregation_regress/tests.py
 
1
import datetime
 
2
from decimal import Decimal
 
3
 
 
4
from django.core.exceptions import FieldError
1
5
from django.conf import settings
2
 
from django.test import TestCase
 
6
from django.test import TestCase, Approximate
3
7
from django.db import DEFAULT_DB_ALIAS
4
 
from django.db.models import Count, Max
 
8
from django.db.models import Count, Max, Avg, Sum, StdDev, Variance, F
5
9
 
6
10
from regressiontests.aggregation_regress.models import *
7
11
 
8
12
 
 
13
def run_stddev_tests():
 
14
    """Check to see if StdDev/Variance tests should be run.
 
15
 
 
16
    Stddev and Variance are not guaranteed to be available for SQLite, and
 
17
    are not available for PostgreSQL before 8.2.
 
18
    """
 
19
    if settings.DATABASES[DEFAULT_DB_ALIAS]['ENGINE'] == 'django.db.backends.sqlite3':
 
20
        return False
 
21
 
 
22
    class StdDevPop(object):
 
23
        sql_function = 'STDDEV_POP'
 
24
 
 
25
    try:
 
26
        connection.ops.check_aggregate_support(StdDevPop())
 
27
    except:
 
28
        return False
 
29
    return True
 
30
 
 
31
 
9
32
class AggregationTests(TestCase):
 
33
    def assertObjectAttrs(self, obj, **kwargs):
 
34
        for attr, value in kwargs.iteritems():
 
35
            self.assertEqual(getattr(obj, attr), value)
10
36
 
11
37
    def test_aggregates_in_where_clause(self):
12
38
        """
70
96
            }).annotate(total_books=Count('book'))
71
97
            # force execution of the query
72
98
            list(qs)
 
99
 
 
100
    def test_aggregate(self):
 
101
        # Ordering requests are ignored
 
102
        self.assertEqual(
 
103
            Author.objects.order_by("name").aggregate(Avg("age")),
 
104
            {"age__avg": Approximate(37.444, places=1)}
 
105
        )
 
106
 
 
107
        # Implicit ordering is also ignored
 
108
        self.assertEqual(
 
109
            Book.objects.aggregate(Sum("pages")),
 
110
            {"pages__sum": 3703},
 
111
        )
 
112
 
 
113
        # Baseline results
 
114
        self.assertEqual(
 
115
            Book.objects.aggregate(Sum('pages'), Avg('pages')),
 
116
            {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}
 
117
        )
 
118
 
 
119
        # Empty values query doesn't affect grouping or results
 
120
        self.assertEqual(
 
121
            Book.objects.values().aggregate(Sum('pages'), Avg('pages')),
 
122
            {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}
 
123
        )
 
124
 
 
125
        # Aggregate overrides extra selected column
 
126
        self.assertEqual(
 
127
            Book.objects.extra(select={'price_per_page' : 'price / pages'}).aggregate(Sum('pages')),
 
128
            {'pages__sum': 3703}
 
129
        )
 
130
 
 
131
    def test_annotation(self):
 
132
        # Annotations get combined with extra select clauses
 
133
        obj = Book.objects.annotate(mean_auth_age=Avg("authors__age")).extra(select={"manufacture_cost": "price * .5"}).get(pk=2)
 
134
        self.assertObjectAttrs(obj,
 
135
            contact_id=3,
 
136
            id=2,
 
137
            isbn=u'067232959',
 
138
            mean_auth_age=45.0,
 
139
            name='Sams Teach Yourself Django in 24 Hours',
 
140
            pages=528,
 
141
            price=Decimal("23.09"),
 
142
            pubdate=datetime.date(2008, 3, 3),
 
143
            publisher_id=2,
 
144
            rating=3.0
 
145
        )
 
146
        # Different DB backends return different types for the extra select computation
 
147
        self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))
 
148
 
 
149
        # Order of the annotate/extra in the query doesn't matter
 
150
        obj = Book.objects.extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2)
 
151
        self.assertObjectAttrs(obj,
 
152
            contact_id=3,
 
153
            id=2,
 
154
            isbn=u'067232959',
 
155
            mean_auth_age=45.0,
 
156
            name=u'Sams Teach Yourself Django in 24 Hours',
 
157
            pages=528,
 
158
            price=Decimal("23.09"),
 
159
            pubdate=datetime.date(2008, 3, 3),
 
160
            publisher_id=2,
 
161
            rating=3.0
 
162
        )
 
163
        # Different DB backends return different types for the extra select computation
 
164
        self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))
 
165
 
 
166
        # Values queries can be combined with annotate and extra
 
167
        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2)
 
168
        manufacture_cost = obj['manufacture_cost']
 
169
        self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))
 
170
        del obj['manufacture_cost']
 
171
        self.assertEqual(obj, {
 
172
            "contact_id": 3,
 
173
            "id": 2,
 
174
            "isbn": u"067232959",
 
175
            "mean_auth_age": 45.0,
 
176
            "name": u"Sams Teach Yourself Django in 24 Hours",
 
177
            "pages": 528,
 
178
            "price": Decimal("23.09"),
 
179
            "pubdate": datetime.date(2008, 3, 3),
 
180
            "publisher_id": 2,
 
181
            "rating": 3.0,
 
182
        })
 
183
 
 
184
        # The order of the (empty) values, annotate and extra clauses doesn't
 
185
        # matter
 
186
        obj = Book.objects.values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2)
 
187
        manufacture_cost = obj['manufacture_cost']
 
188
        self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))
 
189
        del obj['manufacture_cost']
 
190
        self.assertEqual(obj, {
 
191
            'contact_id': 3,
 
192
            'id': 2,
 
193
            'isbn': u'067232959',
 
194
            'mean_auth_age': 45.0,
 
195
            'name': u'Sams Teach Yourself Django in 24 Hours',
 
196
            'pages': 528,
 
197
            'price': Decimal("23.09"),
 
198
            'pubdate': datetime.date(2008, 3, 3),
 
199
            'publisher_id': 2,
 
200
            'rating': 3.0
 
201
        })
 
202
 
 
203
        # If the annotation precedes the values clause, it won't be included
 
204
        # unless it is explicitly named
 
205
        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name').get(pk=1)
 
206
        self.assertEqual(obj, {
 
207
            "name": u'The Definitive Guide to Django: Web Development Done Right',
 
208
        })
 
209
 
 
210
        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name','mean_auth_age').get(pk=1)
 
211
        self.assertEqual(obj, {
 
212
            'mean_auth_age': 34.5,
 
213
            'name': u'The Definitive Guide to Django: Web Development Done Right',
 
214
        })
 
215
 
 
216
        # If an annotation isn't included in the values, it can still be used
 
217
        # in a filter
 
218
        qs = Book.objects.annotate(n_authors=Count('authors')).values('name').filter(n_authors__gt=2)
 
219
        self.assertQuerysetEqual(
 
220
            qs, [
 
221
                {"name": u'Python Web Development with Django'}
 
222
            ],
 
223
            lambda b: b,
 
224
        )
 
225
 
 
226
        # The annotations are added to values output if values() precedes
 
227
        # annotate()
 
228
        obj = Book.objects.values('name').annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).get(pk=1)
 
229
        self.assertEqual(obj, {
 
230
            'mean_auth_age': 34.5,
 
231
            'name': u'The Definitive Guide to Django: Web Development Done Right',
 
232
        })
 
233
 
 
234
        # Check that all of the objects are getting counted (allow_nulls) and
 
235
        # that values respects the amount of objects
 
236
        self.assertEqual(
 
237
            len(Author.objects.annotate(Avg('friends__age')).values()),
 
238
            9
 
239
        )
 
240
 
 
241
        # Check that consecutive calls to annotate accumulate in the query
 
242
        qs = Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards'))
 
243
        self.assertQuerysetEqual(
 
244
            qs, [
 
245
                {'price': Decimal("30"), 'oldest': 35, 'publisher__num_awards__max': 3},
 
246
                {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7},
 
247
                {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1},
 
248
                {'price': Decimal("75"), 'oldest': 57, 'publisher__num_awards__max': 9},
 
249
                {'price': Decimal("82.8"), 'oldest': 57, 'publisher__num_awards__max': 7}
 
250
            ],
 
251
            lambda b: b,
 
252
        )
 
253
 
 
254
    def test_aggrate_annotation(self):
 
255
        # Aggregates can be composed over annotations.
 
256
        # The return type is derived from the composed aggregate
 
257
        vals = Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('pages'), Max('price'), Sum('num_authors'), Avg('num_authors'))
 
258
        self.assertEqual(vals, {
 
259
            'num_authors__sum': 10,
 
260
            'num_authors__avg': Approximate(1.666, places=2),
 
261
            'pages__max': 1132,
 
262
            'price__max': Decimal("82.80")
 
263
        })
 
264
 
 
265
    def test_field_error(self):
 
266
        # Bad field requests in aggregates are caught and reported
 
267
        self.assertRaises(
 
268
            FieldError,
 
269
            lambda: Book.objects.all().aggregate(num_authors=Count('foo'))
 
270
        )
 
271
 
 
272
        self.assertRaises(
 
273
            FieldError,
 
274
            lambda: Book.objects.all().annotate(num_authors=Count('foo'))
 
275
        )
 
276
 
 
277
        self.assertRaises(
 
278
            FieldError,
 
279
            lambda: Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('foo'))
 
280
        )
 
281
 
 
282
    def test_more(self):
 
283
        # Old-style count aggregations can be mixed with new-style
 
284
        self.assertEqual(
 
285
            Book.objects.annotate(num_authors=Count('authors')).count(),
 
286
            6
 
287
        )
 
288
 
 
289
        # Non-ordinal, non-computed Aggregates over annotations correctly
 
290
        # inherit the annotation's internal type if the annotation is ordinal
 
291
        # or computed
 
292
        vals = Book.objects.annotate(num_authors=Count('authors')).aggregate(Max('num_authors'))
 
293
        self.assertEqual(
 
294
            vals,
 
295
            {'num_authors__max': 3}
 
296
        )
 
297
 
 
298
        vals = Publisher.objects.annotate(avg_price=Avg('book__price')).aggregate(Max('avg_price'))
 
299
        self.assertEqual(
 
300
            vals,
 
301
            {'avg_price__max': 75.0}
 
302
        )
 
303
 
 
304
        # Aliases are quoted to protected aliases that might be reserved names
 
305
        vals = Book.objects.aggregate(number=Max('pages'), select=Max('pages'))
 
306
        self.assertEqual(
 
307
            vals,
 
308
            {'number': 1132, 'select': 1132}
 
309
        )
 
310
 
 
311
        # Regression for #10064: select_related() plays nice with aggregates
 
312
        obj = Book.objects.select_related('publisher').annotate(num_authors=Count('authors')).values()[0]
 
313
        self.assertEqual(obj, {
 
314
            'contact_id': 8,
 
315
            'id': 5,
 
316
            'isbn': u'013790395',
 
317
            'name': u'Artificial Intelligence: A Modern Approach',
 
318
            'num_authors': 2,
 
319
            'pages': 1132,
 
320
            'price': Decimal("82.8"),
 
321
            'pubdate': datetime.date(1995, 1, 15),
 
322
            'publisher_id': 3,
 
323
            'rating': 4.0,
 
324
        })
 
325
 
 
326
        # Regression for #10010: exclude on an aggregate field is correctly
 
327
        # negated
 
328
        self.assertEqual(
 
329
            len(Book.objects.annotate(num_authors=Count('authors'))),
 
330
            6
 
331
        )
 
332
        self.assertEqual(
 
333
            len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=2)),
 
334
            1
 
335
        )
 
336
        self.assertEqual(
 
337
            len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__gt=2)),
 
338
            5
 
339
        )
 
340
 
 
341
        self.assertEqual(
 
342
            len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__lt=3).exclude(num_authors__lt=2)),
 
343
            2
 
344
        )
 
345
        self.assertEqual(
 
346
            len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__lt=2).filter(num_authors__lt=3)),
 
347
            2
 
348
        )
 
349
 
 
350
    def test_aggregate_fexpr(self):
 
351
        # Aggregates can be used with F() expressions
 
352
        # ... where the F() is pushed into the HAVING clause
 
353
        qs = Publisher.objects.annotate(num_books=Count('book')).filter(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
 
354
        self.assertQuerysetEqual(
 
355
            qs, [
 
356
                {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},
 
357
                {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}
 
358
            ],
 
359
            lambda p: p,
 
360
        )
 
361
 
 
362
        qs = Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
 
363
        self.assertQuerysetEqual(
 
364
            qs, [
 
365
                {'num_books': 2, 'name': u'Apress', 'num_awards': 3},
 
366
                {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},
 
367
                {'num_books': 1, 'name': u'Sams', 'num_awards': 1}
 
368
            ],
 
369
            lambda p: p,
 
370
        )
 
371
 
 
372
        # ... and where the F() references an aggregate
 
373
        qs = Publisher.objects.annotate(num_books=Count('book')).filter(num_awards__gt=2*F('num_books')).order_by('name').values('name','num_books','num_awards')
 
374
        self.assertQuerysetEqual(
 
375
            qs, [
 
376
                {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},
 
377
                {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}
 
378
            ],
 
379
            lambda p: p,
 
380
        )
 
381
 
 
382
        qs = Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
 
383
        self.assertQuerysetEqual(
 
384
            qs, [
 
385
                {'num_books': 2, 'name': u'Apress', 'num_awards': 3},
 
386
                {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},
 
387
                {'num_books': 1, 'name': u'Sams', 'num_awards': 1}
 
388
            ],
 
389
            lambda p: p,
 
390
        )
 
391
 
 
392
    def test_db_col_table(self):
 
393
        # Tests on fields with non-default table and column names.
 
394
        qs = Clues.objects.values('EntryID__Entry').annotate(Appearances=Count('EntryID'), Distinct_Clues=Count('Clue', distinct=True))
 
395
        self.assertQuerysetEqual(qs, [])
 
396
 
 
397
        qs = Entries.objects.annotate(clue_count=Count('clues__ID'))
 
398
        self.assertQuerysetEqual(qs, [])
 
399
 
 
400
    def test_empty(self):
 
401
        # Regression for #10089: Check handling of empty result sets with
 
402
        # aggregates
 
403
        self.assertEqual(
 
404
            Book.objects.filter(id__in=[]).count(),
 
405
            0
 
406
        )
 
407
 
 
408
        vals = Book.objects.filter(id__in=[]).aggregate(num_authors=Count('authors'), avg_authors=Avg('authors'), max_authors=Max('authors'), max_price=Max('price'), max_rating=Max('rating'))
 
409
        self.assertEqual(
 
410
            vals,
 
411
            {'max_authors': None, 'max_rating': None, 'num_authors': 0, 'avg_authors': None, 'max_price': None}
 
412
        )
 
413
 
 
414
        qs = Publisher.objects.filter(pk=5).annotate(num_authors=Count('book__authors'), avg_authors=Avg('book__authors'), max_authors=Max('book__authors'), max_price=Max('book__price'), max_rating=Max('book__rating')).values()
 
415
        self.assertQuerysetEqual(
 
416
            qs, [
 
417
                {'max_authors': None, 'name': u"Jonno's House of Books", 'num_awards': 0, 'max_price': None, 'num_authors': 0, 'max_rating': None, 'id': 5, 'avg_authors': None}
 
418
            ],
 
419
            lambda p: p
 
420
        )
 
421
 
 
422
    def test_more_more(self):
 
423
        # Regression for #10113 - Fields mentioned in order_by() must be
 
424
        # included in the GROUP BY. This only becomes a problem when the
 
425
        # order_by introduces a new join.
 
426
        self.assertQuerysetEqual(
 
427
            Book.objects.annotate(num_authors=Count('authors')).order_by('publisher__name', 'name'), [
 
428
                "Practical Django Projects",
 
429
                "The Definitive Guide to Django: Web Development Done Right",
 
430
                "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp",
 
431
                "Artificial Intelligence: A Modern Approach",
 
432
                "Python Web Development with Django",
 
433
                "Sams Teach Yourself Django in 24 Hours",
 
434
            ],
 
435
            lambda b: b.name
 
436
        )
 
437
 
 
438
        # Regression for #10127 - Empty select_related() works with annotate
 
439
        qs = Book.objects.filter(rating__lt=4.5).select_related().annotate(Avg('authors__age'))
 
440
        self.assertQuerysetEqual(
 
441
            qs, [
 
442
                (u'Artificial Intelligence: A Modern Approach', 51.5, u'Prentice Hall', u'Peter Norvig'),
 
443
                (u'Practical Django Projects', 29.0, u'Apress', u'James Bennett'),
 
444
                (u'Python Web Development with Django', Approximate(30.333, places=2), u'Prentice Hall', u'Jeffrey Forcier'),
 
445
                (u'Sams Teach Yourself Django in 24 Hours', 45.0, u'Sams', u'Brad Dayley')
 
446
            ],
 
447
            lambda b: (b.name, b.authors__age__avg, b.publisher.name, b.contact.name)
 
448
        )
 
449
 
 
450
        # Regression for #10132 - If the values() clause only mentioned extra
 
451
        # (select=) columns, those columns are used for grouping
 
452
        qs = Book.objects.extra(select={'pub':'publisher_id'}).values('pub').annotate(Count('id')).order_by('pub')
 
453
        self.assertQuerysetEqual(
 
454
            qs, [
 
455
                {'pub': 1, 'id__count': 2},
 
456
                {'pub': 2, 'id__count': 1},
 
457
                {'pub': 3, 'id__count': 2},
 
458
                {'pub': 4, 'id__count': 1}
 
459
            ],
 
460
            lambda b: b
 
461
        )
 
462
 
 
463
        qs = Book.objects.extra(select={'pub':'publisher_id', 'foo':'pages'}).values('pub').annotate(Count('id')).order_by('pub')
 
464
        self.assertQuerysetEqual(
 
465
            qs, [
 
466
                {'pub': 1, 'id__count': 2},
 
467
                {'pub': 2, 'id__count': 1},
 
468
                {'pub': 3, 'id__count': 2},
 
469
                {'pub': 4, 'id__count': 1}
 
470
            ],
 
471
            lambda b: b
 
472
        )
 
473
 
 
474
        # Regression for #10182 - Queries with aggregate calls are correctly
 
475
        # realiased when used in a subquery
 
476
        ids = Book.objects.filter(pages__gt=100).annotate(n_authors=Count('authors')).filter(n_authors__gt=2).order_by('n_authors')
 
477
        self.assertQuerysetEqual(
 
478
            Book.objects.filter(id__in=ids), [
 
479
                "Python Web Development with Django",
 
480
            ],
 
481
            lambda b: b.name
 
482
        )
 
483
 
 
484
    def test_pickle(self):
 
485
        # Regression for #10197 -- Queries with aggregates can be pickled.
 
486
        # First check that pickling is possible at all. No crash = success
 
487
        qs = Book.objects.annotate(num_authors=Count('authors'))
 
488
        out = pickle.dumps(qs)
 
489
 
 
490
        # Then check that the round trip works.
 
491
        query = qs.query.get_compiler(qs.db).as_sql()[0]
 
492
        qs2 = pickle.loads(pickle.dumps(qs))
 
493
        self.assertEqual(
 
494
            qs2.query.get_compiler(qs2.db).as_sql()[0],
 
495
            query,
 
496
        )
 
497
 
 
498
    def test_more_more_more(self):
 
499
        # Regression for #10199 - Aggregate calls clone the original query so
 
500
        # the original query can still be used
 
501
        books = Book.objects.all()
 
502
        books.aggregate(Avg("authors__age"))
 
503
        self.assertQuerysetEqual(
 
504
            books.all(), [
 
505
                u'Artificial Intelligence: A Modern Approach',
 
506
                u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
 
507
                u'Practical Django Projects',
 
508
                u'Python Web Development with Django',
 
509
                u'Sams Teach Yourself Django in 24 Hours',
 
510
                u'The Definitive Guide to Django: Web Development Done Right'
 
511
            ],
 
512
            lambda b: b.name
 
513
        )
 
514
 
 
515
        # Regression for #10248 - Annotations work with DateQuerySets
 
516
        qs = Book.objects.annotate(num_authors=Count('authors')).filter(num_authors=2).dates('pubdate', 'day')
 
517
        self.assertQuerysetEqual(
 
518
            qs, [
 
519
                datetime.datetime(1995, 1, 15, 0, 0),
 
520
                datetime.datetime(2007, 12, 6, 0, 0)
 
521
            ],
 
522
            lambda b: b
 
523
        )
 
524
 
 
525
        # Regression for #10290 - extra selects with parameters can be used for
 
526
        # grouping.
 
527
        qs = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'sheets' : '(pages + %s) / %s'}, select_params=[1, 2]).order_by('sheets').values('sheets')
 
528
        self.assertQuerysetEqual(
 
529
            qs, [
 
530
                150,
 
531
                175,
 
532
                224,
 
533
                264,
 
534
                473,
 
535
                566
 
536
            ],
 
537
            lambda b: int(b["sheets"])
 
538
        )
 
539
 
 
540
        # Regression for 10425 - annotations don't get in the way of a count()
 
541
        # clause
 
542
        self.assertEqual(
 
543
            Book.objects.values('publisher').annotate(Count('publisher')).count(),
 
544
            4
 
545
        )
 
546
        self.assertEqual(
 
547
            Book.objects.annotate(Count('publisher')).values('publisher').count(),
 
548
            6
 
549
        )
 
550
 
 
551
        publishers = Publisher.objects.filter(id__in=[1, 2])
 
552
        self.assertQuerysetEqual(
 
553
            publishers, [
 
554
                "Apress",
 
555
                "Sams"
 
556
            ],
 
557
            lambda p: p.name
 
558
        )
 
559
 
 
560
        publishers = publishers.annotate(n_books=Count("book"))
 
561
        self.assertEqual(
 
562
            publishers[0].n_books,
 
563
            2
 
564
        )
 
565
 
 
566
        self.assertQuerysetEqual(
 
567
            publishers, [
 
568
                "Apress",
 
569
                "Sams",
 
570
            ],
 
571
            lambda p: p.name
 
572
        )
 
573
 
 
574
        books = Book.objects.filter(publisher__in=publishers)
 
575
        self.assertQuerysetEqual(
 
576
            books, [
 
577
                "Practical Django Projects",
 
578
                "Sams Teach Yourself Django in 24 Hours",
 
579
                "The Definitive Guide to Django: Web Development Done Right",
 
580
            ],
 
581
            lambda b: b.name
 
582
        )
 
583
        self.assertQuerysetEqual(
 
584
            publishers, [
 
585
                "Apress",
 
586
                "Sams",
 
587
            ],
 
588
            lambda p: p.name
 
589
        )
 
590
 
 
591
        # Regression for 10666 - inherited fields work with annotations and
 
592
        # aggregations
 
593
        self.assertEqual(
 
594
            HardbackBook.objects.aggregate(n_pages=Sum('book_ptr__pages')),
 
595
            {'n_pages': 2078}
 
596
        )
 
597
 
 
598
        self.assertEqual(
 
599
            HardbackBook.objects.aggregate(n_pages=Sum('pages')),
 
600
            {'n_pages': 2078},
 
601
        )
 
602
 
 
603
        qs = HardbackBook.objects.annotate(n_authors=Count('book_ptr__authors')).values('name', 'n_authors')
 
604
        self.assertQuerysetEqual(
 
605
            qs, [
 
606
                {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},
 
607
                {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}
 
608
            ],
 
609
            lambda h: h
 
610
        )
 
611
 
 
612
        qs = HardbackBook.objects.annotate(n_authors=Count('authors')).values('name', 'n_authors')
 
613
        self.assertQuerysetEqual(
 
614
            qs, [
 
615
                {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},
 
616
                {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}
 
617
            ],
 
618
            lambda h: h,
 
619
        )
 
620
 
 
621
        # Regression for #10766 - Shouldn't be able to reference an aggregate
 
622
        # fields in an an aggregate() call.
 
623
        self.assertRaises(
 
624
            FieldError,
 
625
            lambda: Book.objects.annotate(mean_age=Avg('authors__age')).annotate(Avg('mean_age'))
 
626
        )
 
627
 
 
628
    if run_stddev_tests():
 
629
        def test_stddev(self):
 
630
            self.assertEqual(
 
631
                Book.objects.aggregate(StdDev('pages')),
 
632
                {'pages__stddev': Approximate(311.46, 1)}
 
633
            )
 
634
 
 
635
            self.assertEqual(
 
636
                Book.objects.aggregate(StdDev('rating')),
 
637
                {'rating__stddev': Approximate(0.60, 1)}
 
638
            )
 
639
 
 
640
            self.assertEqual(
 
641
                Book.objects.aggregate(StdDev('price')),
 
642
                {'price__stddev': Approximate(24.16, 2)}
 
643
            )
 
644
 
 
645
            self.assertEqual(
 
646
                Book.objects.aggregate(StdDev('pages', sample=True)),
 
647
                {'pages__stddev': Approximate(341.19, 2)}
 
648
            )
 
649
 
 
650
            self.assertEqual(
 
651
                Book.objects.aggregate(StdDev('rating', sample=True)),
 
652
                {'rating__stddev': Approximate(0.66, 2)}
 
653
            )
 
654
 
 
655
            self.assertEqual(
 
656
                Book.objects.aggregate(StdDev('price', sample=True)),
 
657
                {'price__stddev': Approximate(26.46, 1)}
 
658
            )
 
659
 
 
660
            self.assertEqual(
 
661
                Book.objects.aggregate(Variance('pages')),
 
662
                {'pages__variance': Approximate(97010.80, 1)}
 
663
            )
 
664
 
 
665
            self.assertEqual(
 
666
                Book.objects.aggregate(Variance('rating')),
 
667
                {'rating__variance': Approximate(0.36, 1)}
 
668
            )
 
669
 
 
670
            self.assertEqual(
 
671
                Book.objects.aggregate(Variance('price')),
 
672
                {'price__variance': Approximate(583.77, 1)}
 
673
            )
 
674
 
 
675
            self.assertEqual(
 
676
                Book.objects.aggregate(Variance('pages', sample=True)),
 
677
                {'pages__variance': Approximate(116412.96, 1)}
 
678
            )
 
679
 
 
680
            self.assertEqual(
 
681
                Book.objects.aggregate(Variance('rating', sample=True)),
 
682
                {'rating__variance': Approximate(0.44, 2)}
 
683
            )
 
684
 
 
685
            self.assertEqual(
 
686
                Book.objects.aggregate(Variance('price', sample=True)),
 
687
                {'price__variance': Approximate(700.53, 2)}
 
688
            )