4 ms·
IMO, thinking of it as coercion is wrong. Rather, many aggregate functions on many databases filter nulls first. For sum there's no difference, but for others
by dllthomas 9y ago
IMO, thinking of it as coercion is wrong. Rather, many aggregate functions on many databases filter nulls first. For sum there's no difference, but for others (eg. average) there is. I make no particular claim as to whether that's the right choice.
- willtim 9y agoIt's the wrong choice, since they defined x+null=null and ignoring missing/undefined/error values (which null often represents) is dangerous in many domains. Users could have always filtered nulls manually, which would have made this dangerous behaviour explicit.
- dllthomas 9y agoI see your argument and it's a strong one, I merely haven't considered the issue deeply enough to have much confidence that there aren't other strong arguments the other way. I don't object to your making the claim, I merely wouldn't make it myself :) That said, I think the fact that there's a hard question here stems from SQL's conflation of "I don't know this" with "this is known to be absent". If NULL always represented the latter, implicitly omitting them from aggregates would be clearly correct.