Issue: `object has no attribute` error on join
Author: skatenerd
State: closed
Created: 2018-10-16 17:33:18

Suppose I have a many-to-one relationship between `products` and `departments`, where both tables have a `joinrow_id` column.  Suppose that we only have one product and its `name` is `"chair"`.  The chair is in the only department, whose name is "all products", and whose "priority" is null.

I'm seeing inconsistent behavior between these two queries:
```
builder = Products.select(Products.name,                                                                                                                               
                          Departments.name)\
                  .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))\
                  .switch(Products)\
                  .where(Products.id == 1)
```

```
builder = Products.select(Products.name,                                                                                                                               
                          Departments.priority)\
                  .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))\
                  .switch(Products)\
                  .where(Products.id == 1)
```


In the first case, seemingly because the `name` field is truthy, I am able to type: `[r for r in builder][0].department.name`

But in the second case, if I try `[r for r in builder][0].department.priority`, the library raises an error:
`Products has no attribute 'department'`.

This is easily remedied.  I just have to make sure that, in my select, I include a column from `departments` whose value is always truthy.

I just wanted to bring this behavior to your attention in case you weren't aware.

Is the `joinrow` a `ForeignKeyField`? Which model is the foreign-key on?

It's an `IntegerField` on both tables

I tried to replicate it, but it seems to be working correctly in my test:

```python
class PetType(TestModel):
    name = TextField()
    flags = IntegerField(default=0)
    type_id = IntegerField()

class Pet(TestModel):
    name = TextField()
    pet_type = ForeignKeyField(PetType)
    type_id = IntegerField()

class TestJoinFalseyRelatedModels1756(ModelTestCase):
    requires = [PetType, Pet]

    def test_1756(self):
        cat = PetType.create(name='cats', flags=0, type_id=1)
        dog = PetType.create(name='dogs', flags=1, type_id=2)
        Pet.create(name='huey', pet_type=cat, type_id=1)
        Pet.create(name='zaizee', pet_type=cat, type_id=1)
        Pet.create(name='mickey', pet_type=dog, type_id=2)

        query = (Pet
                 .select(Pet.name, PetType.flags)
                 .join(PetType, on=(Pet.pet_type == PetType.id).alias('kind'))
                 .order_by(Pet.name))
        with self.assertQueryCount(1):
            self.assertEqual([(p.name, p.kind.flags) for p in query],
                             [('huey', 0), ('mickey', 1), ('zaizee', 0)])

        query = (Pet
                 .select(Pet.name, PetType.flags)
                 .join(PetType,
                       on=(Pet.type_id == PetType.type_id).alias('kind'))
                 .order_by(Pet.name))
        with self.assertQueryCount(1):
            self.assertEqual([(p.name, p.kind.flags) for p in query],
                             [('huey', 0), ('mickey', 1), ('zaizee', 0)])

```

Or I suppose I need to change the "flags=0" in the test, to "flags=None" ?

I think to recreate the bug, you need a third table

I'm on Peewee 3.7.0 FWIW

To clarify, the generated SQL looks like:
`('SELECT "t1"."join_row_id", "t2"."name" FROM "products" AS "t1" INNER JOIN "departments" AS "t2" ON ("t2"."join_row_id" = "t1"."join_row_id") WHERE ("t1"."id" = %s)', [1])`


Before you invest any time in this, I'll assemble a program that looks like the one you pasted above, just so there's no ambiguity in recreating the issue.

I've created a self-contained example here:

https://gist.github.com/skatenerd/45b65a361384869e39ee20069d8ea7d4

On my machine, I'm able to make the first print statement in `go()`, but the second crashes with `AttributeError: 'Products' object has no attribute 'department'`

To replicate, this is all I needed:

```python
class Animal(TestModel):
    name = TextField()
    flags = IntegerField(null=True)

class Pet(TestModel):
    name = TextField()
    animal = ForeignKeyField(Animal)

cat = Animal.create(name='cat', flags=0)
dog = Animal.create(name='dog', flags=None)
huey = Pet.create(name='huey', animal=cat)
mickey = Pet.create(name='mickey', animal=dog)

base_query = (Pet
              .select(Pet.name, Animal.flags)
              .join(Animal)
              .order_by(Pet.name))

# Works, prints "huey 0".
for pet in base_query.where(Pet.name == 'huey'):
    print(pet.name, pet.animal.flags)

# raises DoesNotExist
for pet in base_query.where(Pet.name == 'mickey'):
    print(pet.name, pet.animal.flags)
```

What's going on is a side-effect of the way relational databases use `NULL` to signal missing data. Consider if the Pet.animal foreign-key was nullable and we wanted to list all pets and their animal-type name. If the "pet" had an animal relationship, then the name would be displayed. If *no* animal was associated, though, we would see NULL for the animal name.

This NULL could signify *either* that the Animal's name was NULL, *or* that the Pet has no related Animal object. In the case that the Pet has no related Animal row, Peewee has a choice between setting the row's "animal" object to None, or a "blank"/unpopulated Animal instance. Peewee does the latter if-and-only-if all the columns selected from a given model are NULL.

So, in your example, I believe it is the fact that `Department.priority` is NULL *and* since it is the only column selected from that table in your query, Peewee cannot tell whether the "Product" has a department with a null priority, or if it does not have a department at all (since all the values we selected from the department table are NULL).

This is not a bug per-se, but a trade-off between representing missing related objects as "blank"/"empty" objects versus assuming the related object is `None` if all of the columns we selected from it are also NULL.

For your example, you should be able to workaround the "NULL" priority by also selecting the Department's primary key, e.g.

```python
builder = (Products
           .select(Products.name, Departments.id, Departments.priority)
           .join(Departments, on=(Departments.joinrow == Products.joinrow).alias('department'))
           .where(Products.id == 1))
```