Issue: Aliases, group by and count
Author: ak4nv
State: closed
Created: 2018-11-13 11:15:04

Hi @coleifer !

I have a little bit complex query with some joins, aliases, and group by clause (generates a query on the fly). And I found when I try to call a `qs.count()` method I've got an exception:
```
peewee.ProgrammingError: ОШИБКА:  столбец "external_source" не существует
LINE 1: ...ROUP BY "t1"."id", "t1"."id", "t1"."external_id", "external_...
                                                             ^
HINT:  Возможно, предполагалась ссылка на столбец "t1.external_source_id".
```
> column "external_source" does not exist. Perhaps you meant to reference the column "t1.external_source_id" 

This code generates by peewee:

> `('SELECT "t1"."id", "t1"."external_id", "t2"."name" AS "external_source", "t3"."name" AS "genus", "t1"."isolate_strain_id", "t1"."name", "t1"."ncbi_sequence_id", string_agg("subq"."name", %s) AS "samples", "t4"."name" AS "species", "t1"."synonyms" FROM "hosts" AS "t1" LEFT OUTER JOIN (SELECT "t5"."id", "t5"."name", "t6"."host_id", "t6"."phage_id" FROM "samples" AS "t5" INNER JOIN "source_samples" AS "t6" ON ("t6"."sample_id" = "t5"."id")) AS "subq" ON ("t1"."id" = "subq"."host_id") LEFT OUTER JOIN "taxonomies_names" AS "t3" ON (("t3"."tax_id" = "t1"."genus_id") AND ("t3"."name_class" = %s)) LEFT OUTER JOIN "taxonomies_names" AS "t4" ON (("t4"."tax_id" = "t1"."species_id") AND ("t4"."name_class" = %s)) LEFT OUTER JOIN "simple_entities" AS "t2" ON ("t1"."external_source_id" = "t2"."id") WHERE (text("t1"."id") ILIKE %s) GROUP BY "t1"."id", "t1"."id", "t1"."external_id", "external_source", "genus", "t1"."isolate_strain_id", "t1"."name", "t1"."ncbi_sequence_id", "species", "t1"."synonyms" ORDER BY "t1"."name"', [',', 'scientific name', 'scientific name', '%%'])`

and this code gererates after `qs.count()` calling:

> `('SELECT COUNT(1) FROM (SELECT 1 FROM "hosts" AS "t1" LEFT OUTER JOIN (SELECT "t2"."id", "t2"."name", "t3"."host_id", "t3"."phage_id" FROM "samples" AS "t2" INNER JOIN "source_samples" AS "t3" ON ("t3"."sample_id" = "t2"."id")) AS "subq" ON ("t1"."id" = "subq"."host_id") LEFT OUTER JOIN "taxonomies_names" AS "t4" ON (("t4"."tax_id" = "t1"."genus_id") AND ("t4"."name_class" = ?)) LEFT OUTER JOIN "taxonomies_names" AS "t5" ON (("t5"."tax_id" = "t1"."species_id") AND ("t5"."name_class" = ?)) LEFT OUTER JOIN "simple_entities" AS "t6" ON ("t1"."external_source_id" = "t6"."id") WHERE (text("t1"."id") ILIKE ?) GROUP BY "t1"."id", "t1"."id", "t1"."external_id", "external_source", "genus", "t1"."isolate_strain_id", "t1"."name", "t1"."ncbi_sequence_id", "species", "t1"."synonyms") AS "_wrapped"'  , ['scientific name', 'scientific name', '%%'])`

It seems in the `SELECT` section aliases are gone. I wrote my own wrapper for counter
```python
counter = db.Model.select(fn.count('*')).from_(qs).scalar()
```
but maybe it's a bug?

I see -- good work on finding a workaround.

Looks to me like a bug. Peewee is being too aggressive about stripping out the selected columns. The logic in the code reads:

```python
        clone = self.order_by().alias('_wrapped')
        if clear_limit:
            clone._limit = clone._offset = None
        try:
            if clone._having is None and clone._windows is None and \
               clone._distinct is None and clone._simple_distinct is not True:
                clone = clone.select(SQL('1'))
        except AttributeError:
            pass
        return Select([clone], [fn.COUNT(SQL('1'))]).scalar(database)
```

Probably out to be:

```python
if clone._having is None and clone._windows is None and \
    clone._distinct is None and clone._simple_distinct is not True and \
    <we're not doing any important joins>:
```

Since the problem seems to be the removal of the columns which are used for joining subqueries later.

Maybe the issue is the group by not lining up actually...

I think the patch from cb6cc1d should address the issue, thanks for reporting.