skip ORM loading setups for non-toplevel DML

Fixed regression where use of :func:`_dml.update` or :func:`_dml_delete`
within a :class:`_sql.CTE` construct, then used in a :func:`_sql.select`,
would raise a :class:`.CompileError` as a result of ORM related rules for
performing ORM-level update/delete statements.

Fixes: #9767
Change-Id: I4eae9af86752b2e5fd64f7998f8a68754c349e4c

1 files changed, 34 insertions, 0 deletions

diff --git a/test/orm/test_core_compilation.py b/test/orm/test_core_compilation.py
index 6736d5589..8b28de591 100644
--- a/test/orm/test_core_compilation.py
+++ b/test/orm/test_core_compilation.py
@@ -360,6 +360,40 @@ class SelectableTest(QueryTest, AssertsCompiledSQL):
         )
 
 
+class DMLTest(QueryTest, AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    @testing.variation("stmt_type", ["update", "delete"])
+    def test_dml_ctes(self, stmt_type: testing.Variation):
+        User = self.classes.User
+
+        if stmt_type.update:
+            fn = update
+        elif stmt_type.delete:
+            fn = delete
+        else:
+            stmt_type.fail()
+
+        inner_cte = fn(User).returning(User.id).cte("uid")
+
+        stmt = select(inner_cte)
+
+        if stmt_type.update:
+            self.assert_compile(
+                stmt,
+                "WITH uid AS (UPDATE users SET id=:id, name=:name "
+                "RETURNING users.id) SELECT uid.id FROM uid",
+            )
+        elif stmt_type.delete:
+            self.assert_compile(
+                stmt,
+                "WITH uid AS (DELETE FROM users "
+                "RETURNING users.id) SELECT uid.id FROM uid",
+            )
+        else:
+            stmt_type.fail()
+
+
 class ColumnsClauseFromsTest(QueryTest, AssertsCompiledSQL):
     __dialect__ = "default"