Can't do `xxx.in_([None])` with MSSQLStrictCompiler

[Sok Ann Yap]

With MSSQLStrictCompiler, adding a xxx.in_([None](None)) filter to a query will cause error:

This is caused by the `if bindparam.value is None:` checking in source:lib/sqlalchemy/sql/compiler.py. If I remove the checking, then the query works fine. Is it safe to remove the checking?

Also, I am using mssql+pyodbc with MSSQLStrictCompiler, which I am not sure is a supported use case.

[Mike Bayer]

There is absolutely no reason you need the MSSQLStrictCompiler with pyodbc, it was designed for mxodbc which uses a particular API of ODBC that allows binds in far fewer places.

The bug here is that in_() is not expecting "None" at all, which would normally be coerced to null().

Workaround is to, well first off don't bother with the "strict" compiler, no idea what purpose that is solving, then for now just pass null() instead of None.

from sqlalchemy.dialects.mssql.base import MSSQLStrictCompiler, dialect, MSSQLCompiler
from sqlalchemy.sql import column, select, null

expr = select([1](1)).where(column("x").in_([null()](null())))
comp = MSSQLStrictCompiler(dialect(), expr)
print comp

patch:

--- a/lib/sqlalchemy/sql/expression.py  Fri Jun 01 16:31:10 2012 -0400
+++ b/lib/sqlalchemy/sql/expression.py  Mon Jun 04 23:12:13 2012 -0400
@@ -1971,6 +1971,8 @@
                     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 = self._bind_param(op, o)
             args.append(o)

[Sok Ann Yap]

Well, I kind of abuse column_property, resulting in queries with 100+ bind parameters in the columns clause, which makes SQL Server Profiler output rather difficult to read. So using the strict compiler is more of a cosmetic fix for something I regret doing but too late to be changed.

Patch works. Thanks :)

[Sok Ann Yap]

On further thought, setting ansi_bind_rules to True looks like a more fitting workaround for me, as I do want to have IN clause and NOT IN clause to be parameterized.

[Mike Bayer]

cb19f227f31701322f398b5bdd77427dfc81da0d

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)