Commits

Mike Bayer  committed 2c9f0de

- 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]

  • Participants
  • Parent commits 1a0ced6

Comments (0)

Files changed (6)

     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]
 

File lib/sqlalchemy/dialects/mssql/base.py

 
 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
 ------------
 
                     )
                ):
                 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),

File test/dialect/test_mssql.py

         ]:
             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.
                             '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)')
                             '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 '
                             '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 '
                             '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 '

File test/orm/inheritance/test_query.py

             # 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()

File test/orm/test_eager_relations.py

 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
             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()

File test/orm/test_query.py

                             '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