[Sqlalchemy-tickets] Issue #3231: nullsfirst(), nullslast() broken with sqlite (zzzeek/sqlalchemy)
Brought to you by:
zzzeek
|
From: Nils P. <iss...@bi...> - 2014-10-19 18:04:42
|
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. |