sqlalchemy 1.0.0 does not allow specifying a join more than once

[Charles-Axel Dein]

I'm upgrading a codebase to sqlalchemy 1.0.0beta5 from 0.9.9.

It seems that version 1 removed the ability to be forgiving about mentioning a join twice:

session.query(Toaster).join(Toast).join(Toast).all()

This will raise with postgres: ProgrammingError: (psycopg2.ProgrammingError) table name "toasts" specified more than once

The same query run with sqla 0.9.9 runs fine, because I guess it removes the duplicate join.

I'm not sure that can be considered a regression given that joins should not be mentioned more than once, but it's definitely a different behavior from 0.9.9

[Mike Bayer]

Hmmm well I'd have to figure out what change that's part of. very odd 0.9 let that through.

[Mike Bayer]

0.9 seems to auto-alias the second join:

SELECT a.id 
FROM a JOIN b ON a.id = b.a_id JOIN b AS b_1 ON a.id = b_1.a_id

[Mike Bayer]

okey doke the commit is 445b9e2aff4e45a7756a and the change is in the migration notes at http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#single-inheritance-join-targets-will-no-longer-sometimes-implicitly-alias-themselves, though it is specific to single table inheritance, which clearly the change is more general than that so at the very least this change doc needs to be updated to reflect that.

[Mike Bayer]

there's an inconsistency in these two forms:

s.query(A).join(A.bs).join(A.bs)

s.query(A).join(B).join(B)

in that the first one, because it is "pathed", will dedupe, but the second, because it is just a FROM, will not. In fact the dedupe case should probably warn because that's not quite how that logic is supposed to be used.

[Mike Bayer]

• Identified an inconsistency when handling :meth:.Query.join to the same target more than once; it implicitly dedupes only in the case of a relationship join, and due to 3233, in 1.0 a join to the same table twice behaves differently than 0.9 in that it no longer erroneously aliases. To help document this change, the verbiage regarding 3233 in the migration notes has been generalized, and a warning has been added when :meth:.Query.join is called against the same target relationship more than once. fixes #3367

→ <<cset 2bc8c92279a1>>

[Mike Bayer]

thanks for reporting!

[Charles-Axel Dein]

Amazing. Thanks a lot Mike!