changed the anonymous numbering scheme to be more appealing

got tests running

9 files changed, 80 insertions, 72 deletions

diff --git a/doc/build/content/ormtutorial.txt b/doc/build/content/ormtutorial.txt
index 429d3422a..2dd59f355 100644
--- a/doc/build/content/ormtutorial.txt
+++ b/doc/build/content/ormtutorial.txt
@@ -548,14 +548,14 @@ Then apply an **option** to the query, indicating that we'd like `addresses` to
     {sql}>>> jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE
     SELECT anon_1.users_id AS anon_1_users_id, anon_1.users_name AS anon_1_users_name, 
     anon_1.users_fullname AS anon_1_users_fullname, anon_1.users_password AS anon_1_users_password, 
-    addresses_2.id AS addresses_2_id, addresses_2.email_address AS addresses_2_email_address, 
-    addresses_2.user_id AS addresses_2_user_id 
+    addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, 
+    addresses_1.user_id AS addresses_1_user_id 
         FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, 
         users.password AS users_password, users.oid AS users_oid 
         FROM users 
         WHERE users.name = ? ORDER BY users.oid 
-         LIMIT 2 OFFSET 0) AS anon_1 LEFT OUTER JOIN addresses AS addresses_2 
-         ON anon_1.users_id = addresses_2.user_id ORDER BY anon_1.oid, addresses_2.oid
+         LIMIT 2 OFFSET 0) AS anon_1 LEFT OUTER JOIN addresses AS addresses_1 
+         ON anon_1.users_id = addresses_1.user_id ORDER BY anon_1.oid, addresses_1.oid
         ['jack']
     
     >>> jack
diff --git a/doc/build/content/sqlexpression.txt b/doc/build/content/sqlexpression.txt
index 030812517..eaee02175 100644
--- a/doc/build/content/sqlexpression.txt
+++ b/doc/build/content/sqlexpression.txt
@@ -387,8 +387,8 @@ We'd like to show off some of our operators inside of `select()` constructs.  Bu
     ...     or_(addresses.c.email_address=='wendy@aol.com', addresses.c.email_address=='jack@yahoo.com'),
     ...     not_(users.c.id>5))
     users.name LIKE :users_name_1 AND users.id = addresses.user_id AND 
-    (addresses.email_address = :addresses_email_address_2 OR addresses.email_address = :addresses_email_address_3) 
-    AND users.id <= :users_id_4
+    (addresses.email_address = :addresses_email_address_1 OR addresses.email_address = :addresses_email_address_2) 
+    AND users.id <= :users_id_1
 
 And you can also use the re-jiggered bitwise AND, OR and NOT operators, although because of Python operator precedence you have to watch your parenthesis:
 
@@ -397,8 +397,8 @@ And you can also use the re-jiggered bitwise AND, OR and NOT operators, although
     ...     ((addresses.c.email_address=='wendy@aol.com') | (addresses.c.email_address=='jack@yahoo.com')) \
     ...     & ~(users.c.id>5) # doctest: +NORMALIZE_WHITESPACE
     users.name LIKE :users_name_1 AND users.id = addresses.user_id AND 
-    (addresses.email_address = :addresses_email_address_2 OR addresses.email_address = :addresses_email_address_3) 
-    AND users.id <= :users_id_4
+    (addresses.email_address = :addresses_email_address_1 OR addresses.email_address = :addresses_email_address_2) 
+    AND users.id <= :users_id_1
 
 So with all of this vocabulary, let's select all users who have an email address at AOL or MSN, whose name starts with a letter between "m" and "z", and we'll also generate a column containing their full name combined with their email address.  We will add two new constructs to this statement, `between()` and `label()`.  `between()` produces a BETWEEN clause, and `label()` is used in a column expression to produce labels using the `AS` keyword; its recommended when selecting from expressions that otherwise would not have a name:
 
@@ -511,8 +511,8 @@ Aliases can of course be used for anything which you can SELECT from, including
     SELECT users.name 
     FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname 
     FROM users, addresses AS addresses_1, addresses AS addresses_2 
-    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_5 
-    WHERE users.id = anon_5.id
+    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_1 
+    WHERE users.id = anon_1.id
     ['jack@msn.com', 'jack@yahoo.com']
     {stop}[(u'jack',)]
     
@@ -619,9 +619,9 @@ So we started small, added one little thing at a time, and at the end we have a
     >>> print query
     {opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address 
     FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
-    WHERE users.name = :users_name_2 AND (EXISTS (SELECT addresses_1.id 
+    WHERE users.name = :users_name_1 AND (EXISTS (SELECT addresses_1.id 
     FROM addresses AS addresses_1 
-    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :addresses_email_address_3)) ORDER BY users.fullname DESC
+    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :addresses_email_address_1)) ORDER BY users.fullname DESC
 
 One more thing though, with automatic labeling applied as well as anonymous aliasing, how do we retrieve the columns from the rows for this thing ?  The label for the `email_addresses` column is now the generated name `addresses_1_email_address`; and in another statement might be something different !  This is where accessing by result columns by `Column` object becomes very useful:
 
@@ -735,12 +735,12 @@ If we wanted to use our `calculate` statement twice with different bind paramete
     >>> print s
     SELECT users.id, users.name, users.fullname 
     FROM users, (SELECT q, z, r 
-    FROM calculate(:x_1, :y_2)) AS c1, (SELECT q, z, r 
-    FROM calculate(:x_3, :y_4)) AS c2 
+    FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r 
+    FROM calculate(:x_2, :y_2)) AS c2 
     WHERE users.id BETWEEN c1.z AND c2.z
 
     >>> s.compile().params
-    {'y_4': 12, 'y_2': 45, 'x_3': 5, 'x_1': 17}
+    {'x_2': 5, 'y_2': 12, 'y_1': 45, 'x_1': 17}
     
 ### Unions and Other Set Operations {@name=unions}
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 6795c2fcd..94950b872 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -409,13 +409,14 @@ class DefaultCompiler(engine.Compiled):
 
     def _process_anon(self, match):
         (ident, derived) = match.group(1,2)
-        if ('anonymous', ident) in self.generated_ids:
-            return self.generated_ids[('anonymous', ident)]
+        key = ('anonymous', ident)
+        if key in self.generated_ids:
+            return self.generated_ids[key]
         else:
-            anonymous_counter = self.generated_ids.get('anonymous', 1)
+            anonymous_counter = self.generated_ids.get(('anon_counter', derived), 1)
             newname = derived + "_" + str(anonymous_counter)
-            self.generated_ids['anonymous'] = anonymous_counter + 1
-            self.generated_ids[('anonymous', ident)] = newname
+            self.generated_ids[('anon_counter', derived)] = anonymous_counter + 1
+            self.generated_ids[key] = newname
             return newname
     
     def _anonymize(self, name):
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 732d4fdf9..ea8e067cd 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1754,7 +1754,7 @@ class _BindParamClause(ClauseElement, _CompareMixin):
             self.key = "{ANON %d %s}" % (id(self), key or 'param')
         else:
             self.key = key or "{ANON %d param}" % id(self)
-        self._orig_key = key
+        self._orig_key = key or 'param'
         self.unique = unique
         self.value = value
         self.isoutparam = isoutparam
diff --git a/test/base/dependency.py b/test/base/dependency.py
index ddadd1b31..a3d03e2fc 100644
--- a/test/base/dependency.py
+++ b/test/base/dependency.py
@@ -139,7 +139,7 @@ class DependencySortTest(PersistTest):
             node3,
             node4
         ]
-        head = DependencySorter(tuples, allitems).sort()
+        head = DependencySorter(tuples, allitems).sort(ignore_self_cycles=True)
         self.assert_sort(tuples, head)
 
     def testcircular(self):
@@ -156,7 +156,7 @@ class DependencySortTest(PersistTest):
             (node3, node1),
             (node4, node1)
         ]
-        head = DependencySorter(tuples, []).sort(allow_all_cycles=True)
+        head = DependencySorter(tuples, []).sort(allow_cycles=True)
         self.assert_sort(tuples, head)
         
     def testcircular2(self):
@@ -173,13 +173,13 @@ class DependencySortTest(PersistTest):
             (node3, node2),
             (node2, node3)
         ]
-        head = DependencySorter(tuples, []).sort(allow_all_cycles=True)
+        head = DependencySorter(tuples, []).sort(allow_cycles=True)
         self.assert_sort(tuples, head)
     
     def testcircular3(self):
         nodes = {}
         tuples = [('Question', 'Issue'), ('ProviderService', 'Issue'), ('Provider', 'Question'), ('Question', 'Provider'), ('ProviderService', 'Question'), ('Provider', 'ProviderService'), ('Question', 'Answer'), ('Issue', 'Question')]
-        head = DependencySorter(tuples, []).sort(allow_all_cycles=True)
+        head = DependencySorter(tuples, []).sort(allow_cycles=True)
         self.assert_sort(tuples, head)
         
     def testbigsort(self):
diff --git a/test/dialect/mssql.py b/test/dialect/mssql.py
index 1a8504701..3207abaa3 100755
--- a/test/dialect/mssql.py
+++ b/test/dialect/mssql.py
@@ -40,9 +40,11 @@ class CompileTest(SQLCompileTest):
             select([t2.c.col3.label('col3'), t2.c.col4.label('col4')], t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
         )        
         u = union(s1, s2, order_by=['col3', 'col4'])
-        self.assert_compile(u, "SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:t1_col2_1, :t1_col2_2) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2_3, :t2_col2_4) ORDER BY col3, col4")
+        self.assert_compile(u, "SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:t1_col2_1, :t1_col2_2) "\
+        "UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2_1, :t2_col2_2) ORDER BY col3, col4")
 
-        self.assert_compile(u.alias('bar').select(), "SELECT bar.col3, bar.col4 FROM (SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:t1_col2_1, :t1_col2_2) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2_3, :t2_col2_4)) AS bar")
+        self.assert_compile(u.alias('bar').select(), "SELECT bar.col3, bar.col4 FROM (SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE "\
+        "t1.col2 IN (:t1_col2_1, :t1_col2_2) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2_1, :t2_col2_2)) AS bar")
 
     def test_function(self):
         self.assert_compile(func.foo(1, 2), "foo(:foo_1, :foo_2)")
diff --git a/test/dialect/oracle.py b/test/dialect/oracle.py
index 79d73fb89..df8066bdb 100644
--- a/test/dialect/oracle.py
+++ b/test/dialect/oracle.py
@@ -95,8 +95,8 @@ class CompileTest(SQLCompileTest):
         self.assert_compile(query, 
             "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
 FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid(+) AND \
-(mytable.name = :mytable_name_1 OR mytable.myid = :mytable_myid_2 OR \
-myothertable.othername != :myothertable_othername_3 OR EXISTS (select yay from foo where boo = lar))",
+(mytable.name = :mytable_name_1 OR mytable.myid = :mytable_myid_1 OR \
+myothertable.othername != :myothertable_othername_1 OR EXISTS (select yay from foo where boo = lar))",
             dialect=oracle.OracleDialect(use_ansi = False))
 
         query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid)
@@ -125,7 +125,7 @@ myothertable.othername != :myothertable_othername_3 OR EXISTS (select yay from f
             order_by(addresses.oid_column, address_types.oid_column)
         self.assert_compile(s, "SELECT address_types_1.id, address_types_1.name, addresses.id, addresses.user_id, "
             "addresses.address_type_id, addresses.email_address FROM addresses LEFT OUTER JOIN address_types address_types_1 "
-            "ON addresses.address_type_id = address_types_1.id WHERE addresses.user_id = :addresses_user_id_2 ORDER BY addresses.rowid, "
+            "ON addresses.address_type_id = address_types_1.id WHERE addresses.user_id = :addresses_user_id_1 ORDER BY addresses.rowid, "
             "address_types.rowid")
 
 class TypesTest(SQLCompileTest):
diff --git a/test/sql/generative.py b/test/sql/generative.py
index b404b0017..847443330 100644
--- a/test/sql/generative.py
+++ b/test/sql/generative.py
@@ -233,17 +233,17 @@ class ClauseTest(SQLCompileTest):
         s3 = select([s], s.c.col2==s2.c.col2)
 
         self.assert_compile(s3, "SELECT anon_1.col1, anon_1.col2, anon_1.col3 FROM (SELECT table1.col1 AS col1, table1.col2 AS col2, "\
-        "table1.col3 AS col3 FROM table1 WHERE table1.col1 = :param_2) AS anon_1, "\
-        "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1 WHERE table1.col1 = :param_3) AS anon_4 "\
-        "WHERE anon_1.col2 = anon_4.col2")
+        "table1.col3 AS col3 FROM table1 WHERE table1.col1 = :param_1) AS anon_1, "\
+        "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1 WHERE table1.col1 = :param_2) AS anon_2 "\
+        "WHERE anon_1.col2 = anon_2.col2")
         
         s = select([t1], t1.c.col1==4).alias()
         s2 = ClauseVisitor().traverse(s, clone=True).alias()
         s3 = select([s], s.c.col2==s2.c.col2)
         self.assert_compile(s3, "SELECT anon_1.col1, anon_1.col2, anon_1.col3 FROM (SELECT table1.col1 AS col1, table1.col2 AS col2, "\
-        "table1.col3 AS col3 FROM table1 WHERE table1.col1 = :table1_col1_2) AS anon_1, "\
-        "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1 WHERE table1.col1 = :table1_col1_3) AS anon_4 "\
-        "WHERE anon_1.col2 = anon_4.col2")
+        "table1.col3 AS col3 FROM table1 WHERE table1.col1 = :table1_col1_1) AS anon_1, "\
+        "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1 WHERE table1.col1 = :table1_col1_2) AS anon_2 "\
+        "WHERE anon_1.col2 = anon_2.col2")
         
     def test_alias(self):
         subq = t2.select().alias('subq')
diff --git a/test/sql/select.py b/test/sql/select.py
index 66a87d6a0..626dc3847 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -85,7 +85,7 @@ myothertable.othername FROM mytable, myothertable")
             )
             ,
         "SELECT myid, name, description FROM (SELECT mytable.myid AS myid, mytable.name AS name, mytable.description AS description FROM mytable "\
-        "WHERE mytable.name = :mytable_name_1) WHERE myid = :myid_2")
+        "WHERE mytable.name = :mytable_name_1) WHERE myid = :myid_1")
 
         sq = select([table1])
         self.assert_compile(
@@ -243,9 +243,9 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         self.assert_compile(select([table2, s]), "SELECT myothertable.otherid, myothertable.othername, (SELECT mytable.myid FROM mytable) AS anon_1 FROM myothertable")
 
         # test expressions against scalar selects
-        self.assert_compile(select([s - literal(8)]), "SELECT (SELECT mytable.myid FROM mytable) - :param_2 AS anon_1")
-        self.assert_compile(select([select([table1.c.name]).as_scalar() + literal('x')]), "SELECT (SELECT mytable.name FROM mytable) || :param_2 AS anon_1")
-        self.assert_compile(select([s > literal(8)]), "SELECT (SELECT mytable.myid FROM mytable) > :param_2 AS anon_1")
+        self.assert_compile(select([s - literal(8)]), "SELECT (SELECT mytable.myid FROM mytable) - :param_1 AS anon_1")
+        self.assert_compile(select([select([table1.c.name]).as_scalar() + literal('x')]), "SELECT (SELECT mytable.name FROM mytable) || :param_1 AS anon_1")
+        self.assert_compile(select([s > literal(8)]), "SELECT (SELECT mytable.myid FROM mytable) > :param_1 AS anon_1")
 
         self.assert_compile(select([select([table1.c.name]).label('foo')]), "SELECT (SELECT mytable.name FROM mytable) AS foo")
 
@@ -304,8 +304,8 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
     def testand(self):
         self.assert_compile(
             select(['*'], and_(table1.c.myid == 12, table1.c.name=='asdf', table2.c.othername == 'foo', "sysdate() = today()")),
-            "SELECT * FROM mytable, myothertable WHERE mytable.myid = :mytable_myid_1 AND mytable.name = :mytable_name_2 "\
-            "AND myothertable.othername = :myothertable_othername_3 AND sysdate() = today()"
+            "SELECT * FROM mytable, myothertable WHERE mytable.myid = :mytable_myid_1 AND mytable.name = :mytable_name_1 "\
+            "AND myothertable.othername = :myothertable_othername_1 AND sysdate() = today()"
         )
 
     def testor(self):
@@ -315,8 +315,10 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
                 or_(table2.c.othername=='asdf', table2.c.othername == 'foo', table2.c.otherid == 9),
                 "sysdate() = today()",
             )),
-            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :mytable_myid_1 AND (myothertable.othername = :myothertable_othername_2 OR myothertable.othername = :myothertable_othername_3 OR myothertable.otherid = :myothertable_otherid_4) AND sysdate() = today()",
-            checkparams = {'myothertable_othername_2': 'asdf', 'myothertable_othername_3':'foo', 'myothertable_otherid_4': 9, 'mytable_myid_1': 12}
+            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable "\
+            "WHERE mytable.myid = :mytable_myid_1 AND (myothertable.othername = :myothertable_othername_1 OR "\
+            "myothertable.othername = :myothertable_othername_2 OR myothertable.otherid = :myothertable_otherid_1) AND sysdate() = today()",
+            checkparams = {'myothertable_othername_1': 'asdf', 'myothertable_othername_2':'foo', 'myothertable_otherid_1': 9, 'mytable_myid_1': 12}
         )
 
     def testdistinct(self):
@@ -387,19 +389,19 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
 
         self.assert_compile(
          table1.select((table1.c.myid != 12) & ~(table1.c.name=='john')),
-         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid_1 AND mytable.name != :mytable_name_2"
+         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid_1 AND mytable.name != :mytable_name_1"
         )
 
         self.assert_compile(
          table1.select((table1.c.myid != 12) & ~(table1.c.name.between('jack','john'))),
          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid_1 AND "\
-         "NOT (mytable.name BETWEEN :mytable_name_2 AND :mytable_name_3)"
+         "NOT (mytable.name BETWEEN :mytable_name_1 AND :mytable_name_2)"
         )
 
         self.assert_compile(
          table1.select((table1.c.myid != 12) & ~and_(table1.c.name=='john', table1.c.name=='ed', table1.c.name=='fred')),
          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid_1 AND "\
-         "NOT (mytable.name = :mytable_name_2 AND mytable.name = :mytable_name_3 AND mytable.name = :mytable_name_4)"
+         "NOT (mytable.name = :mytable_name_1 AND mytable.name = :mytable_name_2 AND mytable.name = :mytable_name_3)"
         )
 
         self.assert_compile(
@@ -414,7 +416,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         # test the op() function, also that its results are further usable in expressions
         self.assert_compile(
             table1.select(table1.c.myid.op('hoho')(12)==14),
-            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE (mytable.myid hoho :mytable_myid_1) = :param_2"
+            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE (mytable.myid hoho :mytable_myid_1) = :param_1"
         )
 
         # test that clauses can be pickled (operators need to be module-level, etc.)
@@ -644,7 +646,7 @@ FROM mytable, myothertable WHERE foo.id = foofoo(lala) AND datetime(foo) = Today
 
     def testliteral(self):
         self.assert_compile(select([literal("foo") + literal("bar")], from_obj=[table1]),
-            "SELECT :param_2 || :param_3 AS anon_1 FROM mytable")
+            "SELECT :param_1 || :param_2 AS anon_1 FROM mytable")
 
     def testcalculatedcolumns(self):
          value_tbl = table('values',
@@ -674,7 +676,7 @@ FROM mytable, myothertable WHERE foo.id = foofoo(lala) AND datetime(foo) = Today
         """tests the generation of functions using the func keyword"""
         # test an expression with a function
         self.assert_compile(func.lala(3, 4, literal("five"), table1.c.myid) * table2.c.otherid,
-            "lala(:lala_1, :lala_2, :param_3, mytable.myid) * myothertable.otherid")
+            "lala(:lala_1, :lala_2, :param_1, mytable.myid) * myothertable.otherid")
 
         # test it in a SELECT
         self.assert_compile(select([func.count(table1.c.myid)]),
@@ -727,10 +729,10 @@ FROM mytable, myothertable WHERE foo.id = foofoo(lala) AND datetime(foo) = Today
         self.assert_compile(s,
         "SELECT users.id, users.name, users.fullname "
         "FROM users, (SELECT q, z, r "
-        "FROM calculate(:x_1, :y_2)) AS c1, (SELECT q, z, r "
-        "FROM calculate(:x_3, :y_4)) AS c2 "
+        "FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
+        "FROM calculate(:x_2, :y_2)) AS c2 "
         "WHERE users.id BETWEEN c1.z AND c2.z"
-        , checkparams={'y_2': 45, 'x_1': 17, 'y_4': 12, 'x_3': 5})
+        , checkparams={'y_1': 45, 'x_1': 17, 'y_2': 12, 'x_2': 5})
 
     def testextract(self):
         """test the EXTRACT function"""
@@ -877,8 +879,8 @@ UNION SELECT mytable.myid FROM mytable"
         self.assert_compile(query,
             "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
 FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid \
-WHERE mytable.name = %(mytable_name_1)s OR mytable.myid = %(mytable_myid_2)s OR \
-myothertable.othername != %(myothertable_othername_3)s OR \
+WHERE mytable.name = %(mytable_name_1)s OR mytable.myid = %(mytable_myid_1)s OR \
+myothertable.othername != %(myothertable_othername_1)s OR \
 EXISTS (select yay from foo where boo = lar)",
             dialect=postgres.dialect()
             )
@@ -972,7 +974,8 @@ EXISTS (select yay from foo where boo = lar)",
         s = select([table1.c.myid]).where(table1.c.myid==12).as_scalar()
         s2 = select([table1, s], table1.c.myid==s)
         self.assert_compile(s2, 
-            "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid FROM mytable WHERE mytable.myid = :mytable_myid_2) AS anon_1 FROM mytable WHERE mytable.myid = (SELECT mytable.myid FROM mytable WHERE mytable.myid = :mytable_myid_2)")
+            "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid FROM mytable WHERE mytable.myid = "\
+            ":mytable_myid_1) AS anon_1 FROM mytable WHERE mytable.myid = (SELECT mytable.myid FROM mytable WHERE mytable.myid = :mytable_myid_1)")
         positional = s2.compile(dialect=sqlite.dialect())
         pp = positional.get_params()
         assert [pp[k] for k in positional.positiontup] == [12, 12]
@@ -1017,25 +1020,25 @@ EXISTS (select yay from foo where boo = lar)",
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), 'b'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :mytable_myid_2)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :mytable_myid_1)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), literal('b')])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :param_2)")
 
         self.assert_compile(select([table1], table1.c.myid.in_(['a', literal('b')])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid_1, :param_2)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid_1, :param_1)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal(1) + 'a'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 + :param_2)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a') +'a', 'b'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 || :param_2, :mytable_myid_3)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 || :param_2, :mytable_myid_1)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a') + literal('a'), literal('b')])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 || :param_2, :param_3)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([1, literal(3) + 4])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid_1, :param_2 + :param_3)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid_1, :param_1 + :param_2)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a') < 'b'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 < :param_2)")
@@ -1050,10 +1053,10 @@ EXISTS (select yay from foo where boo = lar)",
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, mytable.myid)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid +'a'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, mytable.myid + :mytable_myid_2)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, mytable.myid + :mytable_myid_1)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([literal(1), 'a' + table1.c.myid])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :mytable_myid_2 + mytable.myid)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :mytable_myid_1 + mytable.myid)")
 
         self.assert_compile(select([table1], table1.c.myid.in_([1, 2, 3])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid_1, :mytable_myid_2, :mytable_myid_3)")
@@ -1082,7 +1085,9 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE
                 ),
                 from_obj=[table1.join(table2, table1.c.myid==table2.c.otherid)], order_by=[table1.c.myid]
             ),
-            "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable JOIN myothertable ON mytable.myid = myothertable.otherid WHERE myothertable.otherid IN (SELECT myothertable.otherid FROM myothertable ORDER BY myothertable.othername  LIMIT 10) ORDER BY mytable.myid"
+            "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable "\
+            "JOIN myothertable ON mytable.myid = myothertable.otherid WHERE myothertable.otherid IN (SELECT myothertable.otherid "\
+            "FROM myothertable ORDER BY myothertable.othername  LIMIT 10) ORDER BY mytable.myid"
         )
 
         # test empty in clause
@@ -1156,13 +1161,13 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE
         self.assert_compile(table.select((table.c.field + 5) == table.c.field),
             "SELECT op.field FROM op WHERE op.field + :op_field_1 = op.field")
         self.assert_compile(table.select((table.c.field + 5) * 6),
-            "SELECT op.field FROM op WHERE (op.field + :op_field_1) * :param_2")
+            "SELECT op.field FROM op WHERE (op.field + :op_field_1) * :param_1")
         self.assert_compile(table.select((table.c.field * 5) + 6),
-            "SELECT op.field FROM op WHERE op.field * :op_field_1 + :param_2")
+            "SELECT op.field FROM op WHERE op.field * :op_field_1 + :param_1")
         self.assert_compile(table.select(5 + table.c.field.in_([5,6])),
-            "SELECT op.field FROM op WHERE :param_1 + (op.field IN (:op_field_2, :op_field_3))")
+            "SELECT op.field FROM op WHERE :param_1 + (op.field IN (:op_field_1, :op_field_2))")
         self.assert_compile(table.select((5 + table.c.field).in_([5,6])),
-            "SELECT op.field FROM op WHERE :op_field_1 + op.field IN (:param_2, :param_3)")
+            "SELECT op.field FROM op WHERE :op_field_1 + op.field IN (:param_1, :param_2)")
         self.assert_compile(table.select(not_(and_(table.c.field == 5, table.c.field == 7))),
             "SELECT op.field FROM op WHERE NOT (op.field = :op_field_1 AND op.field = :op_field_2)")
         self.assert_compile(table.select(not_(table.c.field == 5)),
@@ -1227,7 +1232,7 @@ class CRUDTest(SQLCompileTest):
         self.assert_compile(update(table1, table1.c.myid ==12), "UPDATE mytable SET myid=:myid WHERE mytable.myid = :mytable_myid_1", params={'myid':18}, checkparams={'myid':18, 'mytable_myid_1':12})
         s = table1.update(table1.c.myid == 12, values = {table1.c.name : 'lala'})
         c = s.compile(column_keys=['mytable_id', 'name'])
-        self.assert_compile(update(table1, table1.c.myid == 12, values = {table1.c.name : table1.c.myid}).values({table1.c.name:table1.c.name + 'foo'}), "UPDATE mytable SET name=(mytable.name || :mytable_name_1), description=:description WHERE mytable.myid = :mytable_myid_2", params = {'description':'test'})
+        self.assert_compile(update(table1, table1.c.myid == 12, values = {table1.c.name : table1.c.myid}).values({table1.c.name:table1.c.name + 'foo'}), "UPDATE mytable SET name=(mytable.name || :mytable_name_1), description=:description WHERE mytable.myid = :mytable_myid_1", params = {'description':'test'})
         self.assert_(str(s) == str(c))
 
     def testupdateexpression(self):
@@ -1237,8 +1242,8 @@ class CRUDTest(SQLCompileTest):
             values = {
             table1.c.name : table1.c.name + "lala",
             table1.c.myid : func.do_stuff(table1.c.myid, literal('hoho'))
-            }), "UPDATE mytable SET myid=do_stuff(mytable.myid, :param_1), name=(mytable.name || :mytable_name_2) "
-            "WHERE mytable.myid = hoho(:hoho_3) AND mytable.name = :param_4 || mytable.name || :param_5")
+            }), "UPDATE mytable SET myid=do_stuff(mytable.myid, :param_1), name=(mytable.name || :mytable_name_1) "
+            "WHERE mytable.myid = hoho(:hoho_1) AND mytable.name = :param_2 || mytable.name || :param_3")
 
     def testcorrelatedupdate(self):
         # test against a straight text subquery
@@ -1313,13 +1318,13 @@ class SchemaTest(SQLCompileTest):
         self.assert_compile(table4.select(), "SELECT remotetable.rem_id, remotetable.datatype_id, remotetable.value FROM remote_owner.remotetable")
         self.assert_compile(table4.select(and_(table4.c.datatype_id==7, table4.c.value=='hi')), 
             "SELECT remotetable.rem_id, remotetable.datatype_id, remotetable.value FROM remote_owner.remotetable WHERE "\
-            "remotetable.datatype_id = :remotetable_datatype_id_1 AND remotetable.value = :remotetable_value_2")
+            "remotetable.datatype_id = :remotetable_datatype_id_1 AND remotetable.value = :remotetable_value_1")
 
         s = table4.select(and_(table4.c.datatype_id==7, table4.c.value=='hi'))
         s.use_labels = True
         self.assert_compile(s, "SELECT remotetable.rem_id AS remotetable_rem_id, remotetable.datatype_id AS remotetable_datatype_id, remotetable.value "\
             "AS remotetable_value FROM remote_owner.remotetable WHERE "\
-            "remotetable.datatype_id = :remotetable_datatype_id_1 AND remotetable.value = :remotetable_value_2")
+            "remotetable.datatype_id = :remotetable_datatype_id_1 AND remotetable.value = :remotetable_value_1")
 
     def testalias(self):
         a = alias(table4, 'remtable')