I’m trying to be able to dynamically sort a query by a given column name and have nulls be placed at the end of the list. It works fine when using desc
ordering, but raises a ProgrammingError
when trying to use nulls_last
with asc
order. It appears this error is being raised as the result of an InvalidArgument
error (see Traces below).
Dependencies
- sqlalchemy = “~1.4.37”
- sqlalchemy-spanner = “~1.2.0”
We’re using Google Cloud Spanner for our database.
Original Query
Generating Method
@classmethod def get_my_models(cls, sort_by: str, order_by: str) -> List["MyModel"]: with read_session_scope(get_engine()) as session: sort_attr = getattr(MyModel, sort_by) query = ( session.query(MyModel) .filter(func.coalesce(MyModel.is_deleted, False).is_(False)) .order_by(nulls_last(getattr(sort_attr, order_by)())) ) return [MyModel.from_orm(model) for model in query.all()]
Translated Query
When called with sort_by="my_column"
and order_by="desc"
SELECT ... FROM my_model WHERE coalesce(my_model.is_deleted, false) IS false ORDER BY my_model.my_column DESC NULLS LAST
The above works fine. So as long as I want to order by descending, I’m good. Unfortunately, that’s not the requirement.
When called with sort_by="my_column"
and order_by="asc"
SELECT ... FROM my_model WHERE coalesce(my_model.is_deleted, false) IS false ORDER BY my_model.my_column ASC NULLS LAST
it looks great, but it raises:
sqlalchemy.exc.ProgrammingError: (google.cloud.spanner_dbapi.exceptions.ProgrammingError)
The error is raised in the last line of the query method during query.all()
Traces
File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2768, in all return self._iter().all() File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter result = self.session.execute( File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1712, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection return connection._execute_clauseelement( File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement ret = self._execute_context( File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context self._handle_dbapi_exception( File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception util.raise_( File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/opt/pysetup/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context self.dialect.do_execute( File "/opt/pysetup/.venv/lib/python3.8/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 1006, in do_execute cursor.execute(statement, parameters) File "/opt/pysetup/.venv/lib/python3.8/site-packages/google/cloud/spanner_dbapi/cursor.py", line 70, in wrapper return function(cursor, *args, **kwargs) File "/opt/pysetup/.venv/lib/python3.8/site-packages/google/cloud/spanner_dbapi/cursor.py", line 286, in execute raise ProgrammingError(getattr(e, "details", e)) sqlalchemy.exc.ProgrammingError: (google.cloud.spanner_dbapi.exceptions.ProgrammingError) []
self = <google.cloud.spanner_dbapi.cursor.Cursor object at 0xffffacfca220> sql = 'SELECT ... coalesce(my_model.is_deleted, @a0) IS false ORDER BY my_model.my_column ASC NULLS LAST' args = {'a0': False} @check_not_closed def execute(self, sql, args=None): """Prepares and executes a Spanner database operation. :type sql: str :param sql: A SQL query statement. :type args: list :param args: Additional parameters to supplement the SQL query. """ self._result_set = None try: if self.connection.read_only: self._handle_DQL(sql, args or None) return class_ = parse_utils.classify_stmt(sql) if class_ == parse_utils.STMT_DDL: self._batch_DDLs(sql) if self.connection.autocommit: self.connection.run_prior_DDL_statements() return # For every other operation, we've got to ensure that # any prior DDL statements were run. # self._run_prior_DDL_statements() self.connection.run_prior_DDL_statements() if class_ == parse_utils.STMT_UPDATING: sql = parse_utils.ensure_where_clause(sql) if class_ != parse_utils.STMT_INSERT: sql, args = sql_pyformat_args_to_spanner(sql, args or None) if not self.connection.autocommit: statement = Statement( sql, args, get_param_types(args or None) if class_ != parse_utils.STMT_INSERT else {}, ResultsChecksum(), class_ == parse_utils.STMT_INSERT, ) ( self._result_set, self._checksum, ) = self.connection.run_statement(statement) while True: try: self._itr = PeekIterator(self._result_set) break except Aborted: self.connection.retry_transaction() return if class_ == parse_utils.STMT_NON_UPDATING: self._handle_DQL(sql, args or None) elif class_ == parse_utils.STMT_INSERT: _helpers.handle_insert(self.connection, sql, args or None) else: self.connection.database.run_in_transaction( self._do_execute_update, sql, args or None ) except (AlreadyExists, FailedPrecondition, OutOfRange) as e: raise IntegrityError(getattr(e, "details", e)) except InvalidArgument as e: > raise ProgrammingError(getattr(e, "details", e)) E sqlalchemy.exc.ProgrammingError: (google.cloud.spanner_dbapi.exceptions.ProgrammingError) []
Advertisement
Answer
Google Cloud Spanner databases using the standard GoogleSQL dialect always sort NULL
first when the sort order is ascending, and NULL
last when the sort order is descending. The SQL dialect allows you to specify ASC NULLS FIRST
and DESC NULLS LAST
, as those correspond with the default, but not to actually change the sort order.
A workaround for this would be to use a COALESCE(my_column, <large-value>)
expression in your ORDER BY
clause. The feasibility of this workaround depends a little on the data type of the column that you are sorting. If it is for example an INT64
column, you could just use the max value for INT64
. Be aware though that such a workaround can make your query less efficient, as the ORDER BY
clause will not be able to use any index that you might have defined on the column.