SelectBase no longer a FromClause

As part of the SQLAlchemy 2.0 migration project, a conceptual change has
been made to the role of the :class:`.SelectBase` class hierarchy,
which is the root of all "SELECT" statement constructs, in that they no
longer serve directly as FROM clauses, that is, they no longer subclass
:class:`.FromClause`.  For end users, the change mostly means that any
placement of a :func:`.select` construct in the FROM clause of another
:func:`.select` requires first that it be wrapped in a subquery first,
which historically is through the use of the :meth:`.SelectBase.alias`
method, and is now also available through the use of
:meth:`.SelectBase.subquery`.    This was usually a requirement in any
case since several databases don't accept unnamed SELECT subqueries
in their FROM clause in any case.

See the documentation in this change for lots more detail.

Fixes: #4617
Change-Id: I0f6174ee24b9a1a4529168e52e855e12abd60667

1 files changed, 18 insertions, 17 deletions

diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index f11daebb4..f6d4895a5 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -66,14 +66,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_subquery(self):
         t = table("sometable", column("col1"), column("col2"))
-        s = select([t])
+        s = select([t]).subquery()
         s = select([s.c.col1, s.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)",
+            "AS col2 FROM sometable) anon_1",
         )
 
     def test_bindparam_quote(self):
@@ -176,33 +176,34 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_limit_two(self):
         t = table("sometable", column("col1"), column("col2"))
-        s = select([t]).limit(10).offset(20)
+        s = select([t]).limit(10).offset(20).subquery()
 
-        # TODO: this will require a subquery
         s2 = select([s.c.col1, s.c.col2])
         self.assert_compile(
             s2,
-            "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, "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, "
+            "anon_2.col2 AS col2 "
+            "FROM (SELECT anon_3.col1 AS col1, anon_3.col2 AS col2, "
             "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) anon_2 "
-            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
-            "WHERE ora_rn > :param_2)",
+            "sometable.col2 AS col2 FROM sometable) anon_3 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_2 "
+            "WHERE ora_rn > :param_2) anon_1",
             checkparams={"param_1": 10, "param_2": 20},
         )
 
         self.assert_compile(
             s2,
-            "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, "
+            "SELECT anon_1.col1, anon_1.col2 FROM "
+            "(SELECT anon_2.col1 AS col1, "
+            "anon_2.col2 AS col2 "
+            "FROM (SELECT anon_3.col1 AS col1, anon_3.col2 AS col2, "
             "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2 FROM sometable) anon_2 "
-            "WHERE ROWNUM <= :param_1 + :param_2) anon_1 "
-            "WHERE ora_rn > :param_2)",
+            "sometable.col2 AS col2 FROM sometable) anon_3 "
+            "WHERE ROWNUM <= :param_1 + :param_2) anon_2 "
+            "WHERE ora_rn > :param_2) anon_1",
         )
         c = s2.compile(dialect=oracle.OracleDialect())
         eq_(len(c._result_columns), 2)