Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

where.not(x: true) not including rows with x == null in MySQL #19262

Closed
vemv opened this issue Mar 9, 2015 · 16 comments

Comments

Projects
None yet
@vemv
Copy link

commented Mar 9, 2015

Given a MyModel AR::Base class where x is a nullable boolean field, I believe Rails generates incorrect SQL -for MySQL- for the following query:

MyModel.where.not(x: true)
# SELECT `users`.* FROM `my_models` WHERE (`my_models`.`x` != 1)

The problem being that my_models rows where x is NULL are not returned.

FWIW, I do not know how to correctly write the SQL query.

Also, I am using the mysql2 gem, but I think SQL generation happens at Rails level.

Lastly, I'm using MySQL 5.6.22 - essentially the latest.

Thanks!

Victor

@senny

This comment has been minimized.

Copy link
Member

commented Mar 9, 2015

@vemv can you attach an executable test case to reproduce the issue? You can use this script as a starting point.

@senny senny added the activerecord label Mar 9, 2015

@exviva

This comment has been minimized.

Copy link
Contributor

commented Mar 9, 2015

I think that's the ternary logic of NULLs kicking in. Not sure if Rails should try to be smart here and generate an IS NULL OR ... clause?

@matthewd

This comment has been minimized.

Copy link
Member

commented Mar 9, 2015

Indeed. I believe our position here is that where.not isn't an inversion of a where, but rather a means of invoking an SQL NOT. (See also, our behaviour on a multi-key where.not, e.g., where.not(a: 1, b: 2).)

@Gytax

This comment has been minimized.

Copy link

commented Mar 9, 2015

This isn't a bug in Rails, this is how NULLs work in MySQL. NULL is NULL, it's nothing. It's not true, it's not false. It's NULL. So basically, always check for not NULL.

MyModel.where.not(x: [true, nil])

Should do the trick.

@senny senny closed this Mar 9, 2015

@vemv

This comment has been minimized.

Copy link
Author

commented Mar 9, 2015

@Gytax isn't "nothing" different from true?

where.not(x: true) should include rows with null values, because null ("nothing") is different from true.

Your suggested query is wrong - only where(x: [nil, false]) accomplishes what where.not(x: true) should.

As I see it Rails is providing a faulty abstraction here. MySQL has no such thing as booleans. If Rails wants me to believe (which is a good thing) that my column is of boolean type, then it should ensure that a "not-true" query yields whatever is not true.

Please reopen?

@rafaelfranca

This comment has been minimized.

Copy link
Member

commented Mar 9, 2015

I believe our position here is that where.not isn't an inversion of a where, but rather a means of invoking an SQL NOT

Exactly this. where.not(x: true) is WHERE x != true, not the negative form of WHERE x == true.

@Gytax

This comment has been minimized.

Copy link

commented Mar 9, 2015

The thing with the MySQL NULL is that it's not true nor false, it also isn't !true or !false. It's nothing. And while nothing is different than true, NULL still doesn't match. I've run into this myself on more than one occasion. With MySQL you simple have to check for NULL values if the column is ALLOW NULL.

Whenever I create a bool column, I default it to false (or 0), to prevent NULL values.

Rails has little to do with this, because it simple translates to valid SQL. I don't think Rails should incorporate NULL values here, because it is simply a different thing.

NULL is very interesting imo, some reading matter:
http://en.m.wikipedia.org/wiki/Null_(SQL)
This sentence might clear thing up:
"a good way to remember what null means is to remember that in terms of information, "lack of a value" is not the same thing as "a value of zero"; similarly, "lack of an answer" is not the same thing as "an answer of no". For example, consider the question "How many books does Juan own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns, or doesn't own)"

@Gytax

This comment has been minimized.

Copy link

commented Mar 9, 2015

And in this case, not true means false for SQL, not 'lack of an answer'.

@CarlosCD

This comment has been minimized.

Copy link

commented Dec 1, 2015

By the way, this happens in PostgreSQL as well, probably as with any other relational database.

It is going to be a gotcha for many, while using a Ruby DSL instead of SQL. Specially when using boolean columns, expecting that NULL as just another possible value, as the AR DSL maps it to Ruby's nil... so where.not(something: true) would not return any records in which something is NULL.

[ true, false, nil ].select{ |v| !(v == true) }
#=> [false, nil]

I guess a solution is to modify the schema to avoid NULL values, if possible (default: false, null: false).

@olivierpichon

This comment has been minimized.

Copy link

commented Jul 11, 2016

Sure guys this behaviour comes from Postgres or any RDBMS. But what is anticipated from Active Record is to write the correct SQL request for us. Otherwise it leads to some inconsistencies like:

Project.count
  (0.6ms)  SELECT COUNT(*) FROM "projects"
=> 829
[16] pry(main)> Project.where.not(cloned_from_id: nil).count + Project.where(cloned_from_id: nil).count
   (0.7ms)  SELECT COUNT(*) FROM "projects" WHERE ("projects"."cloned_from_id" IS NOT NULL)
   (0.4ms)  SELECT COUNT(*) FROM "projects" WHERE "projects"."cloned_from_id" IS NULL
=> 829
[17] pry(main)> Project.where.not(cloned_from_id: 1260).count + Project.where(cloned_from_id: 1260).count
   (0.6ms)  SELECT COUNT(*) FROM "projects" WHERE ("projects"."cloned_from_id" != $1)  [["cloned_from_id", 1260]]
   (0.5ms)  SELECT COUNT(*) FROM "projects" WHERE "projects"."cloned_from_id" = $1  [["cloned_from_id", 1260]]
=> 199

I think AR should write a SQL which looks like:

SELECT "projects".* FROM "projects" WHERE (projects.cloned_from_id != 1260 OR projects.cloned_from_id is NULL)
@feliperaul

This comment has been minimized.

Copy link

commented Dec 29, 2016

This is much more a logic question then a programming question.

If I write .where.not(attribute: true), I obviously want anything where the attribute IS NOT true, including nil or whatever.

Rails should account for this and I consider this a bug.

@feliperaul

This comment has been minimized.

Copy link

commented Jan 19, 2017

@senny Could you please reconsider opening this bug?

@matthewd

This comment has been minimized.

Copy link
Member

commented Jan 19, 2017

@feliperaul no; this is not a bug.

The above discussion both confirms that the behaviour is intended, and explains why that is the chosen intended behaviour.

It's also been true since where.not was introduced, making any potential change a compatibility issue for the many people currently expecting it to behave the same as SQL equality.

@feliperaul

This comment has been minimized.

Copy link

commented Jun 3, 2017

For the ones suffering from this (like me), and you're using PostgresSQL, I think the solution is use Model.where("column_name IS DISTINCT FROM ?", value) as explained here: https://stackoverflow.com/a/17680845/1290457 . This query will return null values as expected as they are distinct from the value passed.

@clst

This comment has been minimized.

Copy link
Contributor

commented Oct 16, 2017

I just stumbled over this too. The Rails way is correct in this case.
Even if you do: .not('x = 1') the x: nil rows won't show up (at least with mysql). It produces this SQL: WHERE (NOT (x = 1))

@gregblass

This comment has been minimized.

Copy link

commented Nov 19, 2018

This is much more a logic question then a programming question.

If I write .where.not(attribute: true), I obviously want anything where the attribute IS NOT true, including nil or whatever.

Rails should account for this and I consider this a bug.

Couldn't agree with @feliperaul more here. ActiveRecord should account for this, regardless of how NULLs work on the database level. If someone says where.not(attribute: true), I can almost guarantee you they intended that to mean 'any time this is not true'. Logic/common sense would include false and NULL in that. I would argue that most people using ActiveRecord did not know that databases work that way.

The fact that you've already stuck with this direction in other areas of Rails complicates things though.

Anyway - I'm at least glad that I found this. Just wish I had before insisting to my client that not: true would include NULL values, and that all the jobs that she caught that were not regenerating for this year (and potentially losing them tons of business) were not due to this reason...

At least I can tell her that there was some level of programmer disagreement though?!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.