Yesterday afternoon’s little bit of head scratching and debugging confusion comes to you courtesy of the fact that<\/p>\n
SELECT * FROM my_table
\nWHERE my_id = iObject.my_id<\/p><\/blockquote>\ndoes not do the expected thing when both my_id<\/tt> and iObject.my_id<\/tt> are null<\/tt>.
\nBecause apparently null = null<\/tt> and null <> null<\/tt> both evaluate to false<\/tt>, because any comparison involving null<\/tt> evaluates to false<\/tt>.
\n(Edit<\/strong>: As pointed out to me by many people, it should actually evaluate to null<\/tt>, but regardless, the effect is the same… the select statement does not get the rows you expect.)<\/p>\nI guess it makes sense when you think about it (a lack of a value is not equal to the lack of a value, because there is no value, as opposed to C where null<\/tt> is just a pointer to 0x0<\/tt> so a value comparison makes sense), but it’s still a surprising issue when you first come across it. Working around it involves the non-intuitive<\/p>\n
SELECT * FROM my_table
\nWHERE (my_id IS NULL AND iObject.my_id IS NULL) OR my_id = iObject.my_id<\/p><\/blockquote>\nIt looks like MySql also provides the null-comparing operator <=><\/tt> which will return equality even if both items are null. But it doesn’t look like Oracle does.<\/p>\n
Blah.<\/p>\n
On an unrelated note, I’ve redone the design of photos<\/a>. Check it out if you haven’t yet, and let me know if things are broken or don’t work for you. :)<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday afternoon’s little bit of head scratching and debugging confusion comes to you courtesy of the fact that SELECT * FROM my_table WHERE my_id = iObject.my_id does not do the expected thing when both my_id and iObject.my_id are null. Because … Continue reading