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, 473 insertions, 19 deletions

diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py
index 6bf0e8962..d126912a5 100644
--- a/test/sql/test_deprecations.py
+++ b/test/sql/test_deprecations.py
@@ -1,5 +1,6 @@
 #! coding: utf-8
 
+from sqlalchemy import alias
 from sqlalchemy import bindparam
 from sqlalchemy import Column
 from sqlalchemy import column
@@ -8,8 +9,12 @@ from sqlalchemy import exc
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Integer
+from sqlalchemy import join
+from sqlalchemy import literal_column
 from sqlalchemy import MetaData
+from sqlalchemy import null
 from sqlalchemy import select
+from sqlalchemy import sql
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import table
@@ -19,8 +24,11 @@ from sqlalchemy import util
 from sqlalchemy.engine import default
 from sqlalchemy.schema import DDL
 from sqlalchemy.sql import coercions
+from sqlalchemy.sql import quoted_name
 from sqlalchemy.sql import roles
 from sqlalchemy.sql import util as sql_util
+from sqlalchemy.sql import visitors
+from sqlalchemy.sql.selectable import SelectStatementGrouping
 from sqlalchemy.testing import assert_raises
 from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.testing import AssertsCompiledSQL
@@ -377,8 +385,81 @@ class ForUpdateTest(fixtures.TestBase, AssertsCompiledSQL):
 
 
 class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    table1 = table(
+        "mytable",
+        column("myid", Integer),
+        column("name", String),
+        column("description", String),
+    )
+
+    table2 = table(
+        "myothertable", column("otherid", Integer), column("othername", String)
+    )
+
+    def test_select_of_select(self):
+        stmt = select([self.table1.c.myid])
+
+        with testing.expect_deprecated(
+            r"The SelectBase.select\(\) method is deprecated and will be "
+            "removed"
+        ):
+            self.assert_compile(
+                stmt.select(),
+                "SELECT anon_1.myid FROM (SELECT mytable.myid AS myid "
+                "FROM mytable) AS anon_1",
+            )
+
+    def test_join_of_select(self):
+        stmt = select([self.table1.c.myid])
+
+        with testing.expect_deprecated(
+            r"The SelectBase.join\(\) method is deprecated and will be "
+            "removed"
+        ):
+            self.assert_compile(
+                stmt.join(
+                    self.table2, self.table2.c.otherid == self.table1.c.myid
+                ),
+                # note the SQL is wrong here as the subquery now has a name.
+                # however, even SQLite which accepts unnamed subqueries in a
+                # JOIN cannot actually join with how SQLAlchemy 1.3 and
+                # earlier would render:
+                # sqlite> select myid, otherid from (select myid from mytable)
+                # join myothertable on mytable.myid=myothertable.otherid;
+                # Error: no such column: mytable.myid
+                # if using stmt.c.col, that fails often as well if there are
+                # any naming overlaps:
+                # sqlalchemy.exc.OperationalError: (sqlite3.OperationalError)
+                # ambiguous column name: id
+                # [SQL: SELECT id, data
+                # FROM (SELECT a.id AS id, a.data AS data
+                # FROM a) JOIN b ON b.a_id = id]
+                # so that shows that nobody is using this anyway
+                "(SELECT mytable.myid AS myid FROM mytable) AS anon_1 "
+                "JOIN myothertable ON myothertable.otherid = mytable.myid",
+            )
+
+    def test_outerjoin_of_select(self):
+        stmt = select([self.table1.c.myid])
+
+        with testing.expect_deprecated(
+            r"The SelectBase.outerjoin\(\) method is deprecated and will be "
+            "removed"
+        ):
+            self.assert_compile(
+                stmt.outerjoin(
+                    self.table2, self.table2.c.otherid == self.table1.c.myid
+                ),
+                # note the SQL is wrong here as the subquery now has a name
+                "(SELECT mytable.myid AS myid FROM mytable) AS anon_1 "
+                "LEFT OUTER JOIN myothertable "
+                "ON myothertable.otherid = mytable.myid",
+            )
+
     def test_column_roles(self):
-        stmt = select([table1.c.myid])
+        stmt = select([self.table1.c.myid])
 
         for role in [
             roles.WhereHavingRole,
@@ -402,7 +483,7 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
                 is_true(coerced.compare(stmt.scalar_subquery()))
 
     def test_labeled_role(self):
-        stmt = select([table1.c.myid])
+        stmt = select([self.table1.c.myid])
 
         with testing.expect_deprecated(
             "coercing SELECT object to scalar "
@@ -427,7 +508,7 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
             "subquery in a column-expression context is deprecated"
         ):
             self.assert_compile(
-                func.coalesce(select([table1.c.myid])),
+                func.coalesce(select([self.table1.c.myid])),
                 "coalesce((SELECT mytable.myid FROM mytable))",
             )
 
@@ -435,9 +516,9 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
             "coercing SELECT object to scalar "
             "subquery in a column-expression context is deprecated"
         ):
-            s = select([table1.c.myid]).alias()
+            s = select([self.table1.c.myid]).alias()
             self.assert_compile(
-                select([table1.c.myid]).where(table1.c.myid == s),
+                select([self.table1.c.myid]).where(self.table1.c.myid == s),
                 "SELECT mytable.myid FROM mytable WHERE "
                 "mytable.myid = (SELECT mytable.myid FROM "
                 "mytable)",
@@ -448,7 +529,7 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
             "subquery in a column-expression context is deprecated"
         ):
             self.assert_compile(
-                select([table1.c.myid]).where(s > table1.c.myid),
+                select([self.table1.c.myid]).where(s > self.table1.c.myid),
                 "SELECT mytable.myid FROM mytable WHERE "
                 "mytable.myid < (SELECT mytable.myid FROM "
                 "mytable)",
@@ -458,9 +539,9 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
             "coercing SELECT object to scalar "
             "subquery in a column-expression context is deprecated"
         ):
-            s = select([table1.c.myid]).alias()
+            s = select([self.table1.c.myid]).alias()
             self.assert_compile(
-                select([table1.c.myid]).where(table1.c.myid == s),
+                select([self.table1.c.myid]).where(self.table1.c.myid == s),
                 "SELECT mytable.myid FROM mytable WHERE "
                 "mytable.myid = (SELECT mytable.myid FROM "
                 "mytable)",
@@ -471,23 +552,35 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
             "subquery in a column-expression context is deprecated"
         ):
             self.assert_compile(
-                select([table1.c.myid]).where(s > table1.c.myid),
+                select([self.table1.c.myid]).where(s > self.table1.c.myid),
                 "SELECT mytable.myid FROM mytable WHERE "
                 "mytable.myid < (SELECT mytable.myid FROM "
                 "mytable)",
             )
 
+    def test_standalone_alias(self):
+        with testing.expect_deprecated(
+            "Implicit coercion of SELECT and textual SELECT constructs"
+        ):
+            stmt = alias(select([self.table1.c.myid]), "foo")
+
+        self.assert_compile(stmt, "SELECT mytable.myid FROM mytable")
+
+        is_true(
+            stmt.compare(select([self.table1.c.myid]).subquery().alias("foo"))
+        )
+
     def test_as_scalar(self):
         with testing.expect_deprecated(
             r"The SelectBase.as_scalar\(\) method is deprecated and "
             "will be removed in a future release."
         ):
-            stmt = select([table1.c.myid]).as_scalar()
+            stmt = select([self.table1.c.myid]).as_scalar()
 
-        is_true(stmt.compare(select([table1.c.myid]).scalar_subquery()))
+        is_true(stmt.compare(select([self.table1.c.myid]).scalar_subquery()))
 
     def test_fromclause_subquery(self):
-        stmt = select([table1.c.myid])
+        stmt = select([self.table1.c.myid])
         with testing.expect_deprecated(
             "Implicit coercion of SELECT and textual SELECT constructs "
             "into FROM clauses is deprecated"
@@ -498,6 +591,93 @@ class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL):
 
         is_true(coerced.compare(stmt.subquery()))
 
+    def test_plain_fromclause_select_to_subquery(self):
+        with testing.expect_deprecated(
+            "Implicit coercion of SELECT and textual SELECT "
+            "constructs into FROM clauses is deprecated;"
+        ):
+            element = coercions.expect(
+                roles.FromClauseRole,
+                SelectStatementGrouping(select([self.table1])),
+            )
+            is_true(
+                element.compare(
+                    SelectStatementGrouping(select([self.table1])).subquery()
+                )
+            )
+
+    def test_functions_select_method_two(self):
+        expr = func.rows("foo")
+        with testing.expect_deprecated(
+            "Implicit coercion of SELECT and textual SELECT constructs "
+            "into FROM clauses is deprecated"
+        ):
+            stmt = select(["*"]).select_from(expr.select())
+        self.assert_compile(
+            stmt, "SELECT * FROM (SELECT rows(:rows_2) AS rows_1) AS anon_1"
+        )
+
+    def test_functions_with_cols(self):
+        users = table(
+            "users", column("id"), column("name"), column("fullname")
+        )
+        calculate = select(
+            [column("q"), column("z"), column("r")],
+            from_obj=[
+                func.calculate(bindparam("x", None), bindparam("y", None))
+            ],
+        )
+
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns attributes are "
+            "deprecated and will be removed"
+        ):
+            self.assert_compile(
+                select([users], users.c.id > calculate.c.z),
+                "SELECT users.id, users.name, users.fullname "
+                "FROM users, (SELECT q, z, r "
+                "FROM calculate(:x, :y)) AS anon_1 "
+                "WHERE users.id > anon_1.z",
+            )
+
+
+class LateralSubqueryCoercionsTest(fixtures.TablesTest, AssertsCompiledSQL):
+    __dialect__ = default.DefaultDialect(supports_native_boolean=True)
+
+    run_setup_bind = None
+
+    run_create_tables = None
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "people",
+            metadata,
+            Column("people_id", Integer, primary_key=True),
+            Column("age", Integer),
+            Column("name", String(30)),
+        )
+        Table(
+            "bookcases",
+            metadata,
+            Column("bookcase_id", Integer, primary_key=True),
+            Column(
+                "bookcase_owner_id", Integer, ForeignKey("people.people_id")
+            ),
+            Column("bookcase_shelves", Integer),
+            Column("bookcase_width", Integer),
+        )
+        Table(
+            "books",
+            metadata,
+            Column("book_id", Integer, primary_key=True),
+            Column(
+                "bookcase_id", Integer, ForeignKey("bookcases.bookcase_id")
+            ),
+            Column("book_owner_id", Integer, ForeignKey("people.people_id")),
+            Column("book_weight", Integer),
+        )
+
 
 class TextTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = "default"
@@ -530,7 +710,7 @@ class TextTest(fixtures.TestBase, AssertsCompiledSQL):
             t = text(
                 "select id, name from user",
                 typemap=dict(id=Integer, name=String),
-            )
+            ).subquery()
 
         stmt = select([table1.c.myid]).select_from(
             table1.join(t, table1.c.myid == t.c.id)
@@ -554,9 +734,283 @@ class TextTest(fixtures.TestBase, AssertsCompiledSQL):
             text("select id, name from user", autocommit=True)
 
 
-table1 = table(
-    "mytable",
-    column("myid", Integer),
-    column("name", String),
-    column("description", String),
-)
+class SelectableTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    metadata = MetaData()
+    table1 = Table(
+        "table1",
+        metadata,
+        Column("col1", Integer, primary_key=True),
+        Column("col2", String(20)),
+        Column("col3", Integer),
+        Column("colx", Integer),
+    )
+
+    table2 = Table(
+        "table2",
+        metadata,
+        Column("col1", Integer, primary_key=True),
+        Column("col2", Integer, ForeignKey("table1.col1")),
+        Column("col3", String(20)),
+        Column("coly", Integer),
+    )
+
+    def _c_deprecated(self):
+        return testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns attributes are "
+            "deprecated"
+        )
+
+    def test_deprecated_subquery_standalone(self):
+        from sqlalchemy import subquery
+
+        with testing.expect_deprecated(
+            r"The standalone subquery\(\) function is deprecated"
+        ):
+            stmt = subquery(
+                None,
+                [literal_column("1").label("a")],
+                order_by=literal_column("1"),
+            )
+
+        self.assert_compile(
+            select([stmt]),
+            "SELECT anon_1.a FROM (SELECT 1 AS a ORDER BY 1) AS anon_1",
+        )
+
+    def test_append_column_after_replace_selectable(self):
+        basesel = select([literal_column("1").label("a")])
+        tojoin = select(
+            [literal_column("1").label("a"), literal_column("2").label("b")]
+        )
+        basefrom = basesel.alias("basefrom")
+        joinfrom = tojoin.alias("joinfrom")
+        sel = select([basefrom.c.a])
+
+        with testing.expect_deprecated(
+            r"The Selectable.replace_selectable\(\) " "method is deprecated"
+        ):
+            replaced = sel.replace_selectable(
+                basefrom, basefrom.join(joinfrom, basefrom.c.a == joinfrom.c.a)
+            )
+        self.assert_compile(
+            replaced,
+            "SELECT basefrom.a FROM (SELECT 1 AS a) AS basefrom "
+            "JOIN (SELECT 1 AS a, 2 AS b) AS joinfrom "
+            "ON basefrom.a = joinfrom.a",
+        )
+        replaced.append_column(joinfrom.c.b)
+        self.assert_compile(
+            replaced,
+            "SELECT basefrom.a, joinfrom.b FROM (SELECT 1 AS a) AS basefrom "
+            "JOIN (SELECT 1 AS a, 2 AS b) AS joinfrom "
+            "ON basefrom.a = joinfrom.a",
+        )
+
+    def test_against_cloned_non_table(self):
+        # test that corresponding column digs across
+        # clone boundaries with anonymous labeled elements
+        col = func.count().label("foo")
+        sel = select([col])
+
+        sel2 = visitors.ReplacingCloningVisitor().traverse(sel)
+        with testing.expect_deprecated("The SelectBase.c"):
+            assert (
+                sel2._implicit_subquery.corresponding_column(col) is sel2.c.foo
+            )
+
+        sel3 = visitors.ReplacingCloningVisitor().traverse(sel2)
+        with testing.expect_deprecated("The SelectBase.c"):
+            assert (
+                sel3._implicit_subquery.corresponding_column(col) is sel3.c.foo
+            )
+
+    def test_alias_union(self):
+
+        # same as testunion, except its an alias of the union
+
+        u = (
+            select(
+                [
+                    self.table1.c.col1,
+                    self.table1.c.col2,
+                    self.table1.c.col3,
+                    self.table1.c.colx,
+                    null().label("coly"),
+                ]
+            )
+            .union(
+                select(
+                    [
+                        self.table2.c.col1,
+                        self.table2.c.col2,
+                        self.table2.c.col3,
+                        null().label("colx"),
+                        self.table2.c.coly,
+                    ]
+                )
+            )
+            .alias("analias")
+        )
+        s1 = self.table1.select(use_labels=True)
+        s2 = self.table2.select(use_labels=True)
+        with self._c_deprecated():
+            assert u.corresponding_column(s1.c.table1_col2) is u.c.col2
+            assert u.corresponding_column(s2.c.table2_col2) is u.c.col2
+            assert u.corresponding_column(s2.c.table2_coly) is u.c.coly
+            assert s2.c.corresponding_column(u.c.coly) is s2.c.table2_coly
+
+    def test_join_against_self_implicit_subquery(self):
+        jj = select([self.table1.c.col1.label("bar_col1")])
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns attributes are "
+            "deprecated and will be removed",
+            "Implicit coercion of SELECT",
+        ):
+            jjj = join(self.table1, jj, self.table1.c.col1 == jj.c.bar_col1)
+
+        jjj_bar_col1 = jjj.c["%s_bar_col1" % jj._implicit_subquery.name]
+        assert jjj_bar_col1 is not None
+
+        # test column directly against itself
+
+        assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns attributes are "
+            "deprecated and will be removed"
+        ):
+            assert jjj.corresponding_column(jj.c.bar_col1) is jjj_bar_col1
+
+        # test alias of the join
+
+        j2 = jjj.alias("foo")
+        assert j2.corresponding_column(self.table1.c.col1) is j2.c.table1_col1
+
+    def test_select_labels(self):
+        a = self.table1.select(use_labels=True)
+        j = join(a._implicit_subquery, self.table2)
+
+        criterion = a._implicit_subquery.c.table1_col1 == self.table2.c.col2
+        self.assert_(criterion.compare(j.onclause))
+
+
+class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    def test_literal_column_label_embedded_select_samename_explcit_quote(self):
+        col = sql.literal_column("NEEDS QUOTES").label(
+            quoted_name("NEEDS QUOTES", True)
+        )
+
+        with testing.expect_deprecated(
+            r"The SelectBase.select\(\) method is deprecated"
+        ):
+            self.assert_compile(
+                select([col]).select(),
+                'SELECT anon_1."NEEDS QUOTES" FROM '
+                '(SELECT NEEDS QUOTES AS "NEEDS QUOTES") AS anon_1',
+            )
+
+    def test_literal_column_label_embedded_select_diffname_explcit_quote(self):
+        col = sql.literal_column("NEEDS QUOTES").label(
+            quoted_name("NEEDS QUOTES_", True)
+        )
+
+        with testing.expect_deprecated(
+            r"The SelectBase.select\(\) method is deprecated"
+        ):
+            self.assert_compile(
+                select([col]).select(),
+                'SELECT anon_1."NEEDS QUOTES_" FROM '
+                '(SELECT NEEDS QUOTES AS "NEEDS QUOTES_") AS anon_1',
+            )
+
+    def test_literal_column_label_embedded_select_diffname(self):
+        col = sql.literal_column("NEEDS QUOTES").label("NEEDS QUOTES_")
+
+        with testing.expect_deprecated(
+            r"The SelectBase.select\(\) method is deprecated"
+        ):
+            self.assert_compile(
+                select([col]).select(),
+                'SELECT anon_1."NEEDS QUOTES_" FROM (SELECT NEEDS QUOTES AS '
+                '"NEEDS QUOTES_") AS anon_1',
+            )
+
+    def test_literal_column_label_embedded_select_samename(self):
+        col = sql.literal_column("NEEDS QUOTES").label("NEEDS QUOTES")
+
+        with testing.expect_deprecated(
+            r"The SelectBase.select\(\) method is deprecated"
+        ):
+            self.assert_compile(
+                select([col]).select(),
+                'SELECT anon_1."NEEDS QUOTES" FROM (SELECT NEEDS QUOTES AS '
+                '"NEEDS QUOTES") AS anon_1',
+            )
+
+
+class TextualSelectTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    table1 = table(
+        "mytable",
+        column("myid", Integer),
+        column("name", String),
+        column("description", String),
+    )
+
+    table2 = table(
+        "myothertable", column("otherid", Integer), column("othername", String)
+    )
+
+    def test_basic_subquery_resultmap(self):
+        table1 = self.table1
+        t = text("select id, name from user").columns(id=Integer, name=String)
+
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns", "Implicit coercion"
+        ):
+            stmt = select([table1.c.myid]).select_from(
+                table1.join(t, table1.c.myid == t.c.id)
+            )
+        compiled = stmt.compile()
+        eq_(
+            compiled._create_result_map(),
+            {
+                "myid": (
+                    "myid",
+                    (table1.c.myid, "myid", "myid"),
+                    table1.c.myid.type,
+                )
+            },
+        )
+
+    def test_column_collection_ordered(self):
+        t = text("select a, b, c from foo").columns(
+            column("a"), column("b"), column("c")
+        )
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns"
+        ):
+            eq_(t.c.keys(), ["a", "b", "c"])
+
+    def test_column_collection_pos_plus_bykey(self):
+        # overlapping positional names + type names
+        t = text("select a, b, c from foo").columns(
+            column("a"), column("b"), b=Integer, c=String
+        )
+
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns"
+        ):
+            eq_(t.c.keys(), ["a", "b", "c"])
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns"
+        ):
+            eq_(t.c.b.type._type_affinity, Integer)
+        with testing.expect_deprecated(
+            "The SelectBase.c and SelectBase.columns"
+        ):
+            eq_(t.c.c.type._type_affinity, String)