[Sqlalchemy-tickets] Issue #3479: detecting whether outerjoin finds a match or not (zzzeek/sqlalchemy)

[Tim T. <iss...@bi...>]

New issue 3479: detecting whether outerjoin finds a match or not
https://bitbucket.org/zzzeek/sqlalchemy/issues/3479/detecting-whether-outerjoin-finds-a-match

Tim Tisdall:

When doing an outerjoin it's possible an object is returned as a `None`.  I wanted to filter rows based on whether one of the objects would be returned or would be a `None` so I tried something like the following:

```python
DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB == None
)
```

Looking at the resulting SQL, it seems the `TableB == None` filter is translated into `false = 1`.  `TableB !=None` seems to translate into `1 = 1`.

It seems that the way to get this to work is:

```python
DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB.some_primary_key == None
)
```

Essentially you need to test for `None` on an attribute that should never be `None`.  However, that seems a little confusing as the result of the query is not a `TableB` object with that attribute equal to `None`, but the whole object is instead `None`.

This issue is a feature request to make the top-most query work as expected.  This could be done by translating `TableB == None` into a test for a `NULL` value on any of the primary keys of `TableB`.