Deprecate FromClause.count()

count() here is misleading in that it not only
counts from an arbitrary column in the table, it also
does not make accommodations for DISTINCT, JOIN, etc.
as the ORM-level function does.  Core should not be
attempting to provide a function like this.

Change-Id: I9916fc51ef744389a92c54660ab08e9695b8afc2
Fixes: #3724

21 files changed, 193 insertions, 141 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 87ca1cb31..7a41731bf 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -20,6 +20,14 @@
 
     .. change::
         :tags: bug, sql
+        :tickets: 3724
+
+        :meth:`.FromClause.count` is pending deprecation for 1.1.  This function
+        makes use of an arbitrary column in the table and is not reliable;
+        for Core use, ``func.count()`` should be preferred.
+
+    .. change::
+        :tags: bug, sql
         :tickets: 3722
 
         Fixed bug in :class:`.CTE` structure which would cause it to not
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 8d20ef257..923148232 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,14 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: bug, sql
+        :tickets: 3724
+
+        :meth:`.FromClause.count` is deprecated.  This function makes use of
+        an arbitrary column in the table and is not reliable; for Core use,
+        ``func.count()`` should be preferred.
+
+    .. change::
         :tags: feature, postgresql
         :pullreq: bitbucket:84
 
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index f75613e35..ac955a60f 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -308,10 +308,34 @@ class FromClause(Selectable):
 
     _memoized_property = util.group_expirable_memoized_property(["_columns"])
 
+    @util.deprecated(
+        '1.1',
+        message="``FromClause.count()`` is deprecated. Counting "
+        "rows requires that the correct column expression and "
+        "accommodations for joins, DISTINCT, etc. must be made, "
+        "otherwise results may not be what's expected. "
+        "Please use an appropriate ``func.count()`` expression "
+        "directly.")
     @util.dependencies("sqlalchemy.sql.functions")
     def count(self, functions, whereclause=None, **params):
         """return a SELECT COUNT generated against this
-        :class:`.FromClause`."""
+        :class:`.FromClause`.
+
+        The function generates COUNT against the
+        first column in the primary key of the table, or against
+        the first column in the table overall.   Explicit use of
+        ``func.count()`` should be preferred::
+
+            row_count = conn.scalar(
+                select([func.count('*')]).select_from(table)
+            )
+
+
+        .. seealso::
+
+            :data:`.func`
+
+        """
 
         if self.primary_key:
             col = list(self.primary_key)[0]
@@ -1610,21 +1634,6 @@ class TableClause(Immutable, FromClause):
         else:
             return []
 
-    @util.dependencies("sqlalchemy.sql.functions")
-    def count(self, functions, whereclause=None, **params):
-        """return a SELECT COUNT generated against this
-        :class:`.TableClause`."""
-
-        if self.primary_key:
-            col = list(self.primary_key)[0]
-        else:
-            col = list(self.columns)[0]
-        return Select(
-            [functions.func.count(col).label('tbl_row_count')],
-            whereclause,
-            from_obj=[self],
-            **params)
-
     @util.dependencies("sqlalchemy.sql.dml")
     def insert(self, dml, values=None, inline=False, **kwargs):
         """Generate an :func:`.insert` construct against this
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 92ff18069..0cf84c7d5 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -9,7 +9,7 @@ from sqlalchemy import sql
 from sqlalchemy import Integer, String, Table, Column, select, MetaData,\
     update, delete, insert, extract, union, func, PrimaryKeyConstraint, \
     UniqueConstraint, Index, Sequence, literal
-
+from sqlalchemy import testing
 
 class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = mssql.dialect()
@@ -244,6 +244,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             '(SELECT sometable.somecolumn FROM '
                             'sometable)')
 
+    @testing.uses_deprecated
     def test_count(self):
         t = table('sometable', column('somecolumn'))
         self.assert_compile(t.count(),
diff --git a/test/dialect/postgresql/test_query.py b/test/dialect/postgresql/test_query.py
index c031e43de..538312a6a 100644
--- a/test/dialect/postgresql/test_query.py
+++ b/test/dialect/postgresql/test_query.py
@@ -689,28 +689,28 @@ class ServerSideCursorsTest(fixtures.TestBase, AssertsExecutionResults):
         result = engine.execute(s)
         assert result.cursor.name
 
+    @testing.provide_metadata
     def test_roundtrip(self):
+        md = self.metadata
+
         engine = self._fixture(True)
-        test_table = Table('test_table', MetaData(engine),
+        test_table = Table('test_table', md,
                            Column('id', Integer, primary_key=True),
                            Column('data', String(50)))
         test_table.create(checkfirst=True)
-        try:
-            test_table.insert().execute(data='data1')
-            nextid = engine.execute(Sequence('test_table_id_seq'))
-            test_table.insert().execute(id=nextid, data='data2')
-            eq_(test_table.select().execute().fetchall(), [(1, 'data1'
-                                                            ), (2, 'data2')])
-            test_table.update().where(
-                test_table.c.id == 2).values(
-                data=test_table.c.data +
-                ' updated').execute()
-            eq_(test_table.select().execute().fetchall(),
-                [(1, 'data1'), (2, 'data2 updated')])
-            test_table.delete().execute()
-            eq_(test_table.count().scalar(), 0)
-        finally:
-            test_table.drop(checkfirst=True)
+        test_table.insert().execute(data='data1')
+        nextid = engine.execute(Sequence('test_table_id_seq'))
+        test_table.insert().execute(id=nextid, data='data2')
+        eq_(test_table.select().execute().fetchall(), [(1, 'data1'
+                                                        ), (2, 'data2')])
+        test_table.update().where(
+            test_table.c.id == 2).values(
+            data=test_table.c.data +
+            ' updated').execute()
+        eq_(test_table.select().execute().fetchall(),
+            [(1, 'data1'), (2, 'data2 updated')])
+        test_table.delete().execute()
+        eq_(select([func.count('*')]).select_from(test_table).scalar(), 0)
 
 
 class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py
index b1c8673d1..8e553307f 100644
--- a/test/engine/test_execute.py
+++ b/test/engine/test_execute.py
@@ -599,7 +599,8 @@ class ConvenienceExecuteTest(fixtures.TablesTest):
 
     def _assert_no_data(self):
         eq_(
-            testing.db.scalar(self.table.count()), 0
+            testing.db.scalar(
+                select([func.count('*')]).select_from(self.table)), 0
         )
 
     def _assert_fn(self, x, value=None):
diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py
index 3717fafa0..42959a6e3 100644
--- a/test/orm/inheritance/test_basic.py
+++ b/test/orm/inheritance/test_basic.py
@@ -1104,7 +1104,7 @@ class FlushTest(fixtures.MappedTest):
         sess.add(a)
         sess.flush()
 
-        assert user_roles.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(user_roles).scalar(), 1)
 
     def test_two(self):
         admins, users, roles, user_roles = (self.tables.admins,
@@ -1146,7 +1146,7 @@ class FlushTest(fixtures.MappedTest):
 
         a.password = 'sadmin'
         sess.flush()
-        assert user_roles.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(user_roles).scalar(), 1)
 
 
 class PassiveDeletesTest(fixtures.MappedTest):
diff --git a/test/orm/inheritance/test_poly_persistence.py b/test/orm/inheritance/test_poly_persistence.py
index 361377de8..9a89a3970 100644
--- a/test/orm/inheritance/test_poly_persistence.py
+++ b/test/orm/inheritance/test_poly_persistence.py
@@ -335,7 +335,7 @@ def _generate_round_trip_test(include_base, lazy_relationship,
         session.delete(c)
         session.flush()
 
-        eq_(people.count().scalar(), 0)
+        eq_(select([func.count('*')]).select_from(people).scalar(), 0)
 
     test_roundtrip = function_named(
         test_roundtrip, "test_%s%s%s_%s" % (
diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py
index 79278ba3e..5e810bb3c 100644
--- a/test/orm/inheritance/test_polymorphic_rel.py
+++ b/test/orm/inheritance/test_polymorphic_rel.py
@@ -1,4 +1,4 @@
-from sqlalchemy import func, desc
+from sqlalchemy import func, desc, select
 from sqlalchemy.orm import interfaces, create_session, joinedload, joinedload_all, \
     subqueryload, subqueryload_all, aliased,\
     class_mapper, with_polymorphic
@@ -86,11 +86,13 @@ class _PolymorphicTestBase(object):
                 all_employees[1:3])
         self.assert_sql_count(testing.db, go, 3)
 
-        eq_(sess.query(Person).with_polymorphic('*')
+        eq_(
+            select([func.count('*')]).select_from(
+                sess.query(Person).with_polymorphic('*')
                 .options(joinedload(Engineer.machines))
-                .limit(2).offset(1).with_labels()
-                .subquery().count().scalar(),
-            2)
+                .limit(2).offset(1).with_labels().subquery()
+            ).scalar(),
+        2)
 
     def test_get_one(self):
         """
diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py
index 01167b23e..379d8f7e4 100644
--- a/test/orm/inheritance/test_relationship.py
+++ b/test/orm/inheritance/test_relationship.py
@@ -2,7 +2,7 @@ from sqlalchemy.orm import create_session, relationship, mapper, \
     contains_eager, joinedload, subqueryload, subqueryload_all,\
     Session, aliased, with_polymorphic, joinedload_all
 
-from sqlalchemy import Integer, String, ForeignKey
+from sqlalchemy import Integer, String, ForeignKey, select, func
 from sqlalchemy.engine import default
 
 from sqlalchemy.testing import AssertsCompiledSQL, fixtures
@@ -587,7 +587,12 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL):
         )
 
         # another way to check
-        assert q.limit(1).with_labels().subquery().count().scalar() == 1
+        eq_(
+            select([func.count('*')]).select_from(
+                q.limit(1).with_labels().subquery()
+            ).scalar(),
+            1
+        )
         assert q.first() is c1
 
     def test_subquery_load(self):
diff --git a/test/orm/test_association.py b/test/orm/test_association.py
index bcd2131fb..f02064a35 100644
--- a/test/orm/test_association.py
+++ b/test/orm/test_association.py
@@ -1,6 +1,6 @@
 
 from sqlalchemy import testing
-from sqlalchemy import Integer, String, ForeignKey
+from sqlalchemy import Integer, String, ForeignKey, func, select
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy.orm import mapper, relationship, create_session
 from sqlalchemy.testing import fixtures
@@ -153,11 +153,11 @@ class AssociationTest(fixtures.MappedTest):
         item2.keywords.append(KeywordAssociation(Keyword('green'), 'green_assoc'))
         sess.add_all((item1, item2))
         sess.flush()
-        eq_(item_keywords.count().scalar(), 3)
+        eq_(select([func.count('*')]).select_from(item_keywords).scalar(), 3)
 
         sess.delete(item1)
         sess.delete(item2)
         sess.flush()
-        eq_(item_keywords.count().scalar(), 0)
+        eq_(select([func.count('*')]).select_from(item_keywords).scalar(), 0)
 
 
diff --git a/test/orm/test_cascade.py b/test/orm/test_cascade.py
index f104ee34c..2522ab918 100644
--- a/test/orm/test_cascade.py
+++ b/test/orm/test_cascade.py
@@ -1,8 +1,8 @@
 import copy
 
 from sqlalchemy.testing import assert_raises, assert_raises_message
-from sqlalchemy import Integer, String, ForeignKey, Sequence, \
-    exc as sa_exc, util
+from sqlalchemy import Integer, String, ForeignKey, \
+    exc as sa_exc, util, select, func
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy.orm import mapper, relationship, create_session, \
     sessionmaker, class_mapper, backref, Session, util as orm_util,\
@@ -284,8 +284,8 @@ class O2MCascadeDeleteOrphanTest(fixtures.MappedTest):
 
         sess.delete(u)
         sess.flush()
-        assert users.count().scalar() == 0
-        assert orders.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(users).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 0)
 
     def test_delete_unloaded_collections(self):
         """Unloaded collections are still included in a delete-cascade
@@ -303,16 +303,16 @@ class O2MCascadeDeleteOrphanTest(fixtures.MappedTest):
         sess.add(u)
         sess.flush()
         sess.expunge_all()
-        assert addresses.count().scalar() == 2
-        assert users.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(addresses).scalar(), 2)
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
 
         u = sess.query(User).get(u.id)
 
         assert 'addresses' not in u.__dict__
         sess.delete(u)
         sess.flush()
-        assert addresses.count().scalar() == 0
-        assert users.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(addresses).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(users).scalar(), 0)
 
     def test_cascades_onlycollection(self):
         """Cascade only reaches instances that are still part of the
@@ -342,8 +342,8 @@ class O2MCascadeDeleteOrphanTest(fixtures.MappedTest):
         sess.add(u2)
         sess.flush()
         sess.expunge_all()
-        assert users.count().scalar() == 1
-        assert orders.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 1)
         eq_(sess.query(User).all(),
             [User(name='newuser',
                   orders=[Order(description='someorder')])])
@@ -391,14 +391,14 @@ class O2MCascadeDeleteOrphanTest(fixtures.MappedTest):
                          Order(description='someotherorder')])
         sess.add(u)
         sess.flush()
-        assert users.count().scalar() == 1
-        assert orders.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 2)
 
         del u.orders[0]
         sess.delete(u)
         sess.flush()
-        assert users.count().scalar() == 0
-        assert orders.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(users).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 0)
 
     def test_collection_orphans(self):
         User, users, orders, Order = (self.classes.User,
@@ -413,15 +413,15 @@ class O2MCascadeDeleteOrphanTest(fixtures.MappedTest):
         sess.add(u)
         sess.flush()
 
-        assert users.count().scalar() == 1
-        assert orders.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 2)
 
         u.orders[:] = []
 
         sess.flush()
 
-        assert users.count().scalar() == 1
-        assert orders.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 0)
 
 class O2MCascadeTest(fixtures.MappedTest):
     run_inserts = None
@@ -532,14 +532,14 @@ class O2MCascadeDeleteNoOrphanTest(fixtures.MappedTest):
                          Order(description='someotherorder')])
         sess.add(u)
         sess.flush()
-        assert users.count().scalar() == 1
-        assert orders.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 2)
 
         del u.orders[0]
         sess.delete(u)
         sess.flush()
-        assert users.count().scalar() == 0
-        assert orders.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(users).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(orders).scalar(), 1)
 
 class O2OSingleParentTest(_fixtures.FixtureTest):
     run_inserts = None
@@ -1289,13 +1289,13 @@ class M2OCascadeDeleteOrphanTestOne(fixtures.MappedTest):
                                 self.tables.extra)
 
         sess = create_session()
-        assert prefs.count().scalar() == 3
-        assert extra.count().scalar() == 3
+        eq_(select([func.count('*')]).select_from(prefs).scalar(), 3)
+        eq_(select([func.count('*')]).select_from(extra).scalar(), 3)
         jack = sess.query(User).filter_by(name="jack").one()
         jack.pref = None
         sess.flush()
-        assert prefs.count().scalar() == 2
-        assert extra.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(prefs).scalar(), 2)
+        eq_(select([func.count('*')]).select_from(extra).scalar(), 2)
 
     def test_cascade_on_deleted(self):
         """test a bug introduced by r6711"""
@@ -1365,8 +1365,8 @@ class M2OCascadeDeleteOrphanTestOne(fixtures.MappedTest):
         assert p in sess
         assert e in sess
         sess.flush()
-        assert prefs.count().scalar() == 2
-        assert extra.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(prefs).scalar(), 2)
+        eq_(select([func.count('*')]).select_from(extra).scalar(), 2)
 
     def test_pending_expunge(self):
         Pref, User = self.classes.Pref, self.classes.User
@@ -1755,9 +1755,9 @@ class M2MCascadeTest(fixtures.MappedTest):
 
         a1.bs.remove(b1)
         sess.flush()
-        assert atob.count().scalar() ==0
-        assert b.count().scalar() == 0
-        assert a.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(atob).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(b).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(a).scalar(), 1)
 
     def test_delete_orphan_dynamic(self):
         a, A, B, b, atob = (self.tables.a,
@@ -1780,9 +1780,9 @@ class M2MCascadeTest(fixtures.MappedTest):
 
         a1.bs.remove(b1)
         sess.flush()
-        assert atob.count().scalar() == 0
-        assert b.count().scalar() == 0
-        assert a.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(atob).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(b).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(a).scalar(), 1)
 
     def test_delete_orphan_cascades(self):
         a, A, c, b, C, B, atob = (self.tables.a,
@@ -1811,10 +1811,10 @@ class M2MCascadeTest(fixtures.MappedTest):
 
         a1.bs.remove(b1)
         sess.flush()
-        assert atob.count().scalar() ==0
-        assert b.count().scalar() == 0
-        assert a.count().scalar() == 1
-        assert c.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(atob).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(b).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(a).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(c).scalar(), 0)
 
     def test_cascade_delete(self):
         a, A, B, b, atob = (self.tables.a,
@@ -1836,9 +1836,9 @@ class M2MCascadeTest(fixtures.MappedTest):
 
         sess.delete(a1)
         sess.flush()
-        assert atob.count().scalar() ==0
-        assert b.count().scalar() == 0
-        assert a.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(atob).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(b).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(a).scalar(), 0)
 
     def test_single_parent_error(self):
         a, A, B, b, atob = (self.tables.a,
diff --git a/test/orm/test_dynamic.py b/test/orm/test_dynamic.py
index 950ff1953..a1d621788 100644
--- a/test/orm/test_dynamic.py
+++ b/test/orm/test_dynamic.py
@@ -564,8 +564,14 @@ class UOWTest(
         )
         sess.add(u)
         sess.commit()
-        eq_(testing.db.scalar(addresses.count(addresses.c.user_id == None)), 0)
-        eq_(testing.db.scalar(addresses.count(addresses.c.user_id != None)), 6)
+        eq_(
+            testing.db.scalar(
+                select([func.count('*')]).where(addresses.c.user_id == None)),
+            0)
+        eq_(
+            testing.db.scalar(
+                select([func.count('*')]).where(addresses.c.user_id != None)),
+            6)
 
         sess.delete(u)
 
@@ -574,12 +580,26 @@ class UOWTest(
         if expected:
             eq_(
                 testing.db.scalar(
-                    addresses.count(addresses.c.user_id == None)), 6)
+                    select([func.count('*')]).where(
+                        addresses.c.user_id == None
+                    )
+                ),
+                6
+            )
             eq_(
                 testing.db.scalar(
-                    addresses.count(addresses.c.user_id != None)), 0)
+                    select([func.count('*')]).where(
+                        addresses.c.user_id != None
+                    )
+                ),
+                0
+            )
         else:
-            eq_(testing.db.scalar(addresses.count()), 0)
+            eq_(
+                testing.db.scalar(
+                    select([func.count('*')]).select_from(addresses)
+                ),
+                0)
 
     def test_delete_nocascade(self):
         self._test_delete_cascade(True)
diff --git a/test/orm/test_mapper.py b/test/orm/test_mapper.py
index e357a7e25..bc5f24fb2 100644
--- a/test/orm/test_mapper.py
+++ b/test/orm/test_mapper.py
@@ -3,7 +3,8 @@
 from sqlalchemy.testing import assert_raises, assert_raises_message
 import sqlalchemy as sa
 from sqlalchemy import testing
-from sqlalchemy import MetaData, Integer, String, ForeignKey, func, util
+from sqlalchemy import MetaData, Integer, String, \
+    ForeignKey, func, util, select
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy.engine import default
 from sqlalchemy.orm import mapper, relationship, backref, \
@@ -1070,8 +1071,10 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
         sess.add(a)
         sess.flush()
 
-        eq_(addresses.count().scalar(), 6)
-        eq_(email_bounces.count().scalar(), 5)
+        eq_(
+            select([func.count('*')]).select_from(addresses).scalar(), 6)
+        eq_(
+            select([func.count('*')]).select_from(email_bounces).scalar(), 5)
 
     def test_mapping_to_outerjoin(self):
         """Mapping to an outer join with a nullable composite primary key."""
@@ -2996,7 +2999,9 @@ class RequirementsTest(fixtures.MappedTest):
         h1.h1s.append(H1())
 
         s.flush()
-        eq_(ht1.count().scalar(), 4)
+        eq_(
+            select([func.count('*')]).select_from(ht1)
+                .scalar(), 4)
 
         h6 = H6()
         h6.h1a = h1
diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py
index 00d41604c..429e0f308 100644
--- a/test/orm/test_relationships.py
+++ b/test/orm/test_relationships.py
@@ -2,7 +2,8 @@ from sqlalchemy.testing import assert_raises, assert_raises_message
 import datetime
 import sqlalchemy as sa
 from sqlalchemy import testing
-from sqlalchemy import Integer, String, ForeignKey, MetaData, and_
+from sqlalchemy import Integer, String, ForeignKey, MetaData, and_, \
+    select, func
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy.orm import mapper, relationship, relation, \
     backref, create_session, configure_mappers, \
@@ -1980,12 +1981,12 @@ class TypedAssociationTable(fixtures.MappedTest):
         sess.add(a)
         sess.flush()
 
-        assert t3.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(t3).scalar(), 2)
 
         a.t2s.remove(c)
         sess.flush()
 
-        assert t3.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(t3).scalar(), 1)
 
 
 class CustomOperatorTest(fixtures.MappedTest, AssertsCompiledSQL):
diff --git a/test/orm/test_transaction.py b/test/orm/test_transaction.py
index e4ada2292..85125e2b7 100644
--- a/test/orm/test_transaction.py
+++ b/test/orm/test_transaction.py
@@ -209,8 +209,8 @@ class SessionTransactionTest(FixtureTest):
         sess.commit()
         sess.close()
         engine2.dispose()
-        assert users.count().scalar() == 1
-        assert addresses.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(users).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(addresses).scalar(), 1)
 
     @testing.requires.independent_connections
     def test_invalidate(self):
@@ -932,10 +932,11 @@ class AutoExpireTest(_LocalFixture):
         # because the identity map would switch it
         u1 = s.query(User).filter_by(name='ed').one()
         assert u1_state not in s.identity_map.all_states()
-        assert s.scalar(users.count()) == 1
+
+        eq_(s.scalar(select([func.count('*')]).select_from(users)), 1)
         s.delete(u1)
         s.flush()
-        assert s.scalar(users.count()) == 0
+        eq_(s.scalar(select([func.count('*')]).select_from(users)), 0)
         s.commit()
 
     def test_trans_deleted_cleared_on_rollback(self):
diff --git a/test/orm/test_unitofwork.py b/test/orm/test_unitofwork.py
index 21cb1dd12..72b913b1a 100644
--- a/test/orm/test_unitofwork.py
+++ b/test/orm/test_unitofwork.py
@@ -8,8 +8,7 @@ from sqlalchemy.orm import mapper as orm_mapper
 import sqlalchemy as sa
 from sqlalchemy.util import u, ue, b
 from sqlalchemy import Integer, String, ForeignKey, \
-    literal_column, event, Boolean
-from sqlalchemy.testing import engines
+    literal_column, event, Boolean, select, func
 from sqlalchemy import testing
 from sqlalchemy.testing.schema import Table
 from sqlalchemy.testing.schema import Column
@@ -370,9 +369,10 @@ class ForeignPKTest(fixtures.MappedTest):
         session.add(p)
         session.flush()
 
-        p_count = people.count(people.c.person=='im the key').scalar()
+        p_count = select([func.count('*')]).where(
+            people.c.person=='im the key').scalar()
         eq_(p_count, 1)
-        eq_(peoplesites.count(peoplesites.c.person=='im the key').scalar(), 1)
+        eq_(select([func.count('*')]).where(peoplesites.c.person=='im the key').scalar(), 1)
 
 
 class ClauseAttributesTest(fixtures.MappedTest):
@@ -531,13 +531,13 @@ class PassiveDeletesTest(fixtures.MappedTest):
         session.flush()
         session.expunge_all()
 
-        assert myothertable.count().scalar() == 4
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 4)
         mc = session.query(MyClass).get(mc.id)
         session.delete(mc)
         session.flush()
 
-        assert mytable.count().scalar() == 0
-        assert myothertable.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(mytable).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 0)
 
     @testing.emits_warning(r".*'passive_deletes' is normally configured on one-to-many")
     def test_backwards_pd(self):
@@ -565,16 +565,16 @@ class PassiveDeletesTest(fixtures.MappedTest):
         session.add(mco)
         session.flush()
 
-        assert mytable.count().scalar() == 1
-        assert myothertable.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(mytable).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 1)
 
         session.expire(mco, ['myclass'])
         session.delete(mco)
         session.flush()
 
         # mytable wasn't deleted, is the point.
-        assert mytable.count().scalar() == 1
-        assert myothertable.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(mytable).scalar(), 1)
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 0)
 
     def test_aaa_m2o_emits_warning(self):
         myothertable, MyClass, MyOtherClass, mytable = (self.tables.myothertable,
@@ -681,7 +681,7 @@ class ExtraPassiveDeletesTest(fixtures.MappedTest):
         session.flush()
         session.expunge_all()
 
-        assert myothertable.count().scalar() == 4
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 4)
         mc = session.query(MyClass).get(mc.id)
         session.delete(mc)
         assert_raises(sa.exc.DBAPIError, session.flush)
@@ -705,7 +705,7 @@ class ExtraPassiveDeletesTest(fixtures.MappedTest):
         session.flush()
         session.expunge_all()
 
-        assert myothertable.count().scalar() == 1
+        eq_(select([func.count('*')]).select_from(myothertable).scalar(), 1)
 
         mc = session.query(MyClass).get(mc.id)
         session.delete(mc)
@@ -1589,8 +1589,8 @@ class SaveTest(_fixtures.FixtureTest):
         u = session.query(User).get(u.id)
         session.delete(u)
         session.flush()
-        assert users.count().scalar() == 0
-        assert addresses.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(users).scalar(), 0)
+        eq_(select([func.count('*')]).select_from(addresses).scalar(), 0)
 
     def test_batch_mode(self):
         """The 'batch=False' flag on mapper()"""
@@ -1966,10 +1966,10 @@ class ManyToManyTest(_fixtures.FixtureTest):
         session.add(i)
         session.flush()
 
-        assert item_keywords.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(item_keywords).scalar(), 2)
         i.keywords = []
         session.flush()
-        assert item_keywords.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(item_keywords).scalar(), 0)
 
     def test_scalar(self):
         """sa.dependency won't delete an m2m relationship referencing None."""
@@ -2173,10 +2173,10 @@ class SaveTest3(fixtures.MappedTest):
         session.add(i)
         session.flush()
 
-        assert assoc.count().scalar() == 2
+        eq_(select([func.count('*')]).select_from(assoc).scalar(), 2)
         i.keywords = []
         session.flush()
-        assert assoc.count().scalar() == 0
+        eq_(select([func.count('*')]).select_from(assoc).scalar(), 0)
 
 
 class BooleanColTest(fixtures.MappedTest):
diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py
index c8ce13c91..bfc050e35 100644
--- a/test/orm/test_unitofworkv2.py
+++ b/test/orm/test_unitofworkv2.py
@@ -6,7 +6,7 @@ from test.orm import _fixtures
 from sqlalchemy import exc, util
 from sqlalchemy.testing import fixtures, config
 from sqlalchemy import Integer, String, ForeignKey, func, \
-    literal, FetchedValue, text
+    literal, FetchedValue, text, select
 from sqlalchemy.orm import mapper, relationship, backref, \
     create_session, unitofwork, attributes,\
     Session, exc as orm_exc
@@ -1306,7 +1306,9 @@ class RowswitchAccountingTest(fixtures.MappedTest):
         eq_(p1.id, 5)
         sess.flush()
 
-        eq_(sess.scalar(self.tables.parent.count()), 0)
+        eq_(
+            select([func.count('*')]).select_from(self.tables.parent).scalar(),
+            0)
 
 
 class RowswitchM2OTest(fixtures.MappedTest):
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index e21b21ab2..db19e145b 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -775,7 +775,8 @@ class AutoIncrementTest(fixtures.TablesTest):
 
         testing.db.execute(dataset_no_autoinc.insert())
         eq_(
-            testing.db.scalar(dataset_no_autoinc.count()), 1
+            testing.db.scalar(
+                select([func.count('*')]).select_from(dataset_no_autoinc)), 1
         )
 
 
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 344cfefa5..92d35e6e5 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -23,18 +23,6 @@ from sqlalchemy import util
 
 class MetaDataTest(fixtures.TestBase, ComparesTables):
 
-    def test_metadata_connect(self):
-        metadata = MetaData()
-        t1 = Table('table1', metadata,
-                   Column('col1', Integer, primary_key=True),
-                   Column('col2', String(20)))
-        metadata.bind = testing.db
-        metadata.create_all()
-        try:
-            assert t1.count().scalar() == 0
-        finally:
-            metadata.drop_all()
-
     def test_metadata_contains(self):
         metadata = MetaData()
         t1 = Table('t1', metadata, Column('x', Integer))
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 3d527b261..67d20871c 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -1477,8 +1477,8 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
         data = os.urandom(32)
         binary_table.insert().execute(data=data)
         eq_(
-            binary_table.select().where(binary_table.c.data == data).alias().
-            count().scalar(), 1)
+            select([func.count('*')]).select_from(binary_table).
+            where(binary_table.c.data == data).scalar(), 1)
 
     @testing.requires.binary_literals
     def test_literal_roundtrip(self):