[Sqlalchemy-tickets] Issue #3231: nullsfirst(), nullslast() broken with sqlite (zzzeek/sqlalchemy)

[Nils P. <iss...@bi...>]

New issue 3231: nullsfirst(), nullslast() broken with sqlite
https://bitbucket.org/zzzeek/sqlalchemy/issue/3231/nullsfirst-nullslast-broken-with-sqlite

Nils Philippsen:

Using nullsfirst() or nullslast() in a query to an sqlite database will break when it's executed because sqlite doesn't know about NULLS (FIRST|LAST).

See the attached test program:

```
#!python

nils@gibraltar:~/test/sqlalchemy> ./nullsfirstlast.py 
Traceback (most recent call last):
  File "./nullsfirstlast.py", line 38, in <module>
    print "\n".join(unicode(x) for x in query)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2438, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "NULLS": syntax error u'SELECT foo.foo_id AS foo_foo_id, foo.bar AS foo_bar \nFROM foo ORDER BY foo.bar NULLS FIRST ASC' ()
nils@gibraltar:~/test/sqlalchemy> 

```

I haven't found anything in the docs that one shouldn't attempt to do that on sqlite. On the other hand, I've found some recipes on stackoverflow ([here](http://stackoverflow.com/questions/12503120/how-to-do-nulls-last-in-sqlite) and [here](http://stackoverflow.com/questions/19629775/is-this-the-correct-syntax-for-sqlite-for-making-nulls-appear-last)) on how to fake this functionality in SQL, but they seem to use fake columns or special casing dependent on the column type, so I don't know if it's feasible to implement nullsfirst/nullslast in SQLAlchemy that way.

If I'm not off-track, this issue would be in all versions that know nullsfirst(), nullslast(), so everything from 0.7 upward.