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

1 files changed, 9 insertions, 5 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_results.py b/lib/sqlalchemy/testing/suite/test_results.py
index 6c2880ad4..e8ad88f24 100644
--- a/lib/sqlalchemy/testing/suite/test_results.py
+++ b/lib/sqlalchemy/testing/suite/test_results.py
@@ -333,14 +333,18 @@ class ServerSideCursorsTest(
 
     def test_aliases_and_ss(self):
         engine = self._fixture(False)
-        s1 = select(1).execution_options(stream_results=True).alias()
+        s1 = (
+            select(sql.literal_column("1").label("x"))
+            .execution_options(stream_results=True)
+            .subquery()
+        )
+
+        # options don't propagate out when subquery is used as a FROM clause
         with engine.begin() as conn:
-            result = conn.execute(s1)
-            assert self._is_server_side(result.cursor)
+            result = conn.execute(s1.select())
+            assert not self._is_server_side(result.cursor)
             result.close()
 
-        # s1's options shouldn't affect s2 when s2 is used as a
-        # from_obj.
         s2 = select(1).select_from(s1)
         with engine.begin() as conn:
             result = conn.execute(s2)