Issue: KeyError in ModelCursorWrapper.process_row when joining a subquery
Author: zmwangx
State: closed
Created: 2020-03-07 13:22:00

I'm getting a strange error with `ModelCursorWrapper.process_row` when joining a subquery. When the select is built from the subquery's "perspective" instead of a model's, the generated SQL is all good but processing the returned results fail. Here's a simplified example:

```python
import logging

import peewee


db = peewee.SqliteDatabase(":memory:")


class _BaseModel(peewee.Model):
    class Meta:
        database = db


class User(_BaseModel):
    name = peewee.TextField()


class Entry(_BaseModel):
    user = peewee.ForeignKeyField(User)
    content = peewee.TextField()


def main():
    db.create_tables([User, Entry])
    User.insert(name="foo").execute()
    Entry.insert(user_id=1, content="bar").execute()

    logger = logging.getLogger("peewee")
    logger.addHandler(logging.StreamHandler())
    logger.setLevel(logging.DEBUG)

    subquery = User.select(User.id, User.name).where(User.name == "foo").alias("blah")
    print(
        list(
            Entry.select(subquery.c.id, subquery.c.name, Entry.content).join(
                subquery, on=(Entry.user_id == subquery.c.id)
            )
        )
    ) # Works
    print(
        list(
            User.select(subquery.c.id, subquery.c.name, Entry.content)
            .from_(subquery)
            .join(Entry, on=(Entry.user_id == subquery.c.id))
            .namedtuples()
        )
    ) # Works
    print(
        list(
            User.select(subquery.c.id, subquery.c.name, Entry.content)
            .from_(subquery)
            .join(Entry, on=(Entry.user_id == subquery.c.id))
        )
    ) # Fails in ModelCursorWrapper.process_row


if __name__ == "__main__":
    main()
```

Output:

```sql
('SELECT "blah"."id", "blah"."name", "t1"."content" FROM "entry" AS "t1" INNER JOIN (SELECT "t2"."id", "t2"."name" FROM "user" AS "t2" WHERE ("t2"."name" = ?)) AS "blah" ON ("t1"."user_id" = "blah"."id")', ['foo'])
[<Entry: None>]
('SELECT "blah"."id", "blah"."name", "t1"."content" FROM (SELECT "t2"."id", "t2"."name" FROM "user" AS "t2" WHERE ("t2"."name" = ?)) AS "blah" INNER JOIN "entry" AS "t1" ON ("t1"."user_id" = "blah"."id")', ['foo'])
[Row(id=1, name='foo', content='bar')]
('SELECT "blah"."id", "blah"."name", "t1"."content" FROM (SELECT "t2"."id", "t2"."name" FROM "user" AS "t2" WHERE ("t2"."name" = ?)) AS "blah" INNER JOIN "entry" AS "t1" ON ("t1"."user_id" = "blah"."id")', ['foo'])
```
```python-traceback
Traceback (most recent call last):
  File "/tmp/q.py", line 58, in <module>
    main()
  File "/tmp/q.py", line 49, in main
    list(
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 1914, in __len__
    return len(self._cursor_wrapper)
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 4127, in __len__
    self.fill_cache()
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 4168, in fill_cache
    iterator.next()
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 4224, in next
    self.cursor_wrapper.iterate()
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 4143, in iterate
    result = self.process_row(row)
  File "/Users/zmwang/.pyenv/versions/3.8.0/lib/python3.8/site-packages/peewee.py", line 7339, in process_row
    instance = objects[key]
KeyError: <peewee.ModelSelect object at 0x10b31ce50>
```

Is this a bug or a limitation that I shouldn't have touched?

Thanks for the excellent report. This *should* work, but currently it seems to be running into an edge-case that is proving to be difficult to work around right now.

I'll work-up a fix, but for now I'd suggest using the ".objects()" helper, which will patch all the selected values onto a single model instance. Peewee is throwing the error because it sees you've got some User and Entry columns, which are joined by a foreign-key, but it's having trouble resolving the graph because the selected fields are from a subquery rather than the model instance itself... It should work, but I need to dig into it.

So for now, I'd suggest:

```python
User.select(subquery.c.id, subquery.c.name, Entry.content)
            .from_(subquery)
            .join(Entry, on=(Entry.user_id == subquery.c.id))
            .objects()  # <-- this
```

Thanks for looking into this and suggesting a workaround. In my case, `.namedtuples()` and `.objects()` work equally well and there was no disruption to my work other than a short period of confusion about the error, so no biggie.

As I'm digging into this, I think I may just mark this as a known limitation for the time being. The issue occurs under the following conditions, as far as I can tell:

* issue a model-select, e.g. `ModelClass.select()`
* explicitly override the `from_()` with a subquery, where the subquery model is the same as the model the `select()` was called using.
* include one or more joins

It's tricky because when you select "from" a model, Peewee assumes it will be returning instances of that model (e.g., `User` rows). The user rows, however, are mapped to a subquery. Since peewee also supports selecting from subqueries, it is having trouble coordinating the implication that you want the subquery columns to be placed on the "outer" user object.

The workarounds described are recommended if you need this construction, but depending on your actual query, there are probably other ways or expressing an equivalent query that wouldn't trigger these kinds of edge-cases. If you want help with a particular query, let me know.