regression tests for 7314 and 7372 queryset to match most recent revision queryset to search for string in title following queryset should return the most recent revision extra select parameters should stay tied to their corresponding select portions applies when portions are updated or otherwise moved around regression test for 7957 combining extra calls should leave the corresponding parameters associated with the right extra bit i e internal dictionary must remain sorted regression test for 7961 when not using a portion of an extra in a query remove any corresponding parameters from the query as well regression test for 8063 limiting a query shouldn t discard any extra bits regression test for 8039 ordering sometimes removed relevant tables from extra this test is the critical case ordering uses a table but then removes the reference because of an optimization the table should still be present because of the extra call regression test for 8819 fields in the extraselect list should be available to extraorderby when calling the dates method on a queryset with extra selection columns we can and should ignore those columns they don t change the result and cause incorrect sql to be produced otherwise regression test for 10256 if there is a values clause extra columns are only returned if they are explicitly mentioned extra clauses after an empty values clause are still included extra columns are ignored if not mentioned in the values clause extra columns after a nonempty values clause are ignored extra columns can be partially returned also works if only extra columns are included values list works the same way all columns are returned for an empty valueslist extra columns after an empty valueslist are still included extra columns ignored completely if not mentioned in valueslist extra columns after a nonempty valueslist clause are ignored completely only the extra columns specified in the valueslist are returned also works if only extra columns are included and values are returned in the order they are specified regression for 10847 the list of extra columns can always be accurately evaluated using an inner query ensures that assql is producing correct output without requiring full evaluation and execution of the inner query extra where clauses get correctly anded even when they contain or operations test case 1 should appear in queryset test case 2 should appear in queryset test case 3 should not appear in queryset bug case test case 4 should not appear in queryset test case 5 should not appear in queryset test case 6 should not appear in queryset bug case note the extra ordering must appear in select clause so we get two nondistinct results here this is on purpose see 7070 extra select doesn t appear in result values regression tests for 7314 and 7372 queryset to match most recent revision queryset to search for string in title following queryset should return the most recent revision extra select parameters should stay tied to their corresponding select portions applies when portions are updated or otherwise moved around regression test for 7957 combining extra calls should leave the corresponding parameters associated with the right extra bit i e internal dictionary must remain sorted regression test for 7961 when not using a portion of an extra in a query remove any corresponding parameters from the query as well regression test for 8063 limiting a query shouldn t discard any extra bits regression test for 8039 ordering sometimes removed relevant tables from extra this test is the critical case ordering uses a table but then removes the reference because of an optimization the table should still be present because of the extra call regression test for 8819 fields in the extra select list should be available to extra order_by when calling the dates method on a queryset with extra selection columns we can and should ignore those columns they don t change the result and cause incorrect sql to be produced otherwise regression test for 10256 if there is a values clause extra columns are only returned if they are explicitly mentioned extra clauses after an empty values clause are still included extra columns are ignored if not mentioned in the values clause extra columns after a non empty values clause are ignored extra columns can be partially returned also works if only extra columns are included values list works the same way all columns are returned for an empty values_list extra columns after an empty values_list are still included extra columns ignored completely if not mentioned in values_list extra columns after a non empty values_list clause are ignored completely only the extra columns specified in the values_list are returned also works if only extra columns are included and values are returned in the order they are specified regression for 10847 the list of extra columns can always be accurately evaluated using an inner query ensures that as_sql is producing correct output without requiring full evaluation and execution of the inner query extra where clauses get correctly anded even when they contain or operations test case 1 should appear in queryset test case 2 should appear in queryset test case 3 should not appear in queryset bug case test case 4 should not appear in queryset test case 5 should not appear in queryset test case 6 should not appear in queryset bug case note the extra ordering must appear in select clause so we get two non distinct results here this is on purpose see 7070 extra select doesn t appear in result values
import datetime

from django.contrib.auth.models import User
from django.test import TestCase

from .models import Order, RevisionableModel, TestObject


class ExtraRegressTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        cls.u = User.objects.create_user(
            username="fred", password="secret", email="fred@example.com"
        )

    def test_regression_7314_7372(self):
        
        rm = RevisionableModel.objects.create(
            title="First Revision", when=datetime.datetime(2008, 9, 28, 10, 30, 0)
        )
        self.assertEqual(rm.pk, rm.base.pk)

        rm2 = rm.new_revision()
        rm2.title = "Second Revision"
        rm.when = datetime.datetime(2008, 9, 28, 14, 25, 0)
        rm2.save()

        self.assertEqual(rm2.title, "Second Revision")
        self.assertEqual(rm2.base.title, "First Revision")

        self.assertNotEqual(rm2.pk, rm.pk)
        self.assertEqual(rm2.base.pk, rm.pk)

        
        qs = RevisionableModel.objects.extra(
            where=[
                "%(table)s.id IN "
                "(SELECT MAX(rev.id) FROM %(table)s rev GROUP BY rev.base_id)"
                % {
                    "table": RevisionableModel._meta.db_table,
                }
            ]
        )

        self.assertQuerySetEqual(
            qs,
            [("Second Revision", "First Revision")],
            transform=lambda r: (r.title, r.base.title),
        )

        
        qs2 = RevisionableModel.objects.filter(title__contains="Revision")
        self.assertQuerySetEqual(
            qs2,
            [
                ("First Revision", "First Revision"),
                ("Second Revision", "First Revision"),
            ],
            transform=lambda r: (r.title, r.base.title),
            ordered=False,
        )

        
        self.assertQuerySetEqual(
            qs & qs2,
            [("Second Revision", "First Revision")],
            transform=lambda r: (r.title, r.base.title),
            ordered=False,
        )

    def test_extra_stay_tied(self):
        
        
        
        qs = User.objects.extra(
            select={"alpha": "%s", "beta": "2", "gamma": "%s"}, select_params=(1, 3)
        )
        qs = qs.extra(select={"beta": 4})
        qs = qs.extra(select={"alpha": "%s"}, select_params=[5])
        self.assertEqual(
            list(qs.filter(id=self.u.id).values("alpha", "beta", "gamma")),
            [{"alpha": 5, "beta": 4, "gamma": 3}],
        )

    def test_regression_7957(self):
        
        self.assertEqual(
            (
                User.objects.extra(select={"alpha": "%s"}, select_params=(1,))
                .extra(select={"beta": "%s"}, select_params=(2,))[0]
                .alpha
            ),
            1,
        )

        self.assertEqual(
            (
                User.objects.extra(select={"beta": "%s"}, select_params=(1,))
                .extra(select={"alpha": "%s"}, select_params=(2,))[0]
                .alpha
            ),
            2,
        )

    def test_regression_7961(self):
        
        self.assertEqual(
            list(
                User.objects.extra(select={"alpha": "%s"}, select_params=(-6,))
                .filter(id=self.u.id)
                .values_list("id", flat=True)
            ),
            [self.u.id],
        )

    def test_regression_8063(self):
        
        qs = User.objects.extra(where=["id=%s"], params=[self.u.id])
        self.assertSequenceEqual(qs, [self.u])
        self.assertSequenceEqual(qs[:1], [self.u])

    def test_regression_8039(self):
        
        self.assertQuerySetEqual(
            (
                Order.objects.extra(
                    where=["username=%s"], params=["fred"], tables=["auth_user"]
                ).order_by("created_by")
            ),
            [],
        )

    def test_regression_8819(self):
        
        self.assertSequenceEqual(
            User.objects.filter(pk=self.u.id)
            .extra(select={"extra_field": 1})
            .distinct(),
            [self.u],
        )
        self.assertSequenceEqual(
            User.objects.filter(pk=self.u.id).extra(
                select={"extra_field": 1}, order_by=["extra_field"]
            ),
            [self.u],
        )
        self.assertSequenceEqual(
            User.objects.filter(pk=self.u.id)
            .extra(select={"extra_field": 1}, order_by=["extra_field"])
            .distinct(),
            [self.u],
        )

    def test_dates_query(self):
        
        RevisionableModel.objects.create(
            title="First Revision", when=datetime.datetime(2008, 9, 28, 10, 30, 0)
        )

        self.assertSequenceEqual(
            RevisionableModel.objects.extra(select={"the_answer": "id"}).datetimes(
                "when", "month"
            ),
            [datetime.datetime(2008, 9, 1, 0, 0)],
        )

    def test_values_with_extra(self):
        
        obj = TestObject(first="first", second="second", third="third")
        obj.save()

        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values()
            ),
            [
                {
                    "bar": "second",
                    "third": "third",
                    "second": "second",
                    "whiz": "third",
                    "foo": "first",
                    "id": obj.pk,
                    "first": "first",
                }
            ],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.values().extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                )
            ),
            [
                {
                    "bar": "second",
                    "third": "third",
                    "second": "second",
                    "whiz": "third",
                    "foo": "first",
                    "id": obj.pk,
                    "first": "first",
                }
            ],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values("first", "second")
            ),
            [{"second": "second", "first": "first"}],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.values("first", "second").extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                )
            ),
            [{"second": "second", "first": "first"}],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values("first", "second", "foo")
            ),
            [{"second": "second", "foo": "first", "first": "first"}],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values("foo", "whiz")
            ),
            [{"foo": "first", "whiz": "third"}],
        )

        
        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list()
            ),
            [("first", "second", "third", obj.pk, "first", "second", "third")],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.values_list().extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                )
            ),
            [("first", "second", "third", obj.pk, "first", "second", "third")],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("first", "second")
            ),
            [("first", "second")],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.values_list("first", "second").extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                )
            ),
            [("first", "second")],
        )

        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("second", flat=True)
            ),
            ["second"],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("first", "second", "whiz")
            ),
            [("first", "second", "third")],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("foo", "whiz")
            ),
            [("first", "third")],
        )

        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("whiz", flat=True)
            ),
            ["third"],
        )

        
        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("whiz", "foo")
            ),
            [("third", "first")],
        )

        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("first", "id")
            ),
            [("first", obj.pk)],
        )

        self.assertEqual(
            list(
                TestObject.objects.extra(
                    select={"foo": "first", "bar": "second", "whiz": "third"}
                ).values_list("whiz", "first", "bar", "id")
            ),
            [("third", "first", "second", obj.pk)],
        )

    def test_regression_10847(self):
        
        obj = TestObject(first="first", second="second", third="third")
        obj.save()

        self.assertEqual(
            list(TestObject.objects.extra(select={"extra": 1}).values("pk")),
            [{"pk": obj.pk}],
        )

        self.assertSequenceEqual(
            TestObject.objects.filter(
                pk__in=TestObject.objects.extra(select={"extra": 1}).values("pk")
            ),
            [obj],
        )

        self.assertEqual(
            list(TestObject.objects.values("pk").extra(select={"extra": 1})),
            [{"pk": obj.pk}],
        )

        self.assertSequenceEqual(
            TestObject.objects.filter(
                pk__in=TestObject.objects.values("pk").extra(select={"extra": 1})
            ),
            [obj],
        )

        self.assertSequenceEqual(
            TestObject.objects.filter(pk=obj.pk)
            | TestObject.objects.extra(where=["id > %s"], params=[obj.pk]),
            [obj],
        )

    def test_regression_17877(self):
        
        
        t1 = TestObject.objects.create(first="a", second="a", third="a")
        
        t2 = TestObject.objects.create(first="b", second="a", third="a")
        
        t = TestObject(first="a", second="a", third="b")
        t.save()
        
        t = TestObject(first="b", second="a", third="b")
        t.save()
        
        t = TestObject(first="b", second="b", third="a")
        t.save()
        
        t = TestObject(first="a", second="b", third="b")
        t.save()

        self.assertCountEqual(
            TestObject.objects.extra(
                where=["first = 'a' OR second = 'a'", "third = 'a'"],
            ),
            [t1, t2],
        )

    def test_extra_values_distinct_ordering(self):
        t1 = TestObject.objects.create(first="a", second="a", third="a")
        t2 = TestObject.objects.create(first="a", second="b", third="b")
        qs = (
            TestObject.objects.extra(select={"second_extra": "second"})
            .values_list("id", flat=True)
            .distinct()
        )
        self.assertSequenceEqual(qs.order_by("second_extra"), [t1.pk, t2.pk])
        self.assertSequenceEqual(qs.order_by("-second_extra"), [t2.pk, t1.pk])
        
        
        
        self.assertSequenceEqual(
            qs.order_by("-second_extra").values_list("first"), [("a",), ("a",)]
        )