Refs #29850 -- Added RowRange support for positive integer start and negative integer end.

1 files changed, 101 insertions, 2 deletions

diff --git a/tests/expressions_window/tests.py b/tests/expressions_window/tests.py
index 3a02a36707..f73bd511e5 100644
--- a/tests/expressions_window/tests.py
+++ b/tests/expressions_window/tests.py
@@ -1328,6 +1328,84 @@ class WindowFunctionTests(TestCase):
             ),
         )
 
+    def test_row_range_both_preceding(self):
+        """
+        A query with ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING.
+        The resulting sum is the sum of the previous two (if they exist) rows
+        according to the ordering clause.
+        """
+        qs = Employee.objects.annotate(
+            sum=Window(
+                expression=Sum("salary"),
+                order_by=[F("hire_date").asc(), F("name").desc()],
+                frame=RowRange(start=-2, end=-1),
+            )
+        ).order_by("hire_date")
+        self.assertIn("ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING", str(qs.query))
+        self.assertQuerySetEqual(
+            qs,
+            [
+                ("Miller", 100000, "Management", datetime.date(2005, 6, 1), None),
+                ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000),
+                ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 180000),
+                ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 125000),
+                ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 100000),
+                ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 100000),
+                ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 82000),
+                ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 90000),
+                ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 91000),
+                ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 98000),
+                ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 100000),
+                ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 90000),
+            ],
+            transform=lambda row: (
+                row.name,
+                row.salary,
+                row.department,
+                row.hire_date,
+                row.sum,
+            ),
+        )
+
+    def test_row_range_both_following(self):
+        """
+        A query with ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING.
+        The resulting sum is the sum of the following two (if they exist) rows
+        according to the ordering clause.
+        """
+        qs = Employee.objects.annotate(
+            sum=Window(
+                expression=Sum("salary"),
+                order_by=[F("hire_date").asc(), F("name").desc()],
+                frame=RowRange(start=1, end=2),
+            )
+        ).order_by("hire_date")
+        self.assertIn("ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING", str(qs.query))
+        self.assertQuerySetEqual(
+            qs,
+            [
+                ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 125000),
+                ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000),
+                ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 100000),
+                ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 82000),
+                ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 90000),
+                ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 91000),
+                ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 98000),
+                ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 100000),
+                ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 90000),
+                ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 84000),
+                ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 34000),
+                ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
+            ],
+            transform=lambda row: (
+                row.name,
+                row.salary,
+                row.department,
+                row.hire_date,
+                row.sum,
+            ),
+        )
+
     @skipUnlessDBFeature("can_distinct_on_fields")
     def test_distinct_window_function(self):
         """
@@ -1479,6 +1557,19 @@ class WindowFunctionTests(TestCase):
                 )
             )
 
+    def test_invalid_start_end_value_for_row_range(self):
+        msg = "start cannot be greater than end."
+        with self.assertRaisesMessage(ValueError, msg):
+            list(
+                Employee.objects.annotate(
+                    test=Window(
+                        expression=Sum("salary"),
+                        order_by=F("hire_date").asc(),
+                        frame=RowRange(start=4, end=-3),
+                    )
+                )
+            )
+
     def test_invalid_type_end_value_range(self):
         msg = "end argument must be a positive integer, zero, or None, but got 'a'."
         with self.assertRaisesMessage(ValueError, msg):
@@ -1505,7 +1596,7 @@ class WindowFunctionTests(TestCase):
             )
 
     def test_invalid_type_end_row_range(self):
-        msg = "end argument must be a positive integer, zero, or None, but got 'a'."
+        msg = "end argument must be an integer, zero, or None, but got 'a'."
         with self.assertRaisesMessage(ValueError, msg):
             list(
                 Employee.objects.annotate(
@@ -1551,7 +1642,7 @@ class WindowFunctionTests(TestCase):
             )
 
     def test_invalid_type_start_row_range(self):
-        msg = "start argument must be a negative integer, zero, or None, but got 'a'."
+        msg = "start argument must be an integer, zero, or None, but got 'a'."
         with self.assertRaisesMessage(ValueError, msg):
             list(
                 Employee.objects.annotate(
@@ -1636,6 +1727,14 @@ class NonQueryWindowTests(SimpleTestCase):
             repr(RowRange(start=0, end=0)),
             "<RowRange: ROWS BETWEEN CURRENT ROW AND CURRENT ROW>",
         )
+        self.assertEqual(
+            repr(RowRange(start=-2, end=-1)),
+            "<RowRange: ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING>",
+        )
+        self.assertEqual(
+            repr(RowRange(start=1, end=2)),
+            "<RowRange: ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING>",
+        )
 
     def test_empty_group_by_cols(self):
         window = Window(expression=Sum("pk"))