471 lines
16 KiB
Python
471 lines
16 KiB
Python
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):
|
|
"""
|
|
Regression tests for #7314 and #7372
|
|
"""
|
|
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)
|
|
|
|
# Queryset to match most recent revision:
|
|
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),
|
|
)
|
|
|
|
# Queryset to search for string in 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,
|
|
)
|
|
|
|
# Following queryset should return the most recent revision:
|
|
self.assertQuerySetEqual(
|
|
qs & qs2,
|
|
[("Second Revision", "First Revision")],
|
|
transform=lambda r: (r.title, r.base.title),
|
|
ordered=False,
|
|
)
|
|
|
|
def test_extra_stay_tied(self):
|
|
# Extra select parameters should stay tied to their corresponding
|
|
# select portions. Applies when portions are updated or otherwise
|
|
# moved around.
|
|
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):
|
|
"""
|
|
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.
|
|
"""
|
|
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):
|
|
"""
|
|
Regression test for #7961: When not using a portion of an
|
|
extra(...) in a query, remove any corresponding parameters from the
|
|
query as well.
|
|
"""
|
|
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):
|
|
"""
|
|
Regression test for #8063: limiting a query shouldn't discard any
|
|
extra() bits.
|
|
"""
|
|
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):
|
|
"""
|
|
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.
|
|
"""
|
|
self.assertQuerySetEqual(
|
|
(
|
|
Order.objects.extra(
|
|
where=["username=%s"], params=["fred"], tables=["auth_user"]
|
|
).order_by("created_by")
|
|
),
|
|
[],
|
|
)
|
|
|
|
def test_regression_8819(self):
|
|
"""
|
|
Regression test for #8819: Fields in the extra(select=...) list
|
|
should be available to extra(order_by=...).
|
|
"""
|
|
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):
|
|
"""
|
|
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.
|
|
"""
|
|
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):
|
|
"""
|
|
Regression test for #10256... If there is a values() clause, Extra
|
|
columns are only returned if they are explicitly mentioned.
|
|
"""
|
|
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",
|
|
}
|
|
],
|
|
)
|
|
|
|
# Extra clauses after an empty values clause are still included
|
|
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",
|
|
}
|
|
],
|
|
)
|
|
|
|
# Extra columns are ignored if not mentioned in the values() clause
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values("first", "second")
|
|
),
|
|
[{"second": "second", "first": "first"}],
|
|
)
|
|
|
|
# Extra columns after a non-empty values() clause are ignored
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.values("first", "second").extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
)
|
|
),
|
|
[{"second": "second", "first": "first"}],
|
|
)
|
|
|
|
# Extra columns can be partially returned
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values("first", "second", "foo")
|
|
),
|
|
[{"second": "second", "foo": "first", "first": "first"}],
|
|
)
|
|
|
|
# Also works if only extra columns are included
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values("foo", "whiz")
|
|
),
|
|
[{"foo": "first", "whiz": "third"}],
|
|
)
|
|
|
|
# Values list works the same way
|
|
# All columns are returned for an empty values_list()
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values_list()
|
|
),
|
|
[("first", "second", "third", obj.pk, "first", "second", "third")],
|
|
)
|
|
|
|
# Extra columns after an empty values_list() are still included
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.values_list().extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
)
|
|
),
|
|
[("first", "second", "third", obj.pk, "first", "second", "third")],
|
|
)
|
|
|
|
# Extra columns ignored completely if not mentioned in values_list()
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values_list("first", "second")
|
|
),
|
|
[("first", "second")],
|
|
)
|
|
|
|
# Extra columns after a non-empty values_list() clause are ignored completely
|
|
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"],
|
|
)
|
|
|
|
# Only the extra columns specified in the values_list() are returned
|
|
self.assertEqual(
|
|
list(
|
|
TestObject.objects.extra(
|
|
select={"foo": "first", "bar": "second", "whiz": "third"}
|
|
).values_list("first", "second", "whiz")
|
|
),
|
|
[("first", "second", "third")],
|
|
)
|
|
|
|
# ...also works if only extra columns are included
|
|
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"],
|
|
)
|
|
|
|
# ... and values are returned in the order they are specified
|
|
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):
|
|
"""
|
|
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.
|
|
"""
|
|
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):
|
|
"""
|
|
Extra WHERE clauses get correctly ANDed, even when they
|
|
contain OR operations.
|
|
"""
|
|
# Test Case 1: should appear in queryset.
|
|
t1 = TestObject.objects.create(first="a", second="a", third="a")
|
|
# Test Case 2: should appear in queryset.
|
|
t2 = TestObject.objects.create(first="b", second="a", third="a")
|
|
# Test Case 3: should not appear in queryset, bug case.
|
|
t = TestObject(first="a", second="a", third="b")
|
|
t.save()
|
|
# Test Case 4: should not appear in queryset.
|
|
t = TestObject(first="b", second="a", third="b")
|
|
t.save()
|
|
# Test Case 5: should not appear in queryset.
|
|
t = TestObject(first="b", second="b", third="a")
|
|
t.save()
|
|
# Test Case 6: should not appear in queryset, bug case.
|
|
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])
|
|
# 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.
|
|
self.assertSequenceEqual(
|
|
qs.order_by("-second_extra").values_list("first"), [("a",), ("a",)]
|
|
)
|