Duplicate table in JOINs when using more than one Proxy attribute in the same class

[Edoardo Serra]

Hi, I just came across a bug in storm against the current trunk.
Some unit tests to reproduce it are following.

class Foo(object):
    __storm_table__ = "foo"
    id = Int(primary=True)
    title = Unicode()

class Baz(object):
    __storm_table__ = "baz"
    id = Int(primary=True)
    title = Unicode()

class FooBazProxy(object):
    __storm_table__ = "foobaz"
    id = Int(primary=True)
    foo_id = Int()
    foo = Reference(foo_id, Foo.id)
    foo_title = Proxy(foo, Foo.title)
    baz_id = Int()
    baz = Reference(baz_id, Baz.id)
    baz_title = Proxy(baz, Baz.title)

def test_multi_proxy(self):
    result = self.store.find(FooBazProxy).order_by(Baz.title, Foo.title)
    self.assertEquals(list(result.values(FooBazProxy.id,
        FooBazProxy.foo_title, FooBazProxy.baz_title)),
            [(3, u'Title 10', u'Title 100'),
                (2, u'Title 20', u'Title 100'),
                (1, u'Title 30', u'Title 200')])

This test generates the following query:

SELECT foobaz.id, foo.title, baz.title FROM foobaz LEFT JOIN baz ON foobaz.baz_id = baz.id, foobaz LEFT JOIN foo ON foobaz.foo_id = foo.id ORDER BY baz.title, foo.title

The above query fails on PostgreSQL, MySQL and SQLite with the following error messages:
 * PostgreSQL: ProgrammingError: table name "foobaz" specified more than once
 * MySQL: OperationalError: ambiguous column name: foobaz.id
 * SQLLite: OperationalError: (1066, "Not unique table/alias: 'foobaz'")

I'm working on a patch, hope to get back to you soon.

Regards

Edoardo

[Edoardo Serra (edoardo-serra)]

Here is my patch, including unit tests.

My 2 cents

[Edoardo Serra (edoardo-serra)]

Sorry guys, attached the wrong file.
Here is the correct one