Rails 6.1: where.not changes behaviour from NOR to NAND

Updated . Posted . Visible to the public. Repeats.

Since Rails 6.1, if we use where.not with multiple attributes, it applies logical NAND (NOT(A) OR NOT(B)) instead of NOR (NOT(A) AND NOT(B)). If you do not take care, this change will increase the matched set.

Examples

"Don't send newsletters neither to admins nor to trashed users!" becomes "Don't send newsletters to trashed admins".

User.where.not(role: 'admin', trashed: true)
# Before Rails 6.1, with NOR
=> "SELECT "users".* FROM "users" WHERE "users"."role" != 'admin' AND "users"."trashed" != TRUE"
# Equivalent:
=> "SELECT "users".* FROM "users" WHERE NOT ("users"."role" = 'admin' OR "users"."trashed" = TRUE")

# From Rails 6.1, with NAND
=> "SELECT "users".* FROM "users" WHERE NOT ("users"."role" = 'admin' AND "users"."trashed" = TRUE)"

# Use this query to keep applying NOR
User.where.not(role: 'admin').where.not(trashed: true)
=> "SELECT "users".* FROM "users" WHERE "users"."role" != 'admin' AND "users"."trashed" != TRUE"

In the following illustration, assume the left circle is "admins" and the right circle is "trashed users". The red area corresponds to the records in the resulting scope:

NOR NAND (Rails 6.1+)
NOR NAND

Using an Array as an argument is still working as expected

User.where.not(email: [nil, ''])
=> "SELECT "users".* FROM "users" WHERE NOT (("users"."email" = '' OR "users"."email" IS NULL))"

# This is equivalent to the following query. So here we are still using NOR.
=> "SELECT "users".* FROM "users" WHERE "users"."email" != '' AND "users"."email" IS NOT NULL"
Julian
Last edit
Henning Koch
Attachments
License
Source code in this card is licensed under the MIT License.
Posted by Julian to makandra dev (2021-12-15 15:14)