Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

active record conditions to deal with nils in arrays

#1486

currently, this:


MyModel.find(:all,:conditions => {:some_param => [nil]})

returns differently to:


MyModel.find(:all,:conditions => {:some_param => nil})

(the difference being whether the nil is in an array or not)

This is due to the situation where this will return the expected result set:


select * from events where venue_id is null

but this will return an empty set:


select * from events where venue_id in (null)

I don't have access to anything other than MySQL databases so can't test whether this affects all database vendors.

The following demonstrates the behaviour:


SomeActiveRecordModel.send(:sanitize_sql_hash_for_conditions,{:some_param => [nil]})

the existing ActiveRecord will return:


`events`.`some_param` IN (NULL)

my patch changes it to return:


(`events`.`some_param` IN (NULL) OR `events`.`some_param` IS NULL)

This is my first patch so please forgive and inform me if something is not as it should be.

Reported by Nick Sellen · November 27th, 2008 @ 05:24 PM

State: wontfix
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Michael Koziarski
    Michael Koziarski
    • Tag changed from patch to activerecord, patch
    • State changed from new to wontfix

    This doesn't seem like something we should be changing. The whole issue on whether 'null is a value' is something that's pretty deeply held amongst relational theorists and the IN (NULL) behaviour is intentional.

    I think this is just one of those cases where the impedence mismatch between the ruby and SQL view of the world is incompatible and you need to handle that in code.

    November 29th, 2008 @ 11:58 AM

  2. Nick Sellen
    Nick Sellen

    ok

    activerecord already makes allowance for a nil/null oddity, i.e.:

    
    :conditions => {:something => nil}
    

    (resulting in "something is null" rather than "something = " as for any other value)

    ... my feeling is that as the user experience ends up inconsistent and that as ruby nil is already not simply translated to SQL null (as in the above example) that not much is gained by passing SQL's strange idea of null through to the ruby world where nil very much is a value.

    November 29th, 2008 @ 06:43 PM