- The behavior of =/!= when comparing a scalar select

    to a value will no longer produce IN/NOT IN as of 0.8;
    this behavior is a little too heavy handed (use in_() if
    you want to emit IN) and now emits a deprecation warning.
    To get the 0.8 behavior immediately and remove the warning,
    a compiler recipe is given at
    http://www.sqlalchemy.org/docs/07/dialects/mssql.html#scalar-select-comparisons
    to override the behavior of visit_binary().
    [ticket:2277]

6 files changed, 50 insertions, 9 deletions

diff --git a/CHANGES b/CHANGES
index 1f5cd15bd..56d9140d7 100644
--- a/CHANGES
+++ b/CHANGES
@@ -206,6 +206,16 @@ CHANGES
     on OSX, MemoryErrors abound and just plain broken 
     unicode support.   [ticket:2273]
 
+  - The behavior of =/!= when comparing a scalar select
+    to a value will no longer produce IN/NOT IN as of 0.8;
+    this behavior is a little too heavy handed (use in_() if
+    you want to emit IN) and now emits a deprecation warning.   
+    To get the 0.8 behavior immediately and remove the warning, 
+    a compiler recipe is given at 
+    http://www.sqlalchemy.org/docs/07/dialects/mssql.html#scalar-select-comparisons
+    to override the behavior of visit_binary().
+    [ticket:2277]
+
   - "0" is accepted as an argument for limit() which
     will produce "TOP 0". [ticket:2222]
 
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 981ffc238..c7e1a540a 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -157,7 +157,30 @@ following ALTER DATABASE commands executed at the SQL prompt::
 
 Background on SQL Server snapshot isolation is available at
 http://msdn.microsoft.com/en-us/library/ms175095.aspx.
-  
+
+Scalar Select Comparisons
+-------------------------
+
+The MSSQL dialect contains a legacy behavior whereby comparing
+a scalar select to a value using the ``=`` or ``!=`` operator
+will resolve to IN or NOT IN, respectively.  This behavior is 
+deprecated and will be removed in 0.8 - the ``s.in_()``/``~s.in_()`` operators 
+should be used when IN/NOT IN are desired.
+
+For the time being, the existing behavior prevents a comparison
+between scalar select and another value that actually wants to use ``=``.  
+To remove this behavior in a forwards-compatible way, apply this
+compilation rule by placing the following code at the module import
+level::
+
+    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)
+
 Known Issues
 ------------
 
@@ -889,6 +912,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
                     )
                ):
                 op = binary.operator == operator.eq and "IN" or "NOT IN"
+                util.warn_deprecated("Comparing a scalar select using ``=``/``!=`` will "
+                                    "no longer produce IN/NOT IN in 0.8.  To remove this "
+                                    "behavior immediately, use the recipe at "
+                        "http://www.sqlalchemy.org/docs/07/dialects/mssql.html#scalar-select-comparisons")
                 return self.process(
                         expression._BinaryExpression(binary.left,
                                                      binary.right, op),
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index 4cce395b8..f336f5f3b 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -63,6 +63,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         ]:
             self.assert_compile(expr, compile, dialect=mxodbc_dialect)
 
+    @testing.uses_deprecated
     def test_in_with_subqueries(self):
         """Test that when using subqueries in a binary expression
         the == and != are changed to IN and NOT IN respectively.
@@ -117,7 +118,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'DELETE FROM paj.test WHERE paj.test.id = '
                             ':id_1')
         s = select([tbl.c.id]).where(tbl.c.id == 1)
-        self.assert_compile(tbl.delete().where(tbl.c.id == s),
+        self.assert_compile(tbl.delete().where(tbl.c.id.in_(s)),
                             'DELETE FROM paj.test WHERE paj.test.id IN '
                             '(SELECT test_1.id FROM paj.test AS test_1 '
                             'WHERE test_1.id = :id_1)')
@@ -130,7 +131,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'DELETE FROM banana.paj.test WHERE '
                             'banana.paj.test.id = :id_1')
         s = select([tbl.c.id]).where(tbl.c.id == 1)
-        self.assert_compile(tbl.delete().where(tbl.c.id == s),
+        self.assert_compile(tbl.delete().where(tbl.c.id.in_(s)),
                             'DELETE FROM banana.paj.test WHERE '
                             'banana.paj.test.id IN (SELECT test_1.id '
                             'FROM banana.paj.test AS test_1 WHERE '
@@ -144,7 +145,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'DELETE FROM [banana split].paj.test WHERE '
                             '[banana split].paj.test.id = :id_1')
         s = select([tbl.c.id]).where(tbl.c.id == 1)
-        self.assert_compile(tbl.delete().where(tbl.c.id == s),
+        self.assert_compile(tbl.delete().where(tbl.c.id.in_(s)),
                             'DELETE FROM [banana split].paj.test WHERE '
                             '[banana split].paj.test.id IN (SELECT '
                             'test_1.id FROM [banana split].paj.test AS '
@@ -160,7 +161,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'space].test WHERE [banana split].[paj '
                             'with a space].test.id = :id_1')
         s = select([tbl.c.id]).where(tbl.c.id == 1)
-        self.assert_compile(tbl.delete().where(tbl.c.id == s),
+        self.assert_compile(tbl.delete().where(tbl.c.id.in_(s)),
                             'DELETE FROM [banana split].[paj with a '
                             'space].test WHERE [banana split].[paj '
                             'with a space].test.id IN (SELECT '
diff --git a/test/orm/inheritance/test_query.py b/test/orm/inheritance/test_query.py
index 94dde71f8..935af12c8 100644
--- a/test/orm/inheritance/test_query.py
+++ b/test/orm/inheritance/test_query.py
@@ -753,7 +753,7 @@ def _produce_test(select_type):
             # the subquery and usually results in recursion overflow errors within the adaption.
             subq = sess.query(engineers.c.person_id).filter(Engineer.primary_language=='java').statement.as_scalar()
 
-            eq_(sess.query(Person).filter(Person.person_id==subq).one(), e1)
+            eq_(sess.query(Person).filter(Person.person_id.in_(subq)).one(), e1)
 
         def test_mixed_entities(self):
             sess = create_session()
diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py
index 434719486..d1e4535bd 100644
--- a/test/orm/test_eager_relations.py
+++ b/test/orm/test_eager_relations.py
@@ -10,6 +10,7 @@ from sqlalchemy import Integer, String, Date, ForeignKey, and_, select, \
 from test.lib.schema import Table, Column
 from sqlalchemy.orm import mapper, relationship, create_session, \
     lazyload, aliased, column_property
+from sqlalchemy.sql import operators
 from test.lib.testing import eq_, assert_raises, \
     assert_raises_message
 from test.lib.assertsql import CompiledSQL
@@ -2539,18 +2540,20 @@ class CorrelatedSubqueryTest(fixtures.MappedTest):
             stuff_view = select([salias.c.id]).where(salias.c.user_id==users.c.id).\
                                     correlate(users).order_by(salias.c.date.desc()).limit(1)
 
+        operator = operators.in_op
         if labeled == 'label':
             stuff_view = stuff_view.label('foo')
+            operator = operators.eq
         elif labeled == 'scalar':
             stuff_view = stuff_view.as_scalar()
 
         if ondate:
             mapper(User, users, properties={
-                'stuff':relationship(Stuff, primaryjoin=and_(users.c.id==stuff.c.user_id, stuff.c.date==stuff_view))
+                'stuff':relationship(Stuff, primaryjoin=and_(users.c.id==stuff.c.user_id, operator(stuff.c.date, stuff_view)))
             })
         else:
             mapper(User, users, properties={
-                'stuff':relationship(Stuff, primaryjoin=and_(users.c.id==stuff.c.user_id, stuff.c.id==stuff_view))
+                'stuff':relationship(Stuff, primaryjoin=and_(users.c.id==stuff.c.user_id, operator(stuff.c.id, stuff_view)))
             })
 
         sess = create_session()
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 39a9b5137..fc3bb4422 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -836,7 +836,7 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL):
                             'IN (SELECT users.id FROM users WHERE '
                             'users.id = :id_1)')
 
-
+    @testing.fails_on('mssql', "mssql doesn't allow col = <subquery>, sqla deprecated workaround")
     def test_param_transfer(self):
         User = self.classes.User