The :meth:`.ColumnOperators.in_` operator will now coerce

values of ``None`` to :func:`.null`.
[ticket:2496]

4 files changed, 19 insertions, 0 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index e6cc169b1..259c59243 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,13 @@
     :version: 0.8.0
 
     .. change::
+        :tags: bug, sql
+        :tickets: 2496
+
+      The :meth:`.ColumnOperators.in_` operator will now coerce
+      values of ``None`` to :func:`.null`.
+
+    .. change::
         :tags: feature, sql
         :tickets: 2657
 
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index de728f77b..90837f4ab 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -2084,6 +2084,8 @@ class _DefaultColumnComparator(operators.ColumnOperators):
                     raise exc.InvalidRequestError('in() function accept'
                             's either a list of non-selectable values, '
                             'or a selectable: %r' % o)
+            elif o is None:
+                o = null()
             else:
                 o = expr._bind_param(op, o)
             args.append(o)
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index 210f8c748..52ba77310 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -171,6 +171,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                 select([t]).where(t.c.foo.in_(['x', 'y', 'z'])),
                 "SELECT sometable.foo FROM sometable WHERE sometable.foo "
                 "IN ('x', 'y', 'z')",
+            ),
+            (
+                    t.c.foo.in_([None]),
+                    "sometable.foo IN (NULL)"
             )
         ]:
             self.assert_compile(expr, compile, dialect=mxodbc_dialect)
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 45f4978ed..7215ae565 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -736,6 +736,12 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             {'param_1': 10}
         )
 
+    def test_in_28(self):
+        self.assert_compile(
+            self.table1.c.myid.in_([None]),
+            "mytable.myid IN (NULL)"
+        )
+
 class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'