raise informative error when selectable can't be extended

An informative error is raised for the use case where
:meth:`.Insert.from_select` is being passed a "compound select" object such
as a UNION, yet the INSERT statement needs to append additional columns to
support Python-side or explicit SQL defaults from the table metadata. In
this case a subquery of the compound object should be passed.

Fixes: #8073
Change-Id: Ic4a5dbf84ec49d2451901be05cb9cf6ae93f02b7

1 files changed, 15 insertions, 3 deletions

diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index b0cb3cbf8..913e4d433 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -35,6 +35,7 @@ from . import roles
 from .elements import ColumnClause
 from .schema import default_is_clause_element
 from .schema import default_is_sequence
+from .selectable import Select
 from .selectable import TableClause
 from .. import exc
 from .. import util
@@ -486,10 +487,21 @@ def _scan_insert_from_select_cols(
     if add_select_cols:
         values.extend(add_select_cols)
         ins_from_select = compiler.stack[-1]["insert_from_select"]
+        if not isinstance(ins_from_select, Select):
+            raise exc.CompileError(
+                f"Can't extend statement for INSERT..FROM SELECT to include "
+                f"additional default-holding column(s) "
+                f"""{
+                    ', '.join(repr(key) for _, key, _ in add_select_cols)
+                }.  Convert the selectable to a subquery() first, or pass """
+                "include_defaults=False to Insert.from_select() to skip these "
+                "columns."
+            )
         ins_from_select = ins_from_select._generate()
-        ins_from_select._raw_columns = tuple(
-            ins_from_select._raw_columns
-        ) + tuple(expr for col, col_expr, expr in add_select_cols)
+        # copy raw_columns
+        ins_from_select._raw_columns = list(ins_from_select._raw_columns) + [
+            expr for col, col_expr, expr in add_select_cols
+        ]
         compiler.stack[-1]["insert_from_select"] = ins_from_select