Issue: follow_joins - assigns duplicate aliases during complex joins
Author: sdobz
State: closed
Created: 2012-05-29 07:32:49

I am doing something ugly (view support) and in the process of building some automated joins I discovered that certain queries create duplicate aliases in a sql select.

``` python
from peewee import *

class Artist(Model):
    artistid = CharField(size=36,unique=True)
    name = CharField()
    sortname = CharField()
    disambiguation = CharField()

class Track(Model):
    trackid = CharField(size=36,unique=True)
    title = CharField()
    artist = ForeignKeyField(Artist)

class Release(Model):
    releaseid = CharField(db_index=True, size=36)
    title = CharField()
    artist = ForeignKeyField(Artist)

class ReleaseTrack(Model):
    track = ForeignKeyField(Track)
    release = ForeignKeyField(Release)
    tracknumber = IntegerField(size=2)

print("Multiple joins before a single join cause errors")
print(Track.select().join(ReleaseTrack).join(Release).switch(Track).join(Artist).sql())

print("Single join before multiple is OK")
print(Track.select().join(Artist).switch(Track).join(ReleaseTrack).join(Release).sql())
```

Outputs:

```
Multiple joins before a single join cause errors
('SELECT t1."id", t1."trackid", t1."title", t1."artist_id" FROM "track" AS t1 INNER JOIN "releasetrack" AS t2 ON t1."id" = t2."track_id"\nINNER JOIN "release" AS t3 ON t2."release_id" = t3."id"\nINNER JOIN "artist" AS t3 ON t1."artist_id" = t3."id"', [])
Single join before multiple is OK
('SELECT t1."id", t1."trackid", t1."title", t1."artist_id" FROM "track" AS t1 INNER JOIN "artist" AS t2 ON t1."artist_id" = t2."id"\nINNER JOIN "releasetrack" AS t3 ON t1."id" = t3."track_id"\nINNER JOIN "release" AS t4 ON t3."release_id" = t4."id"', [])
```

Notice that on the first both artist and release are aliased to t3, but on the second it goes up to t4.

I've tracked down a cause. In the function follow_joins (~line 1114) you recurse and call the function again (~line 1159), passing in alias_count. This function call may in fact increment alias_count, but though alias_map may be modified, once it pops back out and run the for loop again alias_count does not reflect the change.

In order to work around this I've implemented a truly hacky fix and instead of starting with alias_count = 0 I use alias_count = [0] and always reference alias_count[0], but this is ugly and horrible.

``` diff
--- peewee_fresh.py     2012-05-29 00:29:16.997304000 -0700
+++ library/peewee.py   2012-05-29 00:25:09.429190000 -0700
@@ -1125,8 +1125,8 @@
                 if model in self._table_alias:
                     alias_map[model] = self._table_alias[model]
                 else:
-                    alias_count += 1
-                    alias_map[model] = 't%d' % alias_count
+                    alias_count[0] += 1
+                    alias_map[model] = 't%d' % alias_count[0]
             else:
                 alias_map[model] = ''

@@ -1161,7 +1161,7 @@
         return computed

     def compile_where(self):
-        alias_count = 0
+        alias_count = [0]
         alias_map = {}

         alias_required = self.use_aliases()
@@ -1169,8 +1169,8 @@
             if self.model in self._table_alias:
                 alias_map[self.model] = self._table_alias[self.model]
             else:
-                alias_count += 1
-                alias_map[self.model] = 't%d' % alias_count
+                alias_count[0] += 1
+                alias_map[self.model] = 't%d' % alias_count[0]
         else:
             alias_map[self.model] = ''
```

After the fix:

```
Multiple joins before a single join cause errors
('SELECT t1."id", t1."trackid", t1."title", t1."artist_id" FROM "track" AS t1 INNER JOIN "releasetrack" AS t2 ON t1."id" = t2."track_id"\nINNER JOIN "release" AS t3 ON t2."release_id" = t3."id"\nINNER JOIN "artist" AS t4 ON t1."artist_id" = t4."id"', [])
Single join before multiple is OK
('SELECT t1."id", t1."trackid", t1."title", t1."artist_id" FROM "track" AS t1 INNER JOIN "artist" AS t2 ON t1."artist_id" = t2."id"\nINNER JOIN "releasetrack" AS t3 ON t1."id" = t3."track_id"\nINNER JOIN "release" AS t4 ON t3."release_id" = t4."id"', [])
```


Thanks so much for the excellent bug report, I've added a fix and a testcase.