Corrected a lot of mssql limit / offset issues. Also ensured that mssql uses the IN / NOT IN syntax when using a binary expression with a subquery.

2 files changed, 16 insertions, 26 deletions

diff --git a/test/dialect/mssql.py b/test/dialect/mssql.py
index 4708cc28c..26fc75243 100755
--- a/test/dialect/mssql.py
+++ b/test/dialect/mssql.py
@@ -20,6 +20,16 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         t = table('sometable', column('somecolumn'))
         self.assert_compile(t.update(t.c.somecolumn==7), "UPDATE sometable SET somecolumn=:somecolumn WHERE sometable.somecolumn = :somecolumn_1", dict(somecolumn=10))
 
+    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.
+
+        """
+
+        t = table('sometable', column('somecolumn'))
+        self.assert_compile(t.select().where(t.c.somecolumn==t.select()), "SELECT sometable.somecolumn FROM sometable WHERE sometable.somecolumn IN (SELECT sometable.somecolumn FROM sometable)")
+        self.assert_compile(t.select().where(t.c.somecolumn!=t.select()), "SELECT sometable.somecolumn FROM sometable WHERE sometable.somecolumn NOT IN (SELECT sometable.somecolumn FROM sometable)")
+
     def test_count(self):
         t = table('sometable', column('somecolumn'))
         self.assert_compile(t.count(), "SELECT count(sometable.somecolumn) AS tbl_row_count FROM sometable")
@@ -197,28 +207,6 @@ class QueryTest(TestBase):
         finally:
             table.drop()
 
-    def test_select_limit_nooffset(self):
-        metadata = MetaData(testing.db)
-
-        users = Table('query_users', metadata,
-            Column('user_id', INT, primary_key = True),
-            Column('user_name', VARCHAR(20)),
-        )
-        addresses = Table('query_addresses', metadata,
-            Column('address_id', Integer, primary_key=True),
-            Column('user_id', Integer, ForeignKey('query_users.user_id')),
-            Column('address', String(30)))
-        metadata.create_all()
-
-        try:
-            try:
-                r = users.select(limit=3, offset=2,
-                                 order_by=[users.c.user_id]).execute().fetchall()
-                assert False # InvalidRequestError should have been raised
-            except exc.InvalidRequestError:
-                pass
-        finally:
-            metadata.drop_all()
 
 class Foo(object):
     def __init__(self, **kw):
diff --git a/test/sql/query.py b/test/sql/query.py
index 3118aef64..ac11b4452 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -648,9 +648,10 @@ class LimitTest(TestBase):
         r = users.select(limit=3, order_by=[users.c.user_id]).execute().fetchall()
         self.assert_(r == [(1, 'john'), (2, 'jack'), (3, 'ed')], repr(r))
 
-    @testing.crashes('mssql', 'FIXME: guessing')
     @testing.fails_on('maxdb')
     def test_select_limit_offset(self):
+        """Test the interaction between limit and offset"""
+
         r = users.select(limit=3, offset=2, order_by=[users.c.user_id]).execute().fetchall()
         self.assert_(r==[(3, 'ed'), (4, 'wendy'), (5, 'laura')])
         r = users.select(offset=5, order_by=[users.c.user_id]).execute().fetchall()
@@ -659,14 +660,15 @@ class LimitTest(TestBase):
     def test_select_distinct_limit(self):
         """Test the interaction between limit and distinct"""
 
-        r = sorted([x[0] for x in select([addresses.c.address]).distinct().limit(3).execute().fetchall()])
+        r = sorted([x[0] for x in select([addresses.c.address]).distinct().limit(3).order_by(addresses.c.address).execute().fetchall()])
         self.assert_(len(r) == 3, repr(r))
         self.assert_(r[0] != r[1] and r[1] != r[2], repr(r))
 
+    @testing.fails_on('mssql')
     def test_select_distinct_offset(self):
-        """Test the interaction between limit and offset"""
+        """Test the interaction between distinct and offset"""
 
-        r = sorted([x[0] for x in select([addresses.c.address]).distinct().offset(1).execute().fetchall()])
+        r = sorted([x[0] for x in select([addresses.c.address]).distinct().offset(1).order_by(addresses.c.address).execute().fetchall()])
         self.assert_(len(r) == 4, repr(r))
         self.assert_(r[0] != r[1] and r[1] != r[2] and r[2] != [3], repr(r))