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

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

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

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