0.8 - `q.filter(None)` used to do nothing, now generates "WHERE NULL"

[Yap Sok Ann]

There are a few places in my code that does `q.filter(None)`, e.g. something like this:

    def base_filter(self):

        # subclasses may set this, or not

        pass

    def base_query(self):

        q = self.model_cls.query

        q = q.filter(self.base_filter())

        return q

Previously, it would generate "SELECT * FROM xxx".

In 0.8, it now generates "SELECT * FROM xxx WHERE NULL", and subsequently no row is returned.

The change was introduced 2 months ago in http://sqlalchemy.org/trac/changeset/b221bb4385a4

Anyway, the 0.8 behavior does feel a bit more correct, so perhaps I shall fix my code instead :)

[Michael Bayer]

yeah there's no specific use case I had anticipated for filter(None) but rendering NULL is more consistent.

[kevin...@chownow.com]

It would be helpful if this change was included in the changelog. We were upgrading from an old version of SQLAlchemy that used this to do things like:

.filter(MyClass.attribute > value if value else None)

And we had some queries mysteriously break from the upgrade :(

[Mike Bayer]

On 04/27/2016 01:09 PM, kevin...@chownow.com wrote:
> It would be helpful if this change was included in the changelog. We
> were upgrading from an old version of SQLAlchemy that used this to do
> things like:
>
> .filter(MyClass.attribute > value if value else None)
>
> And we had some queries mysteriously break from the upgrade :(
>

"make me an ice cream sandwich" - poof! you're an ice cream sandwich.
Last part of
http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#improved-rendering-of-boolean-constants-null-constants-conjunctions
talks about this. Unfortunately with each major release, there's lots
of little miscellaneous changes like this so not much option other than
scanning through each "migration" document. But I'm extremely careful
to include every possible change in behavior in these documents.

>
> On Tuesday, December 18, 2012 at 4:04:47 PM UTC-8, Michael Bayer wrote:
>
>
> On Dec 18, 2012, at 3:53 AM, Yap Sok Ann wrote:
>
>> There are a few places in my code that does `q.filter(None)`, e.g.
>> something like this:
>>
>> def base_filter(self):
>> # subclasses may set this, or not
>> pass
>>
>> def base_query(self):
>> q = self.model_cls.query
>> q = q.filter(self.base_filter())
>> return q
>>
>> Previously, it would generate "SELECT * FROM xxx".
>>
>> In 0.8, it now generates "SELECT * FROM xxx WHERE NULL", and
>> subsequently no row is returned.
>>
>> The change was introduced 2 months ago in
>> http://sqlalchemy.org/trac/changeset/b221bb4385a4
>> <http://sqlalchemy.org/trac/changeset/b221bb4385a4>
>>
>> Anyway, the 0.8 behavior does feel a bit more correct, so perhaps
>> I shall fix my code instead :)
>
>
> yeah there's no specific use case I had anticipated for filter(None)
> but rendering NULL is more consistent.
>
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[kevin...@chownow.com]

Sure, understood. I saw that note in the 0.9 docs shortly after this, but suspected it might be something slightly different since this discussion originally referenced 0.8. 

Thank you again for all your hard work on SQLAlchemy. It's incredibly useful. I'm pleasantly surprised by how few things broke when we moved from SQLAlchemy 0.7.9 to the 1.0.12.

[Jonathan Vanasco]

On Wednesday, April 27, 2016 at 5:46:11 PM UTC-4, kevin...@chownow.com wrote:

Thank you again for all your hard work on SQLAlchemy. It's incredibly useful. I'm pleasantly surprised by how few things broke when we moved from SQLAlchemy 0.7.9 to the 1.0.12.

FYI, something that WILL break on that migration is 'pagination' (ie, using limit/offset) with joinedloads if you do not have a fully deterministic column used for sorting in the "where" clause.

A new default was introduced in .9 (and backported to .8 for optional support) that adds a "distinct" clause to an inner query in the subqueryload.  If you don't have a fully deterministic where clause, the first few "pages" will appear to be fine BUT the latter pages will likely generate lots of errors from missing objects.

It's described in this note somewhat differently:
http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#subquery-eager-loading-will-apply-distinct-to-the-innermost-select-for-some-queries

I've been working on a changelog fix to make this more clear.

Because this is usually only apparent towards the latter "pages", most people (and tests) will miss it.  We had this bug in production for months without it being triggered.

The behavior until the .7 branch, combined with default natural sort orders on most backends, allowed "insufficient" queries to work.

I would just check your code to make sure any calls to subqueryload have a fully deterministic column.