graceful degrade for FKs not reflectable

Fixed bugs involving the :paramref:`.Table.include_columns` and the
:paramref:`.Table.resolve_fks` parameters on :class:`.Table`; these
little-used parameters were apparently not working for columns that refer
to foreign key constraints.

In the first case, not-included columns that refer to foreign keys would
still attempt to create a :class:`.ForeignKey` object, producing errors
when attempting to resolve the columns for the foreign key constraint
within reflection; foreign key constraints that refer to skipped columns
are now omitted from the table reflection process in the same way as
occurs for :class:`.Index` and :class:`.UniqueConstraint` objects with the
same conditions. No warning is produced however, as we likely want to
remove the include_columns warnings for all constraints in 2.0.

In the latter case, the production of table aliases or subqueries would
fail on an FK related table not found despite the presence of
``resolve_fks=False``; the logic has been repaired so that if a related
table is not found, the :class:`.ForeignKey` object is still proxied to the
aliased table or subquery (these :class:`.ForeignKey` objects are normally
used in the production of join conditions), but it is sent with a flag that
it's not resolvable. The aliased table / subquery will then work normally,
with the exception that it cannot be used to generate a join condition
automatically, as the foreign key information is missing. This was already
the behavior for such foreign key constraints produced using non-reflection
methods, such as joining :class:`.Table` objects from different
:class:`.MetaData` collections.

Fixes: #8100
Fixes: #8101

Change-Id: Ifa37a91bd1f1785fca85ef163eec031660d9ea4d

2 files changed, 39 insertions, 4 deletions

diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 447e102ed..c37b60003 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2271,10 +2271,19 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
         information is not transferred.
 
         """
+
         fk = [
-            ForeignKey(f.column, _constraint=f.constraint)
-            for f in self.foreign_keys
+            ForeignKey(
+                col if col is not None else f._colspec,
+                _unresolvable=col is None,
+                _constraint=f.constraint,
+            )
+            for f, col in [
+                (fk, fk._resolve_column(raiseerr=False))
+                for fk in self.foreign_keys
+            ]
         ]
+
         if name is None and self.name is None:
             raise exc.InvalidRequestError(
                 "Cannot initialize a sub-selectable"
@@ -2375,6 +2384,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
         link_to_name: bool = False,
         match: Optional[str] = None,
         info: Optional[_InfoType] = None,
+        _unresolvable: bool = False,
         **dialect_kw: Any,
     ):
         r"""
@@ -2448,6 +2458,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
         """
 
         self._colspec = coercions.expect(roles.DDLReferredColumnRole, column)
+        self._unresolvable = _unresolvable
 
         if isinstance(self._colspec, str):
             self._table_column = None
@@ -2658,6 +2669,11 @@ class ForeignKey(DialectKWArgs, SchemaItem):
 
         parenttable = self.parent.table
 
+        if self._unresolvable:
+            schema, tname, colname = self._column_tokens
+            tablekey = _get_table_key(tname, schema)
+            return parenttable, tablekey, colname
+
         # assertion
         # basically Column._make_proxy() sends the actual
         # target Column to the ForeignKey object, so the
@@ -2742,13 +2758,30 @@ class ForeignKey(DialectKWArgs, SchemaItem):
 
         """
 
+        return self._resolve_column()
+
+    @overload
+    def _resolve_column(self, *, raiseerr: Literal[True] = ...) -> Column[Any]:
+        ...
+
+    @overload
+    def _resolve_column(
+        self, *, raiseerr: bool = ...
+    ) -> Optional[Column[Any]]:
+        ...
+
+    def _resolve_column(
+        self, *, raiseerr: bool = True
+    ) -> Optional[Column[Any]]:
         _column: Column[Any]
 
         if isinstance(self._colspec, str):
 
             parenttable, tablekey, colname = self._resolve_col_tokens()
 
-            if tablekey not in parenttable.metadata:
+            if self._unresolvable or tablekey not in parenttable.metadata:
+                if not raiseerr:
+                    return None
                 raise exc.NoReferencedTableError(
                     "Foreign key associated with column '%s' could not find "
                     "table '%s' with which to generate a "
@@ -2757,6 +2790,8 @@ class ForeignKey(DialectKWArgs, SchemaItem):
                     tablekey,
                 )
             elif parenttable.key not in parenttable.metadata:
+                if not raiseerr:
+                    return None
                 raise exc.InvalidRequestError(
                     "Table %s is no longer associated with its "
                     "parent MetaData" % parenttable
diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index 390e23952..0400ab3fe 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -235,7 +235,7 @@ def find_left_clause_to_join_from(
                 if set(f.c).union(s.c).issuperset(cols_in_onclause):
                     idx.append(i)
                     break
-            elif Join._can_join(f, s) or onclause is not None:
+            elif onclause is not None or Join._can_join(f, s):
                 idx.append(i)
                 break