Why does sqlalchemy group_by query hold results for NULL even though there are no records with NULL?

Viewed 10

I'm using sqlalchemy in a FastAPI app with pydantic models.

I'm confused why the grouped results includes a result row for NULL for some Clients even though table-wide there's no record with a NULL status. The status field is an enum with only string options.

status: Status = Field(
    sa_column=Column(Enum(Status)),
    default=Status.pending.value,
)


query1 = db.query(VehicleDB).filter(VehicleDB.status == None).all()  # -> []

query2 = db.query(ClientDB.name, VehicleDB.status, total)
          .outerjoin(VehicleDB)
          .group_by(ClientDB.name, VehicleDB.status).all()

-> [('Client1', None, 0)
...
1 Answers

outerjoin will generate a LEFT OUTER JOIN. If there are no matching VehicleDB records for a ClientDB.name then you will get NULL values for the VehicleDB side of the join.

Related