This project is archived and is in readonly mode.
active record conditions to deal with nils in arrays
Reported by Nick Sellen | November 27th, 2008 @ 05:24 PM | in 2.x
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.
Comments and changes to this ticket
-
Michael Koziarski November 29th, 2008 @ 11:58 AM
- 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.
-
Nick Sellen November 29th, 2008 @ 06:43 PM
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.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
Nick Sellen