NULL safe inequality comparisons in SQL Alchemy?

Question

Currently, the only way I know how to express a NULL safe != comparison in SQL Alchemy (where comparison with NULL entries evaluates as True, rather than NULL) is with:

or_(field == None, field != value)

Is there a way to express this in SQL Alchemy such that it will emit a MySQL <=> operation or a PostgreSQL IS DISTINCT FROM operation that treats NULL as just another possible value?

Additional context

This came up as a bug in a utility that converts from an XML based query language to SQL Alchemy filter expressions: http://gerrit.beaker-project.org/#/c/2776/

The specific bug was in a snippet of code that looked like this:

query = getattr(field, op)(value)

Where "field" is the relevant SQL Alchemy model column, "op" is the relevant Python attribute name for the comparison operation, and "value" is the value to be checked against. For example, in the specific case of filtering for virtual machines running on a hypervisor with a particular name, it would be equivalent to doing:

query = Hypervisor.hypervisor.__eq__("KVM")

The bug arose not due to value potentially being NULL (that doesn't happen - the value is always a string), but when comparing against rows where the column contained a NULL value and the comparison operator was __ne__.

For every operator except __ne__, the standard SQL NULL handling works fine (the comparison returns NULL, which is interpreted as the row not matching the filter, which is what we want). However, for the case of __ne__ we do want to return the rows that contain a NULL value in that column - we only want to exclude the ones where the value is set and it doesn't match the value we're comparing against.

So the code that originally looked like:

query = getattr(field, op)(value)

Now looks more like:

if op == "__ne__":
    query = or_(field == None, field != value)
else:
    query = getattr(field, op)(value)

That seems clumsy to me, so I'm asking if there is a method we can map "!=" to other than __ne__ that would give us the alternate NULL handling at the database layer rather than emulating it with the SQL Alchemy equivalent of field IS NOT NULL OR field != value (as we have started doing now).

Answer 1

There's a few ways to plug an alternate operator in there, as well as creating a custom operator, but the most public/mainstream way to get at what happens when __ne__() is invoked is at the type level:

from sqlalchemy import TypeDecorator, type_coerce, String, or_

class NullComparisons(TypeDecorator):
    impl = String

    class comparator_factory(TypeDecorator.Comparator):
        def __ne__(self, other):
            expr = type_coerce(self.expr, String)
            return or_(expr == None, expr != other)

so that will do the OR thing:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)
    data = Column(NullComparisons(50))
print(Thing.data != 'hi')

gives us:

thing.data IS NULL OR thing.data != :param_1

then for the PG/MySQL operator, what we really should have is the ability to link @compiles to operators directly. But that hook isn't present right now, so with more effort than it ideally should require, we can make a custom column element to handle it:

from sqlalchemy import TypeDecorator, type_coerce, String
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import BinaryExpression
import operator
class IsDistinctFrom(BinaryExpression):
    pass

@compiles(IsDistinctFrom, "postgresql")
def pg_is_distinct_from(element, compiler, **kw):
    return "%s IS DISTINCT FROM %s" % (
                    compiler.process(element.left, **kw),
                    compiler.process(element.right, **kw),
                )

@compiles(IsDistinctFrom, "mysql")
def mysql_is_distinct_from(element, compiler, **kw):
    return "%s <=> %s" % (
                    compiler.process(element.left, **kw),
                    compiler.process(element.right, **kw),
                )

class AdvancedNullComparisons(TypeDecorator):
    impl = String

    class comparator_factory(TypeDecorator.Comparator):
        def __ne__(self, other):
            expr = type_coerce(self.expr, String)
            # this step coerces a literal into a SQL expression,
            # this can be done without the private API here but the private
            # function does the most thorough job, this could also be made
            # public
            other = self._check_literal(expr, operator.ne, other)
            return IsDistinctFrom(self.expr, other, operator.ne)

then we can try that out:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)
    data = Column(AdvancedNullComparisons(50))

from sqlalchemy.dialects import postgresql, mysql
print(Thing.data != 'hi').compile(dialect=postgresql.dialect())
print(Thing.data != 'hi').compile(dialect=mysql.dialect())

gives us:

thing.data IS DISTINCT FROM %(param_1)s
thing.data <=> %s