- Added over() function, method to FunctionElement

classes, produces the _Over() construct which
in turn generates "window functions", i.e.
"<window function> OVER (PARTITION BY <partition by>,
ORDER BY <order by>)".
[ticket:1844]

3 files changed, 87 insertions, 0 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 5a6d46b1b..4ede56320 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2040,6 +2040,79 @@ class SelectTest(TestBase, AssertsCompiledSQL):
                             'CAST(NULL AS INTEGER)',
                             dialect=sqlite.dialect())
 
+    def test_over(self):
+        self.assert_compile(
+            func.row_number().over(
+                order_by=[table1.c.name, table1.c.description]
+            ),
+            "row_number() OVER (ORDER BY mytable.name, mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=[table1.c.name, table1.c.description]
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=[table1.c.name],
+                order_by=[table1.c.description]
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "ORDER BY mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=table1.c.name,
+                order_by=table1.c.description
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "ORDER BY mytable.description)"
+        )
+
+        self.assert_compile(
+            select([func.row_number().over(
+                order_by=table1.c.description
+            ).label('foo')]),
+            "SELECT row_number() OVER (ORDER BY mytable.description) "
+            "AS foo FROM mytable"
+        )
+
+        # test from_obj generation.
+        # from func:
+        self.assert_compile(
+            select([
+                func.max(table1.c.name).over(
+                    partition_by=['foo']
+                )
+            ]),
+            "SELECT max(mytable.name) OVER (PARTITION BY foo) "
+            "AS anon_1 FROM mytable"
+        )
+        # from partition_by
+        self.assert_compile(
+            select([
+                func.row_number().over(
+                    partition_by=[table1.c.name]
+                )
+            ]),
+            "SELECT row_number() OVER (PARTITION BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+        # from order_by
+        self.assert_compile(
+            select([
+                func.row_number().over(
+                    order_by=table1.c.name
+                )
+            ]),
+            "SELECT row_number() OVER (ORDER BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+
+
+
     def test_date_between(self):
         import datetime
         table = Table('dt', metadata,
diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py
index 627736370..f132cbb2d 100644
--- a/test/sql/test_generative.py
+++ b/test/sql/test_generative.py
@@ -316,6 +316,11 @@ class ClauseTest(TestBase, AssertsCompiledSQL):
         expr2 = CloningVisitor().traverse(expr)
         assert str(expr) == str(expr2)
 
+    def test_over(self):
+        expr = func.row_number().over(order_by=t1.c.col1)
+        expr2 = CloningVisitor().traverse(expr)
+        assert str(expr) == str(expr2)
+
     def test_adapt_union(self):
         u = union(t1.select().where(t1.c.col1==4), t1.select().where(t1.c.col1==5)).alias()
 
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 359084cd8..1adb00aea 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -1764,5 +1764,14 @@ class OperatorTest(TestBase):
             [(2,),(1,)]
         )
 
+    @testing.requires.window_functions
+    def test_over(self):
+        eq_(
+            select([
+                flds.c.intcol, func.row_number().over(order_by=flds.c.strcol)
+            ]).execute().fetchall(),
+            [(13, 1L), (5, 2L)]
+        )
+