rails: The construct "where.not" doesn't respect boolean algebra

Steps to reproduce

When two predicates A and B are given to a where clause, Rails combines them using AND, which is the expected behavior.

Model.where(col1: 1, col2: 2).to_sql

# => SELECT "models".* FROM "models" WHERE "models"."col1" = 1 AND "models"."col2" = 2

When the clause is negated (using where.not), Rails distributes the NOT to every predicate before combining them using AND. Why?

Model.where.not(col1: 1, col2: 2).to_sql

# => SELECT "models".* FROM "models" WHERE ("models"."col1" != 1) AND ("models"."col2" != 2)

Expected behavior

NOT(A AND B) = NOT(A) OR NOT(B)

In my example, the query should look like:

SELECT "models".* FROM "models" WHERE ("models"."col1" != 1) OR ("models"."col2" != 2)

Actual behavior

NOT(A AND B) = NOT(A) AND NOT(B) (wrong)

It’s worth noting that the correct behavior can still be obtained by writing SQL directly:

Model.where.not('"models"."col1" = 1 AND "models"."col2" = 2').to_sql
# => SELECT "models".* FROM "models" WHERE (NOT ("models"."col1" = 1 AND "models"."col2" = 2))

It makes the construct where.not(...) behave very differently depending on the kind of arguments which are passed to it.

The culprit seems to be located here : https://github.com/rails/rails/blob/be81b5066074fee8126144d072c6132b93d1fe39/activerecord/lib/active_record/relation/where_clause.rb#L74

System configuration

Rails version: 5.1.4

Ruby version: MRI 2.4.2

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 20
Comments: 24 (11 by maintainers)

Commits related to this issue

Deprecate `where.not` working as NOR and will be changed to NAND in Rails 6.1 `where.not` with polymorphic association is partly fixed incidentally at 213796f (refer #33493, #26207, #17010, #16983, #... — committed to kamipo/rails by kamipo 5 years ago

Most upvoted comments

For me it is fine the way it is.

If where.not was meant to be defined in terms of boolean operators, this would be a bug. But where.not is just an AND of negations by definition. I personally see no problem and the behavior of where does not make me believe where.not should behave one way or another. (Let me add that in my last years in the math faculty including one of PhD I specialized on mathematical logic and foundations of math).

A different topic is if we want API to express an OR of negations.

+18

fxn on Nov 24, 2017

I strongly believe the current behavior is the right one. The OP is based on the interpretation that the string col1: 1, col2: 2 as argument of where and where.not means a logical AND between the two conditions. I don’t see a justification for this interpretation. This is just a sequence of method arguments, used as a shortcut for the alternative of specifying each of the conditions separately. In other words:

where(col1: 1, col2: 2)

is just a shortcut form for:

where(col1: 1).where(col2: 2)

I think it’s highly desirable then if:

where.not(col1: 1, col2: 2)

were also just a shortcut for:

where.not(col1: 1).where.not(col2: 2)

So to give it the semantics in the OP, you’d have to give the second form the same semantics, which I think is highly problematic. You’d have to explain that where.not conditions are collected separately from where conditions, then combined with AND and the result put under a single NOT. I don’t think that would be what most people would expect, especially when the conditions come from different scopes.

octavpo on Dec 4, 2017

I guess the main reason I’m inclined to change it, separate from any expectation/intuition, is that the alternative seems far more useful to me.

It would be perfectly defensible, designed from scratch, for multiple keys to where to mean OR by definition. But it doesn’t, because that’s not the most common thing to need to express. I claim that while any well-stated definition (and let’s be honest, a single example with no mention in the text is not well-stated) is fine by definition, the current definition is not a tool people will regularly have reason to reach for – by the same justification that we lived without an actual OR operator for so long.

We also already have another syntax to briefly AND two not-conditions together: x.where.not(a: 1).where.not(b: 2).

(Likewise, anyone currently encountering this and just wanting to produce the right expression can use x.where.not(a: 1).or(x.where.not(b: 2)))

matthewd on Nov 25, 2017

Is this related? I have scopes producing incorrect results on polymorphic “where not”:

class MyThing
  belongs_to :owner, polymorphic: true
  scope :not_owned_by, ->(owner) { where.not(owner: owner) }
  #...
end

Now MyThing.not_owned_by(Account.find(172483)) incorrectly produces

SELECT "my_things".* FROM "my_things" WHERE ("my_things"."owner_type" != 'Account') AND ("my_things"."owner_id" != 172483)

which returns too few records, rather than the correct

SELECT "my_things".* FROM "my_things" WHERE NOT ("my_things"."owner_type" = 'Account' AND "my_things"."owner_id" = 172483)

which just excludes one record.

inopinatus on Feb 25, 2018

The problem comes from interpreting where(col1: 1, col2: 2) as meaning logical AND, when it just means ‘add these two conditions to the list of where conditions’.

All predicates in where clause are combined using a logical AND, so this is exactly the same thing.

Most people I talked with are understanding this as where('col1 = 1 AND col2 = 2'), and would think that the following assertion is correct (whereas it’s not)

where('NOT(col1 = 1 AND col2 = 2)') = where.not(col1: 1, col2: 2)

The choice of distributing the NOT operator to every argument is arbitrary. Sure, the documented behavior is right by definition, but it would be nicer and less surprising to be compliant with basic boolean algebra. Another consequence, the following assertion is also incorrect if there are more than one condition.

all = where.not(conds) + where(conds)

If you’re not aware of this behavior you would probably assume it to be true. The documentation doesn’t make it obvious today.

fterrazzoni on Dec 6, 2017