allow Executable to be accepted by Session.execute()

Fixed an issue where the API to create a custom executable SQL construct
using the ``sqlalchemy.ext.compiles`` extension according to the
documentation that's been up for many years would no longer function if
only ``Executable, ClauseElement`` were used as the base classes,
additional classes were needed if wanting to use
:meth:`_orm.Session.execute`. This has been resolved so that those extra
classes aren't needed.

Change-Id: I99b8acd88515c2a52842d62974199121e64c0381

4 files changed, 84 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_14/simplify_executable.rst b/doc/build/changelog/unreleased_14/simplify_executable.rst
new file mode 100644
index 000000000..6c91c13d0
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/simplify_executable.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, orm
+
+    Fixed an issue where the API to create a custom executable SQL construct
+    using the ``sqlalchemy.ext.compiles`` extension according to the
+    documentation that's been up for many years would no longer function if
+    only ``Executable, ClauseElement`` were used as the base classes,
+    additional classes were needed if wanting to use
+    :meth:`_orm.Session.execute`. This has been resolved so that those extra
+    classes aren't needed.
diff --git a/lib/sqlalchemy/sql/base.py b/lib/sqlalchemy/sql/base.py
index a1426b628..550111020 100644
--- a/lib/sqlalchemy/sql/base.py
+++ b/lib/sqlalchemy/sql/base.py
@@ -729,7 +729,7 @@ class ExecutableOption(HasCopyInternals, HasCacheKey):
         return c
 
 
-class Executable(Generative):
+class Executable(roles.CoerceTextStatementRole, Generative):
     """Mark a :class:`_expression.ClauseElement` as supporting execution.
 
     :class:`.Executable` is a superclass for all "statement" types
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index d3c767b5d..5ea3526ea 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1502,7 +1502,6 @@ class TextClause(
     roles.OrderByRole,
     roles.FromClauseRole,
     roles.SelectStatementRole,
-    roles.CoerceTextStatementRole,
     roles.BinaryElementRole,
     roles.InElementRole,
     Executable,
diff --git a/test/ext/test_compiler.py b/test/ext/test_compiler.py
index c10e27180..534b839be 100644
--- a/test/ext/test_compiler.py
+++ b/test/ext/test_compiler.py
@@ -7,10 +7,13 @@ from sqlalchemy import literal_column
 from sqlalchemy import MetaData
 from sqlalchemy import Numeric
 from sqlalchemy import select
+from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import table
+from sqlalchemy import testing
 from sqlalchemy.ext.compiler import compiles
 from sqlalchemy.ext.compiler import deregister
+from sqlalchemy.orm import Session
 from sqlalchemy.schema import CreateColumn
 from sqlalchemy.schema import CreateTable
 from sqlalchemy.schema import DDLElement
@@ -18,6 +21,7 @@ from sqlalchemy.sql.elements import ColumnElement
 from sqlalchemy.sql.expression import BindParameter
 from sqlalchemy.sql.expression import ClauseElement
 from sqlalchemy.sql.expression import ColumnClause
+from sqlalchemy.sql.expression import Executable
 from sqlalchemy.sql.expression import FunctionElement
 from sqlalchemy.sql.expression import Select
 from sqlalchemy.sql.sqltypes import NULLTYPE
@@ -491,3 +495,72 @@ class DefaultOnExistingTest(fixtures.TestBase, AssertsCompiledSQL):
             {"a": 1, "b": 2},
             use_default_dialect=True,
         )
+
+
+class ExecuteTest(fixtures.TablesTest):
+    """test that Executable constructs work at a rudimentary level."""
+
+    __requires__ = ("standard_cursor_sql",)
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "some_table",
+            metadata,
+            Column("id", Integer, primary_key=True, autoincrement=False),
+            Column("data", String(50)),
+        )
+
+    @testing.fixture()
+    def insert_fixture(self):
+        class MyInsert(Executable, ClauseElement):
+            pass
+
+        @compiles(MyInsert)
+        def _run_myinsert(element, compiler, **kw):
+            return "INSERT INTO some_table (id, data) VALUES(1, 'some data')"
+
+        return MyInsert
+
+    @testing.fixture()
+    def select_fixture(self):
+        class MySelect(Executable, ClauseElement):
+            pass
+
+        @compiles(MySelect)
+        def _run_myinsert(element, compiler, **kw):
+            return "SELECT id, data FROM some_table"
+
+        return MySelect
+
+    def test_insert(self, connection, insert_fixture):
+        connection.execute(insert_fixture())
+
+        some_table = self.tables.some_table
+        eq_(connection.scalar(select(some_table.c.data)), "some data")
+
+    def test_insert_session(self, connection, insert_fixture):
+        with Session(connection) as session:
+            session.execute(insert_fixture())
+
+        some_table = self.tables.some_table
+
+        eq_(connection.scalar(select(some_table.c.data)), "some data")
+
+    def test_select(self, connection, select_fixture):
+        some_table = self.tables.some_table
+
+        connection.execute(some_table.insert().values(id=1, data="some data"))
+        result = connection.execute(select_fixture())
+
+        eq_(result.first(), (1, "some data"))
+
+    def test_select_session(self, connection, select_fixture):
+        some_table = self.tables.some_table
+
+        connection.execute(some_table.insert().values(id=1, data="some data"))
+
+        with Session(connection) as session:
+            result = session.execute(select_fixture())
+
+            eq_(result.first(), (1, "some data"))