Add IS (NOT) DISTINCT FROM operators

None / True / False render as literals.
For SQLite, "IS" is used as SQLite lacks
"IS DISTINCT FROM" but its "IS" operator acts
this way for NULL.

Doctext-author: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I9227b81f7207b42627a0349d14d40b46aa756cce
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/248

8 files changed, 166 insertions, 2 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 709eaab5e..789a241d0 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -134,6 +134,17 @@
             :ref:`change_3653`
 
     .. change::
+        :tags: feature, sql
+
+        New :meth:`.ColumnOperators.is_distinct_from` and
+        :meth:`.ColumnOperators.isnot_distinct_from` operators; pull request
+        courtesy Sebastian Bank.
+
+        .. seealso::
+
+            :ref:`change_is_distinct_from`
+
+    .. change::
         :tags: bug, orm
         :tickets: 3488
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index b217f0420..d9f48fcb1 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -1137,6 +1137,30 @@ will not have much impact on the behavior of the column during an INSERT.
 
 :ticket:`3216`
 
+.. _change_is_distinct_from:
+
+Support for IS DISTINCT FROM and IS NOT DISTINCT FROM
+------------------------------------------------------
+
+New operators :meth:`.ColumnOperators.is_distinct_from` and
+:meth:`.ColumnOperators.isnot_distinct_from` allow the IS DISTINCT
+FROM and IS NOT DISTINCT FROM sql operation::
+
+    >>> print column('x').is_distinct_from(None)
+    x IS DISTINCT FROM NULL
+
+Handling is provided for NULL, True and False::
+
+    >>> print column('x').isnot_distinct_from(False)
+    x IS NOT DISTINCT FROM false
+
+For SQLite, which doesn't have this operator, "IS" / "IS NOT" is rendered,
+which on SQLite works for NULL unlike other backends::
+
+    >>> from sqlalchemy.dialects import sqlite
+    >>> print column('x').is_distinct_from(None).compile(dialect=sqlite.dialect())
+    x IS NOT NULL
+
 .. _change_1957:
 
 Core and ORM support for FULL OUTER JOIN
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 5109ff3a7..07e4592ba 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -849,6 +849,14 @@ class SQLiteCompiler(compiler.SQLCompiler):
         # sqlite has no "FOR UPDATE" AFAICT
         return ''
 
+    def visit_is_distinct_from_binary(self, binary, operator, **kw):
+        return "%s IS NOT %s" % (self.process(binary.left),
+                                 self.process(binary.right))
+
+    def visit_isnot_distinct_from_binary(self, binary, operator, **kw):
+        return "%s IS %s" % (self.process(binary.left),
+                             self.process(binary.right))
+
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 3d2f02006..144f2aa47 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -81,6 +81,8 @@ OPERATORS = {
     operators.gt: ' > ',
     operators.ge: ' >= ',
     operators.eq: ' = ',
+    operators.is_distinct_from: ' IS DISTINCT FROM ',
+    operators.isnot_distinct_from: ' IS NOT DISTINCT FROM ',
     operators.concat_op: ' || ',
     operators.match_op: ' MATCH ',
     operators.notmatch_op: ' NOT MATCH ',
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 1bb1c344c..7630a9821 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -39,6 +39,12 @@ def _boolean_compare(expr, op, obj, negate=None, reverse=False,
                                     op,
                                     type_=result_type,
                                     negate=negate, modifiers=kwargs)
+        elif op in (operators.is_distinct_from, operators.isnot_distinct_from):
+            return BinaryExpression(expr,
+                                    _literal_as_text(obj),
+                                    op,
+                                    type_=result_type,
+                                    negate=negate, modifiers=kwargs)
         else:
             # all other None/True/False uses IS, IS NOT
             if op in (operators.eq, operators.is_):
@@ -51,8 +57,9 @@ def _boolean_compare(expr, op, obj, negate=None, reverse=False,
                                         negate=operators.is_)
             else:
                 raise exc.ArgumentError(
-                    "Only '=', '!=', 'is_()', 'isnot()' operators can "
-                    "be used with None/True/False")
+                    "Only '=', '!=', 'is_()', 'isnot()', "
+                    "'is_distinct_from()', 'isnot_distinct_from()' "
+                    "operators can be used with None/True/False")
     else:
         obj = _check_literal(expr, op, obj)
 
@@ -249,6 +256,8 @@ operator_lookup = {
     "gt": (_boolean_compare, operators.le),
     "ge": (_boolean_compare, operators.lt),
     "eq": (_boolean_compare, operators.ne),
+    "is_distinct_from": (_boolean_compare, operators.isnot_distinct_from),
+    "isnot_distinct_from": (_boolean_compare, operators.is_distinct_from),
     "like_op": (_boolean_compare, operators.notlike_op),
     "ilike_op": (_boolean_compare, operators.notilike_op),
     "notlike_op": (_boolean_compare, operators.like_op),
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 80f08a97c..bf470710d 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -311,6 +311,28 @@ class ColumnOperators(Operators):
         """
         return self.operate(ne, other)
 
+    def is_distinct_from(self, other):
+        """Implement the ``IS DISTINCT FROM`` operator.
+
+        Renders "a IS DISTINCT FROM b" on most platforms;
+        on some such as SQLite may render "a IS NOT b".
+
+        .. versionadded:: 1.1
+
+        """
+        return self.operate(is_distinct_from, other)
+
+    def isnot_distinct_from(self, other):
+        """Implement the ``IS NOT DISTINCT FROM`` operator.
+
+        Renders "a IS NOT DISTINCT FROM b" on most platforms;
+        on some such as SQLite may render "a IS b".
+
+        .. versionadded:: 1.1
+
+        """
+        return self.operate(isnot_distinct_from, other)
+
     def __gt__(self, other):
         """Implement the ``>`` operator.
 
@@ -722,6 +744,15 @@ def istrue(a):
 def isfalse(a):
     raise NotImplementedError()
 
+
+def is_distinct_from(a, b):
+    return a.is_distinct_from(b)
+
+
+def isnot_distinct_from(a, b):
+    return a.isnot_distinct_from(b)
+
+
 def is_(a, b):
     return a.is_(b)
 
@@ -931,6 +962,8 @@ _PRECEDENCE = {
 
     eq: 5,
     ne: 5,
+    is_distinct_from: 5,
+    isnot_distinct_from: 5,
     gt: 5,
     lt: 5,
     ge: 5,
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 697f21585..473f4f462 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -670,6 +670,17 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             "1"
         )
 
+    def test_is_distinct_from(self):
+        self.assert_compile(
+            sql.column('x').is_distinct_from(None),
+            "x IS NOT NULL"
+        )
+
+        self.assert_compile(
+            sql.column('x').isnot_distinct_from(False),
+            "x IS 0"
+        )
+
     def test_localtime(self):
         self.assert_compile(
             func.localtimestamp(),
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 86286a9a3..5712d8f99 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -99,6 +99,18 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
     def test_notequals_true(self):
         self._do_operate_test(operators.ne, True)
 
+    def test_is_distinct_from_true(self):
+        self._do_operate_test(operators.is_distinct_from, True)
+
+    def test_is_distinct_from_false(self):
+        self._do_operate_test(operators.is_distinct_from, False)
+
+    def test_is_distinct_from_null(self):
+        self._do_operate_test(operators.is_distinct_from, None)
+
+    def test_isnot_distinct_from_true(self):
+        self._do_operate_test(operators.isnot_distinct_from, True)
+
     def test_is_true(self):
         self._do_operate_test(operators.is_, True)
 
@@ -1527,6 +1539,60 @@ class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         self.assert_compile(f / (f / (f - f)), "f / (f / (f - f))")
 
 
+class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    table1 = table('mytable',
+                   column('myid', Integer),
+                   )
+
+    def test_is_distinct_from(self):
+        self.assert_compile(self.table1.c.myid.is_distinct_from(1),
+                            "mytable.myid IS DISTINCT FROM :myid_1")
+
+    def test_is_distinct_from_sqlite(self):
+        self.assert_compile(self.table1.c.myid.is_distinct_from(1),
+                            "mytable.myid IS NOT ?",
+                            dialect=sqlite.dialect())
+
+    def test_is_distinct_from_postgresql(self):
+        self.assert_compile(self.table1.c.myid.is_distinct_from(1),
+                            "mytable.myid IS DISTINCT FROM %(myid_1)s",
+                            dialect=postgresql.dialect())
+
+    def test_not_is_distinct_from(self):
+        self.assert_compile(~self.table1.c.myid.is_distinct_from(1),
+                            "mytable.myid IS NOT DISTINCT FROM :myid_1")
+
+    def test_not_is_distinct_from_postgresql(self):
+        self.assert_compile(~self.table1.c.myid.is_distinct_from(1),
+                            "mytable.myid IS NOT DISTINCT FROM %(myid_1)s",
+                            dialect=postgresql.dialect())
+
+    def test_isnot_distinct_from(self):
+        self.assert_compile(self.table1.c.myid.isnot_distinct_from(1),
+                            "mytable.myid IS NOT DISTINCT FROM :myid_1")
+
+    def test_isnot_distinct_from_sqlite(self):
+        self.assert_compile(self.table1.c.myid.isnot_distinct_from(1),
+                            "mytable.myid IS ?",
+                            dialect=sqlite.dialect())
+
+    def test_isnot_distinct_from_postgresql(self):
+        self.assert_compile(self.table1.c.myid.isnot_distinct_from(1),
+                            "mytable.myid IS NOT DISTINCT FROM %(myid_1)s",
+                            dialect=postgresql.dialect())
+
+    def test_not_isnot_distinct_from(self):
+        self.assert_compile(~self.table1.c.myid.isnot_distinct_from(1),
+                            "mytable.myid IS DISTINCT FROM :myid_1")
+
+    def test_not_isnot_distinct_from_postgresql(self):
+        self.assert_compile(~self.table1.c.myid.isnot_distinct_from(1),
+                            "mytable.myid IS DISTINCT FROM %(myid_1)s",
+                            dialect=postgresql.dialect())
+
+
 class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'