Fixed #14030 -- Allowed annotations to accept all expressions

4 files changed, 617 insertions, 0 deletions

diff --git a/tests/annotations/__init__.py b/tests/annotations/__init__.py
new file mode 100644
index 0000000000..e69de29bb2
--- /dev/null
+++ b/tests/annotations/__init__.py
diff --git a/tests/annotations/fixtures/annotations.json b/tests/annotations/fixtures/annotations.json
new file mode 100644
index 0000000000..09c9b8346b
--- /dev/null
+++ b/tests/annotations/fixtures/annotations.json
@@ -0,0 +1,243 @@
+[
+    {
+        "pk": 1,
+        "model": "annotations.publisher",
+        "fields": {
+            "name": "Apress",
+            "num_awards": 3
+        }
+    },
+    {
+        "pk": 2,
+        "model": "annotations.publisher",
+        "fields": {
+            "name": "Sams",
+            "num_awards": 1
+        }
+    },
+    {
+        "pk": 3,
+        "model": "annotations.publisher",
+        "fields": {
+            "name": "Prentice Hall",
+            "num_awards": 7
+        }
+    },
+    {
+        "pk": 4,
+        "model": "annotations.publisher",
+        "fields": {
+            "name": "Morgan Kaufmann",
+            "num_awards": 9
+        }
+    },
+    {
+        "pk": 5,
+        "model": "annotations.publisher",
+        "fields": {
+            "name": "Jonno's House of Books",
+            "num_awards": 0
+        }
+    },
+    {
+        "pk": 1,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 1,
+            "isbn": "159059725",
+            "name": "The Definitive Guide to Django: Web Development Done Right",
+            "price": "30.00",
+            "rating": 4.5,
+            "authors": [1, 2],
+            "contact": 1,
+            "pages": 447,
+            "pubdate": "2007-12-6"
+        }
+    },
+    {
+        "pk": 2,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 2,
+            "isbn": "067232959",
+            "name": "Sams Teach Yourself Django in 24 Hours",
+            "price": "23.09",
+            "rating": 3.0,
+            "authors": [3],
+            "contact": 3,
+            "pages": 528,
+            "pubdate": "2008-3-3"
+        }
+    },
+    {
+        "pk": 3,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 1,
+            "isbn": "159059996",
+            "name": "Practical Django Projects",
+            "price": "29.69",
+            "rating": 4.0,
+            "authors": [4],
+            "contact": 4,
+            "pages": 300,
+            "pubdate": "2008-6-23"
+        }
+    },
+    {
+        "pk": 4,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 3,
+            "isbn": "013235613",
+            "name": "Python Web Development with Django",
+            "price": "29.69",
+            "rating": 4.0,
+            "authors": [5, 6, 7],
+            "contact": 5,
+            "pages": 350,
+            "pubdate": "2008-11-3"
+        }
+    },
+    {
+        "pk": 5,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 3,
+            "isbn": "013790395",
+            "name": "Artificial Intelligence: A Modern Approach",
+            "price": "82.80",
+            "rating": 4.0,
+            "authors": [8, 9],
+            "contact": 8,
+            "pages": 1132,
+            "pubdate": "1995-1-15"
+        }
+    },
+    {
+        "pk": 6,
+        "model": "annotations.book",
+        "fields": {
+            "publisher": 4,
+            "isbn": "155860191",
+            "name": "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp",
+            "price": "75.00",
+            "rating": 5.0,
+            "authors": [8],
+            "contact": 8,
+            "pages": 946,
+            "pubdate": "1991-10-15"
+        }
+    },
+    {
+        "pk": 1,
+        "model": "annotations.store",
+        "fields": {
+            "books": [1, 2, 3, 4, 5, 6],
+            "name": "Amazon.com",
+            "original_opening": "1994-4-23 9:17:42",
+            "friday_night_closing": "23:59:59"
+        }
+    },
+    {
+        "pk": 2,
+        "model": "annotations.store",
+        "fields": {
+            "books": [1, 3, 5, 6],
+            "name": "Books.com",
+            "original_opening": "2001-3-15 11:23:37",
+            "friday_night_closing": "23:59:59"
+        }
+    },
+    {
+        "pk": 3,
+        "model": "annotations.store",
+        "fields": {
+            "books": [3, 4, 6],
+            "name": "Mamma and Pappa's Books",
+            "original_opening": "1945-4-25 16:24:14",
+            "friday_night_closing": "21:30:00"
+        }
+    },
+    {
+        "pk": 1,
+        "model": "annotations.author",
+        "fields": {
+            "age": 34,
+            "friends": [2, 4],
+            "name": "Adrian Holovaty"
+        }
+    },
+    {
+        "pk": 2,
+        "model": "annotations.author",
+        "fields": {
+            "age": 35,
+            "friends": [1, 7],
+            "name": "Jacob Kaplan-Moss"
+        }
+    },
+    {
+        "pk": 3,
+        "model": "annotations.author",
+        "fields": {
+            "age": 45,
+            "friends": [],
+            "name": "Brad Dayley"
+        }
+    },
+    {
+        "pk": 4,
+        "model": "annotations.author",
+        "fields": {
+            "age": 29,
+            "friends": [1],
+            "name": "James Bennett"
+        }
+    },
+    {
+        "pk": 5,
+        "model": "annotations.author",
+        "fields": {
+            "age": 37,
+            "friends": [6, 7],
+            "name": "Jeffrey Forcier"
+        }
+    },
+    {
+        "pk": 6,
+        "model": "annotations.author",
+        "fields": {
+            "age": 29,
+            "friends": [5, 7],
+            "name": "Paul Bissex"
+        }
+    },
+    {
+        "pk": 7,
+        "model": "annotations.author",
+        "fields": {
+            "age": 25,
+            "friends": [2, 5, 6],
+            "name": "Wesley J. Chun"
+        }
+    },
+    {
+        "pk": 8,
+        "model": "annotations.author",
+        "fields": {
+            "age": 57,
+            "friends": [9],
+            "name": "Peter Norvig"
+        }
+    },
+    {
+        "pk": 9,
+        "model": "annotations.author",
+        "fields": {
+            "age": 46,
+            "friends": [8],
+            "name": "Stuart Russell"
+        }
+    }
+]
diff --git a/tests/annotations/models.py b/tests/annotations/models.py
new file mode 100644
index 0000000000..0c438b99f8
--- /dev/null
+++ b/tests/annotations/models.py
@@ -0,0 +1,86 @@
+# coding: utf-8
+from django.db import models
+from django.utils.encoding import python_2_unicode_compatible
+
+
+@python_2_unicode_compatible
+class Author(models.Model):
+    name = models.CharField(max_length=100)
+    age = models.IntegerField()
+    friends = models.ManyToManyField('self', blank=True)
+
+    def __str__(self):
+        return self.name
+
+
+@python_2_unicode_compatible
+class Publisher(models.Model):
+    name = models.CharField(max_length=255)
+    num_awards = models.IntegerField()
+
+    def __str__(self):
+        return self.name
+
+
+@python_2_unicode_compatible
+class Book(models.Model):
+    isbn = models.CharField(max_length=9)
+    name = models.CharField(max_length=255)
+    pages = models.IntegerField()
+    rating = models.FloatField()
+    price = models.DecimalField(decimal_places=2, max_digits=6)
+    authors = models.ManyToManyField(Author)
+    contact = models.ForeignKey(Author, related_name='book_contact_set')
+    publisher = models.ForeignKey(Publisher)
+    pubdate = models.DateField()
+
+    def __str__(self):
+        return self.name
+
+
+@python_2_unicode_compatible
+class Store(models.Model):
+    name = models.CharField(max_length=255)
+    books = models.ManyToManyField(Book)
+    original_opening = models.DateTimeField()
+    friday_night_closing = models.TimeField()
+
+    def __str__(self):
+        return self.name
+
+
+@python_2_unicode_compatible
+class DepartmentStore(Store):
+    chain = models.CharField(max_length=255)
+
+    def __str__(self):
+        return '%s - %s ' % (self.chain, self.name)
+
+
+@python_2_unicode_compatible
+class Employee(models.Model):
+    # The order of these fields matter, do not change. Certain backends
+    # rely on field ordering to perform database conversions, and this
+    # model helps to test that.
+    first_name = models.CharField(max_length=20)
+    manager = models.BooleanField(default=False)
+    last_name = models.CharField(max_length=20)
+    store = models.ForeignKey(Store)
+    age = models.IntegerField()
+    salary = models.DecimalField(max_digits=8, decimal_places=2)
+
+    def __str__(self):
+        return '%s %s' % (self.first_name, self.last_name)
+
+
+@python_2_unicode_compatible
+class Company(models.Model):
+    name = models.CharField(max_length=200)
+    motto = models.CharField(max_length=200, null=True, blank=True)
+    ticker_name = models.CharField(max_length=10, null=True, blank=True)
+    description = models.CharField(max_length=200, null=True, blank=True)
+
+    def __str__(self):
+        return ('Company(name=%s, motto=%s, ticker_name=%s, description=%s)'
+            % (self.name, self.motto, self.ticker_name, self.description)
+        )
diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
new file mode 100644
index 0000000000..afc23b85df
--- /dev/null
+++ b/tests/annotations/tests.py
@@ -0,0 +1,288 @@
+from __future__ import unicode_literals
+import datetime
+from decimal import Decimal
+
+from django.core.exceptions import FieldError
+from django.db.models import (
+    Sum, Count,
+    F, Value, Func,
+    IntegerField, BooleanField, CharField)
+from django.db.models.fields import FieldDoesNotExist
+from django.test import TestCase
+
+from .models import Author, Book, Store, DepartmentStore, Company, Employee
+
+
+class NonAggregateAnnotationTestCase(TestCase):
+    fixtures = ["annotations.json"]
+
+    def test_basic_annotation(self):
+        books = Book.objects.annotate(
+            is_book=Value(1, output_field=IntegerField()))
+        for book in books:
+            self.assertEqual(book.is_book, 1)
+
+    def test_basic_f_annotation(self):
+        books = Book.objects.annotate(another_rating=F('rating'))
+        for book in books:
+            self.assertEqual(book.another_rating, book.rating)
+
+    def test_joined_annotation(self):
+        books = Book.objects.select_related('publisher').annotate(
+            num_awards=F('publisher__num_awards'))
+        for book in books:
+            self.assertEqual(book.num_awards, book.publisher.num_awards)
+
+    def test_annotate_with_aggregation(self):
+        books = Book.objects.annotate(
+            is_book=Value(1, output_field=IntegerField()),
+            rating_count=Count('rating'))
+        for book in books:
+            self.assertEqual(book.is_book, 1)
+            self.assertEqual(book.rating_count, 1)
+
+    def test_aggregate_over_annotation(self):
+        agg = Author.objects.annotate(other_age=F('age')).aggregate(otherage_sum=Sum('other_age'))
+        other_agg = Author.objects.aggregate(age_sum=Sum('age'))
+        self.assertEqual(agg['otherage_sum'], other_agg['age_sum'])
+
+    def test_filter_annotation(self):
+        books = Book.objects.annotate(
+            is_book=Value(1, output_field=IntegerField())
+        ).filter(is_book=1)
+        for book in books:
+            self.assertEqual(book.is_book, 1)
+
+    def test_filter_annotation_with_f(self):
+        books = Book.objects.annotate(
+            other_rating=F('rating')
+        ).filter(other_rating=3.5)
+        for book in books:
+            self.assertEqual(book.other_rating, 3.5)
+
+    def test_filter_annotation_with_double_f(self):
+        books = Book.objects.annotate(
+            other_rating=F('rating')
+        ).filter(other_rating=F('rating'))
+        for book in books:
+            self.assertEqual(book.other_rating, book.rating)
+
+    def test_filter_agg_with_double_f(self):
+        books = Book.objects.annotate(
+            sum_rating=Sum('rating')
+        ).filter(sum_rating=F('sum_rating'))
+        for book in books:
+            self.assertEqual(book.sum_rating, book.rating)
+
+    def test_filter_wrong_annotation(self):
+        with self.assertRaisesRegexp(FieldError, "Cannot resolve keyword .*"):
+            list(Book.objects.annotate(
+                sum_rating=Sum('rating')
+            ).filter(sum_rating=F('nope')))
+
+    def test_update_with_annotation(self):
+        book_preupdate = Book.objects.get(pk=2)
+        Book.objects.annotate(other_rating=F('rating') - 1).update(rating=F('other_rating'))
+        book_postupdate = Book.objects.get(pk=2)
+        self.assertEqual(book_preupdate.rating - 1, book_postupdate.rating)
+
+    def test_annotation_with_m2m(self):
+        books = Book.objects.annotate(author_age=F('authors__age')).filter(pk=1).order_by('author_age')
+        self.assertEqual(books[0].author_age, 34)
+        self.assertEqual(books[1].author_age, 35)
+
+    def test_annotation_reverse_m2m(self):
+        books = Book.objects.annotate(
+            store_name=F('store__name')).filter(
+            name='Practical Django Projects').order_by(
+            'store_name')
+
+        self.assertQuerysetEqual(
+            books, [
+                'Amazon.com',
+                'Books.com',
+                'Mamma and Pappa\'s Books'
+            ],
+            lambda b: b.store_name
+        )
+
+    def test_values_annotation(self):
+        """
+        Annotations can reference fields in a values clause,
+        and contribute to an existing values clause.
+        """
+        # annotate references a field in values()
+        qs = Book.objects.values('rating').annotate(other_rating=F('rating') - 1)
+        book = qs.get(pk=1)
+        self.assertEqual(book['rating'] - 1, book['other_rating'])
+
+        # filter refs the annotated value
+        book = qs.get(other_rating=4)
+        self.assertEqual(book['other_rating'], 4)
+
+        # can annotate an existing values with a new field
+        book = qs.annotate(other_isbn=F('isbn')).get(other_rating=4)
+        self.assertEqual(book['other_rating'], 4)
+        self.assertEqual(book['other_isbn'], '155860191')
+
+    def test_defer_annotation(self):
+        """
+        Deferred attributes can be referenced by an annotation,
+        but they are not themselves deferred, and cannot be deferred.
+        """
+        qs = Book.objects.defer('rating').annotate(other_rating=F('rating') - 1)
+
+        with self.assertNumQueries(2):
+            book = qs.get(other_rating=4)
+            self.assertEqual(book.rating, 5)
+            self.assertEqual(book.other_rating, 4)
+
+        with self.assertRaisesRegexp(FieldDoesNotExist, "\w has no field named u?'other_rating'"):
+            book = qs.defer('other_rating').get(other_rating=4)
+
+    def test_mti_annotations(self):
+        """
+        Fields on an inherited model can be referenced by an
+        annotated field.
+        """
+        d = DepartmentStore.objects.create(
+            name='Angus & Robinson',
+            original_opening=datetime.date(2014, 3, 8),
+            friday_night_closing=datetime.time(21, 00, 00),
+            chain='Westfield'
+        )
+
+        books = Book.objects.filter(rating__gt=4)
+        for b in books:
+            d.books.add(b)
+
+        qs = DepartmentStore.objects.annotate(
+            other_name=F('name'),
+            other_chain=F('chain'),
+            is_open=Value(True, BooleanField()),
+            book_isbn=F('books__isbn')
+        ).select_related('store').order_by('book_isbn').filter(chain='Westfield')
+
+        self.assertQuerysetEqual(
+            qs, [
+                ('Angus & Robinson', 'Westfield', True, '155860191'),
+                ('Angus & Robinson', 'Westfield', True, '159059725')
+            ],
+            lambda d: (d.other_name, d.other_chain, d.is_open, d.book_isbn)
+        )
+
+    def test_column_field_ordering(self):
+        """
+        Test that columns are aligned in the correct order for
+        resolve_columns. This test will fail on mysql if column
+        ordering is out. Column fields should be aligned as:
+        1. extra_select
+        2. model_fields
+        3. annotation_fields
+        4. model_related_fields
+        """
+        store = Store.objects.first()
+        Employee.objects.create(id=1, first_name='Max', manager=True, last_name='Paine',
+                                store=store, age=23, salary=Decimal(50000.00))
+        Employee.objects.create(id=2, first_name='Buffy', manager=False, last_name='Summers',
+                                store=store, age=18, salary=Decimal(40000.00))
+
+        qs = Employee.objects.extra(
+            select={'random_value': '42'}
+        ).select_related('store').annotate(
+            annotated_value=Value(17, output_field=IntegerField())
+        )
+
+        rows = [
+            (1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00), store.name, 17),
+            (2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00), store.name, 17)
+        ]
+
+        self.assertQuerysetEqual(
+            qs.order_by('id'), rows,
+            lambda e: (
+                e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age,
+                e.salary, e.store.name, e.annotated_value))
+
+    def test_column_field_ordering_with_deferred(self):
+        store = Store.objects.first()
+        Employee.objects.create(id=1, first_name='Max', manager=True, last_name='Paine',
+                                store=store, age=23, salary=Decimal(50000.00))
+        Employee.objects.create(id=2, first_name='Buffy', manager=False, last_name='Summers',
+                                store=store, age=18, salary=Decimal(40000.00))
+
+        qs = Employee.objects.extra(
+            select={'random_value': '42'}
+        ).select_related('store').annotate(
+            annotated_value=Value(17, output_field=IntegerField())
+        )
+
+        rows = [
+            (1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00), store.name, 17),
+            (2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00), store.name, 17)
+        ]
+
+        # and we respect deferred columns!
+        self.assertQuerysetEqual(
+            qs.defer('age').order_by('id'), rows,
+            lambda e: (
+                e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age,
+                e.salary, e.store.name, e.annotated_value))
+
+    def test_custom_functions(self):
+        Company(name='Apple', motto=None, ticker_name='APPL', description='Beautiful Devices').save()
+        Company(name='Django Software Foundation', motto=None, ticker_name=None, description=None).save()
+        Company(name='Google', motto='Do No Evil', ticker_name='GOOG', description='Internet Company').save()
+        Company(name='Yahoo', motto=None, ticker_name=None, description='Internet Company').save()
+
+        qs = Company.objects.annotate(
+            tagline=Func(
+                F('motto'),
+                F('ticker_name'),
+                F('description'),
+                Value('No Tag'),
+                function='COALESCE')
+            ).order_by('name')
+
+        self.assertQuerysetEqual(
+            qs, [
+                ('Apple', 'APPL'),
+                ('Django Software Foundation', 'No Tag'),
+                ('Google', 'Do No Evil'),
+                ('Yahoo', 'Internet Company')
+            ],
+            lambda c: (c.name, c.tagline)
+        )
+
+    def test_custom_functions_can_ref_other_functions(self):
+        Company(name='Apple', motto=None, ticker_name='APPL', description='Beautiful Devices').save()
+        Company(name='Django Software Foundation', motto=None, ticker_name=None, description=None).save()
+        Company(name='Google', motto='Do No Evil', ticker_name='GOOG', description='Internet Company').save()
+        Company(name='Yahoo', motto=None, ticker_name=None, description='Internet Company').save()
+
+        class Lower(Func):
+            function = 'LOWER'
+
+        qs = Company.objects.annotate(
+            tagline=Func(
+                F('motto'),
+                F('ticker_name'),
+                F('description'),
+                Value('No Tag'),
+                function='COALESCE')
+        ).annotate(
+            tagline_lower=Lower(F('tagline'), output_field=CharField())
+        ).order_by('name')
+
+        # LOWER function supported by:
+        # oracle, postgres, mysql, sqlite, sqlserver
+
+        self.assertQuerysetEqual(
+            qs, [
+                ('Apple', 'APPL'.lower()),
+                ('Django Software Foundation', 'No Tag'.lower()),
+                ('Google', 'Do No Evil'.lower()),
+                ('Yahoo', 'Internet Company'.lower())
+            ],
+            lambda c: (c.name, c.tagline_lower)
+        )