Added NULLS FIRST and NULLS LAST support.

It's implemented as an extension to the asc() and desc() operators, called
nullsfirst() and nullslast().  [ticket:723]

3 files changed, 95 insertions, 1 deletions

diff --git a/test/lib/requires.py b/test/lib/requires.py
index d43b601a4..222dc93f6 100644
--- a/test/lib/requires.py
+++ b/test/lib/requires.py
@@ -270,6 +270,13 @@ def sane_multi_rowcount(fn):
         skip_if(lambda: not testing.db.dialect.supports_sane_multi_rowcount)
     )
 
+def nullsordering(fn):
+    """Target backends that support nulls ordering."""
+    return _chain_decorators_on(
+        fn,
+        fails_on_everything_except('postgresql', 'oracle', 'firebird')
+    )
+ 
 def reflects_pk_names(fn):
     """Target driver reflects the name of primary key constraints."""
     return _chain_decorators_on(
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index bc0a2e9c3..b34eaeaae 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -921,6 +921,37 @@ class SelectTest(TestBase, AssertsCompiledSQL):
             "OR mytable.myid = :myid_2 OR mytable.myid = :myid_3"
         )
 
+    def test_order_by_nulls(self):
+        self.assert_compile(
+            table2.select(order_by = [table2.c.otherid, table2.c.othername.desc().nullsfirst()]),
+            "SELECT myothertable.otherid, myothertable.othername FROM "
+            "myothertable ORDER BY myothertable.otherid, myothertable.othername DESC NULLS FIRST"
+        )
+
+        self.assert_compile(
+            table2.select(order_by = [table2.c.otherid, table2.c.othername.desc().nullslast()]),
+            "SELECT myothertable.otherid, myothertable.othername FROM "
+            "myothertable ORDER BY myothertable.otherid, myothertable.othername DESC NULLS LAST"
+        )
+
+        self.assert_compile(
+            table2.select(order_by = [table2.c.otherid.nullslast(), table2.c.othername.desc().nullsfirst()]),
+            "SELECT myothertable.otherid, myothertable.othername FROM "
+            "myothertable ORDER BY myothertable.otherid NULLS LAST, myothertable.othername DESC NULLS FIRST"
+        )
+
+        self.assert_compile(
+            table2.select(order_by = [table2.c.otherid.nullsfirst(), table2.c.othername.desc()]),
+            "SELECT myothertable.otherid, myothertable.othername FROM "
+            "myothertable ORDER BY myothertable.otherid NULLS FIRST, myothertable.othername DESC"
+        )
+
+        self.assert_compile(
+            table2.select(order_by = [table2.c.otherid.nullsfirst(), table2.c.othername.desc().nullslast()]),
+            "SELECT myothertable.otherid, myothertable.othername FROM "
+            "myothertable ORDER BY myothertable.otherid NULLS FIRST, myothertable.othername DESC NULLS LAST"
+        )
+
     def test_orderby_groupby(self):
         self.assert_compile(
             table2.select(order_by = [table2.c.otherid, asc(table2.c.othername)]),
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index e14f5301e..276653e56 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -576,7 +576,63 @@ class QueryTest(TestBase):
                         use_labels=labels,
                         order_by=[users.c.user_id.desc()]),
                  [(3,), (2,), (1,)])
-    
+
+    @testing.requires.nullsordering
+    def test_order_by_nulls(self):
+        """Exercises ORDER BY clause generation.
+
+        Tests simple, compound, aliased and DESC clauses.
+        """
+
+        users.insert().execute(user_id=1)
+        users.insert().execute(user_id=2, user_name='b')
+        users.insert().execute(user_id=3, user_name='a')
+
+        def a_eq(executable, wanted):
+            got = list(executable.execute())
+            eq_(got, wanted)
+
+        for labels in False, True:
+            a_eq(users.select(order_by=[users.c.user_name.nullsfirst()],
+                              use_labels=labels),
+                 [(1, None), (3, 'a'), (2, 'b')])
+
+            a_eq(users.select(order_by=[users.c.user_name.nullslast()],
+                              use_labels=labels),
+                 [(3, 'a'), (2, 'b'), (1, None)])
+
+            a_eq(users.select(order_by=[asc(users.c.user_name).nullsfirst()],
+                              use_labels=labels),
+                 [(1, None), (3, 'a'), (2, 'b')])
+
+            a_eq(users.select(order_by=[asc(users.c.user_name).nullslast()],
+                              use_labels=labels),
+                 [(3, 'a'), (2, 'b'), (1, None)])
+
+            a_eq(users.select(order_by=[users.c.user_name.desc().nullsfirst()],
+                              use_labels=labels),
+                 [(1, None), (2, 'b'), (3, 'a')])
+
+            a_eq(users.select(order_by=[users.c.user_name.desc().nullslast()],
+                              use_labels=labels),
+                 [(2, 'b'), (3, 'a'), (1, None)])
+
+            a_eq(users.select(order_by=[desc(users.c.user_name).nullsfirst()],
+                              use_labels=labels),
+                 [(1, None), (2, 'b'), (3, 'a')])
+
+            a_eq(users.select(order_by=[desc(users.c.user_name).nullslast()],
+                              use_labels=labels),
+                 [(2, 'b'), (3, 'a'), (1, None)])
+
+            a_eq(users.select(order_by=[users.c.user_name.nullsfirst(), users.c.user_id],
+                              use_labels=labels),
+                 [(1, None), (3, 'a'), (2, 'b')])
+
+            a_eq(users.select(order_by=[users.c.user_name.nullslast(), users.c.user_id],
+                              use_labels=labels),
+                 [(3, 'a'), (2, 'b'), (1, None)])
+
     @testing.fails_on("+pyodbc", "pyodbc row doesn't seem to accept slices")
     def test_column_slices(self):
         users.insert().execute(user_id=1, user_name='john')