From 48e19bae49f271cccbb8a8f4549c9366b7cecac6 Mon Sep 17 00:00:00 2001 From: Simon Charette Date: Tue, 20 Apr 2021 22:25:52 -0400 Subject: [3.2.x] Fixed #32650 -- Fixed handling subquery aliasing on queryset combination. This issue started manifesting itself when nesting a combined subquery relying on exclude() since 8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1 but sql.Query.combine never properly handled subqueries outer refs in the first place, see QuerySetBitwiseOperationTests.test_subquery_aliases() (refs #27149). Thanks Raffaele Salmaso for the report. Backport of 6d0cbe42c3d382e5393d4af48185c546bb0ada1f from main --- tests/queries/tests.py | 41 +++++++++++++++++++++++++++++++++++------ 1 file changed, 35 insertions(+), 6 deletions(-) (limited to 'tests') diff --git a/tests/queries/tests.py b/tests/queries/tests.py index 37ba239419..584784fd1d 100644 --- a/tests/queries/tests.py +++ b/tests/queries/tests.py @@ -2079,36 +2079,50 @@ class SubqueryTests(TestCase): ) -@skipUnlessDBFeature('allow_sliced_subqueries_with_in') class QuerySetBitwiseOperationTests(TestCase): @classmethod def setUpTestData(cls): - school = School.objects.create() - cls.room_1 = Classroom.objects.create(school=school, has_blackboard=False, name='Room 1') - cls.room_2 = Classroom.objects.create(school=school, has_blackboard=True, name='Room 2') - cls.room_3 = Classroom.objects.create(school=school, has_blackboard=True, name='Room 3') - cls.room_4 = Classroom.objects.create(school=school, has_blackboard=False, name='Room 4') + cls.school = School.objects.create() + cls.room_1 = Classroom.objects.create(school=cls.school, has_blackboard=False, name='Room 1') + cls.room_2 = Classroom.objects.create(school=cls.school, has_blackboard=True, name='Room 2') + cls.room_3 = Classroom.objects.create(school=cls.school, has_blackboard=True, name='Room 3') + cls.room_4 = Classroom.objects.create(school=cls.school, has_blackboard=False, name='Room 4') + @skipUnlessDBFeature('allow_sliced_subqueries_with_in') def test_or_with_rhs_slice(self): qs1 = Classroom.objects.filter(has_blackboard=True) qs2 = Classroom.objects.filter(has_blackboard=False)[:1] self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2, self.room_3]) + @skipUnlessDBFeature('allow_sliced_subqueries_with_in') def test_or_with_lhs_slice(self): qs1 = Classroom.objects.filter(has_blackboard=True)[:1] qs2 = Classroom.objects.filter(has_blackboard=False) self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2, self.room_4]) + @skipUnlessDBFeature('allow_sliced_subqueries_with_in') def test_or_with_both_slice(self): qs1 = Classroom.objects.filter(has_blackboard=False)[:1] qs2 = Classroom.objects.filter(has_blackboard=True)[:1] self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2]) + @skipUnlessDBFeature('allow_sliced_subqueries_with_in') def test_or_with_both_slice_and_ordering(self): qs1 = Classroom.objects.filter(has_blackboard=False).order_by('-pk')[:1] qs2 = Classroom.objects.filter(has_blackboard=True).order_by('-name')[:1] self.assertCountEqual(qs1 | qs2, [self.room_3, self.room_4]) + def test_subquery_aliases(self): + combined = School.objects.filter(pk__isnull=False) & School.objects.filter( + Exists(Classroom.objects.filter( + has_blackboard=True, + school=OuterRef('pk'), + )), + ) + self.assertSequenceEqual(combined, [self.school]) + nested_combined = School.objects.filter(pk__in=combined.values('pk')) + self.assertSequenceEqual(nested_combined, [self.school]) + class CloneTests(TestCase): @@ -2802,6 +2816,21 @@ class ExcludeTests(TestCase): ) self.assertIn('exists', captured_queries[0]['sql'].lower()) + def test_exclude_subquery(self): + subquery = JobResponsibilities.objects.filter( + responsibility__description='bar', + ) | JobResponsibilities.objects.exclude( + job__responsibilities__description='foo', + ) + self.assertSequenceEqual( + Job.objects.annotate( + responsibility=subquery.filter( + job=OuterRef('name'), + ).values('id')[:1] + ), + [self.j1, self.j2], + ) + class ExcludeTest17600(TestCase): """ -- cgit v1.3