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]

7 files changed, 147 insertions, 4 deletions

diff --git a/CHANGES b/CHANGES
index 900cdd54f..c4ef9e1a9 100644
--- a/CHANGES
+++ b/CHANGES
@@ -6,6 +6,11 @@ CHANGES
 0.7.0b1
 =======
 
+- sql
+  - 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]
+
 - mssql
   - the String/Unicode types, and their counterparts VARCHAR/
     NVARCHAR, emit "max" as the length when no length is 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 0c76f3e74..8bd728a7c 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -101,6 +101,8 @@ OPERATORS =  {
     # modifiers
     operators.desc_op : ' DESC',
     operators.asc_op : ' ASC',
+    operators.nullsfirst_op : ' NULLS FIRST',
+    operators.nullslast_op : ' NULLS LAST',
 }
 
 FUNCTIONS = {
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index f090d14d6..60ca33b93 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -46,12 +46,32 @@ __all__ = [
     'bindparam', 'case', 'cast', 'column', 'delete', 'desc', 'distinct',
     'except_', 'except_all', 'exists', 'extract', 'func', 'modifier',
     'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label',
-    'literal', 'literal_column', 'not_', 'null', 'or_', 'outparam',
-    'outerjoin', 'select', 'subquery', 'table', 'text', 'tuple_', 'type_coerce',
-    'union', 'union_all', 'update', ]
+    'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
+    'or_', 'outparam', 'outerjoin', 'select', 'subquery', 'table', 'text',
+    'tuple_', 'type_coerce', 'union', 'union_all', 'update', ]
 
 PARSE_AUTOCOMMIT = util.symbol('PARSE_AUTOCOMMIT')
 
+def nullsfirst(column):
+    """Return a NULLS FIRST ``ORDER BY`` clause element.
+
+    e.g.::
+
+      order_by = [desc(table1.mycol).nullsfirst()]
+
+    """
+    return _UnaryExpression(column, modifier=operators.nullsfirst_op)
+
+def nullslast(column):
+    """Return a NULLS LAST ``ORDER BY`` clause element.
+
+    e.g.::
+
+      order_by = [desc(table1.mycol).nullslast()]
+
+    """
+    return _UnaryExpression(column, modifier=operators.nullslast_op)
+
 def desc(column):
     """Return a descending ``ORDER BY`` clause element.
 
@@ -1570,6 +1590,12 @@ class ColumnOperators(Operators):
     def asc(self):
         return self.operate(operators.asc_op)
 
+    def nullsfirst(self):
+        return self.operate(operators.nullsfirst_op)
+
+    def nullslast(self):
+        return self.operate(operators.nullslast_op)
+
     def collate(self, collation):
         return self.operate(operators.collate, collation)
 
@@ -1813,6 +1839,16 @@ class _CompareMixin(ColumnOperators):
 
         return asc(self)
 
+    def nullsfirst(self):
+        """Produce a NULLS FIRST clause, i.e. ``NULLS FIRST``"""
+
+        return nullsfirst(self)
+
+    def nullslast(self):
+        """Produce a NULLS LAST clause, i.e. ``NULLS LAST``"""
+
+        return nullslast(self)
+
     def distinct(self):
         """Produce a DISTINCT clause, i.e. ``DISTINCT <columnname>``"""
 
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 0577e6668..77916e80e 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -83,6 +83,12 @@ def desc_op(a):
 def asc_op(a):
     return a.asc()
 
+def nullsfirst_op(a):
+    return a.nullsfirst()
+
+def nullslast_op(a):
+    return a.nullslast()
+
 _commutative = set([eq, ne, add, mul])
 
 def is_commutative(op):
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')