You may already be aware that NULL isn't equal to anything. That's why we always have 'IS NULL' instead of '= NULL' in our where clauses. When they say not equal to anything, they really mean not equal to anything. Not even itself.
So let's say you have a table that looks a bit like this:
and you have a unique key on always2, always3, and sometimes1
You build some logic around the fairly reasonable idea 'we'll try to insert, and if we get a duplicate key error, we'll update instead'.
And then you try to insert
'always2 = "value1", always3 = "value2", sometimes1 = NULL, always1 = m, sometimes2 = n'
expecting this to fail because of a duplicate key error and update the values for always1 and sometimes2 for the third row above. But it won't. Because NULL isn't equal to itself. So you'll end up with a table like this: