David G. Johnston wrote: > Now if only the vast majority of users could have and keep this level of understanding > in mind while writing complex queries so that they remember to always add protections > to compensate for the unique design decision that SQL has taken here...
You can only say that if you don't understand NULL (you wouldn't be alone). If I modify a JSON with an unknown value, the result is unknown. This seems very intuitive to me.
One could argue that whoever uses SQL should understand SQL.
But I believe that it is reasonable to suppose that many people who use JSON in the database are more savvy with JSON than with SQL (they might not have chosen JSON otherwise), so I agree that it makes sense to change this particular behavior.
I can and do understand SQL quite well and still likely would end up being tripped up by this (though not surprised when it happened) because I can't and don't want to think about what will happen if NULL appears in every expression I write when a typical SQL query can contain tens of them. I'd much rather assume that NULL inputs aren't going to happen and have the system tell me when that assumption is wrong. Having to change my expressions to: COALESCE(original_input, function(original_input, something_that_could_be_null_in_future_but_cannot_right_now)) just adds undesirable mental and typing overhead.