> On Mar 16, 2016, at 3:03 PM, Jeremy McNevin wrote:
>
> 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?
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
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.