Using "IS DISTINCT FROM" instead of "!=" for inequality checks

[Jeremy McNevin]

I'd be interested in hearing thoughts about changing the generated SQL for a negated equality comparison from using "!=" to "IS DISTINCT FROM" (where supported), since the latter has less-surprising handling of NULL values.

For example, with the following bit of AR:

User.where.not(parent_id: 10)

You would get some SQL roughly equivalent to:

WHERE parent_id != 10

But this would not return rows where parent_id is NULL, since a comparison against NULL always returns NULL.  This SQL would behave as we'd expect:

WHERE parent_id IS DISTINCT FROM 10

Would there be any disadvantages to doing this sort of comparison by default?

J

[Matt Jones]

It’s definitely additional effort, as `IS DISTINCT FROM` is not supported by all the default adapters:

* PostgreSQL supports it

* SQLite doesn’t support it, but has the similar-in-function `IS NOT` operator.

* MySQL doesn’t support it - and has an `IS NOT` operator, but it means something different (and only accepts TRUE / FALSE / UNKNOWN as a right-hand argument)

* Oracle doesn’t support it, or have a similar operator. (based on my limited reading of the Oracle docs)

Those last two would need a fallback (something like `parent_id != 10 OR parent_id IS NULL`). The change would be pretty straightforward - just tweak the DB-specific visitors in Arel.

I suspect the most difficult work for a change like this would be coordinating & messaging the update - it would need to be announced quite prominently, since the change will result in queries returning different results while still succeeding.

—Matt Jones

[Sean Griffin]

Yeah, we definitely could never make this change. It'd be too major of a backwards incompatible change.