nullsfirst(), nullslast() broken with sqlite

[Former user]

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:

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 and here) 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.

[Mike Bayer]

if SQLite doesn't offer NULLSFIRST/LAST functionality, then usually we'd say, "don't use nullsfirst()/nullslast() with SQLite". It means that your statement requires a feature that isn't supported on your target database, so you'd have to alter your use case to be compatible with SQLite in any case. Silently ignoring it here would mean that your statement would silently fail to do what you intend.

This is SQLAlchemy's usual approach to SQL standard elements that aren't supported by a target database - let it raise an exception. The documentation can't be relied upon to document every target database that happens to not support various parts of SQL (we try to do it for obvious ones that aren't changing anytime soon, but in this case, who knows if a new sqlite3 version would start supporting these constructs? then our docs are out of date). Examples of major SQL features that will break in a similar way if used on non-supporting backends include over(), returning(), cte(), filter(), distinct(col), UPDATE..FROM, as well as a vast amount of func.XYZ() constructs that are backend-specific.

So I'm not seeing what action there'd be to take here.