> On Mar 16, 2016, at 3:03 PM, Jeremy McNevin <jmcne...@gmail.com> 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.
