Generate Oracle ROWNUM scheme using named subqueries

The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries
rather than unnamed subqueries when it transparently rewrites a SELECT
statement to one that uses a subquery that includes ROWNUM.  The change is
part of a larger change where unnamed subqueries are no longer directly
supported by Core, as well as to modernize the internal use of the select()
construct within the Oracle dialect.

Change-Id: I27605d7cf16ce79f9d577dbc84e3bd51b7c9b4ae

4 files changed, 205 insertions, 80 deletions

diff --git a/doc/build/changelog/unreleased_14/oracle_limit.rst b/doc/build/changelog/unreleased_14/oracle_limit.rst
new file mode 100644
index 000000000..4caf7c317
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/oracle_limit.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: oracle, change
+
+    The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries
+    rather than unnamed subqueries when it transparently rewrites a SELECT
+    statement to one that uses a subquery that includes ROWNUM.  The change is
+    part of a larger change where unnamed subqueries are no longer directly
+    supported by Core, as well as to modernize the internal use of the select()
+    construct within the Oracle dialect.
+
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 9f0b23e12..33f9c8659 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -867,12 +867,28 @@ class OracleCompiler(compiler.SQLCompiler):
                 # Outer select and "ROWNUM as ora_rn" can be dropped if
                 # limit=0
 
-                kwargs["select_wraps_for"] = select
+                kwargs["select_wraps_for"] = orig_select = select
                 select = select._generate()
                 select._oracle_visit = True
 
+                # add expressions to accommodate FOR UPDATE OF
+                for_update = select._for_update_arg
+                if for_update is not None and for_update.of:
+                    for_update = for_update._clone()
+                    for_update._copy_internals()
+
+                    for elem in for_update.of:
+                        select = select.column(elem)
+
                 # Wrap the middle select and add the hint
-                limitselect = sql.select([c for c in select.c])
+                inner_subquery = select.alias()
+                limitselect = sql.select(
+                    [
+                        c
+                        for c in inner_subquery.c
+                        if orig_select.corresponding_column(c) is not None
+                    ]
+                )
                 if (
                     limit_clause is not None
                     and self.dialect.optimize_limits
@@ -886,15 +902,9 @@ class OracleCompiler(compiler.SQLCompiler):
                 limitselect._is_wrapper = True
 
                 # add expressions to accommodate FOR UPDATE OF
-                for_update = select._for_update_arg
                 if for_update is not None and for_update.of:
-                    for_update = for_update._clone()
-                    for_update._copy_internals()
-
-                    for elem in for_update.of:
-                        select.append_column(elem)
 
-                    adapter = sql_util.ClauseAdapter(select)
+                    adapter = sql_util.ClauseAdapter(inner_subquery)
                     for_update.of = [
                         adapter.traverse(elem) for elem in for_update.of
                     ]
@@ -913,7 +923,7 @@ class OracleCompiler(compiler.SQLCompiler):
                         max_row = limit_clause
                         if offset_clause is not None:
                             max_row = max_row + offset_clause
-                    limitselect.append_whereclause(
+                    limitselect = limitselect.where(
                         sql.literal_column("ROWNUM") <= max_row
                     )
 
@@ -928,22 +938,35 @@ class OracleCompiler(compiler.SQLCompiler):
                     limitselect._oracle_visit = True
                     limitselect._is_wrapper = True
 
+                    if for_update is not None and for_update.of:
+
+                        for elem in for_update.of:
+                            if limitselect.corresponding_column(elem) is None:
+                                limitselect = limitselect.column(elem)
+
+                    limit_subquery = limitselect.alias()
                     offsetselect = sql.select(
-                        [c for c in limitselect.c if c.key != "ora_rn"]
+                        [
+                            c
+                            for c in limit_subquery.c
+                            if orig_select.corresponding_column(c) is not None
+                        ]
                     )
+
                     offsetselect._oracle_visit = True
                     offsetselect._is_wrapper = True
 
                     if for_update is not None and for_update.of:
-                        for elem in for_update.of:
-                            if limitselect.corresponding_column(elem) is None:
-                                limitselect.append_column(elem)
+                        adapter = sql_util.ClauseAdapter(limit_subquery)
+                        for_update.of = [
+                            adapter.traverse(elem) for elem in for_update.of
+                        ]
 
                     if not self.dialect.use_binds_for_limits:
                         offset_clause = sql.literal_column(
                             "%d" % select._offset
                         )
-                    offsetselect.append_whereclause(
+                    offsetselect = offsetselect.where(
                         sql.literal_column("ora_rn") > offset_clause
                     )
 
diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index 596161ef2..931079240 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -151,7 +151,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "GROUP BY included_parts.sub_part",
         )
 
-    def test_limit(self):
+    def test_limit_one(self):
         t = table("sometable", column("col1"), column("col2"))
         s = select([t])
         c = s.compile(dialect=oracle.OracleDialect())
@@ -159,11 +159,12 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         s = select([t]).limit(10).offset(20)
         self.assert_compile(
             s,
-            "SELECT col1, col2 FROM (SELECT col1, "
-            "col2, ROWNUM AS ora_rn FROM (SELECT "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, "
+            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
-            "col2 FROM sometable) WHERE ROWNUM <= "
-            ":param_1 + :param_2) WHERE ora_rn > :param_2",
+            "col2 FROM sometable) anon_2 WHERE ROWNUM <= "
+            ":param_1 + :param_2) anon_1 WHERE ora_rn > :param_2",
             checkparams={"param_1": 10, "param_2": 20},
         )
 
@@ -171,56 +172,75 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         eq_(len(c._result_columns), 2)
         assert t.c.col1 in set(c._create_result_map()["col1"][1])
 
+    def test_limit_two(self):
+        t = table("sometable", column("col1"), column("col2"))
+        s = select([t]).limit(10).offset(20)
+
+        # TODO: this will require a subquery
         s2 = select([s.c.col1, s.c.col2])
         self.assert_compile(
             s2,
-            "SELECT col1, col2 FROM (SELECT col1, col2 "
-            "FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, "
+            "anon_1.col2 AS col2 "
+            "FROM (SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, "
+            "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) "
-            "WHERE ROWNUM <= :param_1 + :param_2) "
+            "sometable.col2 AS col2 FROM sometable) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
             "WHERE ora_rn > :param_2)",
             checkparams={"param_1": 10, "param_2": 20},
         )
 
         self.assert_compile(
             s2,
-            "SELECT col1, col2 FROM (SELECT col1, col2 "
-            "FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, "
+            "anon_1.col2 AS col2 "
+            "FROM (SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, "
+            "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) "
-            "WHERE ROWNUM <= :param_1 + :param_2) "
+            "sometable.col2 AS col2 FROM sometable) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
             "WHERE ora_rn > :param_2)",
         )
         c = s2.compile(dialect=oracle.OracleDialect())
         eq_(len(c._result_columns), 2)
         assert s.c.col1 in set(c._create_result_map()["col1"][1])
 
+    def test_limit_three(self):
+        t = table("sometable", column("col1"), column("col2"))
+
         s = select([t]).limit(10).offset(20).order_by(t.c.col2)
         self.assert_compile(
             s,
-            "SELECT col1, col2 FROM (SELECT col1, "
-            "col2, ROWNUM AS ora_rn FROM (SELECT "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, "
+            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
-            "sometable.col2) WHERE ROWNUM <= "
-            ":param_1 + :param_2) WHERE ora_rn > :param_2",
+            "sometable.col2) anon_2 WHERE ROWNUM <= "
+            ":param_1 + :param_2) anon_1 WHERE ora_rn > :param_2",
             checkparams={"param_1": 10, "param_2": 20},
         )
         c = s.compile(dialect=oracle.OracleDialect())
         eq_(len(c._result_columns), 2)
         assert t.c.col1 in set(c._create_result_map()["col1"][1])
 
+    def test_limit_four(self):
+        t = table("sometable", column("col1"), column("col2"))
+
         s = select([t]).with_for_update().limit(10).order_by(t.c.col2)
         self.assert_compile(
             s,
-            "SELECT col1, col2 FROM (SELECT "
+            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
-            "sometable.col2) WHERE ROWNUM <= :param_1 "
+            "sometable.col2) anon_1 WHERE ROWNUM <= :param_1 "
             "FOR UPDATE",
         )
 
+    def test_limit_five(self):
+        t = table("sometable", column("col1"), column("col2"))
+
         s = (
             select([t])
             .with_for_update()
@@ -230,12 +250,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         )
         self.assert_compile(
             s,
-            "SELECT col1, col2 FROM (SELECT col1, "
-            "col2, ROWNUM AS ora_rn FROM (SELECT "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, "
+            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
-            "sometable.col2) WHERE ROWNUM <= "
-            ":param_1 + :param_2) WHERE ora_rn > :param_2 FOR "
+            "sometable.col2) anon_2 WHERE ROWNUM <= "
+            ":param_1 + :param_2) anon_1 WHERE ora_rn > :param_2 FOR "
             "UPDATE",
         )
 
@@ -327,10 +348,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             .where(table1.c.myid == 7)
             .with_for_update(nowait=True, of=table1.c.name)
             .limit(10),
-            "SELECT myid, name FROM "
+            "SELECT anon_1.myid, anon_1.name FROM "
             "(SELECT mytable.myid AS myid, mytable.name AS name "
-            "FROM mytable WHERE mytable.myid = :myid_1) "
-            "WHERE ROWNUM <= :param_1 FOR UPDATE OF name NOWAIT",
+            "FROM mytable WHERE mytable.myid = :myid_1) anon_1 "
+            "WHERE ROWNUM <= :param_1 FOR UPDATE OF anon_1.name NOWAIT",
         )
 
     def test_for_update_of_w_limit_adaption_col_unpresent(self):
@@ -341,10 +362,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             .where(table1.c.myid == 7)
             .with_for_update(nowait=True, of=table1.c.name)
             .limit(10),
-            "SELECT myid FROM "
+            "SELECT anon_1.myid FROM "
             "(SELECT mytable.myid AS myid, mytable.name AS name "
-            "FROM mytable WHERE mytable.myid = :myid_1) "
-            "WHERE ROWNUM <= :param_1 FOR UPDATE OF name NOWAIT",
+            "FROM mytable WHERE mytable.myid = :myid_1) anon_1 "
+            "WHERE ROWNUM <= :param_1 FOR UPDATE OF anon_1.name NOWAIT",
         )
 
     def test_for_update_of_w_limit_offset_adaption_col_present(self):
@@ -356,11 +377,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             .with_for_update(nowait=True, of=table1.c.name)
             .limit(10)
             .offset(50),
-            "SELECT myid, name FROM (SELECT myid, name, ROWNUM AS ora_rn "
+            "SELECT anon_1.myid, anon_1.name FROM "
+            "(SELECT anon_2.myid AS myid, anon_2.name AS name, "
+            "ROWNUM AS ora_rn "
             "FROM (SELECT mytable.myid AS myid, mytable.name AS name "
-            "FROM mytable WHERE mytable.myid = :myid_1) "
-            "WHERE ROWNUM <= :param_1 + :param_2) WHERE ora_rn > :param_2 "
-            "FOR UPDATE OF name NOWAIT",
+            "FROM mytable WHERE mytable.myid = :myid_1) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
+            "WHERE ora_rn > :param_2 "
+            "FOR UPDATE OF anon_1.name NOWAIT",
         )
 
     def test_for_update_of_w_limit_offset_adaption_col_unpresent(self):
@@ -372,11 +396,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             .with_for_update(nowait=True, of=table1.c.name)
             .limit(10)
             .offset(50),
-            "SELECT myid FROM (SELECT myid, ROWNUM AS ora_rn, name "
+            "SELECT anon_1.myid FROM (SELECT anon_2.myid AS myid, "
+            "ROWNUM AS ora_rn, anon_2.name AS name "
             "FROM (SELECT mytable.myid AS myid, mytable.name AS name "
-            "FROM mytable WHERE mytable.myid = :myid_1) "
-            "WHERE ROWNUM <= :param_1 + :param_2) WHERE ora_rn > :param_2 "
-            "FOR UPDATE OF name NOWAIT",
+            "FROM mytable WHERE mytable.myid = :myid_1) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
+            "WHERE ora_rn > :param_2 "
+            "FOR UPDATE OF anon_1.name NOWAIT",
         )
 
     def test_for_update_of_w_limit_offset_adaption_partial_col_unpresent(self):
@@ -388,11 +414,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             .with_for_update(nowait=True, of=[table1.c.foo, table1.c.bar])
             .limit(10)
             .offset(50),
-            "SELECT myid, bar FROM (SELECT myid, bar, ROWNUM AS ora_rn, "
-            "foo FROM (SELECT mytable.myid AS myid, mytable.bar AS bar, "
-            "mytable.foo AS foo FROM mytable WHERE mytable.myid = :myid_1) "
-            "WHERE ROWNUM <= :param_1 + :param_2) WHERE ora_rn > :param_2 "
-            "FOR UPDATE OF foo, bar NOWAIT",
+            "SELECT anon_1.myid, anon_1.bar FROM (SELECT anon_2.myid AS myid, "
+            "anon_2.bar AS bar, ROWNUM AS ora_rn, "
+            "anon_2.foo AS foo FROM (SELECT mytable.myid AS myid, "
+            "mytable.bar AS bar, "
+            "mytable.foo AS foo FROM mytable "
+            "WHERE mytable.myid = :myid_1) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
+            "WHERE ora_rn > :param_2 "
+            "FOR UPDATE OF anon_1.foo, anon_1.bar NOWAIT",
         )
 
     def test_limit_preserves_typing_information(self):
@@ -404,58 +434,84 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         compiled = stmt.compile(dialect=dialect)
         assert isinstance(compiled._create_result_map()["foo"][-1], MyType)
 
-    def test_use_binds_for_limits_disabled(self):
+    def test_use_binds_for_limits_disabled_one(self):
         t = table("sometable", column("col1"), column("col2"))
         dialect = oracle.OracleDialect(use_binds_for_limits=False)
 
         self.assert_compile(
             select([t]).limit(10),
-            "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= 10",
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT sometable.col1 AS col1, "
+            "sometable.col2 AS col2 FROM sometable) anon_1 WHERE ROWNUM <= 10",
             dialect=dialect,
         )
 
+    def test_use_binds_for_limits_disabled_two(self):
+        t = table("sometable", column("col1"), column("col2"))
+        dialect = oracle.OracleDialect(use_binds_for_limits=False)
+
         self.assert_compile(
             select([t]).offset(10),
-            "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
+            "anon_2.col1 AS col1, anon_2.col2 AS col2, ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
-            "FROM sometable)) WHERE ora_rn > 10",
+            "FROM sometable) anon_2) anon_1 WHERE ora_rn > 10",
             dialect=dialect,
         )
 
+    def test_use_binds_for_limits_disabled_three(self):
+        t = table("sometable", column("col1"), column("col2"))
+        dialect = oracle.OracleDialect(use_binds_for_limits=False)
+
         self.assert_compile(
             select([t]).limit(10).offset(10),
-            "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
+            "anon_2.col1 AS col1, anon_2.col2 AS col2, ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
-            "FROM sometable) WHERE ROWNUM <= 20) WHERE ora_rn > 10",
+            "FROM sometable) anon_2 WHERE ROWNUM <= 20) anon_1 "
+            "WHERE ora_rn > 10",
             dialect=dialect,
         )
 
-    def test_use_binds_for_limits_enabled(self):
+    def test_use_binds_for_limits_enabled_one(self):
         t = table("sometable", column("col1"), column("col2"))
         dialect = oracle.OracleDialect(use_binds_for_limits=True)
 
         self.assert_compile(
             select([t]).limit(10),
-            "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) WHERE ROWNUM "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT sometable.col1 AS col1, "
+            "sometable.col2 AS col2 FROM sometable) anon_1 WHERE ROWNUM "
             "<= :param_1",
             dialect=dialect,
         )
 
+    def test_use_binds_for_limits_enabled_two(self):
+        t = table("sometable", column("col1"), column("col2"))
+        dialect = oracle.OracleDialect(use_binds_for_limits=True)
+
         self.assert_compile(
             select([t]).offset(10),
-            "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, "
+            "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
-            "FROM sometable)) WHERE ora_rn > :param_1",
+            "FROM sometable) anon_2) anon_1 WHERE ora_rn > :param_1",
             dialect=dialect,
         )
 
+    def test_use_binds_for_limits_enabled_three(self):
+        t = table("sometable", column("col1"), column("col2"))
+        dialect = oracle.OracleDialect(use_binds_for_limits=True)
+
         self.assert_compile(
             select([t]).limit(10).offset(10),
-            "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, "
+            "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
-            "FROM sometable) WHERE ROWNUM <= :param_1 + :param_2) "
+            "FROM sometable) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
             "WHERE ora_rn > :param_2",
             dialect=dialect,
             checkparams={"param_1": 10, "param_2": 10},
@@ -524,7 +580,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=ora_dialect,
         )
 
-    def test_outer_join(self):
+    def _test_outer_join_fixture(self):
         table1 = table(
             "mytable",
             column("myid", Integer),
@@ -543,6 +599,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             column("userid", Integer),
             column("otherstuff", String),
         )
+        return table1, table2, table3
+
+    def test_outer_join_one(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
 
         query = select(
             [table1, table2],
@@ -569,6 +629,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "myothertable.otherid(+)",
             dialect=oracle.OracleDialect(use_ansi=False),
         )
+
+    def test_outer_join_two(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
         query = table1.outerjoin(
             table2, table1.c.myid == table2.c.otherid
         ).outerjoin(table3, table3.c.userid == table2.c.otherid)
@@ -584,6 +648,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "thirdtable.userid = myothertable.otherid",
         )
 
+    def test_outer_join_three(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
+        query = table1.outerjoin(
+            table2, table1.c.myid == table2.c.otherid
+        ).outerjoin(table3, table3.c.userid == table2.c.otherid)
+
         self.assert_compile(
             query.select(),
             "SELECT mytable.myid, mytable.name, "
@@ -596,6 +667,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "myothertable.otherid(+)",
             dialect=oracle.dialect(use_ansi=False),
         )
+
+    def test_outer_join_four(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
         query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
             table3, table3.c.userid == table2.c.otherid
         )
@@ -611,15 +686,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "myothertable.otherid",
             dialect=oracle.dialect(use_ansi=False),
         )
+
+    def test_outer_join_five(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
         query = table1.join(
             table2, table1.c.myid == table2.c.otherid
         ).outerjoin(table3, table3.c.userid == table2.c.otherid)
         self.assert_compile(
             query.select().order_by(table1.c.name).limit(10).offset(5),
-            "SELECT myid, name, description, otherid, "
-            "othername, userid, otherstuff FROM "
-            "(SELECT myid, name, description, otherid, "
-            "othername, userid, otherstuff, ROWNUM AS "
+            "SELECT anon_1.myid, anon_1.name, anon_1.description, "
+            "anon_1.otherid, "
+            "anon_1.othername, anon_1.userid, anon_1.otherstuff FROM "
+            "(SELECT anon_2.myid AS myid, anon_2.name AS name, "
+            "anon_2.description AS description, anon_2.otherid AS otherid, "
+            "anon_2.othername AS othername, anon_2.userid AS userid, "
+            "anon_2.otherstuff AS otherstuff, ROWNUM AS "
             "ora_rn FROM (SELECT mytable.myid AS myid, "
             "mytable.name AS name, mytable.description "
             "AS description, myothertable.otherid AS "
@@ -629,13 +711,16 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "mytable, myothertable, thirdtable WHERE "
             "thirdtable.userid(+) = "
             "myothertable.otherid AND mytable.myid = "
-            "myothertable.otherid ORDER BY mytable.name) "
-            "WHERE ROWNUM <= :param_1 + :param_2) "
+            "myothertable.otherid ORDER BY mytable.name) anon_2 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
             "WHERE ora_rn > :param_2",
             checkparams={"param_1": 10, "param_2": 5},
             dialect=oracle.dialect(use_ansi=False),
         )
 
+    def test_outer_join_six(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
         subq = (
             select([table1])
             .select_from(
@@ -673,12 +758,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=oracle.dialect(use_ansi=False),
         )
 
+    def test_outer_join_seven(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
+
         q = select([table1.c.name]).where(table1.c.name == "foo")
         self.assert_compile(
             q,
             "SELECT mytable.name FROM mytable WHERE " "mytable.name = :name_1",
             dialect=oracle.dialect(use_ansi=False),
         )
+
+    def test_outer_join_eight(self):
+        table1, table2, table3 = self._test_outer_join_fixture()
         subq = (
             select([table3.c.otherstuff])
             .where(table3.c.otherstuff == table1.c.name)
diff --git a/test/orm/test_lockmode.py b/test/orm/test_lockmode.py
index e3653c6ac..bcd7481a0 100644
--- a/test/orm/test_lockmode.py
+++ b/test/orm/test_lockmode.py
@@ -339,9 +339,10 @@ class CompileTest(_fixtures.FixtureTest, AssertsCompiledSQL):
             "addresses_1.id AS addresses_1_id, "
             "addresses_1.user_id AS addresses_1_user_id, "
             "addresses_1.email_address AS addresses_1_email_addres_1 "
-            "FROM (SELECT users_id, users_name FROM "
+            "FROM (SELECT anon_2.users_id AS users_id, "
+            "anon_2.users_name AS users_name FROM "
             "(SELECT users.id AS users_id, users.name AS users_name "
-            "FROM users) WHERE ROWNUM <= :param_1) anon_1 "
+            "FROM users) anon_2 WHERE ROWNUM <= :param_1) anon_1 "
             "LEFT OUTER JOIN addresses addresses_1 "
             "ON anon_1.users_id = addresses_1.user_id FOR UPDATE",
             dialect="oracle",