Allow for multiple FOLLOWING/PRECEDING in a window range

Altered the range specification for window functions to allow
for two of the same PRECEDING or FOLLOWING keywords in a range
by allowing for the left side of the range to be positive
and for the right to be negative, e.g. (1, 3) is
"1 FOLLOWING AND 3 FOLLOWING".

Change-Id: I7d3a6c641151bb49219104968d18dac2266f3db8
Fixes: #4053

2 files changed, 26 insertions, 25 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 53009e2df..2dec3a5c3 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -848,16 +848,25 @@ class SQLCompiler(Compiled):
              cast.typeclause._compiler_dispatch(self, **kwargs))
 
     def _format_frame_clause(self, range_, **kw):
+
         return '%s AND %s' % (
             "UNBOUNDED PRECEDING"
             if range_[0] is elements.RANGE_UNBOUNDED
             else "CURRENT ROW" if range_[0] is elements.RANGE_CURRENT
-            else "%s PRECEDING" % (self.process(range_[0], **kw), ),
+            else "%s PRECEDING" % (
+                self.process(elements.literal(abs(range_[0])), **kw), )
+            if range_[0] < 0
+            else "%s FOLLOWING" % (
+                self.process(elements.literal(range_[0]), **kw), ),
 
             "UNBOUNDED FOLLOWING"
             if range_[1] is elements.RANGE_UNBOUNDED
             else "CURRENT ROW" if range_[1] is elements.RANGE_CURRENT
-            else "%s FOLLOWING" % (self.process(range_[1], **kw), )
+            else "%s PRECEDING" % (
+                self.process(elements.literal(abs(range_[1])), **kw), )
+            if range_[1] < 0
+            else "%s FOLLOWING" % (
+                self.process(elements.literal(range_[1]), **kw), ),
         )
 
     def visit_over(self, over, **kwargs):
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 46a8d2c35..36a6a6557 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3162,6 +3162,10 @@ class Over(ColumnElement):
 
             func.row_number().over(order_by='x', range_=(-2, None))
 
+        * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
+
+            func.row_number().over(order_by='x', range_=(1, 3))
+
         .. versionadded:: 1.1 support for RANGE / ROWS within a window
 
 
@@ -3223,42 +3227,30 @@ class Over(ColumnElement):
             raise exc.ArgumentError("2-tuple expected for range/rows")
 
         if range_[0] is None:
-            preceding = RANGE_UNBOUNDED
+            lower = RANGE_UNBOUNDED
         else:
             try:
-                preceding = int(range_[0])
+                lower = int(range_[0])
             except ValueError:
                 raise exc.ArgumentError(
-                    "Integer or None expected for preceding value")
+                    "Integer or None expected for range value")
             else:
-                if preceding > 0:
-                    raise exc.ArgumentError(
-                        "Preceding value must be a "
-                        "negative integer, zero, or None")
-                elif preceding < 0:
-                    preceding = literal(abs(preceding))
-                else:
-                    preceding = RANGE_CURRENT
+                if lower == 0:
+                    lower = RANGE_CURRENT
 
         if range_[1] is None:
-            following = RANGE_UNBOUNDED
+            upper = RANGE_UNBOUNDED
         else:
             try:
-                following = int(range_[1])
+                upper = int(range_[1])
             except ValueError:
                 raise exc.ArgumentError(
-                    "Integer or None expected for following value")
+                    "Integer or None expected for range value")
             else:
-                if following < 0:
-                    raise exc.ArgumentError(
-                        "Following value must be a positive "
-                        "integer, zero, or None")
-                elif following > 0:
-                    following = literal(following)
-                else:
-                    following = RANGE_CURRENT
+                if upper == 0:
+                    upper = RANGE_CURRENT
 
-        return preceding, following
+        return lower, upper
 
     @property
     def func(self):