SQLAlchemy order_by ASC using nulls_last raises ProgrammingError (Google Spanner DB)

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) []

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.