remove =/!=->IN/NOT IN coercion in MSSQLCompiler.visit_binary

[Former user]

The else block in MSSQLCompiler.visit_binary (line 876 - 896) somehow changes = to IN, and <> to NOT IN. It may make sense if the right hand side is a scalar select, but the code does so even if only the left hand side is a scalar select, e.g.

will be changed to

{{{<scalar select> IN 1}}}

which is invalid.

IMHO, the whole else block can be removed, as the caller should have explicitly used IN/NOT IN when necessary. Did I miss something here?

[Mike Bayer]

The MSSQL dialect does have some helpers that date back to very early SQLA releases, like 0.2 and 0.3, and it does want to do trivial substitutions where they are appropriate. This particular substitution is not one I've dealt with before (note the MSSQL dialect was provided by volunteers). We do try to strike a balance between things that should "just work" across backends and things where we'd like users to write code that is explicitly portable to the backends they need. But I agree this one goes too far with that, if you want IN you should be asking for IN, there's no reason for it to be guessing.

In this case, clearly I can't just take the block out, that would break any number of applications that rely upon it. I'd rather not get into surgically modifying it halfway for now, so I'm going deprecate it, will be gone in 0.8, and put up a recipe to turn it off for those who need it right now. The recipe also turns of the "automatically put binds on the right side for me" feature, not sure if you're relying on that one, I'm not super thrilled about that one either but I'd likely leave that one in.

The recipe that will be up on the next doc build is:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import _BinaryExpression
from sqlalchemy.sql.compiler import SQLCompiler

@compiles(_BinaryExpression, 'mssql')
def override_legacy_binary(element, compiler, **kw):
    return SQLCompiler.visit_binary(compiler, element, **kw)

let me know if you have any serious issues with using a workaround for now. The warning is committed in c52e31b1e019fb447e0a2edb7e2c75ebe9307a95 .

[Former user]

Thank you. The recipe works great for me :)

[Mike Bayer]

0.8 branch at 5c5634c04f6c64c8c95469d4c05ed4adaf5eabe2

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)