Repository: kayak/pypika (python)

Issue:
When nesting several queries, aliases are reused
The query is:

```sql
SELECT COALESCE(SUM("sq0"."expected"),0) "expected"
FROM (
    (
        SELECT SUM("sq0"."view_count") FROM (
            SELECT MIN("user_queries"."view_count")
            FROM "user_queries"
            JOIN (
                SELECT "user_query_word"."user_query_id","user_query_word"."language"
                FROM "words" JOIN "user_query_word" USING ("word_id")
                WHERE "words"."word"='best'
            ) "sq0" USING ("user_query_id")
            JOIN (
                SELECT "user_query_word"."user_query_id","user_query_word"."language" 
                FROM "words" 
                JOIN "user_query_word" USING ("word_id")
                WHERE "words"."word"='mortgage'
            ) "sq1" USING ("user_query_id")
            WHERE "user_queries"."user_query"<>'bestmortgage' AND "sq0"."language"="sq1"."language" 
            GROUP BY "user_queries"."user_query_id"
        ) "sq0"
    ) UNION ALL (
        SELECT "view_count"*1.0 "expected" FROM "user_queries" WHERE "user_query"='bestmortgage'
    )
) "sq0"
```

And the error:

```
psycopg2.ProgrammingError: column sq0.view_count does not exist
LINE 1: ..."sq0"."expected"),0) "expected" FROM ((SELECT SUM("sq0"."vie...
```

Pull request:
Fixed #98 check subquery counts when creating alias

Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index 1a3e7b9..f184781 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -353,8 +353,14 @@ class QueryBuilder(Selectable, Term):
         self._from.append(Table(selectable) if isinstance(selectable, str) else selectable)
 
         if isinstance(selectable, (QueryBuilder, _UnionQuery)) and selectable.alias is None:
-            selectable.alias = 'sq%d' % self._subquery_count
-            self._subquery_count += 1
+            if isinstance(selectable, QueryBuilder):
+                sub_query_count = selectable._subquery_count
+            else:
+                sub_query_count = 0
+
+            sub_query_count = max(self._subquery_count, sub_query_count)
+            selectable.alias = 'sq%d' % sub_query_count
+            self._subquery_count = sub_query_count + 1
 
     @builder
     def into(self, table):


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 589dd88..b8f4cbf 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -107,6 +107,21 @@ class SelectTests(unittest.TestCase):
                          'FROM (SELECT "foo" FROM "abc") "sq0",'
                          '(SELECT "bar" FROM "efg") "sq1"', str(q))
 
+    def test_select__nested_subquery(self):
+        subquery0 = Query.from_(self.table_abc).select("*")
+        subquery1 = Query.from_(subquery0) \
+            .select(subquery0.foo, subquery0.bar)
+        subquery2 = Query.from_(subquery1) \
+            .select(subquery1.foo)
+
+        q = Query.from_(subquery2) \
+            .select(subquery2.foo)
+
+        self.assertEqual('SELECT "sq2"."foo" '
+                         'FROM (SELECT "sq1"."foo" '
+                         'FROM (SELECT "sq0"."foo","sq0"."bar" '
+                         'FROM (SELECT * FROM "abc") "sq0") "sq1") "sq2"', str(q))
+
     def test_select__no_table(self):
         q = Query.select(1, 2, 3)