Disallow AliasedReturnsRows from execution

Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute`
is deprecated and will emit a deprecation warning; this use case was an
oversight that should have been removed from 1.4. The operation will now
execute the underlying :class:`_sql.Select` object directly for backwards
compatibility. Similarly, the :class:`_sql.CTE` class is also not
appropriate for execution. In 1.3, attempting to execute a CTE would result
in an invalid "blank" SQL statement being executed; since this use case was
not working it now raises :class:`_exc.ObjectNotExecutableError`.
Previously, 1.4 was attempting to execute the CTE as a statement however it
was working only erratically.

The change also breaks out StatementRole from ReturnsRowsRole, as these
roles should not be in the same lineage (some statements don't return
rows, the whole class of ReturnsRows that are from clauses are
not statements).    Consolidate StatementRole and
CoerceTextStatementRole as there's no usage difference between
these.   Simplify some old tests that were trying to make
sure that "execution options" didn't transmit from a cte/subquery
out to a select; as cte/subuqery() aren't executable in any case
the options are removed.

Fixes: #6204
Change-Id: I62613b7ab418afdd22c409eae75659e3f52fb65f

3 files changed, 11 insertions, 23 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index b2d443438..afa6aecf8 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -4550,20 +4550,20 @@ class ExecutionOptionsTest(fixtures.TestBase):
         eq_(compiled.execution_options, {"autocommit": True})
 
     def test_embedded_element_true_to_none(self):
-        stmt = table1.insert().cte()
+        stmt = table1.insert()
         eq_(stmt._execution_options, {"autocommit": True})
-        s2 = select(table1).select_from(stmt)
+        s2 = select(table1).select_from(stmt.cte())
         eq_(s2._execution_options, {})
 
         compiled = s2.compile()
         eq_(compiled.execution_options, {"autocommit": True})
 
     def test_embedded_element_true_to_false(self):
-        stmt = table1.insert().cte()
+        stmt = table1.insert()
         eq_(stmt._execution_options, {"autocommit": True})
         s2 = (
             select(table1)
-            .select_from(stmt)
+            .select_from(stmt.cte())
             .execution_options(autocommit=False)
         )
         eq_(s2._execution_options, {"autocommit": False})
diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py
index 92f3b215f..fc2c9b40d 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -1150,7 +1150,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
         products = table("products", column("id"), column("price"))
 
         cte = products.select().cte("pd")
-        assert "autocommit" not in cte._execution_options
+        assert "autocommit" not in cte.select()._execution_options
 
         stmt = products.update().where(products.c.price == cte.c.price)
         eq_(stmt.compile().execution_options["autocommit"], True)
diff --git a/test/sql/test_roles.py b/test/sql/test_roles.py
index e47a7e889..997311071 100644
--- a/test/sql/test_roles.py
+++ b/test/sql/test_roles.py
@@ -209,24 +209,14 @@ class RoleTest(fixtures.TestBase):
         ):
             expect(roles.ExpressionElementRole, t.select().alias())
 
-    def test_statement_no_text_coercion(self):
-        assert_raises_message(
-            exc.ArgumentError,
-            r"Textual SQL expression 'select \* from table' should be "
-            r"explicitly declared",
-            expect,
-            roles.StatementRole,
-            "select * from table",
-        )
-
     def test_statement_text_coercion(self):
         with testing.expect_deprecated_20(
             "Using plain strings to indicate SQL statements"
         ):
             is_true(
-                expect(
-                    roles.CoerceTextStatementRole, "select * from table"
-                ).compare(text("select * from table"))
+                expect(roles.StatementRole, "select * from table").compare(
+                    text("select * from table")
+                )
             )
 
     def test_select_statement_no_text_coercion(self):
@@ -282,13 +272,11 @@ class RoleTest(fixtures.TestBase):
         )
 
     def test_statement_coercion_select(self):
-        is_true(
-            expect(roles.CoerceTextStatementRole, select(t)).compare(select(t))
-        )
+        is_true(expect(roles.StatementRole, select(t)).compare(select(t)))
 
     def test_statement_coercion_ddl(self):
         d1 = DDL("hi")
-        is_(expect(roles.CoerceTextStatementRole, d1), d1)
+        is_(expect(roles.StatementRole, d1), d1)
 
     def test_strict_from_clause_role(self):
         stmt = select(t).subquery()
@@ -325,7 +313,7 @@ class RoleTest(fixtures.TestBase):
 
     def test_statement_coercion_sequence(self):
         s1 = Sequence("hi")
-        is_(expect(roles.CoerceTextStatementRole, s1), s1)
+        is_(expect(roles.StatementRole, s1), s1)
 
     def test_columns_clause_role(self):
         is_(expect(roles.ColumnsClauseRole, t.c.q), t.c.q)