Issue: An empty query result if JOIN.CROSS is combined with JOIN.INNER. At the same time, raw() worked properly
Author: navolotsky
State: closed
Created: 2020-06-05 11:46:53

```python
import logging
import traceback

from peewee import JOIN, CharField, ForeignKeyField, Model, SqliteDatabase

database = SqliteDatabase(":memory:")


class BaseModel(Model):
    class Meta:
        database = database


class FileGroup(BaseModel):
    descr = CharField()


class FileBasename(BaseModel):
    string = CharField()
    type = CharField()


class BaseFile(BaseModel):
    name_end_part = CharField()


class FileFolder(BaseModel):
    group = ForeignKeyField(model=FileGroup, backref='folders')
    name = CharField()
    type = CharField()


class File(BaseModel):
    group = ForeignKeyField(model=FileGroup, backref='files')
    filename_info = ForeignKeyField(model=FileBasename, backref='files')
    base_file = ForeignKeyField(model=BaseFile, backref='derived_files')

    @property
    def filename(self):
        return (self.filename_info.string +
                self.base_file.name_end_part)

    def folder(self):
        query = (
            FileFolder.select()
            .join(File, join_type=JOIN.CROSS)
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (File.group == FileFolder.group) &
                (FileBasename.type.cast('TEXT') == FileFolder.type.cast('TEXT'))
            )
        )
        result = query.execute()
        return result[0] if result else None

    def folder_raw(self):
        query = (
            FileFolder.select()
            .join(File, join_type=JOIN.CROSS)
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (File.group == FileFolder.group) &
                (FileBasename.type.cast('TEXT') == FileFolder.type.cast('TEXT'))
            )
        )
        sql, params = query.sql()
        result = FileFolder.raw(sql, *params).execute()
        return result[0] if result else None


if __name__ == "__main__":
    database.create_tables(
        models=[FileGroup, FileBasename, BaseFile, File, FileFolder])
    with database.atomic():
        FileGroup.create(descr="first group")
        FileFolder.create(group=1, name="first folder", type="first type")
        FileBasename.create(string="some_file_name", type="first type")
        BaseFile.create(name_end_part=".end")
        File.create(group=1, filename_info=1, base_file=1)

    file = File.get_by_id(1)

    logger = logging.getLogger('peewee')
    logger.addHandler(logging.StreamHandler())
    logger.setLevel(logging.DEBUG)

    try:
        print("This won't be printed:", repr(file.folder()))
    except Exception:
        traceback.print_exc()

    print("Let's try with raw():", repr(file.folder_raw()))

```

Run result:
```
(.venv_3.8.3-64) C:\Projects\Python>python peewee_cross_join.py
('SELECT "t1"."id", "t1"."group_id", "t1"."name", "t1"."type" FROM "filefolder" AS "t1" CROSS JOIN "file" AS "t2" INNER JOIN "filebasename" AS "t3" ON ("t2"."filename_info_id" = "t3"."id") WHERE ((("t2"."id" = ?) AND ("t2"."group_id" = "t1"."group_id")) AND (CAST("t3"."type" AS TEXT) = CAST("t1"."type" AS TEXT)))', [1])
Traceback (most recent call last):
  File "peewee_cross_join.py", line 90, in <module>
    print("This won't be printed:", repr(file.folder()))
  File "peewee_cross_join.py", line 55, in folder
    return result[0] if result else None
  File "C:\Projects\Python\.venv_3.8.3-64\lib\site-packages\peewee.py", line 4232, in __len__
    self.fill_cache()
  File "C:\Projects\Python\.venv_3.8.3-64\lib\site-packages\peewee.py", line 4273, in fill_cache
    iterator.next()
  File "C:\Projects\Python\.venv_3.8.3-64\lib\site-packages\peewee.py", line 4329, in next
    self.cursor_wrapper.iterate()
  File "C:\Projects\Python\.venv_3.8.3-64\lib\site-packages\peewee.py", line 4248, in iterate
    result = self.process_row(row)
  File "C:\Projects\Python\.venv_3.8.3-64\lib\site-packages\peewee.py", line 7502, in process_row
    instance = objects[src]
KeyError: <Model: File>
('SELECT "t1"."id", "t1"."group_id", "t1"."name", "t1"."type" FROM "filefolder" AS "t1" CROSS JOIN "file" AS "t2" INNER JOIN "filebasename" AS "t3" ON ("t2"."filename_info_id" = "t3"."id") WHERE ((("t2"."id" = ?) AND ("t2"."group_id" = "t1"."group_id")) AND (CAST("t3"."type" AS TEXT) = CAST("t1"."type" AS TEXT)))', [1])
Let's try with raw(): <FileFolder: 1>

(.venv_3.8.3-64) C:\Projects\Python>
```

Try using `.objects()` instead at the end of your "non-raw" query, which will skip the model-join graph reconstruction step. You don't need it as you're only selecting from one table and it seems to be the issue.

I'm having a bit of trouble parsing-out exactly what your query is designed to accomplish. There's a bit of unnecessary cruft in there (filegroup, e.g.). Can you make a smaller, more semantic example so I can get a better handle on what you're trying to accomplish?

> Try using .objects()

It works.

> Can you make a smaller

I will try do it. This is a visualization diagram. Code will go below.

![изображение](https://user-images.githubusercontent.com/43314216/83925748-a0047f80-a790-11ea-8ee7-6eba4204fd5c.png)

I renamed FileGroup to Storage.

One Storage is related with many files (by storing Storage id in File relation) and several Folders (also Folder related with Storage by id).

One Folder is related to one Storage.

One File is related with one Storage and one Folder.

One Folder is related to many Files but File do not explicitly bound with Folder by id. Instead, File and Folder are implicitly bound through:

```sql
file.storage = folder.storage AND
file.filename_info = filebasename.id AND
CAST(filebasename.type AS TEXT) = CAST(folder.type AS TEXT)
```

There is a way to avoid this strange things by defining foreign key references folder.id.

Also to rewrite the query as it will follow below.

I don't like the first way because this implicit bind is uniquely specified so you don't need to have an additional field in the large File table.

I don't like folder_another_way() and folder_another_way_2() because they do not show that this bind is implicit.

```python
import logging
import traceback

from peewee import JOIN, CharField, ForeignKeyField, Model, SqliteDatabase

database = SqliteDatabase(":memory:")


class BaseModel(Model):
    class Meta:
        database = database


class Storage(BaseModel):
    name = CharField()


class Folder(BaseModel):
    storage = ForeignKeyField(model=Storage)
    type = CharField()  # Enum in DB


class FileBasename(BaseModel):
    string = CharField()
    type = CharField()  # Enum in DB with the same values as in Folder.type


class File(BaseModel):
    storage = ForeignKeyField(model=Storage)
    filename_info = ForeignKeyField(model=FileBasename, backref='files')

    def folder(self):
        query = (
            Folder.select()
            .join(File, join_type=JOIN.CROSS)
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (File.storage == Folder.storage) &
                (FileBasename.type.cast('TEXT') == Folder.type.cast('TEXT'))
            )
        )
        result = query.execute()
        return result[0] if result else None

    def folder_raw(self):
        query = (
            Folder.select()
            .join(File, join_type=JOIN.CROSS)
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (File.storage == Folder.storage) &
                (FileBasename.type.cast('TEXT') == Folder.type.cast('TEXT'))
            )
        )
        sql, params = query.sql()
        result = Folder.raw(sql, *params).execute()
        return result[0] if result else None

    def folder_another_way(self):
        query = (
            Folder.select()
            .join(File, on=(File.storage == Folder.storage))
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (FileBasename.type.cast('TEXT') == Folder.type.cast('TEXT'))
            )
        )
        result = query.execute()
        return result[0] if result else None

    def folder_another_way_2(self):
        query = (
            Folder.select()
            .join(Storage)
            .join(File)
            .join(FileBasename)
            .where(
                self._pk_expr() &
                (FileBasename.type.cast('TEXT') == Folder.type.cast('TEXT'))
            )
        )
        result = query.execute()
        return result[0] if result else None


if __name__ == "__main__":
    database.create_tables(
        models=[Storage, FileBasename, File, Folder])
    with database.atomic():
        storage = Storage.create(name="first storage")
        Folder.create(type="first type", storage=storage)
        filename_info = FileBasename.create(
            type="first type", string="base part of some name")
        File.create(storage=storage, filename_info=filename_info)

    file = File.get_by_id(1)

    logger = logging.getLogger('peewee')
    logger.addHandler(logging.StreamHandler())
    logger.setLevel(logging.DEBUG)

    try:
        print("This won't be printed:", repr(file.folder()))
    except Exception:
        traceback.print_exc()

    print("Let's try with raw():", repr(file.folder_raw()))
    print("And now another way:", repr(file.folder_another_way()))
    print("Another way #2:", repr(file.folder_another_way_2()))

```


This is more or less the way I would have expected the query to be written:

```python
    def folder_another_way(self):
        query = (
            Folder.select()
            .join(File, on=(File.storage == Folder.storage))
            .join_from(File, FileBasename)
            .where(
                self._pk_expr() &
                (FileBasename.type.cast('TEXT') == Folder.type.cast('TEXT'))
            )
        )
        result = query.execute()
        return result[0] if result else None
```