reset_joinpoint used to join across two relations of same type fails

[Former user]

As far as I can tell, when I use reset_joinpoint to join using two relations (created by Elixir), the second join is actually ignored.

This is in SQLAlchemy 0.3.9. Please see the attached TestCase.

[Mike Bayer]

because join() in 0.3 does not support automatic aliasing, it would be impossible here to create two separate joins to bar; because bar is already present as a result of the first join, join() is smart enough to figure out that it should not join from foo to bar a second time, since that would produce invalid SQL.

in 0.4, you would simply apply "aliased=True" to one or both join() statements and two individual paths to bar, each with its own aliased version of bar, will be created.

as a workaround in 0.3, use an explicit join constructed from Alias objects.

[Mike Bayer]

more specifically: assume this change to the test script for readability:

class Foo(Entity):
  using_options(tablename='foo')
  # ...

class Bar(Entity):
  using_options(tablename='bar')
  # ...

if I turn off the "check for an existing join" check in 0.3, you get this:

SELECT foo.the_other AS foo_the_other, foo.that AS foo_that, foo.this AS foo_this, foo.id AS foo_id 
FROM foo JOIN foo_bars1__bar_foos1 ON foo.id = foo_bars1__bar_foos1.foo_id JOIN bar ON bar.id = foo_bars1__bar_foos1.bar_id JOIN foo_bars2__bar_foos2 ON foo.id = foo_bars2__bar_foos2.foo_id JOIN bar ON bar.id = foo_bars2__bar_foos2.bar_id 
WHERE bar.mimsy = ? AND bar.mimsy = ? ORDER BY foo.oid
['mimsy2']('mimsy1',)

SQLError: (OperationalError) ambiguous column name: bar.id u'SELECT foo.the_other AS foo_the_other, foo.that AS foo_that, foo.this AS foo_this, foo.id AS foo_id \nFROM foo JOIN foo_bars1__bar_foos1 ON foo.id = foo_bars1__bar_foos1.foo_id JOIN bar ON bar.id = foo_bars1__bar_foos1.bar_id JOIN foo_bars2__bar_foos2 ON foo.id = foo_bars2__bar_foos2.foo_id JOIN bar ON bar.id = foo_bars2__bar_foos2.bar_id \nWHERE bar.mimsy = ? AND bar.mimsy = ? ORDER BY foo.oid' ['mimsy2']('mimsy1',)

[Mike Bayer]

will add an error raise in 0.3 and 0.4 (different messages)

[Mike Bayer]

ive put error messages for this specific condition in changeset:3057 and changeset:3058. however the join() method should be expanded out to track each full join path so that any conflicts, not just two different m2m tables, should be detected.

[Mike Bayer]

#688 is a 0.4xx milestone ticket for the full solution.

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)