Rails 6 deprecates where.not as NOR & Rails 6.1 as NAND

A notable deprecation warning has been added in Rails 6 when using where.not with multiple attributes.

Before Rails 6, if we use where.not with multiple attributes, it applies logical NOR (NOT(A) AND NOT(B)) in WHERE clause of the query. This does not always work as expected.

Let's look at an example to understand this better.

We have Post model with a polymorphic association.

Rails 5.2

>> Post.all
=> #<ActiveRecord::Relation [
#<Post id: 1, title: "First Post", source_type: "Feed", source_id: 100>,
#<Post id: 2, title: "Second Post", source_type: "Feed", source_id: 101>]>

>> Post.where(source_type: "Feed", source_id: 100)
=> #<ActiveRecord::Relation [#<Post id: 1, title: "First Post", source_type: "Feed", source_id: 100>]>

>> Post.where.not(source_type: "Feed", source_id: 100)
=> #<ActiveRecord::Relation []>

In the last query, we expect ActiveRecord to fetch one record.

Let's check SQL generated for the above case.

>> Post.where.not(source_type: "Feed", source_id: 100).to_sql

=> SELECT "posts".* FROM "posts" WHERE "posts"."source_type" != 'Feed' AND "posts"."source_id" != 100

where.not applies AND to the negation of source_type and source_id, and fails to fetch expected records.

In such cases, correct implementation of where.not would be logical NAND (NOT(A) OR NOT(B)).

Let us query posts table using NAND this time.

>> Post.where("source_type != 'Feed' OR source_id != 100")

   SELECT "posts".* FROM "posts" WHERE (source_type != 'Feed' OR source_id != 100)

=> #<ActiveRecord::Relation [#<Post id: 2, title: "Second Post", source_type: "Feed", source_id: 101>]>

Above query works as expected and returns one record. Rails 6.1 will change where.not working to NAND similar to the above query.

Rails 6.0.0.rc1

>> Post.where.not(source_type: "Feed", source_id: 100)

DEPRECATION WARNING: NOT conditions will no longer behave as NOR in Rails 6.1. To continue using NOR conditions, NOT each conditions manually (`.where.not(:source_type => ...).where.not(:source_id => ...)`). (called from irb_binding at (irb):1)

=> #<ActiveRecord::Relation []>

It is well mentioned in deprecation warning that if we wish to use NOR condition with multiple attributes, we can chain multiple where.not using a single predicate.

>> Post.where.not(source_type: "Feed").where.not(source_id: 100)

Here's the relevant discussion and pull request for this change.