SQLAlchemy should throw an exception if there are multiple foreign keys to relate

[Former user]

The attached code generates something like this:

SELECT "group".id AS group_id FROM "group" JOIN (SELECT "user".type AS type, deriveduser.id AS id, "user".id AS id, deriveduser.preference1_id AS preference1_id, deriveduser.preference2_id AS preference2_id, deriveduser.preference3_id AS preference3_id, deriveduser.group_id AS group_id, count(:param_1) AS deriveduser_count FROM "user" JOIN deriveduser ON "user".id = deriveduser.id GROUP BY deriveduser.group_id) AS anon_1 ON "group".id = anon_1.preference1_id AND "group".id = anon_1.preference2_id AND "group".id = anon_1.preference3_id AND "group".id = anon_1.group_id

Isn't this actually ill behavior that should throw an exception if the join is ambiguous as it is in this case?

[Former user]

File demonstrating the problem

[Mike Bayer]

yeah wow, weird how nobody finds these for so long.

Here's that:

from sqlalchemy import *

m = MetaData()

t1 = Table('t', m,
    Column('id', Integer, primary_key=True)
    )

t2 = Table('t2', m,
    Column('id1', ForeignKey('t.id')),
    Column('id2', ForeignKey('t.id')),
)

s = select([t2](t2)).alias()

print t1.join(s)

here's the patch:

index 3a74cbd..1febb1b 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -1134,7 +1134,9 @@ class Column(SchemaItem, expression.ColumnClause):
         information is not transferred.

         """
-        fk = [for f in self.foreign_keys](ForeignKey(f.column))
+
+        fk = [_constraint=f.constraint)
+                    for f in self.foreign_keys](ForeignKey(f.column,)
         if name is None and self.name is None:
             raise exc.InvalidRequestError("Cannot initialize a sub-selectable"
                     " with this Column object until it's 'name' has "

need to also test print id(fk.constraint.elements[0](0)), etc. should go into test_selectable.

[Mike Bayer]

1fc2611e20ce94d3bbefc6910ba1127c400195b5 0.8

8a865a4d1f7bf7d399a551ae1c3f93 0.9

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)